Getting C# To Insert Defaults Into SQL Server

Question

How can I INSERT values into SQL Server that are stored in a string[] such that some of the values should be disregarded in favor of the values stored in SQL Server as default constraints on the table? What do I need to pass(e.g. NULL or something else) to use those defaults?

Currently, I add all the defaults in my code, and it is getting bulky.

Below is my code:

if (value == "") value = "0";

string insert = "INSERT INTO " + table + " (" + columns + ") VALUES (" + atColumns + ")";

using (SqlConnection connect = new SqlConnection(connection))
{                               
    using (SqlCommand command = new SqlCommand(insert, connect))
    {
        //adds values to corresponding parameters
        for (int i = 0; i < table_cols.Length; i++)
        {
            command.Parameters.AddWithValue("@" + table_cols[i], table_vals[i]);
        }

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

        connect.Open();
        command.ExecuteNonQuery();
        response = "Success";
        return response;

If a specific Parameter.Value is not-null and has a default set by SQL Server, this code will not work with null.

In SQL Server, this gets handled by omitting the value in your insert statement (this omission triggers inserting the default value for the table, whereas providing null produces errors).

Your last sentence - that's the solution, really. If you want the SQL Server defaults to kick in - just **don't insert** anything into that column! — marc_s, Feb 14 '13 at 19:54
I appreciate your timely response. The problem with that is that this is a method that I want to use more generically. And for each different table, I don't want to have to go in and manually not send those values. I want to be able to send all values, null or not, in the string array, and have the code just insert it, if possible. — Ian Best, Feb 14 '13 at 19:57
I cut it off. Haha. It handles the error and returns a string. — Ian Best, Feb 14 '13 at 20:08

Dustin Kingen · Answer 1 · 2013-02-14T20:27:26.213

If you want SQL Server to use the default value constraint for the column then don't include the column as part of the insert parameters.

Example:

--From SQL Server
CREATE TABLE Orders
(
     Id INT IDENTITY PRIMARY KEY
    ,Amount INT NOT NULL
    ,Cost MONEY NOT NULL
    ,SaleDate DATE NOT NULL DEFAULT GETUTCDATE()
);



//From C#
public int Insert(decimal cost, int amount)
{
    using (var connection = new SqlConnection(connectionString))
    {
        var command = connection.CreateCommand();
        //Don't specify the SaleDate and it will insert the current time
        command.CommandText = "INSERT INTO Orders(Amount, Cost) VALUES(@Amount, @Cost); SELECT SCOPE_IDENTITY();";
        command.Parameters.AddWithValue("@Amount", amount);
        command.Parameters.AddWithValue("@Cost", cost);

        using(var reader = command.ExecuteReader())
        {
            if(reader.Read())
                return Convert.ToInt32(reader[0]);
        }
    }

    return 0;
}

If you want to use a parameters list in your C# code then just keep the parameter names grouped with their values and if the value is null and it has a default value then just skip it.

score 2 · Answer 2 · edited Feb 14 '13 at 20:04

2

Passing in a NULL tells SQL that you want a NULL in that column overriding the default. If you want to pass something in pass in the keyword DEFAULT. I wrote an article, "keyword DEFAULT", about the usage:

The DEFAULT keyword causes the default value (from the constraint) to be inserted into the column.

Just remember that when you pass in DEFAULT don't put quotes around it. That makes it the string DEFAULT rather than the keyword DEFAULT.

edited Feb 14 '13 at 20:04

answered Feb 14 '13 at 19:57

Kenneth Fisher

3,692
19
21

Parameter.Value = default; Error: expected "(" – Ian Best Feb 14 '13 at 20:01
@IanBest Of course not, that's nonsense: SQL syntax != C# code. In any case, see http://stackoverflow.com/questions/2970516/how-do-you-specify-default-as-a-sql-parameter-value-in-ado-net - you'll likely have to build these values manually which, in a way, is like omitting the column to begin with. (But it's trivial in either case - only add parameters/parameter-bind to columns with values to specify!) – Feb 14 '13 at 20:07

score 0 · Answer 3 · answered Feb 14 '13 at 19:55

0

The only other way of doing it I can think of would be triggers based your approach (and you're better off coding it at that point).

However, if you alter your approach to use stored procedures, you can do your value handling natively in SQL, otherwise you're stuck coding it into your app... might i recommend Ternary statements for your example: http://msdn.microsoft.com/en-us/library/ty67wk28%28v=vs.80%29.aspx .

answered Feb 14 '13 at 19:55

RandomUs1r

4,010
1
24
44

Thanks for the resposne. What would their usefulness be? – Ian Best Feb 14 '13 at 20:13
They make your code look clean and tidy as opposed to an if else statement that at minimum takes 8 lines if you separate out your brackets in C#. Ternarys do it in 1 line. – RandomUs1r Feb 14 '13 at 21:41

score 0 · Answer 4 · answered Feb 14 '13 at 19:57

0

If you include a column in the column list, it will try and insert the value you give, it. It will not assume that NULL means "just insert the default value".

If you don't want to insert a value into that column, don't include it in your column list (or value list, obviously).

While it may seem more efficient to loop through the table columns and be agnostic of the column name, type, etc. In the long run you may be better off handling each column explicitly so you can choose whether or not to use a default, verify the value, etc.

answered Feb 14 '13 at 19:57

D Stanley

149,601
11
178
240

I appreciate your timely response. The problem with that is that this is a method that I want to use more generically. And for each different table, I don't want to have to go in and manually not send those values. I want to be able to send all values, null or not, in the string array, and have the code just insert it, if possible. – Ian Best Feb 14 '13 at 20:03
OK, then you need to decide how you're going to determine if a `NULL` value means "insert the default" (if there is one) or "insert `NULL`". Also if you leave out a non-NULLable column that does not have a default you're going to get an error. – D Stanley Feb 14 '13 at 20:05
Yeah, that's fine. I handle those. And I have already decided, and have working code. I just was hoping there was something like DBNull.value that would look first to insert a default, and then otherwise insert null, or any other different way to achieve that. – Ian Best Feb 14 '13 at 20:11

score 0 · Accepted Answer · answered Feb 26 '13 at 18:18

0

I actually used the INFORMATION_SCHEMA.COLUMNS table to pull back from SQL Server all of the Column Defaults. Then I just organized the defaults into a string[] and looped through it to insert defaults rather than nulls (some defaults are null).

answered Feb 26 '13 at 18:18

Ian Best

510
1
11
23

Getting C# To Insert Defaults Into SQL Server

5 Answers5