لطفا منتظر باشید ...
5.9. Write Database-Agnostic Code
In developing ADO.NET, Microsoft set out
to create a new data access architecture that would be more flexible,
better performing, and more easily extensible than its previous
COM-based OLE DB and ADO architectures. They did this by creating a
model where every data source must supply its own
data provider: a
set of managed classes that allow you to connect to a particular data
source (e.g., SQL Server, Oracle), execute commands, and retrieve
data. In order to ensure that these providers are consistent, each
implements a standard set of interfaces. However, this approach
creates major challenges for developers who want to write
provider-agnostic
codefor example, a basic database routine that can be used
equally well with the SQL Server provider or the Oracle provider.
Usually, you use provider-agnostic code because you
aren't sure what type of database the final version
of an application will use, or because you anticipate the need to
migrate to a different database in the future.
Note: Want a way to write database code that
isn't bound to a specific data source? This
challenge becomes a whole lot easier in . NET 2.0.
.NET 2.0 takes major steps to facilitate generic database coding by
introducing a new factory model. (A
factory model is a pattern where one class has the exclusive
responsibility for creating instances of other classes.) In this
model, you can use a database provider factory to build the
ADO.NET connections, commands, and many other types of objects
required for a particular database. The factory automatically returns
the type of object that you need for your data source (e.g., a
SqlCommand or an
OracleCommand), but when you write your code, you
don't worry about these details. Instead, you write
generic commands without regard to the particular details of the data
source.
5.9.1. How do I do that?
In provider-agnostic code, you still use all the same strongly typed
objects. However, your code manipulates these objects using common
interfaces. For example, every command object, whether
it's used for SQL Server or Oracle, implements the
common IDbCommand
interface, which guarantees a basic set of methods and properties.
Note: Because provider-agnostic code attempts to be as generic as
possible, it's more difficult to properly optimize a
database. As a result, this technique isn't suitable
for most large-scale enterprise applications.
Provider-agnostic code is structured so that you specify the type of
database you're using early on, usually by reading
some information from a configuration file. You use this information
to retrieve a DbProviderFactory for your database.
Here's an example where the factory string is
hardcoded:
Dim Factory As String = "System.Data.SqlClient"
Dim Provider As DbProviderFactory
Provider = DbProviderFactories.GetFactory(Factory)
In this example, the code uses the shared
GetFactory( ) method of
the System.Data.Common.DbProviderFactories
class. It specifies a string that identifies the provider name. For
example, if you use the string
System.Data.SqlClient, the GetFactory() method returns a
System.Data.SqlClient.SqlClientFactory
object. The DbProviderFactories class can create
factories for all the data providers included with .NET, because they
are explicitly configured in the machine.config
configuration file on the current computer. Essentially, the
configuration record tells the DbProviderFactories
class to create a SqlClientFactory when the
programmer passes the exact string
"System.Data.SqlClient." If you
develop your own provider, you can also register it to work in this
way (although that task is beyond the scope of this lab).
The SqlClientFactory object has the built-in
smarts to create all the objects used by the SQL Server provider.
However, your code can be completely generic. Instead of interacting
with the specific SqlClientFactory class type, it
should use the generic base class
DbProviderFactory. That way, your code can work
with any type of DbProviderFactory, and therefore
support any database provider.
Once you have the DbProviderFactory, you can
create other types of strongly typed ADO.NET objects using a set of
common methods by using the
CreateXxx() methods. These include:
CreateConnection( )CreateCommand( )CreateParameter( )CreateDataAdapter( )CreateCommandBuilder( )
All these methods create a provider-specific version of the object
they name.
To get a better understanding of how generic database code works, it
helps to try out a complete example that can switch from one data
provider to another on the fly. First of all, you need to create an
application configuration file that stores all the provider-specific
details. To do this, create a console application and open the
app.config file. Add
the following three settings, which specify the factory name, the
connection string for the database, and the query to perform:
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<appSettings>
<add key="Factory" value="System.Data.SqlClient" />
<add key="Connection" value=
"Data Source=localhost;Initial Catalog=Northwind;
Integrated Security=SSPI" />
<add key="Query" value="SELECT * FROM Orders" />
</appSettings>
</configuration>
This example uses the SQL Server provider to connect to the Northwind
database and retrieve a list of all the records in the Orders table.
Now you can retrieve the configuration file information and use it
with the DbProviderFactories class to create every
ADO.NET provider object you need. In Example 5-9,
the query is executed, a DataSet is filled, and a
list of OrderID values is displayed in the console window.
Example 5-9. Using DbProviderFactories to write database-agnostic code
Imports System.Data.Common
Imports System.Configuration
Module GenericDatabaseTest
Public Sub Main( )
' Get all the information from the configuration file.
Dim Factory, Connection, Query As String
Factory = ConfigurationManager.AppSettings("Factory")
Connection = ConfigurationSettings.AppSettings("Connection")
Query = ConfigurationManager.AppSettings("Query")
' Get the factory for this provider.
Dim Provider As DbProviderFactory
Provider = DbProviderFactories.GetFactory(Factory)
' Use the factory to create a connection.
Dim con As DbConnection = Provider.CreateConnection( )
con.ConnectionString = Connection
' Use the factory to create a data adapter
' and fill a DataSet.
Dim Adapter As DbDataAdapter = Provider.CreateDataAdapter
Adapter.SelectCommand = Provider.CreateCommand( )
Adapter.SelectCommand.Connection = con
Adapter.SelectCommand.CommandText = Query
Dim ds As New DataSet
Adapter.Fill(ds, "Orders")
' Display the retrieved information.
For Each Row As DataRow In ds.Tables("Orders").Rows
Console.WriteLine(Row("OrderID"))
Next
End Sub
End Module
Mostly, this is a fairly pedestrian piece of data access logic. The
only exciting part is that you can switch from one provider to
another without modifying any of the code or recompiling. You just
need to modify the provider information and connection string in the
configuration file. For example, make these changes to the
configuration file to access the same table through the slower OLE DB
provider interface:
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<appSettings>
<add key="Factory" value="System.Data.OleDb" />
<add key="Connection" value=
"Provider=SQLOLEDB;Data Source=localhost;Initial Catalog=Northwind;
Integrated Security=SSPI" />
<add key="Query" value="SELECT * FROM Orders" />
</appSettings>
</configuration>
After saving the configuration file, you can run the application
again. It will work just as well, displaying the same list of order
records.
5.9.2. What about...
...the challenges you'll encounter in writing
database-agnostic programs? The new factory approach is a giant leap
forward for those who want to write provider-agnostic code. However,
a slew of problems (some minor and some more significant) still
remain. These include:
Handling errors
Every database provider has its own
exception object (like SqlException and
OracleException), and these objects
don't derive from a common base class. That means
there's no way to write an exception handler that
catches database exceptions generically. All you can do is write
exception handlers that catch the base Exception
object.
Provider-specific functionality
Some features aren't exposed through the common
interfaces. For example, SQL Server has the ability to execute FOR
XML queries that return XML documents. To execute this type of query,
you use the SqlCommand.ExecuteXmlReader( ) method.
Unfortunately, this isn't a standard command method,
so there's no way to access it through the
IDbCommand interface.
Handling parameters
Some providers (like SQL Server) recognize command parameters by
their name. Others (like OLE DB) recognize command parameters by the
order of their appearance. Minor differences like this can thwart
provider-agnostic programming.
5.9.3. Where can I learn more?
Unfortunately, there isn't much documentation yet in
the MSDN Help about provider-agnostic coding. However, you can get a
good overview with additional examples from the Microsoft
whitepaper at
http://msdn.microsoft.com/library/en-us/dnvs05/html/vsgenerics.asp.
