Passing a Data Table to a SQL Server 2005 Stored Procedure

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

Through different web development courses, one learns the importance of a web host that provides linux hosting as well as backup software. The courses also emphasize upon need of wireless internet providers and search engine services.