DBHelper数据库操作类

3次阅读
没有评论

DBHelper数据库操作类

vs2010+Access开发程序用到的DBHelper类

分享备用

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Collections;
using System.Windows.Forms;

namespace DBHelper
{
    class DBHelp
    {
        //SqlDataReader dataReader = null;    //dataRader  对象
        OleDbDataReader dr = null;
        //SqlCommand com = null;      //执行命令
        OleDbCommand cmd = null;
        //SqlConnection con = null;   //连库类
        OleDbConnection conn = null;
        OleDbDataAdapter sda = null;

        /// <summary>
        /// 构造函数
        /// </summary>
        public  DBHelp()
        {           
            //连接数据库字符串
          
            string dbpath = Application.StartupPath+"\\";
            //string connStr = @"Provider=Microsoft.Jet.OleDB.4.0;Data Source=E:\db.mdb;Jet OleDb:DataBase Password=dbdb";
            //获取程序当前目录下的数据库
            string connStr = @"Provider=Microsoft.Jet.OleDB.4.0;Data Source="+dbpath+"db.mdb;Jet OleDb:DataBase Password=dbdb";

            conn = new OleDbConnection(connStr);
            //打开数据库连接
            conn.Open();
            cmd = new OleDbCommand();
            cmd.Connection = conn;
        }
        /// <summary>
        /// 增删改查
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public int GetAddUpDel(string sql)
        {
            //cmd.CommandText = sql;
            OleDbCommand cmd = new OleDbCommand(sql, conn);

            int data = Convert.ToInt32(cmd.ExecuteScalar());
            return data;
        }
        /// <summary>
        /// 增删改查,参数查询
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public int UpdateDeleteAdd(string sql,string query1,string query2)
        {
            cmd.CommandText = sql;
             cmd.Parameters.AddWithValue(query1,query2);
             int data = Convert.ToInt32(cmd.ExecuteScalar());
            return data;
        }
        ///<summary>
        ///带参数的执行命令  
        ///</summary>
        ///<param name="sql"></param>
        ///<returns></returns>
        public  int ExecuteCommand(string sql, params OleDbParameter[] values)
        {

            OleDbCommand cmd = new OleDbCommand(sql, conn);
                cmd.Parameters.AddRange(values);
                return cmd.ExecuteNonQuery();
            
        }  
        /// <summary>
        /// 增删改查,参数查询,两个参数
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public int GetData_value(string sql, string query1, string query2,string query3, string query4)
        {
            cmd.CommandText = sql;
            cmd.Parameters.AddWithValue(query1, query2);
            cmd.Parameters.AddWithValue(query3, query4);
            int data = Convert.ToInt32(cmd.ExecuteScalar());
            return data;
        }
        /// <summary>
        /// 查询多个值的方法,正解20160816
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public  OleDbDataReader GetReader(string safeSql ,string query1, string query2)
        {
            OleDbCommand cmd = new OleDbCommand(safeSql, conn);
            cmd.Parameters.AddWithValue(query1, query2);
           // return cmd.ExecuteReader(CommandBehavior.CloseConnection);
            return cmd.ExecuteReader();
        }  
        /// <summary>
        /// 查询多个值的方法,过时
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public OleDbDataReader GetDataReader(string sql)
        {
            cmd.CommandText = sql;
            dr = cmd.ExecuteReader();
            return dr;
        }
        /// <summary>
        /// 查询多个值的方法,参数查询,过时
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public OleDbDataReader SelectAll(string sql, string query1, string query2)
        {
           // cmd.CommandText = sql;
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            cmd.Parameters.AddWithValue(query1, query2);
            dr = cmd.ExecuteReader();
            return dr;
        }
        /// <summary>
        /// 获取OleDbDataAdapter,参数查询
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public OleDbDataAdapter GetAdapter(string sql, string query1, string query2)
        {
            cmd.CommandText = sql;
            cmd.Parameters.AddWithValue(query1, query2);
            sda = new OleDbDataAdapter(cmd);
            return sda;
        }
        /// <summary>
        /// 获取OleDbDataAdapter,参数查询
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public  DataSet GetDataSet(string strSQL)
        {
            OleDbCommand cmd = new OleDbCommand(strSQL, conn);
            OleDbDataAdapter da = new OleDbDataAdapter();
            da.SelectCommand = cmd;
            DataSet ds = new DataSet();
            da.Fill(ds);
            
            return ds;
        }

        ///<summary>
        ///执行查找语句,返回datatable对象
        ///</summary>
        public  DataTable GetDataTable(string strSQL)
        {

                DataTable dt = new DataTable();
                try
                {
                    OleDbDataAdapter sda = new OleDbDataAdapter(strSQL, conn);
                    sda.Fill(dt);
                }
                catch (System.Data.SqlClient.SqlException ex)
                {
                    throw new Exception(ex.Message);
                }
                return dt;
            
        }
        ///<summary>
        ///执行查找语句,返回datatable对象,带参数
        ///</summary>
        public DataTable GetDataTable_var(string strSQL,string var1,string var2)
        {

            DataTable dt = new DataTable();
            try
            {
                OleDbCommand cmd = new OleDbCommand(strSQL, conn);
                cmd.Parameters.AddWithValue(var1, var2);
                OleDbDataAdapter sda = new OleDbDataAdapter();
                sda.SelectCommand = cmd;
                sda.Fill(dt);
                
            }
            catch (System.Data.SqlClient.SqlException ex)
            {
                throw new Exception(ex.Message);
            }
            return dt;

        }


        /// <summary>
        /// 执行多条SQL语句,实现数据库事务。
        /// </summary>
        /// <param name="SQLStringList">SQL语句的哈希表(key为sql语句,value是该语句的SqlParameter[])</param>
        public static void ExecuteSqlTran(Hashtable SQLStringList)
        {
            //string connStr = @"Provider=Microsoft.Jet.OleDB.4.0;Data Source=E:\db.mdb;Jet OleDb:DataBase Password=dbdb";
            string dbpath = Application.StartupPath + "\\";
            string connStr = @"Provider=Microsoft.Jet.OleDB.4.0;Data Source=" + dbpath + "db.mdb;Jet OleDb:DataBase Password=dbdb";
            using (OleDbConnection conn = new OleDbConnection(connStr))
            {
                conn.Open();//打开数据库连接
                using (OleDbTransaction trans = conn.BeginTransaction())//开始数据库事务
                {
                    OleDbCommand cmd = new OleDbCommand();//创建SqlCommand命令
                    try
                    {
                        //循环
                        foreach (DictionaryEntry myDE in SQLStringList)//循环哈希表(本例中 即,循环执行添加在哈希表中的sql语句
                        {
                            string cmdText = myDE.Key.ToString();//获取键值(本例中 即,sql语句)
                            OleDbParameter[] cmdParms = (OleDbParameter[])myDE.Value;//获取键值(本例中 即,sql语句对应的参数)
                            PrepareCommand(cmd, conn, trans, cmdText, cmdParms); //调用PrepareCommand()函数,添加参数
                            int val = cmd.ExecuteNonQuery();//调用增删改函数ExcuteNoQuery(),执行哈希表中添加的sql语句
                            cmd.Parameters.Clear(); //清除参数
                        }
                        trans.Commit();//提交事务
                    }
                    catch //捕获异常
                    {
                        trans.Rollback(); //事务回滚
                        throw; //抛出异常
                    }
                }
            }
        }
        //添加参数
        private static void PrepareCommand(OleDbCommand cmd, OleDbConnection conn, OleDbTransaction trans, string cmdText, OleDbParameter[] cmdParms)
        {
            if (conn.State != ConnectionState.Open)//如果数据库连接为关闭状态
                conn.Open();//打开数据库连接
            cmd.Connection = conn;//设置命令连接
            cmd.CommandText = cmdText;//设置执行命令的sql语句
            if (trans != null)//如果事务不为空
                cmd.Transaction = trans;//设置执行命令的事务
            cmd.CommandType = CommandType.Text;//设置解释sql语句的类型为“文本”类型(也是就说该函数不适用于存储过程)
            if (cmdParms != null)//如果参数数组不为空
            {


                foreach (OleDbParameter parameter in cmdParms) //循环传入的参数数组
                {
                    if ((parameter.Direction == ParameterDirection.InputOutput || parameter.Direction == ParameterDirection.Input) &&
                        (parameter.Value == null))
                    {
                        parameter.Value = DBNull.Value; //获取参数的值
                    }
                    cmd.Parameters.Add(parameter);//添加参数
                }
            }
        }
       
        /// <summary>
        /// 打开数据库连接 
        /// </summary>
        public void GetOpen()
        {
            conn.Open();
        }

          /// <summary>
          /// 关闭数据库连接 
          /// </summary>
          public void GetClose()
          {
              conn.Close();
          }
    }
    
}