.NET2.0 中通用数据访问对象

一.概要:
     主要有以下参考意见
     1.不要去用什么ORM,那样损失性能.
     2.不要辜负数据库设计者的心血用原始存储过程.
     3.不要走极端,把许多业务逻辑放入存储过程中.应用程序和数据库两者分工明确,逻辑清明,达到最好的性能和可伸缩性.

二.实现:
      要切换数据库时,仅做如下简单工作
      1.web.config中配置
            <add key="DataProviderName" value="System.Data.SqlClient" />
       改为
            <add key="DataProviderName" value="System.Data.OracleClient" />
      2.两个数据库请使用相对应相同的存储过程签名(相同的过程名,参数名,返回类型)

三.代码
CommandProvider.cs

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Common;
using Kcitwm.Framework.Config;

using Kcitwm.Framework.Util;

namespace Kcitwm.Framework.DataProvider
{
    public class CommonProvider
    {
        protected bool disposed = false;
        protected IDbConnection conn;
        protected IDbCommand cmd;
        protected  DbProviderFactory df;

        protected string providerName ="" ;
        protected string connString ="";

        public CommonProvider()
        {
            providerName=Configuration.DataProviderName;
            connString= Configuration.ConnString;
        }

        public CommonProvider(string providerName, string connString)
        {
            this.providerName = providerName;
            this.connString = connString;
        }

        public static CommonProvider Instance()
        {
            CommonProvider cp = new CommonProvider();
            try
            {
                cp.Connect();
            }
            catch (Exception e)
            {
                Log.Write(e.Message);
            }
            return cp;
        }

        public static CommonProvider Instance(System.Web.SessionState.HttpSessionState session)
        {
            if (null == session[Configuration.DataProviderSessionKey])
            {
                CommonProvider cp = new CommonProvider();
                session.Add(Configuration.DataProviderSessionKey, cp);
                return cp;
            }
            return (CommonProvider)session[Configuration.DataProviderSessionKey];
        }

        virtual protected void Connect()
        {
#if DEBUG
            Log.Write("providerName:"+providerName+" connString:"+connString);
#endif
            df=DbProviderFactories.GetFactory(providerName);
            conn=df.CreateConnection();
            conn.ConnectionString = connString;
            cmd = conn.CreateCommand();
        }

        public IDbDataParameter AddParameters(string name, DbType type)
        {
            IDbDataParameter param = cmd.CreateParameter();
            param.ParameterName = name;
            param.DbType = type;
            cmd.Parameters.Add(param);
            return param;
        }
        public IDbDataParameter AddParameters(string name, DbType type, int size)
        {
            IDbDataParameter param = cmd.CreateParameter();
            param.ParameterName = name;
            param.DbType = type;
            param.Size = size;
            cmd.Parameters.Add(param);
            return param;
        }

        public IDbDataParameter AddParameters(string name, object o)
        {
            IDbDataParameter param = cmd.CreateParameter();
            param.ParameterName = name;
            param.Value = o;
            cmd.Parameters.Add(param);
            return param;
        }

        public IDataParameterCollection Parameter
        {
            get
            {
                return cmd.Parameters;
            }
        }

        public void ClearParameters()
        {
            if(cmd.Parameters.Count>0)
                cmd.Parameters.Clear();
        }

        public int ExecuteNonQuery(string cmdText, CommandType cmdType)
        {
            cmd.CommandText = cmdText;
            cmd.CommandType = cmdType;
            int n = -1;
            try
            {
                conn.Open();
#if DEBUG
                Log.Write("conn State:"+conn.State);
#endif
                n = cmd.ExecuteNonQuery();
                ClearParameters();
                conn.Close();
            }
            catch (Exception e)
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
                Log.Write("ExecuteNonQuery:" + e.Message);
            }
            return n;
        }

