Retrieving primary key of newly added record

  • Thread starter Thread starter jeff.ranney
  • Start date Start date
J

jeff.ranney

Hi all.

I have three burning ado.net questions about using the
DataAdapter.Update technique of updating data (rather than calling a
stored proc explicty). I can't figure them out and I'm hoping it won't
be too hard for some of you ado.net gurus out there!


1. Getting the PK of a new record
Using the dataset.Update method to add a new row, how can I get the
primary key of the record I just added (assuming that table's primary
key field is an identity/autonumber one in SQL Server - that is SQL
Server automatically generates one for the next record) without doing
another explicit query of SELECT Max(PK_Field) FROM TABLE query.

I'm worried that if I do another explicit query, I am open to the
possibility of another Insert getting in inbetween the time I added the
record and checked for the maxid. I know that is not too likely, but I
would really like to eliminate the possibility all together as the
project scales.


I want to do something like this..


DataAdapter.Update(this.DataSet.Tables[0]); (this is the new table)

this.DataSet.Tables[0].AcceptChanges();
newPrimaryKey = this.DataSet.Tables[0].Rows[0][PK FIELD NAME];




I'm curious about this under

a. The scenario where the dataadapter.InsertCommand is simply a
dynamic sql string, "INSERT INTO MY TABLE..."

where the dynamic sql was derived using the CommandBuilder object:

