告别ADO.NET实现应用系统无缝切换的烦恼(3)_ASP.NET学习网|ASP.NET教程|ASP.NET MVC|ASP.NET 源码|VS2010教程|ASP.NET视频教程

最后说下测试调用代码，首先webconfig配置里面这样配置下，主要选取SQL SERVER和ORACLE做测试，毕竟这是.NET支持的两个典型数据库，要是把.NET所支持的所有书库都测试一遍，那测试量可不小了，呵呵。

protected void Page_Load(object sender, EventArgs e)
        {

            //测试DataReader,SQLSERVER和ORACLE都通过
            //DataProviderFactory fac = new DataProviderFactory();
            //DbParameterCollection ParaCollect = fac.GetParmCollection();
            //fac.AddParam("USERID", 100, ParaCollect);
            //DbDataReader dr = fac.ExecuteDataReader(fac.conn, "SELECT * FROM USER_TEST WHERE USERID=$USERID");
            //while (dr.Read())
            //{
            //    string a = dr[1].ToString();
            //}
            //fac.conn.Close();//在调用处显示关闭

            //无参数DataSet测试 SQLSERVER和ORACLE都通过
            //DataTable dt = fac.ExecDataSet("SELECT * FROM USER_TEST").Tables[0];

            //带参数DataSet测试 SQLSERVER和ORACLE都通过
            //DbParameterCollection ParaCollect = fac.GetParmCollection();
            //fac.AddParam("USERID", 100, ParaCollect);
            //fac.AddParam("USERNAME", "局%", ParaCollect);//这里的参数名可以任意成其它，不一定非要和字段名相同（下同）
            //DataTable dt = fac.ExecDataSet("SELECT * FROM USER_TEST WHERE USERNAME LIKE $USERNAME").Tables[0];
            //DataTable dt = fac.ExecDataSet("SELECT * FROM USER_TEST WHERE USERID=$USERID OR USERNAME LIKE $USERNAME").Tables[0];//多参数测试

            //单值测试(带参数) SQLSERVER和ORACLE都通过
            //DbParameterCollection ParaCollect = fac.GetParmCollection();
            //fac.AddParam("USERID", 100, ParaCollect);
            //string retValue = fac.ReturnValue("SELECT USERNAME FROM USER_TEST WHERE USERID=$USERID");

            //带参存储过程测试返回结果集 SQLSERVER和ORACLE都通过
            //DbParameterCollection ParaCollect = fac.GetParmCollection();
            //fac.AddParam("StartDate", "2009-8-1", ParaCollect);
            //fac.AddParam("EndDate",  "2009-8-21", ParaCollect);
            //DataTable dt = fac.RunProcedure("USP_GetMixedReport").Tables[0];

            //带参数测试存储过程的输出参数值和返回值，方法不返回结果集 SQLSERVER通过
            //int flag = 0, sign = 0, ret = 0;
            //DbParameterCollection ParaCollect = fac.GetParmCollection();
            //fac.AddParam("USER_ACCOUNT", DbType.String, "admin", ParaCollect);
            //fac.AddParam("USER_PWD", DbType.String, "68053af2923e00204c3ca7c6a3150cf7", ParaCollect);

            //fac.AddParam("FLAG", DbType.Int32, "", ParaCollect);
            //ParaCollect["@FLAG"].Direction = System.Data.ParameterDirection.Output;

            //fac.AddParam("SIGN", DbType.Int32, "", ParaCollect);
            //ParaCollect["@SIGN"].Direction = System.Data.ParameterDirection.Output;

            //fac.AddParam("RetValue", DbType.String, "", ParaCollect);
            //ParaCollect["@RetValue"].Direction = System.Data.ParameterDirection.ReturnValue;

            //fac.RunVoidProcedure("SP_ValideLogin");
            //flag = int.Parse(ParaCollect["@FLAG"].Value.ToString());
            //sign = int.Parse(ParaCollect["@SIGN"].Value.ToString());
            //ret = int.Parse(ParaCollect["@RetValue"].Value.ToString());//存储过程约定返回值必须是int型

            //改进后带参数测试存储过程的输出参数值和返回值的测试 SQLSERVER和ORACLE都通过
            //int flag = 0, sign = 0, ret = 0;
            //DataProviderFactory fac = new DataProviderFactory();
            //DbParameterCollection ParaCollect = fac.GetParmCollection();
            //fac.AddInputParam("USER_ACCOUNT", "admin", ParaCollect);
            //fac.AddInputParam("USER_PWD", "68053af2923e00204c3ca7c6a3150cf7", ParaCollect);
            //fac.AddOutputParam("FLAG", ParaCollect);
            //fac.AddOutputParam("SIGN", ParaCollect);
            //fac.AddReturnParam("RetValue", ParaCollect);
            //fac.RunVoidProcedure("SP_ValideLogin");
            //string prefix = fac.retParaformat.Replace(":","");//Oracle存储过程参数前冒号移除掉
            //flag = int.Parse(ParaCollect[string.Format(prefix,"FLAG")].Value.ToString());
            //sign = int.Parse(ParaCollect[string.Format(prefix, "SIGN")].Value.ToString());
            //ret = int.Parse(ParaCollect[string.Format(prefix, "RetValue")].Value.ToString());//存储过程约定返回值必须是int型

            //调用存储过程测试 SQLSERVER和ORACLE都通通过
            //DataProviderFactory fac = new DataProviderFactory();
            //DbParameterCollection ParaCollect = fac.GetParmCollection();
            //fac.AddInputParam("P_UserID", 7, ParaCollect);
            //fac.AddInputParam("P_UserName", "peace", ParaCollect);
            //fac.AddInputParam("P_UserAge", 100, ParaCollect);
            //fac.RunVoidProcedure("PROC_USER_TEST_ADD");

            //多条提交事务处理测试 SQLSERVER和ORACLE都通过
            //List<string> SqlList = new List<string>();
            //DataProviderFactory fac = new DataProviderFactory();
            //DbParameterCollection ParaCollect = fac.GetParmCollection();
            //fac.AddParam("UserName", "peaceli", ParaCollect);
            //fac.AddParam("UserAge", 150, ParaCollect);
            //SqlList.Add("INSERT INTO USER_TEST(UserName,UserAge) VALUES($UserName,$UserAge)");
            //SqlList.Add("INSERT INTO USER_TEST(UserName,UserAge) VALUES($UserName,$UserAge)");
            //SqlList.Add("INSERT INTO USER_TEST(UserName,UserAge) VALUES($UserName,$UserAge)");
            //fac.ExecSqlTran(SqlList);

            //插入操作参数测试(SQL SERVER) 通过
            //UserInfo ui = new UserInfo();
            //ui.UserName = "hello peace";
            //ui.UserAge = 100;
            //Addinn(ui);

            //插入操作参数测试(Oracle) 通过
            //UserInfo ui = new UserInfo();
            //ui.USERID = 10;
            //ui.USERNAME = "hello peace";
            //ui.USERAGE = 120;
            //Addin(ui);

            //插入操作反射参数转换测试 SQLSERVER和ORACLE都通过
            //UserInfo ui = new UserInfo();
            //ui.USERNAME = "peaceli";
            //ui.USERAGE = 110;
            //Add(ui);

            //返回实体对象测试 SQLSERVER和ORACLE都通过
            UserInfo ui = new UserInfo();
            ui.USERID = 1;
            GetInfo(ui);

        }
        //private void Addinn(UserInfo ui)
        //{
        //    DataProviderFactory fac = new DataProviderFactory();
        //    DbParameterCollection ParaCollect = fac.GetParmCollection();
        //    fac.AddParam("@UserName",  ui.UserName, ParaCollect);
        //    fac.AddParam("@UserAge",  ui.UserAge, ParaCollect);
        //    fac.ExecSql("INSERT INTO USER_TEST(UserName,UserAge) VALUES(@UserName,@UserAge)");
        //}
        private void Addin(UserInfo ui)
        {
            DataProviderFactory fac = new DataProviderFactory();
            DbParameterCollection ParaCollect = fac.GetParmCollection();
            //fac.AddParam(":UserName",  ui.UserName, ParaCollect);//给参数赋值时冒号可以不加，但有的版本可能必须加
            //fac.AddParam(":UserAge", ui.UserAge, ParaCollect);

            //fac.AddParam("UserID", ui.USERID, ParaCollect); //这行注释放开在ORACLE下同不过，ORACLE要求所全参数匹配，有多余参数就不行，这点有些变态
            fac.AddParam("UserName",  ui.USERNAME, ParaCollect);//SQL SERVER只要求用到的参数包含在参数集合里就行了，其它多余参数并不影响执行
            fac.AddParam("UserAge",  ui.USERAGE, ParaCollect);
            fac.ExecSql("INSERT INTO USER_TEST(UserName,UserAge) VALUES(:UserName,:UserAge)");
        }
        private void Add(UserInfo ui)
        {
            DataProviderFactory fac = new DataProviderFactory();
            DbParameterCollection ParaCollect = fac.GetParmCollection();
            string[] fields = { "USERNAME", "USERAGE" };//要求参数化的实体属性
            List<string> ListFields = new List<string>(fields);
            fac.ConvertToParameters(ui, ParaCollect, ListFields);//如果新增记录有很多参数的话，可能AddParam很多次，采用反射批量转换
            fac.ExecSql("INSERT INTO USER_TEST(USERNAME,USERAGE) VALUES($USERNAME,$USERAGE)");
        }

        private void GetInfo(UserInfo ui)
        {
            DataProviderFactory fac = new DataProviderFactory();
            DbParameterCollection ParaCollect = fac.GetParmCollection();
            fac.AddParam("USERID", ui.USERID, ParaCollect);
            fac.GetModel(ui, "SELECT USERNAME,USERAGE FROM USER_TEST WHERE USERID=$USERID");
        }
    }

UserInfo类如下：

public class UserInfo
    {
       public int USERID { get; set; }
       public string USERNAME { get; set; }
       public int USERAGE { get; set; }
    }

测试到最后类属性改动过，统一改成了大写，再次建议大写标准(包括数据库设计)，可以定义成USER_ID,USER_NAME,USER_AGE等，并与数据库字段名保持一致，这样有利于多数据库的兼容。

结语：个人并不反对项目里单独用对应的xxhelper.cs，某个项目用SQLSERVER数据库，就用SqlHelper.csL类，ORACLE就用OracleHelper.cs类，这样来得更干脆快捷，基本上每个项目都是这对特定的数据库在开发，没必要搞成通用类，真要搞成通用类，要经过大量的实际测试，也许我最近有时寂寞空虚也无聊，突然想测试下同时也想改进下，呵呵，零零碎碎花了点时间测试了下，选取两个数据库测试了一遍，最终只需要改动config配置的数据库连接就可以了，真正达到了一套系统的无缝切换。里面有些可能还说的不够准确，可能也还有遗漏的地方，仅供参考吧！！！

访问类库的文件完整的贴一次，如下：

//*****************************************************************************************************************
//* 描    述：数据库工厂访问类
//* 更新描述：里面供调用执行的各方法可带参数执行，在外部指定参数名和参数值即可。
//* 最终期望：支持.NET所支持的所有数据库并达到系统的无缝切换（尽情的忽悠吧O(∩_∩)O~）
//*****************************************************************************************************************
using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Configuration;
using System.Reflection;

namespace DataProvider
{
    public class DataProviderFactory
    {
        public   DbConnection conn;//抽象类型
        private  DbCommand cmd;//抽象类型
        private  DbProviderFactory provider;

        private  DbParameter Para;//不同数据库参数类型的抽象类型
        private  DbDataAdapter Adapter;//对应不同数据库的数据适配器

        Dictionary<Type, String> ParametersFormat;//不同数据库参数格式化类型

        public string retParaformat = string.Empty;//最终返回的格式化标志，如@{0},:{0}
        public DataProviderFactory()
        {

            //从配置文件中取出标示数据库类型的字符串并通过ProviderName的不同支持不同类型的数据库
            string providerName = ConfigurationManager.ConnectionStrings["ConnStr"].ProviderName;//也可以用索引,从1开始

            //创建一个数据库对应的实例，使用该实例就可以创建对应的connection,command 和adapater等等对象
            provider = DbProviderFactories.GetFactory(providerName);

            //创建具体的数据库连接类型和命令执行类型
            conn = provider.CreateConnection();
            conn.ConnectionString = ConfigurationManager.ConnectionStrings["ConnStr"].ConnectionString;
            cmd = provider.CreateCommand();
            cmd.Connection = conn;

            //创建具体的参数类型
            Para = provider.CreateParameter();

            //创建具体的适配器类型
            Adapter = provider.CreateDataAdapter();

            //不同数据库参数前缀格式化
            ParametersFormat = new Dictionary<Type, String>();
            ParametersFormat.Add(typeof(System.Data.SqlClient.SqlCommand), "@{0}");//因SQL SERVER只返回{0}没有@前缀,在此初始化处理

            //返回格式化标志
            retParaformat = GetParameterFormat(cmd);
        }

