/// <summary>
/// 访问SQL Server的通用数据访问类
/// </summary>
class SQLHelper
{
//定义连接字符串
private static readonly string connString =
ConfigurationManager.ConnectionStrings["connString"].ToString();
/// <summary>
/// 执行增删改操作
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static int Update(string sql)
{
SqlConnection conn = new SqlConnection(connString);
SqlCommand cmd = new SqlCommand(sql, conn);
try
{
conn.Open();
return cmd.ExecuteNonQuery();
}
catch(Exception ex)
{
//将异常信息写入日志
throw ex;
}
finally//最终一定会被执行的代码
{
conn.Close();
}
}
/// <summary>
/// 执行单一结果查询
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static object GetSingleResult(string sql)
{
SqlConnection conn = new SqlConnection(connString);
SqlCommand cmd = new SqlCommand(sql, conn);
try
{
conn.Open();
return cmd.ExecuteScalar();
}
catch (Exception ex)
{
//将异常信息写入日志
throw ex;
}
finally//最终一定会被执行的代码
{
conn.Close();
}
}
/// <summary>
/// 执行一个结果集的查询
/// </summary>
/// <param name="sql"></param>
/// <returns>返回一个SqlDataReader对象</returns>
public static SqlDataReader GetReader(string sql)
{
SqlConnection conn = new SqlConnection(connString);
SqlCommand cmd = new SqlCommand(sql, conn);
try
{
conn.Open();
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (Exception ex)
{
//将异常信息写入日志
if(conn.State==ConnectionState.Open)
{
conn.Close();
}
throw ex;
}
//finally//最终一定会被执行的代码
//{
// conn.Close();
//}
}
/// <summary>
/// 执行返回数据集的查询
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
public static DataSet GetDataSet(string sql)
{
SqlConnection conn = new SqlConnection(connString);
SqlCommand cmd = new SqlCommand(sql, conn);
//创建数据适配器对象
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
try
{
conn.Open();
da.Fill(ds);//使用数据数据适配器,填充数据集
return ds;
}
catch (Exception ex)
{
//将异常信息写入日志
throw ex;
}
finally//最终一定会被执行的代码
{
//连接对象必须要关闭
conn.Close();
}
}
/// <summary>
/// 获取数据库的时间
/// </summary>
/// <returns></returns>
public static DateTime GetServerTime()
{
return Convert.ToDateTime(GetSingleResult("select getdate()"));
}
/// <summary>
/// 启用事务执行多条sql语句
/// </summary>
/// <param name="sqlList">sql语句集合</param>
/// <returns></returns>
public static bool UpdateByTran(List<string> sqlList)
{
SqlConnection conn = new SqlConnection(connString);
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
try
{
conn.Open();
cmd.Transaction = conn.BeginTransaction();//开启事务
foreach(string itemSql in sqlList)
{
cmd.CommandText = itemSql;
cmd.ExecuteNonQuery();
}
cmd.Transaction.Commit();//提交事务(真正保存到数据库)
return true;
}
catch (Exception ex)
{
if (cmd.Transaction != null)
{
cmd.Transaction.Rollback();//回滚事务
}
throw new Exception("调用事务方法 public static bool UpdateByTran(List<string> sqlList)出错 :"+ex.Message);
}
finally
{
if (cmd.Transaction != null)
{
cmd.Transaction = null;//清除事务
}
conn.Close();
}
}
}