Stored procedure with EF6 Code first - best practice?

Question

In my program I have to import data from a remote sqlserver database. I am using ASP.NET MVC 5 and EF6 Code First (I'm new with EF and MVC 5).

First, I copy data from a remote view to a table in the local database. For this part I use this code in the action method (names are in italian):

using (var source = new TimeWebDBContext())
{
 using (var target = new GESTPrefContext())
  {
   // 1 - Truncate table AnagraficaTimeWeb is exists
   target.Database.ExecuteSqlCommand("IF EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='AnagraficaTimeWeb') TRUNCATE TABLE AnagraficaTimeWeb");
    
   // 2 - Copy from remote view  to  AnagraficaTimeWeb table
   var dati_importati = from i in source.VW_PREFNO_ANAGRAFICORUOLO
        select new AnagraficaTimeWeb()
        {
         Matricola = i.MATRICOLA,
         Cognome = i.COGNOME,
         Nome = i.NOME,
         Sesso = i.SESSO,
         Email = i.EMAIL,
         IdRuolo = i.IDRUOLO,
         Ruolo = i.RUOLO,
         DataInizio = i.DATAINIZIO,
         DataFine = i.DATAFINE,
         DataFineRapporto = i.DATALICENZ,
         DataUltimaImportazione = DateTime.Now
        };
        target.DatiAnagraficaTimeWeb.AddRange(dati_importati.ToList());
        target.SaveChanges();
   }
}

The view returns a list of employees with their role. Roles have to be imported in a distinct local table called PROFILO, while employees data are saved in the IMPIEGATO table.

The remaining part of the import process consists of : a) insert new data in the PROFILO table (data already saved are ignored) b) update of employee data already present in the local IMPIEGATO table (name, email,etc. are overwritten) c) insert new empoyee not yet in the IMPIEGATO table.

since I’m new to EF6 I thought I’d use SQL code . In my opinion the possible solutions are :

1. execute SQL code directly in the actionmethod with db.Database.ExecuteSqlCommand This is the code i write:

code for point a)

StringBuilder sql = new StringBuilder();
sql.AppendLine("INSERT INTO PROFILO (IdTimeWeb, Descrizione, Ordinamento, Stato, Datainserimento)");
sql.AppendLine(" SELECT DISTINCT IdRuolo, Ruolo, 1," + ((int)EnumStato.Abilitato) + ",'" + DateTime.Now.ToShortDateString()+"'");
sql.AppendLine(" FROM AnagraficaTimeWeb i");
sql.AppendLine(" WHERE NOT EXISTS");
sql.AppendLine("(SELECT 1 FROM PROFILO p WHERE p.Descrizione = i.Ruolo)");
target.Database.ExecuteSqlCommand(sql.ToString());

code for point b)

sql.Clear();
sql.Append("UPDATE i " + Environment.NewLine);
sql.Append(" SET i.Cognome = a.Cognome" + Environment.NewLine);
sql.Append(" , i.Nome = a.Nome" + Environment.NewLine);
sql.Append(" , i.Sesso = a.Sesso" + Environment.NewLine);
sql.Append(" ,i.Email = a.Email" + Environment.NewLine);
sql.Append(" ,i.DataModifica = '" + DateTime.Now.ToShortDateString() +"'"+ Environment.NewLine);
sql.Append(" FROM Impiegato i " + Environment.NewLine);
sql.Append(" JOIN AnagraficaTimeWeb a on i.Matricola=a.Matricola " + Environment.NewLine);
sql.Append(" WHERE i.Stato =" + ((int)EnumStato.Abilitato)  + Environment.NewLine);
target.Database.ExecuteSqlCommand(sql.ToString());
    

code for point c)

sql.Clear();
sql.Append("INSERT INTO IMPIEGATO(Cognome, Nome, Matricola, Sesso, Email, Stato, DataInserimento) " + Environment.NewLine);
sql.Append("SELECT a.Cognome" + Environment.NewLine);
sql.Append(", a.Nome" + Environment.NewLine);
sql.Append(", a.Matricola" + Environment.NewLine);
sql.Append(", a.Sesso" + Environment.NewLine);
sql.Append(", a.Email" + Environment.NewLine);
sql.Append("," + ((int)EnumStato.Abilitato )+ Environment.NewLine);
sql.Append(",'"+ DateTime.Now.ToShortDateString() +"'" + Environment.NewLine);
sql.Append(" FROM AnagraficaTimeWeb a " + Environment.NewLine);
sql.Append(" LEFT OUTER JOIN IMPIEGATO on a.Matricola = Impiegato.Matricola " + Environment.NewLine);
sql.Append("  WHERE Impiegato.Matricola is null" + Environment.NewLine);

target.Database.ExecuteSqlCommand(sql.ToString());      
                    
                

2. create a storedprocedure to call in the actionmetghod. in this case how to create the storedprocedure?

2.a) in the Up method of a migration ?

2.b) or running the storedprocedure creation script directly in the database (after it was first created ) and then run the stored from the action method?

Answer 1

You can create stored procedure directly in db and write all three remaining part of your process (point a,b,c) in one SP (stored procedure). SP is stored as object file in sql so it's fast and sql server don't spend time in making execution plan and other extra things.

In order to create sp, you need to more familiar with sql statements and it's control structures etc. It will feel like learning new language. You can start with below link

https://learn.microsoft.com/en-us/sql/relational-databases/stored-procedures/stored-procedures-database-engine?view=sql-server-ver16

To save that stored procedure as migration script you can create a blank migration using

add-migration 'SPName' -IgnoreChanges

Then add your sp

public partial class SPName: DbMigration
{
    public override void Up()
    {
        Sql(@"Create Stored Procedure script");

    }

    public override void Down()
    {
        Sql(@"Drop Stored Procedure script")
    }
} 

don't forget to change migration script every time you modify/alter sp. You can also make migration script of modified sp.

To execute your sp you can refer the below code snippet. SqlPatameter helps you to make query clean,you can avoid sql injection.

List<SqlParameter> sqlParms = new List<SqlParameter>
  {
    new SqlParameter { ParameterName = "@Id", Value = employee.EmployeeID },
    new SqlParameter { ParameterName = "@FirstName ", Value = employee.FirstName },
    new SqlParameter { ParameterName = "@LastName", Value = employee.LastName}
};
db.Database.ExecuteSqlRaw("EXEC dbo.spName @Id, @FirstName, @LastName" sqlParms.ToArray());