Staff.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Services;
using System.ComponentModel;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

namespace web_S10179806_P05
{
    public class Staff
    {

        //Properties
        public int staffId { get; set; }
        public string name { get; set; }
        public string gender { get; set; }
        public DateTime dob { get; set; }
        public string nationality { get; set; }
        public string eMail { get; set; }
        public double salary { get; set; }
        public bool isFullTime { get; set; }
        public int branchNo { get; set; }
        public string joinTime { get; set; }



        public int add()
        {
            //Read connection string "NPBookConnectionString" from web.config file.
            string strConn = ConfigurationManager.ConnectionStrings
                             ["NPBookConnectionString"].ToString();

            //Instantiate a SqlConnection object with the Connection String read.
            SqlConnection conn = new SqlConnection(strConn);

            //Instantiat a SqlCommand object, supply it with an INSERT SQL statement
            //which will return the auto-generated StaffID after insertion,
            //and the connection object for connecting to the database.
            SqlCommand cmd = new SqlCommand
                             ("INSERT INTO Staff (Name, Gender, DOB, DateJoin, Salary, " +
                              "EmailAddr, Nationality, Status)" +
                              "OUTPUT INSERTED.StaffID " +
                              "VALUES(@name, @gender, @dob, @joinTime, @salary, " +
                              "@email, @country, @status)", conn);

            //Define parameters used in SQL statement, value for each parameter
            //is retrieved from respectiv class's property.
            cmd.Parameters.AddWithValue("@name", name);
            cmd.Parameters.AddWithValue("@gender", gender);
            cmd.Parameters.AddWithValue("@dob", dob);
            cmd.Parameters.AddWithValue("@joinTime", joinTime);
            cmd.Parameters.AddWithValue("@salary", salary);
            cmd.Parameters.AddWithValue("@email", eMail);
            cmd.Parameters.AddWithValue("@country", nationality);
            cmd.Parameters.AddWithValue("@status", isFullTime);

            //A connection to database must be opened before any operations made.
            conn.Open();

            //ExecuteScalar is used to retrieve the auto-generated
            //StaffID after executing the INSERT SQL statement
            int id = (int) cmd.ExecuteScalar();

            //A connection should be closed after operations.
            conn.Close();

            //Return id when no error occurs.
            return id;

        }

        public bool isEmailExist(string email)
        {
            string strConn = ConfigurationManager.ConnectionStrings
                             ["NPBookConnectionString"].ToString();

            SqlConnection conn = new SqlConnection(strConn);
            SqlCommand cmd = new SqlCommand
                             ("SELECT * FROM Staff WHERE EmailAddr = @selectedEmail", conn);

            cmd.Parameters.AddWithValue("@selectedEmail", email);

            SqlDataAdapter daEmail = new SqlDataAdapter(cmd);
            DataSet result = new DataSet();

            conn.Open();
            //Use DataAdapter to fetch data to a table to a table "EmailDetails" in DataSet.
            daEmail.Fill(result, "EmailDetails");
            conn.Close();

            if (result.Tables["EmailDetails"].Rows.Count > 0)
                return true; //The email given exists
            else
                return false; //The email given does not exist

        }

        public int getDetails()
        {
            //Read Conncetion string "NPBookConnectionString" from web.config file.
            string strConn = ConfigurationManager.ConnectionStrings
                            ["NPBookConnectionString"].ToString();

            SqlConnection conn = new SqlConnection(strConn);
            SqlCommand cmd = new SqlCommand
                ("SELECT * FROM Staff WHERE StaffID = @selectedStaffID", conn);

            //Define the parameter used in SQL statement,value for the
            //parameter is retrieved from the branchNo property of the Branch class.
            cmd.Parameters.AddWithValue("@selectedStaffID", staffId);

            //Instantiate a DataAdapter object, pass the SQLCommand
            //object created as a parameter
            SqlDataAdapter daStaff = new SqlDataAdapter(cmd);

            //Create a DataSet object result
            DataSet result = new DataSet();

            conn.Open();
            daStaff.Fill(result, "StaffDetails");
            conn.Close();

            if (result.Tables["StaffDetails"].Rows.Count > 0)
            {
                //Fill Staff oject with values from the DataSet
                DataTable table = result.Tables["StaffDetails"];
                if (!DBNull.Value.Equals(table.Rows[0]["Name"]))
                    name = table.Rows[0]["Name"].ToString();
                if (!DBNull.Value.Equals(table.Rows[0]["Salary"]))
                    salary = Convert.ToDouble(table.Rows[0]["Salary"].ToString());
                if (!DBNull.Value.Equals(table.Rows[0]["BranchNo"]))
                    branchNo = Convert.ToInt32(table.Rows[0]["BranchNo"].ToString());

                return 0; //No error occurs
            }
            else
            {
                return -2;//Record not found
            }
        }
        public int update()
        {
            //Read Conncetion string "NPBookConnectionString" from web.config file.
            string strConn = ConfigurationManager.ConnectionStrings
                            ["NPBookConnectionString"].ToString();

            SqlConnection conn = new SqlConnection(strConn);
            SqlCommand cmd = new SqlCommand("UPDATE Staff SET Salary=@salary, BranchNo=@branchNo " +
                "WHERE StaffID = @selectedStaffID", conn);

            //Defind the parameters used in SQL statement, value for each parameter
            //is retrieved from respective class's property.
            cmd.Parameters.AddWithValue("@salary", salary);

            if (branchNo != 0)
                cmd.Parameters.AddWithValue("@branchNo", branchNo);
            else
                cmd.Parameters.AddWithValue("@branchNo", DBNull.Value);

            cmd.Parameters.AddWithValue("@selectedStaffID", staffId);

            conn.Open();
            int count = cmd.ExecuteNonQuery();
            conn.Close();

            if (count > 0)
                return 0;
            else
                return -2;
        }

        public int delete()
        {
            //Read Conncetion string "NPBookConnectionString" from web.config file.
            string strConn = ConfigurationManager.ConnectionStrings
                            ["NPBookConnectionString"].ToString();

            SqlConnection conn = new SqlConnection(strConn.ToString());
            SqlCommand cmd = new SqlCommand(
                "DELETE FROM Staff WHERE StaffID = @selectedStaffID", conn);

            //Define the parameter used in SQL statement, value for the parameter
            //is retrieved from class's property.
            cmd.Parameters.AddWithValue("@selectedStaffID", staffId);

            conn.Open();
            cmd.ExecuteNonQuery();
            conn.Close();

            return 0;
        }

    }
}