how to pass datatable parameter to stored procedure sql server?

Question

i need to pass data table to the following stored procedure

 create procedure insert_data
(@a int ,
@b DataTable)
as 
Begin 
......
end

i use C#

Answer 1

You will need to do a couple of things to get this going, since you want to pass a table as a parameter you need to create a (1) Table Type and (2) make your store procedure accept a parameter of that type.

Following are the steps required to create a TABLE TYPE .

TABLE TYPE

CREATE TYPE dbo.DataTable AS TABLE 
 (
    -- define table structure here
  )
 GO

Procedure

Now make you procedure accept a parameter of that table type.

create procedure insert_data
@a int ,
@b dbo.DataTable READONLY    --<-- Note this is read only param
as 
Begin 
......
end

This passed param will be read-only param, so if you need to manipulate the values passed in this param you will need to get these values in a table variable or temp table inside you procedure before you can do any update or insert operations on them.

Consuming C#

You can make use of DataTable class to create a new instance of a that type which matches the you table type in sql server. something like this..

DataTable dt = new DataTable("DataTable"); 

// Add columns to this object same as the type in sql server

dt.Columns.Add("Column1", typeof(string)); 
dt.Columns.Add("Column2", typeof(Int32)); 

//Populate the dt object 

dt.Rows.Add("Value1", 1); 
dt.Rows.Add("Value2", 2); 
dt.Rows.Add("Value2", 3);