Section A.2. Data Provider Enhancements


A.2. Data Provider Enhancements

ADO.NET 2.0 introduces new features and enhancements to .NET Framework data providers, which are used to connect to data sources, execute commands, retrieve data, and update data. The following subsections describe the key changes.

A.2.1. Asynchronous Processing

ADO.NET 2.0 supports asynchronous programming for data retrieval. This lets you delegate long-running data-processing tasks to a background thread while allowing the user interface to remain responsive. Standard asynchronous processing techniques include callbacks, wait handles, and polling . The SqlCommand class has six methods that support asynchronous processing, described in Table A-4.

Table A-4. SqlCommand class methods for asynchronous processing (continued)

Asynchronous method

Description

BeginExecuteNonQuery

Starts the asynchronous execution of the T-SQL statement or stored procedure for the SqlCommand object. The method returns an IAsyncResult object that can be used to poll for or wait for results, or to invoke the EndExecuteNonQuery( ) method.

Each call to a BeginExecuteNonQuery( ) method must be paired with the EndExecuteNonQuery( ) method that completes the operation.

EndExecuteNonQuery

Completes the asynchronous execution of the T-SQL statement or stored procedure started using the BeginExecuteNonQuery( ) method of the SqlCommand object. The command returns the number of rows affected by the command.

BeginExecuteReader

Starts the asynchronous execution of the T-SQL statement or stored procedure for the SqlCommand object. The method returns an IAsyncResult object that can be used to poll for or wait for results, or to invoke the EndExecuteReader( ) method.

Each call to a BeginExecuteReader( ) method must be paired with the EndExecuteReader( ) method that completes the operation.

EndExecuteReader

Completes the asynchronous execution of the T-SQL statement or stored procedure started using the BeginExecuteReader( ) method of the SqlCommand object. The command returns a SqlDataReader object containing one or more result sets.

BeginExecuteXmlReader

Starts the asynchronous execution of the T-SQL statement or stored procedure for the SqlCommand object. The method returns an IAsyncResult object that can be used to poll for or wait for results, or to invoke the EndExecuteXmlReader( ) method.

Each call to a BeginExecuteXmlReader( ) method must be paired with the EndExecuteXmlReader( ) method that completes the operation.

EndExecuteXmlReader

Completes the asynchronous execution of the T-SQL statement or stored procedure started using the BeginExecuteXmlReader( ) method of the SqlCommand object. The command returns an XmlReader object.


The asynchronous command Begin/End pairs for the SqlCommand object work similarly to each other. The examples in this section that use one of the pairs can be transferred easily to one of the other pairs.

You must add the Asynchronous Processing=true attribute to the SQL Server connection string to use any of the asynchronous methods.

The IAsyncResult interface stores state information about the asynchronous operation and provides a synchronization object that lets threads get signaled when the operation completes. Table A-5 lists the public properties exposed by the IAsyncResult interface.

Table A-5. Public properties of IAsyncResult interface

Property

Description

AsyncState

Returns a user-defined object that contains information about or qualifies an asynchronous operation

AsyncWaitHandle

Returns a WaitHandle object used to wait for an asynchronous operation to complete

CompletedSynchronously

A bool indicating whether the asynchronous operation completed synchronously

IsCompleted

A bool indicating whether the asynchronous operation has completed


The following Windows application uses an asynchronous data reader to get a result set containing all rows in the Person.Contact table in the AdventureWorks database. A WAITFOR T-SQL statement is used to delay the processing of the SELECT statement for five seconds to demonstrate the background processing of the query. After five seconds, the program executes the T-SQL statement to retrieve all rows into a DataReader object, and then calls the HandleCallback( ) callback to display the number of rows in a message box.

