Say you have a table with three columns (for the sake of simplicity). You could write a sproc that takes 45 parameters, all of which, after the first 3, default to NULL. You could then intercept the updates and batch them into batches of 15. Or just do this manully with a plain old Command. One round trip.
Bob Beauchemin [EMAIL PROTECTED] -----Original Message----- From: Matthew Hunter [mailto:[EMAIL PROTECTED]] Sent: Saturday, June 15, 2002 4:37 AM To: [EMAIL PROTECTED] Subject: Re: [DOTNET] ADO.NET Roundtrip DataAdapter Question Bob, You've got me interested now and I'll be looking into it further (I just bought your book - so it is here in OZ - looks good!). I am currently looking at the data transport mechanism from our system from front end to backend, and hence I am very interested in datasets and data adapters. Our system has a lot of interfaces to external systems that will provide some challenge, and each client that uses our software has their own custom fields on our 'customer' and 'sales' tables (and possibly others). Because I need to take care of this custom data somehow (neatly I hope), it has interested me that you have said "stored procedures that handle multiple (optional) parameter series" and I don't quite understand what you mean. Are are you saying that you could put in a command object to call an StoredProc 'X' times - each time writing to a temporary table (or something) and then at the end of the script you put in a call to different SP that does some action on the temporary table values? I understand the example about the delete being an update, it has crossed my mind as we only logically delete customers, products and codes by marking their deleted flag to true. I think its pretty nifty from that respect. Cheers Matthew Hunter ----- Original Message ----- From: "Beauchemin, Bob" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Saturday, June 15, 2002 3:25 PM Subject: Re: [DOTNET] ADO.NET Roundtrip DataAdapter Question > Manual control means that you can hook the DataAdapter.Update before an individual update is sent to the database (in its Updating event) and do anything with the pameterized statement there, including reconstructing the statement to use less parameters (update based on only changed columns, on a per row basis) or batching of statements together. This is usually possible even if your use a CommandBuilder. In addition, you can specify any InsertCommand, UpdateCommand and DeleteCommand you want, so these can include creative things, such as stored procedures that handle multiple (optional) parameter series allowing a primitive form of batch updating. Or a "DeleteCommand" that actually consists of "UPDATE sometable set deleteflag = 'D' where id = @id". > > PS. Haven't been to Australia in a while (darn), let me know when it arrives. ;-) > PPS. Thanks. > > Cheers, > Bob Beauchemin > [EMAIL PROTECTED] > > -----Original Message----- > From: Matthew Hunter [mailto:[EMAIL PROTECTED]] > Sent: Friday, June 14, 2002 8:53 PM > To: [EMAIL PROTECTED] > Subject: Re: [DOTNET] ADO.NET Roundtrip DataAdapter Question > > > Thanks Bob, > > I guessed I used the term 'connect' where i was actually thinking 'talk' - > sorry about that. > > When you say "manual control" does that mean not using the dataadapter FILL > and UPDATE to talk to the database (ie. just executing the command object > directly)? > > PS. Don't suppose your book is out in Australia yet? > PPS. The next post I do will be to the other newsgroups. > > Cheers > > Matthew Hunter > > ----- Original Message ----- > From: "Beauchemin, Bob" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Sent: Saturday, June 15, 2002 12:10 PM > Subject: Re: [DOTNET] ADO.NET Roundtrip DataAdapter Question > > > > It doesn't connect to the database 7 times, but it would make 7 round > trips. By default; unless you code it differently, remember there are a > couple of hooks in the process, including complete manual control. It does > this in order to judge success or failure of each action statement (0 or > non-zero rows affected, non-zero = success). You can transact these > statement series (manually) as well. > > > > If you use a CommandBuilder, the provider may make an extra round trip for > metadata to build commands (one per CommandBuilder instance). > > > > Hope this helps, > > Bob Beauchemin > > [EMAIL PROTECTED] > > > > -----Original Message----- > > From: Matthew Hunter [mailto:[EMAIL PROTECTED]] > > Sent: Friday, June 14, 2002 5:09 PM > > To: [EMAIL PROTECTED] > > Subject: [DOTNET] ADO.NET Roundtrip DataAdapter Question > > > > > > Guys, > > > > When using the SQLDataAdapter or OLEDBDataAdapter to talk to the database, > if you set each of the commands (ie. Select, Insert, update and delete) how > many round trips does it do to the database? > > > > For example, obviously the FILL function would use the Select statement > and this would only have to contact the database once, but how about the > UPDDATE command? If you changed 5 records in the database and then added 2, > would it need to contact the database 7 times or is it smart enough to send > the commands as a batch and therefore only have to contact the database > once? > > > > Cheers > > > > Matthew Hunter > > > > You can read messages from the DOTNET archive, unsubscribe from DOTNET, or > > subscribe to other DevelopMentor lists at http://discuss.develop.com. > > > > You can read messages from the DOTNET archive, unsubscribe from DOTNET, or > > subscribe to other DevelopMentor lists at http://discuss.develop.com. > > You can read messages from the DOTNET archive, unsubscribe from DOTNET, or > subscribe to other DevelopMentor lists at http://discuss.develop.com. > > You can read messages from the DOTNET archive, unsubscribe from DOTNET, or > subscribe to other DevelopMentor lists at http://discuss.develop.com. You can read messages from the DOTNET archive, unsubscribe from DOTNET, or subscribe to other DevelopMentor lists at http://discuss.develop.com. You can read messages from the DOTNET archive, unsubscribe from DOTNET, or subscribe to other DevelopMentor lists at http://discuss.develop.com.
