Oracle Database Interaction Using ODP.NET and ASP.NET: All Ways to Retrieve Data
(Page 1 of 5 )
This article mainly concentrates on retrieving data from an Oracle database with ODP.NET using ASP.NET in several possible ways.
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.
Next: What are all of the possible ways to work with these classes? >>
More ASP.NET Articles
More By Jagadish Chaterjee
