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!
