Retrieving Database Information using ASP.NET and SQL Server 2005/2008

Posted by: Suprotim Agarwal , on 2/26/2009, in Category ASP.NET
Views: 37956
Abstract: In this article, we will retrieve database information using ASP.NET and SQL Server. We will be listing down the databases on our server and a list of database objects like Constraints, Procedures, Views etc. belonging to that database.
Retrieving Database Information using ASP.NET and SQL Server 2005/2008
 
In this article, we will retrieve database information using ASP.NET and SQL Server. We will be listing down the databases on our server and a list of database objects like Constraints, Procedures, Views etc. belonging to that database.
Let us get started. Create an ASP.NET application. Drag and drop a DropDownList, GridView and a SQLDataSource from the toolbox.
We will first populate the DropDownList with a list of databases present on the server. Use the following code to do so. Here we make use of the sys.sysdatabases system view. This view contains one row for each database in an instance of Microsoft SQL Server
C#
using System;
using System.Web.UI;
using System.Data.SqlClient;
 
public partial class _Default : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        if(!Page.IsPostBack)
         PopulateDropDown();
    }
 
    private void PopulateDropDown()
    {
        using (SqlConnection conn = ReturnConnection())
        {
            SqlCommand cmd = new SqlCommand("SELECT name FROM sys.sysdatabases", conn);
            conn.Open();
            SqlDataReader dr = cmd.ExecuteReader();
            if (dr != null)
            {
                while (dr.Read())
                    DropDownList1.Items.Add(dr.GetString(0));
            }
        }
    }
 
    private SqlConnection ReturnConnection()
    {
        SqlConnection conn = new SqlConnection();
        conn.ConnectionString = "Data Source=(local); Initial Catalog=master; Integrated Security=SSPI;";
        return conn;
    }
}
 
 

VB.NET
Imports System
Imports System.Web.UI
Imports System.Data.SqlClient
 
Partial Public Class _Default
      Inherits System.Web.UI.Page
      Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs)
            If (Not Page.IsPostBack) Then
             PopulateDropDown()
            End If
      End Sub
 
      Private Sub PopulateDropDown()
            Using conn As SqlConnection = ReturnConnection()
                  Dim cmd As New SqlCommand("SELECT name FROM sys.sysdatabases", conn)
                  conn.Open()
                  Dim dr As SqlDataReader = cmd.ExecuteReader()
                  If dr IsNot Nothing Then
                        Do While dr.Read()
                              DropDownList1.Items.Add(dr.GetString(0))
                        Loop
                  End If
            End Using
      End Sub
 
      Private Function ReturnConnection() As SqlConnection
            Dim conn As New SqlConnection()
            conn.ConnectionString = "Data Source=(local); Initial Catalog=master; Integrated Security=SSPI;"
            Return conn
      End Function
End Class
 
Let us now configure the SQLDataSource to pull the database schema information
Before that, create the following stored procedure in your database. Choose the database as ‘master’ and type the following stored procedure in your SSMS (SQL Server Management Studio) and Execute it (F5)
CREATE PROCEDURE [dbo].[usersp_GetDBData]
@DatabaseName VARCHAR(50)
AS
BEGIN
DECLARE @DBSql NVARCHAR(200)
SET @DBSql = 'USE '+ @DatabaseName + ' ; SELECT name,type_desc,create_date,modify_date
FROM Sys.Objects WHERE Type IN (''C'',''D'',''F'',''P'',''PK'',''U'',''V'') ORDER BY type_desc'
EXEC sp_executesql@DBSql
END
Once the proc has been created, it’s now time to configure the SQLDataSource
Go to the Design mode of your page and > Click on the smart tag of the SQLDataSource > Configure Data Source
Configure DS
Create a New Data Connection to the master database and click Next. The Dialog will display a screen where you can choose to save your connection string in your web.config. Click the checkbox and save the connection string as ‘masterConnectionString’. An entry will be added to the web.config
<connectionStrings>
 <add name="masterConnectionString" connectionString="Data Source=.;Initial Catalog=master;Integrated Security=True"
   providerName="System.Data.SqlClient" />
 </connectionStrings>
