locked
How to use SqlBulkCopy with List object RRS feed

  • Question

  • User-1256377279 posted

    Hi Guys,

    I have below List object, how can we use to save as BulkInsert into SQL database using SQlBulkCopy.

     public class AbstractTcItem
        {
            public string RecordId { get; set; }
            public string Xob { get; set; }
            public string Structure { get; set; }
            public string Level { get; set; }
            public string Reference { get; set; }
            public int StatusID { get; set; }
            public string CataloguerId { get; set; }
            public DateTime CataloguedDate { get; set; }
            public string ModifiedBy { get; set; }
            public DateTime? ModifiedDate { get; set; }
            public int ItemTypeId { get; set; }
            public string ProcessedFile { get; set; }
    
        }

    Below is SQL Table

    ID uniqueidentifier
    RecordId char
    ItemTypeId int
    ItemXob xml
    StatusId int
    CataloguerId varchar
    CataloguedDate datetime2
    LastModifiedBy varchar
    LastModifiedOn datetime2
    Structure varchar
    Key nvarchar
    ProcessedFile nvarchar
    RecordFormat varchar

    Thanks,

    Shabbir

    Thursday, February 28, 2019 2:11 PM

Answers

  • User-893317190 posted

    Hi shabbir_215,

    Given you have a list of AbstractTcItem, you could also use JsonConverter to convert the list to Datatable.

    Below is my code.To be simple,I remove some of the columns

    List<AbstractTcItem> list = new List<AbstractTcItem>
                 {
                     new AbstractTcItem{RecordId="abs1",Xob="<root>123</root>",ModifiedDate=null,ModifiedBy="judy",CataloguedDate=DateTime.Now,ItemTypeId=1},
                      new AbstractTcItem{RecordId="abs2",Xob="<root>456</root>",ModifiedDate=DateTime.Now.AddDays(-10),ModifiedBy="nancy",CataloguedDate=DateTime.Now.AddDays(2),ItemTypeId=1},
                       new AbstractTcItem{RecordId="abs3",Xob="<root>789</root>",ModifiedDate=DateTime.Now.AddDays(-5),ModifiedBy="keke",CataloguedDate=DateTime.Now.AddDays(1),ItemTypeId=1},
                        new AbstractTcItem{RecordId="abs4",Xob="<root>456</root>",ModifiedDate=null,ModifiedBy="mili",CataloguedDate=DateTime.Now.AddDays(12),ItemTypeId=1},
                         new AbstractTcItem{RecordId="abs5",Xob="<root>234</root>",ModifiedDate=null,ModifiedBy="alikesi",CataloguedDate=DateTime.Now,ItemTypeId=1}
                 };
    // first serialize your list string json= JsonConvert.SerializeObject(new { Table = list });
    // then convert the json to dataset
    // you could refer to the link for more information https://www.newtonsoft.com/json/help/html/DeserializeDataSet.htm DataSet set= JsonConvert.DeserializeObject<DataSet>(json); string consString = ConfigurationManager.ConnectionStrings["EntityDb"].ConnectionString; using (SqlConnection con = new SqlConnection(consString)) { using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con)) { //Set the database table name sqlBulkCopy.DestinationTableName = "dbo.Test";
    // write mapping , the left is your AbstractTcItem's property , the right is the column name of your sql table sqlBulkCopy.ColumnMappings.Add("Xob", "ItemXob"); sqlBulkCopy.ColumnMappings.Add("RecordId", "RecordId"); sqlBulkCopy.ColumnMappings.Add("CataloguedDate", "CaloguedDate"); sqlBulkCopy.ColumnMappings.Add("ModifiedBy", "LastModifiedBy"); sqlBulkCopy.ColumnMappings.Add("ModifiedDate", "LastModifiedOn"); sqlBulkCopy.ColumnMappings.Add("ItemTypeId", "ItemTypeId"); con.Open(); sqlBulkCopy.WriteToServer(set.Tables[0]); con.Close(); } } } public class AbstractTcItem { public string RecordId { get; set; } public string Xob { get; set; } public DateTime CataloguedDate { get; set; } public string ModifiedBy { get; set; } public DateTime? ModifiedDate { get; set; } public int ItemTypeId { get; set; } }

    The result.

    Best regards,

    Ackerly Xu

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, March 1, 2019 6:38 AM

