David Hayden ( Florida .NET Developer )
SqlBulkCopy is a new feature in ADO.NET 2.0 that gives you DTS-like speeds when you need to programmatically copy data from one database to another. Late last night I needed to harness SqlBulkCopy when for some unknown reason my beloved Red Gate Tools kept hanging when trying to transfer data from SQL Server 2000 to SQL Server 2005.
Lucky for me my situation was simple. I had identical tables on both databases. I just needed to populate those empty tables on SQL Server 2005 with the data on SQL Server 2000. It took me about 20 minutes to write the code as SqlBulkCopy does the bulk of the work.
Shown below is my slapped-together CopyData Class that accepts 2 connection strings, one for the source database and one for the destination database. A single method CopyTable is called with the name of the table whose data needs to be transferred from one database to another.
///
/// CopyData
///
public class CopyData
{
string _sourceConnectionString;
string _destinationConnectionString;
public CopyData(string sourceConnectionString,
string destinationConnectionString)
{
_sourceConnectionString =
sourceConnectionString;
_destinationConnectionString =
destinationConnectionString;
}
public void CopyTable(string table)
{
using (SqlConnection source =
new SqlConnection(_sourceConnectionString))
{
string sql = string.Format("SELECT * FROM [{0}]",
table);
SqlCommand command = new SqlCommand(sql, source);
source.Open();
IDataReader dr = command.ExecuteReader();
using (SqlBulkCopy copy =
new SqlBulkCopy(_destinationConnectionString))
{
copy.DestinationTableName = table;
copy.WriteToServer(dr);
}
}
}
}
It assumes you passed in valid connection strings, the databases actually exist, and the table exists at both databases. If not, you can count on an unhandled SqlException being thrown at you.
SqlBulkCopy has the option of accepting an IDataReader as input, so the CopyTable method opens up a connection to the source database and does a “SELECT *” on the source table using the SqlCommand object's ExecuteReader.
An instance of SqlBulkCopy is created and passed the connectionstring of the destination database in its constructor. The name of the destination table is provided and WriteToServer takes all the information from the IDataReader and puts it in the empty destination table.
Copying table data is as simple as:
CopyData copier = new CopyData(".ConnectionString1.",
".ConnectionString2.");
copier.CopyTable(".TableName.");
Conclusion
SqlBulkCopy is a very useful class in ADO.NET 2.0 for copying data between tables at high speeds.
(export data,import data)
By implementing in your code an interface with the 
Grids (two-dimensional tables) are a classic way to present data. They've been around since the beginning of computing. The first killer application for the PC was the spreadsheet, a grid-oriented way of managing data. Throughout the 1990s, several companies made a lot of money selling grid controls—including Visual Basic® custom controls (VBX) in the early 1990s, and Java applets and ActiveX® controls during the mid- to late 1990s. It makes complete sense to have the equivalent functionality within ASP.NET.
RSS Feed (xml)
