[Report #1] Populate ASP DropDownList Using a Database

In this blog post I am going to demonstrate how to use an ASP DropDownList with a database.  This will be the first part of a multi-part series on simple, on the fly, ad-hoc reporting.

This example was developed using Visual Studio Express 2012 Web.  The project is a WebForms project but this can be easily used with a MVC project as well.  This example uses the Northwind Sample Database from the SQL Server 2000 Sample Databases.  You can download and get instruction on how to install the Northwind database here.  You can also use the newer AdventureWorks database which can be found here.

First we need to create a webforms page with an ASP DropDownList.

Report.aspx

<%@ Page Title="" Language="C#" MasterPageFile="~/Site.Master" AutoEventWireup="true" CodeBehind="report.aspx.cs" Inherits="reports.report" %>
<asp:Content ID="Content1" ContentPlaceHolderID="HeadContent" runat="server">
    <script type="text/javascript" src="../Scripts/report.js"></script>
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="FeaturedContent" runat="server">
</asp:Content>
<asp:Content ID="Content3" ContentPlaceHolderID="MainContent" runat="server">
    <asp:DropDownList ID="ddl_Tables" runat="server"
                    ClientIDMode="Static"></asp:DropDownList>
</asp:Content>

Now in the C# aspx.cs file we will open a connection to a database, populate a List<string> and set it as the DropDownList's DataSource.

Report.aspx.cs

using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace reports
{
    public partial class report : System.Web.UI.Page
    {
        private SqlConnection myConnection;
        private string myConnectionString;

        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                try
                {
                    OpenConnection();
                    populateTablesDdl(sender, e);
                }
                catch
                {
                    throw;
                }
            }
        }

        protected void populateTablesDdl(object sender, EventArgs e)
        {
            List<string> tableNames = new List<string>();
            try
            {
                using (myConnection)
                {
                    OpenConnection();
                    DataTable schema = myConnection.GetSchema("Tables");

                    foreach (DataRow row in schema.Rows)
                    {
                        if (!row[2].ToString().Contains("_dss"))
                        {
                            tableNames.Add(row[2].ToString());
                        }
                        else
                        {
                            //nothing at this time
                        }
                    }
                    ddl_Tables.DataSource = tableNames;
                    ddl_Tables.DataBind();
                }
            }
            catch (Exception ex)
            {
                throw;
            }
            ddl_Tables.Items.Insert(0, new ListItem("<Select Table>", " "));
        }

        protected void OpenConnection()
        {
            // Create a connection object
            myConnection= new SqlConnection();

            // Create the connection string
            myConnectionString= @ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
            myConnection.ConnectionString = myConnectionString;

            // Open the connection
            myConnection.Open();
        }
    }
} 

Note: There's a method and an if statement I need to explain. First, myConnection.getSchema("Tables") is used to populate a DataTable. There are different ways to get the database tables but the getSchema method is clean. When I develop a webapp, one tool I often use is Microsoft SQL Data Sync (Preview). One thing about Data Sync (Preview) is that it makes copies of the User Tables with a _dss suffix. I use if(!row[2].ToString().Contains("_dss")) to filter out these tables.  It should work fine with your tables unless your naming convention uses _dss.

On my next post I will build on this example and show how to populate an ASP ListBox with the RowNames of any table the user selects. Until next time, keep calm and carry on.