Passing a Data Table to a SQL Server 2005 Stored Procedure

Posted on December 5th, 2007 | by Admin |

One of the readers at SQLServerCentral forum asked me about passing a DataTable to a stored procedure. He wanted to get an XML representation of the DataTable and then pass it to the stored procedure. I tried to answer the question and came up with a complete sample code, which I think would be useful to others too.

Here is what the sample code does.

  1. Opens a connection to a database
  2. Executes a stored procedure which returns a DataSet with 2 DataTables
  3. Get the XML out of the first table
  4. Pass the XML to a stored procedure

Before we have a look at the code, we need to create a database and create a couple of tables. Then we need to populate the tables with some sample data. Here is the code.

– Create a Database

CREATE DATABASE DataTableTest

USE DataTableTest

GO

–Create the sample tables

CREATE TABLE Employees (

EmployeeID BIGINT IDENTITY(1,1),

EmployeeName VARCHAR(50),

DepartmentID BIGINT )

CREATE TABLE Departments (

DepartmentID BIGINT IDENTITY(1,1),

DepartmentName VARCHAR(50) )

GO

– Populate the Sample Tables

INSERT INTO Departments ( DepartmentName)

SELECT ‘Software’

INSERT INTO Employees (EmployeeName, DepartmentID )

SELECT ‘Jacob’, 1

GO

Now let us create a stored procedure which returns two result sets.

CREATE PROCEDURE GetEmployeeInfo

AS

SET NOCOUNT ON

SELECT EmployeeName, DepartmentID

FROM Employees

WHERE EmployeeID = 1

SELECT DepartmentName FROM Departments

WHERE DepartmentID = 1

GO

Let us create the next stored procedure which accepts an XML parameter. This procedure will insert the data from the XML parameter, into the Employee Table.

CREATE PROCEDURE ProcessXml

(

@data XML

)

AS

INSERT INTO Employees(EmployeeName, DepartmentID)

SELECT

x.d.value(‘EmployeeName[1]‘,’VARCHAR(50)’) AS EmployeeName,

x.d.value(‘DepartmentID[1]‘,’INT’) AS DepartmentID

FROM @data.nodes(‘/NewDataSet/Table’) x(d)

GO

Now Let us see the VB.NET code. I have created a VB.NET console application which performs the 4 steps mentioned above.

Note: The VB.NET code presented here may not be the best possible code. The intension of writing this code is to present the best code to perform the given operation. The idea is to present a basic code which WORKS!

Imports System.Data

Imports System.Data.SqlClient

Imports System.IO

Module Module1

Sub Main()

‘Define a connection string

Dim conStr As String

conStr = “Data Source=TOSHIBA-USER\SQL2005;Initial Catalog=DataTableTest;Integrated Security=True”

‘Create and open a new connection

Dim cn As New SqlConnection(conStr)

cn.Open()

‘Create a command to retrieve data from SP

Dim cmd As New SqlCommand(“GetEmployeeInfo”, cn)

cmd.CommandType = CommandType.StoredProcedure

‘Fill the DataSet

Dim da As New SqlDataAdapter(cmd)

Dim ds As New DataSet()

da.Fill(ds)

da.Dispose()

cmd.Dispose()

‘Access the first table

Dim dt As DataTable

dt = ds.Tables(0)

‘Create a stream object and Write the content of the DataTable

‘to it.

Dim s As New MemoryStream()

dt.WriteXml(s, True)

‘Retrieve the text from the stream

s.Seek(0, SeekOrigin.Begin)

Dim sr As New StreamReader(s)

Dim xmlString As String

xmlString = sr.ReadToEnd()

‘close

sr.Close()

sr.Dispose()

‘pass the XML data to sqlserver

cmd = New SqlCommand(“ProcessXml”, cn)

cmd.CommandType = CommandType.StoredProcedure

Dim p As SqlParameter

p = cmd.Parameters.AddWithValue(“@data”, xmlString)

p.SqlDbType = SqlDbType.Xml

cmd.ExecuteNonQuery()

cmd.Dispose()

‘Close connection

cn.Close()

cn.Dispose()

End Sub

End Module

Sorry, comments for this entry are closed at this time.