数据工厂模式，多种数据库选择 web.config 写法及相关操作

 

web.config

<?xml version= " 1.0 " encoding= " utf-8 "?>

<!--
  有关如何配置 ASP.NET 应用程序的详细消息，请访问
  http: // go.microsoft.com/fwlink/?LinkId=169433
  -->

<configuration>
  
  <appSettings>
    
    <!--
     Oracle数据库写法
  -->
    <add key= " DbHelperProvider " value= " System.Data.OracleClient "/>
    <add key= " DbHelperConnectionString " value= " Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521)))(CONNECT_DATA=(SID=guandb)(SERVER=DEDICATED)));User ID=GUANUSER1;Password=k2763826;Unicode=True "/>

    <!--
     Sqlserver数据库写法
  -->
    <add key= " DbHelperProvider2 " value= " System.Data.SqlClient "/>
    <add key= " DbHelperConnectionString2 " value= " Data Source=localhost;Initial Catalog=guestbook;User ID=sa;password=123456; "/>

    <!--
     Access数据库写法
  -->
    <add key= " DbHelperProvider3 " value= " System.Data.OleDb "/>
    <add key= " DbHelperConnectionString3 " value= " Provider=Microsoft.Jet.OleDb.4.0;Data Source= "/>
    <add key= " DbPath3 " value= " ~/App_Data/guestbook.mdb "/>
    
  </appSettings>
  
    <system.web>
        <compilation debug= " true " targetFramework= " 4.0 " />
    </system.web>

</configuration>

 

注意 oracle 的写法  User ID=GUANUSER1，不能写 User ID=system，否则写sql语句的时候就得这样写了 "select * from 用户名.表名"。 GUANUSER1 为用 system登录后，新建的用户，程序中用的表都在此用户下。

 DBUtility 类 DBHelper.cs

 

using System;
using System.Data;
using System.Web;
using System.Collections;
using System.Collections.Generic;
using System.Data.Common;
using System.Configuration;

namespace DBUtility
{
     public  class DbHelper
    {
         private  static  string dbProviderName = ConfigurationManager.AppSettings[ " DbHelperProvider "];
         public  static  string dbConnectionString = ConfigurationManager.AppSettings[ " DbHelperConnectionString "].ToString();
         // public static string dbConnectionString = ConfigurationManager.AppSettings["DbHelperConnectionString"].ToString() + System.Web.HttpContext.Current.Server.MapPath(ConfigurationManager.AppSettings["DbPath"]);
         private DbConnection connection;
         public DbHelper()
        {
             this.connection = CreateConnection(DbHelper.dbConnectionString);
        }
         public DbHelper( string connectionString)
        {
             this.connection = CreateConnection(connectionString);
        }
         public  static DbConnection CreateConnection()
        {
            DbProviderFactory dbfactory = DbProviderFactories.GetFactory(DbHelper.dbProviderName);
            DbConnection dbconn = dbfactory.CreateConnection();
            dbconn.ConnectionString = DbHelper.dbConnectionString;
             return dbconn;
        }
         public  static DbConnection CreateConnection( string connectionString)
        {
            DbProviderFactory dbfactory = DbProviderFactories.GetFactory(DbHelper.dbProviderName);
            DbConnection dbconn = dbfactory.CreateConnection();
            dbconn.ConnectionString = connectionString;
             return dbconn;
        }
         ///   <summary>
         ///  执行的存储过程
         ///   </summary>
         ///   <param name="storedProcedure"></param>
         ///   <returns></returns>
         public DbCommand GetStoredProcCommond( string storedProcedure)
        {
            DbCommand dbCommand = connection.CreateCommand();
            dbCommand.CommandText = storedProcedure;
            dbCommand.CommandType = CommandType.StoredProcedure;
             return dbCommand;
        }
         ///   <summary>
         ///  执行的sql语句
         ///   </summary>
         ///   <param name="sqlQuery"></param>
         ///   <returns></returns>
         public DbCommand GetSqlStringCommond( string sqlQuery)
        {
            DbCommand dbCommand = connection.CreateCommand();
            dbCommand.CommandText = sqlQuery;
            dbCommand.CommandType = CommandType.Text;
             return dbCommand;
        }

