DB Access using .NET Data Providers

Oracle and SQL Server 2000 Database Access with SqlNet, OleDb and Odbc Data Provider using .NET Framework and C#

Martin Zahn, Akadia AG, Information Technology, CH-3604 Thun
Decembre 2002, Copyright © 2002, Akadia AG, all rights reserved

1.   Introduction
2.   Available .NET Framework Data Providers
3.   Setup of the .NET Framework
4.   SqlClient Data Provider to access SQL Server 2000
5.   OleDb Data Provider to access Oracle
6.   Odbc Data Provider to access ODBC database
7.   Oracle .NET Data Provider

More Information:

Oracle and SQL Server DB Transactions using .NET Framework with C# and Sql Server and Oracle Data Provider

1. Introduction

Summary

This article shows you how to retrieve data from an Oracle and SQL Server 2000 database using the so called data providers shipped with the .NET Framework. It also covers practices when using objects available in ADO.NET and offers suggestions that can help you optimize the design of ADO.NET applications.

A data provider in the .NET Framework serves as a bridge between an application and a data source. A .NET Framework data provider enables you to return query results from a data source, execute commands at a data source, and propagate changes in a DataSet to a data source. This article includes tips on which .NET Framework data provider is best suited for your needs.

2. Available .NET Framework Data Providers

Which .NET Framework Data Provider to Use?

To achieve the best performance for your application, use the .NET Framework data provider that is most appropriate for your data source. There are a number of data provider options for use in your applications. The following table provides information about the available data providers and which data sources a data provider is most appropriate for.

Provider

Details

SQL Server .NET Data Provider

Found in the System.Data.SqlClient namespace.

Recommended for middle-tier applications using Microsoft SQL Server version 7.0 or later.

Recommended for single-tier applications using the Microsoft Data Engine (MSDE) or Microsoft SQL Server 7.0 or later.

OLE DB .NET Data Provider

Found in the System.Data.OleDb namespace.

Recommended for middle-tier applications using Microsoft SQL Server 6.5 or earlier, or any OLE DB provider that supports the OLE DB interfaces listed in OLE DB Interfaces Used by the OLE DB .NET Data Provider in the .NET Framework SDK.

For Microsoft SQL Server 7.0 or later, the .NET Framework Data Provider for SQL Server is recommended.

Recommended for single-tier applications using a Microsoft® Access database. Use of an Access database for a middle-tier application is not recommended.

ODBC .NET Data Provider

Found in the Microsoft.Data.Odbc namespace.

Provides access to data sources that are connected to using an ODBC driver.

Oracle .NET Data Provider Found in the System.Data.OracleClient namespace.
The .NET Framework Data Provider for Oracle, unlike the Microsoft OLE DB provider for Oracle, also supports new Oracle 9i datatypes, as well as ref cursors (useful for running Oracle stored procedures that return result sets). This provider, System.Data.OracleClient, is similar to the .NET Framework Data Provider for SQL Server, System.Data.SqlClient.

3. Setup of the .NET Framework

The Microsoft® .NET Framework is the infrastructure for the overall .NET Platform. The common language runtime and class libraries (including Microsoft Windows® Forms, ADO.NET, and ASP.NET) combine to provide services and solutions that can be easily integrated within and across a variety of systems.

The .NET Framework provides a fully managed, protected, and feature-rich application execution environment, simplified development and deployment, and seamless integration with a wide variety of languages.

Microsoft .NET Framework Software Development Kit

The Microsoft® .NET Framework Software Development Kit (SDK) includes the .NET Framework, as well as everything you need to write, build, test, and deploy .NET Framework applications - documentation, samples, and command-line tools and compilers.

Download and Installation

Goto:

http://msdn.microsoft.com/downloads/

Select:

-> Software Development Kits
-> Microsoft .NET Framework SDK

You'll get the whole framework and a C# command line compiler. Run the downloaded setup.exe if you haven't installed yet Microsoft .NET and follow the installation steps. You will be asked for Server Components which you don't need. If the installation asks for Microsoft Data Access Components MDAC you may continue or quit the installation. Anyway, you need to install MDAC 2.7 or higher prior ODBC data access is going to work.

4. SqlClient Data Provider to access SQL Server 2000

The SqlClient provider ships with ADO.NET and resides in the System.Data.SqlClient namespace. It should be used to access SQL Server 2000. The classes within SqlClient provider all begin with "Sql", so the connection class is SqlConnection, the command class is SqlCommand, and so on. Lets look at an example to access SQL Server 2000.

