sqlserver数据库操作公共类DBOperate

using System; using System.Collections.Generic; using System.Linq; using System.Text;

using System.Data.SqlClient; using System.Data; using System.Windows.Forms; using  WindowsFormsApplication1.DBTools;//提供数据库连接

namespace liuxw_MPS.DBTools {     /// <summary>     /// 数据库操作公共类。     /// 改进：结合SqlParamsTool类，可比较好的支持动态SQL语句操作     /// </summary>     class NewDBOperate     {

        private SqlConnection conn=null;//数据库连接对象         private SqlTransaction  trans=null;//事务         /// <summary>         /// 构造方法中建立数据库连接         /// </summary>         public  NewDBOperate()         {           conn=DBConnection.getConnection();         }         /// <summary>         /// 打开数据库连接         /// </summary>         public  void  OpenConnection()         {            if(conn.State!=ConnectionState.Open)            {               conn.Open();            }              }         /// <summary>         /// 关闭数据库连接         /// </summary>         public  void CloseConnection()         {           if(conn.State!=ConnectionState.Closed)           {             conn.Close();           }         }                /// <summary>         ///  执行需要返回DataSet的sql语句；         /// </summary>         /// <param name="sql"></param>         /// <param name="sp"></param>         /// <returns></returns>         public DataSet GetDataSet(string sql, SqlParameter[] sp)         {             SqlDataAdapter sda = new SqlDataAdapter(sql, conn);             SqlCommand cmd = sda.SelectCommand;             if (sp != null)//sql语句有动态参数,设置参数             {

                //>>>方式1.设置参数                 //foreach(SqlParameter param in sp)                 //{                 // sda.SelectCommand.Parameters.Add(param);

                //}                 //>>>方式2.设置参数                 sda.SelectCommand.Parameters.AddRange(sp);//参数sp不能为null值             }                          sda.SelectCommand.Transaction = trans;//事务.             DataSet ds = new DataSet();             sda.Fill(ds);             return ds;          }         /// <summary>         ///  执行需要返回DataTable表的sql语句；         /// </summary>         /// <param name="sql"></param>         /// <param name="sp"></param>         /// <returns></returns>         public DataTable GetTable(string sql, SqlParameter[] sp)         {             return this.GetDataSet(sql,sp).Tables[0];         }         /// <summary>         ///  执行需要返回SqlDataReader对象的sql语句；         /// </summary>         /// <param name="sql"></param>         /// <param name="sp"></param>         /// <returns></returns>         public SqlDataReader ExecuteReader(string sql, SqlParameter[] sp)         {             SqlCommand cmd = new SqlCommand(sql, conn);             if (sp != null)             {                 cmd.Parameters.AddRange(sp);//设置sql参数;参数sp不能为null值             }             cmd.Transaction = trans;//使用事务;进行数据库事物处理必须对cmd的Transaction赋值，默认为null值             return cmd.ExecuteReader();         }         /// <summary>         /// 返回查询所返回的结果集中第一行的第一列。忽略其他列或行。         ///可用于用户名登录验证。         /// </summary>         /// <returns>返回单个值</returns>         public object ExecuteScalar(string sql,SqlParameter[] sp)         {             object returnValue = 0;             SqlCommand cmd = new SqlCommand(sql, conn);             if (sp != null)             {                 cmd.Parameters.AddRange(sp);             }             cmd.Transaction = trans;//使用事务             return cmd.ExecuteScalar();

        }         /// <summary>         /// 绑定DataGridView；         /// </summary>         /// <param name="dgv"></param>         /// <param name="sql"></param>         /// <param name="sp"></param>         public void BindDataGridView(DataGridView dgv, string sql, SqlParameter[] sp)         {             dgv.DataSource = this.GetTable(sql,sp).DefaultView;//设置数据源              }         /// <summary>         /// 绑定下拉列表,参数“控件”，“表名”,“列”         /// </summary>         /// <param name="cb"></param>         /// <param name="sql"></param>         /// <param name="sp"></param>         public void BindComboBox(ComboBox cb,string tableName,int column )         {             SqlDataReader  reader = this.ExecuteReader("select * from  "+tableName,null);             while (reader.Read())             {                 cb.Items.Add(reader[column].ToString());             }             reader.Close();         }         /// <summary>         ///  操作数据（增删改）；         /// </summary>         /// <param name="sql"></param>         /// <param name="sp"></param>         /// <returns>返回受影响的行数</returns>         public int OperateData(string sql,SqlParameter[] sp)         {             SqlCommand cmd = new SqlCommand(sql, conn);             cmd.Transaction = trans;//事务             if (sp != null)//sql参数不为空             {                             cmd.Parameters.AddRange(sp);//设置参数;参数sp不能为null值             }             int i = cmd.ExecuteNonQuery();//获取操作受影响的行数             return i;         }

        //>>>事务：         /// <summary>         /// 开启数据库事务         /// </summary>         public void BeginTransaction()         {             trans = conn.BeginTransaction();         }         /// <summary>         /// 提交事务         /// </summary>         public void Commit()         {             trans.Commit();         }         /// <summary>         /// 回滚事务         /// </summary>         public void RollBack()         {             trans.Rollback();         }         /// <summary>         /// 销毁事务         /// </summary>         public void disposeTransaction()         {             if (trans != null)             {                 trans.Dispose();                 trans = null;             }         }

    } }