       /// <summary>
       /// 添加参数
       /// </summary>
       /// <param name="ParaName">参数名称</param>
       /// <param name="SqlType">参数数据类型</param>
       /// <param name="ParaValue">参数值</param>
       /// <param name="ParaCollect">参数对象的集合</param>
        public  void AddParam(string ParaName, DbType SqlType, object ParaValue, DbParameterCollection ParaCollect)
        {
            //不允许将一个DbCommand对象的Parameters插入到另外一个DbCommand对象，那么多个参数的话可以加上下面一句判断
            //如果已经存在至少一个对象时，再深层拷贝一个
            if (ParaCollect.Count >= 1)
            {
                Para = (DbParameter)((ICloneable)ParaCollect[0]).Clone();
            }

            Para.ParameterName = string.Format(retParaformat, ParaName);
            Para.DbType = SqlType;

            if (ParaValue == null)
            {
                Para.Value = string.Empty;//DBNull.Value;
            }
            else
            {
                Para.Value = ParaValue;
            }
            ParaCollect.Add(Para);
        }

        public void AddParam(string ParaName, object ParaValue, DbParameterCollection ParaCollect)
        {

            if (ParaCollect.Count >= 1)
            {
                Para = (DbParameter)((ICloneable)ParaCollect[0]).Clone();
            }

            Para.ParameterName = string.Format(retParaformat, ParaName);//将参数格式化为具体的数据库参数格式

            if (ParaValue == null)
            {
                Para.Value = string.Empty;
            }
            else
            {
                Para.Value = ParaValue;
            }
            ParaCollect.Add(Para);
        }

        /// <summary>
        /// 存储过程输入参数
        /// </summary>
        /// <param name="ParaName"></param>
        /// <param name="ParaValue"></param>
        /// <param name="ParaCollect"></param>
        public void AddInputParam(string ParaName, object ParaValue, DbParameterCollection ParaCollect)
        {

            if (ParaCollect.Count >= 1)
            {
                Para = (DbParameter)((ICloneable)ParaCollect[0]).Clone();
            }

            Para.ParameterName = string.Format(retParaformat.Replace(":",""), ParaName);//ORACLE存储过程参数前没有冒号

            if (ParaValue == null)
            {
                Para.Value = string.Empty;
            }
            else
            {
                Para.Value = ParaValue;
            }
            ParaCollect.Add(Para);
        }

