news:1155582074.125321.118000@74g2000cwt.googlegroups.com...
> 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
>

"William (Bill) Vaughn" <billvaRemoveT***@nwlink.com> schreef in bericht
news:uKN2c09vGHA.2432@TK2MSFTNGP06.phx.gbl...
> 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.
> __________________________________
>
> <jeff.ran***@gmail.com> wrote in message
> news:1155582074.125321.118000@74g2000cwt.googlegroups.com...
>> 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
>>
>
>

> 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" <billvaRemoveT***@nwlink.com> schreef in bericht
> news:uKN2c09vGHA.2432@TK2MSFTNGP06.phx.gbl...
> > 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.
> > __________________________________
> >
> > <jeff.ran***@gmail.com> wrote in message
> > news:1155582074.125321.118000@74g2000cwt.googlegroups.com...
> >> 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
> >>
> >
> >