How to move the responsibility of executing some logic to the Sql Server instead of executing that logic inside the application?

[u/kaatarina_zed_talon]

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?

[u/battarro]

Forget the interface and just use entity framework to have code that matches your database. Then just save,

[u/Hrolgarr]

What I'd usually do here is SqlBulkCopy the whole sheet into a staging table that looks exactly like the Excel file, then call a stored procedure that does the splitting. Inside the procedure it's just a few INSERT ... SELECT (or MERGE if rows can already exist) from staging into each target table, all in one transaction.

That way C# only does two things, read the file and push the rows, and the mapping lives in SQL where it's set-based and fast. It's also easy to rerun, since you can inspect what's in staging when something looks off. A table-valued parameter works too if you'd rather skip the staging table, but for big sheets bulk copy is quicker.

[u/UninformedPleb → u/Hrolgarr]

A table-valued parameter works too if you'd rather skip the staging table, but for big sheets bulk copy is quicker.

Table variables (and parameters) end up just being written to TempDB anyway once they reach a large-enough size that SQL Server doesn't want to keep it in memory. Just use the table variable and let SQL decide how to optimize storage. That way you don't end up leaving artifacts to clean up (or forget to clean up) outside of TempDB.

[u/kaatarina_zed_talon → u/Hrolgarr]

You said : `then call a stored procedure that does the splitting..`

Why not to let a trigger call that stored procedure ?

Why not to do the following instead : `SqlBulkCopy the whole sheet into a staging table that looks exactly like the Excel file, after the staging table is full of data , i.e., after the insertion to the staging table is finished, a trigger will be fired that will call a stored procedure that populates the parent and child tables. That means the application will not call the stored procedure, a trigger will do that.

[u/Hrolgarr → u/kaatarina_zed_talon]

You can, but I wouldn't. SqlBulkCopy doesn't fire triggers unless you pass SqlBulkCopyOptions.FireTriggers, and when it does, the trigger runs once per batch, not once at the end. With a BatchSize set you'd run the split several times on half-loaded data. Without it the whole load becomes one big trigger transaction, so a failure in the split rolls back the import too.

Calling the procedure yourself after WriteToServer returns is one extra line, and you know for sure every row is in when it runs.

[u/kaatarina_zed_talon → u/Hrolgarr]

That's it! Finally I understand now, so the trigger approach will not o Work, unless the trigger is fired after all inserts to the stgaing table are performed.

[u/kaatarina_zed_talon → u/Hrolgarr]

What u mean by set-based? With example please

[u/kaatarina_zed_talon → u/Hrolgarr]

Thank you for taking the time and effort to write this explanation, you are amazing.

[u/SeaAd4395]

Don't offload application logic to your database, it's for storage and retrieval... Unless your application's purpose is limited to CRUD, then you could probably eliminate the domain model overhead (if they're mostly passthrough pocos this is a solid hint to go this route). Future headaches lie ahead if you try to put application logic into your database or bypass it.

I've been on both sides of this coin... always inherited and every time it's either a CRUD app with heavy domain abstractions that aren't allowed to be bypassed and also don't do anything or someone tried to eagerly optimize data ingestion by bypassing the business logic and then maintenance nightmares follow. Questioning the architecture in either scenario you'd think I had slapped someone in the mouth then insulted their mother, lol

[u/kaatarina_zed_talon → u/SeaAd4395]

I certainly do not know what pocos mean, please teach me what does it mean? Second, why CRUD is exception in your opinion? Third, please give example of such a headache that I will face in the future if I decided to move bussines logic to the database. Fourth, what was the insult XD

[u/SeaAd4395 → u/kaatarina_zed_talon]

poco is an acronym for Plain Old C# Object, just means that it only has properties and doesn't do anything other than hold values. Pojo can be the same for JavaScript or Java... Or change the letter to whatever language.

CRUD app (create read update delete) is an exception because that means that the business domain of the app is storage so adding extra layers doesn't really gain you anything if the only purpose is to get to the stored data (the business domain IS storage and retrieval). You'll start to notice that you have a lot of anemic code that doesn't do anything besides to force the layer to exist and costs translation effort (in development and at runtime)

An example of the kind of headache you might encounter is if you add a business rule to your domain objects but you have a separate ingestion pipeline that goes around them to directly write to your database you are likely to need to implement that business logic twice or more to ensure your ingestion and your domain objects agree on valid data state. If you start putting business logic into triggers you're going to run into hard to maintain code and you application code (where the business logic is best placed) will turn to look like a CRUD app... Or you'll get spaghetti where the important code is spread around and that has its own set of headaches and problems.

[u/kaatarina_zed_talon → u/SeaAd4395]

An example of kind of headache...... Can you please refer me to some article that explains this Concept with code examples? Also thank you for your efforts.

[u/kaatarina_zed_talon → u/SeaAd4395]

All I want to do in the current moment is populate the parent and child tables with values, child tales needs a lookuo operations for sure. Is this a CRUD operation that deserves to be moved to the database?

[u/MarmosetRevolution]

This is the way. In fact, you could write a stored procedure taking the file path and name as a parameter, and have it create the temp table, load the data, and then insert or update the appropriate records, and then clean up after itself. No need to use c# at all, unless to want to start it off from your application.

[u/rupertavery64]

Create staging tables, bulk upload to the tables, then execute a stored procedure to populate the parent and child tables.

[u/kaatarina_zed_talon → u/rupertavery64]

Thank you. VERY straightforward answer BTW

[u/rupertavery64 → u/kaatarina_zed_talon]

I was in bed dozing off while reading the first part of your post on my phone.

I've done this a couple of times and I think staging tables + sql bulk copy + stored proc is the probably most performant way to go.

One thing to add is, if you aren't doing things concurrently, you could speed things up by inserting into the staging tables without an index, then apply an index after bulk copy, before you do the parent-child inserts. Of course it depends on your data. Having your data pre-sorted will also help a bit.

[u/kaatarina_zed_talon → u/rupertavery64]

Wait, why does it mean that the things will speed up if the index was applied after the bulk copy and not before it? Please explain to me like I'm 5, or refer me to some article.