FillSchema - no column DEFAULTS??

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Greetings,

Developing some code that will do a specialized database regen. We use the following to extract the schema
as it currently stands.

Dim sqlText As String = "SELECT * FROM " & tblname
Dim da As New SqlDataAdapter(sqlText, cnx)
Dim dt As New DataTable(" & dblquotes & tblname & dblquotes & ")
da.FillSchema(dt, SchemaType.Source)

we then loop through the columns using something like:

Dim col As DataColumn
dim vDefault as object
For Each col In dt.Columns
vDefault = col.defaultvalue
Next col

We don't get anything from the col.default value, when we can see that the actual SQL Server table that this
was pulled from has default values for a number of items.

What gives?
 
My understanding is that FillSchema doesn't produce any actual records in
your DataTable, only the correct DataTable structure.


Greetings,

Developing some code that will do a specialized database regen. We use
Click to expand...
the following to extract the schema
as it currently stands.

Dim sqlText As String = "SELECT * FROM " & tblname
Dim da As New SqlDataAdapter(sqlText, cnx)
Dim dt As New DataTable(" & dblquotes & tblname & dblquotes & ")
da.FillSchema(dt, SchemaType.Source)

we then loop through the columns using something like:

Dim col As DataColumn
dim vDefault as object
For Each col In dt.Columns
vDefault = col.defaultvalue
Next col

We don't get anything from the col.default value, when we can see that the
Click to expand...
actual SQL Server table that this
 
Actually was just looking for the definition of the default when using the fill schema method, as this is
something that is commonly included in normal schemas.

I've convinced myself that it doesn't exist, so I'm back to the sp_columns method of digging it up.


"Scott M."
 
Hi mdjhome,

Thank you for using MSDN Newsgroup! My name is Kevin, and I will be
assisting you on this issue.

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that you cannot get the default value of the
column in the database table into the DataTable when using FillSchema
method. Have I fully understood you? If there is anything I misunderstood,
please feel free to let me know.

I've checked the MSDN document, and found that this issue is by design.
According to the document, the FillSchema method will only configures the
following five DataColumn properties if they exist at the data source.

1. AllowDBNull
2. AutoIncrement. You must set AutoIncrementStep and AutoIncrementSeed
separately.
3. MaxLength
4. ReadOnly
5. Unique

Please refer to the following link for more information.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/
frlrfsystemdatacommondataadapterclassfillschematopic.asp

As default value is not listed here, I think you have to set it explicitly.
To get the default value from a SQL Server database, executing the
"sp_columns" stored procedure is a good idea. The default value is in the
COLUMN_DEF field of the result set. Please refer to the SQL Book Online for
more information.

If you're using an OleDbConnection, you can also use the
OleDbConnection.GetOleDbSchemaTable method to get the default value. Here's
an example:

Dim t As DataTable =
Me.OleDbConnection1.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, _
New Object() {Nothing, Nothing, "Table11", Nothing})

The default value is in the COLUMN_DEFAULTcolumn. For more information
about GetOleDbSchemaTable method, please check the following link:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/
frlrfsystemdataoledboledbconnectionclassgetoledbschematabletopic.asp

If anything is unclear, please feel free to reply to the post.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."
 
Back
Top