Access数据库操作类

using System;
using System.Data.OleDb;
using System.Data;
using System.Diagnostics;
using System.Collections.Generic;

namespace Common
{
    /// <summary>
    /// Description of DBUtil.
    /// </summary>
    public class AccessDBUtil
    {
        private static String connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=access.mdb;Persist Security Info=False";
        public  AccessDBUtil()
        {
        }
        public static string msgsend,msgsend2;
        //执行单条插入语句，并返回id，不需要返回id的用ExceuteNonQuery执行。Microsoft.Jet.OLEDB.4.0
        public  int ExecuteInsert(string sql,OleDbParameter[] parameters)
        {
            //Debug.WriteLine(sql);
            using (OleDbConnection connection = new OleDbConnection(connectionString))
            {
                OleDbCommand cmd = new OleDbCommand(sql, connection);
                try
                {
                    connection.Open();
                    if(parameters!=null) cmd.Parameters.AddRange(parameters);
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = @"select @@identity";
                    int value = Int32.Parse(cmd.ExecuteScalar().ToString());
                    return value;
                }
                catch (Exception e)
                {
                    throw e;
                }
            }
        }
        public  int ExecuteInsert(string sql)
        {
            return ExecuteInsert(sql,null);
        }
        
        //执行带参数的sql语句,返回影响的记录数（insert,update,delete)
        public  int ExecuteNonQuery(string sql,OleDbParameter[] parameters)
        {
            //Debug.WriteLine(sql);
            using (OleDbConnection connection = new OleDbConnection(connectionString))
            {
                OleDbCommand cmd = new OleDbCommand(sql, connection);
                try
                {
                    connection.Open();
                    if(parameters!=null) cmd.Parameters.AddRange(parameters);
                    int rows = cmd.ExecuteNonQuery();
                    return rows;
                }
                catch (Exception e)
                {
                    throw e;
                }
            }
        }
        //执行不带参数的sql语句，返回影响的记录数
        //不建议使用拼出来SQL
        public  int ExecuteNonQuery(string sql)
        {
            return ExecuteNonQuery(sql,null);
        }
        
        //执行单条语句返回第一行第一列,可以用来返回count(*)
        public  int ExecuteScalar(string sql,OleDbParameter[] parameters)
        {
            //Debug.WriteLine(sql);
            using (OleDbConnection connection = new OleDbConnection(connectionString))
            {
                OleDbCommand cmd = new OleDbCommand(sql, connection);
                try
                {
                    connection.Open();
                    if(parameters!=null) cmd.Parameters.AddRange(parameters);
                    int value = Int32.Parse(cmd.ExecuteScalar().ToString());
                    return value;
                }
                catch (Exception e)
                {
                    throw e;
                }
            }
        }
        public string ExecuteScalar_str(string sql)
        {
            using (OleDbConnection connection = new OleDbConnection(connectionString))
            {
                OleDbCommand cmd = new OleDbCommand(sql, connection);
                try
                {
                    connection.Open();
                    string  value = cmd.ExecuteScalar().ToString();
                    return value;
                }
                catch (Exception e)
                {
                    throw e;
                }
            }
        
        }
        public  int ExecuteScalar(string sql)
        {
            return ExecuteScalar(sql,null);
        }
        
        //执行事务
        public  void ExecuteTrans(List<string> sqlList,List<OleDbParameter[]> paraList)
        {
            //Debug.WriteLine(sql);
            using (OleDbConnection connection = new OleDbConnection(connectionString))
            {
                OleDbCommand cmd = new OleDbCommand();
                OleDbTransaction transaction = null;
                cmd.Connection = connection;
                try
                {
                    connection.Open();
                    transaction = connection.BeginTransaction();
                    cmd.Transaction = transaction;
                    
                    for(int i=0;i<sqlList.Count;i++)
                    {
                        cmd.CommandText=sqlList[i];
                        if(paraList!=null&&paraList[i]!=null) 
                        {
                            cmd.Parameters.Clear();
                            cmd.Parameters.AddRange(paraList[i]);
                        }
                        cmd.ExecuteNonQuery();
                    }
                    transaction.Commit();

                }
                catch (Exception e)
                {
                    try
                    {
                        transaction.Rollback();
                    }
                    catch
                    {
                       
                    }
                    throw e;
                }
                
            }
        }
        public  void ExecuteTrans(List<string> sqlList)
        {
            ExecuteTrans(sqlList,null);
        }

        //执行查询语句，返回dataset
        public  DataSet ExecuteQuery(string sql,OleDbParameter[] parameters)
        {
            //Debug.WriteLine(sql);
            using (OleDbConnection connection = new OleDbConnection(connectionString))
            {
                DataSet ds = new DataSet();
                try
                {
                    connection.Open();
                    
                    OleDbDataAdapter da = new OleDbDataAdapter(sql, connection);
                    if(parameters!=null) da.SelectCommand.Parameters.AddRange(parameters);
                    da.Fill(ds,"ds");
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                return ds;
            }
        }
        public  DataSet ExecuteQuery(string sql)
        {
            return ExecuteQuery(sql,null);
        }
        public DataTable ExecuteDatatable(string sql)
        {
            using (OleDbConnection connection = new OleDbConnection(connectionString))
            {
                DataSet ds = new DataSet();
                try
                {
                    connection.Open();
                    OleDbDataAdapter da = new OleDbDataAdapter(sql, connection);
                    DataTable dt = new DataTable("table");
                    ds.Tables.Add(dt);
                    da.Fill(ds, "table");
                    return dt;
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }

        }
        //执行查询语句返回datareader，使用后要注意close
        //这个函数在AccessPageUtils中使用，执行其它查询时最好不要用
        public  OleDbDataReader ExecuteReader(string sql)
        {
            //Debug.WriteLine(sql);
            OleDbConnection connection = new OleDbConnection(connectionString);
            OleDbCommand cmd = new OleDbCommand(sql, connection);
            try
            {
                connection.Open();
                return  cmd.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception e)
            {
                connection.Close();
                throw e;
            }
        }
        
    }
}