Using the Microsoft Managed Provider for Oracle...

Microsoft has brought the power and speed of the Managed Provider for SQL Server to the Oracle database. In this article we see how to use it directly, and to call a stored procedure with a join.

By: John Kilgo Date: March 14, 2003 Download the code.

The SqlClient Managed Provider is clearly faster than the OleDb connection class for Sql Server. Until recently, if you used Oracle you were stuck with OleDb as the best way to connect. Now, however, Microsoft has made available a Managed Provider for Oracle databases as well. (Oracle just recently released their own version - perhaps in a later article we can take a look at their class also.) Since the Managed Provider for Oracle was not a part of the original framework, you must download it from the following address: http://www.microsoft.com/downloads/details.aspx?FamilyID=4f55d429-17dc-45ea-bfb3-076d1c052524&DisplayLang=en.

Once you have downloaded and installed the provider (in the form of a DLL) you must add a reference for it in Visual Studio .Net. You do this by right-clicking on References in the Solution Explorer. Once you do that, click on Add Reference. That will bring up a dialog box with three tabs. The .Net tab should be selected by default. Select it if not. Scroll down the list of Component Names until you come to "System.Data.OracleClient.Dll". Click on the file name to highlight it and then click on the "Select" button at the upper right of the form. Then click the "OK" button at the bottom of the form. You should now see the class listed in your References in Solution Explorer. That should be all you need to do - but maybe not! On some machines (including mine [argh!]) the Oracle managed provider class stuff would show up in Intellisense when in code-behind, but when I attempted to run the program I got a configuration error saying (at the first line it came to where the OracleClient was referenced) "Namespace or type 'OracleClient' for the Imports 'System.Data.OracleClient' cannot be found. Intellisense knew about the class, but somehow the runtime did not. After a lot of knashing of teeth I opened up the References tree in Solution Explorer, right-clicked on the "System.Data.OracleClient" reference and selected Properties. In the Properties window one of the properties is "Local Copy" True|False. I changed it to True. That resulted in a copy of the System.Data.OracleClient.DLL being placed in my bin directory. After that my problems went away. Since then I've heard from a couple of people who have had to do the same thing, although most have not. Apparently there is some configuration issue that I've yet to figure out.

Now to some code. In this first example, we are just going to connect to Oracle (the Scott / Tiger database that ships with Oracle) and bring back the rows in the emp table. We will use a very basic (and ugly) datagrid here. The .aspx file code is below.

<%@ Page Language="vb" Src="OracleDirect.aspx.vb" Inherits="OracleDirect" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>
<title>OracleDirect</title>
<meta name="GENERATOR" content="Microsoft Visual Studio.NET 7.0">
<meta name="CODE_LANGUAGE" content="Visual Basic 7.0">
<meta name=vs_defaultClientScript content="JavaScript">
<meta name=vs_targetSchema content="http://schemas.microsoft.com/intellisense/ie5">
</head>
<body MS_POSITIONING="GridLayout">
<form id="Form1" method="post" runat="server">
<asp:DataGrid id="dtgOracle" runat="server" cellpadding="4" />
</form>
</body>
</html>

Now for the code-behind file where we see the managed provider class in use. Actually there is no magic to it. If you have been using OleDb to connect to Oracle all along, all you do is change the OleDb prefix to object names to an Oracle prefix. In other words OleDbConnection becomes OracleConnection, OleDbDataAdapter becomes OracleDataAdapter. If you are coming from Sql Server and have been using its managed provider you just change "Sql" prefixes to "Oracle" prefixes. That's all there is to it. Keep in mind that in the connection string you will have to use the correct Data Source for your setup. I doubt you have one named "kilgo"!

Imports System
Imports System.Data
Imports System.Data.OracleClient

Public Class OracleDirect : Inherits System.Web.UI.Page

  Protected dtgOracle As System.Web.UI.WebControls.DataGrid

  Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    Dim objConn As OracleConnection
    Dim dataAdapter As OracleDataAdapter
    Dim dataSet as New DataSet()
    Dim strSql As String
    Dim strConn As String = "Data Source=kilgo;User Id=scott;Password=tiger;"

    objConn = New OracleConnection(strConn)
    objConn.Open()

    strSql = "SELECT * FROM emp"

    dataAdapter = New OracleDataAdapter(strSql, objConn)
    dataAdapter.Fill(dataSet)
    dtgOracle.DataSource = dataSet
    dtgOracle.DataBind()

  End Sub

End Class

In this next example we will return a resultset using a stored procedure which generates a ref cursor. We will also pass in a parameter so that we can see how that works also. The resultset will be the result of a join with the scott/tiger dept table. The above may seem to be making things a little complicated, but actually it is pretty simple if you just follow the code carefully. We might as well show as many techniques as we can while we are at it. The code that follows will include a simple .aspx page, a code-behind page, and a pl/sql package containing the stored procedure. First the .aspx page (with a little prettier grid this time).

