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

marc_s · Accepted Answer · 2013-02-28 09:35:40Z

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, 2013 at 9:35

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Feb 28, 2013 at 9:02

Abdul Saboor

4,1372 gold badges35 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Abdul Saboor Over a year ago

the batchsize is 25000 rows in 300 seconds

Thomas Over a year ago

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?

Abdul Saboor Over a year ago

If you want to update one row at a time then you don't need sqlbulkCopy. SqlBulkCopy is slower in that case.

Thomas Over a year ago

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

Abdul Saboor Over a year ago

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?

AdamWhite · Accepted Answer · 2013-02-28 14:47:32Z

0

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

edited Feb 28, 2013 at 14:47

answered Feb 28, 2013 at 9:08

AdamWhite

1,0342 gold badges10 silver badges11 bronze badges

6 Comments

Thomas Over a year ago

can u plzz give me bit full code. give me code for dataLines. thanks

AdamWhite Over a year ago

datalines in this case is simply a Hashset<T>

Thomas Over a year ago

how do u assign data to dataLines before inserting into db?

AdamWhite Over a year ago

I've modified the above for you

Thomas Over a year ago

can't we use List instead of HashSet ? if not please tell me the reason.

|

Collectives™ on Stack Overflow

Data insert in loop using sql bulkcopy class c#

2 Answers 2

5 Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related