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.
fundsfAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ted BouskillSenior Software DeveloperCommented:
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

0
fundsfAuthor Commented:
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.


0
Brian CroweDatabase AdministratorCommented:
Try using the INFORMATION_SCHEMA views (look at BOL for deatils)

SELECT * FROM myDB.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'myTable'
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Brian CroweDatabase AdministratorCommented:
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.
0
fundsfAuthor Commented:
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.
0
fundsfAuthor Commented:
Also, is the ADO a ADO.Net colution? I would ranther not intorduce COM objects into the code base.
0
Ted BouskillSenior Software DeveloperCommented:
I'm a little confused by your focus on the 'Microsoft.Practices.EnterpriseLibrary.Data' stuff as you call it.  Read the following quote from the download page for that technology from Microsoft:

"The patterns & practices Enterprise Library is a library of application blocks designed to assist developers with common enterprise development challenges. Application blocks are a type of guidance, provided as source code that can be used "as is," extended, or modified by developers to use on enterprise development projects."

It's not a finished product.  It's to help you as a developer.  If you need to extend it's capabilities we've given you everything you need to do it.

I think you are looking for LINQ type functionality whereby dynamically you can access the database schema using a DOM (Document Object Model) as objects.  Sorry, but in .NET as far as I know nothing like that exists.

Here is the URL for LINQ: http://msdn2.microsoft.com/en-us/netframework/aa904594.aspx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.