We have an ASP.NET 2.0 site that uses SQL Server 2000/2005. I am working on a page that updates our user table. But it will also update multiple related tables. For example, the user could be a member of several different companies and/or organizations and may have 1 to many roles out of a list of available roles. These are handled with listboxes and stored in related Companies, Organizations and Role tables, along with normalized linking tables. We are also using the ObjectDataSource control, and I am working on an object that handles adds, updates and deletes etc. We are also using Enterprise Library 2.0 and prefer to use stored procedures rather than inline code.
My question relates to how to do the multiple table updates that will be required. I know how to pass parameters to a procedure, but in this case, an update will update several different tables and should be part of a transaction. If I create one stored procedure for each operation (Add, Update, Delete) etc, how do I pass the data from the listboxes to SQL Server. There can be 0 to many roles for example. How can I pass these to SQL Server? There does not appear to be an ‘array’ datatype available for passing to SQL Server. I could write multiple stored procedure but this would seem to be less than efficient. What is the best approach for our situation? Should we revert to using in-line code rather than stored procedures?
Thanks in advance for any suggestions,