Retrieving SQL Server database schema and objects and reflect in a .Net application

Question

What is the simplest way to reflect the SQL Server database schema with all its objects in a .Net application?

Basically I would like my application to scan the database schema and present me all tables and other objects. Further I should be able to explore all table columns and their properties (type, constraints, etc.) as well.

I am planning to use Data Access Application Block for my data access needs. Would it also meet the above stated requirement?

Answer 1

You can use any data access method you want - you just need to run the right queries on the database. This article seems to have a good example of what you want.

Answer 2

SQL Server provides some system views that can be used, e.g. sys.tables lists all tables, sys.columns all columns etc.

MSDN has a FAQ.

Answer 3

Although you can use queries on the metadata tables of SQL Server, the cleanest way to access this information is by using Sql Server Management Objects (SMO).

To use this example, reference the Microsoft.SqlServer.ConnectionInfo, Microsoft.SqlServer.Management.Sdk.Sfc and Microsoft.SqlServer.Smo.

using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;

   var sqlConnection = new SqlConnection(@"Data Source=(local);Integrated Security=SSPI");
    var server = new Server(new ServerConnection(sqlConnection));

    foreach (Database database in server.Databases)
    {
        foreach (Table table in database.Tables)
        {
            Console.WriteLine("{0}: {1}", database.Name, table.Name);       
        }
    }