.NET | Persist multiple objects in minimum number of queries

Question

I have a list of objects which needs to be persisted in a SQL Server database table, where each object gets persisted as single record. i.e. List of objects result in insertion of multiple records.

Trivial way of saving the objects is to loop over the list and fire a query/stored procedure/etc. for saving that record. But this results in multiple database interactions.

Is there a way to persist the list of objects in lesser number of database interactions?

Answer 1

Are you using SQL Server 2008? You can use table-valued parameters to pass a collection of values (rows) to a command in a single parameter.

Answer 2

Single stored proc with multiple XML (2005) or table valued (2008) parameters

This is what we do (2005)

The definitive articles from Erland Sommarskog for 2005 and 2008

Answer 3

Even NOT doing that you can put multiple SQL staatements into one call (I.e. batch). There is nothing stopping you from using 30-60 opr even more normal INSERT statements and submit them to the SQL Server at once. SQL Strings can be large - and you can have multiple statements in one of them.

Trick here is to reduce latency through round trips. I.e. call / wait for answer pairs. Table Values Parameters as well as "batching" both do that.

Answer 4

What I have done in the past, if the object is serializable and your sql server is 2005 or greater is use the xml Serializable and then save the object in an xml field. If you want to break the object down into indivual records then you can still pass in the xml and use xml to query

For example

DECLARE @X XML 

SET @X ='<ListOfEmployees>
<Employee ID="5">
<Name>Mike</Name>
<Salary>67000</Salary>
</Employee> 
<Employee ID="6">
    <Name>Bob</Name>
    <Salary>40000</Salary>
</Employee> 
</ListOfEmployees>'


SELECT 
T.c.value('Name[1]', 'varchar(50)'), -- The [1] tells Sql get the first Node Name     under the ListOfEmployees/Employee mandatory
T.c.value('Salary[1]','money'),
T.c.value('@ID','int')
FROM @X.nodes('ListOfEmployees/Employee') T(c)

The function nodes and value are case sensitive

To turn an object in to xml

 XmlSerializer x = new XmlSerializer(classObject.GetType());
        MemoryStream stream = new MemoryStream();
        x.Serialize(stream, classObject);

        return Encoding.ASCII.GetString(stream.ToArray());

All List will actually be translated as <ArrayOf{ClassName}> all your other variables will be the Property Name