In-Depth

Hooking ASP.NET Apps into SQL Server 2012

Use custom-built .NET Framework 4.0 classes to connect an ASP.NET Web application to a SQL Server 2012 database.

• By Frank Solomon
• 11/14/2013

In my recent article, I showed how to connect .NET 4.0 C# and Visual Basic applications to SQL Server 2012 databases using .NET classes. That article and its examples focused on .NET 4.0 desktop application development, and not ASP.NET applications. As a follow-on, this article will use the technique to tie ASP.NET C# and Visual Basic applications to a SQL Server 2012 database.

This article assumes Visual Studio 2010 or later, and some version of SQL Server 2012. It describes sample ASP.NET C# and VB.net applications, each built with the .NET 4.0 framework. These applications connect to a sample SQL Server 2012 Express database. Inside the article download, I first placed the SQL Server script files for the sample database in a separate subdirectory, then placed each application in its own subdirectory within this .ZIP file. See the earlier article for a complete description of the database itself.

To install the DEMO database, run the SQL Server script files from the article download subdirectory in a SQL Server 2012 or SQL Server 2012 Express query window, in this order:

1. CREATE_DEMO_DATABASE_SCRIPT_1.sql
2. CREATE_DEMO_DATABASE_SCRIPT_2.sql
3. CREATE_DEMO_DATABASE_SCRIPT_3.sql

To delete the database, right-click it in the SQL Server Object Explorer and click Delete to launch the delete database wizard.

To install and configure CSharpDemoWebsite, the ASP.NET C# application, first place the complete CSharpDemoWebsite subdirectory in a target directory tree location. The application subdirectory can go in any target location; for this article, I placed it in C:\test_directory. Click the CSharpDemoWebsite.sln file to open the application in Visual Studio.

In Solution Explorer, right-click the CSharpDemoWebsite project node, highlighted in Figure 1, then open Property Pages. In the left text box, click Start Options. Click the Specific page: radio button and make sure the text box next to it says "main_form.aspx" as shown in Figure 2. Click Apply, then OK.

The next step involves the CSharpDemoWebsite default browser. The Web site defaults to Internet Explorer. To change this setting, if necessary, right-click the CSharpDemoWebsite project node, highlighted in Figure 1. Next, open the Browse With dialog box, as shown in Figure 3. Pick or add the chosen browser as appropriate, and click Set as Default. Finally, click Cancel, because Browse would try to launch the application in a browser before it's ready.

Now it's time to tie the C# application to the DEMO database. Although this material will closely resemble the explanation from the earlier article, I included it here because of its importance. Later on, I will show how to configure the Visual Basic application, and how to connect it to the database.

The application configuration described here expects to see some form of SQL Server 2012 installed on the same machine. Since SQL Server has many deployment options -- like editions, server locations and so on -- different situations could need different specific configurations. However, pointing the application to the database will happen in file databaseConnection.cs. Although I could have placed the connection string in web.config, I used class databaseConnection to keep the structure of CSharpDemoWebsite as similar as possible to that of CSharpDemo from the earlier article.

I'll first look for the name of the database server instance in the SQL Server Object Explorer. In Figure 4, the highlighted text has what I need. From this "string" of characters:

FRANK-PC\SQLEXPRESS (SQL Server 11.0.3128 - Frank-PC\Frank)

I pulled out the left "substring" before the left parenthesis, then trimmed the trailing space:

FRANK-PC\SQLEXPRESS

Then I placed this value between the double quotes in the databaseConnection.cs, as shown in Listing 1.

using System.Data.SqlClient;

public class databaseConnection
{
  public static SqlConnection CreateSqlConnection()
  {
    string connectionString;

    connectionString = 
      "workstation id=" + System.Environment.MachineName;
    connectionString += ";packet size=4096;integrated security=SSPI;";
    connectionString += "data source=";

    // Use the name of the SQL Server instance hosting the DEMO 
    // database as the data source value in connectionString . . .

    connectionString += "FRANK-PC\\SQLEXPRESS"; 
    // <-- The name of the SQL Server instance . . .
    connectionString += ";persist security info=False;initial" +
      "catalog=DEMO;MultipleActiveResultSets=True";

    return new SqlConnection(connectionString);
  }
}

