Step 1

Create a table:

  1. create table TableTest
  2. (
  3. ID INT IDENTITY(1,1) PRIMARY KEY
  4. ,NAME VARCHAR(10)
  5. ,ADDR VARCHAR(10)
  6. )
Step 2

Create table types:
  1. CREATE TYPE TABLETEST AS TABLE
  2. (ID INT)
Step 3

Insert some value:
  1. INSERT INTO dbo.TABLETEST VALUES ('SIBI', 'ABCD')
  2. INSERT INTO dbo.TABLETEST VALUES ('SIBEESH', 'EFGH')
Step 4

Create a procedure with table as parameter:
  1. CREATE PROCEDURE SELECTTABLETEST
  2. (
  3. @TABVAR TABLETEST READONLY
  4. )
  5. AS
  6. BEGIN
  7. SELECT * FROM TABLETEST WHERE ID IN (SELECT ID FROM @TABVAR)
  8. END
C# Function
  1. private void passindDataTableToProcedure()
  2. {
  3. try
  4. {
  5. DataTable dt = new DataTable();
  6. dt.Columns.Add("ID");
  7. var dr = dt.NewRow();
  8. dr["ID"] = 1;
  9. dt.Rows.Add(dr);
  10. using (SqlConnection cn = new SqlConnection(@"Data Source=SIBEESH\SQLEXPRESS;Initial Catalog=Task;Integrate
  11. d Security=True"))
  12. {
  13. if (cn.State == ConnectionState.Open)
  14. cn.Close();
  15. cn.Open();
  16. using (SqlCommand cmd = new SqlCommand("SELECTTABLETEST"))
  17. {
  18. cmd.CommandType = CommandType.StoredProcedure;
  19. cmd.Connection = cn;
  20. cmd.Parameters.AddWithValue("@TABVAR", dt);
  21. using(SqlDataAdapter da=new SqlDataAdapter(cmd))
  22. {
  23. da.Fill(dt);
  24. }
  25. }
  26. }
  27. }
  28. catch
  29. {
  30. }
  31. }
Hope someone found it useful :)