Re: Reading *exact* SQL types from database

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

Hi,

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

--
--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation

"Baal80" <Baa...@discussions.microsoft.com> wrote in message
news:72105742-EA2F-455D...@microsoft.com...
> 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

[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,

"Ian Semmel" <isemme...@NOKUNKrocketcomp.com.au> wrote in message
news:OmCArlim...@TK2MSFTNGP05.phx.gbl...

> 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.

"Ignacio Machin ( .NET/ C# MVP )" <ignacio.machin AT dot.state.fl.us> wrote
in message news:%23Eb%23MPrmG...@TK2MSFTNGP03.phx.gbl...

[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

--
--
Ignacio Machin,
ignacio.machin AT dot.state.fl.us
Florida Department Of Transportation

"Ian Semmel" <isemme...@NOKUNKrocketcomp.com.au> wrote in message

news:%23U8kkst...@TK2MSFTNGP03.phx.gbl...