Writing Common Code for .NET Framework Data Providers

Using the common interfaces supplied by ADO.NET, you can write a single set of code that will work regardless of which .NET Framework data provider you are using.

When writing code to be used across .NET Framework data providers, pay special attention to any syntax you are using that is provider specific. For example, SQL syntax will differ depending upon the data source you are accessing. If you change to a different .NET Framework data provider, your code will be easier to maintain if you isolate your SQL commands as string constants.

It is also important to maintain the order in which parameters are created in your code. Parameter order is not an issue with the .NET Framework Data Provider for SQL Server because parameters are identified by name. However, the .NET Framework Data Provider for OLE DB assigns parameter values in the order that the parameters are added to the parameters collection. As a result, it is a good practice to always maintain the order of parameters in your code.

The following code example will work with both the .NET Framework Data Provider for SQL Server, the .NET Framework Data Provider for OLE DB, or the .NET Framework Data Provider for ODBC.

' You can use either:
'  Dim myConn As IDbConnection = New SqlConnection
' or:
'  Dim myConn As IDbConnection = New OleDbConnection
' or:
'  Dim myConn As IDbConnection = New OdbcConnection
' or:
'  Dim myConn As IDbConnection = New OracleConnection

Dim myCommand As IDbCommand = myConn.CreateCommand()
myCommand.CommandText = "SELECT * FROM Customers"
Dim myReader As IDataReader = myCommand.ExecuteReader()

Do While myReader.Read()
  Console.WriteLine("{0}" & vbTab & "{1}", myReader.GetString(0), myReader.GetString(1))
Loop
[C#]
// You can use either:
//  IDbConnection myConn = new SqlConnection();
// or:
//  IDbConnection myConn = new OleDbConnection();
// or:
//  IDbConnection myConn = new OdbcConnection();
// or:
//  IDbConnection myConn = new OracleConnection();

IDbCommand myCommand = myConn.CreateCommand();
myCommand.CommandText = "SELECT * FROM Customers";
IDataReader myReader = myCommand.ExecuteReader();

while (myReader.Read())
  Console.WriteLine("{0}\t{1}", myReader.GetString(0), myReader.GetString(1));

See Also

Overview of ADO.NET | ADO.NET Architecture | Using .NET Framework Data Providers to Access Data