is this a bad idea?

[matt.delvecchio]

hello,

i have a question.. in my data-access-layer, my app is submitting a
bunch of optional paramters to a stored proc for insertion. however, if
my user did not select a value for an optional paramter, i decided i
did not want to store an empty string default value -- i wanted to keep
NULL in the db column for un-edited values.

to this end, i wrote a small function that is used when populated a sql
paramter's value. the function either returns the user-edited value, or
dbnull.

my question...is there anything wrong w/ this practice? code is below.


thanks!
matt



/// <summary>
/// Helper function for use w/ database paramters -- if value is empty,
pass NULL to db
/// </summary>
private static object ReturnValueOrNull(object item)
{
switch (item.GetType().ToString())
{
case "System.String" :
string itemString = (string)item;
if(itemString != null && itemString != string.Empty)
return itemString;

break;

case "System.Int32" :
int itemInt = (int)item;
if(itemInt != -1)
return itemInt;

break;

case "System.Decimal" :
decimal itemDecimal = (decimal)item;
if(itemDecimal != -1)
return itemDecimal;

break;
}

return System.DBNull.Value;

}


....called like such:

//optional params
command.Parameters.Add("p_parentObjectID", OracleType.Number).Value =
ReturnValueOrNull(parentObjectID);
command.Parameters.Add("p_locationID", OracleType.VarChar).Value =
ReturnValueOrNull(locationID);
command.Parameters.Add("p_service", OracleType.VarChar).Value =
ReturnValueOrNull(service);

 

[Bob Lehmann]

Shoudn't your Store Procedure take care of default values?

Bob Lehmann

 

[matt.delvecchio]

depends on your point of view. some dbas dont want their procs doing
any verification, they want the business tier to do that and they want
the proc to just do insertions. so yes, the proc is set up w/ the
params as null (not required params), but my app code still has to
figure out what to do w/ the params values -- either pass them in, or
dont. so i figure i could either 1) use logic on every param and if its
a default value, dont include that param in the command. 2) use logic
on every param value and pass in null or the value. i still havent
decided which ill do.