using System;
using System.Data.SqlClient;

public class CSharpAdoSqlClient
{
    public static void Main(string[] args)
    {
        // Setup connection string to access local SQL Server 2000
        string connectionString = "server=localhost;" +
        "database=Northwind;uid=sa;pwd=manager";

        // Instantiate the connection, passing the
        // connection string into the constructor
        SqlConnection con = new SqlConnection(connectionString);

        // Open the connection
        con.Open();

        // Create and execute the query
        SqlCommand cmd = new SqlCommand("SELECT * FROM Customers",con);
        SqlDataReader reader = cmd.ExecuteReader();

        // Iterate through the DataReader and display row
        while(reader.Read()) {
            Console.WriteLine("{0} - {1}",
            reader.GetString(0), reader.GetString(1));
        }
    }
}

This file is called CSharpAdoSqlClient.cs, we can compile it from the command line simply by typing csc CSharpAdoSqlClient.cs. There is no need to add any references.

5. OleDb Data Provider to access Oracle

If you are not using SQL Server, it is almost certain that your best bet will be to use the OleDb provider, at least until more vendor specific .NET providers are released. If your data source has an ODBC driver, but not an OleDb provider, then you will need to use the Odbc .NET provider. Like the SqlClient provider, the OleDb provider resides in System.Data.dll, and ships with the .NET Framework. The classes that compose the provider are in the System.Data.OleDb namespace and all have the prefix "OleDb". Lets look at an example to access Oracle 9.

using System;
using System.Data.OleDb;

public class CSharpAdoOleDb
{
    public static void Main(string[] args)
    {
        // Setup connection string to access Oracle9 database
        // using the TNSNAMES connect string ARK2
        string connectionString = "Provider=MSDAORA;" +
        "Data Source=ARK2;User ID=scott; Password=tiger";

        // Instantiate the connection, passing the
        // connection string into the constructor
        OleDbConnection con = new OleDbConnection(connectionString);

        // Open the connection
        con.Open();

        // Create and execute the query
        OleDbCommand cmd = new OleDbCommand("SELECT ename,job FROM emp",con);
        OleDbDataReader reader = cmd.ExecuteReader();

        // Iterate through the DataReader and display row
        while(reader.Read()) {
            Console.WriteLine("{0} - {1}",
            reader.GetString(0), reader.GetString(1));
        }
    }
}

This file is called CSharpAdoOleDb.cs, we can compile it from the command line simply by typing csc CSharpAdoOleDb.cs. There is no need to add any references.

6. Odbc Data Provider to access ODBC database

Unlike the two other .NET providers, the Odbc provider is not shipped with the .NET Framework. To an ADO programmer, an ideal world would be one in which every data source exposes an OLE DB interface, so that ADO could call directly into the data source.

Although increasingly more database vendors are implementing OLE DB interfaces, some data sources are not yet exposed this way. However, virtually all DBMS systems in use today can be accessed through ODBC. The Microsoft ODBC Provider, however, allows ADO to connect to any ODBC data source.

The classes that compose the provider are in the Microsoft.Data.Odbc namespace and all have the prefix "Odbc". Lets look at an example to access SQL Server 2000.

using System;
using Microsoft.Data.Odbc;

public class CSharpAdoOdbc
{
    public static void Main(string[] args)
    {
        // Setup connection string to access local SQL Server 2000
        string connectionString = "Driver={SQL Server};" +
        "Server=localhost;database=Northwind;uid=sa;pwd=manager";

        // Instantiate the connection, passing the
        // connection string into the constructor
        OdbcConnection con = new OdbcConnection(connectionString);

        // Open the connection
        con.Open();

        // Create and execute the query
        OdbcCommand cmd = new OdbcCommand("SELECT * FROM Customers",con);
        OdbcDataReader reader = cmd.ExecuteReader();

        // Iterate through the DataReader and display row
        while(reader.Read()) {
            Console.WriteLine("{0} - {1}",
            reader.GetString(0), reader.GetString(1));
        }
    }
}

This file is called CSharpAdoOdbc.cs, we can compile it from the command line by typing csc /r:Microsoft.Data.Odbc.dll CSharpAdoOdbc.cs. The reference to the Microsoft.Data.Odbc.dll is needed.

7. Oracle .NET Data Provider

If you are building applications using Microsoft® .NET against an Oracle backend database, you will want to take a close look at the new .NET Framework Data Provider for Oracle released on MSDN in June 2002. The goal of the provider is to boost the performance and scalability of .NET applications with Oracle databases by providing a native .NET interface to Oracle databases that bypasses the need to use an OLE DB provider.

