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#程序设计有所帮助。