Post reply

asp.net - multiple inserts many primary keys and scope_identity

  • December 4, 2013 at 2:36 pm

    #279756

    Hello

    This is working but my site will be hit by many users soon. Just need your opinions.

    I have a .NET application. I create a SQL connection and a transaction.

    Then I set the command text to insert into table A as follows: "cmd.Text = INSERT INTO TableA (.....) Values(..some values..); SELECT SCOPE_IDENTITY();

    then I execute the command: int nPK = cmd.ExecuteScalar() which executes the insert and returns the primary key.

    Then I change the command text to insert that primary key into another table along with other values cmd.Text = "INSERT INTO TableB (.....) Values();SELECT SCOPE_IDENTITY(); then I execute the command: int nPK2 = cmd.ExecuteScalar() which executes this and returns the primary key of TableB.

    Then I again change the command text to insert that primary key into another third table along with other values cmd.Text = "INSERT INTO TableC (.....) Values();SELECT SCOPE_IDENTITY(); then I execute the command: int nPK3 = cmd.ExecuteScalar() which executes this and returns the primary key of TableC

    Then the entire process repeats up to 10 times.

    Everything is done in the same Connection, same Transaction and using the same command object.

    It is working fine. I'm thnking even if 100 users hit it at once because they will each be different connections and transaction then they will get the correct primary keys and then because it is sequential (i.e.cannot insert into table 2 unless it has PK from table 1) then it should work fine. Does anyone see any reason why I should not do it this way?

  • December 5, 2013 at 5:42 am

    #1671013

    Remove the imbedded SQL, put everything in a stored procedure, pass in all your values, wrap it in a transaction and call it a day. That would be much easier than what you're proposing.

    Mark

  • December 5, 2013 at 5:52 am

    #1671018

    Mark Eckeard (12/5/2013)

    Remove the imbedded SQL, put everything in a stored procedure, pass in all your values, wrap it in a transaction and call it a day. That would be much easier than what you're proposing.

    Mark

    +1, in addition to Mark comments, if you feel there will be to many Parameters you will entertain here, then you can you User - Defined Table Types to store your information in them (if the data is more then one row)

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply