Can I Use ASP.NET Wizard Control to Insert Data into Multiple Tables?

Question

Hello All,

I have an ASP.NET 3.5 webforms project written in VB that involves a multi-table SQL Server insert. That is, I want the customer to input all their contact information, order details etc. into one control (thinking wizard control). Then, I want to call a stored procedure that does the insert into the respective database tables.

I'm familiar and comfortable with the ASP.NET wizard control. However, all the examples I've seen in my searches pertain to inserting data into one table.

Questions: 1. Given a typical order process - customer information, order information, order details - should a wizard control be used to insert data into multiple database tables? If not, what controls/workflow do you suggest? 2. I've set primary keys and indexes on my order details, orders and customers tables. Is there special stored procedure syntax to use to ensure that referential integrity is maintained through the insert process?

Thanks, Sid

Answer 1

Referential integrity doesn't depend on "stored procedure syntax", it depends on how you have the keys/constraints/etc. defined on the database objects themselves. That's really the beauty of referential integrity. It doesn't matter how the insert/update is occurring (i.e. via stored procedure, call from a C#/JAVA program, etc.), the referential integrity will ensure that the integrity of the data is preserved.

To preserve proper parent child relationships between your tables, what you want to use are foreign keys. For example, a foreign key for an order to order_details table might be from order_details->order_num to it's parent table (order->order_num). This relationship would ensure that no order record can be deleted if it has associated order_detail records.

As far as the best way to do the updates (via wizard, etc.) I think the main thing is to stick with stored procedures as much as possible. The high level mechanism you use (a wizard in your example) isn't as important, but by using a stored procedure you encapsulate the database logic in the proper place, and plus, it can be reused later by other applications if need be.