how to pass parameter used in userdefined datatype table in database

Hello Meghana,

To pass a user-defined table type parameter to a stored procedure in C#, you need to create a DataTable object, fill it with the data that you want to pass, and then use it to create a SqlParameter object with SqlDbType.Structured. Here's an example of how you can modify your code to pass a list of integer IDs to the stored procedure:

// Create a DataTable to hold the IDs
DataTable idTable = new DataTable();
idTable.Columns.Add("ID", typeof(int));
foreach (int id in idValues)
{
    DataRow row = idTable.NewRow();
    row["ID"] = id;
    idTable.Rows.Add(row);
}

// Set up the SqlCommand object
SqlCommand cmd = new SqlCommand("SpUpdateID", connect);
cmd.CommandType = CommandType.StoredProcedure;

// Create the SqlParameter object and set its properties
SqlParameter param = cmd.Parameters.AddWithValue("@List", idTable);
param.SqlDbType = SqlDbType.Structured;
param.TypeName = "dbo.STATUSID";

// Execute the stored procedure
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
    // Read the result from the SqlDataReader
    int id = reader.GetInt32(0);
    // Do something with the result...
}
reader.Close();

We create a DataTable object idTable with a single column "ID" of type int, and populate it with the values from the idValues list using a foreach loop. We then create a SqlParameter object param with the name "@List", set its SqlDbType to SqlDbType.Structured, and its TypeName to "dbo.STATUSID" (the name of the user-defined table type). We execute the stored procedure using cmd.ExecuteReader() and read the results using a SqlDataReader object.

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Tuhin Paul3y ago
Hello Meghana,

To pass a user-defined table type parameter to a stored procedure in C#, you need to create a DataTable object, fill it with the data that you want to pass, and then use it to create a SqlParameter object with SqlDbType.Structured. Here's an example of how you can modify your code to pass a list of integer IDs to the stored procedure:
```
// Create a DataTable to hold the IDs
DataTable idTable = new DataTable();
idTable.Columns.Add("ID", typeof(int));
foreach (int id in idValues)
{
    DataRow row = idTable.NewRow();
    row["ID"] = id;
    idTable.Rows.Add(row);
}

// Set up the SqlCommand object
SqlCommand cmd = new SqlCommand("SpUpdateID", connect);
cmd.CommandType = CommandType.StoredProcedure;

// Create the SqlParameter object and set its properties
SqlParameter param = cmd.Parameters.AddWithValue("@List", idTable);
param.SqlDbType = SqlDbType.Structured;
param.TypeName = "dbo.STATUSID";

// Execute the stored procedure
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
    // Read the result from the SqlDataReader
    int id = reader.GetInt32(0);
    // Do something with the result...
}
reader.Close();
```
We create a DataTable object idTable with a single column "ID" of type int, and populate it with the values from the idValues list using a foreach loop. We then create a SqlParameter object param with the name "@List", set its SqlDbType to SqlDbType.Structured, and its TypeName to "dbo.STATUSID" (the name of the user-defined table type). We execute the stored procedure using cmd.ExecuteReader() and read the results using a SqlDataReader object.