WindowsDevCenter.com
oreilly.comSafari Books Online.Conferences.

advertisement


AddThis Social Bookmark Button

Multiple ResultSets in ADO.NET
Pages: 1, 2

The SQL Server DataSet Solution

The SqlDataSet solution is also very similar to the earlier SqlDataReader solution. The only difference is that we’re going to fill the DataSet with the lookup data and bind the DataTable inside the DataSet with the dropdown server controls. Let’s look at the code.



What we’ve done here is that, after opening the database connection, we’re creating a new SqlDataAdopter object with the open database connection object and the batch SQL statement. Then we’re calling the Fill method of the SqlDataAdopter object and we’re passing the DataSet objects name.


...
  Dim strSQL as String = _
  "Select * from Tbl_Lu_Priorities Order By PriorityTitle;
  Select * from Tbl_Lu_Statuses Order By Status;
  Select * from Tbl_Ma_Users Order By Username;"

  Dim DS as New DataSet()
  Dim cnAdapter as new SqlDataAdapter(strSQL, cnConnection)
  cnAdapter.Fill(DS)
  

This will create three DataTable objects inside the DataSet object with names such as Table, Table1, and Table2.

Then we’re binding individual DataTable objects with each dropdown server control object:


  With ID1
    .DataSource = DS.Tables("Table")
    .DataValueField = "PK_Priority_id"
    .DataTextField = "PriorityTitle"
    .DataBind()
  End With

  With ID2
    .DataSource = DS.Tables("Table1")
    .DataValueField = "Pk_Status_ID"
    .DataTextField = "Status"
    .DataBind()
  End With

  With ID3
    .DataSource = DS.Tables("Table2")
    .DataValueField = "PK_User_ID"
    .DataTextField = "Username"
    .DataBind()
  End With
  ...
  

The Oracle Solution

So far, so good. Every things works fine and smoothly. Here comes the Oracle surprise: if we want to execute a batch query with Oracle database, you can’t give bunch of SQL statements in a string; the Oracle database will not accept it as a valid SQL statement. The only way the Oracle database can handle the batch select problem is using an Oracle package with a Ref Cursor.

Tip: the Oracle package is a special kind of stored procedure that can group several stored functions, PL/SQL Types, and Items. A ref cursor is a PL/SQL data type that you can use in a query to fetch data. Each ref cursor query is associated with a PL/SQL function that returns a strongly-typed ref cursor.

The Oracle Package

Therefore, to make Oracle work with the batch select method, we have to write an Oracle Package first. Every Oracle Package has two parts; the first is the Package header and the second is the package body. Here is the code.


CREATE OR REPLACE PACKAGE IssuesDBPKG AS
  TYPE IssCur IS REF CURSOR;
  PROCEDURE GetLookupValues (PriCur OUT IssCur, 
                             StaCur OUT IssCur, 
                             UsrCur OUT IssCur);
END IssuesDBPKG;
/
  

In the package header, we’ve identified the function name with its parameters. As you can see, we’ve specified that all three parameters are output Ref cursors.


CREATE OR REPLACE PACKAGE BODY IssuesDBPKG AS
  PROCEDURE GetLookupValues (PriCur OUT IssCur, 
                             StaCur OUT IssCur, 
                             UsrCur OUT IssCur)
  IS
    LocalPriCur IssCur;
    LocalStaCur IssCur;
    LocalUsrCur IssCur;
  BEGIN
    OPEN LocalPriCur FOR 
        Select * from Tbl_Lu_Priorities Order By PriorityTitle;
    OPEN LocalStaCur FOR 
        Select * from Tbl_Lu_Statuses Order By Status;
    OPEN LocalUsrCur FOR 
        Select * from Tbl_Ma_Users Order By Username;

    PriCur := LocalPriCur;
    StaCur := LocalStaCur;
    UsrCur := LocalUsrCur;
  END GetLookupValues;
END IssuesDBPKG;
/
  

In the body of the package, we’ve declared three ref cursor objects and we’ve opened the cursors in the body of the package. Then we’ve assigned the open cursors into the return variable declared at the procedure signature.

Well, let's look at how to implement the Oracle package with Oracle drivers. If you look at the Oracle Drivers today, there are plenty of ways you can access an Oracle database, such as ODBC, OLE DB, .NET Managed Provider for Oracle from Microsoft, and ODP.NET (a .NET Oracle native driver from Oracle which is still in Beta 2). Since the availability of the Microsoft driver, I’ll use the .NET Managed Provider for Oracle to demonstrate the example.

Tip: the MS Oracle driver is a separate download for .NET Version 1.0, and you can download it here. But for .NET version 1.1, it’s going to be in the same bundle.

.NET Managed Provider for Oracle

