CODEWITHSUNDEEP
c#asp.netsql-serversql-server-2008
Alex J
Alex J

Reputation: 1567

upsert data for a list of objects to database

Often times, I find myself needing to send a user updated collection of records to a stored procedure. For example, lets say there is a contacts table in the database. On the front end, I display lets say 10 contact records for the user to edit. User makes his changes and hits save.

At that point, I can either call my upsertContact stored procedure 10 times with the user modified data in a loop, or send an XML formatted <contact><firstname>name</firstname><lastname>lname</lastname></contact> with all 10 together to the stored procedure. I always end up doing xml.

Is there any better way to accomplish this. Is the xml method going to break if there are large number of records due to size. If so, how do people achieve this kind of functionality?

FYI, it is usually not just a direct table update so I have not looked into sqldatasource.

Change: Based on the request, the version so far has been SQL 2005, but we are upgrading to 2008 now. So, any new features are welcome. Thanks.

Update : based on this article and the feedback below, i think Table Valued Parameters are the best approach to choose. Also the new merge functionality of sql 2008 is really cool with TVP.

Upvotes: 0

Views: 1074

Answers (4)

Aaron Bertrand
Aaron Bertrand

Reputation: 280431

What version of SQL Server? You can use table-valued parameters in SQL Server 2008+ ... they are very powerful even though they are read-only and are going to be less hassle than XML and less trouble than converting to ORM (IMHO). Hit up the following resources:

MSDN : Table-Valued Parameters:

http://msdn.microsoft.com/en-us/library/bb510489%28SQL.100%29.aspx

Erland Sommarskog's Arrays and Lists in SQL Server 2008 / Table-Valued Parameters:

http://www.sommarskog.se/arrays-in-sql-2008.html#TVP_in_TSQL

Upvotes: 4

Ryan Gross
Ryan Gross

Reputation: 6515

It sounds like you could use an Object-Relational-Management(ORM) solution like NHibernate or the Entity Framework. These solutions provide you with the ability to make changes to objects, and have the changes propagated to the database by the ORM provider. This makes them much more flexible than issuing your own sql statements to the database. They also make optimizations like sending all changes in a single transaction over a single connection.

Upvotes: 0

Josh
Josh

Reputation: 2975

The Sql Server XML datatype is the same as a VARCHAR(MAX) so it would take a really large changeset to cause it to break.

I have used a similar method in the past when saving XML requests and responses and found no issues with it. Not sure if it's the "best" solution, but "best" is always relative.

Upvotes: 0

Tejs
Tejs

Reputation: 41256

I would think directly manipulating XML in the database would be more trouble than it is worth to go that route; I would suggest instead making each call separate like you suggest; 10 calls to save each contact.

There are benefits to that approach and drawbacks; obviously, you're having to create the database connection. However, you could simply queue up a bunch of commands to send on one connection.

Upvotes: 0

Related Questions