Default Equivalent to DBNull.Value

Question

I am trying to pass default to SQL Server in c#.

This code passes null. Is there any way to pass default?

foreach (SqlParameter Parameter in command.Parameters)
{
    if (Convert.ToString(Parameter.Value) == "")
    {
        Parameter.Value = DBNull.Value;
    }
}

Is this kind of what you mean? [http://stackoverflow.com/questions/1810638/optional-parameters-in-sql-server-stored-proc](http://stackoverflow.com/questions/1810638/optional-parameters-in-sql-server-stored-proc) By assigning the parameter in the stored procedure, you don't even have to pass that parameter if you want to use the default value you assigned it in the procedure definition. — sparky68967, Feb 14 '13 at 21:44
Thanks for the response. Not really what I meant, though. I am looking to pass the SQL reserve word 'default' from c# somehow. I don't really know how to do it, and there is no DBDefault.Value (because that, of course, would be too easy). I also don't know if it's possible. I've been researching it for a couple hours. — Ian Best, Feb 14 '13 at 21:48

score 5 · Answer 1 · answered Feb 14 '13 at 21:51

5

Either don't add the parameter, or add the parameter but set the value to null (not DBNull.Value). Both have the same effect: the parameter is not passed, therefore the default is used.

answered Feb 14 '13 at 21:51

Marc Gravell

1,026,079
266
2,566
2,900

Like this: Parameter.Value = null; ? – Ian Best Feb 14 '13 at 21:57
I got this error message: PS_DOC_HDR insertion failed. Error: System.Data.SqlClient.SqlException (0x80131904): Cannot insert the value NULL into column 'RS_STAT', table 'TestGolf846.dbo.PS_DOC_HDR'; column does not allow nulls. INSERT fails. By the way, 'RS_STAT' has a default value in SQL of 1. Let me know what you think. Thanks! – Ian Best Feb 18 '13 at 14:18
@IanBest the "default" here only goes as far as the parameter value. There is no such thing as a value that means "use the underlying table's default value". Presumably, the *parameter*'s default value here is: `null`. So your insert statement has tried to insert `null` into a column that *doesn't allow null*. Have you tried making the parameter's default value mimic the column's default? for example `@foo int = 1` ? – Marc Gravell Feb 18 '13 at 14:28
I got it all figured out using an entirely different approach. I don't think what I was looking to do is possible in c#. Thank you for your time, though. – Ian Best Feb 18 '13 at 18:15

score 2 · Answer 2 · answered Feb 14 '13 at 21:45

2

You can set the parameter default in SQL-Server (Stored Procedure...)

(@parm varchar(5) = null)
AS
Begin...

And then don't pass the parameter from C# at all.

answered Feb 14 '13 at 21:45

Chains

12,541
8
45
62

The procedure is already stored, I just want to be able to handle defaults at the SQL level without having to really deal with it in my code. Right now, I am using individual if statements to set all the values to defaults. – Ian Best Feb 14 '13 at 21:57
Default value could be established at different levels. At the table design level, which you would use by not setting or changing a value for the field; at the stored procedure level, in which case you need to say `@parm = null`, and then don't pass the parameter from your code; or else in your code, by creating a constant (or whatever) with the value you wanted to use as your default. – Chains Feb 14 '13 at 22:06
Thanks, I guess I'll just have to stick with what I'm doing. I appreciate the help. – Ian Best Feb 18 '13 at 14:27

Chains · Accepted Answer · 2013-02-19T17:17:48.357

Here is another solution of sorts -- it appears to search the stored procedure for parameter default values (literally, a pattern search for the assignment in the declaration of the variable), and return any default values that are found. So...you could call this procedure from your c# app to get all of your default values, assign them to local C# vars, and then use them when you call your other procedure.

http://www.codeproject.com/Articles/12939/Figure-Out-the-Default-Values-of-Stored-Procedure

And here is how you could find the default value on a table itself: (you might need to strip the parenthesis off the returned value)

SELECT COLUMN_DEFAULT, replace(replace(COLUMN_DEFAULT,'(',''),')','') as DefaultWithNoParenthesis
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'WHATEVER'

Good luck!

Default Equivalent to DBNull.Value

3 Answers3