[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.

Using JavaScript To Collect Data In ASP Table

This builds on my first post which demonstrates how to generate tables using C#.  If you haven't read my first post that is perfectly fine.  I have written my posts so each one is complete on its own.  Please click the following link if you would like to read my first post Dynamically Creating asp Tables in C#.

This example as with all of my .NET posts was written using Visual Studio Express 2012 Web.  This was developed in a webforms project but this can be used in any MVC project as well.

We first start with a table with each row containing an ASP CheckBox, Label, and HiddenField element.

Tables.aspx

<script src="../Scripts/reporting.js" type="text/javascript"></script>
    <input type="submit" name="button1" value="show table" id="button1" />
    <table id="table1">
 <tr>
  <td>
     <input id="cb_0" type="checkbox" name="cb_0" />
  </td>
  <td>
     <span id="lbl_0">0</span>
     <input type="hidden" name="hidden_0" id="hidden_0" value="0" />
  </td>
 </tr>
 <tr>
  <td>
     <input id="cb_1" type="checkbox" name="cb_1" />
  </td>
  <td>
     <span id="lbl_1">1</span>
     <input type="hidden" name="hidden_1" id="hidden_1" value="1" />
  </td>
 </tr>
 <tr>
  <td>
     <input id="cb_2" type="checkbox" name="cb_2" />
  </td>
  <td>
     <span id="lbl_2">2</span>
     <input type="hidden" name="hidden_2" id="hidden_2" value="2" />
  </td>
 </tr>
 <tr>
  <td>
     <input id="cb_3" type="checkbox" name="cb_3" />
  </td>
  <td>
     <span id="lbl_3">3</span>
     <input type="hidden" name="hidden_3" id="hidden_3" value="3" />
  </td>
 </tr>
 <tr>
  <td>
     <input id="cb_4" type="checkbox" name="cb_4" />
  </td>
  <td>
     <span id="lbl_4">4</span>
     <input type="hidden" name="hidden_4" id="hidden_4" value="4" />
  </td>
 </tr>
</table>
    <input
      type="submit"
      name="button2"
      value="get values"
      onclick="getValues(event);"
      id="button2" />
    <input type="hidden" name="hidden1" id="hidden1" />

Again, if you read my first post Dynamically Creating asp Tables in C# this would be the table created by the C# code behind. Now we want to have the users select checkbox(es) and click a button to get all the values of the rows the user checked. There are a few, actually many ways to approach this really. You could build an array and add or remove items to and from it the moment the user checks or unchecks a checkbox using the onchange event handler and checking if the checkbox was checked. I didn't like this approach. I wanted the user to make their selection, then have the selections gathered and passed to the hidden field all at once. To do this we need to use a loop to iterate through the table.

Tables.js

function addNames(event) {
    event.preventDefault();

    var hidden_names = document.getElementById('hidden1');
    var table = document.getElementById('table1');
    var inputs = table.getElementsByTagName('input');
    var inputsPerRow = 

        table.rows[0].getElementsByTagName('input').length;

    hidden_names.value = '';

    for (i = 0; i < inputs.length; i+=inputsPerRow) {
        if (inputs[i].type === 'checkbox') {
            if (inputs[i].checked) {
                if (
                       hidden_names.value == null || 
                       hidden_names.value == '') {

                    hidden_names.value = inputs[i+1].value;
                }
                else if (hidden_names.value != null) {
                    hidden_names.value += 

                        ', ' + inputs[i + 1].value;
                }
                else {
                    //nothing for now
                }
            }
        }        
    }
    alert(hidden_names.value);    
}  

This function gets all of the table input elements using table.getElemetnsByTagName('input'). This creates a list of elements that have an <input> tag. Note: When I first thought of this I tried not using <asp:HiddenField> and only used <asp:Label> in my table and thought I would be able to access the label text or value. What I learned was asp labels do not render as anything but text. I would have to use table.getElementsByTagName('span') and then iterate through those checking each element. I felt that was wasteful so I simply used the HiddenField..

In my next post I will show how to populate dynamically generated tables using input from a SQL database. 

Dynamically Creating asp Tables in C#

For a project I was tasked with at work I needed to program something that had to generate tables on the fly based on user input, accept user selections via textboxes on that table and loop through the table gathering the users' selections and append that to an asp hiddenfield to be passed back to C# codebehind.  I had the concept in my head on how to program it but I did run into some problems and didn't see much documentation on the web on what I wanted to do so I decided to start a blog.  This is my first blog and first post.  Hope I can be clear and to the point.  Enjoy!

I am programming using Visual Studio Express 2012 Web Edition.  The project I am working with is a webforms project.  This concept can be easily translated to any MVC project.  First off we need to generate the table programmatically and assign meaningful, unique names to the table elements. Using a simple for loop I am creating a table containing an asp CheckBox, Label, and HiddenField with a value in each row.

Tables.aspx

<%@ Page Title="" Language="C#" MasterPageFile="~/Site.Master" AutoEventWireup="true" CodeBehind="Tables.aspx.cs" Inherits="Project.Tables" %>
<asp:Content ID="Content1" ContentPlaceHolderID="HeadContent" runat="server">
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="FeaturedContent" runat="server">
</asp:Content>
<asp:Content ID="Content3" ContentPlaceHolderID="MainContent" runat="server">
    <script src="../Scripts/table.js" type="text/javascript"></script>
    <asp:Button ID="button1" runat="server" Text="show table"
        ClientIDMode="Static"
        OnClick="createTable"/>
    <asp:Table ID="table1" runat="server"
        ClientIDMode="Static"></asp:Table>
    <asp:Button ID="button2" runat="server"
        Text="get values"
        ClientIDMode="Static"
        OnClientClick="addNames(event)" /> //ignore this
                                           //javascript function
                                           //for now
    <asp:HiddenField ID="hidden1" runat="server" //also ignore
                                                 //this for now
        ClientIDMode="Static" />
</asp:Content>
Pretty simple.  We just create a webform(with or without masterpage), added a couple of buttons, a table element, and one hidden field.  We will use this hidden field to store values later.

Tables.aspx.cs:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace Project
{
    public partial class test : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                //do nothing
            }
        }

        protected void createTable(object sender, EventArgs e)
        {
            for (int i = 0; i < 5; i++)
            {
                TableRow tr = new TableRow();

                TableCell tc1 = new TableCell();
                TableCell tc2 = new TableCell();

                HiddenField hiddenField = new HiddenField();
                hiddenField.ID = "hidden_" + i;
                hiddenField.Value = i.ToString();
                hiddenField.ClientIDMode = 
                    System.Web.UI.ClientIDMode.Static;

                Label lbl = new Label();
                lbl.ID = "lbl_" + i;
                lbl.Text = i.ToString();
                lbl.ClientIDMode = 
                    System.Web.UI.ClientIDMode.Static;

                CheckBox cb = new CheckBox();
                cb.ID = "cb_" + i;
                cb.AutoPostBack = false;
                cb.ClientIDMode = 
                    System.Web.UI.ClientIDMode.Static;

                tc1.Controls.Add(cb);
                tc2.Controls.Add(lbl);
                tc2.Controls.Add(hiddenField);

                cb.Attributes.Add("onchange",
                    "Javascript:addNames(event)");

                tr.Controls.Add(tc1);
                tr.Controls.Add(tc2);

                table1.Controls.Add(tr);
            }
        }
    }
}
Method createTable will build our table row by row, adding the controls to the cells and cells to the rows.  Note, if (!IsPostBack) this will prevent the page from completely reloading and resetting data. If you do not understand IsPostBack please follow this tutorial and you will get it, http://www.codeproject.com/Articles/12810/The-Intricacies-of-the-IsPostBack-If-Block-in-ASP

At this point the webapp will create a table of any length you want with a checkbox, label, and a hiddenfield with a value.  These checkboxes and hiddenfields all have unique names and can be accessed from the codebehind C#.

Well this was my first blog and first post.  Next post I will show how to get the value of the hidden fields if the checkbox in the row is checked and append the hiddenfields' values to one hidden field that will store them all using JavaScript!