C#數(shù)據(jù)庫(kù)操作類(lèi)AccessHelper實(shí)例

更新時(shí)間：2014年10月14日 15:23:11 投稿：shichen2014

這篇文章主要介紹了C#數(shù)據(jù)庫(kù)操作類(lèi)AccessHelper實(shí)例,可實(shí)現(xiàn)針對(duì)access數(shù)據(jù)庫(kù)的各種常見(jiàn)操作,非常具有實(shí)用價(jià)值,需要的朋友可以參考下

本文實(shí)例講述了C#數(shù)據(jù)庫(kù)操作類(lèi)AccessHelper。分享給大家供大家參考。

具體實(shí)現(xiàn)方法如下：

復(fù)制代碼代碼如下:

using System;
using System.Data;
using System.Configuration;
using System.Data.OleDb;
using ahwildlife.Utils;

/// <summary>
/// AccessHelper 的摘要說(shuō)明
/// </summary>
public class AccessHelper
{
    #region 變量
    protected static OleDbConnection conn = new OleDbConnection();
    protected static OleDbCommand comm = new OleDbCommand();
    protected static string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=ahwildlife.mdb;Persist Security Info=False;Jet OLEDB:Database Password=sa;";
    #endregion

    #region 構(gòu)造函數(shù)
    /// <summary>
    /// 構(gòu)造函數(shù)
    /// </summary>
    public AccessHelper()
    {

}
#endregion

