SQLherpel
using System.Linq;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System;
/*
搞明白几个问题:
1.ADO.NET五大常用对象?
2.using 与 close的作用及区别?
3.DataReader的用法及注意事项?
*/
/// <summary>
/// ADO.NET数据库操作
/// </summary>
public class SqlHelper
{
/// <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;
}
}
}
/// <summary>
/// 执行查询(连接式),逐行读取查询结果
/// </summary>
/// <remarks>使用完毕后,一定不要忘记Reader.Close()</remarks>
/// <param name="sql">SQL语句</param>
/// <param name="parameters">SQL参数集合</param>
/// <returns>SqlDataReader对象</returns>
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;
}
}
}
/// <summary>
/// 执行非查询(增删改)语句
/// </summary>
/// <param name="sql">SQL语句</param>
/// <param name="parameters">SQL参数集合</param>
/// <returns>返回受影响的行数</returns>
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;
}
}
}
}
/// <summary>
/// 执行查询,返回查询结果第一行第一列的值
/// </summary>
/// <remarks>常用于查询SUM、COUNT等聚合函数的结果</remarks>
/// <param name="sql">SQL语句</param>
/// <param name="parameters">SQL参数集合</param>
/// <returns>第一行第一列的值</returns>
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;
}
}
}
}
}