How to pass a DataTable as table parameter to stored procedure

SQL , C# :pass data table as table parameter to stored procedure

here is my references:

http://www.codeproject.com/KB/database/sqlserver2008.aspx

http://www.codeproject.com/KB/cs/CSharpAndTableValueParams.aspx

--This is the database table

CREATE TABLE dbo.SampleTable

(

Id int NOT NULL IDENTITY (1, 1),

SampleString nvarchar(64) NOT NULL,

SampleInt int NULL

) ON [PRIMARY]

-- 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 -- Having one String

, SampleInt Int -- and one int

)

--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

)

AS

Begin

-- 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

End

The DB Test Script

-- This is the sample script to test the SP

-- An instance of the Table parameter type is created

Declare @SampelData As [dbo].[SampleDataType]

-- and then filled with the set of values

Insert Into @SampelData(SampleString, SampleInt) Values('1',1);

Insert Into @SampelData(SampleString, SampleInt) Values('2',null);

Insert Into @SampelData(SampleString, SampleInt) Values('3',3);

Select * From @SampelData

-- we then call the SP to store the values

Exec SampleProcedure @SampelData

Select * From SampleTable

The C# Code

Representing the Data

//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

SqlParameter parameter = new SqlParameter(); 

//The parameter for the SP must be of SqlDbType.Structured 

parameter.ParameterName="@Sample"; 

parameter.SqlDbType = System.Data.SqlDbType.Structured; 

parameter.Value = dataTable; 

 command.Parameters.Add(parameter);