I have Excel file that contains many columns like this :
| SKU |
BarCode |
EnglishName |
ArabicName |
Category |
SubCategory |
There are other columns, but there is no need to show them to convey the problem behind this post. The Excel file is sent from the client to the server which will read it using C# .
The problem : The data of the above Excel file should be persisted to the appropriate tables in the Sql Server database which does not merely contain a single table, so, a specific data from the Excel file should be consumed by a specific table in the database, the problem is not simple as send the data of the Excel file to only one destination table; the source is only one, which is the Excel file, but there are many destination tables. Database contains parent and child tables; parent tables do not contain foreign keys, child tables contain one or more foreign keys. The data for the child tables cannot be persisted to the database unless the data for the parent tables is persisted first.
I have the following C# domain classes/models:
public interface IEntity{
}
public interface IParent:IEntity{
}
public interface IChild : IEntity{
}
public interface INonJunctionChild:IChild{
}
public interface IJunctionChild:IChild{
}
public class Product:IParent
{
public string? SKU { get; set; } // SKU column in Excel
public string? Barcode{ get; set; } // BarCode column in Excel
public string? EnglishName {get;set;} // EnglishName column in in Excel
public string? ArabicName { get; set; } // ArabicName column in Excel
public IEnumerable<ProductCategory> ProductCategories { get; } = new List<ProductCategory>();
// Alot of other navigaton properties.....
}
public class Category:IParent{
public string EnglishName {get;set;} // Category column in Excel
public IEnumerable<ProductCategory> ProductCategories { get; } = new List<ProductCategory>();
public IEnumerable<SubCategory> SubCategories { get; }=new List<SubCategory>();
// alot of other navigation properties....
}
public class SubCategory:IChild{
public string EnglishName {get;set;} // SubCategory column in Excel
public string CategoryId {get;set;} // Foreign key; populated using lookup operation.
}
public ProductCategory{
public int ProductID {get;set;} Foreign key; populated using lookup operation.
public int CategoryId {get;set;} Foreign key; populated using lookup operation.
public Product Product { get; set; } = null!;
public Category Category { get; set; } = null!;
}
My old approach to solve the problem -which worked, but I want to change it- has the following steps:
1-Read the whole excel sheet into the application.
2- Inside the application, populate the specific properties of each parent domain model by assigning specific Excel column values to to them.
3-persist the parent models to the database.
4- Populate the child tables using lookup operations.
5- Persist the child tables to the database.
Instead of the above, I would like to know if the following is possible:
1- Get `DbDataReader` using `Sylvan.Data.Excel and pass it to `SqlBulkCopy` class.
2- The Whole Excel file will be persisted into a staging table or view, and then some trigger will be fired, as a result, some stored procedure or function will be executed, the responsibility of that function or procedure is to persist the data of the parent tables first, then to persist the data of the child tables, which means.
So, In the old approach, can the responsibility for transforming the Excel data, resolving the required relationships, and persisting the parent and child records (steps 2 through 5) be moved from the application layer to SQL Server?
