Table-Value parameter used in .Net. Simple sample

 

Table-Value parameter used in .Net. Simple sample

Table-Value Parameters. One of those new things that I haven’t experimented with. Yet.

So, I had a little bit of spare time so I thought I’d give it a try, turns out that this is really simple.

Here is a really simple example on how to use them.

. First we will create a table. This will only be there in order to show that the TVP can be used in simple insert statement.

. Then we need to create a TYPE that is of the type TABLE, this is what is being used as the parameter to a stored procedure, hence Table-Value Parameter J

. Lastly we create a stored procedure that takes the table-value parameter as, you guessed it, a parameter. We will select out the rows from the parameter and insert them into the backing table.

So, fire up SSMS and connect to a SQL Server 2008 and run the following:

-- First we need a 'backing' table that we will insert data into

create table TVPTable (id int identity, fname nvarchar(20), lname nvarchar(20), age int, dateadded datetime)

go

-- Then we need to create our own type of type Table

create type TVPTableType as table (fname nvarchar(20), lname nvarchar(20), age int default(20))

go

-- And then a stored procedure that takes an table value parameter as argument

create procedure TVPProcedure @TVP TVPTableType readonly

as

insert into TVPTable (fname, lname, age, dateadded)

select fname, lname, age, GETDATE() from @TVP

go

-- and then just test it in T-SQL so that we know that it works

declare @TVPTest as TVPTableType

insert into @TVPTest(fname, lname) values ('Mike', 'Spike') -- omitting Age to show that deafult can be used.

exec TVPProcedure @TVPTest

-- select from the backing table.

select * from TVPTable

-- drop table TVPTable

-- drop type TVPTableType

-- drop procedure TVPProcedure

Now we will create the .Net client (C# / Console application in this case).

SqlClient allows you to populate TVPs from DataTable, DbDataReader or System.Collections.Generic.IList<SqlDataRecord> objects, we will create a local table and send that to the store procedure.

So, create a new C# console application and replace the main method with the following:

string cs = @"Data Source=<your SQL Server 2008>; Initial Catalog=<database>; Integrated Security=SSPI;";

try

{

using(SqlConnection con = new SqlConnection(cs))

{

con.Open();

// First print the rows in the table, if any.

SqlCommand cmd = new SqlCommand("SELECT * FROM TVPTable", con);

SqlDataReader rdr = cmd.ExecuteReader();

while(rdr.Read())

Console.WriteLine("{0} {1}",rdr["fname"], rdr["lname"]);

rdr.Close();

// Now create a local datatable, this will be used as argument to the sproc

DataTable dt = new DataTable("TableToInsert");

dt.Columns.Add("fname", typeof(string));

dt.Columns.Add("lname", typeof(string));

dt.Columns.Add("age", typeof(int));

// Insert some rows.

dt.Rows.Add(new object[] { "Paul", "Paulsson", 50 });

dt.Rows.Add(new object[] { "John", "Johnsson", 30 });

dt.Rows.Add(new object[] { "Fred", "Fredsson", 40 });

// Create a parameter, set it to be the datatable, then use it as argument for the stored procedure.

SqlParameter tvpParam = new SqlParameter();

tvpParam.ParameterName = "@TVP";

tvpParam.Value = dt;

tvpParam.SqlDbType = SqlDbType.Structured;

tvpParam.TypeName = "TVPTableType";

SqlCommand tvpcmd = new SqlCommand("TVPProcedure", con);

tvpcmd.CommandType = CommandType.StoredProcedure;

tvpcmd.Parameters.Add(tvpParam);

tvpcmd.ExecuteNonQuery();

// And rerun the first query to show rows, should show the inserted rows.

Console.WriteLine("\nRun SELECT * again, new rows should be displayed");

rdr = cmd.ExecuteReader();

while (rdr.Read())

Console.WriteLine("{0} {1}", rdr["fname"], rdr["lname"]);

rdr.Close();

con.Close();

}

}

catch(Exception e)

{

Console.WriteLine(e);

}

Run and you should see that the table used as argument has been read in the stored procedure and in that, taken the rows and inserted them into the backing table.

As you can see, this is very easy to use.

More info on this here:

"Table-Valued Parameters in SQL Server 2008 (ADO.NET)"

http://msdn.microsoft.com/en-us/library/bb675163.aspx

"Table-Valued Parameters (Database Engine)"

http://msdn.microsoft.com/en-us/library/bb510489.aspx

"Microsoft Access Team Blog"

http://blogs.msdn.com/access/archive/2009/01/28/using-sql-server-2008-table-valued-parameters-tvps-from-access.aspx

Published Friday, March 20, 2009 2:51 PM by maspeng

Common tips and tricks from a SQL Developer Support perspective : Table-Value parameter used in .Net. Simple sample