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

1
0
2/28/2013 8:53:20 AM

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge

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,
                              });
            }
        }
        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();
        }
0
2/28/2013 2:47:32 PM

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

  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

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

Related Questions

Bulkcopy to SQL table problem in C#

Using C#, .net 4, Visual Studio 2010, and SQL Server 2008....I currently have a table called ...NewPeriodPareto... contains the following columns:...PG nvarchar(50)...Part nvarchar(50)...Sales float...LostSales float...Pareto6 int...p6 image...Pareto5 int...p5 image...Pareto4 int...p4 image...Pareto3 int...p3 image...CurrentPareto int...pNew Image....
c# datagridview sql sqlbulkcopy
asked by lemunk

C# BulkCopy with SQL Server Compact Edition, is it possible?

You can use my SqlCeBulkCopy library, available via NuGet for SQL CE 4.0, or otherwise available here: ...https://sqlcebulkcopy.codeplex.com/
c# sqlbulkcopy sql-server-ce
asked by ErikEJ

BulkCopy with condition in C# for SQL Server

I'm trying to copy data from one server to another. The database I'm copying from is very large and updated daily and I'm interested in just copying the previous day....Using ...bcp... I can do this via the ...queryout... option. Is it possible to set this when using ...SqlBulkCopy...?
bcp c# sqlbulkcopy sql-server
asked by user1167650

Bulk insert into SQL table from CSV using C#

I'm pretty sure you have to escape the backslash's for the line and field terminators, since it looks like the string already converted them to the actual characters 0x10 and 0x09. I would think it has to be ...builder.Append(" rowterminator='\\n',"); builder.Append(" fieldterminator = '\\t'");
bulkinsert c# sql sqlbulkcopy sql-server
asked by Mike

Missing Data after insert into my end sql table using Sql Bulk Copy

I wrote an console application that extracts data from my access table then using sql bulk copy to an temp sql table then from the temp table loads it into main sql table. the application works as expected but it extracts 26624 rows from access table and when it does a WriteToServer on Bulk copy it only copies 24880 rows and hence when inserted int...
c# console-application datatable sqlbulkcopy sql-server-2008
asked by ExpertWannaBe

how to use sql Bulkcopy to insert datagridview rows in table

i want insert datagridview rows into sql server tables ... and now used this code to insert rows values from DataGridView in table :...private DataTable GetDataTableFromDGV(DataGridView dgv) { var dt = new DataTable(); foreach (DataGridViewColumn column in dgv.Columns) { if (column.Visible...
c# datagridview sql sqlbulkcopy
asked by ghasem deh

INSERT object or UPDATE if it already exists using BulkCopy (C#, SQL)

I'm trying to update a table with a ...List<T>.... I created an EnumExtension class and created a method which converts my Enumerable to a DataTable:...public static DataTable AsDataTable<T>(this IEnumerable<T> data)... ...And then I started creating a method which should use BulkCopy to insert or update my table, using the enumerable. Right now, i...
c# sqlbulkcopy sql-server
asked by Lucas Garcia

SQL Bulk Insert in C# not inserting values

Here is the sample for reading schema information from Excel (here we read the tablenames - sheet names with tables in them):...private IEnumerable<string> GetTablesFromExcel(string dataSource) { IEnumerable<string> tables; using (OleDbConnection con = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;" + string.Format("Data Source...
c# oledbdatareader sqlbulkcopy
asked by Cetin Basoz

Bulkcopy excel data to sql using c# application

I am trying to bulkcopy excel data to SQL but the data doesn't copy neither does the program throw an error. Help me point out what am doing wrong....Here is a snippet of my code...OpenFileDialog openFileDialog = new OpenFileDialog(); openFileDialog.Filter = "Excel Files|*.xls;*.xlsx"; openFileDialog.ShowDialog(); if (!st...
c# excel sqlbulkcopy
asked by Kolin_Lonce

Bulkcopy 100 million rows of data to Azure SQL Server using C#?

I have a local SQL Server 2017 database, and I need to copy two tables to an Azure SQL Server database; one table has over 100 million rows of data including a "geography" type column. How do I do that? ...I am right now running a bulk copy:...using (SqlConnection streamsConnection = new SqlConnection(streamsConnectionString)) { streamsConnecti...
c# sqlbulkcopy sql-server-2017
asked by Johan Grobler

Fastest Entity Framework Extensions

Bulk Insert
Bulk Delete
Bulk Update
Bulk Merge
View more on Stack Overflow

Prime Library

Performance

Expression Evaluator

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