SqlConnection sqlConnection1 = new SqlConnection("connectionstring"); sqlConnection1.Open(); var sqlCommand = new SqlCommand(); sqlCommand.Connection = sqlConnection1; sqlCommand.CommandType = CommandType.StoredProcedure; var idList = new List<int> { 17, 55}; var idParameterList = new List<string>(); var index = 0; foreach (var id in idList) { var paramName = "@idParam" + index; sqlCommand.Parameters.AddWithValue(paramName, id); idParameterList.Add(paramName); index++; } sqlCommand.CommandText = String.Format("displayInfo",string.Join(",", idParameterList)); //displayinfo is Stored Procedeure Name using (SqlDataReader sqlReader = sqlCommand.ExecuteReader()) { var resultTable = new DataTable(); resultTable.Load(sqlReader); // Do something with the result table } sqlConnection1.Close();
alter PROCEDURE displayInfo @idParam varchar(max) AS BEGIN SELECT * FROM Table WHERE idParam IN (@idParam); END GO
You should use the following syntax for calling store procedure sqlCommand.CommandType = CommandType.StoredProcedure; sqlCommand.CommandText = "displayInfo"; // Here you could use your list and loop sqlCommand.Parameters["idParam0"].Value = "17"; sqlCommand.Parameters["idParam1"].Value = "55";
CommandText
sqlCommand.CommandText = "displayInfo";
var
This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)