.Net |1 more ... show less ...
By : Darpan Sinha, Solution Architect, Fujitsu Consulting India Pvt Ltd
Source : http://www.ciol.com
Activity: 1 comments 1094 views last activity : 10 22 2011 05:10:24 +0000
|Applies To: .NET developers |
USP: Copy bulk data from different sources to SQL server
Primary Link: msdn.microsoft.com
Keyword: ADO.NET 2.0Bulk Data Copy using ADO.NET
The simplest and fastest way of copying a large amount of data from different sources to an SQL Server table can be accomplished with the help of Bulk Copy Operation, a new feature in ADO.NET version 2.0.
Here different sources of data are those from which data can be loaded into 'DataTable' instance or read with 'IDataReader' instance.
To accomplish bulk copy one has to use 'SqlBulkCopy' class that is present in 'Sytem.Data.SqlClient' namespace in .NET framework. Now using this 'SqlBulkCopy' class one can perform single bulk copy, multiple bulk copy and bulk copy with a transaction.
Before implementing bulk copy we should be ready with source and destination of data. In this example we would be using SQL Server as both source and destination. We would copy data from 'Source' table and paste it into 'Destination' residing on same server.
We will start by creating a connection to the SQL Server that is acting as a source of data. The following code snippet shows how this is done:
string connectionString = "Data Source=.;Initial Catalog=NTL;User ID=sa;Password=P@ssw0rd";
SqlConnection sourceConnection = new SqlConnection(connectionString);
Next step is to run commands to get data into a DataReader, following is the code to accomplish the same:
We created a source table 'Table_1' using SQL Server Manager in 'sandeep' database that contains some values to be copied
SqlCommand commandSourceData = new SqlCommand("SELECT * FROM Source;", sourceConnection);
SqlDataReader reader = commandSourceData.ExecuteReader();
Connect to a destination table of data using:
SqlConnection destinationConnection = new SqlConnection(connectionString);
Destination table 'Table_2' will be required to fill the data from 'Table_1' using bulk copy
Point to be noted here is that we are using same 'connectionString' as we are copying data to same server. We now have to copy data using bulk copy feature:
SqlBulkCopy bulkCopy = new SqlBulkCopy(destinationConnection);
bulkCopy.DestinationTableName = "Destination";
In the end we closed 'reader' and then we can close our connections to the SQL server.In this way data from one table was copied to other in fastest possible way.It is also possible to use single instance of 'SqlBulkCopy' for performing multiple bulk copy operation. This technique is more efficient than using separate 'SqlBulkCopy' instances for each operations.
One more technique is to perform bulk copy in a transaction. Using this technique one can perform multiple bulk copy operation along with other database operations i.e. update, delete etc and as one is using transaction it canbe easily commited or rolled back.
A Windows form was created using Visual Studio 2005 with a single button 'bulkcopy', as one clicks on this button data is copied and a message is shown to user
Besides bulk copy, ADO.NET version 2.0 has plenty of new features, here we are listing some of them:
(a) Multiple Active Results Sets(MARS)-allows application to have multiple 'SqlDataReader' open on a connection where each instance of 'SqlDataReader' is started from a separate command.
To verify our results we went back to SQL Server Manager to check contents of 'Table_2' and found that valus of 'Table_1' were present
(b) Batch processing-another feature included to enhance application performance is batch processing in which updates to databse from 'Dataset' are done in batches.
(c) Data tracing-an interesting feature is built-in data tracing supported by .NET data providers.
All these features of ADO.NET version 2.0 make it a better and efficient tool for data access.
- Create a confidential Career Profile and Resume/C.V. online
- Get advice for planning their career and for marketing of experience and skills
- Maximize awareness of and access to the best career opportunities
Why Not .... there is no rationale behind forcing someone not to do an event which is harmless instead its spreading love.
Thanks Sumitra... and I have embeded the vid
Its basicly coz plp see others as threat and want to be on top they do things like this so If you do your job properly and you yourself don't do politics you can get away.