The ADO.NET libraries contain classes that you can use to transfer data between a data store and the client application. There are many different kinds of data stores, which means you need specialized code to provide the necessary bridge between the disconnected data access classes (discussed in Chapter 1) and a particular data store. This chapter focuses on these specialized classes, starting with the most essential classes, such as DbConnection and DbCommand. The chapter concludes with the more elaborate classes that have been added in ADO.NET 2.0, such as DbProviderFactory and DbProviderFactories.
Using Providers to Move Data
The classes that are responsible for the movement of data between the disconnected data classes in the client application and the data store are referred to as connected classes or provider classes. The Microsoft .NET Framework contains the following providers:
OLEDB Contains classes that provide general-purpose data access to many data sources. You can use this provider to access SQL Server 6.5 and earlier, SyBase, DB2/400, and Microsoft Access.
ODBC Contains classes for general-purpose data access to many data sources. This provider is typically used when no newer provider is available.
SQL Server Contains classes that provide functionality similar to the generic OLEDB provider. The difference is that these classes are tuned for SQL Server 7.0 and later data access. SQL Server 6.5 and earlier must use the OLEDB provider.
Oracle Contains classes for accessing Oracle 8i and later servers. This provider is similar to the OLEDB provider but provides better performance.
You can also use third-party providers, such as DB2 and MySql, which can be downloaded from the Web.
Table 2-1 lists the primary provider classes and interfaces. The classes are subclassed by the provider, which replaces the Db prefix with a provider prefix, such as Sql, Oracle, Odbc, or OleDb. You can use the base classes with factory classes to create client code that is not provider specific. The following sections describe these classes in detail.
Table 2-1: Primary Provider Classes and Interfaces in ADO.NET
Base Classes
SqlClient Classes
Generic Interface
DbConnection
SqlConnection
IDbConnection
DbCommand
SqlCommand
IDbCommand
DbDataReader
SqlDataReader
IDataReader/IDataRecord
DbTransaction
SqlTransaction
IDbTransaction
DbParameter
SqlParameter
IDbDataParameter
DbParameterCollection
SqlParameterCollection
IDataParameterCollection
DbDataAdapter
SqlDataAdapter
IDbDataAdapter
DbCommandBuilder
SqlCommandBuilder
DbConnectionStringBuilder
SqlConnectionStringBuilder
DBDataPermission
SqlPermission
Getting Started with the DbConnection Object
You need a valid, open connection object to access a data store. The DbConnection class is an abstract class from which the provider inherits to create provider-specific classes. The connection class hierarchy is shown in Figure 2-1.
Figure 2-1: The DbConnection class hierarchy
You need a valid connection string to create a connection. The following code snippet shows first how to create the connection and then how to assign the connection string. With a valid connection string, you can open the connection and execute commands. When you are finished working with the connection object, you must close the connection to free up the resources being held. Note that you need the pubs sample database to use this connection. The pubs and Northwind sample databases are available from the Microsoft download site and are also included on the sample disc.
Visual Basic
Dim connection as DbConnection = new SqlConnection() connection.ConnectionString = _ "Server=.;Database=pubs;Trusted_Connection=true" connection.Open() 'Do lots of cool work here connection.Close()
C#
DbConnection connection = new SqlConnection(); connection.ConnectionString = "Server=.;Database=pubs;Trusted_Connection=true"; connection.Open(); //Do lots of cool work here connection.Close();
By creating an instance of the SqlConnection class, the DbConnection is created using the SQL Server .NET provider. The ConnectionString property is initialized to use the local machine (".") and set the startup database to the pubs database. Lastly, the connection uses a trusted connection for authentication when connecting to SQL Server.
Note that the connection string is the same regardless of the programming language used. The following sections explain how to configure a connection string using each of the .NET providers.
Configuring an ODBC Connection String
The connection string can be the most difficult object to set up when you're working with a provider for the first time. Open Database Connectivity (ODBC) is one of the older technologies that the .NET Framework supports, primarily because the .NET Framework is still required to connect to older database products that have ODBC drivers. Table 2-2 describes the most common ODBC connection string settings.
Table 2-2: ODBC Connection String Keywords
Keyword
Description
Driver
The ODBC driver to use for the connection
DSN
A data source name, which can be configured through Control Panel |Administrative Tools| Data Sources (ODBC)
Server
The name of the server to connect to
Trusted_Connection
Specifies that security is based on using the domain account of the currently logged on user
Database
The database to connect to
DBQ
Typically refers to the physical path to a data source
Sample ODBC Connection Strings The following connection string instructs the text driver to treat the files that are located in the C:\Test\MyFolder subdirectory as tables in a database.
Driver={Microsoft Text Driver (*.txt; *.csv)}; DBQ=C:\\Test\\MyFolder;
The following connection string instructs the Access driver to open the northwind database file that is located in the C:\Program Files\myApp folder.
Another common but older technology that is used to access databases is Object Linking and Embedding for Databases (OLEDB). Table 2-3 describes the most common OLEDB connection string settings.
Table 2-3: OLEDB Connection String Keywords
Keyword
Description
Data Source
The name of the database or physical location of the database file.
File Name
The physical location of a file that contains the real connection string.
Persist Security Info
If set to true, retrieving the connection string returns the complete connection string that was originally provided. If set to false, the connection string contains the information that was originally provided, minus the security information.
Provider
The vendor-specific driver to use for connecting to the data store.
Sample OLEDB Connection Strings This connection string uses the settings stored in the MyAppData.udl file. The .udl extension stands for universal data link.
FILE NAME=C:\Program Files\MyApp\MyAppData.udl
This connection string uses the Jet driver, which is the Access driver, and opens the demo database file. Retrieving the connection string from the connection returns the connection that was originally passed in, minus the security information.
Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\Program Files\myApp\demo.mdb; Persist Security Info=False
SQL Server Connection String Configuration
The SQL Server provider allows you to access SQL Server 7.0 and later. If you need to connect to SQL Server 6.5 and earlier, use the OLEDB provider. Table 2-4 describes the most common SQL Server connection string settings.
Table 2-4: SQL Server Connection String Keywords
Keyword
Description
Data Source, addr, address, network address, server
The name or IP address of the database server.
Failover Partner
Provides support for database mirroring in SQL Server 2005.
The full or relative path and name of a file containing the database to be attached to. The path supports the keyword string |DataDirectory|, which points to the application's data directory. The database must reside on a local drive. The log filename must be in the format <database-File-Name>_log.ldf or it will not be found. If the log file is not found, a new log file is created.
Initial Catalog, database
The name of the database to use.
Integrated Security, trusted_connection
Used to connect to SQL Server using a secure connection, where authentication is through the user's domain account. Can be set to true, false, or sspi. The default is false.
Persist Security Info, persistsecurityinfo
If set to true, retrieving the connection string returns the complete connection string that was originally provided. If set to false, the connection string contains the information that was originally provided, minus the security information. The default is false.
User ID, uid, user
The user name to use to connect to the SQL Server when not using a trusted connection.
Password, pwd
The password to use to log in to SQL Server when not using a trusted connection.
Enlist
When set to true, the pooler automatically enlists the connection into the caller thread's ongoing transaction context.
Pooling
When set to true, causes the request for a new connection to be drawn from the pool. If the pool does not exist, it is created.
Max Pool Size
Specifies the maximum allowed connections in the connection pool. The default is 100.
Min Pool Size
Specifies the minimum number of connections to keep in the pool. The default is 0.
Asynchronous Processing, async
When set to true, enables execution of asynchronous commands on the connection. Synchronous commands should use a different connection, to minimize resource usage. The default is false.
Connection Reset
Indicates that the database connection will be reset when the connection is removed from the pool. The default is true. A setting of false results in fewer round-trips to the server when creating a connection, but the connection state is not updated.
MultipleActiveResultSets
When set to true, allows for the retrieval of multiple forward-only, read-only result sets on the same connection. The default is false.
Replication
Used by SQL Server for replication.
Connect Timeout, connection timeout, timeout
The time in seconds to wait while an attempt is made to connect to the data store. The default is 15 seconds.
Encrypt
If Encrypt is set to true and SQL Server has a certificate installed, all communication between the client and server is SSL encrypted.
Load Balance Timeout, connection lifetime
The maximum time in seconds that a pooled connection should live. The maximum time is checked only when the connection is returned to the pool. This setting is useful in load-balanced cluster configurations to force a balance between a server that is on line and a server that has just started. The default is 0.
Network Library, net, network
The network library DLL to use when connecting to SQL Server. Allowed libraries include dbmssocn (TCP/IP), dbnmpntw (Named Pipes), dbmsrpcn (Multiprotocol), dbmsadsn (AppleTalk), dbmsgnet (VIA), dbmsipcn (Shared Memory), and dbmsspxn (IPX/SPX).
The default is dbmssocn (TCP/IP), but if a network is not specified and either "." or "(local)" is specified for the server, shared memory is used as the default.
Packet Size
The size in bytes for each packet that is sent to SQL Server. The default is 8192.
Application Name, app
The name of the application. If not set, this defaults to .NET SQL Client Data Provider.
Current Language, language
The SQL Server language record name.
Workstation ID, wsid
The name of the client computer that is connecting to SQL Server.
Sample SQL Server Connection Strings The following connection string connects to the northwind database on the current computer (localhost) using integrated security. This connection must be made within 30 seconds or an exception is thrown. The security information will not be persisted.
This next connection string uses the TCP sockets library (DBMSSOCN) and connects to the MyDbName database on the computer located at IP address 10.1.2.3, using port 1433. Authentication is based on using MyUsername as the user name and x&1W$dF9 as the password.
Network Library=DBMSSOCN; Data Source=10.1.2.3,1433; Initial Catalog=MyDbName; User ID=myUsername; Password=x&1W$dF9
Attaching to a Local SQL Database File with SQL Express SQL Express is a free database product that is easy to install and use and is based on SQL Server 2005 technology. When you're building small Web sites and single-user applications, SQL Express is a natural choice due to its XCOPY deployment capabilities, reliability, and high-performance engine. In addition, SQL Express databases can easily be attached to SQL Server 2005. SQL Express is installed as part of the default Visual Studio 2005 installation, which makes it an excellent database to use when you're developing applications that are destined to be used on SQL Express or SQL Server 2005. To attach a local database file, you can use the following connection string.
Data Source=.\SQLEXPRESS; AttachDbFilename=C:\MyApplication\PUBS.MDF; Integrated Security=True; User Instance=True
In this example, the Data Source is set to an instance of SQL Express called .\SQLEXPRESS. The database filename is set to the database file located at C:\MyApplication\PUBS.MDF. Note that the log file (PUBS_LOG.LDF) must also exist. Integrated security is used to authenticate with SQL Express; setting User Instance to true starts an instance of SQL Express using the current user's account.
Although you can use SQL Server to attach to a local file, SQL Server does not work with the User Instance=True setting. Also, SQL Server keeps the database attached when your application ends, so the next time you run SQL Server an exception is thrown because the data file is already attached.
AttachDBFile can also understand the keyword |DataDirectory| to use the application's data directory. Here is the revised connection string.
Data Source=.\SQLEXPRESS; AttachDbFilename=|DataDirectory|\PUBS.MDF; Integrated Security=True; User Instance=True
How Is the DataDirectory Resolved? Internally, the System.Data.dll library contains a class called System.Data.Common.DbConnectionOptions, which has a method called ExpandDataDirectory. This method includes code that resolves the |DataDirectory| keyword by executing code that looks something like the following.
Visual Basic
Dim path As string = AppDomain.CurrentDomain.GetData("DataDirectory") If path = string.Empty Then path = AppDomain.CurrentDomain.BaseDirectory End If Return path
What does this mean? Well, first of all, the ExpandDataDirectory method tries to get the DataDirectory location from the current assembly. The DataDirectory is set for every ClickOnce application installed on a local computer. The DataDirectory is located in the user's Documents And Settings folder. If a database file (.mdf) and its log file (.ldf) are included in a ClickOnce application and marked as a "data" file, they are copied to this directory on application install. If the ClickOnce application is uninstalled, the application's data directory and the contents of the data directory are destroyed.
Notice that the sample code uses the BaseDirectory of CurrentDomain if there is no DataDirectory. The BaseDirectory is the directory that contains the compiled application (bin\Debug). Instead of placing the database file directly in the compiled application folder, it's better to place it in the project folder and set Copy To Output Directory to Copy Always or Copy If Newer.
Storing the Connection String in the Application Configuration File
You can store connectionStrings in the machine or application configuration file, which means that the connection strings can be changed without requiring a recompile of the application. You place the <connectionStrings> element under the <configuration> root element. This section supports the <add>, <remove>, and <clear> tags, as shown here:
This example clears the list of connectionSettings that may have been defined in the machine configuration file and then adds a new connection string setting called PubsData. The connectionStrings can be accessed in code by using the static ConnectionStrings collection on the ConfigurationManager class, as shown in the following code snippet.
Visual Basic
'Get the settings from the configuration file Dim pubs As ConnectionStringSettings pubs = ConfigurationManager.ConnectionStrings("PubsData") 'name = "PubsData" Dim name As String = pubs.Name 'provider = "System.Data.SqlClient" Dim provider As String = pubs.ProviderName 'cnString = "Data Source=.\SQLEXPRESS; ' AttachDbFilename=|DataDirectory|PUBS.MDF; ' Integrated Security=True; ' User Instance=True" Dim cnString As String = pubs.ConnectionString
C#
//Get the settings from the configuration file ConnectionStringSettings pubs = ConfigurationManager.ConnectionStrings["PubsData"]; DbConnection connection = new SqlConnection(pubs.ConnectionString); //name = "PubsData" string name = pubs.Name; //provider = "System.Data.SqlClient" string provider = pubs.ProviderName; //cnString = "Data Source=.\SQLEXPRESS; // AttachDbFilename=|DataDirectory|\PUBS.MDF; // Integrated Security=True; // User Instance=True" string cnString = pubs.ConnectionString;
GetSchema Method
The DbConnection object contains a method called GetSchema, which you can use to query a data store for its schema information. The method can be useful when you need to discover a data store's metadata dynamically. To use the GetSchema method, you open a connection, call GetSchema, and then close the connection. The following sample code demonstrates the retrieval of the schema information from the pubs database and binding the returned results to a DataGridView object. The output of this code is shown in Figure 2-2.
Figure 2-2: The GetSchema method provides metadata information.
Visual Basic
'Get the settings from the configuration file Dim pubs As ConnectionStringSettings pubs = ConfigurationManager.ConnectionStrings("PubsData") Dim connection As DbConnection = New SqlConnection() connection.ConnectionString = pubs.ConnectionString connection.Open() Dim schema As DataTable = connection.GetSchema() connection.Close() dataGridView1.DataSource = schema
C#
//Get the settings from the configuration file ConnectionStringSettings pubs = ConfigurationManager.ConnectionStrings["PubsData"]; DbConnection connection = new SqlConnection(pubs.ConnectionString); connection.Open(); DataTable schema = connection.GetSchema(); connection.Close(); dataGridView1.DataSource = schema;
Notice that the schema is returned as a DataTable object that contains three DataColumn objects:
CollectionName Identifies the name of a collection that can be queried for more specific information. For example, one of the collection names is Tables. If you pass Tables to the GetSchema method, you receive a list of all of the tables in the database.
NumberOfRestrictions For each collection item, this represents an array of qualifiers that can be used to restrict the scope of the schema information when you request it.
NumberOfIdentifierParts For each collection item, this represents the number of parts that can be joined together to form a fully qualified object name. For example, the Tables collection name contains three identifier parts: the database name, the user name, and the name of the DataTable itself.
You can use this information to query for more specific information. For the following example, another DataGridView object was added to the form and code was added to the SelectionChanged event of the first DataGridView object, which populates the second DataGridView object as you select a different row. The SelectionChanged code looks like the following:
Visual Basic
Private Sub DataGridView1_SelectionChanged( _ ByVal sender As System.Object, _ ByVal e As System.EventArgs) _ Handles DataGridView1.SelectionChanged Dim schema As DataTable = DataGridView1.DataSource Dim currentRow As DataRow = _ schema.Rows(DataGridView1.CurrentCell.RowIndex) Dim collectionName As String = currentRow("CollectionName") 'Get the settings from the configuration file Dim pubs As ConnectionStringSettings pubs = ConfigurationManager.ConnectionStrings("PubsData") Dim connection As DbConnection = New SqlConnection() connection.ConnectionString = pubs.ConnectionString connection.Open() Dim schema2 As DataTable = connection.GetSchema(collectionName) connection.Close() DataGridView2.DataSource = schema2 End Sub
The results can be seen in Figure 2-3. Notice that the Tables CollectionName is selected, which triggers the SelectionChanged event code. The lower DataGridView object contains the more specific metadata for the tables in the database. There are four columns, each of which can be used to filter the information when you execute the GetSchema method. Also, the fully qualified table name has three parts: the table_catalog, the table_schema, and the table_name. For more information on metadata, refer to Chapter 11, "Retrieving Metadata."
Figure 2-3: The GetSchema method is executed again, with Tables passed in as a parameter to retrieve more specific metadata for the tables in the database.
DbCommand Object
You use the DbCommand object to send a Structured Query Language (SQL) command to the data store. The DbCommand can be a Data Manipulation Language (DML) command to retrieve, insert, update, or delete data. The DbCommand object can also be a Data Definition Language (DDL) command, which allows you to create tables and modify schema information at the database. The DbCommand object requires a valid open connection to issue the command to the data store. A DbConnection object can be passed into the DbCommand object's constructor or attached to the DbCommand object's Connection property after the DbCommand is created, but the best way to create a DbCommand is to use the CreateCommand method on the DbConnection object. Using the CreateCommand method on the DbConnection means that provider-specific code is limited to the creation of the DbConnection, and the DbConnection automatically creates the appropriate provider-specific DbCommand.
The DbCommand also requires a valid value for its CommandText and CommandType properties. The following code snippet shows how to create and initialize a DbCommand.
Visual Basic
Dim pubs As ConnectionStringSettings pubs = ConfigurationManager.ConnectionStrings("PubsData") Dim connection As DbConnection = New SqlConnection() connection.ConnectionString = pubs.ConnectionString Dim cmd As DbCommand = connection.CreateCommand() cmd.CommandType = CommandType.StoredProcedure cmd.CommandText = "uspGetCustomerById"
This code creates a DbConnection object that is a SqlConnection object. The DbConnection object is then used to create a SqlCommand, which is assigned to cmd. The DbConnection must be opened before the command can be executed. This command executes a stored procedure. Notice that the CommandText property contains the name of the stored procedure, whereas the CommandType property indicates that this is a call to a stored procedure.
DbParameter Objects
Stored procedures typically require parameter values to be passed to them in order to execute. For example, a user-defined stored procedure called uspGetCustomerById might require a customer identification to retrieve the appropriate customer. You can create DbParameter objects by using the Parameters.Add method of the Command object, as shown here.
Visual Basic
Dim pubs As ConnectionStringSettings pubs = ConfigurationManager.ConnectionStrings("PubsData") Dim connection As DbConnection = New SqlConnection() connection.ConnectionString = pubs.ConnectionString Dim cmd As DbCommand = connection.CreateCommand() cmd.CommandType = CommandType.StoredProcedure cmd.CommandText = "uspGetCustomerById" Dim parm As DbParameter = cmd.CreateParameter() parm.ParameterName = "@Id" parm.Value = "AROUT" cmd.Parameters.Add(parm)
This code creates a DbConnection object and a DbCommand object. It also configures the DbCommand object to execute a stored procedure called uspGetCustomerById, which requires a single parameter called @Id that is assigned the value "AROUT".
Note
The SQL provider requires that the parameter names match the parameter names defined in the stored procedure. The creation of the parameters is therefore not order dependent.
The OLEDB provider on the other hand requires the parameters to be defined in the same order that they are defined in the stored procedure. This means the name assigned to the parameter need not match the name defined in the stored procedure.
You can use the name assigned to the DbParameter object to access the parameter through code. For example, to retrieve the value that is currently in the SqlParameter called @Id, use the following code.
Visual Basic
Dim id as String = cmd.Parameters("@Id").Value
C#
string id = (string)((DbParameter)cmd.Parameters["@Id"]).Value;
ExecuteNonQuery Method
You execute a DbCommand object differently, depending on the data that is being retrieved or modified. You use the ExecuteNonQuery method when you don't expect a command to return any rows an insert, update, or delete query, for example. This method returns an integer that represents the number of rows affected by the operation. The following example executes a stored procedure to increment the qty field in the sales table for sales with qty greater that 50, and it returns the number of rows that were updated.
Visual Basic
Dim pubs As ConnectionStringSettings pubs = ConfigurationManager.ConnectionStrings("PubsData") Dim connection As DbConnection = New SqlConnection() connection.ConnectionString = pubs.ConnectionString Dim cmd As DbCommand = connection.CreateCommand() cmd.CommandType = CommandType.Text cmd.CommandText = "UPDATE SALES SET qty = qty + 1 WHERE qty > 50" connection.Open() Dim count As Integer = cmd.ExecuteNonQuery() connection.Close()
C#
ConnectionStringSettings pubs = ConfigurationManager.ConnectionStrings["PubsData"]; DbConnection connection = new SqlConnection(pubs.ConnectionString); DbCommand cmd = connection.CreateCommand(); cmd.CommandType = CommandType.Text; cmd.CommandText = "UPDATE SALES SET qty = qty + 1 WHERE qty > 50"; connection.Open(); int count = cmd.ExecuteNonQuery(); connection.Close();
ExecuteScalar Method
Queries are often expected to return a single row with a single column. In these situations, the results can be treated as a single return value. For example, the following SQL returns a result that consists of a single row with a single column.
SELECT COUNT(*) FROM Sales
If you use the ExecuteScalar method, the .NET runtime will not create an instance of a DataTable for the result, which means less resource usage and better performance. The following code shows how to use the ExecuteScalar method to easily retrieve the number of rows in the Sales table into a variable called count.
Visual Basic
Dim pubs As ConnectionStringSettings pubs = ConfigurationManager.ConnectionStrings("PubsData") Dim connection As DbConnection = New SqlConnection() connection.ConnectionString = pubs.ConnectionString Dim cmd As DbCommand = connection.CreateCommand() cmd.CommandType = CommandType.Text cmd.CommandText = "SELECT COUNT(*) FROM Sales" connection.Open() Dim count As Integer = cmd.ExecuteScalar() connection.Close() MessageBox.Show(count.ToString())
C#
ConnectionStringSettings pubs = ConfigurationManager.ConnectionStrings["PubsData"]; DbConnection connection = new SqlConnection(pubs.ConnectionString); DbCommand cmd = connection.CreateCommand(); cmd.CommandType = CommandType.Text; cmd.CommandText = "SELECT COUNT(*) FROM Sales"; connection.Open(); int count = (int)cmd.ExecuteScalar(); connection.Close();
ExecuteReader Method
The ExecuteReader method returns a DbDataReader instance. The DbDataReader object is a forward-only, read-only, server-side cursor. DbDataReader objects can be created only by executing one of the ExecuteReader methods on the DbCommand object. (See the next section for more information on the DbDataReader.) The following example uses the ExecuteReader method to create a DbDataReader object with the selection results and then continuously loops through the results until the end of data has been reached (when the Read method returns false).
Visual Basic
Dim pubs As ConnectionStringSettings pubs = ConfigurationManager.ConnectionStrings("PubsData") Dim connection As DbConnection = New SqlConnection() connection.ConnectionString = pubs.ConnectionString Dim cmd As DbCommand = connection.CreateCommand() cmd.CommandType = CommandType.Text cmd.CommandText = "SELECT stor_id, ord_num FROM Sales" connection.Open() Dim rdr As DbDataReader = cmd.ExecuteReader() While (rdr.Read()) MessageBox.Show(rdr("stor_id") + ": " + rdr("ord_num")) End While connection.Close()
A DbDataReader object provides a high-performance method of retrieving data from the data store. It delivers a forward-only, read-only, server-side cursor. This makes the DbDataReader object an ideal choice for populating ListBox objects and DropDownList objects. When you run reports, you can use the DbDataReader object to retrieve the data from the data store. The DbDataReader might not be a good choice when you are coding an operation that modifies data and needs to send the changes back to the database. For data modifications, the DbData-Adapter object, which is discussed in the next section, might be a better choice.
The DbDataReader contains a Read method that retrieves data into its buffer. Only one row of data is ever available at a time, which means that the data does not need to be completely read into the application before it is processed. The following code populates a new DataTable directly with the list of publishers from the pubs database.
Visual Basic
Dim pubs As ConnectionStringSettings pubs = ConfigurationManager.ConnectionStrings("PubsData") Dim connection As DbConnection = New SqlConnection() connection.ConnectionString = pubs.ConnectionString Dim cmd As DbCommand = connection.CreateCommand() cmd.CommandType = CommandType.Text cmd.CommandText = "SELECT pub_id, pub_name FROM publishers" connection.Open() Dim rdr As DbDataReader = cmd.ExecuteReader() Dim publishers As New DataTable() publishers.Load(rdr, LoadOption.Upsert) connection.Close()
Notice that the DataTable object's Load method contains a LoadOption parameter. The Load-Option gives you the option of deciding which DataRowVersion should get the incoming data. For example, if you load a DataTable object, modify the data, and then save the changes back to the database, you might encounter concurrency errors if someone else has modified the data between the time you got the data and the time you attempted to save the data. One option is to load the DataTable object again, using the default PreserveCurrentValues enumeration value, which loads the original DataRowVersion with the data from the database while leaving the current DataRowVersion untouched. Next you can simply execute the Update method again and the update will succeed.
For this to work properly, the DataTable must have a PrimaryKey defined. Failure to define a PrimaryKey results in duplicate DataRow objects being added to the DataTable object. The LoadOption enumeration members are described in Table 2-5.
Table 2-5: LoadOption Enumeration Members
LoadOption Member
Description
OverwriteChanges
Overwrites the original DataRowVersion and the current DataRowVersion and changes the RowState to Unchanged. New rows will have a RowState of Unchanged as well.
PreserveChanges (default)
Overwrites the original DataRowVersion but does not modify the current DataRowVersion. New rows have a RowState of Unchanged as well.
Upsert
Overwrites the current DataRowVersion but does not modify the original DataRowVersion. New rows have a RowState of Added. Rows that had a RowState of Unchanged have a RowState of Unchanged if the current DataRowVersion is the same as the original DataRowVersion, but if they are different, the RowState is Modified.
Using Multiple Active Result Sets (MARS) to Execute Multiple Commands on a Connection
Using the DbDataReader object is one of the fastest methods to retrieve data from the database, but one of the problems with the DbDataReader is that it keeps an open server-side cursor while you are looping through the results of your query. If you try to execute another command while the first command is still executing, you receive an InvalidOperationException message stating that "There is already an open DataReader associated with this Connection which must be closed first." You can avoid this exception by setting the MultipleActiveResult-Sets connection string option to true when connecting to MARS-enabled hosts, such as SQL Server 2005. For example, the following connection string shows how this setting is added into a new connection string called PubsDataMars.
MARS does not provide any performance gains, but it does simplify your coding efforts. Think of a scenario in which you execute a query to get a list of stores, and while you loop through a list of stores that are returned, you want to execute a second query to get the total quantity of books sold.
MARS is not something that you can't live without. MARS simply makes your programming easier. As a matter of fact, setting MultipleActiveResultSets = True in the connection string has a negative performance impact, so you should not turn on MARS arbitrarily.
On a database server without MARS, you could first collect the list of stores into a collection and close the connection. After that, you can loop through the collection to get each store ID and execute a query to get the total quantity of books sold for that store. This means that you loop through the stores twice once to populate the collection and again to get each store and execute a query to get the store's quantity of book sales. Another solution is simply to create two connections: one for the store list and one for the quantity of books sold query.
Another benefit that MARS provides is that you might have purchased database client licenses that are based on the quantity of connections to the database. Without MARS, you would have to open a separate connection to the database for each command that needs to run at the same time, which means that you might need to purchase more client licenses.
The following code snippet shows how MARS can be used to perform the nested queries for the store list and the quantity of books sold.
Visual Basic
Dim pubs As ConnectionStringSettings pubs = ConfigurationManager.ConnectionStrings("PubsDataMars") Dim connection As DbConnection = New SqlConnection() connection.ConnectionString = pubs.ConnectionString Dim cmd As DbCommand = connection.CreateCommand() cmd.CommandType = CommandType.Text cmd.CommandText = "SELECT stor_id, stor_name FROM Stores" connection.Open() Dim rdr As DbDataReader = cmd.ExecuteReader() while rdr.Read() Dim salesCmd as DbCommand = connection.CreateCommand() salesCmd.CommandType = CommandType.Text salesCmd.CommandText = _ "SELECT SUM(qty) FROM sales WHERE (stor_id = @storeId)" Dim parm as DbParameter = salesCmd.CreateParameter() parm.ParameterName = "@storeId" parm.Value = rdr("stor_id") salesCmd.Parameters.Add(parm) Dim qtySales as object= salesCmd.ExecuteScalar() MessageBox.Show(rdr("stor_name").ToString() + ": " + qtySales.ToString()) End while connection.Close()
Performing Bulk Copy Operations with the SqlBulkCopy Object
There are many occasions for which you need to copy large amounts of data from one location to another. Most of the database servers provide a means to copy from one database to another, either by a Windows GUI interface, such as SQL Server's Enterprise Manager, or through a command-line tool, such as SQL Server's Bulk Copy Program (BCP.exe). In addition to using the tools that are provided by the database vendor, you also can write your own bulk copy program using the SqlBulkCopy class.
The SqlBulkCopy class provides a high performance method for copying data to a table in a SQL Server database. The source of the copy is constrained overloads of the WriteToServer method, which can accept an array of DataRow objects, an object that implements the IDb-DataReader interface, a DataTable object, or a DataTable object and DataRowState enumeration value, as shown in Figure 2-4. This variety of parameters means that you can retrieve data from most locations.
Figure 2-4: The SqlBulkCopy object can copy from a variety of sources to a SQL Server table.
The following code shows how you can use a SqlBulkCopy object to copy data from the Store table in the pubs database to the StoreList table in a SQL Server database called BulkCopy.
Visual Basic
Private Sub BtnBulkCopy_Click( _ ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles BtnBulkCopy.Click Dim pubs As ConnectionStringSettings pubs = ConfigurationManager.ConnectionStrings("PubsData") Dim connection As DbConnection = New SqlConnection() connection.ConnectionString = pubs.ConnectionString Dim bulkCopy As ConnectionStringSettings bulkCopy = ConfigurationManager.ConnectionStrings("BulkCopy") Dim bulkCopyConnection As DbConnection = New SqlConnection() bulkCopyConnection.ConnectionString = bulkCopy.ConnectionString Dim cmd As DbCommand = connection.CreateCommand() cmd.CommandType = CommandType.Text cmd.CommandText = "SELECT stor_name FROM Stores" connection.Open() bulkCopyConnection.Open() Dim rdr As DbDataReader = cmd.ExecuteReader() Dim bc As New SqlBulkCopy(bulkCopyConnection) bc.DestinationTableName = "StoreList" bc.WriteToServer(rdr) connection.Close() bulkCopyConnection.Close() MessageBox.Show("Done with bulk copy") End Sub
C#
private void btnBulkCopy_Click(object sender, EventArgs e) { ConnectionStringSettings pubs = ConfigurationManager.ConnectionStrings["PubsData"]; DbConnection connection = new SqlConnection(pubs.ConnectionString); ConnectionStringSettings bulkCopy = ConfigurationManager.ConnectionStrings["BulkCopy"]; SqlConnection bulkCopyConnection = new SqlConnection(bulkCopy.ConnectionString); DbCommand cmd = connection.CreateCommand(); cmd.CommandType = CommandType.Text; cmd.CommandText = "SELECT stor_name FROM Stores"; connection.Open(); bulkCopyConnection.Open(); DbDataReader rdr = cmd.ExecuteReader(); SqlBulkCopy bc = new SqlBulkCopy(bulkCopyConnection); bc.DestinationTableName = "StoreList"; bc.WriteToServer(rdr); connection.Close(); bulkCopyConnection.Close(); MessageBox.Show("Done with bulk copy"); }
You should consider using the IDbDataReader parameter whenever possible to get the best performance with the least resources used. You can decide how much data should be copied based on the query that you use. For example, the preceding code sample retrieved only the store names and could have had a WHERE clause to further limit the data.
DbDataAdapter Object
You use the DbDataAdapter object to retrieve and update data between a DataTable and a data store. The DbDataAdapter is derived from the DataAdapter class and is the base class of the provider-specific DbDataAdapter classes, as shown in Figure 2-5.
Figure 2-5: The DbDataAdapter hierarchy, showing the DataAdapter base class and the provider-specific derived classes
The DbDataAdapter has a SelectCommand property that you use when retrieving the data. The SelectCommand must contain a valid DbCommand object, which must have a valid connection. Internally, the SelectCommand property's ExecuteReader method is executed to get a DbData-Reader object, which is used to populate a DataTable object.
The DbDataAdapter also has InsertCommand, UpdateCommand, and DeleteCommand properties, which might contain DbCommand objects. You use these commands if you want to save DataTable changes back to the data store. You need not create these command objects if you only need to read data from the data store, but if you create one of these latter three commands, you must create all four of them.
Using the Fill Method
The Fill method moves data from the data store to the DataTable object that you pass into this method. The Fill method has several overloads, some of which accept only a DataSet as a parameter. When a DataSet is passed to the Fill method, a new DataTable object is created in the DataSet if a source DataTable object is not specified.
The following code snippet shows how a DataTable can be loaded using the Fill method.
Visual Basic
Dim pubs As ConnectionStringSettings pubs = ConfigurationManager.ConnectionStrings("PubsData") Dim connection As DbConnection = New SqlConnection() connection.ConnectionString = pubs.ConnectionString Dim cmd As SqlCommand = Ctype(connection.CreateCommand(),SqlCommand) cmd.CommandType = CommandType.Text cmd.CommandText = "SELECT pub_id, pub_name FROM publishers" Dim pubsDataSet as New DataSet("Pubs") Dim da as New SqlDataAdapter(cmd) da.Fill(pubsDataSet, "publishers")
C#
ConnectionStringSettings pubs = ConfigurationManager.ConnectionStrings["PubsData"]; DbConnection connection = new SqlConnection(pubs.ConnectionString); SqlCommand cmd = (SqlCommand)connection.CreateCommand(); cmd.CommandType = CommandType.Text; cmd.CommandText = "SELECT pub_id, pub_name FROM Publishers"; DataAdapter da = new SqlDataAdapter(cmd); DataSet pubsDataSet = new DataSet("Pubs"); da.Fill(pubsDataSet, "publishers");
Many developers attempt to use a single DbDataAdapter for all of their queries or try to use a single DbDataAdapter to execute a SQL statement that returns a result set from multiple tables joined in the SQL query. If you need to store the data changes, you should consider using a separate DbDataAdapter for each DataTable that is being loaded, as shown in Figure 2-6. If all you need is a read-only DataTable, you can simply use a DbCommand object and DbDataReader object to load the DataTable.
Figure 2-6: The DbDataAdapter, which has four DbCommand objects, should be used to populate each DataTable if the DataTable will contain read-write data.
Saving Changes to the Database Using the Update Method
The Update method saves the DataTable modifications to the database by retrieving the changes from the DataTable and then using the respective InsertCommand, UpdateCommand, or DeleteCommand to send the appropriate change to the database on a row-by-row basis. The Update method retrieves the DataRow objects that have changed by looking at the RowState property of each row. If the RowState is anything but Unchanged, the Update method sends the change to the database.
For the Update method to work, all four commands must be assigned to the DbDataAdapter. Normally, this means creating individual DbCommand objects for each command. You can easily create the commands by using the DbDataAdapter configuration wizard, which starts when a DbDataAdapter is dropped onto the form. The wizard can generate stored procedures for all four commands.
Another way to populate the DbDataAdapter object's commands is to use the DbCommandBuilder object. This object creates the InsertCommand, UpdateCommand, and DeleteCommand as long as a valid SelectCommand exists. The DbDataAdapter is great for ad hoc changes and demos, but it's generally better to use stored procedures for all database access to eliminate security risk from SQL injection attacks. The following code demonstrates a simple update to the database using the SqlDataAdapter, which is the SQL Server-specific version of the DbDataAdapter.
Visual Basic
Dim pubs As ConnectionStringSettings pubs = ConfigurationManager.ConnectionStrings("PubsData") Dim connection As DbConnection = New SqlConnection() connection.ConnectionString = pubs.ConnectionString Dim cmd As SqlCommand = CType(connection.CreateCommand(), SqlCommand) cmd.CommandType = CommandType.Text cmd.CommandText = "SELECT * FROM publishers" Dim pubsDataSet As New DataSet("Pubs") Dim da As New SqlDataAdapter(cmd) Dim bldr as New SqlCommandBuilder(da) da.Fill(pubsDataSet, "publishers") 'Modify data here pubsDataSet.Tables("publishers").Rows(0)("pub_name")="Hello" pubsDataSet.Tables("publishers").Rows.Add( _ "9911", "Tailspin Toys","Paris", Nothing, "France") da.Update(pubsDataSet, "publishers") MessageBox.Show("Update Complete")
C#
ConnectionStringSettings pubs = ConfigurationManager.ConnectionStrings["PubsData"]; DbConnection connection = new SqlConnection(pubs.ConnectionString); SqlCommand cmd = (SqlCommand)connection.CreateCommand(); cmd.CommandType = CommandType.Text; cmd.CommandText = "SELECT * FROM Publishers"; SqlDataAdapter da = new SqlDataAdapter(cmd); DataSet pubsDataSet = new DataSet("Pubs"); SqlCommandBuilder bldr = new SqlCommandBuilder(da); da.Fill(pubsDataSet,"publishers"); //Modify data here pubsDataSet.Tables[0].Rows[0]["pub_name"] = "Hello"; pubsDataSet.Tables["publishers"].Rows.Add( "9911", "Tailspin Toys", "Paris", null, "France"); da.Update(pubsDataSet,"publishers"); MessageBox.Show("Update Complete");
Note that if you click the button two times, an exception is thrown that indicates that you are trying to insert duplicate rows.
Saving Changes to the Database in Batches
If you use the SQL Profiler tool to view the update commands sent to SQL Server, you will notice that individual insert, update, and delete commands are sent to SQL Server on a row-by-row basis. One way to increase update performance is to send the changes to the database server in batches. You can do this by assigning a value to the DbDataAdapter object's UpdateBatchSize property. This property defaults to 1, which causes each change to be sent to the server on a row-by-row basis. Setting the value to 0 instructs the DbDataAdapter object to create the largest possible batch size for changes, or you can set the value to the number of changes you want to send to the server in each batch. Setting the UpdateBatchSize to a number greater than the number of changes that need to be sent is equivalent to setting it to 0.
One way to confirm that the changes are being sent to the database server in batches is to add a RowUpdated event to the DbDataAdapter object. The event handler method exposes the number of rows affected in the last batch. When the UpdateBatchSize is set to 1, the RecordsAffected property will always be 1. In the following code snippet, the publishers table contains eight rows. The pubsDataSet is filled, and then the pub_name field is modified on all eight rows. Before the Update method is executed, the UpdateBatchSize is changed to 3. When the Update method is executed, the changes are sent to the database as a batch of three changes, another batch of three changes, and finally a batch of two changes. This code contains a RowUpdated event handler to collect batch information, which is displayed after the Update method is executed.
Visual Basic
Public WithEvents da As New SqlDataAdapter() public sb as New System.Text.StringBuilder() private sub rowUpdated(byval sender as Object, _ byval e as SqlRowUpdatedEventArgs) handles da.RowUpdated sb.Append("Rows: " & e.RecordsAffected.ToString() & vbCrLf) End Sub Private Sub btnUpdateBatch_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles btnUpdateBatch.Click Dim pubs As ConnectionStringSettings pubs = ConfigurationManager.ConnectionStrings("PubsData") Dim connection As DbConnection = New SqlConnection() connection.ConnectionString = pubs.ConnectionString Dim cmd As SqlCommand = CType(connection.CreateCommand(), SqlCommand) cmd.CommandType = CommandType.Text cmd.CommandText = "SELECT * FROM publishers" Dim pubsDataSet As New DataSet("Pubs") da.SelectCommand = cmd Dim bldr As New SqlCommandBuilder(da) da.Fill(pubsDataSet, "publishers") 'Modify data here For Each dr As DataRow In pubsDataSet.Tables("publishers").Rows dr("pub_name") = "Updated Toys" Next da.UpdateBatchSize = 3 da.Update(pubsDataSet, "publishers") MessageBox.Show(sb.ToString()) End Sub
C#
public SqlDataAdapter da = new SqlDataAdapter(); public System.Text.StringBuilder sb = new System.Text.StringBuilder(); private void rowUpdated(object sender, SqlRowUpdatedEventArgs e ) { sb.Append("Rows: " + e.RecordsAffected.ToString() + "\r\n"); } private void btnUpdateBatch_Click(object sender, EventArgs e) { //event subscription is normally placed in constructor but is here //to encapsulate the sample da.RowUpdated+=new SqlRowUpdatedEventHandler(rowUpdated); ConnectionStringSettings pubs = ConfigurationManager.ConnectionStrings["PubsData"]; DbConnection connection = new SqlConnection(pubs.ConnectionString); SqlCommand cmd = (SqlCommand)connection.CreateCommand(); cmd.CommandType = CommandType.Text; cmd.CommandText = "SELECT * FROM Publishers"; da.SelectCommand = cmd; DataSet pubsDataSet = new DataSet("Pubs"); SqlCommandBuilder bldr = new SqlCommandBuilder(da); da.Fill(pubsDataSet, "publishers"); //Modify data here foreach (DataRow dr in pubsDataSet.Tables["publishers"].Rows) { dr["pub_name"] = "Updated Toys"; } da.UpdateBatchSize = 3; da.Update(pubsDataSet, "publishers"); //if event subscription is in the constructor, no need to //remove it here.... da.RowUpdated -= new SqlRowUpdatedEventHandler(rowUpdated); MessageBox.Show(sb.ToString()); }
DbProviderFactory Classes
There are many reasons for writing an application that does not require database provider-specific code. A company might want the flexibility to upgrade from one database product to another, such as moving from Microsoft Access to SQL Server. Or a company might have a retail application that must allow connectivity to any data source. With earlier versions of ADO.NET, you can write a provider-independent application by using generic interfaces. The typical coding might look something like the following:
Visual Basic
Public Enum DbProvider SqlClient OleDb Odbc Oracle End Enum Public Function GetConnection() As IDbConnection 'Get the provider from the config file Dim provider As DbProvider = [Enum].Parse( _ GetType(DbProvider), _ ConfigurationSettings.AppSettings("provider").ToString()) Dim connection As IDbConnection = Nothing Select Case (provider) Case DbProvider.SqlClient connection = New System.Data.SqlClient.SqlConnection() Case DbProvider.OleDb connection = New System.Data.OleDb.OleDbConnection() Case DbProvider.Odbc connection = New System.Data.Odbc.OdbcConnection() Case DbProvider.Oracle connection = New System.Data.OracleClient.OracleConnection() End Select Return connection End Function
C#
public IDbConnection GetConnection() { // Get the provider from the config file DbProvider provider = (DbProvider)Enum.Parse( typeof(DbProvider), (string)ConfigurationManager.AppSettings["provider"]); IDbConnection connection = null; switch (provider) { case DbProvider.SqlClient: connection = new System.Data.SqlClient.SqlConnection(); break; case DbProvider.OleDb: connection = new System.Data.OleDb.OleDbConnection(); break; case DbProvider.Odbc: connection = new System.Data.Odbc.OdbcConnection(); break; case DbProvider.Oracle: connection = new System.Data.OracleClient.OracleConnection(); break; } return connection; } public enum DbProvider { SqlClient, OleDb, Odbc, Oracle };
One problem with this approach is that you can't create interface instances directly, so provider-specific code exists to determine the type of connection to create. Another problem is that interfaces are immutable by definition, so new features can't be easily added without adding a new interface.
ADO.NET provides base classes from which the provider-specific classes inherit, as shown earlier in Table 2-1. The .NET Framework supports only single inheritance, so this approach has limitations if you want to create your own base class, but for classes that will expand, providing base class inheritance is better than providing interface implementation. Note that interfaces are still provided for backward compatibility.
The preceding code listing addresses only the creation of the connection object. You would duplicate the code if you wanted to create many of the other provider objects, such as the data adapter and command objects. To keep from duplicating this conditional code for each of the provider objects, you can create a factory object that is responsible for creating the appropriate provider objects. This is where the DbProviderFactory is used. Each provider must supply a subclass of DbProviderFactory that can be used to create instances of its provider classes. For example, you can use the SqlClientFactory to create instances of any of the SQL Server classes. Figure 2-7 shows the DbProviderFactory and the SqlClientFactory classes, along with their properties and methods.
Figure 2-7: The DbProviderFactory and the SqlClientFactory classes
The provider factory classes are implemented as singletons, where each class provides an "Instance" property that is used to access the methods and properties shown in Figure 2-7. For example, you can use the following code to create a new connection using the SqlClientFactory.
Visual Basic
'Get the singleton instance Dim factory As DbProviderFactory = SqlClientFactory.Instance Public Function GetProviderConnection() As DbConnection Dim connection As DbConnection = factory.CreateConnection() connection.ConnectionString = "Data Source=.\SQLEXPRESS;" _ & "AttachDbFilename=|DataDirectory|PUBS.MDF;" _ & "Integrated Security=True;User Instance=True" Return connection End Function
You can use the factory variable to create any of the other SQL Server-specific objects. Note that the SqlDataReader is created indirectly, by creating a SqlCommand and then using the ExecuteReader method, as shown in the following code snippet.
Visual Basic
Private Function GetData(ByVal commandText As String, _ ByVal commandType As CommandType) As DataTable 'get SqlDbCommand Dim command As DbCommand = factory.CreateCommand() command.Connection = GetProviderConnection() If (command.Connection Is Nothing) Then Return Nothing End If command.CommandText = commandText command.CommandType = commandType command.Connection.Open() Dim dataTable As DataTable = New DataTable() 'Get SqlDataReader and populate data table dataTable.Load(command.ExecuteReader()) command.Connection.Close() Return dataTable End Function
This code snippet uses the factory variable to create a DbCommand, which is then used to create the DbDataReader.
DbProviderFactories Class
You can use the DbProviderFactory object's subclasses to obtain a provider factory that can create any of the provider-specific objects, but it sure would be nice to retrieve a list of the provider factories that are available on a machine, or within an application. To query for the list of available factories, you can use the DbProviderFactories class. This class is a factory for obtaining factories. It contains a method called GetFactoryClasses that returns a DataTable that is populated with information describing all available providers. Retrieving the list of providers can be easily demonstrated by creating a Windows form and adding a Button and a DataGrid-View control. Add the following code to the click event of the button.
Visual Basic
Dim providersList as DataTable = nothing Private Sub Button1_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button1.Click providersList = DbProviderFactories.GetFactoryClasses() DataGridView1.DataSource = providersList End Sub
When this simple application is run and the button is clicked, the screen shown in Figure 2-8 is displayed.
Figure 2-8: The provider factory classes that are available on this computer
The invariant column contains a string that you can use to retrieve a specific provider. The name and description provide information that you can use to display a friendly provider list to an application user. The assembly names listed are fully qualified. Any provider on the list must be located within the application's probing path. This means that the .NET runtime must be able to locate the provider. In most situations, the provider library is installed in the Global Assembly Cache (GAC) or the application folder.
The provider list shown in Figure 2-8 is from the Machine.config file, which by default contains the following provider information within the <configuration> root element:
Machine.config File
<system.data> <DbProviderFactories> <add name="Odbc Data Provider" invariant="System.Data.Odbc" description=".Net Framework Data Provider for Odbc" type="System.Data.Odbc.OdbcFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" /> <add name="OleDb Data Provider" invariant="System.Data.OleDb" description=".Net Framework Data Provider for OleDb" type="System.Data.OleDb.OleDbFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" /> <add name="OracleClient Data Provider" invariant="System.Data.OracleClient" description=".Net Framework Data Provider for Oracle" type="System.Data.OracleClient.OracleClientFactory, System.Data.OracleClient, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" /> <add name="SqlClient Data Provider" invariant="System.Data.SqlClient" description=".Net Framework Data Provider for SqlServer" type="System.Data.SqlClient.SqlClientFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" /> <add name="SQL Server CE Data Provider" invariant="Microsoft.SqlServerCe.Client" description=".NET Framework Data Provider for Microsoft SQL Server 2005 Mobile Edition" type="Microsoft.SqlServerCe.Client.SqlCeClientFactory, Microsoft.SqlServerCe.Client, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" /> </DbProviderFactories></system.data>
Notice that DbDatabaseProviderFactories uses the <add> element. Using the <add> element, you can add more providers to the Machine.config file or the application's configuration file. You can also use the <remove> tag to remove providers from the default Machine.config list. For example, the following is a sample App.config file that removes the Odbc provider from the defaults defined in Machine.config.
If very few specific providers (such as SQL Server and Oracle) are required, you can use the <clear> element to remove all of the providers in the Machine.config file and then use the <add> element to add the desired providers back into the list. The following example clears the provider list and adds the SQL Server provider back into the list.
App.config File
<configuration> <system.data> <DbProviderFactories> <clear/> <add name="SqlClient Data Provider" invariant="System.Data.SqlClient" description=".Net Framework Data Provider for SqlServer" type="System.Data.SqlClient.SqlClientFactory, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" /> </DbProviderFactories> </system.data> </configuration>
Enumerating Data Sources
Sometimes you want to display a list of the available data sources for a given provider. For example, if an application allows data to be read from one SQL Server and written to a different SQL Server, it might require a dialog box for selecting from a list of available SQL Servers for the source and destination servers. We can add an event to the previous sample Windows application that will fire when the user double-clicks a row. We can add code to the event method that uses the selected provider to obtain a list of data sources for that provider. With the list of data sources, we can display a dialog box to prompt the user to select a data source. Here is the required code.
Visual Basic
'code in frmProviderFactories.vb Private Sub DataGridView1_RowHeaderMouseDoubleClick( _ ByVal sender As System.Object, _ ByVal e As System.Windows.Forms.DataGridViewCellMouseEventArgs) _ Handles DataGridView1.RowHeaderMouseDoubleClick Dim providerRow As DataRow = providersList.DefaultView(e.RowIndex).Row Dim factory As DbProviderFactory = _ DbProviderFactories.GetFactory(providerRow) 'get SQL Server instances Dim sources As DataTable = _ factory.CreateDataSourceEnumerator().GetDataSources() Dim f As frmSources = New frmSources() f.DataSources = sources If f.ShowDialog() <> DialogResult.OK Then Return End If 'get selected dataRow Dim selectedSource As DataRow = f.SelectedSource End Sub 'code in frmProviderFactories.vb Public Class frmSources Public Property DataSources() As DataTable Get Return DataGridView1.DataSource End Get Set(ByVal Value As DataTable) DataGridView1.DataSource = Value End Set End Property Public Property SelectedSource() As DataRow Get Return _SelectedSource End Get Set(ByVal value As DataRow) _SelectedSource = value End Set End Property Private _SelectedSource As DataRow = nothing Private Sub DataGridView1_MouseDoubleClick(ByVal sender As System.Object, _ ByVal e As System.Windows.Forms.MouseEventArgs) _ Handles DataGridView1.MouseDoubleClick SelectedSource = _ DataGridView1.DataSource.DefaultView(_ DataGridView1.CurrentCell.RowIndex).Row DialogResult = DialogResult.OK End Sub End Class
C#
//code in frmProviderFactories.cs private void dataGridView1_RowHeaderMouseDoubleClick( object sender, DataGridViewCellMouseEventArgs e) { DataRow providerRow = providersList.DefaultView[e.RowIndex].Row; DbProviderFactory factory = DbProviderFactories.GetFactory(providerRow); //get SQL Server instances DataTable sources = factory.CreateDataSourceEnumerator().GetDataSources(); frmSources f = new frmSources(); f.DataSources = sources; if (f.ShowDialog() != DialogResult.OK) { return; } //get selected dataRow DataRow selectedSource = f.SelectedSource; } //code in frmSources.cs namespace Chapter2 { public partial class frmSources : Form { public frmSources() { InitializeComponent(); } public DataTable DataSources { get { return dataGridView1.DataSource as DataTable; } set { dataGridView1.DataSource = value; } } public DataRow SelectedSource { get { return selectedSource; } set { selectedSource = value; } } DataRow selectedSource; private void dataGridView1_MouseDoubleClick( object sender, MouseEventArgs e) { SelectedSource = ((DataTable)dataGridView1.DataSource) .DefaultView[dataGridView1.CurrentCell.RowIndex].Row; DialogResult = DialogResult.OK; } } }
Using DbException to Catch Provider Exceptions
All provider-specific exceptions inherit from a common base class called DbException. When you are working with a provider-neutral coding model, your try catch block can simply catch DbException generically instead of trying to catch each provider-specific exception.
