I have a SqlDataAdapter that inserts data from a typed dataset using a stored procedure that simply uses the "INSERT INTO.." T-SQL statement and at the end of the procedure, calls SCOPE_IDENTITY() and assigns it to an output parameter.
Example:
PersonDetailsDataTable (related to another table (Ex. AnotherDataTable) thru FK using PersonID as PK)
- PersonID (int), Primary Key (AutoIncrement)
- Name (string)
- Age (byte)
- Employed (bool)
// Assume there's an active SQL Connection
SqlCommand cmd1 = conn.CreateCommand();
cmd1.CommandType = CommandType.StoredProcedure;
cmd1.CommandText = "dbo.usp_InsertIntoDetails";
SqlCommand cmd2 = conn.CreateCommand();
cmd2.CommandType = CommandType.StoredProcedure;
cmd2.CommandText = "dbo.usp_InsertIntoAnotherTable";
SqlTransaction trans = null;
try
{
trans = conn.BeginTransaction();
cmd1.Transaction = trans;
cmd2.Transaction = trans;
sqlAdapter1.InsertCommand = cmd1;
sqlAdapter2.InsertCommand = cmd2;
sqlAdapter1.Update(dsPerson.PersonDetailsDataTable);
sqlAdapter2.Update(dsPerson.AnotherDataTable);
trans.Commit();
}
catch
{
trans.Rollback();
}
Here's a sample of what the stored procedure does
ALTER PROCEDURE dbo.usp_InsertIntoDetails
@PersonID int OUTPUT,
@Name varchar(50),
@Age tinyint,
@Employed bit
AS
SET NOCOUNT ON;
BEGIN
INSERT INTO Details (Name, Age, Employed)
VALUES(@Name, @Age, @Employed);
SELECT @PersonID = SCOPE_IDENTITY();
RETURN
END
By the way, the Details.PersonID column of the Details table is an identity column.
What if I run multiple applications that executes these lines of code and updates thousands of records into the details table? Is the Details table locked whenever I execute SqlDataAdapter.Update() method? Would there be any conflicts in the assignment of the latest value using SCOPE_IDENTITY()?