Using SQLBulkCopy to Insert into Related Tables

Question

Hello,

I am using SQL Bulk copy to read data from Excel to SQL DB. In the Database, I have two tables created by code first EF Core 6 into which I need to insert this data from Excel. How can I use Bulk Copy with two related tables?

Sample excel:

Here are the entities:

public partial class GameBank
    {
        public int GameBankId { get; set; }
        public Guid ReferenceId { get; set; }
        public string? ProductCode { get; set; }
        public int Quantity { get; set; }
        public string? Version { get; set; }
        public DateTime? RequestDateTime { get; set; }
        public int? CustomerId { get; set; }
        public string? Password { get; set; }
        public DateTime? ResponseDateTime { get; set; }
        public string? InitiationResultCode { get; set; }
        public string? CompanyToken { get; set; }
        public int Used { get; set; }
        public string? ProductDescription { get; set; }
        public string? Currency { get; set; }
        public double UnitPrice { get; set; }
        public double TotalPrice { get; set; }
        public string? ApplicationCode { get; set; }
        public double EstimateUnitPrice { get; set; }
        public string? ValidatedToken { get; set; }
        public string? Signature { get; set; }
        public int Status { get; set; }
        public string? ClientTrxRef { get; set; }
    }
public partial class GameBankPin
    {
        public int Id { get; set; }
        public int GameBankId { get; set; }
        public DateTime? ExpiryDate { get; set; }
        public string? Serial { get; set; }
        public string? Pin { get; set; }
    }

try
        {
            var path = Path.Combine(env.ContentRootPath,
                env.EnvironmentName, "unsafe_uploads_stocks",
                untrustedFileName);

            await using FileStream fs = new(path, FileMode.Create);
            await file.CopyToAsync(fs);

            logger.LogInformation("{untrustedFileName} saved at {Path}",
                untrustedFileName, path);

            var sqlConnectionString = configuration["ConnectionStrings:DefaultConnection"];

            // Get the datatable from procedure on Utility.cs page
            var datapush = Utility.Utility.ImportStocksToDataTable(path, "Sheet1");

            // open connection to sql and use bulk copy to write excelData to my table
            await using var destinationConnection = new SqlConnection(sqlConnectionString);
            destinationConnection.Open();
            using var bulkCopy = new SqlBulkCopy(destinationConnection);
            bulkCopy.DestinationTableName = "GameBanks";
            bulkCopy.ColumnMappings.Add("productDescription", "productDescription");
            bulkCopy.ColumnMappings.Add("productCode", "productCode");
            bulkCopy.ColumnMappings.Add("unitPrice", "unitPrice");
            bulkCopy.ColumnMappings.Add("quantity", "quantity");
            bulkCopy.ColumnMappings.Add("version", "version");
            bulkCopy.ColumnMappings.Add("currency", "currency");
            bulkCopy.ColumnMappings.Add("totalPrice", "totalPrice");
            bulkCopy.ColumnMappings.Add("status", "status");
            bulkCopy.ColumnMappings.Add("used", "used");

            await bulkCopy.WriteToServerAsync(datapush);
        }
        catch (Exception ex)
        {
            logger.LogError("{untrustedFileName} error on upload (Err: 3): {Message}",
                untrustedFileName, ex.Message);

        }
    }


 public static DataTable ImportStocksToDataTable(string filePath, string sheetName)
        {
            var dt = new DataTable();
            var fi = new FileInfo(filePath);
            // Check if the file exists
            if (!fi.Exists)
                throw new Exception("File " + filePath + " Does Not Exists");

            // If you use EPPlus in a noncommercial context
            // according to the Polyform Noncommercial license:
            ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
            using var xlPackage = new ExcelPackage(fi);
            // get the first worksheet in the workbook
            var worksheet = xlPackage.Workbook.Worksheets[sheetName];

            dt = worksheet.Cells[1, 1, worksheet.Dimension.End.Row, worksheet.Dimension.End.Column].ToDataTable(c =>
            {

                c.FirstRowIsColumnNames = true;
                //c.Mappings.Add(5, "TransactionDateTime", typeof(DateTime));


            });

            return dt;
        }

Answer 1

