using System;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Data;
namespace My.Common.Database
{
/// <summary>
/// 对SqlServer的支持
/// </summary>
public class SqlDBOperate:DBOperate
{
//数据库连接
private SqlConnection conn;
//事务处理类
private SqlTransaction trans;
//指示当前操作是否在事务中
private bool bInTrans = false;
/// <summary>
/// 在构造方法中创建数据库连接
/// </summary>
/// <param name="strConnection"></param>
public SqlDBOperate(string strConnection)
{
this.conn = new SqlConnection(strConnection);
}
#region 重写基类的属性
/// <summary>
/// 改变数据库连接的类型
/// 重写了基类的虚属性
/// </summary>
public override IDbConnection baseConnection
{
get
{
return this.conn;
}
set
{
this.conn = (SqlConnection)value;
}
}
/// <summary>
/// 改变数据库事务的类型
/// 重写了基类的虚属性
/// </summary>
public override IDbTransaction baseTransaction
{
get
{
return this.trans;
}
set
{
this.trans = (SqlTransaction)value;
}
}
#endregion
#region 重写基类的方法
/// <summary>
/// 释放非托管资源
/// </summary>
public override void Dispose()
{
this.Dispose(true);
GC.SuppressFinalize(this);
}
/// <summary>
/// 打开数据库连接
/// </summary>
public override void Open()
{
if (conn.State.Equals(ConnectionState.Closed))
{
conn.Open();
}
}
/// <summary>
/// 关闭数据库连接
/// </summary>
public override void Close()
{
if (conn.State.Equals(ConnectionState.Open))
{
conn.Close();
}
}
/// <summary>
/// 开始一个事务
/// </summary>
public override void BeginTran()
{
if (!this.bInTrans)
{
trans = conn.BeginTransaction();
bInTrans = true;
}
}
/// <summary>
/// 提交一个事务
/// </summary>
public override void CommitTran()
{
if(this.bInTrans)
{
trans.Commit();
bInTrans = false;
}
}
/// <summary>
/// 回滚一个事务
/// </summary>
public override void RollBackTran()
{
if(this.bInTrans)
{
trans.Rollback();
bInTrans = false;
}
}
#endregion
#region Dispose(bool disposing)
/// <summary>
/// 为什么会是protected 而不是private
/// </summary>
/// <param name="disposing"></param>
protected void Dispose(bool disposing)
{
if(disposing==false)
{
return;
}
if(this.bInTrans==true&& this.trans!= null)
{
this.trans.Dispose();
this.trans= null;
}
if(this.conn!= null)
{
if(this.conn.State== ConnectionState.Open)
{
this.conn.Close();
}
this.conn.Dispose();
this.conn= null;
}
}
#endregion
#region GerPreCommand(string strSql, IDataParameter[] parameters, string strCommandType)
/// <summary>
/// 获取一个SqlCommand对象
/// </summary>
/// <param name="strSql">sql语句</param>
/// <param name="parameters">命令参数</param>
/// <param name="strCommandType">命令类型</param>
/// <returns></returns>
private SqlCommand GetPreCommand(string strSql, IDataParameter[] parameters, string strCommandType)
{
//初始化一个command对象
SqlCommand cmdSql = conn.CreateCommand();
try
{
//设置SqlCommand对象的sql语句或存储过程
cmdSql.CommandText = strSql;
if(strCommandType == "PROCEDURE")
{
cmdSql.CommandType = CommandType.StoredProcedure;
}
//判断是否在事务中
if(this.bInTrans)
{
//设置SqlCommand对象所在的事务
cmdSql.Transaction = this.trans;
}
//指定各个参数的取值
foreach(IDataParameter SqlParm in parameters)
{
//设置SqlCommand对象的参数
cmdSql.Parameters.Add(SqlParm);
}
}
catch(Exception ex)
{
throw ex;
}
finally
{
}
return cmdSql;
}
#endregion
#region 执行update、insert、delete 语句，如果成功返回受影响的条数，失败抛出一个异常
/// <summary>
/// 带预处理的
/// </summary>
/// <param name="strSql"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public override int ExecPreUpdateSql(string strSql, IDataParameter[] parameters)
{
//初始化一个command对象
SqlCommand cmdSql = GetPreCommand(strSql, parameters, "SQL");
//判断是否在事务中
if(this.bInTrans)
{
cmdSql.Transaction = this.trans;
}
//返回受影响的条数
return cmdSql.ExecuteNonQuery();
}
/// <summary>
/// 不带预处理的
/// </summary>
/// <param name="strSql"></param>
/// <returns></returns>
public override int ExecUpdateSql(string strSql)
{
SqlCommand cmdSql = conn.CreateCommand();
cmdSql.CommandText = strSql;
//判断是否在事务中
if(this.bInTrans)
{
cmdSql.Transaction = this.trans;
}
return cmdSql.ExecuteNonQuery();
}
#endregion
#region 执行select 语句，共分三种情况 返回DataSet 、返回DataReader 、返回DataTable
/// <summary>
/// 带预处理的返回DataSet对象
/// </summary>
/// <param name="strSql"></param>
/// <param name="parameters"></param>
/// <param name="strTableName"></param>
/// <returns></returns>
p