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()?
Hi,
If there's multiple Sql Statements being sent to the Server these statements or messages are being Queued and executed in the manner that they arrived in the server. There will be no table-locking if your just issuing INSERT statements.
On your Scope_Identity call, I'm not quite comfortable on using SCOPE_IDENTITY() to get the last generated Identity value. I'm not quite familiar with its behavior, but as I understand, the scope in SCOPE_IDENTITY refers to Triggers, Functions and other modules in SQLServer which as I see in your stored proc is pretty quite safe on using SCOPE_IDENTITY(). But for me, I tend to use IDENT_CURRENT() function to get the latest identity number to a specific table. Though some may argue that its quite slower, but I think its much safer...
cheers,
Thanks a lot wraith. Well, that 'table locking' thing bothered me until you said that it has nothing much to with in INSERT statements.
I tried using the IDENT_CURR(<table>) function but still the same exception occurs.
You might've forgotten to indicate that there was an exception happening in your SP in your initial post. Can you please post here the exception that is occuring? This would help us more on identifying the problem if its really in your SP or on your C# code.
Hi wraith, thanks for the reply. My teammate and I just recently found out that the exception was not thrown by the SP but by the DataTable itself.
After inserting say for example 10,000 records on my datatable, and generating identity values from 1-10,000 on my primary key column (ModelID), I will use the SqlDataAdapter.Update() method to call the SP in the db and insert the records. I have an output parameter that maps to the datatable's Identity column.
SP :
ALTER PROCEDURE ATE.usp_InsertDataInformation
@ModelName varchar(15),
@TestStage char(10),
@CycleNumber tinyint,
@DateTested datetime,
@ModelID int OUTPUT
INSERT INTO ATE.DataInformation(ModelName,TestStage,CycleNumber,DateTested)
VALUES(@ModelName,@TestStage,@CycleNumber,@DateTested)
SET @ModelID = SCOPE_IDENTITY()
After exiting the SP, the ModelID in my datatable gets updated with the last inserted record on the ATE.DataInformation table. What happens is if the ModelID value is already existing in the datatable, the datatable throws an exception. Say for example, the last generated identity for ModelID is 5180, and there exists from the datatable a record with the same value, the datatable throws the primary key unique constraint violation.
Hi jong!
Just a suggestion, I was reading your question above and it seems to me that it's better to do this using SqlBulkCopy so that the whole 10,000 records would be done in a batch(with transactions). Then, the next thing that you need to do is get the SCOPE_IDENTITY which you could do inside the SqlRowsCopied event(you can add a query here to get the SCOPE_IDENTITY) of the SqlBulkCopy Class.
HTH
Hmm.. I used the SqlBulkCopy class before but I never thought of using the event SqlRowsCopied. hehe. Thanks sir keithrull.
If I use SqlConnection.BeginTransaction() and the SqlBulkCopy object detects it, it throws an exception "Unexpected existing transaction."
using (SqlConnection conn = new SqlConnection(connString))
conn.Open();
SqlTransaction activeTrans = conn.BeginTransaction();
using (SqlBulkCopy bcp = new SqlBulkCopy(conn))
bcp.NotifyAfter = 1;
bcp.SqlRowsCopied += new SqlRowsCopiedEventHandler(bcp_SqlRowsCopied);
bcp.DestinationTable = "ATE.DataInformation";
bcp.WriteToServer(diTable); // exception occurs here...
// DO BULK COPY ALSO TO CHILD TABLE
bcp.DestinationTable = "ATE.TestData";
bcp.WriteToServer(tdTable);
activeTrans.Commit();
catch (Exception ex)
activeTrans.Rollback();
throw new Exception("Bulk Insert failed. Reason : " + ex.Message);
keithrull: Hi jong! Just a suggestion, I was reading your question above and it seems to me that it's better to do this using SqlBulkCopy so that the whole 10,000 records would be done in a batch(with transactions). Then, the next thing that you need to do is get the SCOPE_IDENTITY which you could do inside the SqlRowsCopied event(you can add a query here to get the SCOPE_IDENTITY) of the SqlBulkCopy Class. HTH
What actually bothers me is that a value in the ModelID identity column of my table may actually exist in the identity column (ModelID) of my typed datatable. This will obviously violate uniqueness.
Ex.
| ModelID | ModelName | CustomerSerial |
| 1002 | XXX | AIH0009 |
| 1003 | XXX | AIH0010 |
| 1004 | XXX | AIH0011 |
| 1005 | XXX | AIH0012 | <-- will throw exception here because of existing (1005) value in datatable
| 998 | XXX | AIH0013 |
| 999 | XXX | AIH0014 |
:
| 1005 | XXX | AIH0020 |
| 1006 | XXX | AIH0023 |
Blue IDs - updated ModelID from database
Red IDs - auto-generated by datatable (not yet updated by db)
Would it be possible that you won't mark the Identity field in your table as IDENTITY? You can just make it a simple numeric field and don't place any number upon populating the datatable. Just let the Update method populate your Identity (ModelID) field.
Or, you can create another field that will store the Generated Record number by the Database. Cause clearly the problem is synchronization between your datatable generated ID and the Database generated ID.
hi guys, sorry for the late reply.
we have already a solution to the problem and we didn't have to eliminate the IDENTITY column in the database. The typed datatable had to adjust.
We just added another column into the parent table which is of type System.Int32 that will be the SourceColumn of OUTPUT parameter. Then I just created a private method that manually changes the child rows' referencing column to that of the parent row's additional column.
It's a little bit rough but, I think we get better results from the batch inserts.
Hey guys... I'm trying to do essentially the same thing as Jong - but I can't seem to figure out how to get SCOPE_IDENTITY() in the SqlRowsCopied event handler because it doesn't seem like the database has been updated yet at that point. I'm currently passing datarows (instead of datatables because I can't get this to work) to SqlBulkCopy and getting MAX(ID) just after the row has been written to the database... but it bothers me because having to go datarow by datarow somewhat defeats the purpose of doing the copy in bulk... plus I definitely want to use SCOPE_IDENTITY() as opposed to getting the MAX value from the database table because we all know what can happen when we do things that way. Any ideas? Thanks,
Jeremy