Adding new row with default values.

[Viorel]

Adding new row with default values.

In order to insert programmatically a new row into a database table, without
direct "INSERT INTO" SQL statement, I use the well-known DataTable.NewRow,
DataTable.Rows.Add and DataAdapter.Update member functions. Before adding
the new row object to the Rows collection, all of the row's fields that do
not accept NULL must be assigned, otherwise an exception is thrown. However,
I do not want to assign values to some of the fields, because they already
have default values, defined using "Design Table" dialog. So, how can I tell
the system "Please use default values for unassigned fields while inserting
this new row"?

Thanks.

Viorel.

 

[Nicholas Paldino [.NET/C# MVP]]

Viroel,

There is nothing in the framework to do this. What you will have to do
is query the server for the default values of the table, and then set these
yourself.

You can get the information with this query from the database:

select
scol.name, st.name as type, sc.text as [default]
from
sysobjects as so
inner join syscolumns as scol on so.id = scol.id
inner join systypes as st on scol.type = st.type
inner join syscomments as sc on scol.cdefault = sc.id
where
so.xtype = 'U' and
so.name = <table name>

You would have to replace <table name> with the name of the table you
want to get the defaults for. This will give you a result set that would
have the defaults the columns that had them. Once you have that, I would
generate a dynamic query which would perform the cast to the datatype of the
column, and return a single row with those default values. You can then
store this and use it to set the default values of new rows in your data
layer.

Hope this helps.

 

[Viorel]

Thanks for help.



The solution based on acquiring default values from the server seems for me
rather intricate. I am not sure if it will work in case of default values
defined in a form of SQL expressions (like "NewID()").



The "DefaultValue" member of the DataColumn object also can be considered.



(I think it should be an easier way. As I know, a typical DataAdapter
effectively performs the insert operation using "INSERT INTO" SQL statement.
The library can have a special value, "DbDefault", similar to DbNull,
assignable to any field of new DataRow object. This reserved value will
instruct the library to use the "DEFAULT" SQL keyword in a generated INSERT
statement. In addition, it should be a flag for instructing the library to
use automatically default values for insert operations).



Viorel.



-------------------------------------

Viroel,

There is nothing in the framework to do this. What you will have to do
is query the server for the default values of the table, and then set these
yourself.

You can get the information with this query from the database:

select
scol.name, st.name as type, sc.text as [default]
from
sysobjects as so
inner join syscolumns as scol on so.id = scol.id
inner join systypes as st on scol.type = st.type
inner join syscomments as sc on scol.cdefault = sc.id
where
so.xtype = 'U' and
so.name = <table name>

You would have to replace <table name> with the name of the table you
want to get the defaults for. This will give you a result set that would
have the defaults the columns that had them. Once you have that, I would
generate a dynamic query which would perform the cast to the datatype of the
column, and return a single row with those default values. You can then
store this and use it to set the default values of new rows in your data
layer.

Hope this helps.


--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

Adding new row with default values.

In order to insert programmatically a new row into a database table,
without
direct "INSERT INTO" SQL statement, I use the well-known DataTable.NewRow,
DataTable.Rows.Add and DataAdapter.Update member functions. Before adding
the new row object to the Rows collection, all of the row's fields that do
not accept NULL must be assigned, otherwise an exception is thrown.
However,
I do not want to assign values to some of the fields, because they already
have default values, defined using "Design Table" dialog. So, how can I
tell
the system "Please use default values for unassigned fields while
inserting
this new row"?

Thanks.

Viorel.