How to set Default Value as Value of SqlCommand SqlParameter?
SqlCommand is the class contained in System.Data.SqlClient
My Schema does not accept DBNull.Value as value
SqlCommand command = new SqlCommand();
command.Parameters.Add(new SqlParameter("column_1", SqlDbType.VarChar, 1) { Value = DBNull.Value });
As lboshuizen points out, if your "schema" isn't taking null, or the parameter of the stored procedure or query cannot be null, trying to give it a null value will likely fail. In this case, you need to see why you are trying to set it to null if you know it doesn't accept it. I would surmise an empty string as opposed to null would work, or something else that is sensible.
The SqlParameterCollection property Parameters has an AddWithValue method:
You can simply insert null and that will be handled for you:
command.Parameters.AddWithValue("@parameter", null);
If you cannot provide a null, you can provide something like an empty string:
command.Parameters.AddWithvalue("@parameter", "");
You also very rarely need to specify the data type using this mechanism.
DBNull.Value and one wonders why he is trying.
If the schema doesn't accept null it indicates that the column is mandatory and requires a sensible value.
Using a default value for a column is a property of the schema.
ALTER TABLE {TABLENAME}
ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL}
CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE}
const string default = "unknown";
...
SqlCommand command = new SqlCommand();
command.Parameters.Add(new SqlParameter("column_1", SqlDbType.VarChar, 1)
{ Value = default });
Note: Changing the schema to accept NULL is considered cheating :-)
