public class DBHelper
{
private static string connstring = ConfigurationManager.ConnectionStrings["DBConnection"].ToString();
/// <summary>
/// 使用using执行增，删、改
/// </summary>
/// <returns></returns>
public static int UsingExecuteSql(string sql,params SqlParameter[] parameters)
{
using(SqlConnection connection=new SqlConnection(connstring))
{
connection.Open();
SqlCommand command = new SqlCommand(sql,connection);
command.Parameters.AddRange(parameters);
int result = command.ExecuteNonQuery();
return result;
}
}
/// <summary>
/// 存储过程执行增、删、改
/// </summary>
/// <param name="procName"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public static int ExecuteProc(string procName,params SqlParameter[] parameters)
{
using (SqlConnection connection=new SqlConnection(connstring))
{
connection.Open();
SqlCommand command = new SqlCommand(procName,connection);
command.Parameters.AddRange(parameters);
command.CommandType = CommandType.StoredProcedure;//类型为存储过程的名字
return command.ExecuteNonQuery();
}
}
/// <summary>
/// 使用using执行查询单个值
/// </summary>
/// <returns></returns>
public static object UsingSingalExecuteSql(string sql, params SqlParameter[] parameters)
{
using (SqlConnection connection=new SqlConnection(connstring))
{
connection.Open();
SqlCommand command = new SqlCommand(sql,connection);
command.Parameters.AddRange(parameters);
return command.ExecuteScalar();
}
}
/// <summary>
/// 返回多个值的dataReader
/// </summary>
/// <param name="sql"></param>
/// <param name="parameters"></param>
/// <returns>返回一个DATAREADER</returns>
public static SqlDataReader UsingDataReaderExecuteSql(string sql, params SqlParameter[] parameters)
{
try
{
SqlConnection connection = new SqlConnection(connstring);
SqlCommand command = new SqlCommand(sql, connection);
command.Parameters.AddRange(parameters);
return command.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (Exception)
{
throw;
}
}
///// <summary>
///// 执行增，删，改
///// </summary>
///// <param name="sql">执行的SQL语句</param>
///// <param name="parameters"></param>
///// <returns>结果状态</returns>
//public static int ExecuteSql(string sql,params SqlParameter[] parameters)
//{
// try
// {
// OpenConnection();//打开数据库连接
// SqlCommand command = new SqlCommand(sql, conn);
// command.Parameters.AddRange(parameters);
// int result = command.ExecuteNonQuery();
// return result;
// }
// catch (Exception ex)
// {
// throw ex;
// }
// finally
// {
// CloseConnection();
// }
//}
///// <summary>
///// 执行增，删，改
///// </summary>
///// <param name="sql">存储过程名字</param>
///// <param name="parameters"></param>
///// <returns>结果状态</returns>
//public static int ExecuteProc(string procName, params SqlParameter[] parameters)
//{
// try
// {
// OpenConnection();//打开数据库连接
// SqlCommand command = new SqlCommand(sql, conn);
// command.CommandType = CommandType.StoredProcedure;
// command.Parameters.AddRange(parameters);
// int result = command.ExecuteNonQuery();
// return result;
// }
// catch (Exception ex)
// {
// throw ex;
// }
// finally
// {
// CloseConnection();
// }
//}
///// <summary>
///// 通过SQL语句获取单个值
///// </summary>
///// <param name="sql"></param>
///// <param name="parameters"></param>
///// <returns></returns>
//public static object GetScale(string sql, params SqlParameter[] parameters)
//{
// try
// {
// OpenConnection();//打开数据库连接
// SqlCommand command = new SqlCommand(sql, conn);
// command.Parameters.AddRange(parameters);
// return command.ExecuteScalar();
// }
// catch (Exception ex)
// {
// throw ex;
// }
// finally
// {
// CloseConnection();
// }
//}
///// <summary>
///// 返回结果集
///// </summary>
///// <param name="sql"></param>
///// <param name="parameters"></param>
///// <returns></returns>
//public static DataSet GetDataSet(string sql, params SqlParameter[] parameters)
//{
// try
// {
// OpenConnection();//打开数据库连接
// DataSet ds=new DataSet();
// SqlCommand command=new SqlCommand(sql,conn);
// command.Parameters.AddRange(parameters);
// SqlDataAdapter adapter = new SqlDataAdapter(command);
// adapter.Fill(ds);
// return ds;
// }
// catch (Exception ex)
// {
// throw ex;
// }
// finally
// {
// CloseConnection();
// }
//}
/// <summary>
/// 执行增，删，改,事务处理
/// 对于一条SQL语句不需要，对于多条才需要
/// </summary>
/// <param name="sql">存储过程名字</param>
/// <param name="parameters"></param>
/// <returns>结果状态</returns>
//public static int ExecuteProc(string procName, params SqlParameter[] parameters)
//{
// SqlTransaction trans=null;
// try
// {
// OpenConnection();//打开数据库连接
// trans = conn.BeginTransaction();
// SqlCommand command = new SqlCommand(sql, conn);
// command.CommandType = CommandType.StoredProcedure;
// command.Parameters.AddRange(parameters);
// int result = command.ExecuteNonQuery();
// trans.Commit();
// return result;
// }
// catch (Exception ex)
// {
// if (trans!=null)
// {
// trans.Rollback();
// }
// throw ex;
// }
// finally
// {
// CloseConnection();
// }
//}
