1 using System;
2 using System.Data;
3 using System.Configuration;
4 using System.Linq;
5 using System.Web;
6 using System.Web.Security;
7 using System.Web.UI;
8 using System.Web.UI.HtmlControls;
9 using System.Web.UI.WebControls;
10 using System.Web.UI.WebControls.WebParts;
11 using System.Xml.Linq;
12 using System.Data.SqlClient;
13 using System.Web.Configuration;
14 using System.Collections.Generic;
15
16 /// <summary>
17 ///EmployeeDB 的摘要说明
18 /// </summary>
19 public class EmployeeDB
20 {
21 private string connectionString;
22
23 public EmployeeDB()
24 {
25 connectionString = WebConfigurationManager.ConnectionStrings["Northwind"].ConnectionString;
26
27 }
28 public EmployeeDB(string connectionString)
29 {
30 this.connectionString = connectionString;
31 }
32 public int InsertEmployee(EmployeeDetails emp)
33 {
34 SqlConnection con = new SqlConnection(connectionString);
35 SqlCommand cmd = new SqlCommand("InsertEmployee", con);
36 cmd.CommandType = CommandType.StoredProcedure;
37 cmd.Parameters.Add(new SqlParameter("@FirstName", SqlDbType.NVarChar, 10));
38 cmd.Parameters["@FirstName"].Value = emp.FirstName;
39 cmd.Parameters.Add(new SqlParameter("@LastName", SqlDbType.NVarChar, 20));
40 cmd.Parameters["@LastName"].Value = emp.LastName;
41 cmd.Parameters.Add(new SqlParameter("@TitleOfCourtesy", SqlDbType.NVarChar, 25));
42 cmd.Parameters["@TitleOfCourtesy"].Value = emp.TitleOfCourtesy;
43 cmd.Parameters.Add(new SqlParameter("@EmployeeID", SqlDbType.Int, 4));
44 cmd.Parameters["@EmployeeID"].Direction = ParameterDirection.Output;
45
46 try
47 {
48 con.Open();
49 cmd.ExecuteNonQuery();
50 return (int)cmd.Parameters["@EmployeeID"].Value;
51 }
52 catch (SqlException err)
53 {
54 throw new ApplicationException("Data err.");
55 }
56 finally
57 {
58 con.Close();
59 }
60 }
61
62 public void DeleteEmployee(int employeeID)
63 {
64 SqlConnection con = new SqlConnection(connectionString);
65 SqlCommand cmd = new SqlCommand("DeleteEmployee", con);
66 cmd.CommandType = CommandType.StoredProcedure;
67 cmd.Parameters.Add(new SqlParameter("@EmployeeID", SqlDbType.Int, 4));
68 cmd.Parameters["@EmployeeID"].Value = employeeID;
69
70 try
71 {
72 con.Open();
73 cmd.ExecuteNonQuery();
74 }
75 catch (SqlException err)
76 {
77 throw new ApplicationException("Data Error.");
78 }
79 finally
80 {
81 con.Close();
82 }
83 }
84
85 public void UpdateEmployee(int EmployeeID, string firstName, string lastName,string titleOfCourtesy)
86 {
87 SqlConnection con = new SqlConnection(connectionString);
88 SqlCommand cmd = new SqlCommand("UpdateEmployee", con);
89 cmd.CommandType = CommandType.StoredProcedure;
90 cmd.Parameters.Add(new SqlParameter("@Firstname", SqlDbType.NVarChar, 10));
91 cmd.Parameters["@FirstName"].Value = firstName;
92 cmd.Parameters.Add(new SqlParameter("@TitleOfCourtesy", SqlDbType.NVarChar, 25));
93 cmd.Parameters["@TitleOfCourtesy"].Value = titleOfCourtesy;
94 cmd.Parameters.Add(new SqlParameter("@EmployeeID", SqlDbType.Int, 4));
95 cmd.Parameters["@EmployeeID"].Value = EmployeeID;
96
97 try
98 {
99 con.Open();
100 cmd.ExecuteNonQuery();
101 }
102 catch (SqlException err)
103 {
104 throw new ApplicationException("Data error.");
105 }
106 finally
107 {
108 con.Close();
109 }
110 }
111 public EmployeeDetails GetEmployee(int employeeID)
112 {
113 SqlConnection con = new SqlConnection(connectionString);
114 SqlCommand cmd = new SqlCommand("GetEmployee", con);
115 cmd.CommandType = CommandType.StoredProcedure;
116 cmd.Parameters.Add(new SqlParameter("@EmployeeID", SqlDbType.Int, 4));
117 cmd.Parameters["@EmployeeID"].Value = employeeID;
118
119 try
120 {
121 con.Open();
122 SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleRow);
123 if (!reader.HasRows)
124 return null;
125 reader.Read();
126 EmployeeDetails emp = new EmployeeDetails((int)reader["EmployeeID"], (string)reader["FirstName"], (string)reader["LastName"], (string)reader["TitleOfCourtesy"]);
127 reader.Close();
128 return emp;
129 }
130 catch (SqlException err)
131 {
132 throw new ApplicationException("Data error.");
133 }
134 finally
135 {
136 con.Close();
137 }
138 }
139
140 public List<EmployeeDetails> GetEmployees()
141 {
142 SqlConnection con = new SqlConnection(connectionString);
143 SqlCommand cmd = new SqlCommand("GetEmployees", con);
144 cmd.CommandType = CommandType.StoredProcedure;
145
146 List<EmployeeDetails> employees = new List<EmployeeDetails>();
147
148 try
149 {
150 con.Open();
151 SqlDataReader reader = cmd.ExecuteReader();
152 while (reader.Read())
153 {
154 EmployeeDetails emp = new EmployeeDetails(
155 (int)reader["EmployeeID"], (string)reader["FirstName"], (string)reader["LastName"], (string)reader["TitleOfCourtesy"]);
156 employees.Add(emp);
157 }
158 reader.Close();
159 return employees;
160 }
161 catch (SqlException err)
162 {
163 throw new ApplicationException("Data error.");
164 }
165 finally
166 {
167 con.Close();
168 }
169 }
170
171 public int CountEmployees()
172 {
173 SqlConnection con = new SqlConnection(connectionString);
174 SqlCommand cmd = new SqlCommand("CoutEmployees", con);
175 cmd.CommandType = CommandType.StoredProcedure;
176
177 try
178 {
179 con.Open();
180 return (int)cmd.ExecuteScalar();
181 }
182 catch (SqlException err)
183 {
184 throw new ApplicationException("Data error.");
185 }
186 finally
187 {
188 con.Close();
189 }
190 }
191 }
