Linux Application Development (2nd Edition) - page 17

 

Summary

This chapter provided a detailed overview of ADO.NET's disconnected classes. When you work with disconnected data, a DataTable object is almost always a requirement.

The DataTable object contains DataColumn objects, which define the schema, and DataRow objects, which contain the data. DataRow objects have RowState and DataRowVersion properties.

You use the RowState property to indicate whether the DataRow should be inserted, updated, or deleted from the data store if the data is ever persisted to a database.

The DataRow object can contain up to three copies of its data, based on the DataRowVersion. This feature allows the data to be rolled back to its original state, and you can use it when you write code to handle conflict resolution.

The DataSet object is an in-memory, relational data representation. The DataSet object contains a collection of DataTable objects and a collection of DataRelation objects.

DataSet and DataTable objects can be serialized and deserialized to and from a binary or XML file or stream. Data from other DataSet, DataTable, and DataRow objects can be merged into a DataSet object.

You can use the TableDataReader to provide stable, forward-only, read-only looping over a DataTable. You can also use it to populate many Windows and Web controls.

 
 

Chapter 2: Overview of ADO.NET Connected Classes

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.

image from book
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.

image from book

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()
 

image from book

image from book

C#

DbConnection connection = new SqlConnection();
connection.ConnectionString =
   "Server=.;Database=pubs;Trusted_Connection=true";
connection.Open();
   //Do lots of cool work here
connection.Close();
 

image from book

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.

Driver={Microsoft Access Driver (*.mdb)};
   DBQ=C:\\program files\\myApp\\northwind.mdb
 

The following connection string uses the settings that have been configured as a data source name (DSN) on the current machine.

DSN=My Application DataSource
 

The following is a connection to an Oracle database on the ORACLE8i7 servers. The name and password are passed in as well.

Driver={Microsoft ODBC for Oracle};
   Server=ORACLE8i7;
   UID=john;
   PWD=s3$W%1Xz
 

The following connection string uses the Excel driver to open the MyBook.xls file.

Driver={Microsoft Excel Driver (*.xls)};
   DBQ=C:\\Samples\\MyBook.xls
 

The following connection string uses the SQL Server driver to open the northwind database on MyServer using the passed-in user name and password.

DRIVER={SQL Server};
   SERVER=MyServer;
   UID=AppUserAccount;
   PWD=Zx%7$ha;
   DATABASE=northwind;
 

This connection string uses the SQL Server driver to open the northwind database on MyServer using SQL Server's trusted security.

DRIVER={SQL Server};
   SERVER=MyServer;
   Trusted_Connection=yes
   DATABASE=northwind;

 

OLEDB Connection String Configuration

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.

AttachDbFilename, extended properties, initial filename

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.

Persist Security Info=False;
   Integrated Security=SSPI;
   database=northwind;
   server=localhost;
   Connect Timeout=30
 

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.

image from book

Visual Basic

Dim path As string = AppDomain.CurrentDomain.GetData("DataDirectory")
If path = string.Empty Then
      path = AppDomain.CurrentDomain.BaseDirectory
End If
Return path
 

image from book

image from book

C#

string path = (string)AppDomain.CurrentDomain.GetData("DataDirectory");
if (path==string.Empty)
{
      path = AppDomain.CurrentDomain.BaseDirectory;
}
return path;
 

image from book

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:

image from book

XML Application Configuration File

<connectionStrings>
   <clear />
   <add name="PubsData"
      providerName="System.Data.SqlClient"
      connectionString=
      "Data Source=.\SQLEXPRESS;
         AttachDbFilename=|DataDirectory|PUBS.MDF;
         Integrated Security=True;
         User Instance=True"/>
</connectionStrings>
 

image from book

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.

image from book

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
 

image from book

image from book

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;
 

image from book

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.

image from book
Figure 2-2: The GetSchema method provides metadata information.

image from book

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
 

image from book

image from book

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;
 

image from book

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:

image from book

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
 

image from book

image from book

C#

private void dataGridView1_SelectionChanged(object sender, EventArgs e)
{
   DataTable schema = (DataTable)dataGridView1.DataSource;
   DataRow currentRow = schema.Rows[dataGridView1.CurrentCell.RowIndex];
   string collection = (string)currentRow["CollectionName"];
   //Get the settings from the configuration file
   ConnectionStringSettings pubs =
      ConfigurationManager.ConnectionStrings["PubsData"];
   DbConnection connection = new SqlConnection(pubs.ConnectionString);
   connection.Open();
   DataTable schema2 = connection.GetSchema(collection);
   connection.Close();
   dataGridView2.DataSource = schema2;
}
 

image from book

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."

image from book
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.

image from book

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"
 

image from book

image from book

C#

ConnectionStringSettings pubs =
   ConfigurationManager.ConnectionStrings["PubsData"];
DbConnection connection =
   new SqlConnection(pubs.ConnectionString);
DbCommand cmd = connection.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "uspGetCustomerById";
 

image from book

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.

image from book

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)
 

image from book

image from book

C#

ConnectionStringSettings pubs =
   ConfigurationManager.ConnectionStrings["PubsData"];
DbConnection connection = new SqlConnection(pubs.ConnectionString);
DbCommand cmd = connection.CreateCommand();
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "uspGetCustomerById";
DbParameter parm = cmd.CreateParameter();
parm.ParameterName = "@Id";
parm.Value = "AROUT";
cmd.Parameters.Add(parm);
 

image from book

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.

image from book

Visual Basic