C# wants all the embedded backslashes in this substring escaped, so I replaced the existing backslash with two backslashes:

FRANK-PC\\SQLEXPRESS

In my environment, the databaseConnection class will now build SqlConnection objects that point to the DEMO database.

The application itself works almost exactly like CSharpDemo from the earlier article, with two small differences, shown in Figure 5. First, the COUNTIES dropdown list box opens a JavaScript alert box, while the CSharpDemo COUNTIES combobox opens a messagebox. Second, each CSharpDemoWebsite datagrid row has a "Counties Dropdown" button to close the datagrid itself and return focus to the COUNTIES dropdown list.

As in CSharpDemo, the CSharpDemoWebsite application uses objects built from the parentDBObject and childDBObject classes to fill the STATES and COUNTIES dropdown list boxes. Listing 2 shows the CSharpDemoWebsite version of childDBObject.

using System;
using System.Data;
using System.Web;
using System.Web.UI;

/// <summary>
/// Class childDBObject derives from class parentDBObject
/// </summary>

namespace CSharpDemoWebsite
{
  public class childDBObject : parentDBObject
  {
    private DateTime _dateTimeStamp;

    // Public get-set exposing the private date/time stamp field . . .

    public DateTime dateTimeStamp
    {
      get { return _dateTimeStamp; }
      set { _dateTimeStamp = value; }
    }

    public override DataTable executeSP()
    {
      DataTable localDataTable;
      String localRowCount;
      String localSPName;

      localDataTable = base.executeSP();

      if (!(localDataTable == null))
      {
        localRowCount = localDataTable.Rows.Count.ToString();
        localSPName = base.SPName;
        string msgString;

        // The message box shows that the call to
        // the derived class method actually
        // worked. For each hard return in the
        // popup, use "\n" and remember that C# expects
        // me to escape the backslash . . .

        msgString = "Stored procedure\\n\\n" + localSPName + "\\n\\n";
        msgString += "executed\\n\\n" + this.dateTimeStamp + "\\n\\n";
        msgString += "and returned " + localRowCount + " row(s).";

        Page localAlert = HttpContext.Current.CurrentHandler as Page;
        localAlert.ClientScript.RegisterStartupScript(this.GetType(), 
          "ShowMessage", string.Format(
          "<script type='text/javascript'>alert('{0}')</script>", 
          msgString));

        // Return the finished datatable as the return value . . .

        return localDataTable;
      }
      else
      {
        return null;
      }
    }
  }
}

The childDBObject class here closely matches the childDBObject class from CSharpDemo found in the first article, with two differences: First, the namespace value changed; second, the class executeSP method builds a JavaScript alert box instead of the message box used in that earlier version.

Similar to the main_form code from Listing 3 in the earlier article, the code in Listing 3 below shows how to use the parentDBObject and childDBObject classes in an ASP.NET application.

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

public partial class main_form : System.Web.UI.Page
{
    private int _stateID;

    protected void Page_Load(object sender, EventArgs e)
    {
        //  The main form design sets the control states / properties, but
        //  call the load states dropdown method when the page loads . . .

        //  On first load of form AKA application launch, call
        //  the method to fill the states dropdown . . .

        if (!IsPostBack)
        {
            this.fillStatesDDLDS();
        }
    }

    protected void statesDropDownList_TextChanged(object sender, EventArgs e)
    {
        //  Set the class stateID property based on the selected
        //  states dropdown value, set form control properties
        //  as appropriate, and call the load counties dropdown
        //  method . . .

        this._stateID = Convert.ToInt32(this.statesDropDownList.SelectedValue);
        this.countiesDropDownList.Enabled = true;
        this.statesDropDownList.Enabled = false;
        this.btnBack.Enabled = true;
        this.loadCountiesDDL();
    }