DataAdapter.SelectCommand = new SelectCommand("SELECT * FROM Orders
WHERE Order_Id = -1",CN);
CommandBuilder cb = new commandBuilder();
DataAdapter.InsertCommand = cb.GetInsertCommand();


b. The scenario where it is a stored procedure.





2. Adding a new record with one round trip
To add a new row with dataadapter.update, I've been in the habit of
first selecting a row from a table but setting the where clause to a
condition I know will not be met, so I get back an empty row, so that I
can have the table information needed to create the new row. This does
cause a roundtrip however.

DataAdapter.SelectCommand = new SelectCommand("SELECT * FROM Orders
WHERE Order_Id = -1",CN);

CommandBuilder cb = new commandBuilder();
DataAdapter.InsertCommand = cb.GetInsertCommand();

DataAdapter.Fill(myDataset,"Orders");

DataRow dr= myDataset.Tables[0].NewRow;


dr["Amount"] = 12.34;
dr['whatever"] = "x";

myDataset.Tables[0].Rows.Add(dr);



DataAdapter.Update(myDataset.Tables[0]);
myDataset.Tables[0]).AcceptChanges();


The 2 round trips needed for this is nothing fatal I guess, but I'd
like to avoid it if possible, without having to completely manually
construct the new DataRow (ie add columns to it in code, setting (in a
hard-coded way) the types, lengths, names, etc of each of them). Does
anyone know of a better way?



finally...

3. Handling concurrency with stored procs.

So I love the way that when you do DataAdapter.Update(ds), the
dataadapter automatically handles concurrency issues. By concurrency
issues, I mean:

Person A loads rowX
Person B loads rowX
Person A saves his changes (with dataadapter.update)
Person B saves his chnages (with dataadapter.update)

rather than Person B overwriting Person A's changes, a concurrency
error will be thrown. I think SQL is generated for the
DataAdapter.UpdateCommand that checks to see that all the values of the
fields are the same as when the object was originally loaded by this
person (UPDATE TableX where PK=1 AND value1=origvalue,
value2=origvalue, etc).

My question is, if I do this

DataAdapter.UpdateCommand = new command("MyCustomStoredProc");

have I lost this cool feature? Is there any way I can get it back?


I know that was a lot. Thanks so much in advance to anyone that can
help me out. Your help is greatly appreciated!!


Regards,

Jeff
 
Sigh. I wish you could read Chapter 12 of my new book but it's still in
edit--it covers all of these issues in detail. However, there is an article
that covers most of this on my web site. See
http://www.betav.com/Files/Content/whitepapers.htm ("Dealing with an
Identity Crisis")

If this leaves questions unanswered, come back and I'll see if I can fill in
the blanks.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

Hi all.

I have three burning ado.net questions about using the
DataAdapter.Update technique of updating data (rather than calling a
stored proc explicty). I can't figure them out and I'm hoping it won't
be too hard for some of you ado.net gurus out there!


1. Getting the PK of a new record
Using the dataset.Update method to add a new row, how can I get the
primary key of the record I just added (assuming that table's primary
key field is an identity/autonumber one in SQL Server - that is SQL
Server automatically generates one for the next record) without doing
another explicit query of SELECT Max(PK_Field) FROM TABLE query.

I'm worried that if I do another explicit query, I am open to the
possibility of another Insert getting in inbetween the time I added the
record and checked for the maxid. I know that is not too likely, but I
would really like to eliminate the possibility all together as the
project scales.


I want to do something like this..


DataAdapter.Update(this.DataSet.Tables[0]); (this is the new table)

this.DataSet.Tables[0].AcceptChanges();
newPrimaryKey = this.DataSet.Tables[0].Rows[0][PK FIELD NAME];




I'm curious about this under

a. The scenario where the dataadapter.InsertCommand is simply a
dynamic sql string, "INSERT INTO MY TABLE..."

where the dynamic sql was derived using the CommandBuilder object:

DataAdapter.SelectCommand = new SelectCommand("SELECT * FROM Orders
WHERE Order_Id = -1",CN);
CommandBuilder cb = new commandBuilder();
DataAdapter.InsertCommand = cb.GetInsertCommand();


b. The scenario where it is a stored procedure.





2. Adding a new record with one round trip
To add a new row with dataadapter.update, I've been in the habit of
first selecting a row from a table but setting the where clause to a
condition I know will not be met, so I get back an empty row, so that I
can have the table information needed to create the new row. This does
cause a roundtrip however.

DataAdapter.SelectCommand = new SelectCommand("SELECT * FROM Orders
WHERE Order_Id = -1",CN);

CommandBuilder cb = new commandBuilder();
DataAdapter.InsertCommand = cb.GetInsertCommand();

DataAdapter.Fill(myDataset,"Orders");

DataRow dr= myDataset.Tables[0].NewRow;


dr["Amount"] = 12.34;
dr['whatever"] = "x";

myDataset.Tables[0].Rows.Add(dr);



DataAdapter.Update(myDataset.Tables[0]);
myDataset.Tables[0]).AcceptChanges();


The 2 round trips needed for this is nothing fatal I guess, but I'd
like to avoid it if possible, without having to completely manually
construct the new DataRow (ie add columns to it in code, setting (in a
hard-coded way) the types, lengths, names, etc of each of them). Does
anyone know of a better way?



finally...

3. Handling concurrency with stored procs.

So I love the way that when you do DataAdapter.Update(ds), the
dataadapter automatically handles concurrency issues. By concurrency
issues, I mean:

Person A loads rowX
Person B loads rowX
Person A saves his changes (with dataadapter.update)
Person B saves his chnages (with dataadapter.update)

rather than Person B overwriting Person A's changes, a concurrency
error will be thrown. I think SQL is generated for the
DataAdapter.UpdateCommand that checks to see that all the values of the
fields are the same as when the object was originally loaded by this
person (UPDATE TableX where PK=1 AND value1=origvalue,
value2=origvalue, etc).

My question is, if I do this

DataAdapter.UpdateCommand = new command("MyCustomStoredProc");

have I lost this cool feature? Is there any way I can get it back?


I know that was a lot. Thanks so much in advance to anyone that can
help me out. Your help is greatly appreciated!!


Regards,

Jeff
Click to expand...
 
Bill,

I was expecting that answer from you.

Is that by the way the most showed page in this newsgroup?

Maybe you would write a complete book about this instead of a chapter.

:-)

Cor

William (Bill) Vaughn said:
Sigh. I wish you could read Chapter 12 of my new book but it's still in
edit--it covers all of these issues in detail. However, there is an
article that covers most of this on my web site. See
http://www.betav.com/Files/Content/whitepapers.htm ("Dealing with an
Identity Crisis")

If this leaves questions unanswered, come back and I'll see if I can fill
in the blanks.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no
rights.
__________________________________

Hi all.

I have three burning ado.net questions about using the
DataAdapter.Update technique of updating data (rather than calling a
stored proc explicty). I can't figure them out and I'm hoping it won't
be too hard for some of you ado.net gurus out there!


1. Getting the PK of a new record
Using the dataset.Update method to add a new row, how can I get the
primary key of the record I just added (assuming that table's primary
key field is an identity/autonumber one in SQL Server - that is SQL
Server automatically generates one for the next record) without doing
another explicit query of SELECT Max(PK_Field) FROM TABLE query.

I'm worried that if I do another explicit query, I am open to the
possibility of another Insert getting in inbetween the time I added the
record and checked for the maxid. I know that is not too likely, but I
would really like to eliminate the possibility all together as the
project scales.


I want to do something like this..


DataAdapter.Update(this.DataSet.Tables[0]); (this is the new table)

this.DataSet.Tables[0].AcceptChanges();
newPrimaryKey = this.DataSet.Tables[0].Rows[0][PK FIELD NAME];




I'm curious about this under

a. The scenario where the dataadapter.InsertCommand is simply a
dynamic sql string, "INSERT INTO MY TABLE..."

where the dynamic sql was derived using the CommandBuilder object:

DataAdapter.SelectCommand = new SelectCommand("SELECT * FROM Orders
WHERE Order_Id = -1",CN);
CommandBuilder cb = new commandBuilder();
DataAdapter.InsertCommand = cb.GetInsertCommand();


b. The scenario where it is a stored procedure.





2. Adding a new record with one round trip
To add a new row with dataadapter.update, I've been in the habit of
first selecting a row from a table but setting the where clause to a
condition I know will not be met, so I get back an empty row, so that I
can have the table information needed to create the new row. This does
cause a roundtrip however.

DataAdapter.SelectCommand = new SelectCommand("SELECT * FROM Orders
WHERE Order_Id = -1",CN);

CommandBuilder cb = new commandBuilder();
DataAdapter.InsertCommand = cb.GetInsertCommand();

DataAdapter.Fill(myDataset,"Orders");

DataRow dr= myDataset.Tables[0].NewRow;


dr["Amount"] = 12.34;
dr['whatever"] = "x";

myDataset.Tables[0].Rows.Add(dr);



DataAdapter.Update(myDataset.Tables[0]);
myDataset.Tables[0]).AcceptChanges();


The 2 round trips needed for this is nothing fatal I guess, but I'd
like to avoid it if possible, without having to completely manually
construct the new DataRow (ie add columns to it in code, setting (in a
hard-coded way) the types, lengths, names, etc of each of them). Does
anyone know of a better way?



finally...

3. Handling concurrency with stored procs.

So I love the way that when you do DataAdapter.Update(ds), the
dataadapter automatically handles concurrency issues. By concurrency
issues, I mean:

Person A loads rowX
Person B loads rowX
Person A saves his changes (with dataadapter.update)
Person B saves his chnages (with dataadapter.update)

rather than Person B overwriting Person A's changes, a concurrency
error will be thrown. I think SQL is generated for the
DataAdapter.UpdateCommand that checks to see that all the values of the
fields are the same as when the object was originally loaded by this
person (UPDATE TableX where PK=1 AND value1=origvalue,
value2=origvalue, etc).

My question is, if I do this

DataAdapter.UpdateCommand = new command("MyCustomStoredProc");

have I lost this cool feature? Is there any way I can get it back?


I know that was a lot. Thanks so much in advance to anyone that can
help me out. Your help is greatly appreciated!!


Regards,

Jeff
Click to expand...
Click to expand...
 
Bill,

My apologies for a late reply. Thanks very much for the link. It was
very helpful. I'm looking forward to the book!
Bill,

I was expecting that answer from you.

Is that by the way the most showed page in this newsgroup?

Maybe you would write a complete book about this instead of a chapter.

:-)

Cor

William (Bill) Vaughn said:
Sigh. I wish you could read Chapter 12 of my new book but it's still in
edit--it covers all of these issues in detail. However, there is an
article that covers most of this on my web site. See
http://www.betav.com/Files/Content/whitepapers.htm ("Dealing with an
Identity Crisis")

If this leaves questions unanswered, come back and I'll see if I can fill
in the blanks.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
INETA Speaker
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no
rights.
__________________________________

Hi all.

I have three burning ado.net questions about using the
DataAdapter.Update technique of updating data (rather than calling a
stored proc explicty). I can't figure them out and I'm hoping it won't
be too hard for some of you ado.net gurus out there!


1. Getting the PK of a new record
Using the dataset.Update method to add a new row, how can I get the
primary key of the record I just added (assuming that table's primary
key field is an identity/autonumber one in SQL Server - that is SQL
Server automatically generates one for the next record) without doing
another explicit query of SELECT Max(PK_Field) FROM TABLE query.

I'm worried that if I do another explicit query, I am open to the
possibility of another Insert getting in inbetween the time I added the
record and checked for the maxid. I know that is not too likely, but I
would really like to eliminate the possibility all together as the
project scales.


I want to do something like this..


DataAdapter.Update(this.DataSet.Tables[0]); (this is the new table)

this.DataSet.Tables[0].AcceptChanges();
newPrimaryKey = this.DataSet.Tables[0].Rows[0][PK FIELD NAME];




I'm curious about this under

a. The scenario where the dataadapter.InsertCommand is simply a
dynamic sql string, "INSERT INTO MY TABLE..."

where the dynamic sql was derived using the CommandBuilder object:

DataAdapter.SelectCommand = new SelectCommand("SELECT * FROM Orders
WHERE Order_Id = -1",CN);
CommandBuilder cb = new commandBuilder();
DataAdapter.InsertCommand = cb.GetInsertCommand();


b. The scenario where it is a stored procedure.





2. Adding a new record with one round trip
To add a new row with dataadapter.update, I've been in the habit of
first selecting a row from a table but setting the where clause to a
condition I know will not be met, so I get back an empty row, so that I
can have the table information needed to create the new row. This does
cause a roundtrip however.

DataAdapter.SelectCommand = new SelectCommand("SELECT * FROM Orders
WHERE Order_Id = -1",CN);

CommandBuilder cb = new commandBuilder();
DataAdapter.InsertCommand = cb.GetInsertCommand();

DataAdapter.Fill(myDataset,"Orders");

DataRow dr= myDataset.Tables[0].NewRow;


dr["Amount"] = 12.34;
dr['whatever"] = "x";

myDataset.Tables[0].Rows.Add(dr);



DataAdapter.Update(myDataset.Tables[0]);
myDataset.Tables[0]).AcceptChanges();


The 2 round trips needed for this is nothing fatal I guess, but I'd
like to avoid it if possible, without having to completely manually
construct the new DataRow (ie add columns to it in code, setting (in a
hard-coded way) the types, lengths, names, etc of each of them). Does
anyone know of a better way?



finally...

3. Handling concurrency with stored procs.

So I love the way that when you do DataAdapter.Update(ds), the
dataadapter automatically handles concurrency issues. By concurrency
issues, I mean:

Person A loads rowX
Person B loads rowX
Person A saves his changes (with dataadapter.update)
Person B saves his chnages (with dataadapter.update)

rather than Person B overwriting Person A's changes, a concurrency
error will be thrown. I think SQL is generated for the
DataAdapter.UpdateCommand that checks to see that all the values of the
fields are the same as when the object was originally loaded by this
person (UPDATE TableX where PK=1 AND value1=origvalue,
value2=origvalue, etc).

My question is, if I do this

DataAdapter.UpdateCommand = new command("MyCustomStoredProc");

have I lost this cool feature? Is there any way I can get it back?


I know that was a lot. Thanks so much in advance to anyone that can
help me out. Your help is greatly appreciated!!


Regards,

Jeff
Click to expand...
Click to expand...
Click to expand...
 
Back
Top