Solution to SqlCommand "expects parameter" Exception

2008-09-12

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

TechBugs