Inserting/Updating Rows in Sql Database - Avoid chatty interface

Its a good design practise to avoid chatty interfaces. This is not only true when we design our objects and interactions among objects, but also for your database interactions. For example if you have multiple rows to be updated to your table, its highly inefficient to fire your inserts one at a time to your database. If you are using a dataset, in ADO.net 1.1, data adapters update method prcessed each datarow one at a time. In ADO.Net 2.0, there is a new property for the adapter alled "UpdateBatchSize" by which you can control how many rows are send as a batch. To read more about this click on this MSDN link


But how about you have a list of your custom objects you want to use to do your inserts, in this case you can use the power of Xml processing in Sql Server, trick is to format your request as an Xml string and send it once to your database stored proc. This stored proc will process this Xml string and it updates/inserts your rows.


To demonstrate this lets, say you want to insert rows into your Employee Table, represented by Employee (ID,Name)


From your .net code, you will format an Xml request string similar to this,



< EmployeeList >
< Employee ID="E001" Name="John Doe" / >
< Employee ID="E002" Name="Mary Smith" / >
< Employee ID="E003" Name="Luke Skywalker" / >
</ EmployeeList >



and here is how your stored procedure will look like,



CREATE PROCEDURE [dbo].[sp_SaveRows]
(
@request ntext
)
AS
Begin
DECLARE @idoc int
EXEC sp_xml_preparedocument @idoc OUTPUT, @request

Declare @Id nvarchar(10)
Declare @Name nvarchar(255

DECLARE curs CURSOR FOR
SELECT Id, Name
FROM OPENXML (@idoc, '/savelist//saveitem',2)
WITH (
[Id] nvarchar(10) '@Id',
[Name] nvarchar(255) '@Name',
)
OPEN curs
FETCH NEXT FROM curs INTO @Id,@Name
WHILE @@FETCH_STATUS = 0
BEGIN

-- Do your insert/update here!

FETCH NEXT FROM curs INTO @Id,@Name
END
CLOSE curs
DEALLOCATE curs

End



As you can see, we use OPENXML call to read the Xml file and use it in cursor, if you do not want to use a cursor you can always directly insert into table using the select (with openxml). Use cursor if you want to do any kind of data manipulation or rule checking, calculations, etc...


Cheers!