I'm a big fan of ADO.net, which I'll discuss the pro's/con's of in another article.

From time to time I've found the need to pass a list of Id's to a sql stored procedure. Below I explain how to achieve this.

First create a user defined table type in sql server management studio. The user defined table type should have a single column: Id of type int. Name the user defined table type IntList

Next create your stored procedure with a parameter @Ids of type IntList and suffix it with READONLY as below. This parameter will be the user defined table type we created earlier.

Every repository in my data layer inherits from a base repository class. This allows for code reuse across all inheriting repositories. In the base repository I include the following code

internal DataTable CreateIDDataTable(List ids)
{
	DataTable result = new DataTable();

	try
	{
		result = new DataTable("[IDList]");
		result.Columns.Add("ID", typeof(Int32));

		for (Int32 i = 0; i < ids.Count; i++)
		{
			result.Rows.Add(ids[i]);
		}
	}
	catch (Exception ex)
	{
		LogError(ex);
	}

	return result;
}

Next I add the following parameter to my repository that needs to pass the Id list

query.Parameters.AddWithValue("@SupplierIDs", base.CreateIDDataTable(searchContext.SupplierIDs));

This calls the method in the base class and passes the resulting DataTable to the stored procedure as the parameter.