using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace DataLinkMesWork
{
    public class DataAccess
    {
        public static string ConnectionString_MES = ConfigurationManager.AppSettings["ConnectionString_MES"];


        /// <summary>
        /// 增 删 改
        /// </summary>
        /// <param name="procedureName"></param>
        /// <param name="sqlParameters"></param>
        /// <returns></returns>
        public static bool ExecuteStoredProcedure(string procedureName, ref SqlParameter[] sqlParameters)
        {
            string connectionString = ConnectionString_MES;
            string errorMessage;
            bool result = false;
            errorMessage = "";
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                try
                {
                    //if (!SqlServerConnectionStatus) return false;
                    conn.Open();
                    using (SqlCommand cmd = new SqlCommand(procedureName, conn))
                    {
                        cmd.CommandTimeout = 0;
                        cmd.CommandType = CommandType.StoredProcedure;
                        if (sqlParameters != null)
                        {
                            for (int i = 0; i < sqlParameters.Length; i++)
                            {
                                cmd.Parameters.Add(sqlParameters[i]);
                            }
                        }
                        cmd.ExecuteNonQuery();
                    }
                    //SqlServerConnectionStatus = true;
                    result = true;
                }
                catch (Exception e)
                {
                    errorMessage = e.ToString();
                    //TestConnection();
                    ////ApplicationLog.WriteLog(e, "\r\nExecuteStoredProcedure\r\n�洢���̣�\r\n" + procedureName + "\r\n�����ַ�����\r\n" + connectionString);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                        conn.Close();
                }
            }
            return result;
        }
        public static bool ExecuteStoredProcedure(string procedureName, ref SqlParameter[] sqlParameters,out DataTable dt)
        {
            string connectionString = ConnectionString_MES;
            string errorMessage;
            bool result = false;
            errorMessage = "";
            //ds = new DataSet();
            dt = new DataTable();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                try
                {
                    //if (!SqlServerConnectionStatus) return false;
                    conn.Open();
                    using (SqlDataAdapter dsCommand = new SqlDataAdapter())
                    {
                        dsCommand.SelectCommand = new SqlCommand(procedureName, conn);
                        dsCommand.SelectCommand.CommandType = CommandType.StoredProcedure;
                        dsCommand.SelectCommand.CommandTimeout = 0;
                        if (sqlParameters != null)
                        {
                            for (int i = 0; i < sqlParameters.Length; i++)
                            {
                                dsCommand.SelectCommand.Parameters.Add(sqlParameters[i]);
                            }
                        }

                        dsCommand.Fill(dt);
                    }
                    //SqlServerConnectionStatus = true;
                    result = true;
                }
                catch (Exception e)
                {
                    errorMessage = e.ToString();
                    //TestConnection();
                    ////ApplicationLog.WriteLog(e, "\r\nExecuteStoredProcedure\r\n�洢���̣�\r\n" + procedureName + "\r\n�����ַ�����\r\n" + connectionString);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                        conn.Close();
                }
            }
            return result;
        }
        public static bool ExecuteStoredProcedure(string procedureName, ref SqlParameter[] sqlParameters, out DataSet ds)
        {
            string connectionString = ConnectionString_MES;
            string errorMessage;
            bool result = false;
            errorMessage = "";
            ds = new DataSet();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                try
                {
                    //if (!SqlServerConnectionStatus) return false;
                    conn.Open();
                    using (SqlDataAdapter dsCommand = new SqlDataAdapter())
                    {
                        dsCommand.SelectCommand = new SqlCommand(procedureName, conn);
                        dsCommand.SelectCommand.CommandType = CommandType.StoredProcedure;
                        dsCommand.SelectCommand.CommandTimeout = 0;
                        if (sqlParameters != null)
                        {
                            for (int i = 0; i < sqlParameters.Length; i++)
                            {
                                dsCommand.SelectCommand.Parameters.Add(sqlParameters[i]);
                            }
                        }

                        dsCommand.Fill(ds);
                    }
                    //SqlServerConnectionStatus = true;
                    result = true;
                }
                catch (Exception e)
                {
                    errorMessage = e.ToString();
                    //TestConnection();
                    ////ApplicationLog.WriteLog(e, "\r\nExecuteStoredProcedure\r\n�洢���̣�\r\n" + procedureName + "\r\n�����ַ�����\r\n" + connectionString);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                        conn.Close();
                }
            }
            return result;
        }
        public static bool ExecuteDataset(string sql, out DataSet ds)
        {
            string errorMessage;
            string connectionString = ConnectionString_MES;
            bool result = false;
            errorMessage = "";
            ds = new DataSet();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                try
                {
                    //if (!SqlServerConnectionStatus) return false;
                    conn.Open();
                    using (SqlDataAdapter dsCommand = new SqlDataAdapter())
                    {
                        dsCommand.SelectCommand = new SqlCommand(sql, conn);
                        dsCommand.Fill(ds);
                    }
                    //SqlServerConnectionStatus = true;
                    result = true;
                }
                catch (Exception e)
                {
                    errorMessage = e.ToString();
                    //TestConnection();
                    ////ApplicationLog.WriteLog(e, "\r\nExecuteNonQuery\r\nSQL��䣺\r\n" + sql + "\r\n�����ַ�����\r\n" + connectionString);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                        conn.Close();
                }
            }
            return result;
        }

        public static bool ExecuteDataTable(string sql, out DataTable dt)
        {
            //public static bool ExecuteDataTable(string sql, string connectionString, out DataTable dt, out string errorMessage)
            string errorMessage;
            string connectionString = ConnectionString_MES;
            bool result = false;
            errorMessage = "";
            dt = new DataTable();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                try
                {
                    //if (!SqlServerConnectionStatus) return false;
                    conn.Open();
                    using (SqlDataAdapter dsCommand = new SqlDataAdapter())
                    {
                        dsCommand.SelectCommand = new SqlCommand(sql, conn);
                        dsCommand.Fill(dt);
                    }
                    //SqlServerConnectionStatus = true;
                    result = true;
                }
                catch (Exception e)
                {
                    errorMessage = e.ToString();
                    //TestConnection();
                    ////ApplicationLog.WriteLog(e, "\r\nExecuteNonQuery\r\nSQL��䣺\r\n" + sql + "\r\n�����ַ�����\r\n" + connectionString);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                        conn.Close();
                }
            }
            return result;
        }

        public static DataTable  ExecuteSQL(string sql, ref SqlParameter[] sqlParameters)
        {
            string connectionString = ConnectionString_MES;
            string err;
            bool result = false;
            err = "";
            DataTable dt = new DataTable();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                try
                {
                    //if (!SqlServerConnectionStatus) return false;
                    conn.Open();
                    using (SqlDataAdapter dsCommand = new SqlDataAdapter(sql, conn))
                    {
                        if (sqlParameters != null)
                        {
                            for (int i = 0; i < sqlParameters.Length; i++)
                            {
                                dsCommand.SelectCommand.Parameters.Add(sqlParameters[i]);
                            }
                        }
                        dsCommand.Fill(dt);
                    }
                    result = true;
                }
                catch (Exception e)
                {
                    err = e.ToString();
                    ////ApplicationLog.WriteLog(e, "\r\nExecuteSelectMesWork\r\n" + sql + "\r\n" + connectionString);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                        conn.Close();
                }
            }
            return dt;
        }

        public static bool ExecuteSQL(string sql)
        {
            string connectionString = ConnectionString_MES;
            string errorMessage;
            bool result = false;
            errorMessage = "";
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                try
                {
                    using (SqlCommand cmd = new SqlCommand(sql, conn))
                    {
                        //if (!SqlServerConnectionStatus) return false;
                        conn.Open();
                        cmd.ExecuteNonQuery();
                    }
                    //SqlServerConnectionStatus = true;
                    result = true;
                }
                catch (Exception e)
                {
                    errorMessage = e.ToString();
                    //TestConnection();
                    ////ApplicationLog.WriteLog(e, "\r\nExecuteNonQuery\r\nSQL��䣺\r\n" + sql + "\r\n�����ַ�����\r\n" + connectionString);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                        conn.Close();
                }
            }
            return result;
        }

        /// <summary>
        /// 提供通用的调用存储过程的方法（增加数据的数据表的到数据库的调用）
        /// </summary>
        /// <param name="procedureName">存储过程名称</param>
        /// <param name="sqlParameters">存储过程参数数组</param>
        /// <param name="dt">增加数据的数据表</param>
        /// <returns></returns>
        public static bool ExecuteStoredProcedure(string procedureName, ref SqlParameter[] sqlParameters, DataTable dt)
        {
            string connectionString = ConnectionString_MES;
            string errorMessage;
            bool result = false;
            errorMessage = "";
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                try
                {
                   // if (!SqlServerConnectionStatus) return false;
                    conn.Open();
                    using (SqlDataAdapter dsCommand = new SqlDataAdapter())
                    {
                        dsCommand.InsertCommand = new SqlCommand(procedureName, conn);
                        dsCommand.InsertCommand.CommandType = CommandType.StoredProcedure;
                        for (int i = 0; i < sqlParameters.Length; i++)
                        {
                            dsCommand.InsertCommand.Parameters.Add(sqlParameters[i]);
                        }
                        if (dt == null) return false;
                        dt.AcceptChanges();
                        for (int i = 0; i < dt.Rows.Count; i++)
                        {
                            dt.Rows[i].SetAdded();
                        }
                        dsCommand.Update(dt);
                    }
                    //SqlServerConnectionStatus = true;
                    result = true;
                }
                catch (Exception e)
                {
                    errorMessage = e.ToString();
                    //TestConnection();
                    ////ApplicationLog.WriteLog(e, "\r\nExecuteStoredProcedure\r\n�洢���̣�\r\n" + procedureName + "\r\n�����ַ�����\r\n" + connectionString);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                        conn.Close();
                }
            }
            return result;
        }

        /// <summary>
        /// 提供通用的调用存储过程的方法（增加数据的数据表的到数据库的调用）
        /// </summary>
        /// <param name="procedureName"></param>
        /// <param name="dt"></param>
        /// <returns></returns>
        public static bool ExecuteStoredProcedure(string procedureName, out DataTable dt)
        {
            string connectionString = ConnectionString_MES;
            string errorMessage;
            bool result = false;
            errorMessage = "";
            dt = new DataTable();
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                try
                {

                    //if (!SqlServerConnectionStatus) return false;
                    conn.Open();
                    using (SqlDataAdapter dsCommand = new SqlDataAdapter())
                    {
                        dsCommand.SelectCommand = new SqlCommand(procedureName, conn);
                        dsCommand.SelectCommand.CommandType = CommandType.StoredProcedure;

                        dsCommand.Fill(dt);
                    }
                    //SqlServerConnectionStatus = true;
                    result = true;
                }
                catch (Exception e)
                {
                    errorMessage = e.ToString();
                    //TestConnection();
                    ////ApplicationLog.WriteLog(e, "\r\nExecuteStoredProcedure\r\n�洢���̣�\r\n" + procedureName + "\r\n�����ַ�����\r\n" + connectionString);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                        conn.Close();
                }
            }
            return result;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="sqlParameters"></param>
        /// <param name="tb"></param>
        /// <returns></returns>
        public static bool ExecuteSQL(string sql, ref SqlParameter[] sqlParameters, DataTable dt)
        {
            string connectionString = ConnectionString_MES;
            string errorMessage;
            bool result = false;
            errorMessage = "";
            using (SqlConnection conn = new SqlConnection(connectionString))
            {


                try
                {
                   // if (!SqlServerConnectionStatus) return false;
                    conn.Open();
                    using (SqlDataAdapter dsCommand = new SqlDataAdapter())
                    {
                        dsCommand.InsertCommand = new SqlCommand(sql, conn);

                        for (int i = 0; i < sqlParameters.Length; i++)
                        {
                            sqlParameters[i].IsNullable = true;
                            dsCommand.InsertCommand.Parameters.Add(sqlParameters[i]);
                        }
                        if (dt == null) return false;
                        dt.AcceptChanges();
                        for (int i = 0; i < dt.Rows.Count; i++)
                        {
                            dt.Rows[i].SetAdded();
                        }
                        dsCommand.Update(dt);
                    }
                    //SqlServerConnectionStatus = true;
                    result = true;
                }
                catch (Exception e)
                {
                    //ApplicationConfig.ConnectionStringPIS_NEW_Value = "0";
                    //TestConnection();
                    //errorMessage = e.ToString();
                    ////ApplicationLog.WriteLog(e, "\r\nExecuteSQL\r\nSQL��䣺\r\n" + sql + "\r\n�����ַ�����\r\n" + connectionString);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                        conn.Close();
                }
            }
            return result;
        }

        public static bool ExecuteNonQuery_newID(string sql, out long newID)
        {
            sql += ";select @@identity";
            string connectionString;
            connectionString = ConnectionString_MES;
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                try
                {
                    using (SqlCommand cmd = new SqlCommand(sql, conn))
                    {
                        conn.Open();
                        newID = Convert.ToInt64(cmd.ExecuteScalar());
                    }
                    return true;
                }
                catch (Exception e)
                {
                    newID = 0;
                    //TestConnection();
                    ////ApplicationLog.WriteLog(e, "\r\n ExecuteNonQuery_newID\r\n�洢���̣�\r\n" + sql + "\r\n�����ַ�����\r\n" + connectionString);
                    return false;
                }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                        conn.Close();
                }
            }
        }

        public static bool ExecuteOutNum(string sql, out int count)
        {
            string connectionString;
            connectionString = ConnectionString_MES;
            string errorMessage;
            bool result = false;
            errorMessage = "";
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                count = 0;
                try
                {
                    using (SqlCommand cmd = new SqlCommand(sql, conn))
                    {
                        //if (!SqlServerConnectionStatus) return false;
                        conn.Open();
                        count = Convert.ToInt32(cmd.ExecuteScalar().ToString());
                    }
                    //SqlServerConnectionStatus = true;
                    result = true;
                }
                catch (Exception e)
                {
                    errorMessage = e.ToString();
                    //TestConnection();
                    ////ApplicationLog.WriteLog(e, "\r\nExecuteNonQuery\r\nSQL��䣺\r\n" + sql + "\r\n�����ַ�����\r\n" + connectionString);
                }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                        conn.Close();
                }
            }
            return result;
        }
    }


}