Data insert in loop using sql bulkcopy class c#

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

score 1 · Answer 1 · edited Feb 28 '13 at 09:35

1

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

edited Feb 28 '13 at 09:35

marc_s

732,580
175
1,330
1,459

answered Feb 28 '13 at 09:02

Abdul Saboor

4,079
2
33
25

the batchsize is 25000 rows in 300 seconds – Abdul Saboor Feb 28 '13 at 09:04
i know how to use SqlBulkCopy class. my question was different that when i would use SqlBulkCopy then can i update data in loop means one at a time? – Thomas Feb 28 '13 at 09:06
If you want to update one row at a time then you don't need sqlbulkCopy. SqlBulkCopy is slower in that case. – Abdul Saboor Feb 28 '13 at 09:42
i need to know is it at all feasible to update data with SqlBulkCopy in loop one-by-one...if yes then can u guide me with code sample. thanks – Thomas Mar 01 '13 at 18:30
no It is not feasible to update with loop, Bulk update means you have a lot of data and you want to update in DB and in this statement. I don't understand why you need loop? – Abdul Saboor Mar 04 '13 at 07:33

AdamWhite · Accepted Answer · 2013-02-28T14:47:32.610

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();
        }

can u plzz give me bit full code. give me code for dataLines. thanks — Thomas, Feb 28 '13 at 11:42
can't we use List instead of HashSet ? if not please tell me the reason. — Thomas, Mar 01 '13 at 07:42
Of course you can use a list, i used a hashset as i overrode the equality members so i could ensure that each item within that collection was unique based on a variety of factors that were applied when adding an item to the collection, rather than doing a distinct later on — AdamWhite, Mar 01 '13 at 09:59

Data insert in loop using sql bulkcopy class c#

2 Answers2