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
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
