Data insert in loop using sql bulkcopy class c#

c# sqlbulkcopy


i have two database now i have to insert data from one db to another db in loop. i know bit sql bulk copy and i do not know how to insert one data at a time using sql bulk copy.

here is my table structure

CREATE TABLE [CandidateApplication](
 [EmailID] [varchar](300) NOT NULL,
 [Name] [varchar](300) NULL,
 [FileName] [varchar](500) NULL,
 [IsDownloaded] [bit] NOT NULL

basically i need to fetch data from one db with clause like IsDownloaded=0 and then i need to iterate in loop. with in loop i will get file path from FileName field and download that file. if the file download successfully then i have to save that data from db1 to db2 and update field IsDownloaded=1 to db1.

can i handle the data insert from one db to another db with in loop using sqlbulk copy class. please suggest. thanks

2/28/2013 8:53:20 AM

Accepted Answer

you wouldn't need to iterate over the data inserting one by one, all you need to do is to pass a collection into the WriteToServer() method.

an example is:

HashSet<SomeObject> dataLines = new HashSet<SomeObject>(); 

foreach (var entity in someCollection)
            if(entity == somecondition)
                  dataLines.Add(new SomeObject
                                  CollectionItem1 = entity.Property1,
                                  CollectionItem2 = entity.Property2,
                                  CollectionItem3 = entity.Property3,
                                  CollectionItem4 = entity.Property4,
                                  CollectionItem5 = entity.Property5,
                                  CollectionItem6 = entity.Property6,
                                  CollectionItem7 = entity.Property7,
            EntityConnection connection = _context.Connection as EntityConnection;
            SqlConnection sqlConnection = null;
            if (connection != null)
                sqlConnection = connection.StoreConnection as SqlConnection;

            if (sqlConnection != null)

                SqlBulkCopy bulkInsert = new SqlBulkCopy(sqlConnection);
                bulkInsert.DestinationTableName = "SomeTable";
                bulkInsert.ColumnMappings.Add("CollectionItem1", "Column1");
                bulkInsert.ColumnMappings.Add("CollectionItem2", "Column2");
                bulkInsert.ColumnMappings.Add("CollectionItem3", "Column3");
                bulkInsert.ColumnMappings.Add("CollectionItem4", "Column4");
                bulkInsert.ColumnMappings.Add("CollectionItem5", "Column5");
                bulkInsert.ColumnMappings.Add("CollectionItem6", "Column6");
                bulkInsert.ColumnMappings.Add("CollectionItem7", "Column7");
// dataLines is a collection of objects
2/28/2013 2:47:32 PM

Popular Answer

It is simple dude:) Sorry my code in I hope you dont mind it

Note: The important thing is datatype in your datatable and destination table. They should be same

  1. Fetch data from db1 and do whatever you want to do and get ready the data in datatable
  2. Use this function to dump your data in 2nd database


  Public Shared Function BulkSave(ByVal dt As DataTable) As Boolean
    Dim mydb As New CSdatabase

    Dim connectionString = "Connection String"
    '' so there is no need to map columns. 
        Using bulkCopy As SqlBulkCopy = New SqlBulkCopy(connectionString)
            bulkCopy.BatchSize = 25000
            bulkCopy.BulkCopyTimeout = 300

            bulkCopy.ColumnMappings.Add("EmailID", "EmailID")
            bulkCopy.ColumnMappings.Add("Name", "Name")
            bulkCopy.ColumnMappings.Add("FileName", "FileName")
            bulkCopy.ColumnMappings.Add("IsDownloaded", "IsDownloaded")
            bulkCopy.DestinationTableName = "dbo.CandidateApplication"

        End Using
    Catch ex As Exception
        Throw ex
        'mydb.closeConnection() ' Close your conneciton here
    End Try

    Return True

End Function

Related Questions

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow