Reading *exact* SQL types from database

[Guest]

Hello,

I've managed to read an sql type from the database (using a kind of virtual
commandbuilder and SqlDbType method) but I'm not able to retrieve exact data
type (eg. maximum length of a varchar field), ie. I can only retrieve
"NVarChar" type while the true type name is "NVarChar(50)". I hope you know
what I mean, and I really hope somebody here could help me with this.

Regards,
Mike

 

[Ignacio Machin \( .NET/ C# MVP \)]

Hi,

You would have to read this info from one of the system tables syscolums
IIRC.

 

[Ian Semmel]

If you want to do what I think you want to do, you can execute the GetSchema
call on the connection and then go through the 'Columns' collection

 

[Ignacio Machin \( .NET/ C# MVP \)]

Hi,

If you want to do what I think you want to do, you can execute the
GetSchema call on the connection and then go through the 'Columns'
collection

Not sure if this will return the correct type name in the DB. Most probably
you will get the .NET equivalent.

But I'm not sure about this.

 

[Steve Barnett]

Not sure if this helps, but I get the schema of a table as follows. One of
the coumns returned is the "provider" data type, rather than the .Net data
type.

// To get the schema, we need to execute a select command.
cmd.Connection = dBase;
cmd.CommandText = "SELECT * FROM [" + strTableName + "];";

try
{
// Create the datareader, retrieving only the schema information
dataReader = cmd.ExecuteReader(CommandBehavior.KeyInfo);
// Copy the stuff we retrieved in to a data table
schemaTable = dataReader.GetSchemaTable();
// And give the data table a name that we can work with.
schemaTable.TableName = "ColumnNames";
// We don't need the data reader any more - we have what we want
dataReader.Close();

dsTemp.Tables.Add(schemaTable);

...

foreach(DataRow drColumn in dsTemp.Tables["ColumnNames"].Rows)
{
... do stuff

// There is a column with the "provider" data type in it...
columnDef.DataType =
((OleDbType)drColumn["ProviderType"]).ToString();

...
}


HTH
Steve.

 

[Ian Semmel]

In the 'Columns' collection, you get for example, this

TABLE_CATALOG = C:\PROGRAM FILES\MICROSOFT SQL SERVER\MSSQL.1\MSSQL\DATA\QFRS.MDF
TABLE_SCHEMA = dbo
TABLE_NAME = Activity
COLUMN_NAME = ActivityDate
ORDINAL_POSITION = 2
COLUMN_DEFAULT =
IS_NULLABLE = NO
DATA_TYPE = datetime (*** SQL Type ***)
CHARACTER_MAXIMUM_LENGTH =
CHARACTER_OCTET_LENGTH =
NUMERIC_PRECISION =
NUMERIC_PRECISION_RADIX =
NUMERIC_SCALE =
DATETIME_PRECISION = 3
CHARACTER_SET_CATALOG =
CHARACTER_SET_SCHEMA =
CHARACTER_SET_NAME =
COLLATION_CATALOG =

In the 'DataType' collection you get

TypeName = datetime
ProviderDbType = 4
ColumnSize = 23
CreateFormat = datetime
CreateParameters =
DataType = System.DateTime (*** C# Type ***)
IsAutoIncrementable = False
IsBestMatch = True
IsCaseSensitive = False
IsFixedLength = True
IsFixedPrecisionScale = False
IsLong = False
IsNullable = True
IsSearchable = True
IsSearchableWithLike = True
IsUnsigned =
MaximumScale =
MinimumScale =
IsConcurrencyType = False
IsLiteralSupported =
LiteralPrefix = {ts '
LiteralSuffix = '}

 

[Ignacio Machin \( .NET/ C# MVP \)]

Hi,


Good to know, I did not know you had this info returned in the Columns
collection