跨数据库连接类(System.Data.Common)

a.net为为们们提供了逐条数据库的链接.
比如说MSSQL就是System.Data.SqlClient
         Oracle就是System.Data.OracleClient
         MySql就是MySql.Data.MySqlClient
其他的附和正是的了.还有哪些DB2.还有等等一些.
先前我们做跨数据库的时候用的是反射.只但是看到网上海人民广播电视台湾大学讲评说反射功用不怎么好
之所以近日在探究System.Data.Common
也正是通用的数据链接类吧

理所当然那里只是做测试.没有做太多的卷入,
此处是本人的目录结构
图片 1

话不多说.上代码
自然首先步依然在web.config里面写上多少链接语句.
那里是最根本的
自身那里测试的唯有mysql,mssql

图片 2    <connectionStrings>
图片 3        <add name=”ConnectionString1″ connectionString=”Data Source=LIUJU;Initial Catalog=MSPetShop4;Persist Security Info=True;User ID=sa;Password=***” providerName=”System.Data.SqlClient”/>
图片 4        <add name=”ConnectionString” connectionString=”server=localhost;user id=root;password=***;persist security info=True;database=mspetshop4″ providerName=”MySql.Data.MySqlClient”/>
图片 5    </connectionStrings>

那里的name你协调安装2个名字就能够了.connectionString正是数据库链接语句.这里没有何样好说的
最关键的正是后边的providerName那里便是写上你数据库要引入的称呼空间.那样程序才会清楚您用的是什么数据库

上面那里自身便是写的1个webConfig的援助类.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;

namespace DBHelper.Library
{
    public static class WebConfigHelper
    {
        private readonly static string DBConnectionString = ConfigurationManager.ConnectionStrings[“ConnectionString”].ConnectionString;
        private readonly static string DBProviderName = ConfigurationManager.ConnectionStrings[“ConnectionString”].ProviderName;
        public static string GetConnectionString
        {
            get { return DBConnectionString; }
        }
        public static string GetProviderName
        {
            get { return DBProviderName; }
        }
    }
}

好.下边便是关键的了

using System;
using System.Data;
using System.Data.Common;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace DBHelper.Library
{
    public static class DataAccessHelper
    {
        /// <summary>
        /// 创建DbCommand对象
        /// </summary>
        /// <returns>DbCommand对象</returns>
        public static DbCommand GetCommand()
        {
            //获得webConfig里面包车型大巴要引用的名号空间
            string DBProviderName = WebConfigHelper.GetProviderName;
            //拿到webConfig里面包车型客车链接字符串
            string DBConnectionString = WebConfigHelper.GetConnectionString;

            //数据库工厂类为此数据库创制一个数据库链接对象
            DbProviderFactory dpf = DbProviderFactories.GetFactory(DBProviderName);
            //创建Connection
            DbConnection conn = dpf.CreateConnection();
            conn.ConnectionString = DBConnectionString;
            //创建Command
            DbCommand comm = conn.CreateCommand();
            comm.CommandType = CommandType.Text;
            return comm;
        }

        /// <summary>
        /// 执行查询,再次来到datatable
        /// </summary>
        /// <param name=”command”></param>
        /// <returns></returns>
        public static DataTable ExecuteSelectCommand(DbCommand command)
        {
            DataTable table;
            try
            {
                if (command.Connection.State != ConnectionState.Open)
                {
                    command.Connection.Open();
                }
                DbDataReader reader = command.ExecuteReader();
                table = new DataTable();
                table.Load(reader);
                reader.Close();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message, ex);
            }
            finally
            {
                command.Connection.Close();
            }
            return table;

        }
        /// <summary>
        /// 执行update insert del操作
        /// </summary>
        /// <param name=”command”></param>
        /// <returns>再次回到影响行数</returns>
        public static int ExecuteNonQuery(DbCommand command)
        {
            int affectRows = -1;
            try
            {
                if (command.Connection.State != ConnectionState.Open)
                {
                    command.Connection.Open();
                }
                affectRows = command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message, ex);
            }
            finally
            {
                command.Connection.Close();
            }
            return affectRows;
        }
        /// <summary>
        /// 重回第壹列第叁行
        /// </summary>
        /// <param name=”command”></param>
        /// <returns></returns>
        public static string ExecuteScalar(DbCommand command)
        {
            string value = “”;
            try
            {
                if (command.Connection.State != ConnectionState.Open)
                {
                    command.Connection.Open();
                }
                value = command.ExecuteScalar().ToString();

            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message, ex);
            }
            finally
            {
                command.Connection.Close();
            }
            return value;
        }
    }
}

那样你就能够假诺配置一下webConfig程序就自行知道您链接的是何许数据库,
就能够活动为你创制2个数据库链接对象了.

使用办法

        protected void Page_Load(object sender, EventArgs e)
        {
            DbCommand dc = DataAccessHelper.GetCommand();
            dc.CommandText = “SELECT ProductId, CategoryId, Name, Descn FROM Product”;
            DataTable dt = DataAccessHelper.ExecuteSelectCommand(dc);
            GridView1.DataSource = dt;
            GridView1.DataBind();
        }

运作结果
图片 6
五个数据库都测试通常

参考网址
http://www.cnblogs.com/freegarden/archive/2009/09/25/1574044.html
此选用下载地址
http://files.cnblogs.com/liuju150/DBHelper20091008124542.zip

相关文章