110,533
社区成员
发帖
与我相关
我的任务
分享
public class DbHelper
{
#region 私有构造和私有变量
/// <summary>
/// 私有构造函数
/// </summary>
private DbHelper()
{
}
/// <summary>
/// 默认的数据库连接字串,此连接字串在此类第一次初始化的时候进行初始化
/// </summary>
private static string connString = ConfigurationManager.ConnectionStrings["ConnString"].ToString();
#endregion
#region 返回受影响的行数
/// <summary>
/// 按照指定的语句对数据表进行操作,返回受影响的行数
/// </summary>
/// <param name="sqlStr">操作语句</param>
/// <returns>受影响的行数</returns>
public static int ExecuteNonQuery(string sqlStr)
{
int mark = 0;
using (SqlConnection con = new SqlConnection(connString))
{
SqlCommand cmd = new SqlCommand(sqlStr, con);
try
{
if (con.State != ConnectionState.Open)
con.Open();
mark = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
PubFunction.ErrorLogPrint(sqlStr, ex.ToString());
}
finally
{
if (con.State != ConnectionState.Closed)
con.Close();
}
return mark;
}
}
/// <summary>
/// 按照指定的语句对数据表进行操作,返回受影响的行数
/// </summary>
/// <param name="sqlStr">操作语句</param>
/// <param name="conString">非默认的数据连接字符串</param>
/// <returns>受影响的行数</returns>
public static int ExecuteNonQuery(string sqlStr, string conString)
{
using (SqlConnection myCon = new SqlConnection(conString))
{
int mark = 0;
SqlCommand cmd = new SqlCommand(sqlStr, myCon);
try
{
if (myCon.State != ConnectionState.Open)
myCon.Open();
mark = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
PubFunction.ErrorLogPrint(sqlStr, ex.ToString());
}
finally
{
if (myCon.State != ConnectionState.Closed)
myCon.Close();
}
return mark;
}
}
/// <summary>
/// 按照指定的语句对数据表进行操作,返回受影响的行数
/// </summary>
/// <param name="sqlStr">操作语句</param>
/// <param name="isProc">是否是存储过程,true:是;fale:不是</param>
/// <param name="parameter">参数列表,可缺失</param>
/// <returns>受影响的行数</returns>
public static int ExecuteNonQuery(string sqlStr, bool isProc, params SqlParameter[] parameter)
{
int mark = 0;
using (SqlConnection con = new SqlConnection(connString))
{
SqlCommand cmd = new SqlCommand(sqlStr, con);
if (isProc)
{
cmd.CommandType = CommandType.StoredProcedure;
}
if (parameter != null && parameter.Length > 0)
{
foreach (SqlParameter item in parameter)
{
cmd.Parameters.Add(item);
}
}
try
{
if (con.State != ConnectionState.Open)
con.Open();
mark = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
PubFunction.ErrorLogPrint(sqlStr, ex.ToString());
}
finally
{
if (con.State != ConnectionState.Closed)
con.Close();
}
return mark;
}
}
/// <summary>
/// 按照指定的语句对数据表进行操作,返回受影响的行数
/// </summary>
/// <param name="sqlStr">操作语句</param>
/// <param name="conString">非默认的数据连接字符串</param>
/// <param name="isProc">是否是存储过程,true:是;fale:不是</param>
/// <param name="parameter">参数列表,可缺失</param>
/// <returns>受影响的行数</returns>
public static int ExecuteNonQuery(string sqlStr, string conString, bool isProc, params SqlParameter[] parameter)
{
using (SqlConnection myCon = new SqlConnection(conString))
{
int mark = 0;
SqlCommand cmd = new SqlCommand(sqlStr, myCon);
if (isProc)
{
cmd.CommandType = CommandType.StoredProcedure;
}
if (parameter != null && parameter.Length > 0)
{
foreach (SqlParameter item in parameter)
{
cmd.Parameters.Add(item);
}
}
try
{
if (myCon.State != ConnectionState.Open)
myCon.Open();
mark = cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
PubFunction.ErrorLogPrint(sqlStr, ex.ToString());
}
finally
{
if (myCon.State != ConnectionState.Closed)
myCon.Close();
}
return mark;
}
}
#endregion
}
/// <summary>
/// 执行事务
/// </summary>张辉 2011.09.21
/// <param name="cmdList">需要按事务执行的Sql语句集合</param>
/// <returns>返回true:执行成功;返回false:执行失败</returns>
public static bool ExecuteTran(List<string> cmdList)
{
bool mark = false;
using (SqlConnection con = new SqlConnection(connString))
{
if (con.State != ConnectionState.Open)
con.Open();
SqlTransaction tran = con.BeginTransaction();
try
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.Transaction = tran;
foreach (string item in cmdList)
{
cmd.CommandText = item;
cmd.ExecuteNonQuery();
}
mark = true;
tran.Commit();
}
catch (Exception ex)
{
PubFunction.ErrorLogPrint("事务执行", ex.Message);
tran.Rollback();
}
return mark;
}
}
/// <summary>
/// 执行事务,可自定义连接字符串
/// </summary>张辉 2011.09.21
/// <param name="cmdList">需要按事务执行的Sql语句集合</param>
/// <param name="conStr">自定义的连接字符串</param>
/// <returns>返回true:执行成功;返回false:执行失败</returns>
public static bool ExecuteTran(List<string> cmdList, string conStr)
{
using (SqlConnection myCon = new SqlConnection(conStr))
{
bool mark = false;
if (myCon.State != ConnectionState.Open)
myCon.Open();
SqlTransaction tran = myCon.BeginTransaction();
try
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = myCon;
cmd.Transaction = tran;
foreach (string item in cmdList)
{
cmd.CommandText = item;
cmd.ExecuteNonQuery();
}
mark = true;
tran.Commit();
}
catch (Exception ex)
{
PubFunction.ErrorLogPrint("事务执行", ex.Message);
tran.Rollback();
}
return mark;
}
}
/// <summary>
/// 带参数的存储过程执行
/// </summary>张辉 2011.09.28 添加
/// <param name="cmdList">执行命令和参数列表集合列表</param>
/// <returns>true:执行成功 false:执行失败</returns>
public static bool ExecuteTran(List<SqlStrWithParameter> cmdList)
{
bool mark = false;
using (SqlConnection con = new SqlConnection(connString))
{
if (con.State != ConnectionState.Open)
con.Open();
SqlTransaction tran = con.BeginTransaction();
try
{
SqlCommand cmd = new SqlCommand();
cmd.Connection = con;
cmd.Transaction = tran;
foreach (SqlStrWithParameter item in cmdList)
{
cmd.Dispose();
cmd.CommandText = item.SqlStr;
foreach (SqlParameter p in item.Parameters)
{
cmd.Parameters.Add(p);
}
cmd.ExecuteNonQuery();
}
mark = true;
tran.Commit();
}
catch (Exception ex)
{
PubFunction.ErrorLogPrint("事务执行", ex.Message);
tran.Rollback();
}
return mark;
}
}