通用数据库连接执行类（SQL）

using System;

using System.Data;

using System.Data.SqlClient;

 

namespace Public

{

     ///<summary>

     /// CustomADO 数据连接执行类。

     ///</summary>

     public class CustomADO

     {

 

         #region 定义或创建类私有变量或对象

 

         private string _connstr;         // 连接字符串存储变量

         private string _procedure;       // 存储过程名或数据命令字符串变量

         private bool _isprocedure;      // 是否使用存储过程

         private SqlConnection _conn;     // 连接对象

         private SqlCommand _comm;        // 数据命令对象

         private SqlDataReader _dr;       // 定义数据读取器

         private DataSet _ds;             // 定义数据存储器

        

         #endregion

 

         #region 构造函数

 

         ///<summary>

         /// 构造函数重载1

         ///</summary>

         public CustomADO()

         {

              _connstr = null;

              _procedure = null;

              _isprocedure = false;

 

              _conn = new SqlConnection();

              _comm = new SqlCommand();

        }

 

         ///<summary>

         /// 构造函数重载2

         ///</summary>

         ///<param name="connstring"> 数据库连接字符串 </param>

         ///<param name="procedure"> 存储过程或SQL命令 </param>

         ///<param name="isprocedure"> 是否使用存储过程 </param>

         public CustomADO(string connstring,string procedure,bool isprocedure)

         {

              _connstr = connstring;

              _procedure = procedure;

              _isprocedure = isprocedure;

 

              _conn = new SqlConnection();

              _comm = new SqlCommand();

         }

 

         #endregion

 

         #region 属性

 

         ///<summary>

         /// 设置或获取数据库连接字符串

         ///</summary>

         public string ConnString

         {

              get

              {

                   return _connstr;

              }

 

              set

              {

                   _connstr = value;

 

                   _conn.ConnectionString = _connstr;

                   _comm.Connection = _conn;

              }

         }

 

         ///<summary>

         /// 设置或获取存储过程名或SQL命令字符串

         ///</summary>

         public string Procedure

         {

              get

              {

                   return _procedure;

              }

 

              set

              {

                   _procedure = value;

 

                   _comm.CommandText = _procedure;

              }

         }

 

         ///<summary>

         /// 设置是否存储过程属性

         ///</summary>

         public bool IsProcedure

         {

              set

              {

                   _isprocedure = value;

 

                   if (_isprocedure)

                   {

                       _comm.CommandType = CommandType.StoredProcedure;

                   }

                   else

                   {

                       _comm.CommandType = CommandType.Text;

                   }

              }

         }

 

         #endregion

 

         #region 方法

 

         ///<summary>

         /// 打开连接

         ///</summary>

         public void OpenConnection()

         {

              _conn.Open();

         }

 

         ///<summary>

         /// 关闭连接和数据读取器并清空数据集合

         ///</summary>

         public void CloseConnection()

         {

              _conn.Close();

         }

 

         ///<summary>

         /// 销毁连接对象和方法

         ///</summary>

         public void Dispost()

         {

              if (_ds != null)

              {

                   _ds.Clear();

              }

 

              if (_dr!=null)

              {

                   _dr.Close();

              }

              _comm.Dispose();

              _conn.Close();

              _conn.Dispose();

         }

 

         ///<summary>

         /// 添加参数重载1

         ///</summary>

         ///<param name="dbtype"> 参数类型 </param>

         ///<param name="parametername"> 参数名 </param>

         ///<param name="pvalue"> 参数值 </param>

         public void AddParameter(DbType dbtype,string parametername,object pvalue)

         {

              SqlParameter p = new SqlParameter();

             

              p.DbType = dbtype;

              p.ParameterName = parametername;

              p.Value = pvalue;

 

              _comm.Parameters.Add(p);

         }

 

         ///<summary>

         /// 添加参数重载2

         ///</summary>

         ///<param name="dbtype"> 参数类型 </param>

         ///<param name="parametername"> 参数名 </param>

         ///<param name="pvalue"> 参数值 </param>

         ///<param name="parameterdirection"> 参数类型 </param>

         public void AddParameter(DbType dbtype,string parametername,object pvalue,ParameterDirection parameterdirection)

         {

              SqlParameter p = new SqlParameter();

             

              p.DbType = dbtype;

              p.ParameterName = parametername;

              p.Value = pvalue;

              p.Direction = parameterdirection;

 

              _comm.Parameters.Add(p);

         }

 

         ///<summary>

         /// 添加返回参数

         ///</summary>

         ///<param name="dbtype">DbType 参数类型 </param>

         ///<param name="parametername"> 参数名 </param>

         public void AddParameterReturnValue(DbType dbtype,string parametername)

         {

              SqlParameter p = new SqlParameter();

            p.DbType = dbtype;

              p.ParameterName = parametername;

              p.Direction = ParameterDirection.ReturnValue;

 

              _comm.Parameters.Add(p);

         }

 

         ///<summary>

         /// 返回DataSet对象

         ///</summary>

         ///<returns> 执行数据命令后返回的DataSet对象 </returns>

         public DataSet ExecuteDataSet()

         {

              _ds = new DataSet();

              SqlDataAdapter da = new SqlDataAdapter();

              da.SelectCommand = _comm;

              da.Fill(_ds);

              return _ds;

         }

 

         ///<summary>

         /// 返回DataReader对象 重载1

         ///</summary>

         ///<returns> 数据读取器 </returns>

         public SqlDataReader ExecuteDataReader()

         {

              _dr = _comm.ExecuteReader();

 

              return _dr;

         }

 

         ///<summary>

         /// 返回DataReader对象 重载2

         ///</summary>

         ///<param name="behavior">CommandBehavior 参数,对参数结果的数据库影响说明 </param>

         ///<returns> 数据读取器 </returns>

         public SqlDataReader ExecuteDataReader(CommandBehavior behavior)

         {

              _dr = _comm.ExecuteReader(behavior);

 

              return _dr;

         }

 

         ///<summary>

         /// 执行数据命令并返回影响行数

         ///</summary>

         public int ExecuteNonQuery()

         {

              return _comm.ExecuteNonQuery();

         }

 

         ///<summary>

         /// 通过参数名获得参数值

         ///</summary>

         ///<param name="parametername"> 参数名 </param>

         ///<returns> 返回参数值 </returns>

         public object GetParameterValue(string parametername)

         {

              object returnvalue;

 

              returnvalue = _comm.Parameters[parametername].Value;

             

              return returnvalue;

         }

 

         #endregion

 

     }

}