A simple 3-tier layers application in ASP.NET

I will explain how to develop a simple application in ASP.NET using a three-tier architecture.

• The presentation tier
• The business tier
• The data tier

Concepts:

The presentation tier contains the UI (User Interface) elements of the site, and includes all the logic that manages the interaction between the visitor and the client’s business. (ASP.NET Web Forms,Web User Controls,ASP.NET Master Pages)

The business tier receives requests from the presentation tier and returns a result to the presentation tier depending on the business logic it contains. (C# Classes)

The data tier  is responsible for storing the application’s data and sending it to the business tier when requested. (SQL Server Stored Procedures)

The first Step is to create a new Web Site – C# Language.

We need to add a Master Page to define our web site style.

Here is the design of our application.

Creating the Database

Script:

Create Database SampleDb
Use SampleDb
Create Table Client
(
    clientID int not null,
    name varchar(50) not null,
    description varchar(1000)
)
Alter Table Client Add Constraint PK_Client Primary Key(clientID)

Insert into Client Values(001,'Edison Daniel García Chiñas','Mexico - CodeApp.NET Company')
Insert into Client Values(002,'Esperanza Ubaldo Mota','Mexico - COPESA Company');
Creating the Store Procedure
Create Procedure GetClients
As
Select clientID,name,description From Client

Adding Logic to the Site

We need 3 classes

• Configuration
• GenericData
• Client

Creating the Configuration Class

First Add an ASP.NET Folder called App_Code and then inside of it a class called Configuration.cs

Configuration Class Code:

1. - Add the System.Configuration namespace
         .

     using System.Configuration;
namespace SConfiguration {

public
static class Configuration {
 private
  static string dbConnectionString;
 private
  static string dbProviderName;

  static Configuration() {
    dbConnectionString =
        ConfigurationManager.ConnectionStrings["ConnectionString"]
            .ConnectionString;
    dbProviderName =
        ConfigurationManager.ConnectionStrings["ConnectionString"].ProviderName;
  }

 public
  static string DbConnectionString {
    get { return dbConnectionString; }
  }

 public
  static string DbProviderName {
    get { return dbProviderName; }
  }
}

} 

2.- Go to web.config file to add the two values that we are going to take from the configuration class.

<connectionStrings> <add name=”ConnectionString” connectionString=”Data Source=.\MSPLAP;Initial Catalog=SampleDb;Persist Security Info=True;User ID=sa;Password=***********” providerName=”System.Data.SqlClient”/> </connectionStrings>

Creating the GenericData Class

First add these namespaces to the class.using System.Data; using System.Data.Common; using SConfiguration;

We need to create three methods for managing data.

1. ExecuteReader()  is for SELECT Statement . The reason is we just need all the data returned from the select query.
2. ExecuteNoneQuery() is for SQL Statements like INSERT, UPDATE, DELETE. The reason is simple, we don’t need to get data returned only the affected rows.
3. ExecuteScalar()  is for SELECT Statement when we just need the first data returned from a select query. (“The top”).

So, here is the first method:

public static DataTable ExecuteReader(DbCommand command)
        {
            DataTable table;
            try
            {
                command.Connection.Open();
                DbDataReader reader = command.ExecuteReader();
                table = new DataTable();
                table.Load(reader);
            }
            catch (Exception ex)
            {throw ex;}
            finally
            {command.Connection.Close();}
            return table;
        }

This ExecuteReader method has a DbCommand parameter, this command will get a DataTable result of the select query, the DbDataReader will read all the data from the Table(s). Finally the command is going to return the table.

public static int ExecuteNoneQuery(DbCommand command)
        {

            int AfectedRows = -1;
            try
            {   command.Connection.Open();
                AfectedRows = command.ExecuteNonQuery();
            }
            catch (Exception ex)
            { throw ex;}
            finally
            { command.Connection.Close();}
            return AfectedRows;
        }

This method will return the number of AfectedRows result of INSERT,DELETE,UPDATE statements.

 public static string ExecuteScalar(DbCommand command)
        {
            string value = "";
            try
            {
                command.Connection.Open();
                value = command.ExecuteScalar().ToString();
            }
            catch (Exception ex)
            {throw ex;}
            finally
            {command.Connection.Close();}
            return value;
        }

Finally we just need a CreateCommand Method:

 //Create a command
        public static DbCommand CreateCommand()
        {

            string dbProviderName = Configuration.DbProviderName;
            string dbConnectionString = Configuration.DbConnectionString;
            DbProviderFactory factory = DbProviderFactories.GetFactory(dbProviderName);
            DbConnection connection = factory.CreateConnection();
            connection.ConnectionString = dbConnectionString;
            DbCommand command = connection.CreateCommand();
            command.CommandType = CommandType.StoredProcedure;
            return command;

        }

In this method we define what command type we are going to use, in this sample website is going to be StoredProcedure type.

GenericClass Complete Code:

using System;
using System.Collections.Generic;
using System.Web;
using System.Data;
using System.Data.Common;
using SConfiguration;

namespace DataAcess {

public
static class GenericData {
  static GenericData() {}

 public
  static DataTable ExecuteReader(DbCommand command) {
    DataTable table;
    try {
      command.Connection.Open();
      DbDataReader reader = command.ExecuteReader();
      table = new DataTable();
      table.Load(reader);
    } catch (Exception ex) {
      throw ex;
    } finally {
      command.Connection.Close();
    }
    return table;
  }

  // Create a command
 public
  static DbCommand CreateCommand() {
    string dbProviderName = Configuration.DbProviderName;
    string dbConnectionString = Configuration.DbConnectionString;
    DbProviderFactory factory = DbProviderFactories.GetFactory(dbProviderName);
    DbConnection connection = factory.CreateConnection();
    connection.ConnectionString = dbConnectionString;
    DbCommand command = connection.CreateCommand();
    command.CommandType = CommandType.StoredProcedure;
    return command;
  }

 public
  static int ExecuteNoneQuery(DbCommand command) {
    int AfectedRows = -1;
    try {
      command.Connection.Open();
      AfectedRows = command.ExecuteNonQuery();
    } catch (Exception ex) {
      throw ex;
    } finally {
      command.Connection.Close();
    }
    return AfectedRows;
  }

 public
  static string ExecuteScalar(DbCommand command) {
    string value = "";
    try {
      command.Connection.Open();
      value = command.ExecuteScalar().ToString();
    } catch (Exception ex) {
      throw ex;
    } finally {
      command.Connection.Close();
    }
    return value;
  }
}
}  // namespace DataAcess
Creating the Client Class

    using System.Data;
using System.Data.Common;
using DataAcess;

namespace SClient {
public
class Client {
 public
  static DataTable GetClients() {
    DbCommand command = GenericData.CreateCommand();
    command.CommandText = "GetClients";
    return GenericData.ExecuteReader(command);
  }
}

}

Adding the presentation Layer

Add a Web User Control called ClientsList.ascx.

Then add a gridview into the design tab.

After that double click to ClientsList.ascx.cs to add the fowolling code:

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

public partial class Controls_ClientsList : System.Web.UI.UserControl
{
    protected void Page_Load(object sender, EventArgs e)
    {
        GridView1.DataSource = Client.GetClients();
        GridView1.DataBind();
    }
}

Create a Content Page (New WebForm) from the MasterPage and drag and drop the ClientsList server control to the contentplaceHolder. Press F5.

See you in the other post of this sample…