cmd.Parameters.AddWithValue nullable int

In my data layer class, I initialize a parameter like so:

private int? _DependencyID;

public int? DependencyID
{ get {return _DependencyID;} set {_DependencyID = value;} }

public ConstructorMethod()
{
    _DependencyID = (开发者_运维技巧int?)null;
}

In the class Insert() method, I am attempting

cmd.AddWithValue("@DependencyID", _DependencyID);

If _DependencyID has a value, all is well. If _DependencyID is null, I get error:

The parameterized query '(@param1(nvarchar(10), @param2(nvarchar(255), expects the parameter '@DependecyID", which was not supplied

I found this [article][1], so I tried adjusting code like so:

cmd.AddWithValue("@DependencyID", _DependencyID == null? DBNull.Value : _DependencyID); 

                           and
cmd.AddWithValue("@DependencyID", _DependencyID == null? (int?) DBNull.Value : _DependencyID); 

Either way, there are issues. How should I handle this?

Thanks in advance for any help

---

You need to add an object:

cmd.AddWithValue("@DependencyID", _DependencyID == null? DBNull.Value : (object)_DependencyID); 

You can shorten that to

cmd.AddWithValue("@DependencyID", (object)_DependencyID ?? DBNull.Value); 

---

I have created an extension to simply the code.

public static class SQLExtension
{
    public static SqlParameter AddWithNullable<T>(
        this SqlParameterCollection collection, 
        string parameterName, 
        Nullable<T> value) where T : struct, IComparable
    {
        return collection.AddWithValue(
            parameterName, (value.HasValue ? value.Value : (object)DBNull.Value)
        );
    }
}

Here is how you can use it.

cmd.AddWithNullable("@DependencyID", _DependencyID);

---

AFAIC, ADO.NET/SQL don't support nullable types this way.

The last example looks closest to correct to me. What kind of issues are you having with it? If you are just getting a compile error related to types, type cast your value to object:

(object)DependencyID.Value;

Since both values on either side of : must be of the same type.