This article explains how to insert/update bulk data in .net application. This way your application will improve performance. For example, inserting few hundred records together into same table, we normally do something like:

For i As Integer = 0 To 200

    'SQL insert/update

Next

For insert, Luckily, .NET has a SqlBulkCopy Class that Lets you efficiently bulk load a SQL Server table with data from another source. Here, i will save datatable into sql table.

Using bulkcopy As SqlBulkCopy = New SqlBulkCopy(Configuration.ConnectionString, _

SqlBulkCopyOptions.UseInternalTransaction)

   bulkcopy.DestinationTableName = "dbo.MyTable"  

   bulkcopy.WriteToServer(myDataTable)

End Using

In above code,  bulkcopy.WriteToServer(myDataTable) Copies all rows in the supplied myDataTable to a destination dbo.MyTable specified by the DestinationTableName property of the SqlBulkCopy object. The SqlBulkCopy class can be used to write data only to SQL Server tables. However, the data source is not limited to SQL Server; any data source can be used, as long as the data can be loaded to a DataTable instance or read with a IDataReader instance.

Unfortunately, sqlBulkCopy does not provide any features to check existing records and update. Therefore, we can use the following technique to improve performance for update. Here is a code snippet.

Dim strSQL As New StringBuilder()

trSQL.Append(" DECLARE @temp TABLE(_ID int, _FullName varchar(20)) ")

trSQL.Append(" Insert Into @temp (_ID, _FullName) ")

For i As Integer = 0 To 200

   strSQL.AppendFormat("select {0},'{1}' union all ", i, "Some text")

Next

 'Remove last union all 

If strSQL.Length > 10 Then

strSQL.Remove(strSQL.Length - 10, 10)  

strSQL.Append(" UPDATE dbo.RealTable SET ID = t._ID, FullName=t._FullName from @temp t WHERE ID = t._ID ")

'then excute sql statement

Our sql statement will look as shown below.

DECLARE @temp TABLE(_ID int, _FullName varchar(20))

Insert Into @temp (_ID, _FullName)

select 1,'some value' union all

select 2,'some value' union all

.......

.......

.......

select 199,'some value' union all

select 200,'some value' 

UPDATE dbo.RealTable 

SET ID = t._ID, 

FullName=t._FullName 

from @temp t WHERE ID = t._ID

In above code, i created a temp variable table, and then inserted all the records using sql union all. Once the temp table is populated then updated the sql table using join with temp table. This is very powerful technique to improve performance in application.