SqlHelper

public static class SqlHelper
    {
        private const int MaxPool = 2000;//最大连接数
        private const int MinPool = 100;//最小连接数
        private const bool Asyn_Process = true;//设置异步访问数据库
        private const bool Mars = true;
        private const int Conn_Timeout = 120;//设置连接等待时间
        private const int Conn_Lifetime = 120;//设置连接的生命周期
        public static string[] GetConnectionString()
        {
            StringBuilder server0 = new StringBuilder();
            server0.Append($" max pool size={MaxPool} ; ");
            server0.Append($" min pool size={ MinPool} ; ");
            server0.Append($"  connect timeout={ Conn_Timeout} ; ");
            server0.Append($"  connection lifetime={ Conn_Lifetime} ; ");
            server0.Append($"  MultipleActiveResultSets={ Mars} ; ");

            string[] ConnectionString = new string[] { "", "" };

            string Server_ip =   ConfigurationManager.AppSettings["Server_ip"].ToString();
            string Server_user = ConfigurationManager.AppSettings["Server_user"].ToString();
            string Server_pass = ConfigurationManager.AppSettings["Server_pass"].ToString();
            string Server_DB =   ConfigurationManager.AppSettings["Server_DB"].ToString();
            string Server_ip1 =  ConfigurationManager.AppSettings["Middle_ip"].ToString();
            string Server_user1 = ConfigurationManager.AppSettings["Middle_user"].ToString();
            string Server_pass1 = ConfigurationManager.AppSettings["Middle_pass"].ToString();
            string Server_DB1 = ConfigurationManager.AppSettings["Middle_DB"].ToString();
            ConnectionString[0] = $"Data Source={Server_ip}; Initial Catalog={Server_DB}; User ID={Server_user}; Password={Server_pass};"    +  server0.ToString();
            ConnectionString[1] = $"Data Source={Server_ip1}; Initial Catalog={Server_DB1}; User ID={Server_user1}; Password={Server_pass1};"+  server0.ToString();
            return ConnectionString;
        }


        public static int ExecuteNonQuery(int Server_id, string sql, params SqlParameter[] ps)
        {

            string[] connArray = GetConnectionString();
            string connStr = connArray[Server_id];

            //创建连接对象
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                //创建命令对象
                SqlCommand cmd = new SqlCommand(sql, conn);
                //添加参数
                cmd.Parameters.AddRange(ps);
                //打开连接
                conn.Open();
                //执行命令，并返回受影响的行数
                int  re=cmd.ExecuteNonQuery();
                conn.Close();
                return re;
            }
        }
         
      


        //获取结果集
        public static DataSet GetDataSet(int Server_id, string sql, params SqlParameter[] ps)
        {
                        DataSet ds = new DataSet();
                      try
                        {
                                    string[] connArray = GetConnectionString();
                                    string connStr = connArray[Server_id];
                                    using (SqlConnection conn = new SqlConnection(connStr))
                                    {
                                        //构造适配器对象               
                                        SqlDataAdapter adapter = new SqlDataAdapter(sql, conn);
                                        adapter.SelectCommand.CommandTimeout = 120;//就是120s了
                                                                                   //添加参数
                                        adapter.SelectCommand.Parameters.AddRange(ps);
                                        //执行结果
                                        //构造数据表，用于接收查询结果


                                        adapter.Fill(ds);
                                        conn.Close();
                                    }
                      }
                    catch (Exception)
                    {


                    }
          


                //返回结果集
                return ds;
            }
      
         
        public static int ExecuteNonQuery_img(int Server_id, string sql, byte[] param)
        {
            string[] connArray = GetConnectionString();
            string connStr = connArray[Server_id];
            using (SqlConnection conn = new SqlConnection(connStr))
            {
                if (conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                }
                using (SqlCommand SQL_cmd = new SqlCommand(sql, conn))
                {
                    SqlCommand cmd = new SqlCommand(sql, conn);
                    SqlParameter parmeter = new SqlParameter("@photo", SqlDbType.Image);
                    parmeter.Value = param;
                    cmd.Parameters.Add(parmeter);
                    int result = cmd.ExecuteNonQuery();
                    conn.Close();   
                }

            }


            return 1;
        }

        #region 使用SqlBulkCopy将DataTable中的数据批量插入数据库中  
       



        /// <summary>        ///     
        /// 
        /// /// </summary>      
        /// /// <param name="connectionString">目标连接字符</param>      
        /// /// <param name="TableName">目标表</param>      
        /// /// <param name="dt">源数据</param>    

        public static void SqlBulkCopyByDatatable(int Server_id, string TableName, DataTable dt)
        {
            string[] connArray = GetConnectionString();
            string connectionString = connArray[Server_id];
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                using (SqlBulkCopy sqlbulkcopy = new SqlBulkCopy(connectionString, SqlBulkCopyOptions.UseInternalTransaction))
                {
                    try
                    {
                        sqlbulkcopy.DestinationTableName = TableName;
                        for (int i = 0; i < dt.Columns.Count; i++)
                        {
                            sqlbulkcopy.ColumnMappings.Add(dt.Columns[i].ColumnName, dt.Columns[i].ColumnName); 
                        }
                        sqlbulkcopy.WriteToServer(dt);
                        sqlbulkcopy.Close();
                    }
                    catch (System.Exception ex)
                    { throw ex; }
                }
            }
        }

        public static object ExecuteScalar(int Server_id, string sql)
        {
            string[] connArray = GetConnectionString();
            string connStr = connArray[Server_id];
            SqlConnection connection = new SqlConnection(connStr);
            using (connection)
            {
                if (connection.State == ConnectionState.Closed)
                {
                    connection.Open();
                }
                using (SqlCommand SQL_cmd = new SqlCommand(sql, connection))
                {
                    SqlCommand cmd = new SqlCommand(sql, connection);
                    object obj= cmd.ExecuteScalar();
                    connection.Close();
                    return obj;
                }

            }



        }

        #endregion
        //------------------
    }