| ◇usingSystem; usingSy...... using System; using System.Data.SqlClient; using System.Data.OleDb; using System.Data; namespace My.Common.Database { /// /// 对SqlServer的支持 /// public class SqlDBOperate:DBOperate { //数据库连接 private SqlConnection conn; //事务处理类 private SqlTransaction trans; //指示当前操作是否在事务中 private bool bInTrans = false; /// /// 在构造方法中创建数据库连接 /// /// public SqlDBOperate(string strConnection) { this.conn = new SqlConnection(strConnection); } #region 重写基类的属性 /// /// 改变数据库连接的类型 /// 重写了基类的虚属性 /// public override IDbConnection baseConnection { get { return this.conn; } set { this.conn = (SqlConnection)value; } } /// /// 改变数据库事务的类型 /// 重写了基类的虚属性 /// public override IDbTransaction baseTransaction { get { return this.trans; } set { this.trans = (SqlTransaction)value; } } #endregion #region 重写基类的方法 /// /// 释放非托管资源 /// public override void Dispose() { this.Dispose(true); GC.SuppressFinalize(this); } /// /// 打开数据库连接 /// public override void Open() { if (conn.State.Equals(ConnectionState.Closed)) { conn.Open(); } } /// /// 关闭数据库连接 /// public override void Close() { if (conn.State.Equals(ConnectionState.Open)) { conn.Close(); } } /// /// 开始一个事务 /// public override void BeginTran() { if (!this.bInTrans) { trans = conn.BeginTransaction(); bInTrans = true; } } /// /// 提交一个事务 /// public override void CommitTran() { if(this.bInTrans) { trans.Commit(); bInTrans = false; } } /// /// 回滚一个事务 /// public override void RollBackTran() { if(this.bInTrans) { trans.Rollback(); bInTrans = false; } } #endregion #region Dispose(bool disposing) /// /// 为什么会是protected 而不是private /// /// 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) /// /// 获取一个SqlCommand对象 /// /// sql语句 /// 命令参数 /// 命令类型 /// 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 语句，如果成功返回受影响的条数，失败抛出一个异常 /// /// 带预处理的 /// /// /// /// 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(); } /// /// 不带预处理的 /// /// /// 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 /// /// 带预处理的返回DataSet对象 /// /// /// /// /// public override System.Data.DataSet ExecPreQueryForDataSet(string strSql, IDataParameter[] parameters, string strTableName) { //初始化一个DataSet对象，一个DataAdapter对象 System.Data.DataSet dsSql = new DataSet(); System.Data.SqlClient.SqlDataAdapter daSql = new SqlDataAdapter(strSql,conn); //初始化一个command对象 SqlCommand cmdSql = GetPreCommand(strSql, parameters, "SQL"); //返回DataSet对象 daSql.SelectCommand = cmdSql; daSql.Fill(dsSql,strTableName); return dsSql; } /// /// 带预处理的返回DataReader对象 /// /// /// /// public override System.Data.IDataReader ExecPreQueryForDataReader(string strSql, IDataParameter[] parameters) { //初始化一个command对象 SqlCommand cmdSql = GetPreCommand(strSql, parameters, "SQL"); //返回DataReader对象 return cmdSql.ExecuteReader(); } /// /// 带预处理的返回DataTable对象 /// /// /// /// public override System.Data.DataTable ExecPreQueryForDataTable(string strSql, IDataParameter[] parameters) { //初始化一个DataAdapter对象，一个DataTable对象 System.Data.DataTable dtSql = new DataTable(); System.Data.SqlClient.SqlDataAdapter daSql = new SqlDataAdapter(strSql,conn); //初始化一个command对象 SqlCommand cmdSql = GetPreCommand(strSql, parameters, "SQL"); //返回DataTable对象 daSql.SelectCommand = cmdSql; daSql.Fill(dtSql); return dtSql; } /// /// 不带预处理的返回DataSet对象 /// /// /// /// public override System.Data.DataSet ExecQueryForDataSet(string strSql,string strTableName) { //初始化一个DataSet对象，一个DataAdapter对象 System.Data.DataSet dsSql = new DataSet(); System.Data.SqlClient.SqlDataAdapter daSql = new SqlDataAdapter(strSql,conn); //初始化一个command对象 SqlCommand cmdSql = conn.CreateCommand(); cmdSql.CommandText = strSql; //判断是否在事务中 if(this.bInTrans) { cmdSql.Transaction = this.trans; } //返回DataSet对象 daSql.SelectCommand = cmdSql; daSql.Fill(dsSql,strTableName); return dsSql; } /// /// 不带预处理的返回DataReader对象 /// /// /// public override System.Data.IDataReader ExecQueryForDataReader(string strSql) { SqlCommand cmdSql = null; //初始化一个command对象 cmdSql = conn.CreateCommand(); cmdSql.CommandText = strSql; //判断是否在事务中 if(this.bInTrans) { cmdSql.Transaction = this.trans; } //返回DataReader对象 return cmdSql.ExecuteReader(); } /// /// 不带预处理的返回DataTable对象 /// /// /// public override System.Data.DataTable ExecQueryForDataTable(string strSql) { //初始化一个DataAdapter对象，一个DataTable对象 System.Data.DataTable dtSql = new DataTable(); System.Data.SqlClient.SqlDataAdapter daSql = new SqlDataAdapter(strSql,conn); //初始化一个command对象 SqlCommand cmdSql = conn.CreateCommand(); cmdSql.CommandText = strSql; //判断是否在事务中 if(this.bInTrans) { cmdSql.Transaction = this.trans; } //返回DataTable对象 daSql.SelectCommand = cmdSql; daSql.Fill(dtSql); return dtSql; } #endregion #region 处理存储过程 /// /// 不返回结果集的 /// /// /// /// public override int ExecUpdateProc(string strSql, IDataParameter[] parameters) { //初始化一个command对象 SqlCommand cmdSql = GetPreCommand(strSql, parameters, "PROCEDURE"); //判断是否在事务中 if(this.bInTrans) { cmdSql.Transaction = this.trans; } //返回受影响的条数 return cmdSql.ExecuteNonQuery(); } /// /// 返回DataSet对象 /// /// /// /// /// public override DataSet ExecProcForDataSet(string strSql, IDataParameter[] parameters,string strTableName) { //初始化一个DataSet对象，一个DataAdapter对象 System.Data.DataSet dsSql = new DataSet(); System.Data.SqlClient.SqlDataAdapter daSql = new SqlDataAdapter(strSql,conn); //初始化一个command对象 SqlCommand cmdSql = GetPreCommand(strSql, parameters, "PROCEDURE"); //返回DataSet对象 daSql.SelectCommand = cmdSql; daSql.Fill(dsSql,strTableName); return dsSql; } /// /// 返回DataReader /// /// /// /// public override IDataReader ExecProcForDataReader(string strSql, IDataParameter[] parameters) { //初始化一个command对象 SqlCommand cmdSql = GetPreCommand(strSql, parameters, "PROCEDURE"); //返回DataReader对象 return cmdSql.ExecuteReader(); } /// /// 返回DataTable /// /// /// /// public override DataTable ExecProcForDataTable(string strSql, IDataParameter[] parameters) { //初始化一个DataAdapter对象，一个DataTable对象 System.Data.DataTable dtSql = new DataTable(); System.Data.SqlClient.SqlDataAdapter daSql = new SqlDataAdapter(strSql,conn); //初始化一个command对象 SqlCommand cmdSql = GetPreCommand(strSql, parameters, "PROCEDURE"); //返回DataTable对象 daSql.SelectCommand = cmdSql; daSql.Fill(dtSql); return dtSql; } #endregion } } |
|小黑屋|最新主题|手机版|微赢网络技术论坛
( 苏ICP备08020429号 )
GMT+8, 2024-9-29 21:25 , Processed in 0.235096 second(s), 12 queries , Gzip On, MemCache On.
Powered by Discuz! X3.5
© 2001-2023 Discuz! Team.
