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