    #region 打開(kāi)數(shù)據(jù)庫(kù)
    /// <summary>
    /// 打開(kāi)數(shù)據(jù)庫(kù)
    /// </summary>
    private static void openConnection()
    {
        if (conn.State == ConnectionState.Closed)
        {
            conn.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=ahwildlife.mdb;Persist Security Info=False;Jet OLEDB:Database Password=sa;";
            comm.Connection = conn;
            try
            {
                conn.Open();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }
    }
    #endregion

    #region 關(guān)閉數(shù)據(jù)庫(kù)
    /// <summary>
    /// 關(guān)閉數(shù)據(jù)庫(kù)
    /// </summary>
    private static void closeConnection()
    {
        if (conn.State == ConnectionState.Open)
        {
            conn.Close();
            conn.Dispose();
            comm.Dispose();
        }
    }
    #endregion

    #region 執(zhí)行sql語(yǔ)句
    /// <summary>
    /// 執(zhí)行sql語(yǔ)句
    /// </summary>
    public static void ExecuteSql(string sqlstr)
    {
        try
        {
            openConnection();
            comm.CommandType = CommandType.Text;
            comm.CommandText = sqlstr;
            comm.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }
        finally
        {
            closeConnection();
        }
    }
    #endregion

    #region 返回指定sql語(yǔ)句的OleDbDataReader對(duì)象,使用時(shí)請(qǐng)注意關(guān)閉這個(gè)對(duì)象。
    /// <summary>
    /// 返回指定sql語(yǔ)句的OleDbDataReader對(duì)象,使用時(shí)請(qǐng)注意關(guān)閉這個(gè)對(duì)象。
    /// </summary>
    public static OleDbDataReader DataReader(string sqlstr)
    {
        OleDbDataReader dr = null;
        try
        {
            openConnection();
            comm.CommandText = sqlstr;
            comm.CommandType = CommandType.Text;

            dr = comm.ExecuteReader(CommandBehavior.CloseConnection);
        }
        catch
        {
            try
            {
                dr.Close();
                closeConnection();
            }
            catch { }
        }
        return dr;
    }
    #endregion

    #region 返回指定sql語(yǔ)句的OleDbDataReader對(duì)象,使用時(shí)請(qǐng)注意關(guān)閉
    /// <summary>
    /// 返回指定sql語(yǔ)句的OleDbDataReader對(duì)象,使用時(shí)請(qǐng)注意關(guān)閉
    /// </summary>
    public static void DataReader(string sqlstr, ref OleDbDataReader dr)
    {
        try
        {
            openConnection();
            comm.CommandText = sqlstr;
            comm.CommandType = CommandType.Text;
            dr = comm.ExecuteReader(CommandBehavior.CloseConnection);
        }
        catch
        {
            try
            {
                if (dr != null && !dr.IsClosed)
                    dr.Close();
            }
            catch
            {
            }
            finally
            {
                closeConnection();
            }
        }
    }
    #endregion

    #region 返回指定sql語(yǔ)句的DataSet
    /// <summary>
    /// 返回指定sql語(yǔ)句的DataSet
    /// </summary>
    /// <param name="sqlstr"></param>
    /// <returns></returns>
    public static DataSet DataSet(string sqlstr)
    {
        DataSet ds = new DataSet();
        OleDbDataAdapter da = new OleDbDataAdapter();
        try
        {
            openConnection();
            comm.CommandType = CommandType.Text;
            comm.CommandText = sqlstr;
            da.SelectCommand = comm;
            da.Fill(ds);

        }
        catch (Exception e)
        {
            throw new Exception(e.Message);
        }
        finally
        {
            closeConnection();
        }
        return ds;
    }
    #endregion

    #region 返回指定sql語(yǔ)句的DataSet
    /// <summary>
    /// 返回指定sql語(yǔ)句的DataSet
    /// </summary>
    /// <param name="sqlstr"></param>
    /// <param name="ds"></param>
    public static void DataSet(string sqlstr, ref DataSet ds)
    {
        OleDbDataAdapter da = new OleDbDataAdapter();
        try
        {
            openConnection();
            comm.CommandType = CommandType.Text;
            comm.CommandText = sqlstr;
            da.SelectCommand = comm;
            da.Fill(ds);
        }
        catch (Exception e)
        {
            throw new Exception(e.Message);
        }
        finally
        {
            closeConnection();
        }
    }
    #endregion

    #region 返回指定sql語(yǔ)句的DataTable
    /// <summary>
    /// 返回指定sql語(yǔ)句的DataTable
    /// </summary>
    /// <param name="sqlstr"></param>
    /// <returns></returns>
    public static DataTable DataTable(string sqlstr)
    {
        DataTable dt = Common.GetDataTableCache(sqlstr);//讀緩存
        if (dt != null)
        {
            return dt.Copy();
        }
        else
        {
            dt = new DataTable();
            OleDbDataAdapter da = new OleDbDataAdapter();
            try
            {
                using (OleDbConnection conn = new OleDbConnection())
                {
                    conn.ConnectionString = connectionString;
                    conn.Open();
                    using (OleDbCommand comm = new OleDbCommand())
                    {
                        comm.Connection = conn;
                        comm.CommandType = CommandType.Text;
                        comm.CommandText = sqlstr;
                        da.SelectCommand = comm;
                        da.Fill(dt);
                    }
                }
            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                closeConnection();
            }
            Common.InsertDataTableCache(sqlstr, dt);//添加緩存
            return dt.Copy();
        }
    }
    #endregion

    #region 返回指定sql語(yǔ)句的DataTable
    /// <summary>
    /// 返回指定sql語(yǔ)句的DataTable
    /// </summary>
    public static void DataTable(string sqlstr, ref DataTable dt)
    {
        OleDbDataAdapter da = new OleDbDataAdapter();
        try
        {
            openConnection();
            comm.CommandType = CommandType.Text;
            comm.CommandText = sqlstr;
            da.SelectCommand = comm;
            da.Fill(dt);
        }
        catch (Exception e)
        {
            throw new Exception(e.Message);
        }
        finally
        {
            closeConnection();
        }
    }
    #endregion

    #region 返回指定sql語(yǔ)句的DataView
    /// <summary>
    /// 返回指定sql語(yǔ)句的DataView
    /// </summary>
    /// <param name="sqlstr"></param>
    /// <returns></returns>
    public static DataView DataView(string sqlstr)
    {
        OleDbDataAdapter da = new OleDbDataAdapter();
        DataView dv = new DataView();
        DataSet ds = new DataSet();
        try
        {
            openConnection();
            comm.CommandType = CommandType.Text;
            comm.CommandText = sqlstr;
            da.SelectCommand = comm;
            da.Fill(ds);
            dv = ds.Tables[0].DefaultView;
        }
        catch (Exception e)
        {
            throw new Exception(e.Message);
        }
        finally
        {
            closeConnection();
        }
        return dv;
    }
    #endregion
}