How to execute multiple raw SQL commands in a single transaction?

asp.net-core-mvc entity-framework-core sql

Question

I need to insert several values into several tables using .net core app.

First table [AI_Table] contains keys and second table [AI_TableLanguage] contains names in mutiple languages. [AI_Table] has IDENTITY primary key, so DB generates it. I need to get that key from [AI_Table] and insert it into [AI_TableLanguage].

Firstly, I am not sure get max key is the right approach, and secondly, what if two or more users approach this table at the same time? Will it deadlock, or what? So my question is does or if not, how can this http post action result act as one single transaction, so noone can interrupt these inserts?

General idea:

  • insert into [AI_Table] (DB generates a PK).
  • get that PK and insert it into [AI_TableLanguage] three times.

I read few other questions that got answered and also read the Microsoft documentation. I couldn't find any answer that uses RAW SQL. Also, I couldn't find if the whole IActionResult acts as single transaction.

[HttpPost, ActionName("Create")]
[ValidateAntiForgeryToken]
public async Task<IActionResult> Create(IzvorDropDown IzvorDropDown, InstitucijaDropDown InstitucijaDropDown, UpitTabeleCreate UpitTabeleCreate)
{
    //insert
    await context.Database.ExecuteSqlCommandAsync($"INSERT INTO AI_Table (IAICode, VOD, VDO, user_insert, user_update, date_insert, date_update) VALUES ({IzvorDropDown.IAIcode},{UpitTabeleCreate.VOD},{UpitTabeleCreate.VDO},{TrenutniKorisnik},{TrenutniKorisnik},{TrenutnoVreme},{TrenutnoVreme})");            

    //get max key
    var item = await context.NextIAITableCodeGenerator.FromSql($"SELECT ISNULL(MAX(IAITableCode), 0) AS NextIAIcode FROM AI_Table").ToListAsync();
    int IAITableCode = item[0].NextIAIcode;     

    //insert continue
    await context.Database.ExecuteSqlCommandAsync($"INSERT INTO AI_TableLanguage (IAITableCode, LanguageID, TableName) VALUES ({IAITableCode},1,{UpitTabeleCreate.TableNameCyr})");  //cirilica - radi
    await context.Database.ExecuteSqlCommandAsync($"INSERT INTO AI_TableLanguage (IAITableCode, LanguageID, TableName) VALUES ({IAITableCode},2,{UpitTabeleCreate.TableNameLat})"); //latinica
    await context.Database.ExecuteSqlCommandAsync($"INSERT INTO AI_TableLanguage (IAITableCode, LanguageID, TableName) VALUES ({IAITableCode},3,{UpitTabeleCreate.TableNameEng})"); //engleski

    return RedirectToAction(nameof(Index));
}

The code itself works. It inserts correctly into DB, but I don't know if it will continue to do so when there are many users trying to access these tables at the same time.

1
0
7/25/2019 1:23:48 PM

Popular Answer

Databases work on commit logs. And in every commit, database checks the final version(state) of the record(or sequence) and the version number provided by the commit log itself and if there is a inconsistency between these informations you may take a exception like "This record has been changed by another user since you started". So it is important to make these(related with each other in one transaction) insert/update/delete operations with a single commit. So that you can use database's consistency mechanisms and can be sure about the behaviour of the code in any load circumstance.

1
7/25/2019 2:47:05 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow