0

What is the best way to write data from a .NET web form to SQL Server 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?

VMAtm
  • 27,943
  • 17
  • 79
  • 125
chut319
  • 81
  • 1
  • 13
  • Inserting 100 rows 1 by 1 isn't going to be that slow. – Ash Burlaczenko Sep 14 '11 at 13:15
  • 1
    @Ash I'd be concerned about the overhead of calling the procedure hundreds of times from the remote app... even with connection pooling there's a lot of scaffolding going on for each call, and if the user is waiting while that form is submitted... – Aaron Bertrand Sep 14 '11 at 13:15

3 Answers3

2

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.

Community
  • 1
  • 1
Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • Thanks, Aaron. Whilst I'm sure this will work, like you say, it's still not that efficient. I assumed there was a far more elegant way of doing this but I guess I'm mistaken! – chut319 Sep 14 '11 at 18:41
  • Depends on the split method you're using. I bet XML, Inline, or CLR approaches described here (modified slightly to deal with strings) https://sqlblog.org/blogs/aaron_bertrand/archive/2010/07/07/splitting-a-list-of-integers-another-roundup.aspx would be more efficient than hundreds of calls. 10s? Maybe not. – Aaron Bertrand Sep 14 '11 at 18:43
0

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.

Widor
  • 13,003
  • 7
  • 42
  • 64
  • Thanks Widor. I've had a quick look but am struggling to get my head around it. Could I bind my data table or would I have to drop to the element level? Do you have any (beginner) links you could recommend on this subject? – chut319 Sep 14 '11 at 18:49
  • It depends how you're adding form fields and whether the `INSERT` statement always has the same number of fields. If you're just passing `NULL` for fields which don't apply then you could bind, say, a FormView directly to the SqlDataSource. If what you're doing is more complex and the nature of the `INSERT` changes depending on the number of dynamic fields (or if they only exist client-side) then doing it as per Aaron's answer would make sense. – Widor Sep 15 '11 at 11:27
0

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.

Jay
  • 26,876
  • 10
  • 61
  • 112
  • Thanks Jay. There's several tables that I wish to update with as few sql connection calls as possible. With Stored Procedures, I'm able to capture the form elements as well as writing audit information etc. By creating an insert statement, I'd have to subsequently open a new connection for the 'housekeeping' stuff. – chut319 Sep 14 '11 at 18:43
  • Well, there's no reason why you couldn't open a connection, build the insert on the fly, do the insert, do the housekeeping, close the connection. I do that all the time. Maybe what you mean is not openning a new connection but making a second trip to the database. That is entirely true and will hurt performance. But each execution of the SP is a trip to the database, too. If it's a choice between 20 SP calls versus 1 big insert and 2 or 3 more big housekeeping calls, the latter will perform better. – Jay Sep 16 '11 at 15:48