Inserting multiple rows into SQL Server table from web application - best practice

What is the best way to write data from a .NET web form to SQL Server开发者_如何学Python 2005?

My web application creates forms dynamically and the amount of fields on each form could range from a few to close to one hundred.

The way I'm currently doing this is to collect the submitted form data (using the Request.Form.AllKeys method) into a data table, open a sql connection and call the same stored procedure for each row in my data table before closing the connection again. This seems an inefficient way of doing things.

I know I can use SqlBulkCopy for this purpose but I then lose the functionality given to me by stored procedures (the ability to chain different commands together, for instance).

Does anybody have any suggestions?

---

You can pass the data in as a delimited list and split it (see How do I split a string so I can access item x?) - while still not the most efficient thing in the world, it's likely going to be bounds more efficient than calling the procedure hundreds of times.

In SQL Server 2008 you'll be able to table-valued parameters.

---

I'd recommend you take a look at DataBinding and the Data controls.

Although they'll be doing much the same "under the hood" as you are currently, you'll at least have the benefit of increased maintainability and optimised code by using the framework.

---

You could construct a single insert statement that inserts multiple records. Of course, it would be tough to do this with a stored procedure. Without knowing more of what you're doing, it's hard to say if there's any particular advantage to using the stored procedure. Life is full of trade-offs.