18.03.2009

Table Valued Parameters in SQL Server 2008

In my previous post i mentioned new insert syntax and it’s execution plan. In this post i’ll demonstrate how to write a simple c# application that inserts multiple rows into a table.

Table-Valued parameters are new and to develope compatible applications with SQL Server 2008 you have install .NET Framework 3.5  service pack 1. The use of table-valued parameters is inserting multiple rows into sql server 2008, so  it mostly looks like arrays in a high level programming language.

Sample

  1. Create a table type in SQL Server 2008

 

 
if exists(select * from sys.tables where name ='Tag')
  drop table Tag
go
create table Tag
(
  TagId  uniqueidentifier not null primary key  ,
  Name  varchar(255)              ,
)  
if exists(select * from sys.types where name = 'typTagTable')
  drop type typTagTable
go
create type  typTagTable 
  as Table 
  (
    TagId uniqueidentifier,
    Name varchar(255 )  
  )
  
go

2. Create  Stored Procedure but be careful, the table-valued paramter must be readonly for immutability

if exists(select * from sys.procedures where name = 'procInsertMultipleTag')
  drop proc procInsertMultipleTag
go
create procedure procInsertMultipleTag
(
  @tags  typTagTable readonly
)
as
insert into Tag
  select TagId,Name from @tags
    where  Name not in(select Name from @Tags)
go

3. Write your .NET Code in this step be carefull you must set parameter type as struct, cause every table type is different each other, so .NET Framework team makes it easy for theirselves and us.

    static void Main(string[] args)
    {
      DataTable tagTable = new DataTable("Tags");
      tagTable.Columns.Add("TagId", typeof (Guid));
      tagTable.Columns.Add("Name", typeof(string));
      tagTable.Rows.Add(Guid.NewGuid(), "Table Valued Parameters");
      tagTable.Rows.Add(Guid.NewGuid(), "SQL Server 2008");
      using(SqlConnection con = new SqlConnection("data source=localhost;initial catalog=BlogDB;integrated security=sspi"))
      using (SqlCommand cmd = new SqlCommand("procInsertMultipleTag", con))
      {
        cmd.Parameters.AddWithValue("@tags", tagTable);
        cmd.Parameters["@tags"].SqlDbType = SqlDbType.Structured;
        cmd.CommandType = CommandType.StoredProcedure;
        con.Open();
        cmd.ExecuteNonQuery();
        con.Close();
      }

}


Hiç yorum yok: