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();
		}
	}
}

Leave a Reply

Your email address will not be published. Required fields are marked *


Fatal error: Uncaught GuzzleHttp\Exception\ClientException: Client error: `POST https://dc.services.visualstudio.com/v2/track` resulted in a `400 Invalid instrumentation key` response: {"itemsReceived":1,"itemsAccepted":0,"errors":[{"index":0,"statusCode":400,"message":"Invalid instrumentation key"}]} in D:\home\site\wwwroot\wp-content\plugins\application-insights\vendor\guzzlehttp\guzzle\src\Exception\RequestException.php:113 Stack trace: #0 D:\home\site\wwwroot\wp-content\plugins\application-insights\vendor\guzzlehttp\guzzle\src\Middleware.php(66): GuzzleHttp\Exception\RequestException::create(Object(GuzzleHttp\Psr7\Request), Object(GuzzleHttp\Psr7\Response)) #1 D:\home\site\wwwroot\wp-content\plugins\application-insights\vendor\guzzlehttp\promises\src\Promise.php(203): GuzzleHttp\Middleware::GuzzleHttp\{closure}(Object(GuzzleHttp\Psr7\Response)) #2 D:\home\site\wwwroot\wp-content\plugins\application-insights\vendor\guzzlehttp\promises\src\Promise.php(156): GuzzleHttp\Promise\Promise::callHandler(1, Object(GuzzleHttp\P in D:\home\site\wwwroot\wp-content\plugins\application-insights\vendor\guzzlehttp\guzzle\src\Exception\RequestException.php on line 113