You need to develop an application that can be used with different data providers, but does not lose functionality that is specific to the different providers. You want to create provider-independent code and use it with the provider-specific code that you might need.
The solution shows how to use interfaces that are inherited by .NET
connected classes (such as Connection
and
DataReader
) to create provider-independent code
that can be used with provider-specific code to access unique
functionality.
The sample code contains a method and two event handlers:
GetData( )
This method is a .NET data provider-independent method that accepts .NET data provider-specific
Connection
andDataAdapter
arguments as genericIDbConnection
andIDbDataAdapter
interface types. The interfaces are used to fill aDataSet
from the Customers table in Northwind. The default view of the CustomersDataTable
is bound to the data grid on the form.Finally, the provider-specific
Connection
for theIDbConnection
is identified and provider-specific logic executed.- SQL
Button.Click
This event handler is provider-specific code that creates a
SqlConnection
and aSqlDataAdapter
object and passes them as arguments into the provider-independentGetData( )
method.- OLE DB
Button.Click
This event handler is provider-specific code that creates an
OleDbConnection
and anOleDbDataAdapter
object and passes them as arguments into the provider-independentGetData( )
method.
The C# code is shown in Example 1-9.
Example 1-9. File: DatabaseIndependentCodeForm.cs
// Namespaces, variables, and constants using System; using System.Configuration; using System.Windows.Forms; using System.Data; using System.Data.Common; using System.Data.SqlClient; using System.Data.OleDb; // . . . private void GetData(IDbConnection conn, IDbDataAdapter da) { // Create the command and assign it to the IDbDataAdapter interface. IDbCommand cmd = conn.CreateCommand( ); cmd.CommandText = "SELECT * FROM Customers"; da.SelectCommand = cmd; // Add a table mapping. da.TableMappings.Add("Table", "Customers"); dataGrid.DataSource = null; // Fill the DataSet. DataSet ds = new DataSet( ); da.Fill(ds); // Bind the default view for the Customer table to the grid. dataGrid.DataSource = ds.Tables["Customers"].DefaultView; // Identify provider-specific connection type and process appropriately. if (conn is SqlConnection) { MessageBox.Show("Specific processing for SQL data provider."); } else if(conn is OleDbConnection) { MessageBox.Show("Specific processing for OLE DB data provider."); } } private void sqlButton_Click(object sender, System.EventArgs e) { // Create a SQL Connection and DataAdapter. SqlConnection conn = new SqlConnection( ConfigurationSettings.AppSettings["Sql_ConnectString"]); SqlDataAdapter da = new SqlDataAdapter( ); dataGrid.CaptionText = "SQL .NET Provider"; // Call provider-independent function to retrieve data. GetData(conn, da); } private void oleDbButton_Click(object sender, System.EventArgs e) { // Create a OLE DB Connection and DataAdapter. OleDbConnection conn = new OleDbConnection( ConfigurationSettings.AppSettings["OleDb_ConnectString"]); OleDbDataAdapter da = new OleDbDataAdapter( ); dataGrid.CaptionText = "OLE DB .NET Provider"; // Call provider-independent function to retrieve data. GetData(conn, da); }
The IDbConnection
,
IDbCommand
,
IDataAdapter
, and IDataReader
interfaces are implemented by Connection
,
Command
, DataAdapter
, and
DataReader
classes in .NET data providers. You can
pass these provider-independent base classes
as interface arguments instead of the provider-specific inherited
classes. This allows applications that support multiple data
providers to reuse common provider-independent code.
The provider-specific functionality of the classes is not available
when the base interfaces are used. The is
operator
is used to identify the provider-specific class of the
provider-independent interface. Branching logic is then used execute
code specific to that class.
Get ADO.NET Cookbook now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.