Building parameters type conversion question.

[Noel Justus]

Im attempting to use the datareader.getschema table in order to create
the schema table which supposedly produces a reference to the
essential values.

I will be able to map most of the SQLparameters using the values from
the "DataType" and ColumnSize" but will not be able to differentiate
between the char and nchar, varchar and nvarchar, and text and ntext
values.

I notice the "ProviderType" column probably refers to the sql data
type and hesitate to use it for fear of changes down the road.

Any way to truly map the required SqlParameter.dbtype or
sqlparameter.sqldbtype from the datareader.getschema values or from a
datatable created with fillschema using a dataadapter?

Any info appreciated.

 

[IbrahimMalluf]

Hello Noel

there are probably other ways to do this as well, but this gives you a lot
of flexibility

Run a query like this:

"Select syscolumns.Name, systypes.Name as TypeName,
systypes.xtype from syscolumns
join systypes on syscolumns.xtype = systypes.xusertype
Where sysColumns.name='PersonID'
And syscolumns.id =(Select ID From sysobjects Where Name='Persons')"

PersonID is a column name
Persons is a Table Name

This will return the column name, the column Type Name, and the Sql-Server
column type ID.

This will work on sql 6.5, 7.0 and 2k

you could eliminate the syscolumns.name parametor and get the type for all
the columns in the named table.

"Select syscolumns.Name, systypes.Name as TypeName,
systypes.xtype from syscolumns
join systypes on syscolumns.xtype = systypes.xusertype
Where syscolumns.id =(Select ID From sysobjects Where Name='persons')"

This would return a rowset containing the names, type names, and sql-server
type ids for all columns in the table.



--
Ibrahim Malluf
http://www.malluf.com
==============================================
MCS Data Services Code Generator
http://64.78.34.175/mcsnet/DSCG/Announcement.aspx
==============================================

 

[Noel Justus]

Thanks Ibrahim,

I appreciate the info but I am attempting to stay within the confines
of .net on the assumption that it would provide the necessary features
in future upgrades.

Simply want the ability to configure my own sqlparameters as well as
oledbparameters rather than have the system generate them for me.

Probably, as far as I can tell, the only way to be completely accurate
is to do something along the line as you suggest and stray from the
..net confines.

 

[Kathleen Dollard]

Noel,

I think you can count on SQL Server to continue to support the SQL-92
standard, meaning you can use the Information Schema views to retrieve most
of the information about your schema. The only things I know aren't
supported by these standard schemas are Autoincrement/Identity and extended
properties.

The Information schema views are well documented in the SQL Server help.

The significant benefit of this solution is that it should work on other
SQL-92 backends such as Oracle.

AFAIK, you can't get this information within .NET without asking SQL Server
for it.

Kathleen

 

[Noel Justus]

Thanks for the info.