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!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s