/// <summary>
/// 数据库连接字符串,从配置文件读取
/// </summary>
private static readonly string strConn = ConfigurationManager.ConnectionStrings["sql"].ConnectionString;
/// <summary>
/// 执行查询(非连接式),将查询结果全部加载到内存中
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="parameters">SQL参数集合</param>
/// <returns>包含查询结果的表</returns>
public static DataTable ExecQuery(string sql, params SqlParameter[] parameters)
{
//using加载完之后自动关闭
using (SqlDataAdapter dataAdapter = new SqlDataAdapter(sql, strConn))
{
using (DataTable table = new DataTable())
{
//判断参数中是否有值
if (parameters != null && parameters.Count() > 0)
{
//将参数集合加载到SelectCommand
dataAdapter.SelectCommand.Parameters.AddRange(parameters);
}
try
{
dataAdapter.Fill(table);
}
catch (Exception ex) //执行出错,释放资源
{
table.Dispose();
dataAdapter.Dispose();
//抛出异常
throw ex;
}
return table;
}
}
}
执行查询语句,使用完之后记得关闭 Reader.Close();
public static SqlDataReader ExecReader(string sql, params SqlParameter[] parameters)
{
//创建连接对象
//【为什么不能使用using?】
SqlConnection sqlConnection = new SqlConnection(strConn);
using (SqlCommand sqlCommand = new SqlCommand(sql, sqlConnection))
{
//判断参数中是否有值
if (parameters != null && parameters.Count() > 0)
{
sqlCommand.Parameters.AddRange(parameters);
}
try
{
sqlConnection.Open();
//将Reader与Connection进行绑定,关闭Reader的同时释放Connection
return sqlCommand.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (Exception ex) //执行出错,释放资源
{
sqlCommand.Dispose();
sqlConnection.Close();
sqlConnection.Dispose();
throw ex;
}
}
}
执行增删改查方法
public static int ExecNonQuery(string sql, params SqlParameter[] parameters)
{
using (SqlConnection sqlConnection = new SqlConnection(strConn))
{
using (SqlCommand sqlCommand = new SqlCommand(sql, sqlConnection))
{
if (parameters != null && parameters.Count() > 0)
{
sqlCommand.Parameters.AddRange(parameters);
}
try
{
sqlConnection.Open();
//返回受影响的行数
return sqlCommand.ExecuteNonQuery();
}
catch (Exception ex)
{
sqlCommand.Dispose();
sqlConnection.Close();
sqlConnection.Dispose();
throw ex;
}
}
}
}
执行查询,返回查询结果第一行第一列的值,常用于查询SUM、COUNT等聚合函数的结果
public static object ExecScalar(string sql, params SqlParameter[] parameters)
{
using (SqlConnection sqlConnection = new SqlConnection(strConn))
{
using (SqlCommand sqlCommand = new SqlCommand(sql, sqlConnection))
{
if (parameters != null && parameters.Count() > 0)
{
sqlCommand.Parameters.AddRange(parameters);
}
try
{
sqlConnection.Open();
return sqlCommand.ExecuteScalar();
}
catch (Exception ex)
{
sqlCommand.Dispose();
sqlConnection.Close();
sqlConnection.Dispose();
throw ex;
}
}
}
}
}