Shiju S
asked on
Default column value of a table - C#
Hi
How can i get default value of a table column in C#?
Thanks
Shiju
How can i get default value of a table column in C#?
Thanks
Shiju
ASKER
for null value column its not retrieving
ASKER
if the data table is a result of a select query it is not fetching the default value
This DefaultValue has nothing to do with the results of a query. Rather, when you create a new row in a C# data table, this will be the default value of the column.
The value returned from a sql query is controled by sql, not C#.
The value returned from a sql query is controled by sql, not C#.
ASKER
Is there any other way to get default values of a table column?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
why don't you use a separate SQL to get default values.....
Do you mean something like this?
DataSet1.Tables[0].Columns [0].Defaul tValue
DataSet1.Tables[0].Columns
ASKER
TheLearnedOne,
yes i tried like that but without data set.
When we fill datatable using a select statement it doesnt fetch default value.
philipjonathan,
Your idea seems good. i will adopt that if there is no other alternative
shijusn:
I think you may be asking for something that is not readily available, at least in the way you seem to think. As I mentioned before "The value returned from a sql query is controled by sql, not C#" or rather, it is the values in the rows of the table. Whatever values are in a sql table, those are the values you are going to get.
To look at it another way, the "normal" default for a column in T-SQL is NULL, as long as the column accepts NULLs. you CAN put DEFAULT definitions on columns during a CREATE TABLE or ALTER TABLE statement in T-SQL. You didnt mention if you are using SQL Server, but lets run with that. Put simply, what that does is define a DEFAULT value that will be used during an INSERT statement, if no explict value is supplied. If no DEFAULT is defined, a NULL is inserted. IF the column does not accept NULLs in that case, you get an error during the INSERT statement.
So if you are doing a SELECT statement from C#, you may get a NULL, you may get a value that was inserted, or you may get the DEFAULT value that was inserted. You aren't going to know how it got there or what the DEFAULT was.
Finally, there is a way to find out what the default definitions ON THE SQL COLUMN are. A system table, sys.default_constraints, contains the DEFAULT definitions for the entire database. See this link for an example of how to query this table: http://msdn.microsoft.com/en-us/library/ms173758(SQL.100).aspx. If you want to try it, You will probably want to read up on the other links referenced to understand how DEFAULTS are used. This would be a good place to begin: http://msdn.microsoft.com/en-us/library/ms189909(SQL.100).aspx
I think you may be asking for something that is not readily available, at least in the way you seem to think. As I mentioned before "The value returned from a sql query is controled by sql, not C#" or rather, it is the values in the rows of the table. Whatever values are in a sql table, those are the values you are going to get.
To look at it another way, the "normal" default for a column in T-SQL is NULL, as long as the column accepts NULLs. you CAN put DEFAULT definitions on columns during a CREATE TABLE or ALTER TABLE statement in T-SQL. You didnt mention if you are using SQL Server, but lets run with that. Put simply, what that does is define a DEFAULT value that will be used during an INSERT statement, if no explict value is supplied. If no DEFAULT is defined, a NULL is inserted. IF the column does not accept NULLs in that case, you get an error during the INSERT statement.
So if you are doing a SELECT statement from C#, you may get a NULL, you may get a value that was inserted, or you may get the DEFAULT value that was inserted. You aren't going to know how it got there or what the DEFAULT was.
Finally, there is a way to find out what the default definitions ON THE SQL COLUMN are. A system table, sys.default_constraints, contains the DEFAULT definitions for the entire database. See this link for an example of how to query this table: http://msdn.microsoft.com/en-us/library/ms173758(SQL.100).aspx. If you want to try it, You will probably want to read up on the other links referenced to understand how DEFAULTS are used. This would be a good place to begin: http://msdn.microsoft.com/en-us/library/ms189909(SQL.100).aspx
ASKER
Well i tried this
SELECT COLUMN_NAME, COLUMN_DEFAULT FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = <TableName>
SELECT COLUMN_NAME, COLUMN_DEFAULT FROM INFORMATION_SCHEMA.COLUMNS
if col is a DataColumn
col.DefaultValue can be get or set.