100分求C#下好用的Access DBHelper!

Question

100分求C#下好用的Access DBHelper!

Answer 1

using System;    
using System.Text;    
using System.Collections;    
using System.Collections.Specialized;    
using System.Data;    
using System.Data.OleDb;    
using System.Configuration;    
   
   
   
namespace NMJU.Web.DBUtility    
{    /// <summary>    
    /// 数据访问抽象基础类(ACCESS)    
    /// Copyright (C) 2006-2007 NMJU.NET    
    /// All rights reserved    
    /// </summary>    
    public abstract class DbHelperACE    
    {    
        //数据库连接字符串(web.config来配置)    
        //public static string connectionString = ConfigurationManager.AppSettings["ConnectionString"];    
        // public static string connectionString = System.Web.HttpContext.Current.Server.MapPath(ConfigurationManager.AppSettings["AccessConnectionString"]);    
        public static string connectionString = ConfigurationManager.AppSettings["AccessConnectionString"];    
        public DbHelperACE()    
        {    
        }           #region 公用方法    
   
        public static int GetMaxID(string FieldName, string TableName)    
        {    
            string strsql = "select max(" + FieldName + ")+1 from " + TableName;    
            object obj = DbHelperACE.GetSingle(strsql);    
            if (obj == null)    
            {    
                return 1;    
            }    
            else   
            {    
                return int.Parse(obj.ToString());    
            }    
        }    
        public static bool Exists(string strSql)    
        {    
            object obj = DbHelperACE.GetSingle(strSql);    
            int cmdresult;    
            if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))    
            {    
                cmdresult = 0;    
            }    
            else   
            {    
                cmdresult = int.Parse(obj.ToString());    
            }    
            if (cmdresult == 0)    
            {    
                return false;    
            }    
            else   
            {    
                return true;    
            }    
        }    
        public static bool Exists(string strSql, params OleDbParameter[] cmdParms)    
        {    
            object obj = DbHelperACE.GetSingle(strSql, cmdParms);    
            int cmdresult;    
            if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))    
            {    
                cmdresult = 0;    
            }    
            else   
            {    
                cmdresult = int.Parse(obj.ToString());    
            }    
            if (cmdresult == 0)    
            {    
                return false;    
            }    
            else   
            {    
                return true;    
            }    
        }   
        #endregion           #region 执行简单SQL语句    
   
        /// <summary>    
        /// 执行SQL语句，返回影响的记录数    
        /// </summary>    
        /// <param name="SQLString">SQL语句</param>    
        /// <returns>影响的记录数</returns>    
        public static int ExecuteSql(string SQLString)    
        {    
            using (OleDbConnection connection = new OleDbConnection(connectionString))    
            {    
                using (OleDbCommand cmd = new OleDbCommand(SQLString, connection))    
                {    
                    try   
                    {    
                        connection.Open();    
                        int rows = cmd.ExecuteNonQuery();    
                        return rows;    
                    }    
                    catch (System.Data.OleDb.OleDbException E)    
                    {    
                        connection.Close();    
                        throw new Exception(E.Message);    
                    }    
                }    
            }    
        }    
   
        /// <summary>    
        /// 执行SQL语句，设置命令的执行等待时间    
        /// </summary>    
        /// <param name="SQLString"></param>    
        /// <param name="Times"></param>    
        /// <returns></returns>    
        public static int ExecuteSqlByTime(string SQLString, int Times)    
        {    
            using (OleDbConnection connection = new OleDbConnection(connectionString))    
            {    
                using (OleDbCommand cmd = new OleDbCommand(SQLString, connection))    
                {    
                    try   
                    {    
                        connection.Open();    
                        cmd.CommandTimeout = Times;    
                        int rows = cmd.ExecuteNonQuery();    
                        return rows;    
                    }    
                    catch (System.Data.OleDb.OleDbException E)    
                    {    
                        connection.Close();    
                        throw new Exception(E.Message);    
                    }    
                }    
            }    
        }    
   
        /// <summary>    
        /// 执行多条SQL语句，实现数据库事务。    
        /// </summary>    
        /// <param name="SQLStringList">多条SQL语句</param>         
        public static void ExecuteSqlTran(ArrayList SQLStringList)    
        {    
            using (OleDbConnection conn = new OleDbConnection(connectionString))    
            {    
                conn.Open();    
                OleDbCommand cmd = new OleDbCommand();    
                cmd.Connection = conn;    
                OleDbTransaction tx = conn.BeginTransaction();    
                cmd.Transaction = tx;    
                try   
                {    
                    for (int n = 0; n < SQLStringList.Count; n++)    
                    {    
                        string strsql = SQLStringList[n].ToString();    
                        if (strsql.Trim().Length > 1)    
                        {    
                            cmd.CommandText = strsql;    
                            cmd.ExecuteNonQuery();    
                        }    
                    }    
                    tx.Commit();    
                }    
                catch (System.Data.OleDb.OleDbException E)    
                {    
                    tx.Rollback();    
                    throw new Exception(E.Message);    
                }    
            }    
        }    
   
        /// <summary>    
        /// 向数据库里插入图像格式的字段(和上面情况类似的另一种实例)    
        /// </summary>    
        /// <param name="strSQL">SQL语句</param>    
        /// <param name="fs">图像字节,数据库的字段类型为image的情况</param>    
        /// <returns>影响的记录数</returns>    
        public static int ExecuteSqlInsertImg(string strSQL, byte[] fs)    
        {    
            using (OleDbConnection connection = new OleDbConnection(connectionString))    
            {    
                OleDbCommand cmd = new OleDbCommand(strSQL, connection);    
                System.Data.OleDb.OleDbParameter myParameter = new System.Data.OleDb.OleDbParameter("@fs", SqlDbType.Image);    
                myParameter.Value = fs;    
                cmd.Parameters.Add(myParameter);    
                try   
                {    
                    connection.Open();    
                    int rows = cmd.ExecuteNonQuery();    
                    return rows;    
                }    
                catch (System.Data.OleDb.OleDbException E)    
                {    
                    throw new Exception(E.Message);    
                }    
                finally   
                {    
                    cmd.Dispose();    
                    connection.Close();    
                }    
            }    
        }    
   
        /// <summary>    
        /// 执行一条计算查询结果语句，返回查询结果（object）。    
        /// </summary>    
        /// <param name="SQLString">计算查询结果语句</param>    
        /// <returns>查询结果（object）</returns>    
        public static object GetSingle(string SQLString)    
        {    
            using (OleDbConnection connection = new OleDbConnection(connectionString))    
            {    
                using (OleDbCommand cmd = new OleDbCommand(SQLString, connection))    
                {    
                    try   
                    {    
                        connection.Open();    
                        object obj = cmd.ExecuteScalar();    
                        if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))    
                        {    
                            return null;    
                        }    
                        else   
                        {    
                            return obj;    
                        }    
                    }    
                    catch (System.Data.OleDb.OleDbException e)    
                    {    
                        connection.Close();    
                        throw new Exception(e.Message);    
                    }    
                }    
            }    
        }    
  

