|
|
|
|
|
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 :
1008 |
|
Adding to your Favorites....
|
|
|
 |
|
|
|
|
|
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. |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|