In-Depth

How to Use .NET Objects to Tie an Application to a Database

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

• By Frank Solomon
• 10/18/2013

Database connectivity gives desktop and Web applications much of their value. .NET developers have products and framework objects that can tie a front-end application to SQL Server, MySQL, Sybase, Oracle and Access databases. The approach is straightforward and becomes even more powerful when expressed as a .NET class.

The .NET Framework makes class and object development easy, and makes the use of those classes and objects even easier:

1. Classes abstract, or hide, the actual machinery behind the tasks they do.
2. Their simple syntax works almost like a black box: the developer creates an object based on a class, then might set one or more object properties, and finally invokes an object method, or command.
3. A common, centralized code base for the database-related objects used throughout the application improves security, especially database security.
4. This centralization makes code enhancement and maintenance much easier.

This article explores sample .NET 4.0 C# and VB.NET applications, which each include parent/derived classes to handle all this. For these example applications, the classes assume a SQL Server database included in the article download. To better structure the code samples, I placed each application in its own separate directory, and I placed the SQL Server script files for the database in their own separate directory. Then, I rolled these three directories into a large parent directory for this article.

Objects based on this class take a required stored procedure name and zero or more stored procedure parameters, all as object properties. The C# objects return datatables, or null values, as appropriate through object method calls; the VB.NET objects return datatables, or nothing values, as appropriate through object method calls.

For this article, you should be using Visual Studio 2010 or later, and some version of SQL Server 2012 for the associated database. I used SQL Server 2012 Express.

I'll first focus on the database and describe how to install it. The database has three tables, as shown in Figure 1.

It also has three stored procedures, highlighted in Figure 2.

The tables relate states, counties, and cities with the expected primary/foreign key relationships, and the stored procedures return result sets. Run the SQL Server scripts included in the download in a SQL Server 2012 or SQL Server 2012 Express query window, in this order, to build the database itself:

1.	CREATE_DEMO_DATABASE_SCRIPT_1.sql

2.	CREATE_DEMO_DATABASE_SCRIPT_2.sql

3.	CREATE_DEMO_DATABASE_SCRIPT_3.sql

The scripts will place the component DEMO database files in C:\DEMO, building this directory if it doesn't exist. The scripts do this to make sure that database files will reliably land in a defined, existing directory. To delete the database, right-click it in the SQL Server Object Explorer and click Delete to launch the delete database wizard.

Now, I'll focus on the C# application included with this article. First, I'll explain how to configure the application to see the DEMO database.

The application assumes that SQL Server 2012 Express and Visual Studio 2010 are installed on the same computer. Since SQL Server has many deployment options, like editions, server locations and so on, different situations might need different specific configurations. However, pointing the application to the database will happen in file databaseConnection.cs.

I'll look for the name of the database server instance in the SQL Server Object Explorer. In Figure 3, the highlighted text has what I need.

From the entire "string" of characters here:

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

I extracted the left "substring" before the left parenthesis, and 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;";

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

        connectionString += "data source=";
        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);
    }
}

Note that C# expected me to escape all the embedded backslashes in this substring, 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.

Now, I'll briefly explain what the application does, and then I'll explore how the database access classes work.

This application has one form, with two combobox dropdowns, two buttons, and a datagridview as shown in Figure 4. Click the CLOSE APP button at any time to close the application. First, pick a state in the STATES combobox. This enables both the BACK button, to re-enable the STATES combobox for a new state pick, and the COUNTIES combobox. This second combobox has child counties of the selected state. Pick a county and a message box opens, as shown in Figure 5.

After clicking OK, the datagridview shows all the cities in the selected county. Click a city row here to clear the datagridview and return to the COUNTIES combobox. The application uses objects and classes to build the data sets that fill the dropdowns and the datagridview. Here's how it works:

The application uses objects created from the parentDBObject class, as shown in Listing 2, to fill the STATES and COUNTIES comboboxes.

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;

//	Author:		Frank

//	Create date:	4-20-13
//	Edit date:	5-09-13

