SQL Server 2008 Table Valued Parameters

• Download source code - 4.2 KB

Introduction

The last two weeks, I have been working on developing bulk data manipulations. And even before that, I always wondered “What's the best way to pass an array of values into a SQL Server Stored Procedure?” One option I found was OpenXML, which I blogged about here.

I am trying out Visual Studio 2008 and SQL Server 2008 these days. And, last night, I found out that SQL Server now supports table valued parameters which allow us to send data tables as parameters to Stored Procedures. It still uses the same ADO.NET API.

How it works

Now I’m going to show how it works. For this, I’m using the same scenario which I used in my previous post (reading contents of a folder and saving their information). Here, I have used the SQL Server 2008 November CTP and the Visual C# 2008 Express edition.

First, create the table given below:

CREATE TABLE FileDetails(
FileName varchar(50) PRIMARY KEY,
CreatedDate varchar(50) ,
Size  decimal(18, 0) )  

Then, we should declare a new table User Defined Type in the database:

create type FileDetailsType as table
(
    FileName        varchar(50),
    CreatedDate        varchar(50),
    Size       decimal(18,0)
)

Then, create a Stored Procedure that gets a parameter of the above type, and insert several rows in a single command.

create procedure InsertFileDetails
(
    @FileDetails FileDetailsType readonly
)

as
insert into  
    FileDetails (FileName, CreatedDate, Size)
        select FileName, CreatedDate, Size
        from  
    @FileDetails; 

To execute this procedure, we can create a data table and add the rows into it. Then pass this data table as a parameter to the database.

private static void SaveFileDetail(List<FileInfo> info)
{ 
    Console.WriteLine("**********updating with tablevalued parameters****");
    DataTable dt = preparedatatable();
    foreach (FileInfo file in info)
    {
        DataRow dr = dt.NewRow();
        dr[0] = file.Name;
        dr[1] = file.CreationTime.ToShortDateString();
        dr[2] = (decimal)file.Length;
        dt.Rows.Add(dr);
    }

    using(SqlConnection conn = new SqlConnection("your connection string"))
    {
        SqlCommand cmd = conn.CreateCommand();
        cmd.CommandType = System.Data.CommandType.StoredProcedure;
        cmd.CommandText = "dbo.InsertFileDetails";
        SqlParameter param = cmd.Parameters.AddWithValue("@FileDetails", dt);
        conn.Open(); 
        cmd.ExecuteNonQuery();
        Console.WriteLine("Completed Updating the database");
    }

Now that’s cool, isn’t it?