The next step of the Configure Data Source wizard presents you with the option of specifying a custom SQL statement or Stored Procedure. Click Next. In the ‘Define Custom Statements or Stored Procedures’ , select the stored procedure ‘usersp_GetDBData’ that we just created a short while ago > click Next
Configure DS Step 1
In the ‘Define Parameters’ screen, select the Parameter source as ‘Control’ and ControlID as ‘DropDownList1’ > Click Next.
Configure DS Step 2
Test the query and click on the Finish Button.
With the DataSource set, configure the GridView using the smart tag to use this DataSource. I have also enabled Paging and Sorting on the GridView and added some CSS to beautify the GridView. After setting the properties, the markup would look similar to the following:

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
    <style type="text/css">
    body
    {
    font: normal 11px auto "Trebuchet MS", Verdana, Arial, Helvetica, sans-serif;   
    background-color: #ffffff;
    color: #4f6b72;      
    }
    
    th {
    font: bold 11px "Trebuchet MS", Verdana, Arial, Helvetica, sans-serif;
    color: #4f6b72;
    border-right: 1px solid #C1DAD7;
    border-bottom: 1px solid #C1DAD7;
    border-top: 1px solid #C1DAD7;
    letter-spacing: 2px;
    text-transform: uppercase;
    text-align: left;
    padding: 6px 6px 6px 12px;
    background: #D5EDEF;
    }
    
    td {
    border-right: 1px solid #C1DAD7;
    border-bottom: 1px solid #C1DAD7;
    background: #fff;
    padding: 6px 6px 6px 12px;
    color: #4f6b72;
    }
    
    td.alt
    {
    background: #F5FAFA;
    color: #797268;
    }
    
    td.boldtd
    {
    font: bold 13px "Trebuchet MS", Verdana, Arial, Helvetica, sans-serif;
    background: #D5EDEF;
    color: #797268;
    }
    </style>
</head>
<body>
<form id="form1" runat="server">
<div>
    <asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="True">
    </asp:DropDownList>
   
    <br />
   
    <asp:GridView ID="GridView1" runat="server" AllowPaging="True"
        AllowSorting="True" DataSourceID="SqlDataSource1">
    </asp:GridView>       
   
    <asp:SqlDataSource ID="SqlDataSource1" runat="server"
        ConnectionString="<%$ ConnectionStrings:masterConnectionString %>"
        SelectCommand="usersp_GetDBData" SelectCommandType="StoredProcedure">
        <SelectParameters>
            <asp:ControlParameter ControlID="DropDownList1" Name="DatabaseName"
                PropertyName="SelectedValue" Type="String" />
        </SelectParameters>
       
    </asp:SqlDataSource>
    <br />      
   
</div>
</form>
</body>
</html>
 
It’s now time to run the application. On selecting a database, you can find out the Constraints, Keys, Stored Procedures, Views etc in that database.
Shot
I hope you liked the article and I thank you for viewing it.

This article has been editorially reviewed by Suprotim Agarwal.

Absolutely Awesome Book on C# and .NET

C# and .NET have been around for a very long time, but their constant growth means there’s always more to learn.

We at DotNetCurry are very excited to announce The Absolutely Awesome Book on C# and .NET. This is a 500 pages concise technical eBook available in PDF, ePub (iPad), and Mobi (Kindle).

Organized around concepts, this Book aims to provide a concise, yet solid foundation in C# and .NET, covering C# 6.0, C# 7.0 and .NET Core, with chapters on the latest .NET Core 3.0, .NET Standard and C# 8.0 (final release) too. Use these concepts to deepen your existing knowledge of C# and .NET, to have a solid grasp of the latest in C# and .NET OR to crack your next .NET Interview.

Click here to Explore the Table of Contents or Download Sample Chapters!

What Others Are Reading!
Was this article worth reading? Share it with fellow developers too. Thanks!
Share on LinkedIn
Share on Google+

Author
Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigious Microsoft MVP award for ten consecutive times. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that offers Digital Marketing and Branding services to businesses, both in a start-up and enterprise environment.

Get in touch with him on Twitter @suprotimagarwal or at LinkedIn



Page copy protected against web site content infringement 	by Copyscape




Feedback - Leave us some adulation, criticism and everything in between!
Comment posted by Bryan on Tuesday, February 16, 2010 7:07 AM
thank for sharing
Comment posted by Shyner on Friday, May 7, 2010 5:18 AM
What a good article!
Comment posted by datta salve on Thursday, August 12, 2010 4:11 AM
plz my comment is insert query and update query do sol.
Comment posted by vicor u on Wednesday, January 21, 2015 12:59 PM
please how can I retrieve date from a data row in sql database using asp.net and display it on the text property of a label

Categories

JOIN OUR COMMUNITY

POPULAR ARTICLES

C# .NET BOOK

C# Book for Building Concepts and Interviews

Tags

JQUERY COOKBOOK

jQuery CookBook