Wednesday, 27 February 2013

Using SqlConnection GetSchema in asp.net ado.net Vb.NET


Using SqlConnection GetSchema in asp.net ado.net Vb.NET


Add
import 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 ado.net Vb.NET,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.,