Oracle Database Interaction Using ODP.NET and ASP.NET: All Ways to Manipulate Data
(Page 1 of 5 )
This article (part four of this series) mainly concentrates on manipulating data in 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) is 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 articles mainly concentrated on connecting to Oracle database and retrieving information in several possible ways. In this article I mainly concentrate on manipulating information in an Oracle 10g (10.2) database using ODP.NET.
By now, you must have known that you need to work with Oracle.DataAccess.Client namespace to use ODP.NET. The same namespace has several classes, collections, enumerated types, and so on to work conveniently with both SQL and PL/SQL of Oracle 10g product. It even boasts much tighter integration with .NET, when compared to Microsoft’s .NET provider for Oracle (System.data.OracleClient namespace).
The most generic classes in ODP.NET for manipulating information in an Oracle database would be the following:
- OracleConnection
- OracleCommand
- OracleDataAdapter
- OracleParameter
- OracleParameterCollection
- OracleCommandBuilder
Of course, any .NET data provider would provide similar interfaces to a 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 (as we will not be able to connect at all).
The “OracleCommand” class is used to issue a command (either SELECT or DML or to execute a stored procedure or any of a number of other options) to an Oracle database for some DML operation. If we pass a DML statement (or execute a stored procedure which does some DML operation) to an instance of the “OracleCommand” class, our application may receive the result of that DML statement (in the form of rows, or a single value, or bytes, or other possible forms).
The “OracleDataAdapter” is generally used in connectionless scenarios. It connects (through “OracleConnection”) to the database, passes a command (of the type “OracleCommand”), fetches the information and files it into either “datatable” or “dataset” and finally breaks the “OracleConnection” itself. After making all modifications to the “datatable” or “dataset” (which are offline), we update them again using the same “OracleDataAdapter”. We can provide our own DML commands for “OracleDataAdapter”, when it is trying to update the database. “OracleCommandBuilder” is generally used with “OracleDataAdapter” to automatically generate DML commands for a single table SELECT command issued.
“OracleParameter” and “OracleParameterCollection” are generally used to work with parameterized commands (or parameterised stored procedures, and so on). The next section gives further depth for 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 so on.
Next: What are all the possible ways to work with those classes? >>
More ASP.NET Articles
More By Jagadish Chaterjee
