Passing NULL Values to a SQL Stored Procedure from .NET Core

I’ve previously posted about how to cope with nullable values retrieved from a SqlDataReader but it’s also necessary to account for nulls when passing values through to a SQL operation such as a stored procedure.

sp__set_completed.sql

If you want you’re stored procedure to accept nulls then you first need to specify this in your input variables.

create procedure
	sp__set_completed
	(
	@id int,
	@completed bit = null
	) as
update
	product
set
	completed = @completed
where
	id = @id
go

Product.cs

The nullable value should also obviously be declared as such in the model.

public class ProductAttributes
{
	public int Id { get; set; }

	public bool? Completed { get; set; }
}

Usage

public static Object SafeDbObject(Object input)
{
	if (input == null)
	{
		return DBNull.Value;
	}
	else
	{
		return input;
	}
}

public static async Task SetCompleted(Product product, string connectionString)
{
	using (SqlConnection connection = new SqlConnection(connectionString))
	{
		using (SqlCommand command = new SqlCommand("sp__set_completed"))
		{
			command.CommandType = System.Data.CommandType.StoredProcedure;
			command.Parameters.AddWithValue("@id", product.Id);
			command.Parameters.AddWithValue("@completed", SafeDbObject(product.Completed));

			command.Connection = connection;

			connection.Open();
			await command.ExecuteNonQueryAsync();
			connection.Close();
		}
	}
}