         // 增加参数#region 增加参数
         public  void AddParameterCollection(DbCommand cmd, DbParameterCollection dbParameterCollection)
        {
             foreach (DbParameter dbParameter  in dbParameterCollection)
            {
                cmd.Parameters.Add(dbParameter);
            }
        }
         ///   <summary>
         ///  增加输出参数 适用于存储过程
         ///   </summary>
         ///   <param name="cmd"> cmd对象 </param>
         ///   <param name="parameterName"> 参数名 </param>
         ///   <param name="dbType"> 参数类型 </param>
         ///   <param name="size"> 参数大小 </param>
         public  void AddOutParameter(DbCommand cmd,  string parameterName, DbType dbType,  int size)
        {
            DbParameter dbParameter = cmd.CreateParameter();
            dbParameter.DbType = dbType;
            dbParameter.ParameterName = parameterName;
            dbParameter.Size = size;
            dbParameter.Direction = ParameterDirection.Output;
            cmd.Parameters.Add(dbParameter);
        }
         ///   <summary>
         ///  增加参数列表
         ///   </summary>
         ///   <param name="cmd"> CMD对象 </param>
         ///   <param name="parameterName"> 参数名 </param>
         ///   <param name="dbType"> 参数类型 </param>
         ///   <param name="value"> 参数值 </param>
         public  void AddInParameter(DbCommand cmd,  string parameterName, DbType dbType,  object value)
        {
            DbParameter dbParameter = cmd.CreateParameter();
            dbParameter.DbType = dbType;
            dbParameter.ParameterName = parameterName;
            dbParameter.Value = value;
            dbParameter.Direction = ParameterDirection.Input;
            cmd.Parameters.Add(dbParameter);
        }
         ///   <summary>
         ///  增加返回参数 适用于存储过程
         ///   </summary>
         ///   <param name="cmd"></param>
         ///   <param name="parameterName"></param>
         ///   <param name="dbType"></param>
         public  void AddReturnParameter(DbCommand cmd,  string parameterName, DbType dbType)
        {
            DbParameter dbParameter = cmd.CreateParameter();
            dbParameter.DbType = dbType;
            dbParameter.ParameterName = parameterName;
            dbParameter.Direction = ParameterDirection.ReturnValue;
            cmd.Parameters.Add(dbParameter);
        }
         public DbParameter GetParameter(DbCommand cmd,  string parameterName)
        {
             return cmd.Parameters[parameterName];
        }

         ///   <summary>
         ///  执行SQL或者存储过程返回一个dataSet对象
         ///  所需参数cmd对象
         ///   </summary>
         ///   <param name="cmd"> cmd对象 </param>
         ///   <returns> 查询的结果 dataSet对象 </returns>
         public DataSet ExecuteDataSet(DbCommand cmd)
        {
            DbProviderFactory dbfactory = DbProviderFactories.GetFactory(DbHelper.dbProviderName);
            DbDataAdapter dbDataAdapter = dbfactory.CreateDataAdapter();
            dbDataAdapter.SelectCommand = cmd;
            DataSet ds =  new DataSet();
            dbDataAdapter.Fill(ds);
             return ds;
        }

         ///   <summary>
         ///  执行SQL或者存储过程返回一个dataTable对象
         ///  所需参数cmd对象
         ///   </summary>
         ///   <param name="cmd"> cmd对象 </param>
         ///   <returns> 查询的结果 DataReader对象 </returns>
         public DataTable ExecuteDataTable(DbCommand cmd)
        {
            DbProviderFactory dbfactory = DbProviderFactories.GetFactory(DbHelper.dbProviderName);
            DbDataAdapter dbDataAdapter = dbfactory.CreateDataAdapter();
            dbDataAdapter.SelectCommand = cmd;
            DataTable dataTable =  new DataTable();
            dbDataAdapter.Fill(dataTable);
             return dataTable;
        }
         ///   <summary>
         ///  执行sql语句或者存储过程返回一个datareader对象
         ///  所需参数CMD对象
         ///   </summary>
         ///   <param name="cmd"> cmd对象 </param>
         ///   <returns> 查询的结果 DataReader对象 </returns>
         public DbDataReader ExecuteReader(DbCommand cmd)
        {
            cmd.Connection.Open();
            DbDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
             return reader;
        }
         ///   <summary>
         ///  执行一条SQL语句或者存储过程，返回受影响的行数
         ///   </summary>
         ///   <param name="cmd"> cmd对象 </param>
         ///   <returns></returns>
         public  int ExecuteNonQuery(DbCommand cmd)
        {
            cmd.Connection.Open();
             int ret = cmd.ExecuteNonQuery();
            cmd.Connection.Close();
             return ret;
        }
         ///   <summary>
         ///  执行一条SQL语句或者存储过程，返回Object对象
         ///   </summary>
         ///   <param name="cmd"> cmd对象 </param>
         ///   <returns></returns>
         public  object ExecuteScalar(DbCommand cmd)
        {
            cmd.Connection.Open();
             object ret = cmd.ExecuteScalar();
            cmd.Connection.Close();
             return ret;
        }
         // 执行事务#region 执行事务
         public DataSet ExecuteDataSet(DbCommand cmd, Trans t)
        {
            cmd.Connection = t.DbConnection;
            cmd.Transaction = t.DbTrans;
            DbProviderFactory dbfactory = DbProviderFactories.GetFactory(DbHelper.dbProviderName);
            DbDataAdapter dbDataAdapter = dbfactory.CreateDataAdapter();
            dbDataAdapter.SelectCommand = cmd;
            DataSet ds =  new DataSet();
            dbDataAdapter.Fill(ds);
             return ds;
        }

