access數(shù)據(jù)庫(kù)操作類(lèi)accesshelper

using system;
using system.data;
using system.configuration;
using system.web;
using system.web.security;
using system.web.ui;
using system.web.ui.webcontrols;
using system.web.ui.webcontrols.webparts;
using system.web.ui.htmlcontrols;
using system.data.oledb;
using system.collections;
/// <summary>
/// accehelper 的摘要說(shuō)明
/// </summary>
public static class accesshelper
{
    //數(shù)據(jù)庫(kù)連接字符串
    public static readonly string conn = "provider=microsoft.jet.oledb.4.0;data source=" + httpcontext.current.request.physicalapplicationpath + system.configuration.configurationmanager.connectionstrings["connectionstring"].connectionstring;
    // 用于緩存參數(shù)的hash表
    private static hashtable parmcache = hashtable.synchronized(new hashtable());
    /// <summary>
    ///  給定連接的數(shù)據(jù)庫(kù)用假設(shè)參數(shù)執(zhí)行一個(gè)sql命令（不返回?cái)?shù)據(jù)集）
    /// </summary>
    /// <param name="connectionstring">一個(gè)有效的連接字符串</param>
    /// <param name="commandtext">存儲(chǔ)過(guò)程名稱(chēng)或者sql命令語(yǔ)句</param>
    /// <param name="commandparameters">執(zhí)行命令所用參數(shù)的集合</param>
    /// <returns>執(zhí)行命令所影響的行數(shù)</returns>
    public static int executenonquery(string connectionstring, string cmdtext, params oledbparameter[] commandparameters)
    {
        oledbcommand cmd = new oledbcommand();
        using (oledbconnection conn = new oledbconnection(connectionstring))
        {
            preparecommand(cmd, conn, null, cmdtext, commandparameters);
            int val = cmd.executenonquery();
            cmd.parameters.clear();
            return val;
        }
    }
    /// <summary>
    /// 用現(xiàn)有的數(shù)據(jù)庫(kù)連接執(zhí)行一個(gè)sql命令（不返回?cái)?shù)據(jù)集）
    /// </summary>
    /// <remarks>
    ///舉例: 
    ///  int result = executenonquery(connstring, "publishorders", new oledbparameter("@prodid", 24));
    /// </remarks>
    /// <param name="conn">一個(gè)現(xiàn)有的數(shù)據(jù)庫(kù)連接</param>
    /// <param name="commandtext">存儲(chǔ)過(guò)程名稱(chēng)或者sql命令語(yǔ)句</param>
    /// <param name="commandparameters">執(zhí)行命令所用參數(shù)的集合</param>
    /// <returns>執(zhí)行命令所影響的行數(shù)</returns>
    public static int executenonquery(oledbconnection connection, string cmdtext, params oledbparameter[] commandparameters)
    {
        oledbcommand cmd = new oledbcommand();
        preparecommand(cmd, connection, null, cmdtext, commandparameters);
        int val = cmd.executenonquery();
        cmd.parameters.clear();
        return val;
    }
    /// <summary>
    ///使用現(xiàn)有的sql事務(wù)執(zhí)行一個(gè)sql命令（不返回?cái)?shù)據(jù)集）
    /// </summary>
    /// <remarks>
    ///舉例: 
    ///  int result = executenonquery(trans, "publishorders", new oledbparameter("@prodid", 24));
    /// </remarks>
    /// <param name="trans">一個(gè)現(xiàn)有的事務(wù)</param>
    /// <param name="commandtext">存儲(chǔ)過(guò)程名稱(chēng)或者sql命令語(yǔ)句</param>
    /// <param name="commandparameters">執(zhí)行命令所用參數(shù)的集合</param>
    /// <returns>執(zhí)行命令所影響的行數(shù)</returns>
    public static int executenonquery(oledbtransaction trans, string cmdtext, params oledbparameter[] commandparameters)
    {
        oledbcommand cmd = new oledbcommand();
        preparecommand(cmd, trans.connection, trans, cmdtext, commandparameters);
        int val = cmd.executenonquery();
        cmd.parameters.clear();
        return val;
    }
    /// <summary>
    /// 用執(zhí)行的數(shù)據(jù)庫(kù)連接執(zhí)行一個(gè)返回?cái)?shù)據(jù)集的sql命令
    /// </summary>
    /// <remarks>
    /// 舉例: 
    ///  oledbdatareader r = executereader(connstring, "publishorders", new oledbparameter("@prodid", 24));
    /// </remarks>
    /// <param name="connectionstring">一個(gè)有效的連接字符串</param>
    /// <param name="commandtext">存儲(chǔ)過(guò)程名稱(chēng)或者sql命令語(yǔ)句</param>
    /// <param name="commandparameters">執(zhí)行命令所用參數(shù)的集合</param>
    /// <returns>包含結(jié)果的讀取器</returns>
    public static oledbdatareader executereader(string connectionstring, string cmdtext, params oledbparameter[] commandparameters)
    {
        //創(chuàng)建一個(gè)sqlcommand對(duì)象
        oledbcommand cmd = new oledbcommand();
        //創(chuàng)建一個(gè)sqlconnection對(duì)象
        oledbconnection conn = new oledbconnection(connectionstring);
        //在這里我們用一個(gè)try/catch結(jié)構(gòu)執(zhí)行sql文本命令/存儲(chǔ)過(guò)程，因?yàn)槿绻@個(gè)方法產(chǎn)生一個(gè)異常我們要關(guān)閉連接，因?yàn)闆](méi)有讀取器存在，
        //因此commandbehaviour.closeconnection 就不會(huì)執(zhí)行
        try
        {
            //調(diào)用 preparecommand 方法，對(duì) sqlcommand 對(duì)象設(shè)置參數(shù)
            preparecommand(cmd, conn, null, cmdtext, commandparameters);
            //調(diào)用 sqlcommand  的 executereader 方法
            oledbdatareader reader = cmd.executereader(commandbehavior.closeconnection);
            //清除參數(shù)
            cmd.parameters.clear();
            return reader;
        }
        catch
        {
            //關(guān)閉連接，拋出異常
            conn.close();
            throw;
        }
    }
    /// <summary>
    /// 返回一個(gè)dataset數(shù)據(jù)集
    /// </summary>
    /// <param name="connectionstring">一個(gè)有效的連接字符串</param>
    /// <param name="cmdtext">存儲(chǔ)過(guò)程名稱(chēng)或者sql命令語(yǔ)句</param>
    /// <param name="commandparameters">執(zhí)行命令所用參數(shù)的集合</param>
    /// <returns>包含結(jié)果的數(shù)據(jù)集</returns>
    public static dataset executedataset(string connectionstring, string cmdtext, params oledbparameter[] commandparameters)
    {
        //創(chuàng)建一個(gè)sqlcommand對(duì)象，并對(duì)其進(jìn)行初始化
        oledbcommand cmd = new oledbcommand();
        using (oledbconnection conn = new oledbconnection(connectionstring))
        {
            preparecommand(cmd, conn, null, cmdtext, commandparameters);
            //創(chuàng)建sqldataadapter對(duì)象以及dataset
            oledbdataadapter da = new oledbdataadapter(cmd);
            dataset ds = new dataset();
            try
            {
                //填充ds
                da.fill(ds);
                // 清除cmd的參數(shù)集合
                cmd.parameters.clear();
                //返回ds
                return ds;
            }
            catch
            {
                //關(guān)閉連接，拋出異常
                conn.close();
                throw;
            }
        }
    }
    /// <summary>
    /// 用指定的數(shù)據(jù)庫(kù)連接字符串執(zhí)行一個(gè)命令并返回一個(gè)數(shù)據(jù)集的第一列
    /// </summary>
    /// <remarks>
    ///例如: 
    ///  object obj = executescalar(connstring, "publishorders", new oledbparameter("@prodid", 24));
    /// </remarks>
    ///<param name="connectionstring">一個(gè)有效的連接字符串</param>
    /// <param name="commandtext">存儲(chǔ)過(guò)程名稱(chēng)或者sql命令語(yǔ)句</param>
    /// <param name="commandparameters">執(zhí)行命令所用參數(shù)的集合</param>
    /// <returns>用 convert.to{type}把類(lèi)型轉(zhuǎn)換為想要的 </returns>
    public static object executescalar(string connectionstring, string cmdtext, params oledbparameter[] commandparameters)
    {
        oledbcommand cmd = new oledbcommand();
        using (oledbconnection connection = new oledbconnection(connectionstring))
        {
            preparecommand(cmd, connection, null, cmdtext, commandparameters);
            object val = cmd.executescalar();
            cmd.parameters.clear();
            return val;
        }
    }
    /// <summary>
    /// 用指定的數(shù)據(jù)庫(kù)連接執(zhí)行一個(gè)命令并返回一個(gè)數(shù)據(jù)集的第一列
    /// </summary>
    /// <remarks>
    /// 例如: 
    ///  object obj = executescalar(connstring, "publishorders", new oledbparameter("@prodid", 24));
    /// </remarks>
    /// <param name="conn">一個(gè)存在的數(shù)據(jù)庫(kù)連接</param>
    /// <param name="commandtext">存儲(chǔ)過(guò)程名稱(chēng)或者sql命令語(yǔ)句</param>
    /// <param name="commandparameters">執(zhí)行命令所用參數(shù)的集合</param>
    /// <returns>用 convert.to{type}把類(lèi)型轉(zhuǎn)換為想要的 </returns>
    public static object executescalar(oledbconnection connection, string cmdtext, params oledbparameter[] commandparameters)
    {
        oledbcommand cmd = new oledbcommand();
        preparecommand(cmd, connection, null, cmdtext, commandparameters);
        object val = cmd.executescalar();
        cmd.parameters.clear();
        return val;
    }
    /// <summary>
    /// 將參數(shù)集合添加到緩存
    /// </summary>
    /// <param name="cachekey">添加到緩存的變量</param>
    /// <param name="cmdparms">一個(gè)將要添加到緩存的sql參數(shù)集合</param>
    public static void cacheparameters(string cachekey, params oledbparameter[] commandparameters)
    {
        parmcache[cachekey] = commandparameters;
    }
    /// <summary>
    /// 找回緩存參數(shù)集合
    /// </summary>
    /// <param name="cachekey">用于找回參數(shù)的關(guān)鍵字</param>
    /// <returns>緩存的參數(shù)集合</returns>
    public static oledbparameter[] getcachedparameters(string cachekey)
    {
        oledbparameter[] cachedparms = (oledbparameter[])parmcache[cachekey];
        if (cachedparms == null)
            return null;
        oledbparameter[] clonedparms = new oledbparameter[cachedparms.length];
        for (int i = 0, j = cachedparms.length; i < j; i++)
            clonedparms =(oledbparameter[])((icloneable)cachedparms).clone();
        return clonedparms;
    }
    /// <summary>
    /// 準(zhǔn)備執(zhí)行一個(gè)命令
    /// </summary>
    /// <param name="cmd">sql命令</param>
    /// <param name="conn">sql連接</param>
    /// <param name="trans">sql事務(wù)</param>
    /// <param name="cmdtext">命令文本,例如：select * from products</param>
    /// <param name="cmdparms">執(zhí)行命令的參數(shù)</param>
    private static void preparecommand(oledbcommand cmd, oledbconnection conn, oledbtransaction trans, string cmdtext, oledbparameter[] cmdparms)
    {
        //判斷連接的狀態(tài)。如果是關(guān)閉狀態(tài)，則打開(kāi)
        if (conn.state != connectionstate.open)
            conn.open();
        //cmd屬性賦值
        cmd.connection = conn;
        cmd.commandtext = cmdtext;
        //是否需要用到事務(wù)處理
        if (trans != null)
            cmd.transaction = trans;
        cmd.commandtype = commandtype.text;
        //添加cmd需要的存儲(chǔ)過(guò)程參數(shù)
        if (cmdparms != null)
        {
            foreach (oledbparameter parm in cmdparms)
                cmd.parameters.add(parm);
        }
    }
}