Best approach to Insert a new record with 40 columns from a VB.Net app

Question

A colleague of mine is working on a .NET application. He needs to write his save function for his object.

Normally he would use a stored procedure in SQL Server to do this, but with 40 columns that is quite a task.

Several of our other colleagues have preferred using XSD's for their database interaction.

What is going to give the best effort, maintainability and efficiency trade off when dealing with large records?

Answer 1

The XSD does not affect the comunication between app & db. What do you use for comunication with db? DataAdapters? You should use a parameters in commands for values. Then you can use direct table access or stored procedures. The direct table access is more fast but the SP are (commonly) more sucurable.

Answer 2

I suspect you use ado.net here.

If you do you have to lazy option of using an SqlCommandBuilder to create insert, update and delete statements automatically. The SqlCommandBuilder examines an SqlDataAdapter that is set up with a single query that selects from a single table. The query can even be a select *

Answer 3

An approach that has worked for me is to create a stored procedure that takes one paramter - an xml string. Then you can either user the build in sql tools to xpath to the data in the string or just parse it out.

Say your xml looks like this:

<data>
<col1 value="myVal">
<col2 value="myVal2">
<col3 value="myVal3">
</data>

Your sproc would look something like this:

Create Procedure InsertData @xml xml
as

DECLARE @hDoc int
--Prepare input values as an XML documnet
exec sp_xml_preparedocument @hDoc OUTPUT, @xml

Insert Into MyTable(col1,col2,col3)
select col1,col2,col3
from OPENXML(@hdoc,'/data/')
WITH (col1 varchar(100) '/data/col1/@value', col2 varchar(100) '/data/col2/@value', col3 varchar(100) '/data/col3/@value')
EXEC sp_xml_removedocument @hDoc

GO

You see more on this here: http://technet.microsoft.com/en-us/library/ms187897.aspx

Answer 4

This is one of those questions that doesn't really have an answer.

It depends on what you are trying to achive at the time.

I would usually use a Stored Procedure on the basis that I can write fewer lines of code and also I can tweak the Stored Procedure without rebuilding or redeploying anything. If I need to add new fields then extra code is obviously required.

If speed then becomes an issue I can explore more complex solutions.

I like the suggestion that Brendan makes of using XML however I think we might be doing a lot of work just to avoid creating too many parameters. I can see this would be a very good way to deal with a situation where we hit some king of limit.