Import records to Database using SqlBulkCopy with Expression Column in C#

Introduction

How to import records to data base using sqlBulkCopy class detail discussed at this article. Since sometimes users need import a custom column n or expression column value also to need insert to database. For that this article has details and sample code how we could import expression column data to database which not existing in the source data.

Implementation

To the Implements this scenario, I have taken following assumption and created a sample database also.

1. I have created a sample dataset which have 4 columns, first 3 columns are having data from the source and a firth column is expression column. The firth column is called as Tax, and third column is called as Salary. Since 4^Th column have value from the Salary and multiple by a constant amount.

2. The database has followings structure.

Let's write code lines to create data source and finally import to data base using SqlBulkCopy Object. In the first step we need create a new windows Type project using Visual Studio 2008 and change the name as "SqlbulkCopyDemo", see following figure.

Then add a namespace to access the SqlBulkCopy class from the .Net Famework.

{codecitation class="brush: csharp; gutter: true;" width="650px"}

using System.Data.SqlClient;

{/codecitation}

In the next step write code to compose a dataset with columns and rows with sample records.

{codecitation class="brush: csharp; gutter: true;" width="650px"}

private DataSet ComposeDataSet()
{
DataSet data = new DataSet();
DataTable table = new DataTable();
DataColumn columnID = new DataColumn("EmpID", typeof(string));
DataColumn columnName = new DataColumn("FName", typeof(string));
DataColumn columnSalary = new DataColumn("Salary", typeof(double));
DataColumn columnTax = new DataColumn("Tax", typeof(double));
table.Columns.AddRange(new[] { columnID, columnName, columnSalary, columnTax });
DataRow row = null;
row = table.NewRow();
row[0] = "00001";
row[1] = "John";
row[2] = 25000.00;
row[3] = int.Parse(row[2].ToString()) * 0.34;
table.Rows.Add(row);

row = table.NewRow();
row[0] = "00002";
row[1] = "Peter";
row[2] = 28000.00;
row[3] = int.Parse(row[2].ToString()) * 0.34;
table.Rows.Add(row);

row = table.NewRow();
row[0] = "00003";
row[1] = "Peter";
row[2] = 32000.00;
row[3] = double.Parse(row[2].ToString()) * 0.34; // expression column
table.Rows.Add(row);

data.Tables.Add(table);
return data;
}

{/codecitation}

In above method have code lines to create new data set, then create new table and add four columns. In the first two columns are data type in string.and third and fourth columns are data type is double.Fourth column depending on the third column. Since its work as expression column with a multiple by the a constant value.

Then add 3 samples record to data table , finally add that table to dataset.

Note: In above source data set has a expression column.And above method is good example to add columns dyanmically to the dataset.

Now we are going to write code to import this records to database. To that we need write code like followings.

{codecitation class="brush: csharp; gutter: true;" width="650px"}

{/codecitation}

In the above lines code help us to import data to database using SqlBulkCopy.Within the UpDateToServer method first create new destination sql connection.And then create new SqlBulkCopy within the using clause.The advantage of the using clause , if sometimes error occurred in data write to server method it's will throw the exception. But it's close the SqlBulkCopy object first and dispose the current object then it will throw the execption anc ome to the catch point.

Then I have added four SqlBulkCopyColumnMapping columns object to map with source column with destiona column for Employee table.then add those columns to SqlBulkCopy object one by one. Then iahve register a event to raise event every 1000 rows copied it will give message to users.And also give the Destination Atble name also.

The Error messages and reason return by the SqlBulkCopy Object.

1. forget to set destination table name.

The DestinationTableName property must be set before calling this method.

2. Set wrong destionation table name to SqlBulkCopy object

Cannot access destination table 'TableName'.

3. Set wrong destination column name

The given ColumnMapping does not match up with any column in the source or destination.

4.When you set wrong source column name

The given ColumnName 'nameOftheColumn' does not match up with any column in data source.

Finaly call the WriteToServerMethod and give the source data table to write data to database.every 1000 records you will get message box from the RowCopied event.

Conclusion

This article discussed about import data to database suing SqlBulkCopy.In the most important here import a source expression column to database with SqlBulkCopy.

What's Next

In the next article , i 'm going to add more features to SqlBulkCopy.currently SqlBulkCopy class have support to import data from the DataRow,datatable and IDataReader.i would like to write code to import from the Excel,CSV file using SqlBulkCopy Class.

Download Source code

Thank you

About the Author

RRaveen - B.Sc. degree in Computer Science. - 4+ years experience in Visual C#.net and VB.net - Obsessed in OOP style design and programming. - Designing and developing Network security tools. - Designing and developing a client/server application for sharing files among users in a way other than FTP protocol. - Designing and implementing GSM gateway applications and bulk messaging. - Windows Mobile and Symbian Programming - Having knowledge with ERP solutions The summary of my skills: C#, VB.Net#,ASP.net, VC++, Java, WPF,WCF, Oracle, SQL Server, MS Access, Windows NT administration Occupation: Tech lead Location: Singapore Company: TGS Solutions