Oracle Database Interaction Using ODP.NET and ASP.NET: All Ways to Retrieve Data Continued - Making an offline (connectionless) cache using “OracleDataReader”
(Page 3 of 5 )
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()
Next: Making an offline cache using “OracleDataAdapter” >>
More ASP.NET Articles
More By Jagadish Chaterjee
