.Net 開發者與 Oracle Database (六)

2012-06-17

27518
0
Oracle

安裝好 Oracle Server、建立 Tablespace、使用者帳號、建立表格及資料後，其實不需要額外安裝任何套件就可以開始寫 Data Access 的部分囉！

主要的方式有：

1. 使用 System.Data.OracleClient 命名空間：

在 .Net 2.0 後，就可以專案下加入參考，使用 System.Data.OracleClient 命名空間。

不過，在 .Net 4.0 後已經被標示為 deprecated，已經不建議使用，並且在未來版本該命名空間會被移除。

簡易使用方式如下：


// .Net 4.0 以後 OracleConnection 不建議使用：參考 http://go.microsoft.com/fwlink/?LinkID=144260
var conn1 = new OracleConnection("Data Source=orcl;User ID=username;Password=password;Integrated Security=no;");
var cmd1 = new OracleCommand("SELECT * FROM tableName WHERE columnName= :columnName", conn1);
cmd1.Parameters.Add(":columnName", 1);

conn1.Open();
var reader1 = cmd1.ExecuteReader();
while (reader1.Read())
{
    Response.Write(reader1[0] + "〈br〉");
    Response.Write(reader1["columnName"] + "〈br〉");
}
conn1.Close();

注意事項：

(1) 如果對於 Oracle 連接字串不熟悉如何寫，可以到 ConnectionStrings.com 去查詢 .NET Framework Data Provider for Oracle(OracleConnection)：

(2) OracleParameter 使用 " : " 來表示，而不是 SQL Server 使用的 " @ "

2. 使用 System.Data.OleDb 命名空間：

(1) Microsoft OLE DB Provider for Oracle 簡易使用方式如下：


// System.Data.OleDb :http://msdn.microsoft.com/en-us/library/6d9ew87b
var conn2 = new OleDbConnection("Provider=msdaora;Data Source=orcl;User Id=username;Password=password;");
var cmd2 = new OleDbCommand("SELECT * FROM tableName WHERE columnName = ?", conn2);

var para2 = new OleDbParameter();
para2.Value = 2;
cmd2.Parameters.Add(para2);
   
conn2.Open();
var reader = cmd2.ExecuteReader();
while (reader != null && reader.Read())
{
    Response.Write(reader[0] + "〈br〉");
    Response.Write(reader["columnName"] + "〈br〉");
}
conn2.Close();

注意事項：OleDbParameter 使用 " ? " 來表示，不用寫參數名稱，多個參數時依序加入

(2) .NET Framework Data Provider for OLE DB 簡易使用方式如下：


// System.Data.OleDb :http://msdn.microsoft.com/en-us/library/6d9ew87b
var conn2 = new OleDbConnection("Provider=OraOLEDB.Oracle;Data Source=orcl;User Id=username;Password=password;OLEDB.NET=True;");
var cmd2 = new OleDbCommand("SELECT * FROM tableName WHERE columnName = :columnName", conn2);
cmd2.Parameters.Add(":columnName", 2);

conn2.Open();
var reader = cmd2.ExecuteReader();
while (reader != null && reader.Read())
{
    Response.Write(reader[0] + "〈br〉");
    Response.Write(reader["columnName"] + "〈br〉");
}
conn2.Close();

注意事項：OleDbParameter 使用 " : " 來表示

3. 使用 TableAdapter：

(1) 新增 DataSet 檔案

(2) 在 Server Explorer 中，新增一個連線，並選擇 Data Source：Oracle Database

(3) 主要有兩個 Data provider ： .NET Framework Data Provider for Oracle 與 .NET Framework Data Provider for OLE DB

(4) 在 DataSet 編輯畫面中，就可以連接 Oracle 資料庫，使用拖拉就可以建立 TableAdapter 即屬於該資料表的查詢

(5) 簡易使用方式：


// 使用 TableAdapter
var adapter = new DataSetNameTableAdapters.tableNameTableAdapter();
var dt = Enumerable.AsEnumerable(adapter.GetDataByColumnName(2));
foreach (DataRow rows in dt)
{
    Response.Write(rows[0] + "〈br〉");
    Response.Write(rows.Field("columnName") + "〈br〉");
}

(6) 注意 .NET Framework Data Provider for Oracle 與 .NET Framework Data Provider for OLE DB 來產生查詢，注意參數名稱是使用 " : " ，呼叫自動產生的 GetDataByXXX 來讀取資料

4. 如果需要 Oracel 官方建議的連接方式，並且取得更多的資源，可以參考 Oracle 網站，並下載 ODAC (Oracle Developer Access Components) for Windows

內建元件包含：

