DataTable Schema...not enough information

[Josh]

I need to know if there is a way to determine the precision and scale of a
column in a DataTable, when the data type is a decimal. We do not have a
problem if there is a row that has a non-null value in it using the GetBits
method. However, if the value is Null or even 0 I cannot get the scale.
Let me run through it a little bit explaining along the way. We populate
our DataSet using the following procedure:



Private Function FillDataset(ByVal voCommand As SqlCommand, _
ByVal veMissingSchemaAction As
MissingSchemaAction) As DataSet

Dim loDA As SqlDataAdapter
Dim loDS As DataSet

Try
loDA = New SqlDataAdapter(voCommand)
loDS = New DataSet

'MissingSchemaAction can now be set when retrieving dataset. This
was necessary
'for proper sorting when going through trsADO interface.

loDA.MissingSchemaAction = veMissingSchemaAction
loDA.Fill(loDS)

Return loDS

Finally
CleanUpDatabaseObjs(roDataAdapter:=loDA)

End Try

End Function

In this case we are using MissingSchemaAction of Add. Let just say for this
command we have 1 table, with 2 columns and 3 rows. The columns are defined
in SQL server as a Decimal(5,3) and the values are as follows:
Column1 Column2
------------------- ---------------
Null 23.12
12.023 Null
1.555 0

Column2 is not a problem for me, because I can look at the value in the
first row and using the Decimal.GetBits method I can determine the scale
(believe it or not the scale correctly returns 3). However, I cannot do a
GetBits on Row 1 of Column1 because the value is Null. Furthermore I
cannot spin through all the rows until I find a non-null/non-zero value
because first we use some pretty big tables with many rows, performance is
an issue and second, and probably more importantly, we cannot guarantee that
we will always have a value, sometimes the entire column could be filled
with Null or 0.

So, is there a way to tap into the underlying table schema and determine how
a column is defined? I am actually kind of surprised that there are not
properties on the columns collection for precision and scale, but there is
not, so I need another solution to figure it out.

Thanks!

 

[Mark Ashton]

You don't have access to that information via fill, but it does exist.
// MissingSchemaAction.AddWithKey uses CommandBehavior.KeyInfo

SqlDataReader reader = command.ExecuteReader(CommandBehavior.KeyInfo)
DataTable table = reader.GetSchemaTable() ' this table has the information
' The "NumericPrecision" and "NumericScale" columns have that information.

Unfortunately if you get the information this way, its not obvious to then
fill the DataSet without rexecuting.

(new LoadDataReader()).Fill(dataset, reader);

internal class LoadDataReader : System.Data.Common.DbDataAdapter() {
internal int Fill(DataSet dataset, IDataReader reader) {
// the method on DbDataAdapter that takes a DataTable and a reader
is protected
// so you can only call it if your derive from DbDataAdapter and
delegate to it
base.Fill(table, "Table", reader, 0, 0);
}
}