在.net存取Oracle資料庫

連接到Oracle有幾種方式ODBC、OLEDB、MS Data Provider for Oracle、ODP.NET,MS Data Provider for Oracle和ODP.NET是很類似的東西, Oracle宣稱ODP.NET比較穩,

這邊有些討論:

• Comparing MS Data Provider for Oracle and the Oracle Data Provider for .NET
• System.Data.OracleClient versus Oracle.DataAccess.Client (ODP.NET)

, 以下是要介紹MS Data Provider for Oracle的使用

1. 下載ODAC 32 bit 或  ODAC 64bit (看你oracle版本下載對應的檔案), 進行安裝
2. 專案加入參考System.Data.OracleClient
3. 將oracle安裝目錄grand給Authenticated Users帳號[讀取和執行], 這很重要常常會導致錯誤:系統所描述的錯誤訊息是需安裝8i以上的客戶端元件 ‧
4. 安裝環境變數PATH=安裝client路徑, ex: D:\oracle\product\11.2.0\client_1
5. 排除oracle network不通問題, 直接將network的字串貼上程式, 如果可以通, 表示在”安裝client路徑\network\admin”下沒有tnsnames.ora,

string connStr = @"Data Source=(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.0.218)(PORT = 1521))
)
(CONNECT_DATA =
(SID = instantDBID)
(SERVER = DEDICATED)

)
)
;Persist Security Info=True;User ID=doc;Password=doc;Unicode=True";

6. tnsnames.ora的格式, data source後名稱就是以下的DATASOURCE_STRING

DATASOURCE_STRING =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.0.218)(PORT = 1521))
)
(CONNECT_DATA =
(SID = instantDBID)
(SERVER = DEDICATED)

)
)

7. 以上環境OK後, 就可以開始程式部份:





.aspx

<form id="form1" runat="server">
<div>
<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
<asp:Button ID="Button1" runat="server" OnClick="btnQuery_Click" Text="Query" />
<asp:Button ID="Button2" runat="server" OnClick="btnUpdate_Click" Text="Update" /></div>
</form>

.c

using System.Data.OracleClient; //需要引用


public partial class oracle : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{

//連線字串
string connStr2 = @"Data Source=DATASOURCE_STRING;Persist Security Info=True;User ID=XXX;Password=XXX;Unicode=True";

//查詢
protected void btnQuery_Click(object sender, EventArgs e)
{
using (OracleConnection conn = new OracleConnection(connStr2))
{
conn.Open(); 
string sql = @"select NAME from tblTest where keyA = 'XXX' ";
OracleCommand cmd = new OracleCommand(sql, conn);
OracleDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
this.TextBox1.Text = dr["NAME "].ToString();
}
conn.Close();
} 
}
//修改
protected void btnUpdate_Click(object sender, EventArgs e)
{
using (OracleConnection conn = new OracleConnection(connStr2))
{
conn.Open();
string sql = @"update  tblTestt set NAME  = 'AAA'  where keyA= 'XXX'";
OracleCommand cmd = new OracleCommand(sql, conn);
cmd.ExecuteNonQuery();
conn.Close();
} 
}

參考:


http://download.oracle.com/docs/cd/B25329_01/doc/appdev.102/b25312/building_odp.htm

http://www.dotblogs.com.tw/dotnetfactory/archive/2008/09/06/5311.aspx

http://msdn.microsoft.com/zh-tw/library/ms165428(v=VS.80).aspx  ,MDAC