        /// <summary>
        /// 存储过程输出参数
        /// </summary>
        /// <param name="ParaName"></param>
        /// <param name="ParaValue"></param>
        /// <param name="ParaCollect"></param>
        public void AddOutputParam(string ParaName, DbParameterCollection ParaCollect)
        {

            if (ParaCollect.Count >= 1)
            {
                Para = (DbParameter)((ICloneable)ParaCollect[0]).Clone();
            }

            Para.ParameterName = string.Format(retParaformat.Replace(":", ""), ParaName);
            Para.Value = string.Empty;
            ParaCollect.Add(Para);
            ParaCollect[Para.ParameterName].Direction = System.Data.ParameterDirection.Output;//指定该参数为输出参数
        }

        /// <summary>
        /// 存储过程返回值参数
        /// </summary>
        /// <param name="ParaName"></param>
        /// <param name="ParaValue"></param>
        /// <param name="ParaCollect"></param>
        public void AddReturnParam(string ParaName,DbParameterCollection ParaCollect)
        {

            if (ParaCollect.Count >= 1)
            {
                Para = (DbParameter)((ICloneable)ParaCollect[0]).Clone();
            }

            Para.ParameterName = string.Format(retParaformat.Replace(":", ""), ParaName);
            Para.Value = string.Empty;
            ParaCollect.Add(Para);
            ParaCollect[Para.ParameterName].Direction = System.Data.ParameterDirection.ReturnValue;//指定该参数为返回值参数
        }

        /// <summary>
        /// 抽象参数集合类型
        /// </summary>
        /// <returns></returns>
        public  DbParameterCollection GetParmCollection()
        {
            return cmd.Parameters;
        }

        /// <summary>
        /// 执行SQL并返回数据集
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public DataSet ExecDataSet(string Sql)
        {
            DataSet ds = new DataSet();
            try
            {
                this.Open();
                cmd.CommandText = Replace(Sql);
                Adapter.SelectCommand = cmd;
                Adapter.Fill(ds);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                this.Close();
            }
            return ds;

        }

        /// <summary>
        /// 执行SQL语句并返回DataReader对象
        /// </summary>
        /// <param name="dbcon"></param>
        /// <param name="cmdText"></param>
        /// <returns></returns>
        public DbDataReader ExecuteDataReader(DbConnection dbcon,string cmdText)
        {

            try
            {
                if (dbcon.State == ConnectionState.Closed)
                {
                    dbcon.Open();
                }
                cmd.CommandText = Replace(cmdText);
                DbDataReader dr = cmd.ExecuteReader();
                cmd.Parameters.Clear();
                cmd.Dispose();
                return dr;
            }
            catch
            {
                dbcon.Close();//发生异常在此处关闭，否则在调用显式处关闭
                return null;
            }

        }
(责任编辑：admin)

ADO.NET中的表映射	C#引用System.Data.SQLite操作SQLite数据库一例
打开和关闭数据库连接的3种方式	浅谈ExecuteNonQuery,ExecuteReader和ExecuteSca
ADO.NET数据访问方式：SqlDataReader	OleDbDataReader的一些属性和方法
ActiveRecord多表查询，返回DataTable	DataSet,DataTable,DataView
DataTable数据导出到EXCEL文件	告别ADO.NET实现应用系统无缝切换的烦恼
DataTable转换成JSON字符串的函数	.NET中统一的存储过程调用方法
SQL语句之普通行列转换	DataTable.Compute强大的功能
将DataRow转换成相应的对象（通用以及泛型操作）	ADO.NET与ADO在数据内存中的差异
XamDataGrid控件显示checkbox并绑定DataTable	DataSet DataTable获得总记录数

ASP.NET学习网|ASP.NET教程|ASP.NET MVC|ASP.NET 源码|VS2010教程|ASP.NET视频教程

热门标签

告别ADO.NET实现应用系统无缝切换的烦恼(3)

Tags：ADO.NET