Data insert in loop using sql bulkcopy class c#

Question

Data insert in loop using sql bulkcopy class c#

c# sqlbulkcopy

Question

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

Thomas

Popular Answer

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

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

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

Code:

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

    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"

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

    Return True

End Function

Abdul Saboor

View more on Stack Overflow

AdamWhite · Accepted Answer · 2/28/2013 2:47:32 PM

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,
                              });
            }
        }
        try
        {
            _context.Connection.Open();
            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
                bulkInsert.WriteToServer(dataLines.AsDataReader());
                _context.SaveChanges();
            }
        }
        finally
        {
            _context.Connection.Close();
        }

Data insert in loop using sql bulkcopy class c#

Question

Fastest Entity Framework Extensions

Accepted Answer

Popular Answer

Performance

Expression Evaluator