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
//------------------
}
12-09
4452
![](https://csdnimg.cn/release/blogv2/dist/pc/img/readCountWhite.png)
12-10
04-10