C# and Sql Table Value Parameters

The Database Table

--This is the database table
CREATE TABLE dbo.SampleTable
Id int NOT NULL IDENTITY (1, 1),
SampleString nvarchar(64) NOT NULL,
SampleInt int NULL

The Table Value Parameter Type 

-- Create a table data type
CREATE TYPE [dbo].[SampleDataType] As Table
--This type has structure similar to the DB table 
SampleString Nvarchar(64) Not Null 
, SampleInt Int

The Stored Procedure

--This is the Stored Procedure
CREATE PROCEDURE [dbo].[SampleProcedure]
-- which accepts one table value parameter.
-- It should be noted that the parameter is readonly
@Sample As [dbo].[SampleDataType] Readonly

-- We simply insert values into the DB table from the parameter
-- The table value parameter can be used like a table with only read rights

Insert Into SampleTable(SampleString,SampleInt)
Select SampleString, SampleInt From @Sample

Representing the Data in C#

//To represent the table parameter in C#, we need to either 
//have a set of entities which are IEnumreable 
//or a data reader or a Data table.
//In this example we create a data table with same name as the type we have in the DB 
DataTable dataTable = new DataTable("SampleDataType"); 
//we create column names as per the type in DB 
dataTable.Columns.Add("SampleString", typeof(string)); 
dataTable.Columns.Add("SampleInt", typeof(Int32)); 
//and fill in some values 
dataTable.Rows.Add("99", 99); 
dataTable.Rows.Add("98", null); 
dataTable.Rows.Add("97", 99); 

Passing the Data from C#

 SqlParameter parameter = new SqlParameter(); 
//The parameter for the SP must be of SqlDbType.Structured 
parameter.SqlDbType = System.Data.SqlDbType.Structured; 
parameter.Value = dataTable; 
Please note that the above code is developed in Visual Studio 2010 + 
SQL Server 2008 however it will work with Visual Studio 2005 and SQL Server 2008. 


Post a Comment

Related Posts Plugin for WordPress, Blogger...