how to pass parameter used in userdefined datatype table in database

Question

I have created user defined data type and passing parameter ,how to pass that parameter in code using C# ,i'm facing issue while passing it is getting conversion failed from list to enumerable

eg:database server

ALTER procedure [dbo].[SpUpdateID] 
    @List AS dbo.STATUSID READONLY
AS
BEGIN
 SET NOCOUNT ON;
 Select ID from @List 
END

CREATE TYPE [dbo].[STATUSID] AS TABLE(
    [ID] [int] NULL
)
GO

code :

List<object> idValues = new List<object>();

string filename = exportPath + "Data_" + datetime + ".csv";
SqlCommand cmd = new SqlCommand("SpUpdateID", connect);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter valparam = new SqlParameter("@List", idValues); 
valparam.SqlDbType = SqlDbType.Structured;
valparam.TypeName = "dbo.STATUSID";
cmd.ExecuteNonQuery();

Tuhin Paul · Answer

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.