Oracle Developer Tools for Visual Studio 11.2.0.3.0
(Includes support for Entity Framework and LINQ)
Oracle Data Provider for .NET 4 11.2.0.3.0
Oracle Data Provider for .NET 2.0 11.2.0.3.0
Oracle Providers for ASP.NET 4 11.2.0.3.0
Oracle Providers for ASP.NET 2.0 11.2.0.3.0
Oracle Database Extensions for .NET 4 11.2.0.3.0
Oracle Database Extensions for .NET 2.0 11.2.0.3.0
Oracle Provider for OLE DB 11.2.0.3.0
Oracle Objects for OLE 11.2.0.3.0
Oracle Services for Microsoft Transaction Server 11.2.0.3.0
Oracle ODBC Driver 11.2.0.3.0
Oracle SQL*Plus 11.2.0.3.0
Oracle Instant Client 11.2.0.3.0

安裝 ODAC for Windows 後，你就可以在 Visual Stuio 中開發 Oracle 應用程式有較好的整合。

內建有 Oracle Data Provider for .NET 、Oracle Provider for OLE DB 及 Oracle ODBC Driver 三種，所以你也透過這三種方式進行連接。

參考資料：

Integration with Visual Studio: Use Server Explorer with Visual Studio 2010 to browse your Oracle schema and launch one of the many integrated Oracle designers and wizards to create and alter schema objects. Use Microsoft Query Designer to visually design queries. Visual Studio 2008 and 2005 are also supported.
Entity Framework Designers and Wizards: Use Visual Studio 2010's Entity Designer for Database First and Model First object-relational mapping
Automatic .NET Code Generation: Use the Visual Studio Datasources Window, the Dataset Designer, and the TableAdapter Configuration Wizard to drag and drop and automatically generate .NET code.
Easy ASP.NET Web Development: If you're an ASP.NET web developer, ODT makes it easy to generate ASP.NET web applications with minimal coding required.
Powerful Application Tuning Tools: New! Use Oracle Performance Analyzer to tune your .NET application's use of Oracle Database via a single click of a button. The database is then monitored under load and recommendations are made, such as modifications to SQL or adding an index on a table. You can also tune Ad-hoc SQL statements in Query Window with SQL Tuning Advisor.
SQL Script Lifecycle with Source Control Integration: Generate SQL scripts for Oracle schema objects that your .NET application uses, manage them in an Oracle Database Project, check them into source control, edit the scripts in the Oracle SQL Editor, and execute them with a built in SQL*Plus execution engine.
PL/SQL Editor and Debugger: The fully integrated PL/SQL Editor and Debugger allows you to leverage all of your favorite Visual Studio debugging features from within PL/SQL code. You can even seamlessly step from your .NET code into your PL/SQL stored procedure code and back out again!
NET Stored Procedure Deployment: The .NET Deployment Wizard makes it easy to deploy .NET stored procedures and functions into Oracle Database.
Integrated Online Help System: The integrated context sensitive online help, including the Oracle SQL and PL/SQL Users Guides puts the Oracle documentation at your fingertips.
Manage Users, Roles and Privileges: New! Create Users and Roles using graphical designers. Assign privileges to the roles and assign roles to users. View the Users and Roles in Server Explorer.
Oracle Advanced Queuing (AQ) Designers: New! Create, modify and administer your Queues and Queue Tables.
User-Defined Types (UDTs): Create UDTs in Oracle with multiple new designers. A powerful UDT Custom Class code generation wizard makes using UDTs from .NET code easy and fast.
Import Table Wizard: This wizard makes it easy to import tables and their data from Oracle databases, or from external datasources such as Microsoft SQL Server, Microsoft Access and Excel spreadsheets.
Data Editing, Stored Procedure Testing, Adhoc SQL Execution: When testing your .NET application you can use the Oracle Data Window to insert and update Oracle data. There's also a testbed for testing stored procedures and an Oracle Query Window for executing any SQL statement you choose.

Download:
Download ODT 11.2.0.3 New! This release adds new support for Visual Studio Entity Designer!

Watch Video Demonstrations:
Introduction to ODT (External link to Microsoft Developer Network)

Guided Walkthroughs:
Oracle by Example: SQL Tuning Advisor and Oracle Performance Analyzer New!
Oracle by Example: Building ASP.NET Web Applications with ODT
Oracle by Example: Building .NET Applications Using ODT
Oracle by Example: Debugging Oracle PL/SQL from Visual Studio
Oracle by Example: Using Oracle User-Defined Types with .NET and Visual Studio

Read:
Oracle Magazine: Write Messaging Applications with ODP.NET and Oracle Streams Advanced Queuing.New!
Oracle Magazine: Use Visual Studio for the Complete .NET and Oracle Database Development Lifecycle
Oracle Magazine: Build Applications with ODT and Oracle User-Defined Types
ODT FAQ: Answers to Common OTN Discussion Forum Questions

回首頁