Some time we need data from Dataset into Sqlserver for insertion or updation in the Sqlserver Tables.
For example if you need to insert an image then you can use this method to insert the data in Sqlserver tables.
Today, I will explain how to get data from Dataset into Sqlserver Table.
— Code for .Net
Dim ds as new Dataset
Dim dt as new Table
dt.TableName = “MyTable”
ds.Tables.add(dt)
Dim lStrWriter As New StringWriter
Dim lStr As String
ds.WriteXML(lStrWriter)
lstr = lStrWriter.Tostring
Pass this lstr variable to the procedure as parameter – @xmldoc.
Now write the code procedure to get the data from Dataset.
— Code in Sqlserver Store Procedure
CREATE PROCEDURE [dbo].[TestXML]
@xmldoc text — Give Text datatype for getting xml from dataset
as
———Read the Datset From XML —————–
— call sp_xml_preparedocument
DECLARE @idoc int
EXEC sp_xml_preparedocument @idoc OUTPUT, @xmldoc
SELECT * into #TestTable
FROM OPENXML (@idoc, ‘/NewDataSet/MyTable’,2)
WITH (pk_Userid BigInt,
UserName Varchar(10))
EXEC sp_xml_removedocument @idoc
———- End Read ———-
END
BY the use of this procedure you get all the data from dataset in to the “#Testtable” and we can use this as Sqlserver table in procedure.
I hope this article will be useful to you, Please give feedback on this article.