一个仿PetShop的通用DBHelper类

今天参考PetShop的SqlHelper写了个更通用的DBHelper,兼容大部分DB.感觉对于小型开发来说,使用这样的通用类,更换DB会方便.奉上代码:

 

Code using System;using System.Collections.Generic;using System.Text;using System.Data;using System.Data.Common;using System.Configuration;namespace MBDone.Core.DBUtility{    public static class DBHelper    {        private static readonly string connStringName = ConfigurationManager.AppSettings["connStringName"].ToString();        private static readonly string connString = ConfigurationManager.ConnectionStrings[connStringName].ConnectionString;        private static readonly string providerName = ConfigurationManager.ConnectionStrings[connStringName].ProviderName;        public static DbProviderFactory provider = DbProviderFactories.GetFactory(providerName);        #region Public Mothods        public static int ExcuteNonQuery(string cmdText, params DbParameter[] cmdParms)        {            return ExcuteNonQuery(null, CommandType.Text, cmdText, cmdParms);        }        public static int ExcuteNonQuery(CommandType cmdType, string cmdText, params DbParameter[] cmdParms)        {            return ExcuteNonQuery(null, cmdType, cmdText, cmdParms);        }        public static int ExcuteNonQuery(DbTransaction trans, CommandType cmdType, string cmdText, params DbParameter[] cmdParms)        {            using (DbConnection conn = provider.CreateConnection())            {                using (DbCommand cmd = provider.CreateCommand())                {                    PrepareCommand(cmd, conn, trans, cmdType, cmdText, cmdParms);                    return cmd.ExecuteNonQuery();                }            }        }        public static DbDataReader ExecuteReader(string cmdText, params DbParameter[] cmdParms)        {            return ExecuteReader(null, CommandType.Text, cmdText, cmdParms);        }        public static DbDataReader ExecuteReader(CommandType cmdType, string cmdText, params DbParameter[] cmdParms)        {            return ExecuteReader(null, cmdType, cmdText, cmdParms);        }        public static DbDataReader ExecuteReader(DbTransaction trans, CommandType cmdType, string cmdText, params DbParameter[] cmdParms)        {            DbConnection conn = provider.CreateConnection();            DbCommand cmd = provider.CreateCommand();            try            {                PrepareCommand(cmd, conn, trans, cmdType, cmdText, cmdParms);                return cmd.ExecuteReader(CommandBehavior.CloseConnection);            }            catch            {                conn.Close();                cmd.Dispose();                throw;            }        }        public static object ExecuteScalar(string cmdText, params DbParameter[] cmdParms)        {            return ExecuteScalar(null, CommandType.Text, cmdText, cmdParms);        }        public static object ExecuteScalar(CommandType cmdType, string cmdText, params DbParameter[] cmdParms)        {            return ExecuteScalar(null, cmdType, cmdText, cmdParms);        }        public static object ExecuteScalar(DbTransaction trans, CommandType cmdType, string cmdText, params DbParameter[] cmdParms)        {            using (DbConnection conn = provider.CreateConnection())            {                using (DbCommand cmd = provider.CreateCommand())                {                    PrepareCommand(cmd, conn, trans, cmdType, cmdText, cmdParms);                    return cmd.ExecuteScalar();                }            }        }        #endregion        #region Private Methods        private static void PrepareCommand(DbCommand cmd, DbConnection conn, DbTransaction trans, CommandType cmdType, string cmdText, DbParameter[] cmdParms)        {            conn.ConnectionString = connString;            if (conn.State != ConnectionState.Open)                conn.Open();            cmd.Connection = conn;            cmd.CommandText = cmdText;            if (trans != null)                cmd.Transaction = trans;            cmd.CommandType = cmdType;            if (cmdParms != null)            {                foreach (DbParameter parm in cmdParms)                    cmd.Parameters.Add(parm);            }        }        #endregion    }}

 

使用如下:

 

Code string sql1 = "Delete From [User] Where [Id]=@Id";DbParameter param = DBHelper.provider.CreateParameter();param.ParameterName = "@Id";param.Value = 3;DbParameter[] ps = { param };int count = DBHelper.ExcuteNonQuery(sql2, ps);string sql2 = "SELECT TOP 100 [Id],[Name] FROM [User]";DbDataReader dr = DBHelper.ExecuteReader(sql2, null);while (dr.Read()){    string name = dr[1].ToString();}dr.Close();

 

Web.config配置使用哪一种DB:(由上而下分别是Access,SQL server,SQLite)

 

Code <appSettings>    <add key="connStringName" value="defaultConnectionString1"/></appSettings><connectionStrings>    <add name="defaultConnectionString1" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\UserDB.mdb;Jet OLEDB:Database Password=1234567" providerName="System.Data.OleDb"/>    <add name="defaultConnectionString2" connectionString="Data Source=10.152.86.42;Initial Catalog=UserDB;Persist Security Info=False;User ID=sa; pwd = 1234567" providerName="System.Data.SqlClient"/>    <add name="defaultConnectionString3" connectionString="Data Source=|DataDirectory|\UserDB.s3db;Version=3;BinaryGUID=False;" providerName="System.Data.SQLite"/></connectionStrings>

 

注意如果使用SQLite除了引用"System.Data.SQLite.DLL"(在 http://sourceforge.net/projects/sqlite-dotnet2 下载 ADO.NET 2.0 Provider。找到下载包中的 System.Data.SQLite.DLL 拷贝到程序 Bin 目录。)

还必须配置DB Provider:

 

Code <system.data>    <DbProviderFactories>        <remove invariant="System.Data.SQLite"/>        <add name="SQLite Data Provider" invariant="System.Data.SQLite" description=".Net Framework Data Provider for SQLite" type="System.Data.SQLite.SQLiteFactory, System.Data.SQLite"/>    </DbProviderFactories></system.data>

 

否则会报错:

 

Code Unable to find the requested .Net Framework Data Provider.  It may not be installed.

 

注:本文基于WebApp开发而讲述.

转载于:https://www.cnblogs.com/KenBlove/archive/2009/07/01/1514869.html

相关资源：JAVA上百实例源码以及开源项目