The .NET Framework Data Provider for Oracle, unlike the Microsoft OLE DB provider for Oracle, also supports new Oracle 9i datatypes, as well as ref cursors (useful for running Oracle stored procedures that return result sets). This provider, System.Data.OracleClient, is similar to the .NET Framework Data Provider for SQL Server, System.Data.SqlClient.

Until recently, the primary mechanism developers used to access Oracle databases from .NET application was OLE DB, channeling database requests through the System.Data.OleDb data classes. However, developers writing .NET data-driven applications against SQL Server have been able to take advantage of the super-fast System.Data.SqlClient data classes, which provide data access to SQL Server via a SQL Server provider written in managed .NET code. This provider communicates to SQL Server via the native SQL Server client libraries, and derives very fast speeds. While OLE DB provided an adequate data access mechanism for .NET applications to communicate with Oracle backends, certainly developers have been asking for a faster, more scalable Oracle data access mechanism to get better performance for the .NET applications. The new .NET Framework Data Provider for Oracle, recently released on MSDN, provides just this.

Basically, developers now have a much faster database access mechanism for ADO.NET in the form of new System.Data.OracleClient framework classes that work in much the same way as the System.Data.SqlClient classes. In both cases, the fastest database read mechanism will be ADO.NET Data Readers, as opposed to Data Sets, although both are fully functional using the new Oracle Managed Provider. The good news is the new OracleClient classes should provide significant performance improvements for .NET applications, and migrating code between OLE DB data classes and OracleClient data classes is not very difficult, although some work is required. However, the performance boost can be dramatic.

The OLE DB client classes are designed to provide a database-independent layer for accessing generic databases. While the value of a generic layer is nearly universal access, it is difficult to deliver database-specific optimizations in this generic access layer. Also, the OLE DB layer is implemented as a COM library, so the System.Data.Oledb namespace works through COM interop. To achieve the significant performance boost described here, the .NET Framework Data Provider for Oracle avoids the cost of COM interop, and also employs Oracle-specific optimizations.

The Microsoft® .NET Framework Data Provider for Oracle is an add-on component to the Microsoft .NET Framework that provides access to an Oracle database using the Oracle Call Interface (OCI) as provided by Oracle Client software. Oracle 8i Release 3 (8.1.7) Client or later must be installed for this provider to function.

The Oracle .NET Data Provider is available for download

The following files are installed by Setup:

File name Description

Eula.rtf .NET Framework Data Provider for Oracle end-user license agreement.

Oracleref.chm .NET Framework Data Provider for Oracle documentation.

Oracleref.chi Index file that accompanies Oracleref.chm (.NET Framework Data Provider for Oracle documentation).

Readme.txt Additional product information that is not contain in Oracleref.chm (.NET Framework Data Provider for Oracle documentation).

System.Data.OracleClient.dll The .NET Framework Data Provider for Oracle.

Mtxoci8.dll DLL that provides distributed transaction support.

All of these files, except Mtxoci8.dll, are installed in C:\Program Files\Microsoft.NET\OracleClient.Net by default (assuming that C:\Program Files is your default Program Files folder location). Mtxoci8.dll is installed in the windows system directory (for example, C:\Windows\System32 on a Windows 2000 computer on which C: is the system drive).

As part of Setup, the System.Data.OracleClient namespace is added to the global assembly cache.

The classes that compose the provider are in the System.Data.OracleClient namespace and all have the prefix "Oracle". Lets look at an example to access Oracle 9.2.0.

using System;
using System.Data.OracleClient;

public class CSharpAdoOra
{
    public static void Main(string[] args)
    {
        // Setup connection string to access local Oracle 9
        string connectionString = "Data Source=ARK2;User ID=scott; Password=tiger";

        // Instantiate the connection, passing the
        // connection string into the constructor
        OracleConnection con = new OracleConnection(connectionString);

        // Open the connection
        con.Open();

        // Create and execute the query
        OracleCommand cmd = new OracleCommand("SELECT ename,job FROM emp",con);
        OracleDataReader reader = cmd.ExecuteReader();

        // Iterate through the DataReader and display row
        while(reader.Read()) {
            Console.WriteLine("{0} - {1}",
            reader.GetString(0), reader.GetString(1));
        }
    }
}

This file is called CSharpAdoOra.cs, we can compile it from the command line by typing csc /r:System.Data.OracleClient.dll CSharpAdoOra.cs. The reference to the System.Data.OracleClient.dll is needed.