I was getting this exception earlier today:
Test method x threw exception: System.Data.SqlClient.SqlException: Procedure or function ‘sp_get_x’ expects parameter ‘@x’, which was not supplied…
I was 100% certain that the parameter was there, so I ran a SQL profile and noticed it was treating the cmd as plain text.
Turns out that I forgot to set the CommandType property to StoredProcedure.
List profileContacts = null;
using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
using (SqlCommand cmd = new SqlCommand("sp_get_x", conn))
using (DataSet ds = new DataSet())
using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
{
//cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@x", profiles.ToXml());
adapter.Fill(ds);
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
ProfileContactSlim profileContact = new ProfileContactSlim();
profileContact.Load(dr);
profileContacts.Add(profileContact);
}
}
}
return profileContacts;
Should be…
List profileContacts = null;
using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString))
using (SqlCommand cmd = new SqlCommand("sp_get_x", conn))
using (DataSet ds = new DataSet())
using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@x", profiles.ToXml());
adapter.Fill(ds);
if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
ProfileContactSlim profileContact = new ProfileContactSlim();
profileContact.Load(dr);
profileContacts.Add(profileContact);
}
}
}
return profileContacts;
Jon