<%@ Page Language="vb" Src="OracleStoredProc.aspx.vb" Inherits="OracleStoredProc" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>
<title>OracleStoredProc</title>
<meta name="GENERATOR" content="Microsoft Visual Studio.NET 7.0">
<meta name="CODE_LANGUAGE" content="Visual Basic 7.0">
<meta name=vs_defaultClientScript content="JavaScript">
<meta name=vs_targetSchema content="http://schemas.microsoft.com/intellisense/ie5">
</head>
<body MS_POSITIONING="GridLayout">
<form id="Form1" method="post" runat="server">
<asp:DataGrid id="dtgOracle" style="Z-INDEX: 102; LEFT: 185px; POSITION: absolute; TOP: 37px"
          runat="server"
          BorderColor="#CCCCCC"
          BorderStyle="None"
          BorderWidth="1px"
          BackColor="White"
          CellPadding="3">
  <ItemStyle ForeColor="#000066" />
  <HeaderStyle Font-Bold="True" ForeColor="White" BackColor="#006699" />
</asp:DataGrid>
</form>
</body>
</html>

Now for the code-behind file. Things are a little more involved here than in the first program, but the same process holds true. Substitute "Oracle" for "Sql" or "OleDb" in the database handling object names and you are home. Of course, again, you will have to use a Data Source correct for your setup rather than "kilgo" that I used in my connection string. Also, I normally put my connection strings in Web.config but have left it in the code here for demonstration purposes.

Notice the fourth line under Page_Load (objCmd.CommandText="scott.employee.get_emp_info"). That is a fully qualified path to the stored procedure we are using to return the result set. "scott" is the schema (database) name. "employee" is what I named the PL/SQL Package, and "get_emp_info" is what I named the procedure. Two lines below that we add a parameter named pDeptNo and pass in the value of 30. We are asking the stored procedure to give us only employees who work in dept 30 (you will see it in the WHERE clause when we get to the stored procedure). In the next line we let the system know that we are expecting "pCursor" to be an Output parameter. The rest of the code is pretty straight forward.

Imports System.Data
Imports System.Data.OracleClient

Public Class OracleStoredProc : Inherits System.Web.UI.Page

  Protected dtgOracle As System.Web.UI.WebControls.DataGrid

  Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    Dim objConn As New OracleConnection("Data Source=kilgo;User Id=scott;Password=tiger;")
    Dim objCmd As New OracleCommand
    objCmd.Connection = objConn
    objCmd.CommandText = "scott.employee.get_emp_info"
    objCmd.CommandType = CommandType.StoredProcedure
    objCmd.Parameters.Add("pDeptNo", 30)
    objCmd.Parameters.Add("pCursor", System.Data.OracleClient.OracleType.Cursor)
    objCmd.Parameters("pCursor").Direction = ParameterDirection.Output
    Dim dataAdapter As New OracleClient.OracleDataAdapter(objCmd)
    Dim dataSet As New DataSet()
    Try
      dataAdapter.Fill(dataSet)
      dtgOracle.DataSource = dataSet
      dtgOracle.DataBind()
    Finally
      If objConn.State = ConnectionState.Open Then
        objConn.Close()
      End If
      objConn.Dispose()
    End Try
  End Sub

End Class

Following are the package body and spec containing the stored procedure. I'll not go into much detail explaining the code. If you are reading this you probably already have some experience with Oracle and don't need my explanation. You can ping me at webmaster@dotnetjohn.com if you have any questions about the code.

Package Body:

PACKAGE BODY EMPLOYEE
IS
PROCEDURE GET_EMP_INFO ( pDeptNo IN Number, pCursor OUT outputCursor )
IS
BEGIN
OPEN pCursor FOR
  SELECT a.empno, a.ename, a.job, a.mgr, a.hiredate, a.sal, a.comm, a.deptno, b.dname, b.loc
  FROM emp a, dept b
  WHERE a.deptno = pDeptNo
  AND a.deptno = b.deptno;

END; -- Procedure GET_EMP_INFO

END; -- Package EMPLOYEE

Package Spec:

PACKAGE EMPLOYEE
IS
TYPE outputCursor IS REF CURSOR;
PROCEDURE GET_EMP_INFO ( pDeptNo IN Number, pCursor OUT outputCursor );

END; -- Package spec

Unfortunately I cannot let you demo the programs. My web host does not support Oracle. Hopefully you can use the downloaded code to experiment with this nice new feature of .net data handling for Oracle. You've seen how to return a simple resultset and also how to return a ref cursor from a stored procedure using an input parameter. Good luck with your use of this exciting new addition to .net.

You may download the code here.