Introduction
In this article, we will continue our talking about ADO.Net 2.0 Enhancements. Here we will explore:
- Bulk copy
- Notifications (not SQL Server Notification Services but something else as we will see)
ADO.NET 2.0 provides the
System.Data.SqlClient.SqlBulkCopy class, which can be used to perform efficient bulk copy operations to SQL Server.
But the data source is not limited to SQL Server; any data source can be used, as long as the data can be loaded to a DataTable instance or read with an IDataReader instance. The
SqlBulkCopy class supports:
- single or multiple bulk copy operations.
- Perform a bulk copy operation within a transaction.
This feature was not supported by the previous release of .Net Framework. But bulk copy can be performed in the old version using the Transact-SQL BULK INSERT statement using the SqlCommand object.
Example:
- SqlBulkCopy bcp = new SqlBulkCopy(connection);
- bcp.DestinationTableName = "BulkCopyDemoTable";
- bcp.WriteToServer(reader);
You can Review the attached demo for the demonstration.
Notifications
SQL Server 2005 can notify client applications when data they have previously retrieved has been updated on the server. This makes it possible for client applications to refresh their local copy of the data when it becomes out-of-date.
ADO.NET provides two ways to take advantage of this notification functionality:
- Using a high-level SqlDependency class with an OnChanged event
- Using a lower-level SqlNotifcationRequest class that can be used to access custom notification queues on the server.
SqlDependency requires the .NET Framework version 2.0 and SQL Server 2005.
The most non-ASP.NET application should use
the SqlDependency object, while ASP.NET applications should use the higher level
SqlCacheDependency object which wraps
SqDependency and provides a framework for administrating the notification and cache objects.
Also, you should note that
SqlDependency was designed to be used in ASP.NET or middle-tier services where there is a relatively small number of servers having dependencies active against the database. It was not designed for use in client applications, where hundreds or thousands of client computers would have
SqlDependency objects set up for a single database server.
You can demonstrate
SqlDependency, download the attached demo. You may need to change the connection string in the
GetConnectionString method.
How to run the demo:
Compile and run both applications. Clicking Get Data on the watcher application should fill the application's grid with data and register a notification request. Examine the data in the grid, noting a product ID for a product whose inventory is zero. Then, using the updater application, enter the product ID and a new quantity of one, and click Update. You should see the watcher application automatically update itself moments after the updater changes the data on the server. Observe that the Listbox control displays information about the update. Repeat the process for other quantities and products.
Hope you enjoyed ADO.Net 2.0 Enhancements.