//	Description:	Class to return a stored procedure result set as a dataset . . .

//	Input:          SPName()        <-- stored procedure name as class property
//                 	SPPrms()        <-- stored procedure parameters as list of
//      	                            SqlParameters class property; possibly
//					    empty
//
//	Output:		executeSP()     <-- finished result set as .Net DataTable; exposed
//                      		    as an object method call

namespace CSharpDemo
{
    public class parentDBObject
    {
        private SqlConnection local_conn = databaseConnection.CreateSqlConnection();

        private string _SPName = "";
        private List<SqlParameter> _prmList = new List<SqlParameter>();

        //  Public get-set exposing the private stored procedure name object field . . .

        public string SPName
        {
            get { return _SPName; }
            set { _SPName = value; }
        }

        public List<SqlParameter> SPPrms
        {
            get { return _prmList; }
            set { _prmList = value; }
        }

        public parentDBObject()
        {
            //   Basic constructor . . .
        }

        public virtual DataTable executeSP()
        {
            SqlCommand cmd = new SqlCommand();
            DataTable dt = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter();

            //  Make sure that the object has a stored procedure name . . . . . .

            if (String.IsNullOrEmpty(this.SPName))
            {
                return null;
            }


            //   Set up the command object, and tie the called stored
            //   procedure to the SPName object property . . .

            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = SPName;

            //   The SPPrms object property has all the stored procedure
            //   parameters in a list object, so extract them and add
            //   them to the command object parameters property . . .

            foreach (SqlParameter localPrm in SPPrms)
            {
                cmd.Parameters.Add(localPrm);
            }

            //   Set up the command object connection property . . .

            cmd.Connection = this.local_conn;

            //   Use try-catch to run the command object (AKA the
            //   stored procedure) and fill the data set . . .

            try
            {
                this.local_conn.Open();
                dt.Clear();
                da.SelectCommand = cmd;

                da.Fill(dt);
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }

            //   Clear out the existing parameter collection - each time
            //   a command object runs, it wants a new parameter
            //   collection. Therefore, throw out the existing
            //   parameters collection after command object
            //   execution . . .

            cmd.Parameters.Clear();

            //   Close the connection property ASAP . . .

            this.local_conn.Close();

            //   Count the rows in the data table; if it has
            //   at least one row, return that row, otherwise
            //   return Nothing . . .

            if (dt.Rows.Count > 0)
            {
                return dt;
            }
            else
            {
                return null;
            }
        }
    }
}

This class first makes five C# libraries available to the CSharpDemo namespace with the using directive. Then, the parentDBObject class inside the CSharpDemo namespace itself creates local_conn, a databaseConnection object pointing to the DEMO database as described above. The class declares private variables for the stored procedure name and the associated stored procedure parameters its objects will call. The parameters variable, _prmList, is a list, or collection, of SqlParameters. I used a list variable for the SQL parameters because a list can easily handle parameter "sets" with one or more elements using minimal code. Get-set accessors then make those variables publically available in a controlled way.

Method executeSP() is the most important part of the class. It unpacks the SQL parameter list property of the class, if passed, runs the specified stored procedure, and returns either a result set as a DataTable, or null.

First, the method initializes a SqlCommand object as cmd, a DataTable object as dt, and a SqlDataAdapter object as da. It tests the object SPName property to verify that the object has a stored procedure name, returning a null value if this property is null or empty. It then sets properties of SqlCommand object cmd to handle the stored procedure name, and extracts the stored procedure parameters from the SPPrms object property, adding them to the parameters property of SqlCommand object cmd.

Next, it points SqlCommand object cmd to the DEMO database using the local_conn variable set earlier. The actual stored procedure execution happens in a try catch block, which opens a message box for the error if something goes wrong. The method then clears the SqlCommand object cmd parameters property, because SqlCommand objects expect new parameters property values each time they execute. Since SqlCommand object Connection properties are expensive, the method closes this property as soon as possible. Finally, the method counts the rows in the DataTable object dt result set, returning null for zero rows or the DataTable itself otherwise.

