TableAdapter, stored procedure, optional parameters

[Dennis McCarthy]

I am trying to figure out how to get a TableAdpater to send the value
"default" rather than "NULL" as the value of a stored procedure parameter.

I have an existing stored procedure with optional parameters:

CREATE PROCEDURE sp_getPosition1Ctrl
@PersonIdNo int,
@PersonalityId int = 0,
@EffectDate datetime = NULL
AS ...

Using the Visual Studio 2008 SP1 dataset designer, I added a TableAdapter
for this stored procedure to a typed dataset. When I do "Preview Data..." on
the TableAdapter in the designer, and do not enter values for the optional
parameter, I see this call in a SQL Server Profiler trace:

exec dbo.sp_getPosition1Ctrl
@PersonIdNo=3817,@PersonalityId=default,@EffectDate=default

However, when I call the same TableAdapter method from my application, here
is what I see in the trace:

exec dbo.sp_getPosition1Ctrl
@PersonIdNo=NULL,@PersonalityId=NULL,@EffectDate=NULL

I need my application to send default rather than NULL, like the dataset
designer does.

The code generated by the designer explains this behavior:

if ((PersonIdNo.HasValue == true)) {
this.Adapter.SelectCommand.Parameters[1].Value =
((int)(PersonIdNo.Value));
}
else {
this.Adapter.SelectCommand.Parameters[1].Value =
global::System.DBNull.Value;
}
if ((PersonalityId.HasValue == true)) {
this.Adapter.SelectCommand.Parameters[2].Value =
((int)(PersonalityId.Value));
}
else {
this.Adapter.SelectCommand.Parameters[2].Value =
global::System.DBNull.Value;
}
if ((EffectDate.HasValue == true)) {
this.Adapter.SelectCommand.Parameters[3].Value =
((System.DateTime)(EffectDate.Value));
}
else {
this.Adapter.SelectCommand.Parameters[3].Value =
global::System.DBNull.Value;
}

If I change the code to set the parameter value to CLR null rather than
DBNull, then the table adapter does what I need:


else {
this.Adapter.SelectCommand.Parameters[3].Value = null;
}

I would really like to avoid modifying code that is generated by Visual
Studio. Is there any way to get the dataset code generator to use CLR null
rather than DBNull when the application does not provide a value for an
optional parameter?

Thanks,
Dennis