We just think that all user access to the database should be through SPs. You can set up your SQL Server to only allow access to users through SPs, giving the database further security (don't ask me how to do this, I'm not an expert on all the SQL Server details).

As far as the CommandBuilder goes, if you have a DataSet that is fairly complex and the SQL to get this data is more than a few simple SELECTs, then there is no way the CommandBuilder can generate any code for you. We have lots of DataSets in our app like this.

I suppose there are other reasons, but it's early Saturday morning and I haven't had my coffee yet ... can't think straight until then. <g>

~~Bonnie


>Nice! But tell me, what is the advantage of using SP's instead of generating the sql statements on the fly as the CommandBuilder does ?
>
>I am with you on using VFP for string manipulation, i still use it a lot since using the regular expressions in .NET is VERY ugly :(
>
>>Stephane,
>>
>>No, not a command builder. Here's what we do: All data access is through Stored Procedures. No exceptions to that rule. Each table has basic Stored Procedures to handle updates, reads and deletes, named with a naming convention. So, for example, the Personnel table would have the following three basic SPs.
>>
>>bsp_PersonnelGet
>>bsp_PersonnelDelete
>>bsp_PersonnelPut
>>
>>The Put SP handles both updates and inserts depending on the PK (if the PK, PersonnelKey, is 0 the SP will insert a row, otherwise it updates the row). It always returns the PK as an OUTPUT parameter. We use a tool that I wrote in VFP to generate the scripts to create the SPs (we used VFP because VFP is great at string handling, plus the fact that when we started this project I knew VFP and didn't quite have a handle on C# yet). Each SP has a parameter for every column in the table. Even if you have 100's of tables, by using something to automatically generate the scripts and automatically create the SPs, as we do, it's not an inconvenience at all to use this methodology. We also have various custom SPs other than the basic ones that have to be coded by hand.
>>
>>Anyway, the DataAccess layer in the .NET app simply calls a generic method in our DataAccess base class, SetBasicParameters, to fill the Parameters collection. This method simply spins through every column in a row, like this:
>>

>>Command.Parameters.Add("@" + Table.Columns[i].ColumnName, Row[i]);
>>

>>Also, if you pass the name of the PK column to the SetBasicParameters method:
>>

>>Command.Parameter["@" + KeyName].Direction = ParameterDirection.InputOutput
>>

>>
>>
>>Anyway, that's just a brief overview of our techniques. Hope this helps you generate some ideas.
>>
>>~~Bonnie
>>
>>
>>>Hi Bonnie,
>>>
>>>When you say you "Roll your own", did you build your own command builder? If so could you expand a bit on it.
>>>
>>>I am thinking of building something similar which would take a dataset a build the SQL Command with if (UPDATE... DELETE... INSERT..) To persist any changes back to the database.
>>>
>>>Thanks.
>>>
>>>>OK, I don't use the CommandBuilder stuff (I roll my own), but I think your problem is that you should do the EndCurrentEdit() before you build the Insert command.