SqlTypes Decimal

[carlospedr]

Hi,
I'm using .Net Framework 2.0.

I Querying an Sql 2000 Database to fill a Dataset, the user dynamically
choses what tables to query to fill the dataset. I need to provide
information to the user about the tables, information such as the type
and size of the columns.

Though at start this seemed to be quite simple to do, I have now came
to a problem when working with decimal columns. how can I get the
number of digits that a decimal column suports?
I have tried using sqlTypes when filling the table but not have been
able to get this info.

Can SomeBody Help me Please.

Carlos Pedro

 

[Dave Sexton]

Hi Carlos,

According to the specs for Sql Server 2005, max precision is 38 for the decimal data type, by default, and the default precision is
18. You can examine the global @@MAX_PRECISION variable to retrieve the current maximum value for your database.

If you need to discover the precision for a specific column at runtime, you can execute a query that examines
INFORMATION_SCHEMA.COLUMNS:

USE TheDatabase;
GO

SELECT
NUMERIC_PRECISION,
NUMERIC_SCALE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'aspnet_WebEvent_Events' AND COLUMN_NAME = N'EventSequence';

 

[carlospedr]

The is no way of discover this information from ADO objects (such as
DataColumn) that result from the query I used fill a DataSet and it's
datatables? I know I can do that by querying the system tables but i
don't want to that at run time.

 

[Dave Sexton]

Hi Carlos,

DataColumn doesn't make use of precision and scale information.

If you need this information in your program your three options, as I see them, are to hard-code the schema, execute the query that
I suggested, or use these SqlClient classes as follows:

System.Data.DataTable schema;

using (SqlConnection connection = new SqlConnection(connString))
{
// aspnet_WebEvent_EVents is the name of the table
// for which the schema will be returned
using (SqlCommand command = new SqlCommand(
"SELECT TOP 0 * FROM aspnet_WebEvent_Events", connection))
{
connection.Open();

using (SqlDataReader reader = command.ExecuteReader(
CommandBehavior.KeyInfo | CommandBehavior.SchemaOnly))
{
schema = reader.GetSchemaTable();
}
}
}

// TODO: use schema
// If you are using Visual Studio.NET 2005 a nice way of
// examining the schema columns is to use the
// DataTable visualizer in the debugger

You must realize that in order to retrieve the information at runtime a query must be made. Whether or not you create your own
procedure or use the SqlClient classes above there will be a database hit. Therefore, you might want to execute code that retrieves
the data and caches the result in memory.