'Visual Basic Dim da As SqlDataAdapter = New SqlDataAdapter("SELECT * FROM Customers; SELECT * FROM Orders;", myConnection)
Dim ds As DataSet = New DataSet()
da.Fill(ds, "Customers")

//C#
SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM Customers; SELECT * FROM Orders;", myConnection);
DataSet ds = new DataSet();
da.Fill(ds, "Customers");

The data from the Customers table is placed in a DataTable named "Customers". The data from the Orders table is placed in a DataTable named "Customers1".

You can easily modify the TableName property of the "Customers1" table to be "Orders" after the DataSet has been filled. However, subsequent fills would result in the "Customers" table being re-filled, but the "Orders" table being ignored and another "Customers1" table being created. To remedy this situation, create a DataTableMapping that maps "Customers1" to "Orders" and additional table mappings for other subsequent tables. For example:

'Visual Basic Dim da As SqlDataAdapter = New SqlDataAdapter("SELECT * FROM Customers; SELECT * FROM

 Orders;", myConnection)
da.TableMappings.Add("Customers1", "Orders")
Dim ds As DataSet = New DataSet()
da.Fill(ds, "Customers")

//C#
SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM Customers; SELECT * FROM Orders;", myConnection);
da.TableMappings.Add("Customers1", "Orders");
DataSet ds = new DataSet();
da.Fill(ds, "Customers");

Using the DataReader

The following are some tips for best performance using a DataReader, as well as, answers to common questions regarding the use of the DataReader.

• The DataReader must be closed before accessing any output parameters for the associated Command.
• Always close the DataReader when you are finished reading the data. If the Connection you are using is only used to return the DataReader, close it immediately after closing the DataReader.

  An alternative to explicitly closing the Connection is to pass CommandBehavior.CloseConnection to the ExecuteReader method to ensure that the associated connection is closed when the DataReader is closed. This is especially useful if you are returning a DataReader from a method and do not have control over the closing of the DataReader or associated connection.

• The DataReader cannot be remoted between tiers. The DataReader is designed for connected data access.
• When accessing column data use the typed accessors like GetString, GetInt32, and so on. This saves you the processing required to cast the Object returned from GetValue as a particular type.
• Only one DataReader can be open at a time, off of a single connection. In ADO, if you opened a single connection and requested two recordsets that used a forward-only, read-only cursor, ADO implicitly opens a second, unpooled connection to the data store for the life of that cursor, and then implicitly closes it. With ADO.NET, little is done for you "under-the-covers". If you want two DataReaders open at the same time, off the same data store, you have to explicitly create two connections, one for each DataReader. This is one way that ADO.NET gives you more control over the use of pooled connections.
• By default, the DataReader loads an entire row into memory with each Read. This allows for random access of columns within the current row. If this random access is not necessary, for increased performance, pass CommandBehavior.SequentialAccess to the call to ExecuteReader. This changes the default behavior of the DataReader to only load data into memory when it is requested. Note that, CommandBehavior.SequentialAccess requires you to access returned columns in order. That is, once you have read past a returned column, you can no longer read its value.
• If you are finished reading the data from a DataReader, but still have a large number of unread results pending, call Cancel on the Command prior to calling Close on the DataReader. Calling Close on the DataReader causes it to retrieve pending results and empty the stream prior to closing the cursor. Calling Cancel on the Command discards results on the server so that the DataReader does not have to read though them when it is closed. If you are returning output parameters from your Command, calling Cancel discards them as well. If you need to read any output parameters, do not call Cancel on the Command; just call Close on the DataReader.

Binary Large Objects (BLOBs)

When using the DataReader to retrieve a Binary Large Object (BLOB), you should pass CommandBehavior.SequentialAccess to the ExecuteReader method call. Because the default behavior of the DataReader is to load an entire row into memory with each Read, and because BLOB values can be very large, the result can be large amounts of memory being used up for a single BLOB. SequentialAccess sets the behavior of the DataReader to only load the data requested. You can then control how much data is loaded at a time using GetBytes or GetChars.

Remember that, when using SequentialAccess, you cannot access the different fields returned by the DataReader out of order. That is, if your query returns three columns, the third of which is a BLOB, and you want to access data in the first two columns, you must access the first column value, then the second column value before accessing the BLOB data. This is because the data is now returned in sequence and is not available once the DataReader has read past it.

Using Commands

ADO.NET provides several different methods for command execution, as well as different options for optimizing the execution of a command. The following includes tips on choosing the best command execution and how to improve the performance of an executed command.

Best Practices with OleDbCommand

Command execution between the different .NET Framework data providers is standardized as much as possible. However, there are differences between the data providers. The following are some tips for fine-tuning command execution with the .NET Framework Data Provider for OLE DB.

