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)
Bulk Copy
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 a 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 previous release of .Net Framework. But bulk copy can be performed in old version using Transact-SQL BULK INSERT statement using the SqlCommand object.
Example:
SqlBulkCopy bcp = new SqlBulkCopy(connection); //Assuing you have DbConnection object named connection.
bcp.DestinationTableName = "BulkCopyDemoTable";
bcp.WriteToServer(reader); //Assuing you have IDataReader object named reader and ready to use.
You can Review the attached demo for 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.
Most non-ASP.NET application should use SqlDependency object, while ASP.NET appications should use the higher level SqlCacheDependency object which wraps SqDependency and provides a frame work 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.