         public DataTable ExecuteDataTable(DbCommand cmd, Trans t)
        {
            cmd.Connection = t.DbConnection;
            cmd.Transaction = t.DbTrans;
            DbProviderFactory dbfactory = DbProviderFactories.GetFactory(DbHelper.dbProviderName);
            DbDataAdapter dbDataAdapter = dbfactory.CreateDataAdapter();
            dbDataAdapter.SelectCommand = cmd;
            DataTable dataTable =  new DataTable();
            dbDataAdapter.Fill(dataTable);
             return dataTable;
        }

         public DbDataReader ExecuteReader(DbCommand cmd, Trans t)
        {
            cmd.Connection.Close();
            cmd.Connection = t.DbConnection;
            cmd.Transaction = t.DbTrans;
            DbDataReader reader = cmd.ExecuteReader();
            DataTable dt =  new DataTable();
             return reader;
        }
         public  int ExecuteNonQuery(DbCommand cmd, Trans t)
        {
            cmd.Connection.Close();
            cmd.Connection = t.DbConnection;
            cmd.Transaction = t.DbTrans;
             int ret = cmd.ExecuteNonQuery();
             return ret;
        }

         public  object ExecuteScalar(DbCommand cmd, Trans t)
        {
            cmd.Connection.Close();
            cmd.Connection = t.DbConnection;
            cmd.Transaction = t.DbTrans;
             object ret = cmd.ExecuteScalar();
             return ret;
        }

    }
     ///   <summary>
     ///  事务执行操作
     ///   </summary>
     public  class Trans : IDisposable
    {
         private DbConnection conn;
         private DbTransaction dbTrans;
         public DbConnection DbConnection
        {
             get {  return  this.conn; }
        }
         public DbTransaction DbTrans
        {
             get {  return  this.dbTrans; }
        }

         public Trans()
        {
            conn = DbHelper.CreateConnection();
            conn.Open();
            dbTrans = conn.BeginTransaction();
        }
         public Trans( string connectionString)
        {
            conn = DbHelper.CreateConnection(connectionString);
            conn.Open();
            dbTrans = conn.BeginTransaction();
        }
         public  void Commit()
        {
            dbTrans.Commit();
             this.Colse();
        }

         public  void RollBack()
        {
            dbTrans.Rollback();
             this.Colse();
        }

         public  void Dispose()
        {
             this.Colse();
        }

         public  void Colse()
        {
             if (conn.State == System.Data.ConnectionState.Open)
            {
                conn.Close();
            }
        }
    }
}

 

 

aspx.CS

 

 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using DBUtility;
namespace WebUI.AdminCenter
{
     public  partial  class ReadInfoList : System.Web.UI.Page
    {
        DbHelper newdb =  new DbHelper();
         protected  void Page_Load( object sender, EventArgs e)
        {
             if (!IsPostBack)
            {
                BindInfoList();
            }

        }

         public  void BindInfoList()
        {
             string sql =  " select * from GtContent ";
            DataSet ds = newdb.ExecuteDataSet(newdb.GetSqlStringCommond(sql));
             this.GridView1.DataSource = ds;
             this.GridView1.DataBind();
        }

    }
}

 

 

 

以上实现不管改变何种数据库， sql语句都不用变功能。

 

 

转载于:https://www.cnblogs.com/tiger8000/archive/2012/01/04/2312134.html