• 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;             }         }

        } }

  • 相关阅读:
    Python 网络爬虫
    Linux 下安装 java 运行环境 jdk1.8
    SSH: Transferred 0 file(s) 解决
    java idea 好用的插件
    java idea 安装 环境配置等 注意事项(只是备注给自己看的事项,不是安装教程)
    git 上传已有项目 到git仓库 初始化git并上传
    git 清除远程仓库已经删除的本地分支 清除已经合并到master的本地分支
    oracle trunc 日期 数字 的使用例子
    股票小白2
    股票小白知识点1
  • 原文地址:https://www.cnblogs.com/xiaowei-blog/p/4171982.html
Copyright © 2020-2023  润新知