Passing a DataTable to a Stored Procedure

Step 1

Create a table:

create table TableTest
(
ID INT IDENTITY(1,1) PRIMARY KEY
,NAME VARCHAR(10)
,ADDR VARCHAR(10)
)

Step 2

Create table types:

CREATE TYPE TABLETEST AS TABLE
(ID INT)

Step 3

Insert some value:

INSERT INTO dbo.TABLETEST VALUES ('SIBI', 'ABCD')
INSERT INTO dbo.TABLETEST VALUES ('SIBEESH', 'EFGH')

Step 4

Create a procedure with table as parameter:

CREATE PROCEDURE SELECTTABLETEST
(
@TABVAR TABLETEST READONLY
)
AS
BEGIN
SELECT * FROM TABLETEST WHERE ID IN (SELECT ID FROM @TABVAR)
END

C# Function

private void passindDataTableToProcedure()
{
   try
   {
      DataTable dt = new DataTable();
      dt.Columns.Add("ID");
      var dr = dt.NewRow();
      dr["ID"] = 1;
      dt.Rows.Add(dr);
      using (SqlConnection cn = new SqlConnection(@"Data Source=SIBEESH\SQLEXPRESS;Initial Catalog=Task;Integrate
      d Security=True"))
      {
         if (cn.State == ConnectionState.Open)
         cn.Close();
         cn.Open();
         using (SqlCommand cmd = new SqlCommand("SELECTTABLETEST"))
         {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Connection = cn;
            cmd.Parameters.AddWithValue("@TABVAR", dt);
            using(SqlDataAdapter da=new SqlDataAdapter(cmd))
            {
               da.Fill(dt);
            }
         }
      }
   }
   catch
   {
   }
}

Hope someone found it useful :)