Dim id as String = cmd.Parameters("@Id").Value
 

image from book

image from book

C#

string id = (string)((DbParameter)cmd.Parameters["@Id"]).Value;
 

image from book

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.

image from book

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()
 

image from book

image from book

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();
 

image from book

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.

image from book

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())
 

image from book

image from book

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();
 

image from book

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).

image from book

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()
 

image from book

image from book

C#

ConnectionStringSettings pubs =
   ConfigurationManager.ConnectionStrings["PubsData"];
DbConnection connection = new SqlConnection(pubs.ConnectionString);
DbCommand cmd = connection.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT stor_id, ord_num FROM Sales";
connection.Open();
DbDataReader rdr = cmd.ExecuteReader();

while (rdr.Read())
{
   MessageBox.Show((string)rdr["stor_id"] + ": " + (string)rdr["ord_num"]);
}
connection.Close();
 

image from book

DbDataReader Object

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.

image from book

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()
 

image from book

image from book

C#

ConnectionStringSettings pubs =
   ConfigurationManager.ConnectionStrings["PubsData"];
DbConnection connection = new SqlConnection(pubs.ConnectionString);
DbCommand cmd = connection.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT pub_id, pub_name FROM Publishers";
connection.Open();
DbDataReader rdr = cmd.ExecuteReader();
DataTable publishers = new DataTable();
publishers.Load(rdr, LoadOption.Upsert);
connection.Close();
 

image from book

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.

image from book

XML Application Configuration File

<connectionStrings>
   <clear />
   <add name="PubsData"
      providerName="System.Data.SqlClient"
      connectionString=
      "Data Source=.\SQLEXPRESS;

         AttachDbFilename=|DataDirectory|PUBS.MDF;
         Integrated Security=True;
         User Instance=True"/>
   <add name="PubsDataMars"
      providerName="System.Data.SqlClient"
      connectionString=
      "Data Source=.\SQLEXPRESS;
         AttachDbFilename=|DataDirectory|PUBS.MDF;
         Integrated Security=True;
         User Instance=True;
         MultipleActiveResultSets=True"/>
</connectionStrings>
 

image from book

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.

image from book

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()
 

image from book

image from book

C#

ConnectionStringSettings pubs =
   ConfigurationManager.ConnectionStrings["PubsDataMars"];
DbConnection connection = new SqlConnection(pubs.ConnectionString);
DbCommand cmd = connection.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT stor_id, stor_name FROM Stores";
connection.Open();
DbDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
   DbCommand salesCmd = connection.CreateCommand();
   salesCmd.CommandType = CommandType.Text;
   salesCmd.CommandText =
      "SELECT SUM(qty) FROM sales WHERE (stor_id = @storeId)";
   DbParameter parm = salesCmd.CreateParameter();
   parm.ParameterName = "@storeId";
   parm.Value = (string)rdr["stor_id"];
   salesCmd.Parameters.Add(parm);
   object qtySales = salesCmd.ExecuteScalar();
   MessageBox.Show((string)rdr["stor_name"] + ": "
      + qtySales.ToString());
}
connection.Close();
 

image from book

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.

image from book
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.

image from book

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
 

image from book

image from book

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");
}
 

image from book

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.

image from book
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.

image from book

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")
 

image from book

image from book

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");
 

image from book

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.

image from book
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.

image from book

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")
 

image from book

image from book

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");
 

image from book

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.

image from book

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
 

image from book

image from book

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());
}
 

image from book

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:

image from book

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
 

image from book

image from book

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 };
 

image from book

image from book

XML App.config File

<configuration>
    <appSettings>
        <add key="provider" value="SqlClient" />
    </appSettings>
</configuration>
 

image from book

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.

image from book
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.

image from book

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
 

image from book

image from book

C#

//Get the singleton instance
DbProviderFactory factory = SqlClientFactory.Instance;

public DbConnection GetProviderConnection()
{
   DbConnection connection = factory.CreateConnection();
   connection.ConnectionString = "Data Source=.\SQLEXPRESS;"
      + "AttachDbFilename=|DataDirectory|PUBS.MDF;"
      + "Integrated Security=True;User Instance=True";
   return connection;
}
 

image from book

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.

image from book

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
 

image from book

image from book

C#

private DataTable GetData(string commandText, CommandType commandType)
{
   //get SqlDbCommand
   DbCommand command = factory.CreateCommand();
   command.Connection = GetProviderConnection();
   if (command.Connection == null) return null;
   command.CommandText = commandText;
   command.CommandType = commandType;
   command.Connection.Open();
   DataTable dataTable = new DataTable();
   //Get SqlDataReader and populate data table
   dataTable.Load(command.ExecuteReader());
   command.Connection.Close();
   return dataTable;
}
 

image from book

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.

image from book

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
 

image from book

image from book

C#

DataTable providersList = null;
private void button1_Click(object sender, EventArgs e)
{
   providersList = DbProviderFactories.GetFactoryClasses();
   dataGridView1.DataSource = providersList;
}
 

image from book

When this simple application is run and the button is clicked, the screen shown in Figure 2-8 is displayed.

image from book
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:

image from book

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>
 

image from book

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.

image from book

App.config File

<configuration>
  <system.data>
    <DbProviderFactories>
        <remove invariant="System.Data.Odbc" />
    </DbProviderFactories>
  </system.data>
</configuration>
 

image from book

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.

image from book

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>
 

image from book

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.

image from book

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
 

image from book

image from book

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;
      }
   }
}
 

image from book

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.