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).

Answer 1

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).