Click here to Skip to main content
Click here to Skip to main content

SQL Server 2008 Table Valued Parameters

, 23 Dec 2007
Rate this:
Please Sign up or sign in to vote.
Using SQL Server 2008 table valued parameters to insert multiple records (VS 2008).

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?

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

mfmaneef
Software Developer
Sri Lanka Sri Lanka
i work for as a c# software developer, and experienced with c# 2.0/3.0,vb.net,asp.net,ado.net sqlserver,mysql, xml, xsl,

Comments and Discussions

 
GeneralThanks PinmemberDavidLieu9-Dec-09 11:49 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web02 | 2.8.140827.1 | Last Updated 23 Dec 2007
Article Copyright 2007 by mfmaneef
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid