Ho can I execute multiple stored procedure calls in one query?

Question

I'm rewriting the app which uses MS SQL database. There are few stored procedures which are being called. Let's say:

• InsertFile @fileId, @fileName
• DeleteFile @fileId

There is the case when you have to call these procedures. For example, request comes and to process this request you have to insert few files and delete few files:

InsertFile 100, file1
InsertFile 101, file2
DeleteFile 5
InsertFile 108, file8

Current app builds query as a string and then through SqlCommand executes it in one call.

In new app I'm using Entity Framework Core 6.0.5. I can call specific procedure in this way:

var fileIdParameter = new SqlParameter("@Id", fileId);
var fileNameParameter = new SqlParameter("@FileName", fileName);
await _db.Database.ExecuteSqlRawAsync("InsertFile @Id, @FileName", fileIdParameter, fileNameParameter);

But in case I described above there will be 5 separated calls to database. I want to do this in one database call.

How can I do this?

Answer 1

You can execute multiple statements in one SQL:

var sql = @"
   EXEC InsertFile @id1, @FileName1;
   EXEC InsertFile @id2, @FileName2;
   EXEC DeleteFile @id3;
   EXEC InsertFile @id4, @FileName4;
";

await _db.Database.ExecuteSqlRawAsync(sql, 
     new SqlParameter("@Id1", 100),
     new SqlParameter("@FileName1", "file1"),
     new SqlParameter("@Id2", 101),
     new SqlParameter("@FileName2", "file2"),
     new SqlParameter("@Id3", 5),
     new SqlParameter("@Id4", 108),
     new SqlParameter("@FileName4", "file8")
);