小型SqlHelper执行类
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
namespace SMS.DBUtility
{
/// <summary>
/// 数据操作类
/// </summary>
public class SqlHelper
{
private readonly static string ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();
/// <summary>
/// 执行一条sql语句
/// </summary>
/// <param name="strSql">sql语句</param>
/// <returns>返回值</returns>
public static int ExecuteSql(string strSql)
{
using (SqlConnection con = new SqlConnection(ConnectionString))
{
using (SqlCommand cmd = new SqlCommand(strSql, con))
{
try
{
con.Open();
int rows = cmd.ExecuteNonQuery();
return rows;
}
catch (SqlException e)
{
con.Close();
throw e;
}
finally
{
cmd.Dispose();
con.Close();
}
}
}
}
/// <summary>
/// 执行带参sql语句,返回影响条数
/// </summary>
/// <param name="strSql"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public static int ExecuteSql(string strSql, params SqlParameter[] parameters)
{
using (SqlConnection con = new SqlConnection(ConnectionString))
{
SqlCommand cmd = new SqlCommand();
try
{
PrepareCommand(cmd, con, null, strSql, parameters);
int rows = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return rows;
}
catch (SqlException e)
{
throw e;
}
}
}
/// <summary>
/// 执行带参sql语句,返回数据SqlDataReader
/// </summary>
/// <param name="strSql"></param>
/// <param name="parameters"></param>
/// <returns>返回值</returns>
public static SqlDataReader ExecuteReader(string strSql, params SqlParameter[] parameters)
{
using (SqlConnection con = new SqlConnection(ConnectionString))
{
SqlCommand cmd = new SqlCommand();
try
{
PrepareCommand(cmd, con, null, strSql, parameters);
SqlDataReader sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
return sdr;
}
catch (SqlException e)
{
throw e;
}
}
}
/// <summary>
/// 执行存储过程
/// </summary>
/// <param name="ProcName">过程名</param>
/// <param name="parameters">参数</param>
/// <returns></returns>
public static SqlDataReader RunProcedure(string ProcName, params IDataParameter[] parameters)
{
using (SqlConnection con = new SqlConnection())
{
try
{
SqlDataReader sdr = new SqlDataReader();
SqlCommand cmd = BuildCommand(con, ProcName, parameters);
sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
return sdr;
}
catch (SqlException e)
{
throw e;
}
}
}
/// <summary>
/// 分解存储过程参数
/// </summary>
/// <param name="con"></param>
/// <param name="ProcName"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public static SqlCommand BuildCommand(SqlConnection con, string ProcName, params IDataParameter[] parameters)
{
SqlCommand cmd = new SqlCommand(ProcName, con);
cmd.CommandType = CommandType.StoredProcedure;
foreach (SqlParameter parameter in parameters)
{
//检查未分配值的输出参数,将其分配以DBNull.Value.
if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
(parameter.Value == null))
{
parameter.Value = DBNull.Value;
}
command.Parameters.Add(parameter);
}
return cmd;
}
/// <summary>
/// 分解参数
/// </summary>
/// <param name="cmd"></param>
/// <param name="con"></param>
/// <param name="trans"></param>
/// <param name="cmdText"></param>
/// <param name="cmdParms"></param>
public static void PrepareCommand(SqlCommand cmd, SqlConnection con, SqlTransaction trans, string cmdText, SqlParameter[] cmdParms)
{
if (con.State != ConnectionState.Open)
con.Open();
cmd.Connection = con;
cmd.CommandText = cmdText;
if (trans != null)
{
foreach (SqlParameter parameter in cmdParms)
{
if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
(parameter.Value == null))
{
parameter.Value = DBNull.Value;
}
cmd.Parameters.Add(parameter);
}
}
}
}
}
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
namespace SMS.DBUtility
{
/// <summary>
/// 数据操作类
/// </summary>
public class SqlHelper
{
private readonly static string ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();
/// <summary>
/// 执行一条sql语句
/// </summary>
/// <param name="strSql">sql语句</param>
/// <returns>返回值</returns>
public static int ExecuteSql(string strSql)
{
using (SqlConnection con = new SqlConnection(ConnectionString))
{
using (SqlCommand cmd = new SqlCommand(strSql, con))
{
try
{
con.Open();
int rows = cmd.ExecuteNonQuery();
return rows;
}
catch (SqlException e)
{
con.Close();
throw e;
}
finally
{
cmd.Dispose();
con.Close();
}
}
}
}
/// <summary>
/// 执行带参sql语句,返回影响条数
/// </summary>
/// <param name="strSql"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public static int ExecuteSql(string strSql, params SqlParameter[] parameters)
{
using (SqlConnection con = new SqlConnection(ConnectionString))
{
SqlCommand cmd = new SqlCommand();
try
{
PrepareCommand(cmd, con, null, strSql, parameters);
int rows = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return rows;
}
catch (SqlException e)
{
throw e;
}
}
}
/// <summary>
/// 执行带参sql语句,返回数据SqlDataReader
/// </summary>
/// <param name="strSql"></param>
/// <param name="parameters"></param>
/// <returns>返回值</returns>
public static SqlDataReader ExecuteReader(string strSql, params SqlParameter[] parameters)
{
using (SqlConnection con = new SqlConnection(ConnectionString))
{
SqlCommand cmd = new SqlCommand();
try
{
PrepareCommand(cmd, con, null, strSql, parameters);
SqlDataReader sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
return sdr;
}
catch (SqlException e)
{
throw e;
}
}
}
/// <summary>
/// 执行存储过程
/// </summary>
/// <param name="ProcName">过程名</param>
/// <param name="parameters">参数</param>
/// <returns></returns>
public static SqlDataReader RunProcedure(string ProcName, params IDataParameter[] parameters)
{
using (SqlConnection con = new SqlConnection())
{
try
{
SqlDataReader sdr = new SqlDataReader();
SqlCommand cmd = BuildCommand(con, ProcName, parameters);
sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
return sdr;
}
catch (SqlException e)
{
throw e;
}
}
}
/// <summary>
/// 分解存储过程参数
/// </summary>
/// <param name="con"></param>
/// <param name="ProcName"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public static SqlCommand BuildCommand(SqlConnection con, string ProcName, params IDataParameter[] parameters)
{
SqlCommand cmd = new SqlCommand(ProcName, con);
cmd.CommandType = CommandType.StoredProcedure;
foreach (SqlParameter parameter in parameters)
{
//检查未分配值的输出参数,将其分配以DBNull.Value.
if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
(parameter.Value == null))
{
parameter.Value = DBNull.Value;
}
command.Parameters.Add(parameter);
}
return cmd;
}
/// <summary>
/// 分解参数
/// </summary>
/// <param name="cmd"></param>
/// <param name="con"></param>
/// <param name="trans"></param>
/// <param name="cmdText"></param>
/// <param name="cmdParms"></param>
public static void PrepareCommand(SqlCommand cmd, SqlConnection con, SqlTransaction trans, string cmdText, SqlParameter[] cmdParms)
{
if (con.State != ConnectionState.Open)
con.Open();
cmd.Connection = con;
cmd.CommandText = cmdText;
if (trans != null)
{
foreach (SqlParameter parameter in cmdParms)
{
if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
(parameter.Value == null))
{
parameter.Value = DBNull.Value;
}
cmd.Parameters.Add(parameter);
}
}
}
}
}