If you’ve never used the .NET Managed Provider for Oracle, then I’d like to give you a brief introduction. The .NET Managed Provider for Oracle is bundled in the System.Data.OracleClient.Dll assembly that is very similar to System.Data.SqlClient.Dll for SQL Server. All of the Oracle ADO.NET Objects start with the keyword Oracle, such as OracleDataReader, OracleCommand, OracleConnection, etc.

If you use ODP.NET all the ADO.NET classes start with the keyword "Ora." Only Oracle can say why this is. I guess it is their trademark, since their OLE DB Driver starts with Ora, and if you look at Oracle Objects of OLE, they all start with Ora. I wish Oracle would follow the same naming conversion as Microsoft, so that if I’m not satisfied with the Microsoft .NET driver then I could just replace the Microsoft driver with Oracle's driver, or another third-party driver, and my code will work. Anyway, it is just a thought.

To use the .NET Managed provider, you have to import the namespace System.Data.OracleClient as you’d do for any provider.

The OracleDataReader Solution

Well, lets look at how to implement the Oracle package with OracleDataReader object. If you compare the Oracle DataReader solution with SQL Server, there are no huge changes at all. After connecting to the Oracle database using the OracleConnection objects, we’ve created a new OracleCommand object. Then we’ve assigned the Oracle package name and the parameter information to the OracleCommand object. Then we’re calling the ExecuteReader() method of the OracleCommand object to get the DataReader object. The remaining code stays the same.


Protected Sub Page_Load(Sender As Object, E As EventArgs)
  Dim cnConnection as OracleConnection = _
           New OracleConnection("User ID=Ora9i;
                                 Password=!Idontknow2002;
                                 Data Source=ORCL")
  cnConnection.Open()

  Dim cnCommand As new OracleCommand()

  With cnCommand
    .Connection = cnConnection
    .CommandText = "IssuesDBPKG.GetLookupValues"
    .CommandType = CommandType.StoredProcedure
    .Parameters.Add(New OracleParameter("PriCur", _
                    OracleType.Cursor)).Direction = _
                                       ParameterDirection.Output
    .Parameters.Add(New OracleParameter("StaCur", _
                    OracleType.Cursor)).Direction = _
                                       ParameterDirection.Output
    .Parameters.Add(New OracleParameter("UsrCur", _
                    OracleType.Cursor)).Direction = _
                                       ParameterDirection.Output
  End With

  'Can't use the CommandBehavior.CloseConnection attribute
  'since the NextResult() method will connect to the database
  'to get the next set of result set.

  Dim Rdr as OracleDataReader = cnCommand.ExecuteReader()
  

The Oracle DataSet Solution

If we compare the Oracle DataSet solution with the SQL Server solution, it is not much different at all. All we’ve done is add the Oracle package information to the OracleCommand object, and we’ve executed the Oracle package instead of the dynamic SQL that we did in the SQL Server solution.


Protected Sub Page_Load(Sender As Object, E As EventArgs)
  Dim cnConnection as OracleConnection = _
        New OracleConnection("User ID=Ora9i;
                              Password=!Idontknow2002;
                              Data Source=ORCL")
  cnConnection.Open()

  Dim cnCommand As new OracleCommand()

  With cnCommand
    .Connection = cnConnection
    .CommandText = "IssuesDBPKG.GetLookupValues"
    .CommandType = CommandType.StoredProcedure
    .Parameters.Add(New OracleParameter("PriCur", _
                 OracleType.Cursor)).Direction = _
                                       ParameterDirection.Output
    .Parameters.Add(New OracleParameter("StaCur", _
                 OracleType.Cursor)).Direction = _
                                       ParameterDirection.Output
    .Parameters.Add(New OracleParameter("UsrCur", _
                 OracleType.Cursor)).Direction = _
                                       ParameterDirection.Output
  End With

  Dim DS as New DataSet()
  Dim cnAdapter as new OracleDataAdapter(cnCommand)
  cnAdapter.Fill(DS)
  

Other than that, both of the solutions are very compatible.

Conclusion

Every database project involves with lookup data and most of the time we end up querying the database more than once to get the lookup data. In this article, I’ve shown a very cool way to get all of the lookup data in one roundtrip from the database server. Based on your applications requirement, you can either implement a DataReader or a DataSet solution.

I’ve also tried to do the same solution with Access and MySql databases. Unfortunately, both MS Access and MySql drivers didn’t understand the batch dynamic SQL statements and both of them don’t support stored procedures (MS Access does support stored queries, but can’t store multiple separate queries in the same object).

Srinivasa Sivakumar is a software consultant, developer, and writer. He specializes in web and mobile technologies using Microsoft solutions.


Return to ONDotnet.com