    protected void countiesDropDownList_TextChanged(object sender, EventArgs e)
    {
        //  The user might choose the first value in the counties
        //  dropdown - PICK A COUNTY - so simply ignore that
        //  pick . . .

        if (this.countiesDropDownList.SelectedValue != "")
        {
            //   Declare a child data access object . . .

            childDBObject listCitiesOfCountiesDT = new childDBObject();

            //   Set up a list of SQL parameters for
            //   the relevant stored procedure . . .

            List<SqlParameter> prmList = new List<SqlParameter>();

            prmList.Add(new SqlParameter("@COUNTY_ID", Convert.ToInt32(this.countiesDropDownList.SelectedValue)));

            //   Set the stored procedure for the data access object . . .

            listCitiesOfCountiesDT.SPName = "SELECT_CITIES_OF_COUNTY";

            //   Set the data access object dateTimeStamp and SPPrms properties . . .

            listCitiesOfCountiesDT.dateTimeStamp = DateTime.Now;
            listCitiesOfCountiesDT.SPPrms = prmList;

            //   The childDBObject.ExecuteSP method overrides
            //   method DBObject.ExecuteSP . . .

            this.citiesOfCountyDG.DataSource = listCitiesOfCountiesDT.executeSP();
            this.citiesOfCountyDG.DataBind();

            this.statesDropDownList.Enabled = false;
            this.countiesDropDownList.Enabled = false;
            this.btnBack.Enabled = false;
            this.citiesOfCountyDG.Visible = true;
        }
    }

    private void fillStatesDDLDS()
    {
        //   Dim a new database access object, and set
        //   the stored procedure property. This SP
        //   doesn't need parameters, so don't worry
        //   about the SPParms property . . .

        parentDBObject listStatesDT = new parentDBObject();
        listStatesDT.SPName = "SELECT_ALL_STATES";

        //   Call the executeSP object method
        //   and use this as the combobox
        //   data source . . .

        this.statesDropDownList.DataSource = listStatesDT.executeSP();
        this.statesDropDownList.DataTextField = "STATE_NAME";
        this.statesDropDownList.DataValueField = "STATE_ID";
        this.statesDropDownList.DataBind();

        //  Artificially place a dummy dropdown list item so that the
        //  first result set pick in that list will work . . .

        this.statesDropDownList.Items.Insert(0, new ListItem("PICK A STATE", ""));
    }

    private void loadCountiesDDL()
    {
        //  Remove the dummy dropdown list item . . .

        this.statesDropDownList.Items.Remove(new ListItem("PICK A STATE", ""));
        parentDBObject listCountiesOfStateDT = new parentDBObject();
        listCountiesOfStateDT.SPName = "SELECT_COUNTIES_OF_STATE";

        //   Set up a list of SQL parameters for
        //   relevant the stored procedure . . .

        List<SqlParameter> prmList = new List<SqlParameter>();

        prmList.Add(new SqlParameter("@STATE_ID", _stateID));
        listCountiesOfStateDT.SPPrms = prmList;

        //   Call the executeSP object method
        //   and use this as the combobox
        //   data source . . .

        this.countiesDropDownList.DataSource = listCountiesOfStateDT.executeSP();
        this.countiesDropDownList.DataTextField = "COUNTY NAME";
        this.countiesDropDownList.DataValueField = "COUNTY_ID";
        this.countiesDropDownList.DataBind();

        //  Artificially place a dummy dropdown list item so that the
        //  first result set pick in that list will work . . .

        this.countiesDropDownList.Items.Insert(0, new ListItem("PICK A COUNTY", ""));
    }

    protected void btnBack_Click(object sender, EventArgs e)
    {
        //  Set control properties / states appropriately, clear
        //  the counties dropdown, and clear / refill the states
        //  dropdown . . .

        this.btnBack.Enabled = false;
        this.countiesDropDownList.Items.Clear();
        this.countiesDropDownList.Enabled = false;
        this.statesDropDownList.Enabled = true;
        this.fillStatesDDLDS();
    }

