'SqlBulkInsert Insert Mappping of IDentity column
Im working on an application that takes a large dataset from a file and then saves the content in sql-tables. We have had some performance issues with entity due to the large datasets so im giving SqlBulkCopy a go.
Ive arrived at a stage where I am not getting any exceptions but it also is not storing any data.
I have read that it usually is the mapping of the columns and my question is how to handle the Identity column. I am not inserting any value in it on any entry so do i need to include it in the data table and also mapp it in the bulkCopy object?
Se Code
DataTable Translations = new DataTable();
Translations.Columns.Add(new DataColumn("ID", typeof(Int32))); //Do i need to include this?
Translations.Columns.Add(new DataColumn("Identifier", typeof(string)));
Translations.Columns.Add(new DataColumn("FileID", typeof(Int32)));
DataTable Words = new DataTable();
Words.Columns.Add(new DataColumn("ID", typeof(Int32))); //Do i need to include this?
Words.Columns.Add(new DataColumn("Status", typeof(string)));
Words.Columns.Add(new DataColumn("Value", typeof(string)));
Words.Columns.Add(new DataColumn("Language", typeof(Int32)));
Words.Columns.Add(new DataColumn("Translation", typeof(Int32)));
foreach (Translation translation in translationEntries) //translationEntries is a list of entities
{
DataRow tEntry = Translations.NewRow();
//tEntry["ID"] = translation.ID; //Not inserting any value
tEntry["Identifier"] = translation.Identifier;
tEntry["FileID"] = translation.FileID;
Translations.Rows.Add(tEntry);
foreach (Word word in translation.Words)
{
DataRow wEntry = Words.NewRow();
//wEntry["ID"] = word.ID; //Not inserting any value
wEntry["Status"] = word.Status;
wEntry["Value"] = word.Value;
wEntry["Language"] = word.Language;
wEntry["Translation"] = word.Language;
Words.Rows.Add(wEntry);
}
}
SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
connection.Open();
SqlBulkCopy tObjBulk = new SqlBulkCopy(connection);
tObjBulk.DestinationTableName = "IDESTRANSLATE.dbo.Translation";
tObjBulk.ColumnMappings.Add("ID", "ID"); //Do i need to map this?
tObjBulk.ColumnMappings.Add("Identifier", "Identifier");
tObjBulk.ColumnMappings.Add("FileID", "FileID");
SqlBulkCopy wObjBulk = new SqlBulkCopy(connection);
wObjBulk.DestinationTableName = "IDESTRANSLATE.dbo.Word";
wObjBulk.ColumnMappings.Add("ID", "ID"); //Do i need to map this?
wObjBulk.ColumnMappings.Add("Status", "Status");
wObjBulk.ColumnMappings.Add("Value", "Value");
wObjBulk.ColumnMappings.Add("Language", "Language");
wObjBulk.ColumnMappings.Add("Translation", "Translation");
try
{
tObjBulk.WriteToServer(Translations); //no errors and nothing saved.
}
catch (Exception e)
{
Debug.WriteLine(e.Message + $"\n{e.StackTrace}");
throw;
}
tObjBulk.Close();
try
{
wObjBulk.WriteToServer(Words); //no errors and nothing saved.
}
catch (Exception e)
{
Debug.WriteLine(e.Message + $"\n{e.StackTrace}");
throw;
}
wObjBulk.Close();
connection.Close();
Sidenote.
The word table is a child table of Translation. Am i doing it correctly with two separate inserts or can you do both at once somehow? The data entities are ADO-entities.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|