All replies

  • User-821857111 posted

    You can get your class to implement IDataReader: https://sachabarbs.wordpress.com/2015/02/14/bulk-insert-into-sql-from-c/, or more simply, transfer the data to an ADO.NET DataTable and call WriteToServer on that.

    https://www.mikesdotnetting.com/article/297/the-best-way-to-import-data-from-excel-to-sql-server-via-asp-net

    Thursday, February 28, 2019 3:33 PM
  • User-893317190 posted

    Hi shabbir_215,

    Given you have a list of AbstractTcItem, you could also use JsonConverter to convert the list to Datatable.

    Below is my code.To be simple,I remove some of the columns

    List<AbstractTcItem> list = new List<AbstractTcItem>
                 {
                     new AbstractTcItem{RecordId="abs1",Xob="<root>123</root>",ModifiedDate=null,ModifiedBy="judy",CataloguedDate=DateTime.Now,ItemTypeId=1},
                      new AbstractTcItem{RecordId="abs2",Xob="<root>456</root>",ModifiedDate=DateTime.Now.AddDays(-10),ModifiedBy="nancy",CataloguedDate=DateTime.Now.AddDays(2),ItemTypeId=1},
                       new AbstractTcItem{RecordId="abs3",Xob="<root>789</root>",ModifiedDate=DateTime.Now.AddDays(-5),ModifiedBy="keke",CataloguedDate=DateTime.Now.AddDays(1),ItemTypeId=1},
                        new AbstractTcItem{RecordId="abs4",Xob="<root>456</root>",ModifiedDate=null,ModifiedBy="mili",CataloguedDate=DateTime.Now.AddDays(12),ItemTypeId=1},
                         new AbstractTcItem{RecordId="abs5",Xob="<root>234</root>",ModifiedDate=null,ModifiedBy="alikesi",CataloguedDate=DateTime.Now,ItemTypeId=1}
                 };
    // first serialize your list string json= JsonConvert.SerializeObject(new { Table = list });
    // then convert the json to dataset
    // you could refer to the link for more information https://www.newtonsoft.com/json/help/html/DeserializeDataSet.htm DataSet set= JsonConvert.DeserializeObject<DataSet>(json); string consString = ConfigurationManager.ConnectionStrings["EntityDb"].ConnectionString; using (SqlConnection con = new SqlConnection(consString)) { using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con)) { //Set the database table name sqlBulkCopy.DestinationTableName = "dbo.Test";
    // write mapping , the left is your AbstractTcItem's property , the right is the column name of your sql table sqlBulkCopy.ColumnMappings.Add("Xob", "ItemXob"); sqlBulkCopy.ColumnMappings.Add("RecordId", "RecordId"); sqlBulkCopy.ColumnMappings.Add("CataloguedDate", "CaloguedDate"); sqlBulkCopy.ColumnMappings.Add("ModifiedBy", "LastModifiedBy"); sqlBulkCopy.ColumnMappings.Add("ModifiedDate", "LastModifiedOn"); sqlBulkCopy.ColumnMappings.Add("ItemTypeId", "ItemTypeId"); con.Open(); sqlBulkCopy.WriteToServer(set.Tables[0]); con.Close(); } } } public class AbstractTcItem { public string RecordId { get; set; } public string Xob { get; set; } public DateTime CataloguedDate { get; set; } public string ModifiedBy { get; set; } public DateTime? ModifiedDate { get; set; } public int ItemTypeId { get; set; } }

    The result.

    Best regards,

    Ackerly Xu

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, March 1, 2019 6:38 AM