Questions That Should Be Asked More Frequently

The Oracle Client .NET Data Provider

As of this writing, Microsoft is developing a .NET data provider for Oracle databases. This provider will communicate with Oracle databases version 8i and later and will allow you to access the newer Oracle data types, such as LOBs and BFILEs. The provider will also allow you to fetch the contents of multiple REF cursors from a stored procedure.

Microsoft has not indicated how or when the Oracle Client .NET Data Provider will be released or whether it will be a separate component or part of a subsequent release of the .NET Framework. If the provider is not released as part of the .NET Framework, you'll need to add a reference to the component in your projects, as described earlier for the ODBC .NET Data Provider. At the time of this writing, the provider's namespace is Microsoft.Data.OracleClient.

The Oracle Client .NET Data Provider will communicate with your Oracle database using Oracle's client libraries. In order to connect to your Oracle database using the Oracle Client .NET Data Provider, you'll have to have the Oracle client components (version 8.1.7 or later) installed. You'll also have to create a database alias for each database to which you want to connect using the Oracle's client configuration utility.

The code snippets in this section assume that you've added a reference to the Oracle Client .NET Data Provider in your project as well as your code module, using the construct for your language of choice (the Imports command for Visual Basic .NET developers and the using command for Visual C# .NET developers).

Connecting to Your Oracle Database Using an OracleConnection