• Use CommandType.Text with the ODBC CALL syntax to call stored procedures. Using CommandType.StoredProcedure just generates the ODBC CALL syntax under the covers.
• Be sure to set the OleDbParameter type, size (if applicable), as well as precision and scale (if the parameter is of type numeric or decimal). Note that, if you do not supply the parameter information explicitly, the OleDbCommand recreates the OLE DB parameter accessor with every command execution.

Best Practices with SqlCommand

A quick tip for executing stored procedures using the SqlCommand: If you are calling a stored procedure, specify a CommandType of StoredProcedure for the CommandType property of the SqlCommand. This removes the need to parse the command before execution, by explicitly identifying it as a stored procedure.

Use of the Prepare Method

The Command.Prepare method can improve the performance of parameterized commands that are repeated at your data source. Prepare instructs the data source to optimize the specified command for multiple calls. To use Prepare effectively, you need to thoroughly understand how your data source responds to the Prepare call. For some data sources such as SQL Server 2000, commands are implicitly optimized and a call to Prepare is unnecessary. For other data sources such as SQL Server 7.0, Prepare can be effective.

Specify Schema and Metadata Explicitly

Many objects in ADO.NET infer metadata information whenever the user does not specify it. Examples are:

• The DataAdapter.Fill method, which creates a table and columns in a DataSet if none exist
• The CommandBuilder, which generates DataAdapter command properties for single-table SELECT commands.
• CommandBuilder.DeriveParameters, which populates a Command object's Parameters collection.

Each time these features are used, however, there is a performance hit. It is recommended that these features be used primarily for design-time and ad-hoc applications. Whenever possible, specify the schema and metadata explicitly. This includes defining tables and columns in a DataSet, defining the Command properties of a DataAdapter, and defining Parameter information for a Command.

ExecuteScalar and ExecuteNonQuery

If you want to return a single value such as the result of Count(*), Sum(Price), or Avg(Quantity), you can use Command.ExecuteScalar. ExecuteScalar returns the value of the first column of the first row, returning result set as a scalar value. ExecuteScalar both simplifies your code and improves performance by accomplishing in a single step, what would have been a two-step process using a DataReader (that is, ExecuteReader + Get the value).

When using SQL statements that do not return rows, like those that modify data (such as INSERT, UPDATE, or DELETE) or return only output parameters or return values, use ExecuteNonQuery. This removes any unnecessary processing to create an empty DataReader.

For more information, see Executing a Command.

Testing for Null

If a column in a table (in your database) allows nulls, you cannot test for a parameter value of "equal to" null. Instead, you need to write a WHERE clause to test whether both the column is null and the parameter is null. The following SQL statement returns rows where the LastName column equals the value assigned to the @LastName parameter, or whether both the LastName column and the @LastName parameter are null.

SELECT * FROM Customers
WHERE ((LastName = @LastName) OR (LastName IS NULL AND @LastName IS NULL))

Passing Null as a Parameter Value

When sending a null value as a Parameter value in a command to the database, you cannot use null (Nothing in Visual Basic® .NET). Instead you need to use DBNull.Value. For example:

'Visual Basic Dim param As SqlParameter = New SqlParameter("@Name", SqlDbType.NVarChar, 20)
param.Value = DBNull.Value

//C#
SqlParameter param = new SqlParameter("@Name", SqlDbType.NVarChar, 20);
param.Value = DBNull.Value;

Performing Transactions

The transaction model has changed for ADO.NET. In ADO, when StartTransaction was called, any update following the call is considered part of the transaction. However, in ADO.NET, when Connection.BeginTransaction is called, a Transaction object is returned that needs to be associated with the Transaction property of a Command. This design enables you to perform multiple root transactions off of a single connection. If the Command.Transaction property is not set to a Transaction that has been started for the associated Connection, the Command fails and an exception is thrown.

Upcoming releases of the .NET Framework will enable you to manually enlist in an existing distributed transaction. This is ideal for an object pooling scenario where a connection is opened once for a pooled object, but the object is involved in multiple separate transactions. This capability is not available in the .NET Framework 1.0 release.

Using Connections

High performance applications keep connections to the data source in use for a minimal amount of time, as well as take advantage of performance enhancing technology such as connection pooling. The following topics provide you with tips to help you achieve greater performance when using ADO.NET to connect to your data source.

Connection Pooling