In main_form.cs, as shown in Listing 3, the main_form() method calls the main_form class method fillStatesComboBox().

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Drawing;
using System.Windows.Forms;

namespace CSharpDemo
{
    public partial class main_form : Form
    {
        private int _stateID;

        public main_form()
        {
            InitializeComponent();

            //  Fill the states combobox . . .

            this.fillStatesComboBox();
            this.countiesComboBox.Enabled = false;

            //   Enable the back button so that the
            //   user can "roll back" to the states
            //   combobox . . .

            this.btn_back.Enabled = false;

            this.FormBorderStyle = FormBorderStyle.FixedSingle;
        }

        private void btn_back_Click(object sender, EventArgs e)
        {
            //   Shift focus to the states combobox, "zero" out
            //   the counties combobox, and disable the back
            //   button because it did its job . . .

            this.statesComboBox.Enabled = true;
            this.countiesComboBox.Enabled = false;
            this.countiesComboBox.DataSource = null;
            this.countiesComboBox.Items.Clear();
            this.btn_back.Enabled = false;
        }

        private void statesComboBox_SelectionChangeCommitted(object sender, EventArgs e)
        {
            //   Use SelectionChangeCommitted to prevent a DGV load when the form loads . . .

            this._stateID = (int)statesComboBox.SelectedValue;
            this.fillCountiesComboBox();

            //   Enable / disable controls as appropriate . . .

            this.statesComboBox.Enabled = false;
            this.countiesComboBox.Enabled = true;
            this.btn_back.Enabled = true;
        }

        private void countiesComboBox_SelectionChangeCommitted(object sender, EventArgs e)
        {
            //   Use SelectionChangeCommitted to prevent a DGV load when the form loads . . .

            //   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", (int)this.countiesComboBox.SelectedValue));

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

            listCitiesOfCountiesDT.SPName = "SELECT_CITIES_OF_COUNTY";

            //   Set the DGV header row font . . .

            DataGridViewCellStyle columnHeaderStyle = new DataGridViewCellStyle();
            columnHeaderStyle.Font = new Font("Verdana", 10, FontStyle.Bold);
            this.citiesOfCountyDGV.ColumnHeadersDefaultCellStyle = columnHeaderStyle;

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

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

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

            citiesOfCountyDGV.DataSource = listCitiesOfCountiesDT.executeSP();
            citiesOfCountyDGV.CurrentCell = citiesOfCountyDGV[1, 0];

            //   Hide the result set StateName and CityID columns . . .

            citiesOfCountyDGV.Columns[0].Visible = false;
            citiesOfCountyDGV.Columns[3].Visible = false;

            //   Hide the DGV row selectors . . .

            citiesOfCountyDGV.RowHeadersVisible = false;

            this.statesComboBox.Enabled = false;
            this.countiesComboBox.Enabled = false;
            this.btn_back.Enabled = false;
        }

        private void citiesOfCountyDGV_CellClick(object sender, DataGridViewCellEventArgs e)
        {
            int current_row;
            int state_id;

            int county_id;

            this.fillCountiesComboBox();

            //  Get the state_id and county_id values
            //  from the clicked DGV row . . .

            current_row = this.citiesOfCountyDGV.CurrentCellAddress.Y;
            state_id = (int)this.citiesOfCountyDGV[0, current_row].Value;
            county_id = (int)this.citiesOfCountyDGV[4, current_row].Value;

            this.countiesComboBox.Enabled = true;
            this.citiesOfCountyDGV.DataSource = null;

            //   Restore the counties combobox selected value . . .

            this.countiesComboBox.SelectedValue = county_id;
            this.countiesComboBox.Focus();
            this.btn_back.Enabled = true;
            this.countiesComboBox.Enabled = true;
        }

        private void btn_close_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        private void fillStatesComboBox()
        {
            //   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 listCitiesDT = new parentDBObject();
            listCitiesDT.SPName = "SELECT_ALL_STATES";

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

            statesComboBox.DataSource = listCitiesDT.executeSP();
            statesComboBox.DisplayMember = "STATE_NAME";
            statesComboBox.ValueMember = "STATE_ID";
        }