    protected void citiesOfCountyDG_RowDataBound(object sender, GridViewRowEventArgs e)
    {
        //  Hide the state ID and city ID columns . . .

        if ((e.Row.RowType == DataControlRowType.DataRow) || (e.Row.RowType == DataControlRowType.Header))
        {
            e.Row.Cells[1].Visible = false;
            e.Row.Cells[4].Visible = false;
        }
    }

    protected void citiesOfCountyDG_RowCommand(object sender, GridViewCommandEventArgs e)
    {
        //  After the user clicks the datagrid command button to return
        //  to the counties dropdown, set focus to that dropdown, enable
        //  that dropdown, select PICK A COUNTY in that dropdown, hide
        //  the datagrid, and enable the back button . . .

        this.countiesDropDownList.Focus();
        this.countiesDropDownList.Enabled = true;
        this.countiesDropDownList.SelectedValue = "";
        this.citiesOfCountyDG.Visible = false;
        this.btnBack.Enabled = true;
    }
}

To fill statesDropDownList, the STATES dropdown list box of the main form, method fillStatesDDLDS first initializes a parentDBObject class variable called listStatesDT. Then it sets the stored procedure name property of this object.

Next, the method sets the list box DataSource property to the object executeSP method. To configure the list box correctly, the method then sets the list box DataTextField and DataValueField properties based on column names in the SELECT_ALL_STATES stored procedure result set. Then it calls the DataBind method. Finally, the method inserts a dummy row at the top of the list box.

The CSharpDemoWebsite main_form code event handler

	countiesDropDownList_TextChanged

works almost exactly like the event handler

	countiesComboBox_SelectionChangeCommitted

of main_form in the earlier article. Here, the handler verifies that the user picked an actual data value in the COUNTIES dropdown list. If so, it initializes a childDBObject class variable called

	listCitiesOfCountiesDT

and then declares a SqlParameter list object. It adds a SqlParameter object to that list object, set to the list box pick, and proceeds. Finally, the method sets form control properties as required by the business rules.

When the user makes a pick in the COUNTIES dropdown list, code in

	countiesDropDownList_TextChanged

(the TextChanged event handler of this dropdown) makes the citiesOfCounty datagrid visible. At this point, code in

	citiesOfCountyDG_RowDataBound

(the RowDataBound event of the datagrid) hides the STATE_ID and CITY_ID result set columns. I kept these result set columns because future applications might need them, and I hid them here. When the user clicks a datagrid command button, event handler

	citiesOfCountyDG_RowCommand

hides the datagrid itself. It then returns focus to the COUNTIES dropdown list, highlighting the dummy row.

The code and behavior of application VBNetDemoWebsite, included in the download, essentially clones CSharpDemoWebsite over to Visual Basic. All the configuration steps for VBNetDemoWebsite match the steps for CSharpDemoWebsite, except for small differences in the database connection code. I'll explain this now.

To point VBNetDemoWebsite to the DEMO database, find the name of the database service instance as described above, and place this value between the double quotes in file databaseConnection.vb, as shown in Listing 4.

Imports System.Data.SqlClient

Public Class databaseConnection

  Public Shared Function CreateSqlConnection() As SqlConnection

    Dim connectionString As String

    connectionString = "workstation id=" + My.Computer.Name
    connectionString += ";packet size=4096;integrated security=SSPI;"
    connectionString += "data source="

    ' Use the name of the SQL Server instance hosting the DEMO 
    ' database as the data source value in connectionString . . .

    connectionString += 
      "FRANK-PC\SQLEXPRESS" ' <-- The name of the SQL Server instance . . .
    connectionString += ";persist security info=False;initial catalog=
      DEMO;MultipleActiveResultSets=True"

    Return New SqlConnection(connectionString)

  End Function

End Class

VBNetDemoWebsite is now ready. Because C# and Visual Basic are similar, understanding CSharpDemoWebsite means understanding VBNetDemoWebsite.

With the parentDBObject and childDBObject classes, ASP.NET developers have a simple, streamlined way to tie ASP.NET applications to the database layer. This approach offers substantial development, testing and maintenance savings.

• Get Code Download