AspdotnetCodes.com
Search
Articles
Books
Resources
Asp.Net News
Downloads
Free Tech Magazines
Asp.Net Web Hosting
Archives
Softwares
Newsletter
Suggest Us
Link to Us
Feeds Subscription
Articles
Questions & Answers
Tips & Tricks
 Transfer Data from Source to Destination Table Without Insert Query
Posted by konda reddy on 6/9/2008 7:12:59 AM Category: ADO.NET
Total Views : 37312
Add to my favorites
Email to friend
  
Introduction
This article explains the methods of transferring huge data from any data source to SQL Server database using Asp.Net 2.0’s SqlBulkCopy class.
Transferring data to SQL Server database is a troubling process in various circumstances. If the amount of data is limited or you are transferring data within SQL Server data sources, then you can say it is an easy process to do, either by using a Select and Insert statement or by opening the Import and Export options in SQL Server Enterprise Manager. But what can you say if the data you are going to transfer is huge, bulk or large and your source data source is not SQL Server?

The answer is simple. Just go for SqlBulkCopy. This is a class that helps you to create a managed code application to transfer data from any data sources into SQL Server tables. Using this class you can write data to SQL Server tables only, but the data source can be any such as Microsoft Access, Microsoft Excel, XML, CSV file or almost any data that can be loaded to a DataTable.

This article explains transferring of data from the 4 different data sources such as Access, Excel, CSV and XML using SqlBulkCopy class. To transfer data with SqlBulkCopy, from any data sources, the procedure is same, which is explained below.

1. Open the Data Source with appropiate Connection String and retrieve the data, keep it in a DataTable.

2. Open the destination SQL Server database.

3. Create SqlBulkCopy object, by passing the destination SQL Server database connection object.

4. Set the DestinationTableName property, this is your destination SQL Server table name.

5. Map the columns between your source and the destination table’s column names. This is optional but it is recommended in case if either column names or column positions are different.

6. Now call SqlBulkCopy WriteToServer method, by passing the DataTable object as its argument. This method is responsible for transferring data from the data source to the SQL Server tables.

7. At last close the SqlBulkCopy object.


Transferring data from Microsoft Access

Now let us see some sample source code for performing data transfer from Access data source to SQL Server database.
string cnstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\\test.mdb;";

string sql="Select * from Customers";

OleDbDataAdapter da = new OleDbDataAdapter(sql, cnstr);
DataTable dt = new DataTable();
da.Fill(dt);

SqlConnection cn = new SqlConnection(“YourConnectionString”);
cn.Open();

SqlBulkCopy bulkcopy = new SqlBulkCopy(cn);
bulkcopy.DestinationTableName = "SampleCustomers";
SqlBulkCopyColumnMapping colmap1 = new SqlBulkCopyColumnMapping(0, "CusCode");
SqlBulkCopyColumnMapping colmap2 = new SqlBulkCopyColumnMapping(1, "CusName");
SqlBulkCopyColumnMapping colmap3 = new SqlBulkCopyColumnMapping(2, "CusSex");
SqlBulkCopyColumnMapping colmap4 = new SqlBulkCopyColumnMapping(3, "CusCity");
SqlBulkCopyColumnMapping colmap5 = new SqlBulkCopyColumnMapping(4, "CusState");
SqlBulkCopyColumnMapping colmap6 = new SqlBulkCopyColumnMapping(5, "CusType");
bulkcopy.ColumnMappings.Add(colmap1);
bulkcopy.ColumnMappings.Add(colmap2);
bulkcopy.ColumnMappings.Add(colmap3);
bulkcopy.ColumnMappings.Add(colmap4);
bulkcopy.ColumnMappings.Add(colmap5);
bulkcopy.ColumnMappings.Add(colmap6);

bulkcopy.WriteToServer(dt);
bulkcopy.Close();
cn.Close();


In the above code, we are using OleDbDataAdapter to fill the source DataTable. hen destination SQL Server Connection is opened, SqlBulkCopy object is created, set the Destination table name, map the columns between source and destination data sources and then call WriteToServer method to transfer the data.

Transferring data from Microsoft Excel

Here the data source is Microsoft Excel Worksheet. The sample data is stored in worksheet named as ‘Customer’. The Excel sheet file name is used in the ConnectionString and the Excel worksheet name is used in the Select statement.

string cnstrexcel = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\\SampleBook.xls;Extended Properties='Excel 8.0;HDR=no;'";

string sql = "Select * from [Customers$]";

OleDbDataAdapter da = new OleDbDataAdapter(sql, cnstrexcel);
DataTable dt = new DataTable();
da.Fill(dt);

SqlBulkCopy bulkcopy = new SqlBulkCopy(cn);
bulkcopy.DestinationTableName = "SampleCustomers";
SqlBulkCopyColumnMapping colmap1 = new SqlBulkCopyColumnMapping(0, "CusCode");
SqlBulkCopyColumnMapping colmap2 = new SqlBulkCopyColumnMapping(1, "CusName");
SqlBulkCopyColumnMapping colmap3 = new SqlBulkCopyColumnMapping(2, "CusSex");
SqlBulkCopyColumnMapping colmap4 = new SqlBulkCopyColumnMapping(3, "CusCity");
SqlBulkCopyColumnMapping colmap5 = new SqlBulkCopyColumnMapping(4, "CusState");
SqlBulkCopyColumnMapping colmap6 = new SqlBulkCopyColumnMapping(5, "CusType");
bulkcopy.ColumnMappings.Add(colmap1);
bulkcopy.ColumnMappings.Add(colmap2);
bulkcopy.ColumnMappings.Add(colmap3);
bulkcopy.ColumnMappings.Add(colmap4);
bulkcopy.ColumnMappings.Add(colmap5);
bulkcopy.ColumnMappings.Add(colmap6);

bulkcopy.WriteToServer(dt);
bulkcopy.Close();
cn.Close();
If you look at the above sample code, only the process of fetching data from Excel worksheet is different. Rest everything is same.

Three Points to Remember:

1. ConnectionString for Excel Sheet: Note the Extended Properties values must be within single quotes. Else you will receive some errors like ‘Could not find installable ISAM’.

2. HDR=Yes indicates that the first row of the worksheet is column names, not data. If you mention as HDR=No, then the first row must be column names.

3. Table name in the data source query. This is not the Excel file name. Instead it is Excel Worksheet name in which you have data to tranfer. So keep your excel worksheet name between “[“ and “]” square brackets followed by “$” dollar symbol.

Transferring data from Comma Separated Values (CSV) file

The transfer of data from a CSV file is same as the Excel worksheet process. There are only small changes in the Select statement and the connection string.

string filepath = "c:\\";

string sql = "SELECT * FROM SampleCustomer.csv";

string cnstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";" + "Extended Properties='text;FMT=Delimited(;);HDR=No'";

OleDbDataAdapter da = new OleDbDataAdapter(sql, cnstrexcel);
DataTable dt = new DataTable();
da.Fill(dt);

SqlBulkCopy bulkcopy = new SqlBulkCopy(cn);
bulkcopy.DestinationTableName = "SampleCustomers";
SqlBulkCopyColumnMapping colmap1 = new SqlBulkCopyColumnMapping(0, "CusCode");
SqlBulkCopyColumnMapping colmap2 = new SqlBulkCopyColumnMapping(1, "CusName");
SqlBulkCopyColumnMapping colmap3 = new SqlBulkCopyColumnMapping(2, "CusSex");
SqlBulkCopyColumnMapping colmap4 = new SqlBulkCopyColumnMapping(3, "CusCity");
SqlBulkCopyColumnMapping colmap5 = new SqlBulkCopyColumnMapping(4, "CusState");
SqlBulkCopyColumnMapping colmap6 = new SqlBulkCopyColumnMapping(5, "CusType");
bulkcopy.ColumnMappings.Add(colmap1);
bulkcopy.ColumnMappings.Add(colmap2);
bulkcopy.ColumnMappings.Add(colmap3);
bulkcopy.ColumnMappings.Add(colmap4);
bulkcopy.ColumnMappings.Add(colmap5);
bulkcopy.ColumnMappings.Add(colmap6);

bulkcopy.WriteToServer(dt);
bulkcopy.Close();
cn.Close();

In the above code, the filepath is the physical location of the csv file. We have to pass this filepath value to the connection string of the data source. Remember that you must not pass csv file name along in this filepath. Instead you have to use the csv file name in the Select statement. So if you check in the above code, SampleCustomer.csv is the csv file name.


Transferring data from XML file

Last, we are going to look the transfer of data from a XML file. The procedure is very simple. Load the XML file to a dataset by using the ReadXml method as follows.


DataSet ds = new DataSet();
ds.ReadXml(Server.MapPath("SampleCustomer.xml"));

SqlBulkCopy bulkcopy = new SqlBulkCopy(cn);
bulkcopy.DestinationTableName = "SampleCustomers";
SqlBulkCopyColumnMapping colmap1 = new SqlBulkCopyColumnMapping(0, "CusCode");
SqlBulkCopyColumnMapping colmap2 = new SqlBulkCopyColumnMapping(1, "CusName");
SqlBulkCopyColumnMapping colmap3 = new SqlBulkCopyColumnMapping(2, "CusSex");
SqlBulkCopyColumnMapping colmap4 = new SqlBulkCopyColumnMapping(3, "CusCity");
SqlBulkCopyColumnMapping colmap5 = new SqlBulkCopyColumnMapping(4, "CusState");
SqlBulkCopyColumnMapping colmap6 = new SqlBulkCopyColumnMapping(5, "CusType");
bulkcopy.ColumnMappings.Add(colmap1);
bulkcopy.ColumnMappings.Add(colmap2);
bulkcopy.ColumnMappings.Add(colmap3);
bulkcopy.ColumnMappings.Add(colmap4);
bulkcopy.ColumnMappings.Add(colmap5);
bulkcopy.ColumnMappings.Add(colmap6);

bulkcopy.WriteToServer(ds.Tables(0));
bulkcopy.Close();
cn.Close();

Thus, we have seen 4 methods of transferring data from different data sources. And it is understood how much it is easy to transfer data between two SQL Server data sources.
Below VB.Net Source Code is contributed by Mr. Konda Reddy

Our valued member provides the above source code. We appreciate the time and effort given by him/her to suggest a nice concept for our visitors. We provide the source code for educational purpose only. We take no responsibility for the accuracy of the above source code.
 
Viewer's Comments
Posted by Mukesh kumar on 7/5/2008 10:33:42 PM
can it possible through oracle database.
 
Posted by RAJESH KUMAR VISHWAKARMA on 12/8/2009 6:13:00 AM
Really The Above Article is very fantastic....
 
 Rating & Comments
A word 'Excellent' means lot to the author of this article. You can give comments about this article but not the author.
Rate this Article:
Name:
Email Id:  
We never display your email id anywhere.
Comment/Question: Max. 500 letters
One plus Thirty Equals To
 
Sponsored by
Oracle Magazine
The ASP.NET 2.0 Anthology: 101 Essential Tips, Tricks & Hacks - Free 156 Page Preview
Introduction to Software Development