3 Layered ASP.NET Application Example - Part 2

• Open Microsoft Visual Studio.
• Create a new ASP.NET Empty Web Application.
• Name the project PersonalTaskTracker
• Right click the Solution and add a new project which is Console Application named DAL.
• Right click the DAL project and add the reference to System.Configuration

Add the following two classes to the DAL project.

Database.cs

using System.Configuration;

namespace DAL
{
    /// 

    /// Database class.
    /// 
    public class Database
    {
        /// 

        /// The property returns the connection string of the database to be connected.
        /// 
        public static string TestConnectionString
        {
            get
            {
                return ConfigurationManager.ConnectionStrings["PersonalTaskTrackerConnectionString"].ConnectionString;
            }
        }
    }
}

TaskManager.cs

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

namespace DAL
{
    /// 

    /// TaskManager class.
    /// 
    public static class TaskManager
    {
        /// 

        /// Task structure
        /// 
        public struct Task
        {
            /// 

            /// Id of task.
            /// 
            public int TaskId;

            /// 

            /// Summary of task.
            /// 
            public string Summary;

            /// 

            /// Description of task.
            /// 
            public string Description;

            /// 

            /// Id of the status of task.
            /// 
            public int StatusId;

            /// 

            /// Name of the status of the task.
            /// 
            public string StatusName;

            /// 

            /// Date of creation of the task.
            /// 
            public DateTime CreatedOn;
        }

        /// 

        /// Method to insert a new task to the database.
        /// 
        ///  Wraps all the data related to the task.
        /// A Task object.
        public static Task InsertTask(Task taskData)
        {
            using (SqlConnection cn = new SqlConnection(Database.TestConnectionString))
            {
                cn.Open();
                using (SqlCommand cm = cn.CreateCommand())
                {
                    cm.CommandText = "AddTask";
                    cm.CommandType = CommandType.StoredProcedure;
                    cm.Parameters.AddWithValue("@taskId", taskData.TaskId);
                    cm.Parameters.AddWithValue("@summary", taskData.Summary);
                    cm.Parameters.AddWithValue("@description", taskData.Description);
                    cm.Parameters.AddWithValue("@status", taskData.StatusId);
                    cm.Parameters.AddWithValue("@createdOn", taskData.CreatedOn);

                    taskData.TaskId = Convert.ToInt32(cm.ExecuteScalar());

                }
            }

            return taskData;
        }

        /// 

        /// Method to get a task of given id.
        /// 
        /// Id of the task.
        /// A task object
        public static Task GetTask(int taskId)
        {
            Task task = new Task();
            using (SqlConnection cn = new SqlConnection(Database.TestConnectionString))
            {
                cn.Open();


                using (SqlCommand cm = cn.CreateCommand())
                {
                    cm.CommandText = "GetTask";
                    cm.CommandType = CommandType.StoredProcedure;
                    cm.Parameters.AddWithValue("@taskId", taskId);

                    using (SqlDataReader dr = cm.ExecuteReader())
                    {
                        if (dr.Read())
                        {
                            task.TaskId = (int)dr["TaskID"];
                            task.Summary = dr["Summary"] as string;
                            task.Description = dr["Description"] as string;
                            task.StatusName = dr["StatusName"] as string;
                            task.CreatedOn = (DateTime)dr["CreateOn"];
                            task.StatusId = (int)dr["StatusID"];
                        }
                        else
                        {
                            throw new ApplicationException(string.Format("Task{0} not found1", taskId));
                        }
                    }
                }
            }

            return task;
        }


        /// 

        /// Method to get all the tasks from database.
        /// 
        /// List of tasks.
        public static List<Task> GetAllTasks()
        {
            List<Task> taskList = new List<Task>();

            using (SqlConnection cn = new SqlConnection(Database.TestConnectionString))
            {
                cn.Open();

                using (SqlCommand cm = cn.CreateCommand())
                {
                    cm.CommandText = "GetAllTasks";
                    cm.CommandType = CommandType.StoredProcedure;

                    using (SqlDataReader dr = cm.ExecuteReader())
                    {
                        while (dr.Read())
                        {
                            Task task = ReadTask(dr);
                            taskList.Add(task);
                        }
                    }
                }
            }

            return taskList;
        }

        /// 

        /// Method to datareader and populate the data to a Task object.
        /// 
        /// Data reader of Sql.
        /// 
        private static Task ReadTask(SqlDataReader dr)
        {
            Task task = new Task();
            task.TaskId = (int)dr["TaskID"];
            task.Summary = dr["Summary"] as string;
            task.Description = dr["Description"] as string;
            task.StatusName = dr["StatusName"] as string;
            task.CreatedOn = (DateTime)dr["CreateOn"];

            return task;
        }


        /// 

        /// Method to update a task.
        /// 
        /// Contasin data to be updated
        public static void UpdateTask(Task taskData)
        {
            using (SqlConnection cn = new SqlConnection(Database.TestConnectionString))
            {
                cn.Open();
                using (SqlCommand cm = cn.CreateCommand())
                {
                    cm.CommandText = "UpdateTask";
                    cm.CommandType = CommandType.StoredProcedure;
                    cm.Parameters.AddWithValue("@taskId", taskData.TaskId);
                    cm.Parameters.AddWithValue("@summary", taskData.Summary);
                    cm.Parameters.AddWithValue("@description", taskData.Description);
                    cm.Parameters.AddWithValue("@status", taskData.StatusId);
                    cm.Parameters.AddWithValue("@createdOn", taskData.CreatedOn);

                    cm.ExecuteNonQuery();
                }
            }
        }

        /// 

        /// Method to delete a task of given id.
        /// 
        /// Id of the task to be deleted.
        public static void DeleteTask(int taskId)
        {
            using (SqlConnection cn = new SqlConnection(Database.TestConnectionString))
            {
                cn.Open();
                using (SqlCommand cm = cn.CreateCommand())
                {
                    cm.CommandText = "DeleteTask";
                    cm.CommandType = CommandType.StoredProcedure;
                    cm.Parameters.AddWithValue("@taskId", taskId);

                    cm.ExecuteNonQuery();
                }
            }
        }

    }
}

We will see about BLL in the next post.