CODEWITHSUNDEEP
.netsql-serverstored-proceduresparametersnullable
odiseh
odiseh

Reputation: 26547

.net: How to execute a stored procedure with a nullable parameter in c#?

How to execute a stored procedure with a nullable parameter in c#?

EDIT:

Actually, I've written below code. As you can see, status parameter is a nullable value type. Is it correct? or not?

public void LoadAll(DataTable tb, int? status=null)
    {
        try
        {
            using (SqlConnection connection = new SqlConnection())
            {
                connection.ConnectionString = this.connectionString;

                using (SqlCommand command = connection.CreateCommand())
                {
                    command.CommandType = CommandType.StoredProcedure;
                    command.CommandText = "USP_OrganizationChartSelect";
                    SqlCommandBuilder.DeriveParameters(command);
                    command.Parameters["@Status"].Value = status.HasValue ? status : null;
                    if (connection.State != ConnectionState.Open)
                        connection.Open();
                    SqlDataAdapter adapter = new SqlDataAdapter(command);
                    tb.Clear();
                    adapter.Fill(tb);
                    adapter.Dispose();
                    adapter = null;
                }
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

Thanks

Upvotes: 3

Views: 2081

Answers (3)

Eddie Groves
Eddie Groves

Reputation: 34888

You could try DBNull.Value instead of null or omit the parameter when the nullable has no value entirely.

Upvotes: 4

masoud ramezani
masoud ramezani

Reputation: 22950

you can check the nullable value in your sp like below:

select * from table
where (code = @code or @code is null)

by this solution if @code be null then this condition will not apply.

Upvotes: 0

this. __curious_geek
this. __curious_geek

Reputation: 43217

If the parameter is nullable, it must be a value type. Suppose you have an int nullable parameter, then you can pass it this way..

  int? nullableParam = null;
  nullableParam = 10; //set the value if any
  //Pass nullableParam to your sp call.

Upvotes: 0

Related Questions