u/kaatarina_zed_talon

I have a database that currently contains 20 tables.

In my application, there are 10 classes that I should refactor, to make them more readable, for my future self and others who will read my code, by refactoring, I mean changing the code it self - the logic- and also changing the methods and classes names.

Let's only consider one specific case; changing the name of one class.

When my friend so the following code that I wrote:

/// &lt;summary&gt;
///  Abstracts the destination to which data is written, regardless of  whether the destination is a file, database, or any other kind of storage.
/// &lt;/summary&gt;
public interface IWriter
{
//...
}

public class SqlServerWriter : IWriter {//code.....}

he thought that the responsibility of the above class is to write data to any number of tables in the the Sql Server database, i.e., that it can use it to write data to only one table, two or more tables, or all of the tables.

Unfortunately, that was not what the class is actually responsible for; it is meant to write a specific data across all the tables in the database, it does not give the caller an option to write a generic data to a specific tables in the database.

He was mad at me and said that I should introduce an additional layer of abstraction:

/// &lt;summary&gt;
/// Abstracts the destination tables in the Sql Server; may write to all tables in the database or some of them;
/// &lt;/summary&gt;
public interface ISqlServerWriter:IWriter
{
}

/// &lt;summary&gt;
/// Intended to write some data to all the tables in the database.
/// &lt;/summary&gt;
public class AllTablesWriter : ISqlServerWriter {//code.....}

Do you consider that an overkill/overengineering ?

EDIT:

If you wonder why the class write data to all the tables in the database, it because it has the job of synchronizing the content of the database with external data source, specifically, an Excel file.

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&lt;ProductCategory&gt; ProductCategories { get; } = new List&lt;ProductCategory&gt;();  
    
    // Alot of other navigaton properties.....

}

public class Category:IParent{
  public string EnglishName {get;set;} // Category column in Excel
  public IEnumerable&lt;ProductCategory&gt; ProductCategories { get; } = new List&lt;ProductCategory&gt;();
  public IEnumerable&lt;SubCategory&gt; SubCategories { get; }=new List&lt;SubCategory&gt;();    


  // 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?

Would you introduce additional abstraction layer just to make the code more understandable ?

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