Web programming blog

I will create an example and we go through it. Suppose we have a task to display on some web page data from table customers.

at the moment I will not take care about database. let’s say that database already exists, tables and data are there. We can do this task very quickly. It is not some big deal. We will open connection to database, read data from table, display data and close connection.
all of that can be put in one class. Bu the question is: “Is that really good practice?” in my favorite C# I will write down some code. To follow me, create an empty ASP.NET project in your Visual Studio ( I use VS2005)and choose C# for language.
There is Default.aspx page in your solution. Add GridView control (drag and drop from Toolbox). Call your grid view as: “grdCustomers“.
Edit Default.aspx.cs file:

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

using System.Data.SqlClient;

namespace Customers
{
    public partial class _Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            string strConnection = null;
            string strSQL = "select customerID, customerName, customerContact from Customers ";

            strConnection = "server=DB_SERVER_NAME; initial catalog=TestDB; user=usernameValue; password=passwordValue";
            DataSet ds = new DataSet();

            SqlConnection conn = new SqlConnection(strConnection);
            conn.Open();

            SqlDataAdapter da = new  SqlDataAdapter(strSQL, conn);
            da.Fill(ds);

            grdCustomers.DataSource = ds;
            grdCustomers.DataBind();

            conn.Close();
        }
    }
}

and when we start web app with “F5″ button, we get this on the screen:

What is wrong with this? This is working, right. What if we need an other web page to display Employees. The most of the previous code will be the same except the sql query; and we will have the copy paste in our application which is bad. The other problem is sql server connection string which is hardcoded. First we will put sql connection string into configuration: Web.Config.
Edit web config to looks like this:

< ?xml version="1.0"?>

and after that, change your Default.aspx.cs code. This line:

            strConnection = "server=DB_SERVER_NAME; initial catalog=TestDB; user=usernameValue; password=passwordValue";

Chamge with this one:

            strConnection = ConfigurationManager.ConnectionStrings["SQLServerConnStr"].ConnectionString;

That is better. Now we do not need to think about sql server, its location or username/password. When we move our application to some other server, we only need to edit web.config file. No “building” necessary at all. But this can make some problem letter. If in the future Microsoft publish some better way for reading from web config we will need to change our code and recompile it again. To avoid this I will create one new project which will be used in entire solution for reading from web.config.

Create new project, type of Class library, language C#. Call it: “WebCommon“. Renamce Class1.cs to Configuration.cs

using System;
using System.Collections.Generic;
using System.Text;
using System.Configuration;

namespace WebCommon
{
    public static class Configuration
    {
        public static string ReadSqlConfiguration
        {
             get
             {
                return ConfigurationManager.ConnectionStrings["SQLServerConnStr"].ConnectionString;
             }
        }
    }
}

You need to add System.Configuration reference to this WebCommon project.

When this is done, now get back to our web project and add reference to WebCommon project.
this is how Default.aspx.cs looks after modification:

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

using System.Data.SqlClient;
using WebCommon;

namespace Customers
{
    public partial class _Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            string strConnection = null;
            string strSQL = "select customerID, customerName, customerContact from Customers ";

            strConnection = WebCommon.Configuration.ReadSqlConfiguration();
            DataSet ds = new DataSet();

            SqlConnection conn = new SqlConnection(strConnection);
            conn.Open();

            SqlDataAdapter da = new  SqlDataAdapter(strSQL, conn);
            da.Fill(ds);

            grdCustomers.DataSource = ds;
            grdCustomers.DataBind();

            conn.Close();
        }
    }
}

This is how we read from web.config now:

         strConnection = WebCommon.Configuration.ReadSqlConfiguration();

we need this using directive:

         using WebCommon;

Now we will separate our presentation layer and data access layer. Data access layer will deal with database. Create new Project, type of class Library, language C#. Call it: “DAL” Data Access Layer. Rename Class1.cs to Helper.cs
Enter this code to Helper.cs

using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace DAL
{
    public class Helper
    {
        public DataSet GetDataSet(string strSql, string strSqlServerConnection)
        {
            DataSet ds = new DataSet();

            SqlConnection conn = new SqlConnection(strSqlServerConnection);
            conn.Open();

            SqlDataAdapter da = new  SqlDataAdapter(strSql, conn);
            da.Fill(ds);             

            conn.Close();
            return ds;
        }
    }
}

There is a public method GetDataSet(string strSql, string strSqlServerConnection) which executes sql query and return populated data set.
Now add reference to this project to our web project and modify Default.aspx.cs:

using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

using System.Data.SqlClient;
using WebCommon;
using DAL;

namespace Customers
{
    public partial class _Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            DataSet ds;
            string strSQL = "select customerID, customerName, customerContact from Customers ";

            DAL.Helper objHelper = new DAL.Helper();
            ds = objHelper.GetDataSet(strSQL, WebCommon.Configuration.ReadSqlConfiguration);

            grdCustomers.DataSource = ds;
            grdCustomers.DataBind();
        }
    }
}

This is code in code behind of Default.aspx page. As you can see there is a less code than in the begining. If we need some other page to display similiar info like employees, we will create new page and put some code like this:

using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

using System.Data.SqlClient;
using WebCommon;
using DAL;

namespace Customers
{
    public partial class _Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            DataSet ds;
            string strSQL = "select employeID, employeeName, employeeAddress, zipCode, COuntry from Employees";

            DAL.Helper objHelper = new DAL.Helper();
            ds = objHelper.GetDataSet(strSQL, WebCommon.Configuration.ReadSqlConfiguration);

            grdCustomers.DataSource = ds;
            grdCustomers.DataBind();
        }
    }
}

If I think a litle bit more I will decide to remove this sql queries from my web project. web project should be presentation layer and there we only use information for publishing or perform some actions. I will create one more Layer for some business logic and derision making. It will be BL(Business Layer)

Create new Project, type of class Library, call it BL and renamce Class1.cs to Customers.cs and put this code there.

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;

using WebCommon;
using DAL;

namespace BL
{
    public class Customers
    {
        public DataSet GetCustomers()
        {
            DataSet ds;
            string strSQL = "select customerID, customerName, customerContact from Customers ";

            DAL.Helper objHelper = new DAL.Helper();
            ds = objHelper.GetDataSet(strSQL, WebCommon.Configuration.ReadSqlConfiguration);
            return ds;
        }

        public int UpdateCustomer()
        {
            // to do
            return 0;
        }

        public int DeleteCustomer(int customerID)
        {
            // to do
            return 0;
        }

    }
}

You need to add references to “WebCommon” and to “DAL” in “BL” project. And now modify Web project. Remove reference to DAL and WebCommon and add reference to BL.
edit Default.aspx.cs:

using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

using System.Data.SqlClient;
using BL;

namespace Customers
{
    public partial class _Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            DataSet ds;

            BL.Customers objCustomers = new BL.Customers();
            ds = objCustomers.GetCustomers();

            grdCustomers.DataSource = ds;
            grdCustomers.DataBind();
        }
    }
}

Now our presentation layer, GUI or web form doesn’t know about data sources. All data it will get from BL. Just call proper method of BL. In BL project we can add more classes for Employees, Customers, Orders, …
DAL (Data Access Layer) is used to provide information.
that is all for now. Have a good day.