If you have multiple rows of data, it is much more efficient to insert all the rows in one go in a single call to a stored procedure. To do this you serialize your data into xml and then call a stored procedure which takes xml as a string parameter.
AFAIK you cannot serialize a Dictionary, but you can serialize a List.
So I suggest you loop through your Dictionary (List of Dictionaries) filling a List with appropriate values. I tend to use Structures for this, but simple classes work fine as well, e.g.:
Public Class DBData
Public pKey As Integer
Public pValue As Double
Public Sub New(key As Integer, val As Double)
pKey = key
pValue = val
End Sub
Public Sub New()
pKey = 0
pValue = 0.0
End Sub
End Class
Create your List and fill it e.g:
Dim myList As New List(Of DBData)
For Each kvPair In dict
myList.Add(New DBData(kvPair.Key, kvPair.Value))
Next
Now for the bit of magic to serialize:
Dim sw As New StringWriter
Dim serializer As New XmlSerializer(GetType(List(Of DBData)))
Dim ns As New XmlSerializerNamespaces()
serializer.Serialize(sw, myList)
Dim xml As String
xml = sw.ToString
Dim pos As Integer
pos = xml.IndexOf("<Array")
xml = xml.Substring(pos)
Notice here I am stripping off the header of the xml that has just been created. That is because I have never been able to get SQL Server to work if the header was included - you probably can, I've just never looked hard enough.
We call the stored procedure like this (obviously change the connection string to one that works for you):
Using conn As New SqlConnection("Integrated Security=true; Initial Catalog=dbname; Data Source=servername")
Using cmd As New SqlCommand("uspDBDataInsert", conn)
cmd.CommandType = CommandType.StoredProcedure
Dim param As SqlParameter
param = cmd.CreateParameter
param.ParameterName = "@dbdata"
param.DbType = DbType.String
param.Value = xml
cmd.Parameters.Add(param)
conn.Open()
cmd.ExecuteNonQuery()
conn.Close()
End Using
End Using
As to SQL Server the procedure required looks like this:
CREATE PROCEDURE [dbo].[uspDBDataInsert]
-- Add the parameters for the stored procedure here
@dbdata varchar(MAX)
AS
BEGIN try
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
declare @idoc int
--Create an internal representation of the XML document
EXEC sp_xml_preparedocument @idoc OUTPUT, @dbdata
insert into DBData
(
id, rowvalue
)
select
pKey,pValue
FROM OPENXML (@idoc, '/ArrayOfDBData/DBData',2)
WITH (
pKey int,
pValue float
)
select @@ROWCOUNT
end try
begin catch
declare @msg nvarchar(200)
SELECT @msg = ('Error during insert of DBData...')
end catch
Obviously here you will need to change the ArrayOf to whatever you call your class and of course use your own table.
Although I have shown here just a simple key/value pair, you can populate a multi-column table in exactly the same way.
One point to note: your class/structure must be Public in a Public module, otherwise serialize will not work. Also if you provide your own parameter constructor for a class (as I do), you must also provide a parameterless one, otherwise again the serializer will object.
HTH
Addendum
What I forgot to put, for the VB to work you need:
Imports System.IO
Imports System.Xml.Serialization
Imports System.Data.SqlClient
