CodeKicks.com
Focus on Microsoft Technologies - Tutorials, Articles, Code Samples.

Saturday, September 02, 2006

Using the .NET Framework Data Provider for Oracle

Although MS SQL Server is gaining more popularity on Windows world, if your database layer resides on Unix, you have to interact with Oracle databases mainly. If you've had to access an Oracle database from an ASP.NET application you've likely been using, up until now, the generic OleDb drivers. This generic OleDb data provider is the data provider for any OleDb-compliant data source, such as Access, SQL, Excel, Oracle, etc.

The problem with a generic data provider is that it is not tweaked to maximize performance for a particular data store. In order to address this, Microsoft released a data provider specifically for Microsoft SQL Server 7.0 and up - the SqlClient data provider. Recently Microsoft released a .NET Framework Data Provider for Oracle. No longer do we have to use the generic OleDb driver! You can download the free Oracle driver at http://www.msdn.microsoft.com/downloads/default.asp?url=/downloads/sample.asp?url=/msdn-files/027/001/940/msdncompositedoc.xml .

This provider can be used instead of the OleDb provider, delivering a native interface for Oracle. Microsoft has reported that, in certain cases the performance of this specialized driver can exceed the generic OleDb driver by up to 200%! For information on making your .NET applications work faster and more efficiently with Oracle databases, be sure to read Greg Leake's article, Using .NET Framework Data Provider for Oracle to Improve .NET Application Performance.

The Oracle data provider supports all Oracle 9i data types and ref cursors of Oracle stored procedures which return result sets. One important note: Oracle 8i Release 3 (8.1.7) Client or later must be installed on the Web server for this provider to function properly.

If you have existing ASP.NET code that accesses an Oracle database, you'll be pleased to learn that migrating your code to use the new provider is quite simple. All you need to do is the follwoing three steps:

  1. Install the provider from Microsoft site,
  2. Add the reference (System.Data.OracleClient.dll, most likely under \Program Files\Microsoft.Net\OracleClient.Net) to your Visual Studio .NET project,
  3. Replace all OleDbConnection, OleDbDataAdapter, OleDbCommand, OleDbDataReader, etc. with OracleConnection, OracleDataAdapter, OracleCommand, and OracleDataReader in your code.

Continue from 4guys

Post a Comment