BulkCopy only works with one table at a time. You will need to make two passes or use two connections with one pass of the data.

Answer 2

@Cenk, Welcome to Microsoft Q&A, as others suggested, we could not use sqlbulk copy with two tables at one time.

I recommend that you use the ef to do it, like the following code:

First Method:

foreach (DataRow item in dt.Rows)
{
    GameBank bank = new GameBank();
    bank.ProductDescription = item["ProductDescription"].ToString(); 
    bank.ProductCode = item["ProductCode"].ToString();
    bank.Currency= item["Currency"].ToString();
    bank.UnitPrice = Convert.ToDouble(item["UnityPrice"]);
    bank.Currency = item["Currency"].ToString();
    bank.Quantity= Convert.ToInt32(item["Quantity"]);
    bank.Version = item["Version"].ToString(); 
    bank.TotalPrice= Convert.ToDouble(item["TotalPrice"]);
    bank.Used = Convert.ToInt32(item["Used"]);
    bank.Status= Convert.ToInt32(item["Status"]);
    GameBankPin pin = new GameBankPin();
    pin.Pin = item["Pin"].ToString();
    pin.ExpiryDate= Convert.ToDateTime(item["ExpiryDate"]);
    pin.Serial = item["Serial"].ToString();
    context.banks.Add(bank);
    context.pins.Add (pin);

}
context.SaveChanges();

Second method:

If you want to consider the performance, I still recommend that you used sqlbulkcopy to solve the problem.

var listcols = new List<string>();
foreach (DataColumn item in dt.Columns)
{
    listcols.Add(item.ColumnName);
}
DataView view = new DataView(dt);
DataTable table1 = view.ToTable(false, listcols.Take(9).ToArray());
DataTable table2 = view.ToTable(false, listcols.Skip(9).Take(3).ToArray());
MyContext context=new MyContext();
DataTable dt = GameBank.ImportStocksToDataTable("path", "Sheet1");
var listcols = new List<string>();
foreach (DataColumn item in dt.Columns)
{
    Console.WriteLine(item.ColumnName);
    
    listcols.Add(item.ColumnName);
}
DataView view = new DataView(dt);
DataTable table1 = view.ToTable(false, listcols.Take(9).ToArray());
DataTable table2 = view.ToTable(false, listcols.Skip(9).Take(3).ToArray());
var str = context.Database.GetDbConnection().ConnectionString;
SqlConnection connection = new SqlConnection(str);
connection.Open();
SqlBulkCopy copy = new SqlBulkCopy(connection);
copy.DestinationTableName = "banks";
copy.ColumnMappings.Add("ProductDescription", "ProductDescription");
copy.ColumnMappings.Add("ProductCode", "ProductCode");
copy.ColumnMappings.Add("UnitPrice", "UnitPrice");
copy.ColumnMappings.Add("Currency", "Currency");
copy.ColumnMappings.Add("Quantity", "Quantity");
copy.ColumnMappings.Add("Version", "Version");
copy.ColumnMappings.Add("TotalPrice", "TotalPrice");
copy.ColumnMappings.Add("Used", "Used");
copy.ColumnMappings.Add("Status", "Status");

copy.WriteToServer(table1);
var listofbankid = from m in context.banks.ToList()
                   from t in table1.AsEnumerable()
                   where m.ProductDescription == t["ProductDescription"].ToString()
                   select m.GameBankId;
DataColumn newColumn = new DataColumn("BankId", typeof(int));
table2.Columns.Add(newColumn);
for (int i = 0; i < listofbankid.Count(); i++)
{
    table2.Rows[i]["BankId"] = listofbankid.ElementAt(i);
}
SqlBulkCopy copy1 = new SqlBulkCopy(connection);
copy1.DestinationTableName = "pins";
copy1.ColumnMappings.Add("TotalPrice", "TotalPrice");
copy1.ColumnMappings.Add("Status", "Status");
copy1.ColumnMappings.Add("Used", "Used");
copy1.WriteToServer(table2);
connection.Close();


          
          

Hope my solution could help you.

Best Regards,

Jack

---

If the answer is the right solution, please click "Accept Answer" and upvote it.If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our [documentation][3] to enable e-mail notifications if you want to receive the related email notification for this thread.