Bonnie,

I try to avoid complex stored procedures like the plague. Maintenance can be a nightmare without third-party tools. T-SQL as a procedural language is comparable to FoxBase, Query Analyzer is at least two generations out of date, and the VS.NET editor is not a whole lot better.

I have a few large, hand-coded SPs that were not generated by my framework and I dread the day that I have to update them to match database changes.

>>>>If your tables are related (which I also haven't done)...

Its interesting that enforcing relationships in the database is not really necessary anymore. I still do it out of habit. But the only tangible benefit I get from it is the ability to divine a more complete database schema within my code. I could probably accomplish the same thing by using index and constraint names that conform to some specific format.

15 years ago, a relational database on a PC was the greatest thing since VisiCalc. Now we are cycling back to nearly free-form data files with embedded metadata tags. Who'd a thunk it?

>Keith,
>
>Thanks ... I only wondered about the foreign keys ... and hopefully your post gives some insight for any lurkers here.
>
>I do things the same as you, except that I don't use the .Update() method. And I agree with you that it's better to perform multiple table updates in .NET rather than in SQL. I do that also. Probably the main reason that I don't use the .Update() command and roll-my-own instead is because I pass data back-and-forth between client-side and server-side via Web Services and consequently I don't pass a DataSet. I pass XML as a string and so the "state" of each DataRow is *not* preserved (I pass updated/added rows separate from deleted rows).
>
>But, anyway, the basic flow of logic sounds to be pretty much the same as the way you do it.
>
>Thanks,
>~~Bonnie
>
>
>
>
>>Bonnie,
>>
>>The Update method doesn't automatically handle foreign keys, which is why the parent tables are updated first. In a DataSet with multiple related tables, the foreign key columns in the child tables will be DbNull for newly created rows or will already have the value of the related parent row from the initial Fill.
>>
>>So really, the only consideration is adding new child rows that are related to a new parent row. Since Update also refreshes the DataRows with the new identity values from the database, and since the parent rows are added before the children, you can set the foreign key columns in the related child tables between the Update call for the parent and the Update call for the children.
>>
>>Basically, you have to order the updates and grab the newly generated foreign key values either in the stored procedure(s) or in your data-layer code. And the way I have my stuff set up is through a superclass that evaluates the schema and does all this stuff automatically.
>>
>>Aside from it being easy for my own projects, it is also much more flexible to perform multiple table updates in .NET rather than SQL. Exception handling is better and you also get a ton of flexibility for handling update conflicts in a multi-user situation.
>>
>>>Keith,
>>>
>>>>Am I missing something important?
>>>
>>>No, not at all ... I didn't say you couldn't do it, all I was saying that it's not as straightforward when you have multiple tables. You have a lot you have to do yourself, like be sure to wrap it all in a transaction, call the Updates in the proper order, make sure you get all the foreign keys correct, etc.etc.
>>>
>>>BTW, since I roll my own updates and I haven't used this methodology for updating the database, let me ask you a question. If your tables are related (which I also haven't done), when you do an .Update(), does the .Update() method automatically know to plug in new keys into the related tables? Or do you have to do that yourself?
>>>
>>>~~Bonnie
>>>
>>>
>>>>Bonnie,
>>>>
>>>>Really? I mean, I use this method all the time and it works very well for me. Are we talking about the same thing?
>>>>
>>>>For instance, I have a DataSet with 5 related tables, 1 grandparent, 1 parent, 1 many-to-many xref table, and 2 children. The way that I update all of the tables while avoiding constraint problems is to use a different DataAdapter for each table, then call .Update for the grandparent first, then the parent, then the 2 children, and finally the xref table.
>>>>
>>>>Am I missing something important?
>>>>
>>>>TIA
>>>>
>>>>>Keith,
>>>>>
>>>>>But wouldn't the easiest method of updating the database be to use the DataAdapter's Update method?
>>>>>
>>>>>I think that gets a little difficult with multiple tables in the DataSet and the use of Stored Procs.
>>>>>
>>>>>~~Bonnie
>>>>>
>>>>>
>>>>>>>All,
>>>>>>>
>>>>>>> In the world of Microsoft programming there has always been more than one way to do something. My question pertains to what is the right or best way to get data from a dataset into the database when using a data adapter. The way I've done it now is the insert, update or delete command on the data adapter is set to the proper stored procedure in the database. Most of the stored procedures have parameters. In my code I update each of the parameters on the command and call the executenonquery method on the command. Hence the database is then updated, inserted or deleted. The problem gets to be that if there are a lot of parameters being passed to the stored procedure there is 1 line of code for each 1. Is there another or better method of updating a database?
>>>>>>>
>>>>>>>Any thoughts or other information would be greatly appreciated.
>>>>>>
>>>>>>Jim,
>>>>>>
>>>>>>I don't understand your question completely, so maybe this is off-base. But wouldn't the easiest method of updating the database be to use the DataAdapter's Update method?