ASP.NET利用DbProviderFactory、DbConnection、DbCommand、DbDataAdapter的結合來連結MSSQL或Oracle資料庫

2008-08-19

19814
0
ASP.NET

小弟最近剛好有在玩Oracle的資料庫..
介紹如何使用Web.Config的不同連結字串,就可以連到不同的資料庫.

小弟最近剛好有在玩Oracle的資料庫..

介紹如何使用Web.Config的不同連結字串,就可以連到不同的資料庫.

首先記得把Oracle的tnsnames.ora設定好(要安裝Oracle Client 10g),內容如下：

# tnsnames.ora Network Configuration File: C:\oracle\product\10.2.0\client_1\network\admin\tnsnames.ora # Generated by Oracle configuration tools. TEST = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.1)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME = TEST) ) )

Web.Config連線字串的設定如下：

asp.net(c#)

DB.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="DB.aspx.cs" Inherits="DB" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head runat="server"> <title>DB</title> </head> <body> <form id="form1" runat="server"> <div> MS SQL<asp:GridView ID="GridView1" runat="server"> </asp:GridView> <br /> <br /> Oracle<asp:GridView ID="GridView2" runat="server"> </asp:GridView> </div> </form> </body> </html>

DB.aspx.cs

using System; using System.Collections.Generic; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; using System.Data; using System.Data.Common;//記得要using using System.Configuration; public partial class DB : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { DataSet dsMSSQL = GetDataSet("select * from [Table]", "MSSQL"); this.GridView1.DataSource = dsMSSQL; this.GridView1.DataBind(); DataSet dsOracle = GetDataSet("select * from TEST2", "Oracle"); this.GridView2.DataSource = dsOracle; this.GridView2.DataBind(); } protected DataSet GetDataSet(string SqlCommand,string DB) { DbProviderFactory dbProviderFactory = DbProviderFactories.GetFactory(ConfigurationManager.ConnectionStrings[DB].ProviderName); DbConnection dbConnection = dbProviderFactory.CreateConnection(); dbConnection.ConnectionString = ConfigurationManager.ConnectionStrings[DB].ConnectionString; DataSet ds = new DataSet(); DbCommand dbCommand = dbProviderFactory.CreateCommand(); dbCommand.Connection = dbConnection; DbDataAdapter dbDataAdapter = dbProviderFactory.CreateDataAdapter(); dbCommand.CommandText = SqlCommand; dbDataAdapter.SelectCommand = dbCommand; dbDataAdapter.Fill(ds); return ds; } }

執行結果:

參考網址:

http://msdn.microsoft.com/zh-tw/library/9hy8csk1(VS.80).aspx
http://msdn.microsoft.com/zh-tw/library/system.data.common.dbproviderfactory(VS.80).aspx

回首頁