Wednesday, 27 February 2013

Using SqlConnection GetSchema in asp.net C#

Using SqlConnection GetSchema in asp.net C#


Add
using System.Data.SqlClient;


Every Database has set of Schema objects associated with it. Using SQL Server Management Studio , user can query all types of objects such as Users in the DB, Databases,Tables,Columns Associated with Each table/view,set of functions and stored procedures etc.,

 Programmatically  user wants to query Schema Objects , here is the Procedure.

Add Sql Connection Object ,  which as Instance name and credentials. (By default it will take master database)

SqlConnection conn = new SqlConnection(@"Data Source=localhost\sqlexpress2012;trusted_connection=yes;");
                conn.Open();
               DataTable schemaTable = conn.GetSchema();
                GridView1.DataSource = schemaTable;
                GridView1.DataBind();

>Add GridView to aspx page.

<asp:GridView  Font-Names="'Times New Roman',roman" 
            Caption="<h3 STYLE='BACKGROUND:TEAL;COLOR:WHITE'>Using SqlConnection GetSchema in ASP.NET</H3>"
            ID="GridView1" runat="server" Height="179px" Width="652px">
            <HeaderStyle BackColor="Teal" ForeColor="WhiteSmoke" />
        </asp:GridView>

> Bind to GridView.



Using SqlConnection GetSchema in ASP.NET

CollectionNameNumberOfRestrictionsNumberOfIdentifierParts
MetaDataCollections00
DataSourceInformation00
DataTypes00
Restrictions00
ReservedWords00
Users11
Databases11
Tables43
Columns44
AllColumns44
ColumnSetColumns33
StructuredTypeMembers44
Views33
ViewColumns44
ProcedureParameters41
Procedures43
ForeignKeys43
IndexColumns54
Indexes43
UserDefinedTypes21

Tags:Using SqlConnection GetSchema in asp.net C#,Get Schema Objects in Sql Server, Get all Schema Objects in SQL Server Database,Get Schema Objects like Databases,tables,Views,Stored procedures,Functions etc.,