Answer 2

using System;
using System.Data;
using System.Data.OleDb;
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;namespace AccessDBHelper
{
    /// <summary>
    /// 封装Access数据库的常用操作。
    /// </summary>
    public class DBHelper
    {
        private string connectionString = null;        private OleDbConnection connection = null;
        /// <summary>
        /// 返回当前的数据库连接对象OleDbConnection。
        /// </summary>
        public OleDbConnection Connection
        {
            get { return connection; }
        }        /// <summary>
        /// 创建一个OleDbCommand对象实例
        /// </summary>
        /// <param name="commandText">SQL命令</param>
        /// <param name="connection">数据库连接对象实例OleDbConnection</param>
        /// <param name="oleDbParameters">可选参数</param>
        /// <returns></returns>
        private OleDbCommand CreateCommand(string commandText, OleDbConnection connection,
            params System.Data.OleDb.OleDbParameter[] oleDbParameters)
        {
            if (connection == null)
                connection = new OleDbConnection(connectionString);
            if (connection.State == ConnectionState.Closed)
                connection.Open();            OleDbCommand comm = new OleDbCommand(commandText, connection);
            if (oleDbParameters != null)
            {
                foreach (OleDbParameter parm in oleDbParameters)
                {
                    comm.Parameters.Add(parm);
                }
            }
            return comm;
        }        /// <summary>
        /// 创建一个OleDbParameter参数对象实例
        /// </summary>
        /// <param name="parmname">参数名称</param>
        /// <param name="parmvalue">参数值</param>
        /// <returns></returns>
        public OleDbParameter MakeParm(string parmname, object parmvalue)
        {
            return new OleDbParameter(parmname, parmvalue);
        }        /// <summary>
        /// 执行 SQL INSERT、DELETE、UPDATE 和 SET 语句等命令。
        /// </summary>
        /// <param name="commandText">SQL命令</param>
        /// <param name="oleDbParameters">可选参数</param>
        /// <returns></returns>
        public int ExecuteNonQuery(string commandText, params System.Data.OleDb.OleDbParameter[] oleDbParameters)
        {
            OleDbCommand comm = CreateCommand(commandText, connection, oleDbParameters);
            return comm.ExecuteNonQuery();
        }        /// <summary>
        /// 从数据库中检索单个值(例如一个聚合值)。
        /// </summary>
        /// <param name="commandText"></param>
        /// <param name="oleDbParameters"></param>
        /// <returns></returns>
        public object ExecuteScalar(string commandText, params System.Data.OleDb.OleDbParameter[] oleDbParameters)
        {
            OleDbCommand comm = CreateCommand(commandText, connection, oleDbParameters);
            return comm.ExecuteScalar();
        }        /// <summary>
        /// 提供读取数据行的方法。
        /// </summary>
        /// <param name="commandText">SQL命令</param>
        /// <param name="oleDbParameters">可选参数</param>
        /// <returns>OleDbDataReader</returns>
        public OleDbDataReader ExecuteDataReader(string commandText, params System.Data.OleDb.OleDbParameter[] oleDbParameters)
        {
            OleDbCommand comm = CreateCommand(commandText, connection, oleDbParameters);
            return comm.ExecuteReader();
        }        /// <summary>
        /// 表示一组数据命令和一个数据库连接，它们用于填充 DataSet 和更新数据源。
        /// </summary>
        /// <param name="commandText">SQL命令</param>
        /// <param name="oleDbParameters">可选参数</param>
        /// <returns></returns>
        public OleDbDataAdapter ExecuteDataAdapter(string commandText, params System.Data.OleDb.OleDbParameter[] oleDbParameters)
        {
            OleDbCommand comm = CreateCommand(commandText, connection, oleDbParameters);
            OleDbDataAdapter da = new OleDbDataAdapter(comm);
            OleDbCommandBuilder cb = new OleDbCommandBuilder(da);
            return da;
        }        /// <summary>
        /// 返回一个DataSet数据集。
        /// </summary>
        /// <param name="commandText">SQL命令</param>
        /// <param name="oleDbParameters">可选参数</param>
        /// <returns>DataSet</returns>
        public DataSet ExecuteDataSet(string commandText, params OleDbParameter[] oleDbParameters)
        {
            DataSet ds = new DataSet();
            OleDbCommand comm = CreateCommand(commandText, connection, oleDbParameters);
            OleDbDataAdapter da = new OleDbDataAdapter(comm);
            da.Fill(ds);
            return ds;
        }
    }
}

