'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