A downloadable file for this article is available here.
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.
What classes are generally used?
My previous article mainly concentrated on connecting to an Oracle database in several possible ways. I also introduced you the concept of ODP.NET in my previous article. In this article I mainly concentrate on fetching information from an Oracle 10g (10.2) database using ODP.NET.
By now, you should know that you need to work with the Oracle.DataAccess.Client namespace to use ODP.NET. The same namespace has several classes, collections, and enumerated types to work conveniently with both SQL and PL/SQL of the Oracle 10g product. It even offers much tighter integration to .NET, when compared with Microsoft’s .NET provider for Oracle (System.data.OracleClient namespace).
The most generic classes in ODP.NET to retrieve information from an Oracle database include the following:
OracleConnection
OracleCommand
OracleDataReader
OracleDataAdapter
OracleParameter
OracleParameterCollection
Of course, any .NET data provider would provide a similar interface to the database (conforming to the standards of .NET data provider specification). The “OracleConnection” class is used to make a connection (for both dedicated or shared connections) to an Oracle database. Without the help of the OracleConnection” class, all the rest would do nothing (because we would not be able to connect at all).
The “OracleCommand” class is specially used to issue a command (either SELECT or DML or to execute stored procedure and so on) to the Oracle database for some operation. If we pass a SELECT statement (or execute a stored function returning some rows, and so forth) to an instance of the “OracleCommand” class, our application may receive some data (either in the form of rows, or a single value, or bytes, and so on). The data, which has been returned by “OracleCommand”, can be consumed using any of the “OracleDataReader” or “OracleDataAdapter” classes.
“OracleDataReader” is connection oriented. It is something like a forward-only and read-only resultset. “OracleDataAdapter” is generally used in connection-less scenarios. Don’t confuse this with “OracleDataAdapter”, as if it could retrieve even without connecting to the database! It indeed connects (through “OracleConnection”) to the database, passes a command (of type “OracleCommand”), fetches the information, files it into either “datatable” or “dataset” and finally breaks the “OracleConnection” itself. “OracleDataReader”, even after fetching the data, does not break the “OracleConnection” yet (unless you “dispose” of it).
“OracleParameter” and “OracleParameterCollection” are generally used to work with parameterized queries (or even parameterized stored procedures). The next section gives further depth to some of the above classes in detail, so that you can select your own flexible approach based on the considerations of performance, ease, readability, re-usability, and other needs.
Actually to improve performance for retrieving information from huge databases, we also need to consider stored procedures, functions, triggers, packages, objects, and so forth (using Oracle server side programming). In this article, I don’t want to concentrate on performance issues (or Oracle server side programming). If you need to know about Oracle server side programming, I suggest you to follow the series “Database Interaction with PL/SQL” on www.devshed.com. A number of my articles in that series concentrate on working with PL/SQL (including stored procedures) from within ODP.NET. For now, I am concentrating on interacting with just traditional SQL.
Most of the time we will work with the following methodologies to retrieve data from an Oracle 10g database (keep in mind that I am not dealing with PL/SQL right now).
Getting information/data with just only a table name
Getting information/data with SELECT statement
Getting single value from database
Single parameterized queries
Multiple parameterized queries
Making an offline (connection less) cache using “OracleDataReader”
Making as offline cache using “OracleDataAdapter”
Parameterized queries using “OracleDataAdapter”
Even though this series started with the title ASP.NET, you can also work with the same type of concept either in VB.NET or VC#.NET (or any other .NET supported language). I simply used the ASP.NET based “datagrid” control to display the data fetched from an Oracle database (just for my convenience). Even though all of those methodologies would give the same result, we need to choose a particular method for a particular scenario based on all factors (like performance, speed, offline data, and so forth). It is NOT simply something you can take for granted.
Before working with any of these examples, don’t forget to import “Oracle.DataAccess.Client” at the top of your class. This would be present, if and only if you install ODP.NET on your machine. If your development machine is simply an Oracle Client, don’t forget to configure SQL*Net and the TNS stuff. So, let us start with each of those methodologies.
Dim cn As New OracleConnection("User ID=scott;password=tiger;Data Source=ORCL") Try Dim cmd As New OracleCommand cmd.CommandText = "emp" cmd.CommandType = CommandType.TableDirect cmd.Connection = cn 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
Those who already work with ADO.NET will understand the above code within seconds. It is always a good practice to maintain exception handling in our coding, especially with Oracle database connections. They are very sensitive if you are working in the “dedicated” connection mode.
Coming to the above code, I used “OracleConnection” (to connect to the Oracle database) and “OracleCommand” (to specify a table name) classes. Surprisingly, in the above code, you already worked with “OracleDataReader” (but indirectly) without even noticing. The statement “cmd.ExecuteReader” actually returns an instance of “OracleDataReader”, which has been directly assigned to the “datagrid” (which automatically pulls information from “OracleDataReader”).
You need to concentrate a bit on the following statement as well:
cmd.CommandType = CommandType.TableDirect
The above statement specifies to “OracleCommand” that you are not providing any SQL command to process, but simply passing a direct table name to fetch entire information from that table. This would be a bit different in the case of an SQL statement (explained in the next section).
Dim cn As New OracleConnection("User ID=scott;password=tiger;Data Source=ORCL") Try Dim cmd As New OracleCommand cmd.CommandText = "select * from scott.emp" cmd.CommandType = CommandType.Text cmd.Connection = cn cmd.Connection.Open() Dim rdr As OracleDataReader = cmd.ExecuteReader Me.DataGrid1.DataSource = rdr 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
Coming to the above code, I used “OracleConnection” (to connect to Oracle database) and “OracleCommand” (to specify a SELECT statement) classes. Here, I added a separate statement (for demonstration purposes only) to work with “OracleDataReader”. You could even proceed, just like the code in the previous section. The statement “cmd.ExecuteReader” actually returns an instance of “OracleDataReader” (now into the reference “rdr”), where “rdr” has been directly assigned to the “datagrid” (which automatically pulls information from “OracleDataReader”).
You need to concentrate a bit on the following statement as well:
cmd.CommandType = CommandType.Text
The above statement specifies to “OracleCommand” that you are not providing table name, but instead an SQL command (it could be any DML command) to process or fetch information from the table. This is quite different from the previous section.
This approach is preferred over the one in the previous section. By using this approach, you can filter (using WHERE clause) or even join (with various types of joins) with other tables and get what is required. This is my favorite method for dealing with SELECT statements (if not using stored procedures or functions).
Dim cn As New OracleConnection("User ID=scott;password=tiger;Data Source=ORCL") Try Dim cmd As New OracleCommand("select count(*) from emp", cn) cmd.Connection.Open() Me.lblError.Text = "No. of rows " & cmd.ExecuteScalar cmd.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 example, I simplified the code by removing repeated steps. Instead I tried to mix a larger number of statements within a single statement. In the above example, our main concentration should be on the following line:
Me.lblError.Text = "No. of rows " & cmd.ExecuteScalar
First of all, consider my apologies that I assigned the value to the “label” control, which is actually dedicated to showing error messages. I just did this, as it is a simple demonstration. Coming to the point, I replaced the “ExecuteReader” method with the “ExecuteScalar” method. “ExecuteReader” is generally used to retrieve one or more rows. The “ExecuteScalar” method is optimized to retrieve single values only. It doesn’t mean that you cannot use “ExecuteReader” for retrieving single values. The point is the optimization. The .NET data provider specification prefers to use “ExecuteScalar” if you are trying to retrieve single values from the database.
You can find the other methodologies in the immediate next part of this series. Any comments, suggestions, bugs, errors, feedback etc. are highly appreciated at jag_chat@yahoo.com.
ASP.NET 
Using Microsoft .NET and C# with Oracle 9i
