C#数据库操作类AccessHelper实例

本文实例讲述了C#数据库操作类AccessHelper。分享给大家供大家参考。

具体实现方法如下：

using System;

using System.Data;

using System.Configuration;

using System.Data.OleDb;

using ahwildlife.Utils;



/// <summary>

/// AccessHelper 的摘要说明

/// </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 构造函数

    /// <summary>

    /// 构造函数

    /// </summary>

    public AccessHelper()

    {
    }

    #endregion
    #region 打开数据库

    /// <summary>

    /// 打开数据库

    /// </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 关闭数据库

    /// <summary>

    /// 关闭数据库

    /// </summary>

    private static void closeConnection()

    {

        if (conn.State == ConnectionState.Open)

        {

            conn.Close();

            conn.Dispose();

            comm.Dispose();

        }

    }

    #endregion
    #region 执行sql语句

    /// <summary>

    /// 执行sql语句

    /// </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语句的OleDbDataReader对象,使用时请注意关闭这个对象。

    /// <summary>

    /// 返回指定sql语句的OleDbDataReader对象,使用时请注意关闭这个对象。

    /// </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语句的OleDbDataReader对象,使用时请注意关闭

    /// <summary>

    /// 返回指定sql语句的OleDbDataReader对象,使用时请注意关闭

    /// </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语句的DataSet

    /// <summary>

    /// 返回指定sql语句的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语句的DataSet

    /// <summary>

    /// 返回指定sql语句的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语句的DataTable

    /// <summary>

    /// 返回指定sql语句的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语句的DataTable

    /// <summary>

    /// 返回指定sql语句的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语句的DataView

    /// <summary>

    /// 返回指定sql语句的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

}

希望本文所述对大家的C#程序设计有所帮助。