Oracle Database Interaction Using ODP.NET and ASP.NET: All Ways to Retrieve Data Continued

The sample downloadable solution (zip) was entirely developed using Visual Studio.NET 2003 Enterprise Architect on Windows Server 2003 Standard Edition together with Oracle 10g (version 10.2).  But, I am confident that it would work with other versions of Windows (which support .NET 1.1) as well.

Single Parameterized queries

In my previous article I explained a few of the methods to retrieve information from an Oracle database.  In this article, we try to cover the rest of the methodologies.  One of them is “Single Parameterized Queries.” 

Let us consider the following example:

Dim cn As New OracleConnection("User ID=scott;password=tiger;Data
Source=ORCL")
        Try
            Dim cmd As New OracleCommand
            cmd.CommandText = "select * from emp where deptno
= :p_deptno"
            cmd.CommandType = CommandType.Text
            cmd.Connection = cn
            Dim pr_deptno As New OracleParameter
            pr_deptno.ParameterName = "p_deptno"
            pr_deptno.OracleDbType = OracleDbType.Int16
            pr_deptno.Value = Convert.ToInt16("10")
            cmd.Parameters.Add(pr_deptno)
            cmd.Connection.Open()
            Me.DataGrid1.DataSource = cmd.ExecuteReader
            Me.DataGrid1.DataBind()
            cmd.Dispose()
        Catch ex As Exception
            Me.lblError.Text = ex.Message
        Finally
            If cn.State = ConnectionState.Open Then
                cn.Close()
            End If
        End Try

From the above example, let us consider only the differential changes from previous methodologies.  Let us consider the following statement:

cmd.CommandText = "select * from emp where deptno = :p_deptno"

We can understand the entire SELECT statement except “:p_deptno”.  It is called as a parameter within the SELECT query.  A parameter could be any name but should be preceded with a colon.  We provide a value for that parameter by using a separate class, “OracleParameter,” as shown in the following:

Dim pr_deptno As New OracleParameter
pr_deptno.ParameterName = "p_deptno"
pr_deptno.OracleDbType = OracleDbType.Int16
pr_deptno.Value = Convert.ToInt16("10")

Finally, we add the parameter value (or the instance of OracleParameter) to the “OracleCommand” object and then execute it as shown in the following:

cmd.Parameters.Add(pr_deptno)
cmd.Connection.Open()
Me.DataGrid1.DataSource = cmd.ExecuteReader
Me.DataGrid1.DataBind()
cmd.Dispose()

Multiple Parameterized queries

The above section already introduced you to working with a single parameter.  Why is a parameter necessary for a query?  It is used to cache the same query (which is being used multiple times with multiple values) at the server side and gets executed any number of times without any round trip back to the client for every value of the same parameter.  If we work without any parameter and provide the same SELECT statements with different values, our client makes a round trip to the server for every SELECT statement.

Now let us consider another example of working with more than one parameter:

Dim cn As New OracleConnection("User ID=scott;password=tiger;Data
Source=ORCL")
        Try
            Dim cmd As New OracleCommand
            cmd.CommandText = "select * from emp where sal
between :p_low and :p_high"
            cmd.CommandType = CommandType.Text
            cmd.Connection = cn
            Dim pr_low As New OracleParameter
            pr_low.ParameterName = "p_low"
            pr_low.OracleDbType = OracleDbType.Double
            pr_low.Value = Convert.ToDouble("1500")
            Dim pr_high As New OracleParameter
            pr_high.ParameterName = "p_high"
            pr_high.OracleDbType = OracleDbType.Double
            pr_high.Value = Convert.ToDouble("3000")
            cmd.Parameters.Add(pr_low)
            cmd.Parameters.Add(pr_high)
            cmd.Connection.Open()
            Me.DataGrid1.DataSource = cmd.ExecuteReader
            Me.DataGrid1.DataBind()
            cmd.Dispose()
        Catch ex As Exception
            Me.lblError.Text = ex.Message
        Finally
            If cn.State = ConnectionState.Open Then
                cn.Close()
            End If
        End Try

Let us observe the parameterized statement first:

cmd.CommandText = "select * from emp where sal between :p_low
and :p_high"

In the above statement, I defined two parameters, where those values must be  passed before executing the command.  I declare those two with the “OracleParameter” class and assign values as follows:

Dim pr_low As New OracleParameter
pr_low.ParameterName = "p_low"
pr_low.OracleDbType = OracleDbType.Double
pr_low.Value = Convert.ToDouble("1500")

Dim pr_high As New OracleParameter
pr_high.ParameterName = "p_high"
pr_high.OracleDbType = OracleDbType.Double
pr_high.Value = Convert.ToDouble("3000")

And finally, I add those parameter values to “OracleCommand” object and execute them as following:

cmd.Parameters.Add(pr_low)
cmd.Parameters.Add(pr_high)
cmd.Connection.Open()
Me.DataGrid1.DataSource = cmd.ExecuteReader
Me.DataGrid1.DataBind()
cmd.Dispose()

In that way we can add as many parameter values as possible to the same parameter and execute the command any number of times to achieve the highest level of performance without having any round trips.

Making an offline (connectionless) cache using “OracleDataReader”

I clearly explained the “OracleDataReader” class in my previous articles.  It is something like a forward-only and read-only result set.  As long as it exists in memory, a separate connection gets dedicated to it.  So, if I have 1000 clients accessing the database using “OracleDataReader”, the server needs to support 1000 simultaneous connections, which is very problematic.  If your server can support 1000 connections, what about 1,000,000 connections? Certainly not.

So, the solution should be something like “get the data offline and forget it.”  Even though there exist several other efficient methods (further successive sections), I would like to show you how we can get the data from “OracleDataReader” offline and then close it (without making the connection exist forever).  In that way, I can explain further about the “OracleDataReader” class.  So let us consider the following example:

Dim cn As New OracleConnection("User ID=scott;password=tiger;Data
Source=ORCL")
        Try
            Dim cmd As New OracleCommand("select * from emp", cn)
            cmd.Connection.Open()
            Dim rdr As OracleDataReader = cmd.ExecuteReader
            Dim dt As New DataTable
            Dim i As Integer
            For i = 0 To rdr.FieldCount - 1
                dt.Columns.Add(rdr.GetName(i))
            Next

            While rdr.Read
                Dim dr As DataRow = dt.NewRow
                For i = 0 To rdr.FieldCount - 1
                    dr(rdr.GetName(i)) = rdr.GetValue(i)
                Next
                dt.Rows.Add(dr)
            End While
            dt.AcceptChanges()
            cmd.Dispose()
            Me.DataGrid1.DataSource = dt
            Me.DataGrid1.DataBind()
            dt.Dispose()
        Catch ex As Exception
            Me.lblError.Text = ex.Message
        Finally
            If cn.State = ConnectionState.Open Then
                cn.Close()
            End If
        End Try

Let me explain this part by part.  We get the information into “OracleDataReader” using the following statements:

Dim cmd As New OracleCommand("select * from emp", cn)
cmd.Connection.Open()
Dim rdr As OracleDataReader = cmd.ExecuteReader

After getting the minimum information from the database, we need to first create an offline cache (using a datatable).  Now we shall create “DataColumns” for the “DataTable” to maintain the offline cache.

Dim dt As New DataTable
Dim i As Integer
For i = 0 To rdr.FieldCount - 1
         dt.Columns.Add(rdr.GetName(i))
Next

After creating the structure for the offline cache, we need to add the rows to the offline cache (and finally dispose of the “OracleCommand” object), which is done using the following code:

While rdr.Read
                Dim dr As DataRow = dt.NewRow
                For i = 0 To rdr.FieldCount - 1
                    dr(rdr.GetName(i)) = rdr.GetValue(i)
                Next
                dt.Rows.Add(dr)
            End While
            dt.AcceptChanges()
            cmd.Dispose()

Making an offline cache using “OracleDataAdapter”

Even though, the above method works well, it has its own drawbacks. If we have 10,000 rows,  for example, it takes a long time to fill with all the rows (even though it is sometimes unnecessary).  With  the “OracleDataAdapter” object, it just fetches based on the “fetchsize” property dynamically, only when necessary.  The following example illustrates this:

        Dim cn As New OracleConnection("User
ID=scott;password=tiger;Data Source=ORCL")
        Try
            Dim da As New OracleDataAdapter("select * from
scott.emp", cn)
            Dim dt As New DataTable
            da.Fill(dt)
            da.Dispose()
            Me.DataGrid1.DataSource = dt
            Me.DataGrid1.DataBind()
            dt.Dispose()
        Catch ex As Exception
            Me.lblError.Text = ex.Message
        Finally
            If cn.State = ConnectionState.Open Then
                cn.Close()
            End If
        End Try

The main statements from the above code fragment are only the following:

Dim da As New OracleDataAdapter("select * from scott.emp", cn)
Dim dt As New DataTable
da.Fill(dt)
da.Dispose()

The above makes all the rows (based on the built-in “fetchsize” property) offline with only four lines.  This method is quite simple and quite efficient when compared with the previous method, and is very suitable to work in production scenarios.

Parameterized queries using “OracleDataAdapter”

The above method works quite well with the SELECT statement.  But what if we want to work with parameterized queries using the “OracleDataAdapter” class?  Not only that, the same method would also help you to work with Stored Procedures or Stored Functions.  Let us consider the following example:

Dim cn As New OracleConnection("User ID=scott;password=tiger;Data
Source=ORCL")
        Try
            Dim cmd As New OracleCommand
            cmd.CommandText = "select * from emp where deptno
= :p_deptno"
            cmd.CommandType = CommandType.Text
            cmd.Connection = cn

            Dim pr_deptno As New OracleParameter
            pr_deptno.ParameterName = "p_deptno"
            pr_deptno.OracleDbType = OracleDbType.Int16
            pr_deptno.Value = Convert.ToInt16("10")

            cmd.Parameters.Add(pr_deptno)           

            Dim da As New OracleDataAdapter
            da.SelectCommand = cmd

            Dim dt As New DataTable
            da.Fill(dt)
            da.Dispose()
            Me.DataGrid1.DataSource = dt
            Me.DataGrid1.DataBind()
            dt.Dispose()
        Catch ex As Exception
            Me.lblError.Text = ex.Message
        Finally
            If cn.State = ConnectionState.Open Then
                cn.Close()
            End If
        End Try

In the above code fragment, everything is similar except the following statement:

            da.SelectCommand = cmd

This is the only trick I used to bind the “OracleCommand” object to the “OracleDataAdapter” object.  When the statement “da.Fill(dt)” gets executed, the adapter executes “OracleCommand” and the data gets filled into the “DataTable”.

In the next article, we will look into manipulating the data using ODP.NET.  Any comments, suggestions, bugs, errors, feedback, and so on are highly appreciated at jag_chat@yahoo.com.