CODEWITHSUNDEEP
c#sql-serverstored-procedures
GThree
GThree

Reputation: 3502

Unable to insert null values in nullable column via stored procedure in C#

I have a SQL table where column is set to null. Now I am passing the null value from code via stored procedure and it throws an error which is, Procedure or function 'MyStoredProcedure' expects parameter '@Parameter', which was not supplied.

Now Confusing part for me is, if I add null condition and than pass DBNull.Value, it works fine but if I pass direct parameter which could be null than it throws error. I am trying to understand why? Am I missing something here?

Note: Just for information, passing parameter could be null based on the requirement.

Code:

string connString = _dbContext.Database.Connection.ConnectionString;
using (SqlConnection connection = new SqlConnection(connString))
{
    connection.Open();

    SqlCommand cmd = new SqlCommand("MyStoredProcedure", connection);
    cmd.CommandType = CommandType.StoredProcedure;

    scheduledEndDateTime = !string.IsNullOrEmpty(bulkUpdateSchedule.EndDate) && !string.IsNullOrEmpty(bulkUpdateSchedule.EndTime) 
    ? (DateTime?) 
    Convert.ToDateTime(bulkUpdateSchedule.EndDate + " " + bulkUpdateSchedule.EndTime)
    : null;

    //This Fails
    cmd.Parameters.AddWithValue("@scheduledEndDateTime", scheduledEndDateTime);

    //This Works
    if (scheduledEndDateTime != null)
    {
        cmd.Parameters.AddWithValue("@scheduledEndDateTime", scheduledEndDateTime);
    }
    else
    {
        cmd.Parameters.AddWithValue("@scheduledEndDateTime", DBNull.Value);
    }

    cmd.ExecuteReader();

    connection.Close();
}

Stored Procedure:

ALTER PROCEDURE [dbo].[InsertScheduledBulkUpdateRecords]
    @scheduledEndDateTime DATETIME,
AS
BEGIN
    SET NOCOUNT ON;
        INSERT INTO ScheduledBulkUpdate (ScheduledEndDateTime)
        VALUES (@scheduledEndDateTime)
END

Table Structure screen shot:

enter image description here

Upvotes: 3

Views: 1797

Answers (1)

NikolaiDante
NikolaiDante

Reputation: 18639

You've pretty much answered your own question null and DBNull.Value are different things. Where DBNull.Value means something to the database provider than null doesn't.

From MSDN on DBNull:

Do not confuse the notion of null in an object-oriented programming language with a DBNull object. In an object-oriented programming language, null means the absence of a reference to an object. DBNull represents an uninitialized variant or nonexistent database column.

Upvotes: 5

Related Questions