Answer 3

去把petshop里的那个sqlhelper随便替换下字符串得了。

Answer 4

下载看看吧，整个项目都在！http://download.csdn.net/source/3407167

Answer 5

好多啊，我的咋就那么少呢，而且一直用呢，是深度不够吗public class sqlhelper
{
    /// <summary>
    /// 与数据库建立连接
    /// </summary>
    /// <returns></returns>
    public static OleDbConnection Createconn()
    {
        OleDbConnection conn = new OleDbConnection(ConfigurationManager.ConnectionStrings["conn"].ToString());
        return conn;
    }
    /// <summary>
    /// 执行带参数的增删改SQL语句或存储过程
    /// </summary>
    /// <param name="cmdtxt">增删改SQL语句或存储过程</param>
    /// <param name="paras">参数集合</param>
    /// <param name="ct">命令类型</param>
    /// <returns></returns>
    public static int ExecuteQuery(string cmdtxt, OleDbParameter[] paras, CommandType ct)
    {
        int rows;
        using (OleDbConnection conn = Createconn())
        {
            using (OleDbCommand cmd = new OleDbCommand(cmdtxt, conn))
            {
                conn.Open();
                cmd.CommandType = ct;
                cmd.Parameters.AddRange(paras);
                rows = cmd.ExecuteNonQuery();
            }
        }
        return rows;
    }
    /// <summary>
    /// 执行不带参数的增删改SQL语句
    /// </summary>
    /// <param name="sql"></param>
    /// <returns></returns>
    public static int ExecuteQuery(string sql)
    {
        int rows;
        using (OleDbConnection conn = Createconn())
        {
            using (OleDbCommand cmd = new OleDbCommand(sql, conn))
            {
                conn.Open();
                rows = cmd.ExecuteNonQuery();
            }
        }
        return rows;
    }
    /// <summary>
    /// 有参数返回执行结果第一行第一列的值
    /// </summary>
    /// <param name="cmdtxt">SQL语句或存储过程</param>
    /// <param name="paras">参数集合</param>
    /// <param name="ct">命令类型</param>
    /// <returns></returns>
    public static object ExecuteScalar(string cmdtxt, OleDbParameter[] paras, CommandType ct)
    {
        object obj;
        using (OleDbConnection conn = Createconn())
        {
            using (OleDbCommand cmd = new OleDbCommand(cmdtxt, conn))
            {
                conn.Open();
                cmd.CommandType = ct;
                cmd.Parameters.AddRange(paras);
                obj = cmd.ExecuteScalar();
                if (obj != null)
                {
                    return obj;
                }
                else
                {
                    return null;
                }
            }
        }
    }
    /// <summary>
    /// 没有参数的返回执行结果第一行第一列的值
    /// </summary>
    /// <param name="sql">sql语句</param>
    /// <returns></returns>
    public static object ExecuteScalar(string sql)
    {
        object obj;
        using (OleDbConnection conn = Createconn())
        {
            using (OleDbCommand cmd = new OleDbCommand(sql, conn))
            {
                conn.Open();
                obj = cmd.ExecuteScalar();
                if (obj != null)
                {
                    return obj;
                }
                else
                {
                    return null;
                }
            }
        }
    }
    /// <summary>
    /// 有参数的返回DataTable对象
    /// </summary>
    /// <param name="cmdtxt">SQL语句或存储过程名称</param>
    /// <param name="paras">参数</param>
    /// <param name="ct">命令类型</param>
    /// <returns></returns>
    public static DataTable GetTable(string cmdtxt, OleDbParameter[] paras, CommandType ct)
    {
        DataTable dt = new DataTable();
        using (OleDbConnection conn = Createconn())
        {
            using (OleDbCommand cmd = new OleDbCommand(cmdtxt, conn))
            {
                conn.Open();
                cmd.CommandType = ct;
                cmd.Parameters.AddRange(paras);
                OleDbDataAdapter da = new OleDbDataAdapter(cmd);
                da.Fill(dt);
            }
        }
        return dt;
    }
    /// <summary>
    /// 没有参数的返回DataTable对象
    /// </summary>
    /// <param name="sql"></param>
    /// <returns></returns>
    public static DataTable GetTable(string sql)
    {
        DataTable dt = new DataTable();
        using (OleDbConnection conn = Createconn())
        {
            using (OleDbCommand cmd = new OleDbCommand(sql, conn))
            {
                conn.Open();
                OleDbDataAdapter da = new OleDbDataAdapter(cmd);
                da.Fill(dt);
            }
        }
        return dt;
    }
    /// <summary>
    /// 加密对象
    /// </summary>
    /// <param name="pToEncrypt"></param>
    /// <returns></returns>
    public static string Encrypt(string pToEncrypt)
    {
        DESCryptoServiceProvider des = new DESCryptoServiceProvider();　//把字符串放到byte数组中        byte[] inputByteArray = Encoding.Default.GetBytes(pToEncrypt);
        //byte[]　inputByteArray=Encoding.Unicode.GetBytes(pToEncrypt);        des.Key = ASCIIEncoding.ASCII.GetBytes("alsges12");　//建立加密对象的密钥和偏移量
        des.IV = ASCIIEncoding.ASCII.GetBytes("alsges12");　 //原文使用ASCIIEncoding.ASCII方法的GetBytes方法
        MemoryStream ms = new MemoryStream();　　 //使得输入密码必须输入英文文本
        CryptoStream cs = new CryptoStream(ms, des.CreateEncryptor(), CryptoStreamMode.Write);        cs.Write(inputByteArray, 0, inputByteArray.Length);
        cs.FlushFinalBlock();        StringBuilder ret = new StringBuilder();
        foreach (byte b in ms.ToArray())
        {
            ret.AppendFormat("{0:X2}", b);
        }
        ret.ToString();
        return ret.ToString();
    }    /// <summary>
    /// 解密对象
    /// </summary>
    /// <param name="pToDecrypt"></param>
    /// <returns></returns>
    public static string Decrypt(string pToDecrypt)
    {
        DESCryptoServiceProvider des = new DESCryptoServiceProvider();        byte[] inputByteArray = new byte[pToDecrypt.Length / 2];
        for (int x = 0; x < pToDecrypt.Length / 2; x++)
        {
            int i = (Convert.ToInt32(pToDecrypt.Substring(x * 2, 2), 16));
            inputByteArray[x] = (byte)i;
        }        des.Key = ASCIIEncoding.ASCII.GetBytes("alsges12");　//建立加密对象的密钥和偏移量，此值重要，不能修改
        des.IV = ASCIIEncoding.ASCII.GetBytes("alsges12");
        MemoryStream ms = new MemoryStream();
        CryptoStream cs = new CryptoStream(ms, des.CreateDecryptor(), CryptoStreamMode.Write);        cs.Write(inputByteArray, 0, inputByteArray.Length);
        cs.FlushFinalBlock();        StringBuilder ret = new StringBuilder();　//建立StringBuild对象，CreateDecrypt使用的是流对象，必须把解密后的文本变成流对象        return System.Text.Encoding.Default.GetString(ms.ToArray());
    }
    ///   <summary> 
    ///   将指定字符串按指定长度进行剪切， 
    ///   </summary> 
    ///   <param   name= "oldStr "> 需要截断的字符串 </param> 
    ///   <param   name= "maxLength "> 字符串的最大长度 </param> 
    ///   <param   name= "endWith "> 超过长度的后缀 </param> 
    ///   <returns> 如果超过长度，返回截断后的新字符串加上后缀，否则，返回原字符串 </returns> 
    public static string StringTruncat(string oldStr, int maxLength, string endWith)
    {
        if (string.IsNullOrEmpty(oldStr))
            //   throw   new   NullReferenceException( "原字符串不能为空 "); 
            return oldStr + endWith;
        if (maxLength < 1)
            throw new Exception("返回的字符串长度必须大于[0] ");
        if (oldStr.Length > maxLength)
        {
            string strTmp = oldStr.Substring(0, maxLength);
            if (string.IsNullOrEmpty(endWith))
                return strTmp;
            else
                return strTmp + endWith;
        }
        return oldStr;
    }    public static bool IsNumber(string s)
    {
        return !String.IsNullOrEmpty(s) && Regex.IsMatch(s, "^[-]?\\d+$");
    }
    #region 分解字符串为数组
    /// <summary> 
    /// 字符串分函数 
    /// </summary> 
    /// <param name="str">要分解的字符串</param> 
    /// <param name="splitstr">分割符,可以为string类型</param> 
    /// <returns>字符数组</returns> 
    public static string[] splitstr(string str, string splitstr)
    {
        if (splitstr != "")
        {
            System.Collections.ArrayList c = new System.Collections.ArrayList();
            while (true)
            {
                int thissplitindex = str.IndexOf(splitstr);
                if (thissplitindex >= 0)
                {
                    c.Add(str.Substring(0, thissplitindex));
                    str = str.Substring(thissplitindex + splitstr.Length);
                }
                else
                {
                    c.Add(str);
                    break;
                }
            }
            string[] d = new string[c.Count];
            for (int i = 0; i < c.Count; i++)
            {
                d[i] = c[i].ToString();
            }
            return d;
        }
        else
        {
            return new string[] { str };
        }
    }
    #endregion    public static string EncryptPassword(string str)
    {
        return System.Web.Security.FormsAuthentication.HashPasswordForStoringInConfigFile(str, "MD5");
    }
}

Answer 6

你把这个项目里的COPY出来自己用就行了
传送门或者参考这个传说中万能的