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.

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_sFeb 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 BestFeb 14 '13 at 19:57

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.

@IanBest Of course not, that's nonsense: SQL syntax != C# code. In any case, see stackoverflow.com/questions/2970516/… - 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!)
–
user166390Feb 14 '13 at 20:07

Thanks for the resposne. What would their usefulness be?
–
Ian BestFeb 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.
–
RandomUs1rFeb 14 '13 at 21:41

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.

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 BestFeb 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 StanleyFeb 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 BestFeb 14 '13 at 20:11

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