Bulk Inserts Using Dapper (edit)

• Đọc tệp Excel bằng thư viện Open XML
• Đưa vào List<Dto>
• Build câu Insert bằng StringBuilder
• Dùng Dapper để thực hiện Bulk Insert

Read Excel using Open XML

https://alberton.info/postgresql_meta_info.html (PostgreSQL)

https://stackoverflow.com/questions/10689779/bulk-inserts-taking-longer-than-expected-using-dapper/ (Bulk Inserts)

https://www.thecodebuzz.com/read-excel-file-in-dotnet-core-2-1/ (Read/Write excel file .NET Core using OpemXML SDK)

https://www.thecodebuzz.com/read-excel-as-json-using-open-xml-sdk/ (OpenXML SDK)

https://www.aspsnippets.com/Articles/Read-and-Import-Excel-data-to-DataTable-using-OpenXml-in-ASPNet-with-C-and-VBNet.aspx (OpenXML SDK)

https://dzone.com/articles/import-and-export-excel-file-in-asp-net-core-31-ra (Import & Export)

• ASP.NET Core 2.1
• EF Core 2.1
• PostgreSQL (Install-Package Npgsql)
• Dapper (Install-Package Dapper)
• DocumentFormat.OpenXml or OpenXML (Install-Package Open-XML-SDK | Install-Package DocumentFormat.OpenXml)
• Upload files
• Read Excel files
• Import and Export to CSV files

Open XML

https://www.c-sharpcorner.com/article/creating-excel-file-using-openxml/

https://bettersolutions.com/csharp/xml/open-xml-sdk.htm

https://www.codingame.com/playgrounds/11047/edit-word-documents-using-openxml-and-c-without-automationinterop

Code Samples

https://www.michalbialecki.com/2019/05/21/bulk-insert-in-dapper/

https://github.com/mikuam/Blog/tree/master/ServiceBusExamples/MichalBialecki.com.NetCore.Web/Users

[HttpGet]
[Route("[action]")]
public ActionResult ImportData()
{
 var listUsers = new List<Users>();
 Users users;

 var excelFilePath = System.IO.Path.Combine(System.Environment.GetFolderPath(Environment.SpecialFolder.Desktop), "Sample.xlsx");

 if (!System.IO.File.Exists(excelFilePath)) return Ok("File excel not found.");

 try
 {
 // Lets open the existing excel file and read through its content. Open the excel using openxml sdk
 using (SpreadsheetDocument doc = SpreadsheetDocument.Open(excelFilePath, false))
 {
 // Create the object for workbook part
 WorkbookPart workbookPart = doc.WorkbookPart;
 Sheets thesheetcollection = workbookPart.Workbook.GetFirstChild<Sheets>();

 // Using for each loop to get the sheet from the sheetcollection
 foreach (Sheet thesheet in thesheetcollection)
 {
 // Statement to get the worksheet object by using the sheet id
 Worksheet theWorksheet = ((WorksheetPart)workbookPart.GetPartById(thesheet.Id)).Worksheet;

 SheetData thesheetdata = (SheetData)theWorksheet.GetFirstChild<SheetData>();

 foreach (Row thecurrentrow in thesheetdata)
 {
 if (thecurrentrow.RowIndex == 1)
 {
 continue;
 }

 string[] arr = new string[3];
 var idx = 0;
 foreach (Cell thecurrentcell in thecurrentrow)
 {
 // Statement to take the integer value
 string currentcellvalue = string.Empty;
 if (thecurrentcell.DataType != null)
 {
 if (thecurrentcell.DataType == CellValues.SharedString)
 {
 int id;
 if (Int32.TryParse(thecurrentcell.InnerText, out id))
 {
 SharedStringItem item = workbookPart.SharedStringTablePart.SharedStringTable.Elements<SharedStringItem>().ElementAt(id);
 if (item.Text != null)
 {
 currentcellvalue = item.Text.Text;
 }
 else if (item.InnerText != null)
 {
 currentcellvalue = item.InnerText;
 }
 else if (item.InnerXml != null)
 {
 currentcellvalue = item.InnerXml;
 }
 }
 }
 }
 else
 {
 currentcellvalue = thecurrentcell.InnerText;
 }
 arr[idx++] = currentcellvalue;
 if (idx == 3)
 {
 users = new Users();
 var canInsert = true;
 try
 {
 users.ColumnIndex = NumberUtil.TryGetInt32(arr[0]);
 users.Name = arr[1];
 users.Salary = arr[2];
 }
 catch (Exception ex)
 {
 canInsert = false;
 Debug.WriteLine(ex.ToString());
 }

 if (canInsert)
 {
 listUsers.Add(users);
 users = null;
 }

 idx = 0;
 }
 }
 }
 }
 }
 }
 catch (Exception ex)
 {
 Debug.WriteLine(ex.ToString());
 }

 var allItems = (from x in listUsers
 where x.ColumnIndex > 0
 select new
 {
 ColumnIndex = x.ColumnIndex,
 Name = x.Name,
 Salary = x.Salary
 }).ToList();

 using (var connection = new NpgsqlConnection(_dbContext.Connection.ConnectionString))
 {
 connection.Open();
 using (var transaction = connection.BeginTransaction())
 {
 try
 {
 connection.Execute("INSERT INTO public.\"Users\" (\"ColumnIndex\", \"Name\", \"Salary\") VALUES (@ColumnIndex, @Name, @Salary)", allItems, transaction);

 transaction.Commit();
 }
 catch (Exception ex)
 {
 Debug.WriteLine(ex.ToString());
 transaction.Rollback();
 throw;
 }
 finally
 {
 connection.Close();
 }
 }
 }

 var allUsers = _dbContext.Users.ToList();

 return Ok(allUsers.Count);
}

