工作中偶尔会用到ado.net,但一直没有自己的sqlhelper,网上的感觉有些乱。今天抽时间自己整理了一份,功能简洁,但能满足大部分情况下使用,支持sql,存储过程,事务。
public static class SqlHelper
{
private static readonly string conStr = System.Configuration.ConfigurationManager.ConnectionStrings["mssqlserver"].ConnectionString;
#region 使用示例
//string sql = "update [User] set Name=@name where Id=@id";
//SqlParameter[] pms = new SqlParameter[]{
// new SqlParameter("name",SqlDbType.NVarChar,20){Value="xc"},
// new SqlParameter("id",SqlDbType.Int){Value=1}
// };
//int n = SqlHelper.ExecuteNonQuery(sql, CommandType.Text, pms);
#endregion
/// <summary>
/// 执行返回受影响行数的sql或者存储过程
/// </summary>
/// <param name="cmdText">sql,存储过程名</param>
/// <param name="cmdType">CommandType.Text,CommandType.StoredProcedure</param>
/// <param name="pms">sql或procedure的参数,没有该参数则传null</param>
/// <returns>返回受影响行数</returns>
public static int ExecuteNonQuery(string cmdText, CommandType cmdType, params SqlParameter[] pms)
{
using (SqlConnection con = new SqlConnection(conStr))
{
using (SqlCommand cmd = new SqlCommand(cmdText, con))
{
cmd.CommandType = cmdType;
if (pms != null)
{
cmd.Parameters.AddRange(pms);
}
con.Open();
return cmd.ExecuteNonQuery();
}
}
}
/// <summary>
/// 执行返回单个值的sql或者存储过程
/// </summary>
/// <param name="cmdText">sql或procedure</param>
/// <param name="cmdType">CommandType.Text,CommandType.StoredProcedure</param>
/// <param name="pms">sql或procedure的参数,没有该参数则传null</param>
/// <returns>返回单个值</returns>
public static object ExecuteScalar(string cmdText, CommandType cmdType, params SqlParameter[] pms)
{
using (SqlConnection con = new SqlConnection(conStr))
{
using (SqlCommand cmd = new SqlCommand(cmdText, con))
{
cmd.CommandType = cmdType;
if (pms != null)
{
cmd.Parameters.AddRange(pms);
}
con.Open();
return cmd.ExecuteScalar();
}
}
}
/// <summary>
/// 执行返回DataReader的sql或者存储过程
/// 读取完毕需要手动关闭SqlDataReader
/// </summary>
/// <param name="cmdText">sql或procedure</param>
/// <param name="cmdType">CommandType.Text,CommandType.StoredProcedure</param>
/// <param name="pms">sql或procedure的参数,没有该参数则传null</param>
/// <returns>返回DataReader</returns>
public static SqlDataReader ExecuteReader(string cmdText, CommandType cmdType, params SqlParameter[] pms)
{
SqlConnection con = new SqlConnection(conStr);
using (SqlCommand cmd = new SqlCommand(cmdText, con))
{
cmd.CommandType = cmdType;
if (pms != null) cmd.Parameters.AddRange(pms);
try
{
if (con.State == ConnectionState.Closed)
{
con.Open();
}
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (SqlException ex)
{
con.Close();
con.Dispose();
throw ex;
}
}
}
/// <summary>
/// 执行返回DataTable的sql或者存储过程
/// </summary>
/// <param name="cmdText">sql或procedure</param>
/// <param name="cmdType">CommandType.Text,CommandType.StoredProcedure</param>
/// <param name="pms">sql或procedure的参数,没有该参数则传null</param>
/// <returns>返回DataTable</returns>
public static DataTable ExecuteDataTable(string cmdText, CommandType cmdType, params SqlParameter[] pms)
{
DataTable dt = new DataTable();
using (SqlDataAdapter adapter = new SqlDataAdapter(cmdText, conStr))
{
adapter.SelectCommand.CommandType = cmdType;
if (pms != null)
{
adapter.SelectCommand.Parameters.AddRange(pms);
}
adapter.Fill(dt);
}
return dt;
}
/// <summary>
/// 在事务中执行返回受影响行数的多条sql语句
/// </summary>
/// <param name="list">KeyValuePair<string, SqlParameter[]>集合,Key=sql,Value=params</param>
/// <param name="cmdType">CommandType.Text,CommandType.StoredProcedure</param>
/// <returns>返回受影响行数</returns>
public static int ExecuteNonQueryTransaction(List<KeyValuePair<string, SqlParameter[]>> list, CommandType cmdType)
{
using (SqlConnection con = new SqlConnection(conStr))
{
con.Open();
SqlTransaction tx = con.BeginTransaction();
SqlCommand cmd = new SqlCommand();
try
{
int count = 0;
cmd.Connection = con;
cmd.Transaction = tx;
cmd.CommandType = cmdType;
foreach (var item in list)
{
string sql = item.Key;
if (!string.IsNullOrEmpty(sql)) cmd.CommandText = sql;
SqlParameter[] pms = item.Value;
if (pms != null) cmd.Parameters.AddRange(pms);
count += cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
}
tx.Commit();
return count;
}
catch (SqlException ex)
{
tx.Rollback();
throw ex;
}
finally
{
tx.Dispose();
cmd.Dispose();
}
}
}
}