        private void fillCountiesComboBox()
        {

            //  Dim a new database access object, and set
            //  the stored procedure property . . .

            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.countiesComboBox.DataSource = listCountiesOfStateDT.executeSP();
            this.countiesComboBox.DisplayMember = "COUNTY NAME";
            this.countiesComboBox.ValueMember = "COUNTY_ID";
        }
    }
}	

The fillStatesComboBox() method initializes a parentDBObject class variable called listCitiesDT, sets the object stored procedure name property, and maps the statesComboBox DataSource property to the listCitiesDT executeSP() method.

Because the SELECT_ALL_STATES stored procedure has no parameters, I simply ignored the listCitiesDT SPPrms property: in this case, three lines tied the stored procedure result set to the form control. In the main_form() class, the statesComboBox SelectionChangeCommitted handler calls method fillCountiesComboBox(). This method works in a similar way, except that it declares a SqlParameter list object and adds a SqlParameter to that list.

This approach makes stored procedure parameter handling really easy; just add one line of code to the parameter list for each parameter. The object executeSP() method will unpack them. The method then sets the SPPrms property of the parentDBObject and proceeds.

The childDBObject class, shown in Listing 4, derives from the parentDBObject class.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Windows.Forms;

namespace CSharpDemo
{
    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;

                //  The message box shows that the call to
                //  the derived class method actually
                //  worked . . .

                MessageBox.Show("Stored procedure" + (char)13 + (char)13 +
                        localSPName + (char)13 + (char)13 +
                        "executed" + (char)13 + (char)13 + this.dateTimeStamp + (char)13 + (char)13 +
                        "and returned " + localRowCount + " row(s).",
                        "Method childDBObject.ExecuteSP . . .",
                        MessageBoxButtons.OK);

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

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

It has a private DateTime variable called _dateTimeStamp, seen through get set accessors as property dateTimeStamp. The executeSP() method of this child class overrides the base class executeSP() method, opening a message box that shows the value of property dateTimeStamp, and metadata about the stored procedure it executed.

Just like the parentDBObject, it returns a DataTable value, or null, as appropriate. In the main_form() class, the countiesComboBox SelectionChangeCommitted event handler uses the childDBObject to fill the citiesOfCountyDGV datagridview form control. The declaration and code for this object work exactly the same as that for parentDBObject, except that the handler sets the childDBObject dateTimeStamp property.

Although the childDBObject executeSP() method override serves more as an example, it definitely shows the potential of object method overrides. For example, I could easily add code to call an auditing stored procedure, to record a datestamp value each time the class executes a stored procedure, and more. It has endless possibilities.

Application VBDemo, included in the download, works the same as CSharpDemo. Pointing VBDemo to the DEMO database works almost exactly the same as it did for CSharpDemo. I placed the name of the database server instance between the double quotes in file databaseConnection.vb, as shown in Listing 5. Unlike databaseConnection.cs as described above, I did not escape the embedded backslash in the server instance name. VB.net won't mind embedded backslashes in a string value.

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;"

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

        connectionString += "data source="
        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

Because C# and VB.NET are so similar, the description of VBDemo closely matches that of CSharpDemo. One difference to note involves variable localPrm in the parentDBObject executeSP() method. In VBDemo, the method declares the variable at the top of the method; in CSharpDemo, executeSP() declares the variable in the foreach loop itself. That's because in C#, the foreach loop works that way.

In addition, the "lists" of libraries referenced at the top of the parentDBObject, childDBObject, and main_form classes differ between the C# and VB.NET versions.

Finally, I added a reference to System.Xml.Linq to VBDemo in the Solution Explorer to deal with a compiler warning message. Other than that, understanding CSharpDemo means understanding VBDemo.

The .NET Framework makes application database connectivity possible. As I showed, objects and classes built with the .NET Framework make this connectivity easy.

• Get Code Download