asked on

Getting the Database schema using Microsoft.Practices.EnterpriseLibrary.Data

Hello,

I need to get the Database schema information using Microsoft.Practices.EnterpriseLibrary.Data. I have not found a means to do this with these tools. I found an article using ADO.Net OpenSchema, but I do not want to add multiple database connections to my application.

Does anyone know how to gain access to this information? I use selects to tables and stored procedure/queries in SQL Server and MS Access. The type of information that I need to derive is the column name, column data type, column size (length), column precision, column scale, column order (ordinal), is the column null able, is the column part of the primary key, is the column part of an index, is the column for replication or not for replication, is the column a GIUD and is the column an identity or the equivalent.

Thank in advance for any help.

Ted Bouskill

The only way I know how to do this is actually querying the SQL database directly

-- Query to return tables and their columns include column details
SELECT
	sysobjects.[id]       ObjectID,
	(SELECT [name] FROM sys.sysusers WHERE sysobjects.uid = sys.sysusers.uid) [Owner],
	sysobjects.[name]     [Object],
	sysobjects.xtype      ObjectType,
	sysobjects.crdate     DateCreated,
	syscolumns.[name]     [Column],
	systypes.xusertype    DataTypeID, 
	systypes.[name]       DataType,
	systypes.length       [Length],
	systypes.variable     VariableLength,
	systypes.allownulls   AllowNulls,
	systypes.prec         [Precision],
	systypes.scale        [Scale],
	syscolumns.iscomputed [IsComputed],
	syscolumns.isoutparam [IsOutputParam],
	syscolumns.isnullable [IsNullable],
	(SELECT TOP 1 CAST([value] AS VARCHAR(8000)) FROM sys.extended_properties sysproperties WHERE [Name] = 'MS_Description' AND minor_id = 0 AND sysobjects.[id] = sysproperties.[major_id]) [MSExtendedProperty]
FROM
	sys.syscolumns syscolumns
		INNER JOIN sys.sysobjects sysobjects ON syscolumns.[id] = sysobjects.[id]
		INNER JOIN sys.systypes systypes ON syscolumns.xusertype = systypes.xusertype
WHERE
	sysobjects.xtype IN ('U', 'S')
 
-- Query to return procedures, parameters and parameter types
SELECT
	sysobjects.[id]       ObjectID,
	(SELECT [name] FROM sys.sysusers WHERE sysobjects.uid = sys.sysusers.uid) [Owner],
	sysobjects.[name]     [Object],
	sysobjects.xtype      ObjectType,
	sysobjects.crdate     DateCreated,
	syscolumns.[name]     [Column],
	systypes.xusertype    DataTypeID, 
	systypes.[name]       DataType,
	systypes.length       [Length],
	systypes.variable     VariableLength,
	systypes.allownulls   AllowNulls,
	systypes.prec         [Precision],
	systypes.scale        [Scale],
	syscolumns.iscomputed [IsComputed],
	syscolumns.isoutparam [IsOutputParam],
	syscolumns.isnullable [IsNullable]
FROM
	sys.syscolumns syscolumns
		INNER JOIN sys.sysobjects sysobjects ON syscolumns.[id] = sysobjects.[id]
		INNER JOIN sys.systypes systypes ON syscolumns.xusertype = systypes.xusertype
WHERE
	sysobjects.xtype = 'P'

Open in new window

fundsf

ASKER

Correct, but looking into the net, I found that you could use the ADODB OpenSchema to get the info from SQL Server, Access or oracle. I cannot find a simular solution for the Microsoft.Practices.EnterpriseLibrary.Data that Microsft now provides.
I also found a DAO example, but I would rather not have two difference means of getting this information.

Brian Crowe

Try using the INFORMATION_SCHEMA views (look at BOL for deatils)

SELECT * FROM myDB.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'myTable'

Brian Crowe

I guess that doesn't address your desire to have a single process for all datasources. I've used the ADO DataAdapter.FillSchema method in the past maybe that will work.

fundsf

ASKER

Can you give me an example of using the ADO DataAdapter.FillSchema while using the Microsoft.Practices.EnterpriseLibrary.Data stuff? If so, and I can get it to work , you will win the points.

fundsf

ASKER

Also, is the ADO a ADO.Net colution? I would ranther not intorduce COM objects into the code base.

ASKER CERTIFIED SOLUTION

Ted Bouskill

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial