Getting C # to enter default values ​​in SQL Server

Question

How can I INSERT values ​​in SQL Server that are stored in string[] , so some of the values ​​should be ignored in favor of the values ​​stored in SQL Server, as default constraints in the table? What do I need to pass (e.g. NULL or something else) to use these default values?

I am currently adding all the default values ​​to my code and it is becoming cumbersome.

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 the specific Parameter.Value non-null and has a default value set by SQL Server, this code will not work with zero.

In SQL Server, this is handled by omitting the value in your insert statement (this omission causes the default value to be entered for the table, while providing null results in errors).

Answer 1

I actually used the INFORMATION_SCHEMA.COLUMNS table to override all default column values ​​in SQL Server. Then I just organized the default values ​​in string[] and looped on it to insert the default values, not null (some default values ​​are null).

Answer 2

If you want SQL Server to use the default restriction for a column, then do not include the column as part of the insertion options.

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 the parameter list in your C # code, just keep the parameter names grouped with their values, and if the value is null and it has a default value, just skip it.

Answer 3

Passing to NULL tells SQL that you want NULL in this column to override the default value. If you want to pass something in the skip field to the DEFAULT keyword. I wrote an article on keyword DEFAULT about using:

The DEFAULT keyword calls the default value (from the constraint), which must be inserted into the column.

Just remember that when you go into DEFAULT, do not put quotation marks there. This makes it a DEFAULT string, not a DEFAULT keyword.

Answer 4

The only way I can think of this would be triggers based on your approach (and you'd better code it at this point).

However, if you change your approach to using stored procedures, you can process your values ​​initially in SQL, otherwise you are stuck in coding it into your application ... Can I recommend the terms for your example: http://msdn.microsoft. com / en-us / library / ty67wk28% 28v = vs. 80% 29.aspx .

Answer 5

If you include a column in the column list, it will try to insert the value you specified. It does not imply that NULL means "just insert the default value."

If you do not want to insert a value in this column, do not include it in the list of columns (or the list of values, obviously).

Although it may seem more efficient to loop through the columns of the table and be an agnostic of the column name, type, etc. Ultimately, you might be better off handling each column explicitly so that you can choose to use or not use the default, check the value, etc.