using System;
using System.Data;
using System.Configuration;
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.Collections.Generic;
using System.Data.SqlClient;
using System.Text;

/// <summary>
/// Summary description for Employees
/// </summary>
public class Employees
{
	public Employees()
	{
		//
		// TODO: Add constructor logic here
		//
	}

    public static int GetCount(string filterExpressions)
    {
        int count = 0;

        using (SqlConnection connection =
            new SqlConnection(
                ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString))
        {
            SqlCommand command = new SqlCommand(String.Format("SELECT COUNT(*) FROM Employees {0}",
                (filterExpressions != String.Empty) ? String.Format("WHERE {0}", filterExpressions) : ""),
                    connection);

            connection.Open();

            SqlDataReader reader = command.ExecuteReader();

            while (reader.Read())
            {
                count = reader.GetInt32(0);
            }

            reader.Close();

        }
        return count;
    }

    public static List<Employee> GetData(int startIndex, int maximumRows,
        string sortExpressions, string filterExpressions)
    {
        startIndex = startIndex + 1;

        List<Employee> list = new List<Employee>();

        StringBuilder sqlBuilder = new StringBuilder();
        sqlBuilder.AppendLine("DECLARE @startRowIndex int");
        sqlBuilder.AppendLine("DECLARE @maximumRows int");
        sqlBuilder.AppendLine(String.Format("SET @startRowIndex = {0}", startIndex));
        sqlBuilder.AppendLine(String.Format("SET @maximumRows = {0}", maximumRows));
        sqlBuilder.AppendLine("DECLARE @first_id int, @startRow int	");
        sqlBuilder.AppendLine("SET ROWCOUNT @startRowIndex");
        sqlBuilder.AppendLine("SELECT ");
        sqlBuilder.AppendLine("	@first_id = EmployeeID ");
        sqlBuilder.AppendLine("FROM Employees ");
        sqlBuilder.AppendLine("ORDER BY EmployeeID");

        sqlBuilder.AppendLine("SET ROWCOUNT @maximumRows");
        sqlBuilder.AppendLine("SELECT e.*");
        sqlBuilder.AppendLine("FROM Employees e");
        sqlBuilder.AppendLine("WHERE EmployeeID >= @first_id");

        string where = string.Empty;

        if (filterExpressions != String.Empty)
        {
            sqlBuilder.AppendLine(String.Format(" And {0}", filterExpressions));
        }

        if (sortExpressions != String.Empty)
        {
            sqlBuilder.AppendLine(String.Format(" Order By {0}", sortExpressions));
        }

        using (SqlConnection connection =
            new SqlConnection(
                ConfigurationManager.ConnectionStrings["NorthwindConnectionString"].ConnectionString))
        {
            SqlCommand command = new SqlCommand(sqlBuilder.ToString(), connection);
            connection.Open();

            SqlDataReader reader = command.ExecuteReader();

            while (reader.Read())
            {
                Employee newEmployee = new Employee();

                for (int i = 0; i < reader.FieldCount; i++)
                {
                    switch (reader.GetName(i))
                    {
                        case "EmployeeID":
                            newEmployee.EmployeeID = reader.GetInt32(i);
                            break;
                        case "LastName":
                            newEmployee.LastName = reader.GetString(i);
                            break;
                        case "FirstName":
                            newEmployee.FirstName = reader.GetString(i);
                            break;
                        case "Title":
                            newEmployee.Title = reader.GetString(i);
                            break;
                        case "TitleOfCourtesy":
                            newEmployee.TitleOfCourtesy = reader.GetString(i);
                            break;
                        case "BirthDate":
                            newEmployee.BirthDate = reader.GetDateTime(i);
                            break;
                        default:
                            break;
                    }
                }

                list.Add(newEmployee);
            }

            reader.Close();
        }

        return list;
    }

    public class Employee
    {
        private int _EmployeeID;
        private string _LastName;
        private string _FirstName;
        private string _Title;
        private string _TitleOfCourtesy;
        private System.Nullable<System.DateTime> _BirthDate;

        public Employee()
        {

        }

        public int EmployeeID
        {
            get
            {
                return this._EmployeeID;
            }
            set
            {
                if ((this._EmployeeID != value))
                {
                    this._EmployeeID = value;
                }
            }
        }

        public string LastName
        {
            get
            {
                return this._LastName;
            }
            set
            {
                if ((this._LastName != value))
                {
                    this._LastName = value;
                }
            }
        }

        public string FirstName
        {
            get
            {
                return this._FirstName;
            }
            set
            {
                if ((this._FirstName != value))
                {
                    this._FirstName = value;
                }
            }
        }

        public string Title
        {
            get
            {
                return this._Title;
            }
            set
            {
                if ((this._Title != value))
                {
                    this._Title = value;
                }
            }
        }

        public string TitleOfCourtesy
        {
            get
            {
                return this._TitleOfCourtesy;
            }
            set
            {
                if ((this._TitleOfCourtesy != value))
                {
                    this._TitleOfCourtesy = value;
                }
            }
        }

        public System.Nullable<System.DateTime> BirthDate
        {
            get
            {
                return this._BirthDate;
            }
            set
            {
                if ((this._BirthDate != value))
                {
                    this._BirthDate = value;
                }
            }
        }
    }
}