Create a new .NET Windows project. Replace the code in Form1.cs with the following code. There are no other user interface elements to this sample.

 using System; using System.Windows.Forms; using System.Data; using System.Data.SqlClient; namespace ADONET20Win {     public partial class Form1 : Form     {         public Form1(  )         {             InitializeComponent(  );         }         // use a delegate to display the results from the         // async read since it is likely on a different thread and         // cannot interact with the form         private delegate void DisplayResultsDelegate(string results);         // delegate to display results         private void DisplayResults(string results)         {             MessageBox.Show(results);         }         private void Form1_Load(object sender, EventArgs e)         {             SqlConnection conn = new SqlConnection(  );             conn.ConnectionString = "Data Source=localhost;" +                 "Integrated Security=SSPI;" +                 "Initial Catalog=AdventureWorks;" +                 "Asynchronous Processing=true";             string cmdText = "WAITFOR DELAY '00:00:05';" +                 "SELECT * FROM Person.Contact;";             SqlCommand cmd = new SqlCommand(cmdText, conn);             conn.Open(  );             // start the async operation. The HandleCallback(  ) method             // is called when the operation completes in 5 seconds.             cmd.BeginExecuteReader(                 new AsyncCallback(HandleCallback), cmd);         }         private void HandleCallback(IAsyncResult asyncResult)         {             // get the original object             SqlCommand cmd = (SqlCommand)asyncResult.AsyncState;             int rowCount = 0;             // get the data reader returned from the async call             using (SqlDataReader dr = cmd.EndExecuteReader(asyncResult))             {                 // iterate over the reader                 while (dr.Read(  ))                 {                     // do some work with the reader                     rowCount++;                 }             }             cmd.Connection.Close(  );             string results = "Rows in Person.Contact: " + rowCount;             // output the number of rows using the delegate described             // earlier in this sample             DisplayResultsDelegate del =                 new DisplayResultsDelegate(DisplayResults);             this.Invoke(del, results);         }     } } 

The next example is a Windows application that polls the IAsyncResult interface using its IsComplete property to determine when the operation is complete. The example is similar to the previous example except that the user can click a button to check the status of the asynchronous operation. The status is displayed in a message box and is either false if the query is still running or true if it has completed. After completion, the number of rows in the data reader returned from the query is also displayed.

Create a new .NET Windows project. Open Form1 in the designer and add two buttons to the form: one with Name = getDataButton and Caption = Get Data, and the other with Name = checkStatusButton and Caption = Check Status. Replace the code in Form1.cs with the following code. Run the application and click the Get Data button. Click the Check Status button periodically to check whether the query has completed. The number of rows is returned when the query has completed.

 using System; using System.Windows.Forms; using System.Data; using System.Data.SqlClient; namespace ADONET20Win {     public partial class Form1 : Form     {         IAsyncResult asyncResult;         SqlCommand cmd;         public Form1(  )         {             InitializeComponent(  );         }         private void getDataButton_Click(object sender, EventArgs e)         {             SqlConnection conn = new SqlConnection(  );             conn.ConnectionString = "Data Source=localhost;" +                 "Integrated Security=SSPI;" +                 "Initial Catalog=AdventureWorks;" +                 "Asynchronous Processing=true";             string cmdText = "WAITFOR DELAY '00:00:10';" +                 "SELECT * FROM Person.Contact;";             cmd = new SqlCommand(cmdText, conn);             conn.Open(  );             // start the async operation. The HandleCallback method             // will be called when it completes.             asyncResult = cmd.BeginExecuteReader(  );         }         private void checkStatusButton_Click(object sender, EventArgs e)         {             string status = "Query complete: " + asyncResult.IsCompleted;             if (asyncResult.IsCompleted)             {                 int rowCount = 0;                 // get the data reader returned from the async call when                 // the operation is complete                 using (SqlDataReader dr = cmd.EndExecuteReader(asyncResult))                 {                     // iterate over the reader                     while (dr.Read(  ))                     {                         // do some work with the reader                         rowCount++;                     }                 }                 cmd.Connection.Close(  );                 status += Environment.NewLine + "Rows returned: " + rowCount;             }             MessageBox.Show(status);         }     } } 

The callback and polling techniques shown in the preceding examples are useful when you are processing one asynchronous operation at a time. The wait model lets you process multiple simultaneous asynchronous operations. The wait model uses the AsyncWaitHandle property of the IAsyncResult instance returned from the BeginExecuteNonQuery( ), BeginExecuteReader( ), or BeginExecuteXmlReader( ) method of the SqlCommand object.

The WaitAny( ) and WaitAll( ) static methods of the WaitHandle class monitor and wait for the completion of asynchronous operations. The WaitAny( ) method waits for any of the asynchronous operations to complete or time outyou can process the results and continue to wait for the next operation to either complete or time out. The WaitAll( ) method waits for all of the processes in the array of WaitHandle instances to complete or time out before continuing.

The following console application demonstrates using the WaitAny( ) method for asynchronous command processing:

 using System; using System.Data.SqlClient; using System.Threading; class Program {     static void Main(string[] args)     {         string connectionString =             "Data Source=localhost;" +             "Integrated Security=SSPI;" +             "Initial Catalog=AdventureWorks;" +             "Asynchronous Processing=true";         Random rnd = new Random((int)DateTime.Now.Ticks);         // create an array of commands with "n" members         int n = 10;         SqlConnection[] conn = new SqlConnection[n];         SqlCommand[] cmd = new SqlCommand[n];         string[] cmdText = new string[n];         IAsyncResult[] asyncResult = new IAsyncResult[n];         WaitHandle[] wh = new WaitHandle[n];         for (int i = 0; i < n; i++)         {             // each command waits randomly for between 1 and 10 seconds             cmdText[i] = "WAITFOR DELAY '00:00:" +                 rnd.Next(1, 10) + "';";             conn[i] = new SqlConnection(connectionString);             conn[i].Open(  );             cmd[i] = new SqlCommand(cmdText[i], conn[i]);             asyncResult[i] = cmd[i].BeginExecuteNonQuery(  );             wh[i] = asyncResult[i].AsyncWaitHandle;         }         // wait for all processes to complete, outputing completion         for (int i = 0; i < n; i++)         {             int index = WaitHandle.WaitAny(wh);             int result = cmd[index].EndExecuteNonQuery(asyncResult[index]);             Console.WriteLine("Completed command " + index +                 ": " + cmd[index].CommandText);             conn[index].Close(  );         }         Console.WriteLine("Press any key to continue.");         Console.ReadKey(  );     } } 

The preceding example creates an array of 10 WAITFOR T-SQL statements of random duration between 1 and 10 seconds and displays a line to the console as each of them completes. Figure A-5 shows sample output.

Figure A-5. Results for WaitAny( ) method example


The wait all model waits for the completion of all processes. The method returns true if every element in the WaitHandle array receives a signal within the timeout time span (in this example, 20000 milliseconds, or 20 seconds). Otherwise, false is returned.

The following console application demonstrates using the WaitAll( ) method for asynchronous command processing:

 using System; using System.Data.SqlClient; using System.Threading; class Program {     static void Main(string[] args)     {         string connectionString =             "Data Source=localhost;" +             "Integrated Security=SSPI;" +             "Initial Catalog=AdventureWorks;" +             "Asynchronous Processing=true";         Random rnd = new Random((int)DateTime.Now.Ticks);         // create an array of commands with "n" members         int n = 10;         SqlConnection[] conn = new SqlConnection[n];         SqlCommand[] cmd = new SqlCommand[n];         string[] cmdText = new string[n];         IAsyncResult[] asyncResult = new IAsyncResult[n];         WaitHandle[] wh = new WaitHandle[n];         for (int i = 0; i < n; i++)         {             // each command waits for randomly between 1 and 10 seconds             cmdText[i] = "WAITFOR DELAY '00:00:" +                 rnd.Next(1, 10) + "';";             conn[i] = new SqlConnection(connectionString);             conn[i].Open(  );             cmd[i] = new SqlCommand(cmdText[i], conn[i]);             asyncResult[i] = cmd[i].BeginExecuteNonQuery(  );             wh[i] = asyncResult[i].AsyncWaitHandle;         }         // wait for all processes to complete and output results         bool result = WaitHandle.WaitAll(wh, 20000, false);         if (result)         {             for (int i = 0; i < n; i++)             {                 int recAff = cmd[i].EndExecuteNonQuery(asyncResult[i]);                 conn[i].Close(  );             }             Console.WriteLine("Completed all commands successfully.");         }         else             Console.WriteLine("Timeout error.");         Console.WriteLine("Press any key to continue.");         Console.ReadKey(  );     } } 

The preceding example creates an array of 10 WAITFOR T-SQL statements of random duration between 1 and 10 seconds and displays a line to the console indicating when all of them have completed, as shown in Figure A-6.

Figure A-6. Results for WaitAll( ) method example


See MSDN for more information about the WaitAny( ) and WaitAll( ) methods.

A.2.2. Support for SQL Server Notifications

SQL Server 2005 and ADO.NET 2.0 let you ask for a notification if executing the same command to retrieve data would generate a different result set. This happens, for example, if another user has changed the data since the current user fetched it. This capability is built on top of the new queuing functionality in SQL Server 2005. The two classes that support notifications are SqlDependency and SqlNotificationRequest . A discussion and example of each follows.

Both examples use a table called Contact. Create the table and add two records to it with the following query:

 USE ProgrammingSqlServer2005 CREATE TABLE Contact(     ID int NOT NULL,     FirstName varchar(50) NOT NULL,     LastName varchar(50) NOT NULL,   CONSTRAINT [PK_Contact] PRIMARY KEY CLUSTERED   (     [ID] ASC   ) ON [PRIMARY] ) ON [PRIMARY] GO INSERT INTO Contact (ID, FirstName, LastName) VALUES (1, 'John', 'Doe'); INSERT INTO Contact (ID, FirstName, LastName) VALUES (2, 'Jane', 'Smith'); 

SQL Server 2005 databases do not have Service Broker enabled by default, for security reasons. Enable Service Broker for the ProgrammingSqlServer2005 database by executing the following T-SQL statement:

 ALTER DATABASE ProgrammingSqlServer2005 SET ENABLE_BROKER 

You can confirm that Service Broker is now enabled for the database by using the DATABASEPROPERTYEX function, as shown in the following T-SQL statement:

 SELECT DATABASEPROPERTYEX('ProgrammingSqlServer2005', 'IsBrokerEnabled') 

The function returns 0 for false and 1 for true.

The SqlDependency class lets you create an object to detect changes in the query result. In this example, you create a SqlDependency instance. You then register to receive notifications of changes to the result set through the OnChanged event handler. Follow these steps:

  1. Create a SqlConnection object and a SqlCommand object with the query that you want to monitor for changes.

  2. Create a SqlDependency object and bind it to the SqlCommand object.

  3. Subscribe an event handler to the OnChanged event of the SqlDependency object.

  4. Execute the SqlCommand object using any Execute( ) method.

The following example shows how to monitor and handle notifications using the SqlDependency class. For notifications to work, you must specify the database owner as part of the table name and a list of columns in the queryspecifying all columns using an asterisk (*) will not work.

 using System; using System.Data; using System.Data.Common; using System.Data.SqlClient; class Program {     static void Main(string[] args)     {         string connString = "Data Source=localhost;Integrated Security=SSPI;" +             "Initial Catalog=ProgrammingSqlServer2005;";         // create the connection and the command to monitor for changes         SqlConnection conn = new SqlConnection(connString);         SqlCommand cmd = new SqlCommand(             "SELECT ID, FirstName, LastName FROM dbo.Contact", conn);         // create the SqlDependency object and bind it to the command         SqlDependency d = new SqlDependency(cmd);         d.OnChange += new DEFANGED_OnChangeEventHandler(d_OnChange);         SqlDependency.Start(connString);         Console.WriteLine("Notification handler configured.");         // create the DataReader         conn.Open(  );         SqlDataReader dr = cmd.ExecuteReader(  );         while (dr.Read(  ))         {             // process the DataReader row         }         dr.Close(  );         Console.WriteLine(Environment.NewLine + "Press any key to end.");         Console.ReadKey(  );         conn.Close(  );     }     static void d_OnChange(object sender, SqlNotificationEventArgs e)     {         Console.WriteLine(Environment.NewLine + "SqlDependency.OnChange event");         Console.WriteLine("  Source = " + e.Source);         Console.WriteLine("  Type =   " + e.Type);         Console.WriteLine("  Info =   " + e.Info);     } } 

Run the example and, while it is running, add a row to the Contact table. The results are shown in Figure A-7.

Figure A-7. Results for SqlDependency event example


The SqlNotificationRequest class lets you execute a command so that SQL Server generates a notification when query results change. Unlike the SqlDependency class, once the notification is created, you do not have to maintain the SqlNotificationRequest object. You simply query your queue for notifications as you need to. This model is particularly useful in a disconnected environment.

You must first create a queue and a service to receive the notification messages, as shown in the following T-SQL statement:

 USE ProgrammingSqlServer2005 GO CREATE QUEUE ContactQueue CREATE SERVICE ContactNotification   ON QUEUE ContactQueue   ([http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]); CREATE ROUTE ContactQueueRoute   WITH SERVICE_NAME = 'ContactNotification', ADDRESS = 'LOCAL'; 

This T-SQL block does three things:

  • Creates a queue named ContactQueue to hold Service Broker messages.

  • Creates a service named ContactNotification used by Service Broker to deliver messages to the ContactQueue queue in the SQL Server database.

  • Creates a route used by Service Broker to route messages to the correct SQL Server for the service.

After setting up the queue, service, and route, you need to bind a SqlNotificationRequest object to the SqlCommand object containing your query. This means that when a T-SQL statement is executed, SQL Server keeps track of the query and sends a notification to the SQL Server queue specified in the notification request if a change is detected.

To do this, build a console application to create the notification as follows:

 using System; using System.Data; using System.Data.Common; using System.Data.SqlClient; using System.Data.Sql; class Program {     static void Main(string[] args)     {         SqlConnection conn = new SqlConnection("Data Source=localhost;" +             "Integrated Security=SSPI;" +             "Initial Catalog=ProgrammingSqlServer2005;");         SqlCommand cmd = new SqlCommand(             "SELECT ID, FirstName, LastName FROM dbo.Contact", conn);         // create the SqlNotificationRequest and bind to the command         SqlNotificationRequest nr = new SqlNotificationRequest(  );         nr.UserData = Guid.NewGuid().ToString(  );         nr.Options = "Service=ContactNotification  ; " +             "Local Database = ProgrammingSqlServer2005";         nr.Timeout = Int32.MaxValue;         cmd.Notification = nr;         Console.WriteLine("Notification handler configured.");         // create a data reader         conn.Open(  );         SqlDataReader dr = cmd.ExecuteReader(  );         while (dr.Read(  ))         {             // ... do some work with the data reader         }         Console.WriteLine("Press any key to end.");         Console.ReadKey(  );         conn.Close(  );     } } 

When you run the example, SQL Server creates a new query-notification subscription. Any changes to the data that affect the results of the query SELECT ID, FirstName, LastName FROM dbo.Contact produce a notification.

While the example is running, add a record to the contact table using SQL Management Studio. The notifications are delivered to the ContactNotification service. The ContactNotification service uses the queue ContactQueue to store the notifications. You can retrieve those messages by using the following T-SQL statement:

 SELECT * FROM ContactQueue 

As the example shows, you must specify three properties for the SqlNotificationRequest object:


UserData

The application-specific identifier for the notification


Options

The Service Broker service name where the notification messages are posted


Timeout

The length of time, in seconds, that SQL Server waits for a change to occur before timing out

A.2.3. Multiple Active Result Sets

Multiple Active Result Sets (MARS ) allows multiple commands to be executed on a single connection against a SQL Server 2005 database. Each command requires its own SqlCommand object and adds an additional session to the connection. You must enable MARS by setting the MultipleActiveResultSets key in the connection string to true.

The following console application queries AdventureWorks and returns the top 10 sales order headers and the sales order details for each header. A single connection is used with two command objects to create the DataReader objects.

 using System; using System.Data.SqlClient; class Program {     static void Main(string[] args)     {         // open a connection         SqlConnection conn = new SqlConnection(  );         conn.ConnectionString = "Data Source=localhost;" +             "Integrated Security=SSPI;Initial Catalog=AdventureWorks;" +             "MultipleActiveResultSets=true";         conn.Open(  );         // create a DataReader with the top 10 sales header records         SqlCommand cmdHeader = conn.CreateCommand(  );         cmdHeader.CommandText =             "SELECT TOP 10 SalesOrderID, TotalDue FROM Sales.SalesOrderHeader";         using (SqlDataReader drHeader = cmdHeader.ExecuteReader(  ))         {             while (drHeader.Read(  ))             {                 int salesOrderID = (int)drHeader["SalesOrderID"];                 Console.WriteLine("{0}\t{1}",                     salesOrderID, drHeader["TotalDue"]);                 // create a DataReader with detail for the sales order                 SqlCommand cmdDetail = conn.CreateCommand(  );                 cmdDetail.CommandText = "SELECT ProductID, OrderQty FROM " +                     "Sales.SalesOrderDetail WHERE SalesOrder\t{0}\t{1}",                             drDetail["ProductID"], drDetail["OrderQty"]);                     drDetail.Dispose(  );                 }                 Console.WriteLine(  );             }         }         conn.Close(  );         Console.WriteLine("Press any key to continue.");         Console.ReadKey(  );     } } 

Partial results are shown in Figure A-8.

Figure A-8. Partial results for MARS example


A.2.4. Bulk Copy

Bulk copy is a high-performance mechanism for transferring large amounts of data into a database table or view. In ADO.NET 2.0, you can bulk copy data into SQL Server from either a DataTable or DataReader object using the new SqlBulkCopy class in the System.Data.SqlClient namespace. This class supports both single and multiple bulk copy operations within either dedicated (by default) or existing transactions.

Table A-6 describes the key methods and properties of the SqlBulkCopy class.

Table A-6. Key methods and properties of the SqlBulkCopy class

Constructors

Description

SqlBulkCopy(SqlConnection conn)

SqlBulkCopy(string connString)

SqlBulkCopy(string connString,

SqlBulkCopyOptions options)

SqlBulkCopy(string connString,

SqlBulkCopyOptions options,

SqlTransaction, tx)

Creates a new instance of the SqlBulkCopy class, where:


conn

A SqlConnection instance.


connString

A SQL Server connection string.


options

Bitwise flag that specifies options for the SqlBulkCopy( ) method from the SqlBulkCopyOptions enumeration. See MSDN for more information.


tx

An existing transaction (as a SqlTransaction object) in which the bulk copy takes place.

Properties

 

BatchSize

The number of rows in each batch sent to the server. The default is 0, indicating that the rows are written in a single batch.

BulkCopyTimeout

Number of seconds for the bulk copy to complete before it times out.

ColumnMappings

A collection of SqlBulkCopyColumnMapping objects that defines the mapping of columns from the source data object to the destination table.

DestinationTableName

The name of the destination table on the server.

NotifyAfter

The number of rows to process before generating a notification event. The default is 0, indicating that notifications are not sent.

Methods

 

Close( )

Closes the SqlBulkCopy instance.

WriteToServer( )

Copies all rows in the data source object (DataReader or DataTable) to the destination table.


In general, an application performs the following steps to bulk copy data:

  1. Retrieve the data to copy into a DataTable or DataReader object.

  2. Connect to the destination database server.

  3. Create and configure the SqlBulkCopy object.

  4. Call the WriteToServer( ) method of the SqlBulkCopy object.

  5. Call the Close( ) method of the SqlBulkCopy object or dispose of the SqlBulkCopy object.

The following example copies all rows in the Person.Address table in the AdventureWorks database to a new table called Address (without a schema) in the ProgrammingSqlServer2005 database. Follow these steps:

  1. Create a SQL Server 2005 database called ProgrammingSqlServer2005 if you haven't already created it.

  2. Execute the following T-SQL command to create the Address table in the ProgrammingSqlServer2005 database:

     USE ProgrammingSqlServer2005 CREATE TABLE [Address](     [AddressID] [int] IDENTITY(1,1) NOT NULL,     [AddressLine1] [nvarchar](60) NOT NULL,     [AddressLine2] [nvarchar](60) NULL,     [City] [nvarchar](30) NOT NULL,     [StateProvinceID] [int] NOT NULL,     [PostalCode] [nvarchar](15) NOT NULL,     [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,     [ModifiedDate] [datetime] NOT NULL) 

  3. Create a Windows console application named BulkCopy.

  4. Copy the following code into Program.cs. Change the highlighted connection strings if necessary.

     using System; using System.Data; using System.Data.SqlClient; class Program {     static void Main(string[] args)     {         // get data from the source server using a data reader         SqlConnection srcConn = new SqlConnection(  );         srcConn.ConnectionString = "Data Source=localhost;" +             "Integrated Security=SSPI;" +             "Initial Catalog=AdventureWorks;";         srcConn.Open(  );         SqlCommand cmd =             new SqlCommand("SELECT * FROM Person.Address;", srcConn);         IDataReader dr = cmd.ExecuteReader(  );         // connection to the destination server         SqlConnection dstConn = new SqlConnection(  );         dstConn.ConnectionString = "Data Source=localhost;" +             "Integrated Security=SSPI;" +             "Initial Catalog=ProgrammingSqlServer2005;";         dstConn.Open(  );         // bulk copy the  data to the destination table         using (SqlBulkCopy bcp = new SqlBulkCopy(dstConn))         {             bcp.DestinationTableName = "Address";             bcp.WriteToServer(dr);         }         dstConn.Close(  );         dr.Close(  );         srcConn.Close(  );         Console.WriteLine("Press any key to continue.");         Console.ReadKey(  );     } } 

  5. Execute the application. The rows from the Person.Address table in the AdventureWorks database are bulk copied into the Address table in the ProgrammingSqlServer2005 database.

If the column names in the source and destination table do not match, you need to map the columns by using the SqlBulkCopyColumnMapping class. Each SqlBulkCopyColumnMapping instance defines a map between a column in the bulk copy source and the destination. Add the mapping instances by using the Add( ) method of the ColumnMappings property of the SqlBulkCopy object before calling the WriteToServer( ) method.

For example, if you change the name of the address line fields from AddressLine1 and AddressLine2 to AddressLine1a and AddressLine2a, you must add the following mapping code before you call the WriteToServer( ) method:

 bcp.ColumnMappings.Add(   new SqlBulkCopyColumnMapping("AddressID", "AddressID")); bcp.ColumnMappings.Add(   new SqlBulkCopyColumnMapping("AddressLine1", "AddressLine1a")); bcp.ColumnMappings.Add(   new SqlBulkCopyColumnMapping("AddressLine2", "AddressLine2a")); bcp.ColumnMappings.Add(   new SqlBulkCopyColumnMapping("City", "City")); bcp.ColumnMappings.Add(   new SqlBulkCopyColumnMapping("StateProvinceID", "StateProvinceID")); bcp.ColumnMappings.Add(   new SqlBulkCopyColumnMapping("PostalCode", "PostalCode")); bcp.ColumnMappings.Add(   new SqlBulkCopyColumnMapping("rowguid", "rowguid")); bcp.ColumnMappings.Add(   new SqlBulkCopyColumnMapping("ModifiedDate", "ModifiedDate")); 

Mappings can be specified by ordinal or column name, but all mappings must be specified in the same way. If the ColumnMapping collection is not empty, every column must be mapped whether their names match or not.

The SqlBulkCopy class supports transactions that are dedicated to the bulk copy operation, and can also use existing transactions. Dedicated transactions are used by default, as shown in the preceding example. The bulk copy is committed or rolled back automatically.

You can perform a bulk copy within an existing transaction, making the bulk copy part of the transaction together with other operations. This Windows application is similar to the previous example. It performs a bulk copy within a transaction. It also uses a DataTable object as the data source instead of a DataReader object.

 using System; using System.Data; using System.Data.SqlClient; class Program {     static void Main(string[] args)     {         SqlConnection srcConn = new SqlConnection(  );         srcConn.ConnectionString = "Data Source=localhost;" +             "Integrated Security=SSPI;" +             "Initial Catalog=AdventureWorks;";         SqlCommand cmd =             new SqlCommand("SELECT * FROM Person.Address;", srcConn);         SqlDataAdapter da = new SqlDataAdapter(cmd);         DataTable dt = new DataTable(  );         da.Fill(dt);         // connection to the destination server         SqlConnection dstConn = new SqlConnection(  );         dstConn.ConnectionString = "Data Source=localhost;" +             "Integrated Security=SSPI;" +             "Initial Catalog=ProgrammingSqlServer2005;";         dstConn.Open(  );         // create the transaction on the destination connection         SqlTransaction tx = dstConn.BeginTransaction(  );         try         {             // ... do some work using the transaction (tx)             // bulk copy the  data to the destination table within             // the transaction (tx)             using (SqlBulkCopy bcp =                 new SqlBulkCopy(dstConn, SqlBulkCopyOptions.Default, tx))             {                 bcp.DestinationTableName = "Address";                 bcp.WriteToServer(dt);             }             tx.Commit(  );         }         catch         {             tx.Rollback(  );         }         dstConn.Close(  );         Console.WriteLine("Press any key to continue.");         Console.ReadKey(  );     } } 

A.2.5. Support for New SQL Server Large-Value Data Types

SQL Server 2005 introduces large-value data typesvarchar(max), nvarchar(max), and varbinary(max) which allow storage of values up to 232 bytes in size. These types simplify working with large object (LOB) data working with large-value data types is the same as working with the smaller-value data types (varchar, nvarchar, and varbinary). Large-value data types can be used as column types and as variables, and they can be specified as input and output parameters without special handling. You can return a large-value data type in a SqlDataReader object or use a large-value data type to fill a DataTable object using a SqlDataAdapter object.

The limitations of the large-value data types are as follows:

  • A sql_variant type cannot contain a large-value data type.

  • A large-value data type cannot be specified as a key column in an index or used as a partitioning key column.

A.2.6. Support for SQL Server User-Defined Types

SQL Server 2005 introduces user-defined types (UDTs ). These extend SQL Server data types by letting you define both custom data structures containing one or more data types and objects containing one or more data types together with behaviors. UDTs can be used everywhere that SQL Server system data types can be used, including as variables or arguments or in column definitions.

You can create a UDT by using any language supported by the .NET Common Language Runtime (CLR). UDTs are defined as a class or structuredata is exposed as fields or properties, whereas behaviors are defined by methods.

Once a UDT is compiled into a .NET assembly, you must register the assembly in SQL Server by using the CREATE ASSEMBLY T-SQL statement. You must then create the UDT in SQL Server by using the CREATE TYPE T-SQL statement before you can use the UDT.

A.2.7. Support for Snapshot Isolation in Transactions

SQL Server 2005 introduces support for snapshot isolation row locking. When snapshot isolation is enabled, updated row versions for each transaction are maintained in the tempdb system database. Each transaction is identified by a unique transaction sequence number, which is recorded together with the updated row versions. A transaction works with the most recent row versions having transaction sequence numbers prior to the sequence number of the current transactiontransaction sequence numbers that are greater than the current transaction sequence number indicate that the transactions occurred after the current transaction started, and thus are ignored. The result is that all queries in the transaction see a consistent view of the database at the moment the transaction started. No locks are acquired, which allows multiple simultaneous transactions to execute without blocking or waiting. This improves performance and significantly reduces the chance of a deadlock. Snapshot isolation uses optimistic concurrencyif an attempt is made to update data that has been modified since it was last read, the transaction will roll back and an error will be raised.

You can reduce the chance of update conflict by using locking hints in a T-SQL statement or at the beginning of a transaction. For example, the UPDLOCK hint locks rows selected in a statement and blocks attempts to update them before the statement completes. Hints should be used sparinglyexcessive hints might suggest a problem with the application design.

Snapshot isolation is explicitly enabled for each database by setting the ALLOW_TRANSACTION_ISOLATION option to ON. You also need to set the READ_COMMITTED_SNAPSHOT option to ON to allow access to versioned rows under the default READ_COMMITTED isolation level. If the READ_COMMITTED_SNAPSHOT option is set to OFF, you must explicitly set the isolation level when initiating a transaction, as shown in the following code snippet:

 SqlTransaction tx = conn.BeginTransaction(IsolationLevel.Snapshot); 

A.2.8. Database Mirroring Support

Database mirroring lets you keep an up-to-date copy of a database on a standby server. The two copies of the database provide high availability and redundancyif the primary database fails, the mirror can quickly be promoted to take its place. The .NET Data Provider for SQL Server implicitly supports database mirroringonce the SQL Server 2005 database has been configured, database mirroring is automatic and is transparent to the developer.

SQL Server 2005 also supports explicit database mirroring. The SqlConnection object supports the Failover Partner parameter in the connection string. This lets the client application specify the name of the failover partner server. In this way, the client application can transparently attempt to establish a connection with the mirror database if the principal database is unavailable.

The name of the active server for the current connection is always available through the DataSource property of the SqlConnection instancethis property is updated when a connection is switched to the mirror server in response to a failover event.

Microsoft does not support database mirroring in the November 7, 2005 release of SQL Server 2005. As a result, database mirroring should be used only for evaluation purposes and not in production. Database mirroring is disabled by default and can be enabled by using trace flag 1400 as a startup parameter.


A.2.9. Server Enumeration

The GetdataSources( ) method of the SqlDataSourceEnumerator class enumerates active instances of SQL Server 2000 and later that are installed on your local network. The results are returned in a DataTable object with the columns shown in Table A-7.

Table A-7. DataTable schema for GetDataSources( ) method results

Column name

Description

ServerName

Name of the SQL Server.

InstanceName

Name of the server instance. This value is blank if the server is running as the default instance.

IsClustered

Indicates whether the server is part of a cluster.

Version

The version number of the server.


The following console application uses the SqlDataSourceEnumerator object to enumerate SQL Server instances:

 using System; using System.Data; using System.Data.Sql; class Program {     static void Main(string[] args)     {         DataTable dt = SqlDataSourceEnumerator.Instance.GetDataSources(  );         foreach (DataRow row in dt.Rows)             Console.WriteLine("{0}\t{1}\t{2}\t{3}",             row["ServerName"], row["InstanceName"],             row["IsClustered"], row["Version"]);         Console.WriteLine("Press any key to continue.");         Console.ReadKey(  );     } } 

The output looks similar to Figure A-9.

Figure A-9. Results for SqlDataSourceEnumerator example


The static Instance property of the SqlDataSourceEnumerator class returns an instance of the enumerator that is used to retrieve information about SQL Server instances.

A.2.10. Support for Retrieving Provider Statistics in SQL Server 2005

The .NET Framework Data Provider for SQL Server supports runtime statistics that expose information about processing queries in the database.

You must enable statistics by setting the StatisticsEnabled property of the SqlConnection object to true after the connection has been created. Once statistics are enabled, they can be retrieved into an IDictionary instance using the RetrieveStatistics( ) method of the SqlConnection object. The values in the dictionary are the statistic counter values, and are all of the long data type. The ResetStatistics( ) method of the SqlConnection object resets the counters. All statistics are gathered on a per-connection basis.

The following console application creates a connection, enables statistics, does a bit of work by filling a DataTable object using a data adapter, and iterates over the dictionary to output the name-value pair for each counter in the dictionary:

 using System; using System.Data; using System.Collections; using System.Data.SqlClient; class Program {     static void Main(string[] args)     {         // open a connection and enable statistics         using (SqlConnection conn = new SqlConnection(  ))         {             conn.ConnectionString = "Data Source=localhost;" +                 "Integrated Security=SSPI;Initial Catalog=AdventureWorks";             conn.StatisticsEnabled = true;             // do some work with the connection             SqlDataAdapter da =                 new SqlDataAdapter("SELECT * FROM Person.Contact", conn);             DataTable dt = new DataTable(  );             da.Fill(dt);             // get the statistics             IDictionary d = conn.RetrieveStatistics(  );             // move the dictionary keys to an array             string[] keys = new string[d.Count];             d.Keys.CopyTo(keys, 0);             // iterate over the dictionary displaying the key-value pair             for (int i = 0; i < d.Count; i++)                 Console.WriteLine("{0}\t{1}",                     keys[i], (long)d[keys[i]]);         }         Console.WriteLine(Environment.NewLine + "Press any key to continue.");         Console.ReadKey(  );     } } 

Results are shown in Figure A-10.

Figure A-10. Results for retrieving provider statistics example


See MSDN for a complete discussion of the available statistics.

A.2.11. Change Password Support

With SQL Server 2005 and Windows Server 2003 or later, you can programmatically change the existing password for the user specified in a connection string.

This example changes the password for a login named TestUser. First, create the user login in SQL Server Management Studio by right-clicking Security Logins in Object Explorer and selecting New Login from the context menu. In the Login-New dialog box, do the following:

  • Select the General page on the left side of the dialog box.

  • Enter TestUser in the Login name listbox.

  • Select the SQL Server Authentication radio button.

  • Enter password in both the Password and Confirm Password listboxes.

  • Uncheck the Enforce password policy checkbox.

  • Select User Mapping on the left side of the dialog box.

  • Check the AdventureWorks checkbox in the Users mapped to this login panel.

  • Click the OK button to create the user.

Create a new console application, replace Program.cs with the following code, and execute the example:

 using System; using System.Data; using System.Collections; using System.Data.SqlClient; class Program {     static void Main(string[] args)     {         string connStringOld = "Data Source=localhost;" +             "uid=TestUser;pwd=password;Initial Catalog=AdventureWorks";         SqlConnection.ChangePassword(connStringOld, "password2");         Console.WriteLine("Password changed to 'password2'.");         // open a connection         string connStringNew = "Data Source=localhost;" +             "uid=TestUser;pwd=password2;Initial Catalog=AdventureWorks";         SqlConnection conn = new SqlConnection(  );         conn.ConnectionString = connStringNew;         conn.Open(  );         Console.WriteLine("Connected with changed password.");         conn.Close(  );         Console.WriteLine("Disconnected.");         Console.WriteLine(Environment.NewLine + "Press any key to continue.");         Console.ReadKey(  );     } } 

Results are shown in Figure A-11.

Figure A-11. Results for change password example


The ChangePassword( ) method of the SqlConnection class take two arguments:

  • A connection string containing the user ID and password. An exception will be thrown if integrated security is specified in the connection string.

  • The new password.

The ChangePassword( ) method can be used to change an expired user password without administrator intervention. If the password has expired, calling the Open( ) method of the SqlConnection object raises a SqlException exception. If the password needs to be reset, the Number property of the SqlException object will be either 18487 (password expired) or 18488 (password must be reset before logging in).

A.2.12. Schema Discovery

The new schema discovery API in ADO.NET 2.0 lets you programmatically find and return metadata about the database for a connection. The database-independent API exposes schema elements, including tables, columns, and stored procedures.

The data connection exposes five categories of metadata through the GetSchema( ) method of the DbConnection class. This returns a DataTable object containing the metadata. It takes one of the five metadata collection names from the DbMeta-DataCollectionNames class described in Table A-8.

Table A-8. DbMetaDataCollectionNames public fields

Collection name

Description

DataSourceInformation

Information about the database instance.

DataTypes

Information about data types that the database supports. This includes information about mapping data-source types to .NET Framework data types.

MetaDataCollections

List of metadata collections available.

ReservedWords

List of reserved words in the database.

Restrictions

Array of qualifiers for each metadata collection that can be used to restrict the scope of information returned. One value is returned per row with the position of the qualifier in the array specified by the RestrictionNumber column.


The following example retrieves and outputs the available metadata collections:

 using System; using System.Collections; using System.Data.SqlClient; using System.Data; using System.Data.Common; class Program {     static void Main(string[] args)     {         SqlConnection conn = new SqlConnection(  );         conn.ConnectionString = "Data Source=localhost;" +             "Integrated Security=SSPI;Initial Catalog=AdventureWorks";         conn.Open(  );         DataTable dt = conn.GetSchema(             DbMetaDataCollectionNames.MetaDataCollections);         conn.Close(  );         foreach (DataRow row in dt.Rows)         {             Console.WriteLine("{0}; {1}; {2}",                 row[0], row[1], row[2]);         }         Console.WriteLine("\n\rPress any key to continue.");         Console.ReadKey(  );     } } 

Results are shown in Figure A-12.

Figure A-12. Results for GetSchema( ) method example


The DataTable object returned from the GetSchema( ) method has three columns, as described in Table A-9.

Table A-9. Columns in DataTable object returned by GetSchema( )

Column name

Description

CollectionName

The metadata collection name

NumberOfRestrictions

The maximum number of qualifiers for a metadata collection that can be used to restrict the scope of information returned

NumberOfIdentifierParts

The maximum number of identifier parts


An overload of the GetSchema( ) method takes the metadata collection name (one of the CollectionName values returned by the GetSchema( ) method) as an argument. For example, the following statement returns metadata about the tables in the database:

 DataTable dt = conn.GetSchema("Tables"); 

An extract from the result set follows:

TABLE_CATALOG

TABLE_SCHEMA

TABLE_NAME

TABLE_TYPE

AdventureWorks

dbo

AWBuildVersion

BASE TABLE

AdventureWorks

dbo

DatabaseLog

BASE TABLE

AdventureWorks

dbo

sysdiagrams

BASE TABLE

AdventureWorks

HumanResources

Department

BASE TABLE

...

   

AdventureWorks

Sales

vSalesPerson

VIEW

AdventureWorks

Sales

vSalesPersonSales

ByFiscalYears

VIEW

AdventureWorks

Sales

vStoreWith

Demographics

VIEW


Another overload of GetSchema( ) takes a string array of restrictions as a second argument. Call the GetSchema( ) method with the DbMetaDataCollectionNames.Restric-tions argument to get a valid list of restrictions for a metadata collection. There is one row per restrictioneach restriction has a unique RestrictionNumber value. For example, for the Tables metadata collection in SQL Server, there are four restrictions:

Restriction name

Restriction default

Restriction number

Catalog

TABLE_CATALOG

1

Owner

TABLE_SCHEMA

2

Table

TABLE_NAME

3

TableType

TABLE_TYPE

4


Continuing with the Tables metadata, the following code snippet uses restrictions to return information only for views in the Production schema:

 string[] r = new string[] {null, "Production", null, "VIEW"}; DataTable dt = conn.GetSchema("Tables", r); 

Support for DbConnection.GetSchema( ) is optional, so a data provider can choose to throw a NotSupportedException. There is no standard for the information returned when a metadata collection is queriedtwo providers can return different information (i.e., columns in the DataTable object) and support different restrictions.



Programming SQL Server 2005
Programming SQL Server 2005
ISBN: 0596004796
EAN: 2147483647
Year: 2007
Pages: 147
Authors: Bill Hamilton

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net