The SQL Server, OLE DB, and .NET Framework Data Provider for ODBC pool connections implicitly. You can control connection-pooling behavior by specifying different attribute values in the connection string. For details on how to control connection pooling behavior, see Connection Pooling for the SQL Server .NET Data Provider [ http://msdn.microsoft.com/en-us/library/8xx3tyca.aspx ] and Connection Pooling for the OLE DB .NET Data Provider [ http://msdn.microsoft.com/en-us/library/aa719769.aspx ] .

Optimizing Connections with the DataAdapter

The Fill and Update methods, of the DataAdapter, automatically open the connection specified for the related command property if it is closed. If the Fill or Update method open the connection, Fill or Update will close it when the operation is complete. For best performance, keep connections to the database open only when required. Also, reduce the number of times you open and close a connection for multiple operations.

It is recommended that, if you are only performing a single Fill or Update method call, that you allow the Fill or Update method to open and close the connection implicitly. If you are making numerous calls to Fill and/or Update, it is recommended that you explicitly open the connection, make your calls to Fill and/or Update, and then explicitly close the connection.

Additionally, when performing transactions, explicitly open the connection before beginning the transaction and close the connection after you commit. For example:

'Visual Basic Public Sub RunSqlTransaction(da As SqlDataAdapter, myConnection As SqlConnection, ds As DataSet)
  myConnection.Open()
  Dim myTrans As SqlTransaction = myConnection.BeginTransaction()
  myCommand.Transaction = myTrans

  Try
    da.Update(ds)
    myTrans.Commit()
    Console.WriteLine("Update successful.")
  Catch e As Exception
    Try
      myTrans.Rollback()
    Catch ex As SqlException
      If Not myTrans.Connection Is Nothing Then
        Console.WriteLine("An exception of type " & ex.GetType().ToString() & _
                          " was encountered while attempting to roll back the transaction.")
      End If
    End Try

    Console.WriteLine("An exception of type " & e.GetType().ToString() & " was encountered.")
    Console.WriteLine("Update failed.")
  End Try
  myConnection.Close()
End Sub

//C#
public void RunSqlTransaction(SqlDataAdapter da, SqlConnection myConnection, DataSet ds)
{
  myConnection.Open();
  SqlTransaction myTrans = myConnection.BeginTransaction();
  myCommand.Transaction = myTrans;

  try
  {
    da.Update(ds);
    myCommand.Transaction.Commit();
    Console.WriteLine("Update successful.");
  }
  catch(Exception e)
  {
    try
    {
      myTrans.Rollback();
    }
    catch (SqlException ex)
    {
      if (myTrans.Connection != null)
      {
        Console.WriteLine("An exception of type " + ex.GetType() +
                          " was encountered while attempting to roll back the transaction.");
      }
    }

    Console.WriteLine(e.ToString());
    Console.WriteLine("Update failed.");
  }
  myConnection.Close();
}

Always Close Connections and DataReaders

Always explicitly close your Connection or DataReader objects when you are finished using them. While garbage collection eventually cleans up objects and therefore releases connections and other managed resources, garbage collection only occurs when it is needed. Therefore, it is still your responsibility to make sure any expensive resources are explicitly released. Also, Connections that are not explicitly closed might not be returned to the pool. For example, a connection that has gone out of scope but that has not been explicitly closed will only be returned to the connection pool if the maximum pool size has been reached and the connection is still valid.

Note   Do not call Close or Dispose on a Connection, a DataReader, or any other managed object in the Finalize method of your class. In a finalizer, only release unmanaged resources that your class owns directly. If your class does not own any unmanaged resources, do not include a Finalize method in your class definition.

Use the "Using" Statement in C#

For C# programmers, a convenient way to ensure that you always close your Connection and DataReader objects is to use the using statement. The using statement automatically calls Dispose on the object being "used" when leaving the scope of the using statement. For example:

//C# string connString = "Data Source=localhost;Integrated Security=SSPI;Initial Catalog=Northwind;";

using (SqlConnection conn = new SqlConnection(connString))
{
  SqlCommand cmd = conn.CreateCommand();
  cmd.CommandText = "SELECT CustomerId, CompanyName FROM Customers";
  
  conn.Open();

  using (SqlDataReader dr = cmd.ExecuteReader())
  {
    while (dr.Read())
      Console.WriteLine("{0}\t{1}", dr.GetString(0), dr.GetString(1));
  }
}

The using statement is not available for Microsoft® Visual Basic® .NET.

Avoid Accessing the OleDbConnection.State Property

If the connection has been opened, OleDbConnection.State property makes the native OLE DB call IDBProperties.GetProperties to the DATASOURCEINFO property set for the DBPROP_CONNECTIONSTATUS property, which may result in a round trip to the data source. In other words, checking the State property can be expensive. So only check the State property when required. If you need to check this property often, your application may perform better if you listen for the StateChange event for your OleDbConnection. For details on the StateChange event, see Working with Connection Events [ http://msdn.microsoft.com/en-us/library/a0hee08w.aspx ] .

Integration with XML

ADO.NET provides extensive XML integration in the DataSet, and also exposes some of the XML functionality provided by SQL Server 2000 and later. You can also make use of SQLXML 3.0 for extensive access to the XML functionality in SQL Server 2000 and later. Here are tips and information when using XML and ADO.NET.

The DataSet and XML

The DataSet is tightly integrated with XML providing you with the ability to:

• Load the schema or relational structure of a DataSet from XSD Schema.
• Load the contents of a DataSet from XML.
• Infer the schema of a DataSet from the contents of an XML document when no schema is supplied.
• Write the schema of a DataSet as XSD Schema.
• Write the contents of a DataSet as XML.
• Have synchronous access to both the relational representation of your data using the DataSet, as well as the hierarchical representation of your data using the XmlDataDocument.

Note   You can use this synchronization to apply XML functionality such as XPath queries and XSLT transformations to the data in your DataSet, or to provide a relational view of all, or a subset of the data in an XML document while preserving the fidelity of the original XML.

Schema Inference

When loading a DataSet from an XML file, you can load the schema of the DataSet from XSD Schema, or you can predefine the tables and columns before loading the data. If no XSD Schema is available and you do not know which tables and columns to define for the contents of an XML file, you can infer the schema based on the structure of the XML document.

Schema inference is useful as a migration tool, but should be limited to design-time applications only as the inference process has the following limitations.

• Inferring schema introduces additional processing that hinders the performance of an application.
• All inferred columns are of type string.
• The inference process is not deterministic. That is, it is based on the contents of the XML file, not the intended schema. As a result, you can have two XML files, with the same intended schema, that result in two entirely different inferred schemas because their contents differ.

SQL Server FOR XML Queries

If you are returning the results of a SQL Server 2000 FOR XML query, you can have the .NET Framework Data Provider for SQL Server directly create an XmlReader using the SqlCommand.ExecuteXmlReader method.

SQLXML Managed Classes

There are classes available that expose the functionality of XML for SQL Server 2000 in the .NET Framework. These classes are found in the Microsoft.Data.SqlXml namespace and add the ability to execute XPath queries and XML Template files, as well as apply XSLT transformation to data.

SQLXML Managed Classes are included in the release of XML for Microsoft SQL Server 2000 (SQLXML 2.0), which is available at XML for Microsoft SQL Server 2000 Web Release 2 (SQLXML 2.0) [ http://www.microsoft.com/downloads/release.asp.aspx?ReleaseID=33055 ] .

More Useful Tips

Here are some general tips for writing ADO.NET code.

Avoiding Auto-Increment Value Conflicts

Like most data sources, the DataSet enables you to identify columns that automatically increment their value when new rows are added. When using auto-increment columns in a DataSet, with auto-increment columns from a data source, avoid conflicts between the local numbering of rows added to the DataSet and rows added to the data source.

For example, consider a table with an auto-incrementing primary key column of CustomerID. Two new rows of customer information are added to the table and receive auto-incremented CustomerID values of 1 and 2. Then, only the second customer row is passed to the Update method of the DataAdapter, the newly added row receives an auto-incremented CustomerID value of 1 at the data source, which does not match the value 2, in the DataSet. When the DataAdapter fills the second row in the table with the returned value, a constraint violation occurs because the first customer row already has a CustomerID of 1.

To avoid this behavior, it is recommended that, when working with auto-incrementing columns at a data source and auto-incrementing columns in a DataSet, you create the column in the DataSet with an AutoIncrementStep of -1 and an AutoIncrementSeed of 0, as well as ensuring that your data source generates auto-incrementing identity values starting from 1 and incrementing with a positive step value. As a result, the DataSet generates negative numbers for auto-incremented values that do not conflict with the positive auto-increment values generated by the data source. Another option is to use columns of type Guid instead of auto-incrementing columns. The algorithm that generates Guid values should never generate the same Guid in the DataSet as is generated by the data source.

If your auto-incremented column is used simply as a unique value, and does not have any meaning, consider using Guids instead of auto-incrementing columns. They are unique and avoid the extra work necessary to work with auto-incremented columns.

Check for Optimistic Concurrency Violations

Because the DataSet is, by design, disconnected from the data source, you need to ensure that your application avoids conflicts when multiple clients update data at the data source, according to the optimistic concurrency model.

There are several techniques when testing for an optimistic concurrency violation. One involves including a timestamp column in the table. Another technique is to verify that all the original column values in a row still match those found in the database by testing using a WHERE clause in your SQL statement.

Multithreaded Programming

ADO.NET is optimized for performance, throughput, and scalability. As a result, the ADO.NET objects do not lock resources and must only be used on a single thread. The one exception is the DataSet, which is thread-safe for multiple readers. However, you need to lock the DataSet during writes.

Access ADO using COM Interop Only When Required

ADO.NET is designed to be the best solution for a large number of applications. However, some applications require capabilities that are only available using ADO objects, for example ADO Multidimensional (ADOMD). In these situations, the application can access ADO using COM Interop. Note that using COM Interop to access data with ADO incurs a performance hit. When designing an application, first determine if ADO.NET meets your design needs before implementing a design that accesses ADO using COM Interop.