CODEWITHSUNDEEP
c#entity-frameworkstored-procedures
solmaz ahamdi
solmaz ahamdi

Reputation: 150

Execute stored procedure with null values

The following stored procedure has been created in the database :

@se_code_melli varchar(10) = NULL,
@se_name nvarchar(30) = NULL,
@se_family nvarchar(30) = NULL,
@se_name_uni nvarchar(100) = NULL,
@se_name_reshte_tahsili nvarchar(50) = NULL

With the commands below, I want to run stored procedure from my C# program :

if (comboBox11.SelectedIndex == -1)
{
    comboBox11.SelectedValue = DBNull.Value;
}

if (comboBox12.SelectedIndex == -1)
{
    comboBox12.SelectedValue = DBNull.Value;
}

sqlParams = new SqlParameter[]
        {
            new SqlParameter { ParameterName = "@se_code_melli",  Value = (String.IsNullOrEmpty(textBox23.Text) ? DBNull.Value : (object)textBox23.Text)},
            new SqlParameter { ParameterName = "@se_name",  Value = (String.IsNullOrEmpty(textBox22.Text) ? DBNull.Value : (object)textBox22.Text)},
            new SqlParameter { ParameterName = "@se_family",  Value = (String.IsNullOrEmpty(textBox21.Text) ? DBNull.Value : (object)textBox21.Text)},
            new SqlParameter { ParameterName = "@se_name_uni",  Value =comboBox11.SelectedValue},
            new SqlParameter { ParameterName = "@se_name_reshte_tahsili",  Value =comboBox12.SelectedValue}
        };

using (SamenEntities dbContext = new SamenEntities())
{
    dataGridView1.DataSource = dbContext.Database.SqlQuery<SamenEntities>("storedProcedureName", sqlParams).SingleOrDefault();
}

But I get the following error:

System.Data.SqlClient.SqlException: 'The parameterized query '(@se_code_melli nvarchar(10),@se_name nvarchar(4000),@se_family ' expects the parameter '@se_name_uni', which was not supplied.'

I tried to solve the NULL problem, but I did not succeed.

Upvotes: 1

Views: 1120

Answers (1)

Marc Gravell
Marc Gravell

Reputation: 1064184

ADO.NET has a really really odd feature: if a parameter has the value null, it isn't sent. This confuses everyone. Instead, you must manually avoid adding null, and add DBNull.Value instead.

Clearly your comboBox11.SelectedValue = DBNull.Value; attempt didn't work, so : set it at the ADO.NET level instead.

For example:

new SqlParameter {
    ParameterName = "@se_name_uni",
    Value = (object)comboBox11.SelectedValue ?? DBNull.Value
},

Alternatively, most ORM or micro-ORM tools will know to do this for you automatically.

Upvotes: 4

Related Questions