9KpFZb7L

1using System;
2using System.Collections.Generic;
3using System.Linq;
4using System.Web;
5using System.Web.Services;
6using System.ComponentModel;
7using System.Configuration;
8using System.Data;
9using System.Data.SqlClient;
10
11namespace web_S10179806_P05
12{
13    public class Staff
14    {
15
16        //Properties 
17        public int staffId { get; set; }
18        public string name { get; set; }
19        public string gender { get; set; }
20        public DateTime dob { get; set; }
21        public string nationality { get; set; }
22        public string eMail { get; set; }
23        public double salary { get; set; }
24        public bool isFullTime { get; set; }
25        public int branchNo { get; set; }
26        public string joinTime { get; set; }
27
28
29
30        public int add()
31        {
32            //Read connection string "NPBookConnectionString" from web.config file.
33            string strConn = ConfigurationManager.ConnectionStrings
34                             ["NPBookConnectionString"].ToString();
35
36            //Instantiate a SqlConnection object with the Connection String read.
37            SqlConnection conn = new SqlConnection(strConn);
38
39            //Instantiat a SqlCommand object, supply it with an INSERT SQL statement
40            //which will return the auto-generated StaffID after insertion,
41            //and the connection object for connecting to the database.
42            SqlCommand cmd = new SqlCommand
43                             ("INSERT INTO Staff (Name, Gender, DOB, DateJoin, Salary, " +
44                              "EmailAddr, Nationality, Status)" +
45                              "OUTPUT INSERTED.StaffID " +
46                              "VALUES(@name, @gender, @dob, @joinTime, @salary, " +
47                              "@email, @country, @status)", conn);
48
49            //Define parameters used in SQL statement, value for each parameter
50            //is retrieved from respectiv class's property.
51            cmd.Parameters.AddWithValue("@name", name);
52            cmd.Parameters.AddWithValue("@gender", gender);
53            cmd.Parameters.AddWithValue("@dob", dob);
54            cmd.Parameters.AddWithValue("@joinTime", joinTime);
55            cmd.Parameters.AddWithValue("@salary", salary);
56            cmd.Parameters.AddWithValue("@email", eMail);
57            cmd.Parameters.AddWithValue("@country", nationality);
58            cmd.Parameters.AddWithValue("@status", isFullTime);
59
60            //A connection to database must be opened before any operations made.
61            conn.Open();
62
63            //ExecuteScalar is used to retrieve the auto-generated 
64            //StaffID after executing the INSERT SQL statement
65            int id = (int) cmd.ExecuteScalar();
66
67            //A connection should be closed after operations.
68            conn.Close();
69
70            //Return id when no error occurs.
71            return id;
72
73        }
74
75        public bool isEmailExist(string email)
76        {
77            string strConn = ConfigurationManager.ConnectionStrings
78                             ["NPBookConnectionString"].ToString();
79
80            SqlConnection conn = new SqlConnection(strConn);
81            SqlCommand cmd = new SqlCommand
82                             ("SELECT * FROM Staff WHERE EmailAddr = @selectedEmail", conn);
83
84            cmd.Parameters.AddWithValue("@selectedEmail", email);
85
86            SqlDataAdapter daEmail = new SqlDataAdapter(cmd);
87            DataSet result = new DataSet();
88
89            conn.Open();
90            //Use DataAdapter to fetch data to a table to a table "EmailDetails" in DataSet.
91            daEmail.Fill(result, "EmailDetails");
92            conn.Close();
93
94            if (result.Tables["EmailDetails"].Rows.Count > 0)
95                return true; //The email given exists
96            else
97                return false; //The email given does not exist
98
99        }
100
101        public int getDetails()
102        {
103            //Read Conncetion string "NPBookConnectionString" from web.config file.
104            string strConn = ConfigurationManager.ConnectionStrings
105                            ["NPBookConnectionString"].ToString();
106
107            SqlConnection conn = new SqlConnection(strConn);
108            SqlCommand cmd = new SqlCommand
109                ("SELECT * FROM Staff WHERE StaffID = @selectedStaffID", conn);
110
111            //Define the parameter used in SQL statement,value for the
112            //parameter is retrieved from the branchNo property of the Branch class.
113            cmd.Parameters.AddWithValue("@selectedStaffID", staffId);
114
115            //Instantiate a DataAdapter object, pass the SQLCommand
116            //object created as a parameter
117            SqlDataAdapter daStaff = new SqlDataAdapter(cmd);
118
119            //Create a DataSet object result
120            DataSet result = new DataSet();
121
122            conn.Open();
123            daStaff.Fill(result, "StaffDetails");
124            conn.Close();
125
126            if (result.Tables["StaffDetails"].Rows.Count > 0)
127            {
128                //Fill Staff oject with values from the DataSet
129                DataTable table = result.Tables["StaffDetails"];
130                if (!DBNull.Value.Equals(table.Rows[0]["Name"]))
131                    name = table.Rows[0]["Name"].ToString();
132                if (!DBNull.Value.Equals(table.Rows[0]["Salary"]))
133                    salary = Convert.ToDouble(table.Rows[0]["Salary"].ToString());
134                if (!DBNull.Value.Equals(table.Rows[0]["BranchNo"]))
135                    branchNo = Convert.ToInt32(table.Rows[0]["BranchNo"].ToString());
136
137                return 0; //No error occurs
138            }
139            else
140            {
141                return -2;//Record not found
142            }
143        }
144        public int update()
145        {
146            //Read Conncetion string "NPBookConnectionString" from web.config file.
147            string strConn = ConfigurationManager.ConnectionStrings
148                            ["NPBookConnectionString"].ToString();
149
150            SqlConnection conn = new SqlConnection(strConn);
151            SqlCommand cmd = new SqlCommand("UPDATE Staff SET Salary=@salary, BranchNo=@branchNo " +
152                "WHERE StaffID = @selectedStaffID", conn);
153
154            //Defind the parameters used in SQL statement, value for each parameter 
155            //is retrieved from respective class's property.
156            cmd.Parameters.AddWithValue("@salary", salary);
157
158            if (branchNo != 0)
159                cmd.Parameters.AddWithValue("@branchNo", branchNo);
160            else
161                cmd.Parameters.AddWithValue("@branchNo", DBNull.Value);
162
163            cmd.Parameters.AddWithValue("@selectedStaffID", staffId);
164
165            conn.Open();
166            int count = cmd.ExecuteNonQuery();
167            conn.Close();
168
169            if (count > 0)
170                return 0;
171            else
172                return -2;
173        }
174
175        public int delete()
176        {
177            //Read Conncetion string "NPBookConnectionString" from web.config file.
178            string strConn = ConfigurationManager.ConnectionStrings
179                            ["NPBookConnectionString"].ToString();
180
181            SqlConnection conn = new SqlConnection(strConn.ToString());
182            SqlCommand cmd = new SqlCommand(
183                "DELETE FROM Staff WHERE StaffID = @selectedStaffID", conn);
184
185            //Define the parameter used in SQL statement, value for the parameter
186            //is retrieved from class's property.
187            cmd.Parameters.AddWithValue("@selectedStaffID", staffId);
188
189            conn.Open();
190            cmd.ExecuteNonQuery();
191            conn.Close();
192
193            return 0;
194        }
195
196    }
197}