null valued parameters vs. not supplying parameters on execute() of stored procedure

[GoogleCodeExporter]

Running Dapper 1.6 from NuGet.

Most of my stored procedures define default values at the top. e.g:

CREATE PROCEDURE dbo.Test
(
   @contactId int,
   @isactive bit = 1
)
...  do something... usually update/insert/delete

however, when using dapper and connection.Execute, if I do the following:

var p = new DynamicParameters();
p.Add("@contactid",contact.Id);
p.Add("@isactive",contact.IsActive);

int count = cn.Execute("Test",p, commandType: CommandType.StoredProcedure);

I get a message back from my database that...

"Cannot insert the value NULL into column 'IsActive', table 
'myDB.dbo.Contacts'; column does not allow nulls. INSERT fails.The statement 
has been terminated"

So I have to change my code to:

if (contact.IsActive.HasValue) p.Add("@isActive",contact.IsActive.Value);

So that it only adds the parameter if it has a value.  Which isnt a huge deal, 
but it doesn't seem to be consistent with how I recall the default 
SqlConnection and SqlCommand objects behave when you add a parameter with a 
null value.  They just ignore it (or whatever needs to happen) so the database 
uses the default as defined in the procedure.

So, I'm not sure if it's something in the implementation when it hits the 
database or when the parameters are being enumerated, but I think for 
consistency and ultimately for ease of use, it'd be a great addition.

Thanks,

Jorin

Original issue reported on code.google.com by jslayba...@gmail.com on 29 Jul 2011 at 9:18

[GoogleCodeExporter]

The null vs DBNull issue is a constant cause of confusion; however, *generally* 
if people say `null` in C# they intend `null` in SQL. This is the approach that 
dapper adopts. Since you are using `DynamicParameters`... maybe just don't add 
it? 

Note that *in the general case*, `new {contactid=contact.Id, 
isactive=contact.IsActive}` would be easier, but in this example 
`DynamicParameters` is perhaps useful. 

Original comment by marc.gravell on 29 Jul 2011 at 9:51