Migration Data from Database-Dev to Database-Test

Giả sử bạn phải chuyển hết dữ liệu từ DB Dev sang DB Test hoặc sang DB Staging

PostgreSQL, Dapper, NpgSql, CsvHelper, Open-XML-SDK with .NET Framework 4.7.2

using Dapper;
using Npgsql;
using System;
using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using System.Text;

namespace ConsoleApp1
{
 class Program
 {
 static string mConnectionStringSource = "server=localhost:5432;database=test;uid=postgres;pwd=postgres;";
 static string mSchemaSource = "public";

 static string mConnectionStringDestination = "server=localhost:5432;database=test;uid=postgres;pwd=postgres;";
 static string mSchemaDestination = "public";



 static void Main(string[] args)
 {
 Dictionary<string, List<object>> keyValuePairs = new Dictionary<string, List<object>>();

 Console.WriteLine("Hello World!");

 //Step 1: Lấy danh sách tên bảng + tên các column (tên các column join bằng dấu chẩm phẩy)
 var listTableDto = new List<TableDto>()
 {
 new TableDto("Users", "Id,Name,Salary,DoB,Gender,IsActive,CreatedBy,CreatedDate,ModifiedBy,ModifiedDate"),
 new TableDto("Roles", "Id,Name"),
 };

 //Step 2: Lấy dữ liệu từ DB Source (mConnectionStringSource)
 using (var connection = new NpgsqlConnection(mConnectionStringSource))
 {
 connection.Open();
 for (int i = 0, n = listTableDto.Count; i < n; i++)
 {
 var tableDto = listTableDto[i];
 if (tableDto == null || string.IsNullOrWhiteSpace(tableDto.TableName)) continue;
 var tableName = listTableDto[i].TableName;
 var list = connection.Query<object>($"SELECT * FROM \"{mSchemaSource}\".\"{tableName}\"").ToList();
 keyValuePairs.Add(tableName, list);
 }
 }

 var sb = new StringBuilder();
 //Step 3: Build câu insert hàng loạt (Bulk Insert)

 using (var connection = new NpgsqlConnection(mConnectionStringDestination))
 {
 connection.Open();
 using (var transaction = connection.BeginTransaction())
 {
 for (int i = 0, n = listTableDto.Count; i < n; i++)
 {
 var tableDto = listTableDto[i];
 if (tableDto == null || string.IsNullOrWhiteSpace(tableDto.TableName)) continue;
 var tableName = listTableDto[i].TableName;
 var listColumnNames = listTableDto[i].ListColumnNames;
 var sqlInsert = $"INSERT INTO \"{mSchemaDestination}\".\"{tableName}\" ({BuildColumnInsert(listColumnNames)}) VALUES ({BuildParamInsert(listColumnNames)})";
 try
 {
 connection.Execute(sqlInsert, keyValuePairs.ElementAt(i).Value, transaction);

 transaction.Commit();
 }
 catch (Exception ex)
 {
 Debug.WriteLine(ex.ToString());
 transaction.Rollback();
 throw;
 }
 finally
 {
 connection.Close();
 }
 }
 }
 }
 }

 /// <summary>
 /// Input: Id,Name,DoB,Desc
 /// Output: \"Id\", \"Name\", \"DoB\", \"Desc\"
 /// </summary>
 /// <param name="listColumnNames"></param>
 /// <returns></returns>
 private static string BuildColumnInsert(string listColumnNames)
 {
 var sb = new StringBuilder();
 sb.Append("\"");
 var arr = listColumnNames.Split(new char[] { ' ', ',' }, StringSplitOptions.RemoveEmptyEntries).Select(x => x.Trim()).ToList();
 sb.Append(string.Join("\", \"", arr));
 sb.Append("\"");
 var s = sb.ToString();
 return s;
 }

 /// <summary>
 /// Input: Id,Name,DoB,Desc
 /// Output: @Id, @Name, @DoB, @Desc
 /// </summary>
 /// <param name="table"></param>
 /// <returns></returns>
 private static string BuildParamInsert(string listColumnNames)
 {
 var sb = new StringBuilder();
 sb.Append("@");
 var arr = listColumnNames.Split(new char[] { ' ', ',' }, StringSplitOptions.RemoveEmptyEntries).Select(x => x.Trim()).ToList();
 sb.Append(string.Join(", @", arr));
 var s = sb.ToString();
 return s;
 }

 }

 class Users
 {
 public int Id { get; set; }
 public string Name { get; set; }
 public decimal? Salary { get; set; }
 public DateTime? DoB { get; set; }
 public int? Gender { get; set; }
 public bool IsActive { get; set; }
 public int CreatedBy { get; set; }
 public DateTime CreatedDate { get; set; }
 public int? ModifiedBy { get; set; }
 public DateTime? ModifiedDate { get; set; }
 }

 class TableDto
 {
 public string TableName { get; set; }
 public string ListColumnNames { get; set; }
 public TableDto()
 {

 }

 public TableDto(string tableName, string listColumnNames)
 {
 TableName = tableName;
 ListColumnNames = listColumnNames;
 }
 }
}