Retrieving primary key of newly added record

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

.

Relevant Pages

  • RE: Inserting/deleting data in table in multiuser environment
    ... Add a field to the table in the SQL DB? ... Capture the value to a variable before you send it to the stored proc, ... I did create a table in a front-end to just copy data from that table on SQL ... the stored procedure will be running each time with report generation anyway. ...
    (microsoft.public.access.modulesdaovba)
  • Re: full-text contains * issue
    ... You may also want to consider using a stored proc for this situation as it ... EXEC usp_FTSearchPubsInfo '' ... > no data but was concatenation of the fields I wanted to index. ... > This is the SQL ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Parameter Sniffing - Need more info
    ... Consider a stored proc defined as follows: ... In 6.5, at compile time SQL ... when compiling a plan. ... execution plans, but a key requirement for everything to work as expected ...
    (microsoft.public.sqlserver.programming)
  • Re: type argument in OpenRecordset method
    ... format call to stored proc ... The databases/tables on the SQL Server side are split up based on ... derive the fully qualified table name, the Access app does, in fact, ... things on the SQL side, and by providing this abstraction layer, it is ...
    (comp.databases.ms-access)
  • Re: Another view on analysis and ER
    ... Jon Heggland wrote: ... it will be implicitly given by the attribute names in the foreign key). ... names, if you're not explicit. ... But SQL is well-known for mixing logical ...
    (comp.databases.theory)
Loading