        public object ExecuteScalar(string cmdText, CommandType cmdType)
        {
            //conn = new SqlConnection(cns);
            cmd.CommandText = cmdText;
            cmd.CommandType = cmdType;
            object o = null;
            try
            {
                conn.Open();
                o = cmd.ExecuteScalar();
                ClearParameters();
                conn.Close();
            }
            catch (Exception e)
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
                Log.Write("ExecuteScalar:" + e.Message);
            }
            return o;
        }

        public IDataReader ExcuteDataReader(string cmdText, CommandType cmdType)
        {
            //conn = new SqlConnection(cns);
            cmd.CommandText = cmdText;
            cmd.CommandType = cmdType;
            IDataReader reader = null;
            try
            {
                conn.Open();
                reader = cmd.ExecuteReader();
                ClearParameters();
            }
            catch (Exception e)
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
                Log.Write("ExcuteDataReader:" + e.Message);
            }
            return reader;
        }

        public DataSet ExcuteDataSet(string cmdText, CommandType cmdType, string tableName)
        {
            //conn = new SqlConnection(cns);
            cmd.CommandText = cmdText;
            cmd.CommandType = cmdType;
            System.Data.DataSet ds = null;
            try
            {
                conn.Open();
                IDbDataAdapter da = df.CreateDataAdapter();
                da.SelectCommand = cmd;
                ds = new DataSet(tableName);
                da.Fill(ds);
                ClearParameters();
                conn.Close();
            }
            catch (Exception e)
            {
                if (conn.State == ConnectionState.Open)
                    conn.Close();
                Log.Write("ExcuteDataSet:" + e.Message);
            }
            return ds;
        }

        public void Dispose()
        {
            Dispose(true);
        }

        public void Dispose(bool disposing)
        {
            if (!disposed)
            {
                if (disposing)
                {
                    if (conn.State == ConnectionState.Open)
                        conn.Close();
                    cmd.Dispose();
                    disposed = true;
                    GC.SuppressFinalize(this);
                }
            }
        }

    }
}

UserDAO.cs

using System;
using System.Text;
using System.Data;
using System.Collections.Generic;
using Kcitwm.Framework.DataProvider;
using Kcitwm.Cosmic.DataAccess.Entity;
using Kcitwm.Cosmic.DataProvider;

namespace Kcitwm.Cosmic.DataAccess.DAO
{
    public class UserDAO
    {

        static CommonProvider cp = null;

        public static  int Add(User user)
        {
            cp = CosmicCommonProvider.Instance();
            cp.AddParameters("UserID", user.UID);
            cp.AddParameters("UserPWD", user.PWD);
            cp.AddParameters("UserName", user.Name);
            cp.AddParameters("UserNick", user.Nick);
            cp.AddParameters("UserMail", user.Mail);
            cp.AddParameters("UserCreateTime", user.CreateTime);
            cp.AddParameters("UserLastTime", user.LastTime);
            System.Data.IDataParameter p = cp.AddParameters("UserIdentity", DbType.Int32);
            p.Direction = ParameterDirection.Output;
            //DBStatics.CosmicUserAdd是固定的存储过程名,下同.
            int rtn = (int)cp.ExecuteNonQuery(DBStatics.CosmicUserAdd, CommandType.StoredProcedure);
            if(rtn==1)
                return (int)p.Value; 
            return rtn;
        }

        public static int Update(User user)
        {
            cp = CosmicCommonProvider.Instance();
            cp.AddParameters("AutoID", user.ID);
            cp.AddParameters("UserID", user.UID);
            cp.AddParameters("UserPWD", user.PWD);
            cp.AddParameters("UserName", user.Name);
            cp.AddParameters("UserNick", user.Nick);
            cp.AddParameters("UserMail", user.Mail);
            cp.AddParameters("UserCreateTime", user.CreateTime);
            cp.AddParameters("UserLastTime", user.LastTime);
            return cp.ExecuteNonQuery(DBStatics.CosmicUserUpdate, CommandType.StoredProcedure);
        }

        public static int Delete(User user)
        {
            cp = CosmicCommonProvider.Instance();
            cp.AddParameters("AutoID", user.ID);
            return cp.ExecuteNonQuery(DBStatics.CosmicUserDelete, CommandType.StoredProcedure);           
        }

        public static  int Delete(int id)
        {
            cp = CosmicCommonProvider.Instance();
            cp.AddParameters("AutoID", id);
            return cp.ExecuteNonQuery(DBStatics.CosmicUserDelete, CommandType.StoredProcedure);
         }
    }
}


web.config

<configuration>
  <configSections>
    <section name="Kcitwm.Cosmic" type="System.Configuration.NameValueSectionHandler,System,Version=2.0.0.0,Culture=neutral,PublicKeyToken=b77a5c561934e089"/>
  </configSections>
  <Kcitwm.Cosmic>

    <!--
      <add key="ConnString" value="Data Source=ora9i;Pooling=true;Min Pool Size=0;Max Pool Size=100;User Id=Kcitwm;Password=Cosmic" />
      <add key="DataProviderName" value="System.Data.OracleClient" />
    --> 
    <add key="ConnString" value="server=cl;uid=sa;pwd=kcitwm;database=Cosmic" />
    <add key="DataProviderName" value="System.Data.SqlClient" />
  </Kcitwm.Cosmic>
 
  <system.web>
  </system.web>
</configuration>