You'll use the OracleConnection object to connect to your Oracle database. As with the other .NET data providers, to connect to your Oracle database you'll simply need to create an OracleConnection object, set its ConnectString property (either explicitly or via the object's constructor), and call its Open method, as shown in the following code snippet.

Visual Basic .NET

Dim strConn As String strConn = "Data Source=MyOracleDatabaseAlias;" & _           "User ID=MyUserID;Password=MyPassword;" Dim cn As New OracleConnection(strConn) cn.Open() cn.Close()

Visual C# .NET

string strConn; strConn = "Data Source=MyOracleDatabaseAlias;" +           "User ID=MyUserID;Password=MyPassword;"; OracleConnection cn = new OracleConnection(strConn); cn.Open(); cn.Close();

Working with Parameterized Queries

The Oracle Client .NET Data Provider will support only named parameters, much like the SQL Client .NET Data Provider does. The one difference is that you'll have to preface your parameter names with a colon. A parameterized query would look like this:

SELECT EMPNO, ENAME FROM EMP WHERE JOB = :JOB

Retrieving the Results of a Query Using an OracleDataAdapter

Let's look at an example of code that uses an OracleDataAdapter to fill a DataTable based on the results of this parameterized query.

Visual Basic .NET

Dim strConn, strSQL As String strConn = "Data Source=MyOracleDatabaseAlias;" & _           "User ID=MyUserID;Password=MyPassword;" strSQL = "SELECT EMPNO, ENAME FROM EMP WHERE JOB = :JOB" Dim da As New OracleDataAdapter(strSQL, strConn) Dim param As OracleParameter param = da.SelectCommand.Parameters.Add(":JOB", OracleType.VarChar, 9) param.Value = "CLERK" Dim tbl As New DataTable() da.Fill(tbl) Console.WriteLine("Retrieved " & tbl.Rows.Count & " row(s)")

Visual C# .NET

string strConn, strSQL; strConn = "Data Source=MyOracleDatabaseAlias;" +           "User ID=MyUserID;Password=MyPassword;"; strSQL = "SELECT EMPNO, ENAME FROM EMP WHERE JOB = :JOB"; OracleDataAdapter da = new OracleDataAdapter(strSQL, strConn); OracleParameter param; param = da.SelectCommand.Parameters.Add(":JOB", OracleType.VarChar, 9); param.Value = "CLERK"; DataTable tbl = new DataTable(); da.Fill(tbl); Console.WriteLine("Retrieved " + tbl.Rows.Count + " row(s)");

Examining the Results of a Query Using an OracleDataReader

Now let's see how to retrieve that same data using an OracleDataReader.

Visual Basic .NET

Dim strConn, strSQL As String strConn = "Data Source=MyOracleDatabaseAlias;" & _           "User ID=MyUserID;Password=MyPassword;" strSQL = "SELECT EMPNO, ENAME FROM EMP WHERE JOB = :JOB" Dim cn As New OracleConnection(strConn) Dim cmd As New OracleCommand(strSQL, cn) Dim param As OracleParameter param = cmd.Parameters.Add(":JOB", OracleType.VarChar, 9) param.Value = "CLERK" cn.Open() Dim rdr As OracleDataReader = cmd.ExecuteReader() Do While rdr.Read()     Console.WriteLine("EmpNo = " & rdr.GetDecimal(0))     Console.WriteLine("EName = " & rdr.GetString(1))     Console.WriteLine() Loop rdr.Close() cn.Close()

Visual C# .NET

string strConn, strSQL; strConn = "Data Source=MyOracleDatabaseAlias;" +           "User ID=MyUserID;Password=MyPassword;"; strSQL = "SELECT EMPNO, ENAME FROM EMP WHERE JOB = :JOB"; OracleConnection cn = new OracleConnection(strConn); OracleCommand cmd = new OracleCommand(strSQL, cn); OracleParameter param; param = cmd.Parameters.Add(":JOB", OracleType.VarChar, 9); param.Value = "CLERK"; cn.Open(); OracleDataReader rdr = cmd.ExecuteReader(); while (rdr.Read()) {     Console.WriteLine("EmpNo = " + rdr.GetDecimal(0));     Console.WriteLine("EName = " + rdr.GetString(1));     Console.WriteLine(); } rdr.Close(); cn.Close();

Oracle-Specific Data Types

The Oracle Client .NET Data Provider will include Oracle-specific data types, much like the SQL Client .NET Data Provider does for SQL Server data types. Using these Oracle-specific data types will improve the performance of your code and allow you to retrieve data from your DataReader more quickly because you'll be able to store data in these data types without having to perform checks for Null values ahead of time. Plus, many of these data types will offer additional functionality not available on the corresponding .NET data type.

The following code snippet uses the Oracle-specific data types to retrieve and display the results of a query:

Visual Basic .NET

Dim strConn, strSQL As String strConn = "Data Source=MyOracleDatabaseAlias;" & _           "User ID=MyUserID;Password=MyPassword;" strSQL = "SELECT EMPNO, ENAME FROM EMP WHERE JOB = :JOB" Dim cn As New OracleConnection(strConn) Dim cmd As New OracleCommand(strSQL, cn) Dim param As OracleParameter param = cmd.Parameters.Add(":JOB", OracleType.VarChar, 9) param.Value = "CLERK" Dim numEmpNo As OracleNumber Dim strEName As OracleString cn.Open() Dim rdr As OracleDataReader = cmd.ExecuteReader() Do While rdr.Read()     numEmpNo = rdr.GetOracleNumber(0)     strEName = rdr.GetOracleString(1)     Console.WriteLine("EmpNo = " & numEmpNo.ToString())     Console.WriteLine("EName = " & strEName.ToString())     Console.WriteLine() Loop rdr.Close() cn.Close()

Visual C# .NET

string strConn, strSQL; strConn = "Data Source=MyOracleDatabaseAlias;" +           "User ID=MyUserID;Password=MyPassword;"; strSQL = "SELECT EMPNO, ENAME FROM EMP WHERE JOB = :JOB"; OracleConnection cn = new OracleConnection(strConn); OracleCommand cmd = new OracleCommand(strSQL, cn); OracleParameter param; param = cmd.Parameters.Add(":JOB", OracleType.VarChar, 9); param.Value = "CLERK"; OracleNumber numEmpNo; OracleString strEName; cn.Open(); OracleDataReader rdr = cmd.ExecuteReader(); while (rdr.Read()) {     numEmpNo = rdr.GetOracleNumber(0);     strEName = rdr.GetOracleString(1);     Console.WriteLine("EmpNo = " + numEmpNo.ToString());     Console.WriteLine("EName = " + strEName.ToString());     Console.WriteLine(); } rdr.Close(); cn.Close();

Calling a Stored Procedure

You can set an OracleCommand object's CommandText property to your stored procedure name and then add parameters to the Command object's Parameters collection. When you add stored procedure parameters in this fashion, you need not preface the parameter name with a colon when you're populating the Parameters collection. You then simply call the ExecuteNonQuery method, as shown in the following code snippet.

Visual Basic .NET

Dim strConn As String strConn = "Data Source=MyOracleDatabaseAlias;" & _           "User ID=MyUserID;Password=MyPassword;" Dim cn As New OracleConnection(strConn) Dim cmd As New OracleCommand("GetNumOrders", cn) cmd.CommandType = CommandType.StoredProcedure Dim param As OracleParameter param = cmd.Parameters.Add("pCustomerID", OracleType.Char, 5) param.Value = "ALFKI" param = cmd.Parameters.Add("pNumOrders", OracleType.Int32) param.Direction = ParameterDirection.Output cn.Open() cmd.ExecuteNonQuery() Console.WriteLine(param.Value) cn.Close()

Visual C# .NET

string strConn; strConn = "Data Source=MyOracleDatabaseAlias;" +           "User ID=MyUserID;Password=MyPassword;"; OracleConnection cn = new OracleConnection(strConn); OracleCommand cmd = new OracleCommand("GetNumOrders", cn); cmd.CommandType = CommandType.StoredProcedure; OracleParameter param; param = cmd.Parameters.Add("pCustomerID", OracleType.Char, 5); param.Value = "ALFKI"; param = cmd.Parameters.Add("pNumOrders", OracleType.Int32); param.Direction = ParameterDirection.Output; cn.Open(); cmd.ExecuteNonQuery(); Console.WriteLine(param.Value); cn.Close();

You can avoid the overhead of having the provider translate this information into the appropriate Oracle syntax by leaving the CommandType property as Text and setting the CommandText property to a query in the following format:

BEGIN GetNumOrders(:pCustomerID, :pNumOrders); END;

Fetching Data from Oracle REF Cursors

The Oracle Client .NET Data Provider will let you retrieve data from multiple REF cursors from a stored procedure call. Say you have the following definition for your Oracle package:

CREATE PACKAGE PackCursorTest AS   TYPE curOrders IS REF CURSOR RETURN Orders%ROWTYPE;   TYPE curDetails IS REF CURSOR RETURN Order_Details%ROWTYPE;   PROCEDURE OrdersAndDetailsForCustomer     (pCustomerID IN CHAR, pOrders OUT curOrders, pDetails OUT curDetails); END; CREATE PACKAGE BODY PackCursorTest AS     PROCEDURE OrdersAndDetailsForCustomer   (     pCustomerID IN CHAR,     pOrders OUT curOrders,     pDetails OUT curDetails   )   AS   BEGIN     OPEN pOrders FOR SELECT * FROM Orders WHERE CustomerID = pCustomerID;     OPEN pDetails FOR SELECT * FROM Order_Details WHERE OrderID IN       (SELECT OrderID FROM Orders WHERE CustomerID = pCustomerID);   END; END;

You could use the following code to call the stored procedure and fetch the contents of both REF cursors into a single DataSet:

Visual Basic .NET

Dim strConn, strSQL As String strConn = "Data Source=MyOracleDatabaseAlias;" & _           "User ID=MyUserID;Password=MyPassword;" Dim cn As New OracleConnection(strConn) strSQL = "PackCursorTest.OrdersAndDetailsForCustomer" Dim cmd As New OracleCommand(strSQL, cn) cmd.CommandType = CommandType.StoredProcedure Dim param As OracleParameter param = cmd.Parameters.Add("pCustomerID", OracleType.Char, 5) param.Value = "ALFKI" param = cmd.Parameters.Add("pOrders", OracleType.Cursor) param.Direction = ParameterDirection.Output param = cmd.Parameters.Add("pDetails", OracleType.Cursor) param.Direction = ParameterDirection.Output Dim da As New OracleDataAdapter(cmd) da.TableMappings.Add("Table", "Orders") da.TableMappings.Add("Table1", "Order_Details") Dim ds As New DataSet() Dim tbl As DataTable da.Fill(ds) For Each tbl In ds.Tables     Console.WriteLine(tbl.TableName & " now has " & _                       tbl.Rows.Count & " row(s)") Next tbl

Visual C# .NET

string strConn, strSQL; strConn = "Data Source=MyOracleDatabaseAlias;" +           "User ID=MyUserID;Password=MyPassword;"; OracleConnection cn = new OracleConnection(strConn); strSQL = "PackCursorTest.OrdersAndDetailsForCustomer"; OracleCommand cmd = new OracleCommand(strSQL, cn); cmd.CommandType = CommandType.StoredProcedure; OracleParameter param; param = cmd.Parameters.Add("pCustomerID", OracleType.Char, 5); param.Value = "ALFKI"; param = cmd.Parameters.Add("pOrders", OracleType.Cursor); param.Direction = ParameterDirection.Output; param = cmd.Parameters.Add("pDetails", OracleType.Cursor); param.Direction = ParameterDirection.Output; OracleDataAdapter da = new OracleDataAdapter(cmd); da.TableMappings.Add("Table", "Orders"); da.TableMappings.Add("Table1", "Order_Details"); DataSet ds = new DataSet(); da.Fill(ds); foreach (DataTable tbl in ds.Tables)     Console.WriteLine(tbl.TableName + " now has " +                        tbl.Rows.Count + " row(s)");

Retrieving Database Schema Information

The Oracle Client .NET Data Provider will not directly provide schema discovery features to allow you to fetch information such as table and column names from your Oracle database. However, you'll be able to query the Oracle data dictionary to retrieve this information. For example, the following two queries retrieve a list of tables and a list of columns from the database:

SELECT TABLE_NAME FROM USER_TABLES SELECT TABLE_NAME, COLUMN_NAME FROM USER_TAB_COLUMNS     ORDER BY TABLE_NAME

For more information on using the Oracle data dictionary, see your Oracle documentation.



Microsoft ADO. NET Core Reference
Microsoft ADO.NET (Core Reference) (PRO-Developer)
ISBN: 0735614237
EAN: 2147483647
Year: 2002
Pages: 104
Authors: David Sceppa

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net