using System;
using System.Data;
using System.Data.SqlClient;
namespace PeterZhu.DbOperator
{
public class DbOperator : IDisposable
{
public DbOperator()
{
}
/// <summary>
/// 用于数据库连接
/// </summary>
protected SqlConnection conn;
/// <summary>
/// 用于执行命令
/// </summary>
protected SqlCommand cmd;
/// <summary>
/// 数据库事务
/// </summary>
protected SqlTransaction trans;
/// <summary>
/// 当前连接是否正处在事务中
/// </summary>
protected bool _IsInTrans;
/// <summary>
/// 数据库连接字符串节的名称
/// </summary>
private string _ConnStrSectionName;
/// <summary>
/// 指示是否已经执行过清理
/// </summary>
private bool disposed;
#region 设置连接字符串
/// <summary>
/// 公开属性,用于设置数据库连接字符串节的名称。连接字符串位于connectionStrings节
/// </summary>
public string ConnStrSectionName
{
set
{
if (String.IsNullOrEmpty(value))
throw new Exception("数据库连接字符串节的名称不能为空!");
_ConnStrSectionName = value;
}
}
/// <summary>
/// 私有方法,取得web.config文件中的连接字符串节
/// </summary>
/// <returns></returns>
private string GetConnString()
{
if (String.IsNullOrEmpty(_ConnStrSectionName))
{
throw new Exception("数据库连接字符串节的名称不能为空!");
}
return System.Web.Configuration.WebConfigurationManager.ConnectionStrings[_ConnStrSectionName].ConnectionString;
}
#endregion
#region 数据库连接
/// <summary>
/// 私有方法,用于打开数据库连接
/// </summary>
private void OpenConn()
{
if (conn == null || conn.State == ConnectionState.Closed)
{
if (conn == null) conn = new SqlConnection();
conn.ConnectionString = GetConnString();
conn.Open();
}
}
/// <summary>
/// 用于关闭数据库连接
/// </summary>
private void CloseConn()
{
if (conn != null)
{
conn.Close();
}
}
#endregion
#region 释放资源
/// <summary>
/// 释放类所占用的资源
/// </summary>
public void Dispose()
{
if (!this.disposed)
{
this.Free();
}
disposed = true;
GC.SuppressFinalize(this);
}
/// <summary>
/// 析构函数,执行最后的清理工作,以防止未手工调用Dispose方法
/// </summary>
~DbOperator()
{
Free();
}
/// <summary>
///私有方法, 释放类所占用的资源
/// </summary>
private void Free()
{
if (conn != null)
{
conn.Dispose();
}
if (cmd != null)
{
cmd.Dispose();
}
if (trans != null)
{
trans.Dispose();
}
}
#endregion
#region 事务处理
/// <summary>
/// 公开属性,判断当前连接是否正处在事务中
/// </summary>
public bool IsInTrans
{
get { return _IsInTrans; }
}
/// <summary>
/// 开始一个事务处理
/// </summary>
/// <param name="transName">事务的名称</param>
public void BeginTrans(string transName)
{
if (String.IsNullOrEmpty(transName))
throw new ArgumentNullException("transName", "事务名称不能为空!");
OpenConn();
trans = conn.BeginTransaction(transName);
_IsInTrans = true;
}
/// <summary>
/// 提交事务
/// </summary>
public void CommitTrans()
{
if (_IsInTrans == true)
{
if (trans.Connection != null)
{
trans.Commit();
_IsInTrans = false;
}
else
{
throw new Exception("该事务已经失效,提交事务失败!");
}
}
}
/// <summary>
/// 回滚事务
/// </summary>
public void RollBackTrans()
{
if (_IsInTrans == true && trans.Connection != null)
{
trans.Rollback();
_IsInTrans = false;
}
}
#endregion
#region 创建SqlCommand对象
/// <summary>
/// 创建SqlCommand对象
/// </summary>
/// <param name="cmdText">要执行的Sql语句</param>
/// <returns>SqlCommand</returns>
public SqlCommand CreateCommand(string cmdText)
{
return CreateCommand(cmdText, CommandType.Text);
}
/// <summary>
/// 创建SqlCommand对象
/// </summary>
/// <param name="cmdText">要执行的CommandText</param>
/// <param name="cmdType">执行命令的CommandType</param>
/// <returns>SqlCommand</returns>
public SqlCommand CreateCommand(string cmdText, CommandType cmdType)
{
OpenConn();
SqlCommand myCmd = new SqlCommand();
myCmd.Connection = conn;
myCmd.CommandText = cmdText;
myCmd.CommandType = cmdType;
return myCmd;
}
#endregion
#region 为SqlCommand执行命令准备参数
/// <summary>
/// 为SqlCommand执行命令准备参数
/// </summary>
/// <param name="cmdText">CommandText</param>
/// <param name="cmdType">CommandType</param>
/// <param name="cmdParms">参数数组</param>
protected void PrepareCommand(string cmdText, CommandType cmdType, SqlParameter[] cmdParms)
{
cmd = CreateCommand(cmdText, cmdType);
if (_IsInTrans)
{
cmd.Transaction = trans;
}
if (cmdParms != null)
{
foreach (SqlParameter parm in cmdParms)
{
cmd.Parameters.Add(parm);
}
}
}
#endregion
#region 基础数据操作
#region ExecuteNonQuery操作
/// <summary>
/// 执行一个带参数的命令,返回受影响的条数
/// </summary>
/// <param name="cmdText">CommandText</param>
/// <param name="cmdType">CommandType</param>
/// <param name="cmdParms">参数数组</param>
/// <returns></returns>
public int ExecuteNonQuery(string cmdText, CommandType cmdType, SqlParameter[] cmdParms)
{
PrepareCommand(cmdText, cmdType, cmdParms);
int result = 0;
result = cmd.ExecuteNonQuery();
cmd.Parameters.Clear();
return result;
}
/// <summary>
/// 执行一个不带参数的命令,返回受影响的条数
/// </summary>
/// <param name="cmdText">要执行的CommandText命令</param>
/// <param name="cmdType">要执行命令的CommandType</param>
/// <returns></returns>
public int ExecuteNonQuery(string cmdText, CommandType cmdType)
{
int result = ExecuteNonQuery(cmdText, cmdType, null);
return result;
}
#endregion
#region ExecuteReader操作
/// <summary>
/// 执行一个带参数的命令,返回SqlDataReader。使用后应当及时关闭DataReader
/// </summary>
/// <param name="cmdText">要执行的CommandText</param>
/// <param name="cmdType">要执行命令的CommandType</param>
/// <param name="cmdParms">参数数组</param>
/// <returns></returns>
public SqlDataReader ExecuteReader(string cmdText, CommandType cmdType, SqlParameter[] cmdParms)
{
PrepareCommand(cmdText, cmdType, cmdParms);
try
{
SqlDataReader r = cmd.ExecuteReader(CommandBehavior.CloseConnection);
cmd.Parameters.Clear();
return r;
}
catch
{
CloseConn();
throw;
}
}
/// <summary>
/// 执行一个不带参数的命令,返回SqlDataReader对象
/// </summary>
/// <param name="cmdText">要执行的CommandText</param>
/// <param name="cmdType">要执行命令的CommandType</param>
/// <returns></returns>
public SqlDataReader ExecuteReader(string cmdText, CommandType cmdType)
{
SqlDataReader r = ExecuteReader(cmdText, cmdType, null);
return r;
}
#endregion
#region ExecuteScalar操作
/// <summary>
/// 返回第一条记录第一列
/// </summary>
/// <param name="cmdText">要执行的命令CommandText</param>
/// <param name="cmdType">要执行命令的CommandType</param>
/// <param name="cmdParms">参数数组</param>
/// <returns></returns>
public object ExecuteScalar(string cmdText, CommandType cmdType, SqlParameter[] cmdParms)
{
PrepareCommand(cmdText, cmdType, cmdParms);
object result = cmd.ExecuteScalar();
cmd.Parameters.Clear();
return result;
}
#endregion
#endregion
}
}