.net简单的三层实现功能
在网上看到别人的代码,写下来留着看,欢迎各位指教,有没有工厂模式的学习资料啊,留下给小弟学习学习.
首先写个Model类，实现业务实体，我的比较简单，只有三个字段
View Code
1 public class LoginModel
2 {
3 #region
4
5 private int _AdminId;
6 private string _AdminUID;
7 private string _AdminPWD;
8
9 public string AdminPWD
10 {
11 get { return _AdminPWD; }
12 set { _AdminPWD = value; }
13 }
14
15 public int AdminId
16 {
17 get { return _AdminId; }
18 set { _AdminId = value; }
19 }
20
21
22 public string AdminUID
23 {
24 get { return _AdminUID; }
25 set { _AdminUID = value; }
26 }
27
28 #endregion
29 }
2.创建一个DBHelp类，实现SQLServer数据库访问
View Code
1 public class DBHelp
2 {
3 private static readonly string connectionString = ConfigurationManager.ConnectionStrings["connstr"].ConnectionString.ToString();
4 private static readonly string providerName = "System.Data.SqlClient";
5
6 /// <summary>
7 /// GetConnection 用于获取连接数据库的 connection 对象
8 /// </summary>
9 /// <returns></returns>
10 private static DbConnection GetConnection()
11 {
12 DbProviderFactory _factory = DbProviderFactories.GetFactory(providerName);
13 DbConnection connection = _factory.CreateConnection();
14 connection.ConnectionString = connectionString;
15 return connection;
16 }
17
18 /// <summary>
19 /// GetCommand 获取命令参数 command 对象
20 /// </summary>
21 /// <param name="commandText"></param>
22 /// <param name="commandType"></param>
23 /// <param name="connection"></param>
24 /// <returns></returns>
25 private static DbCommand GetCommand(string commandText, CommandType commandType, DbConnection connection)
26 {
27 DbCommand command = connection.CreateCommand();
28 command.CommandText = commandText;
29 command.CommandType = commandType;
30 return command;
31 }
32
33 /// <summary>
34 /// GetCommand 方法重载
35 /// </summary>
36 /// <param name="commandText">sql语句</param>
37 /// <param name="connection"></param>
38 /// <returns></returns>
39 private static DbCommand GetCommand(string commandText, DbConnection connection)
40 {
41 DbCommand command = connection.CreateCommand();
42 command.CommandText = commandText;
43 command.CommandType = CommandType.Text;
44 return command;
45 }
46
47 /// <summary>
48 /// GetParameter 用于为命令设置参数
49 /// </summary>
50 /// <param name="paramName"></param>
51 /// <param name="paramValue"></param>
52 /// <param name="command"></param>
53 /// <returns></returns>
54 private static DbParameter GetParameter(string paramName, object paramValue, DbCommand command)
55 {
56 DbParameter parameter = command.CreateParameter();
57 parameter.ParameterName = paramName;
58 parameter.Value = paramValue;
59 return parameter;
60 }
61
62 /// <summary>
63 /// 执行无参的存储过程
64 /// </summary>
65 /// <param name="sqlCommand">存储过程名</param>
66 /// <returns></returns>
67 public static int ExecuteNonQueryProc(string sqlCommand)
68 {
69 int result = 0;
70 using (DbConnection connection = GetConnection())
71 {
72 DbCommand command = GetCommand(sqlCommand, CommandType.StoredProcedure, connection);
73 connection.Open();
74 result = command.ExecuteNonQuery();
75 command.Parameters.Clear();
76 }
77 return result;
78 }
79
80 /// <summary>
81 /// 执行无返回值有参数的存储过程
82 /// </summary>
83 /// <param name="sqlCommand">存储过程名</param>
84 /// <param name="parameters">参数</param>
85 /// <returns></returns>
86 public static int ExecuteNonQueryProc(string sqlCommand, Dictionary<string, object> parameters)
87 {
88 int result = 0;
89 using (DbConnection connection = GetConnection())
90 {
91 DbCommand command = GetCommand(sqlCommand, CommandType.StoredProcedure, connection);
92 foreach (KeyValuePair<string, object> p in parameters)
93 {
94 command.Parameters.Add(GetParameter(p.Key, p.Value, command));
95 }
96 connection.Open();
97 result = command.ExecuteNonQuery();
98 command.Parameters.Clear();
99 }
100 return result;
101 }
102
103
104 /// <summary>
105 /// 执行无返回值的sql语句 www.2cto.com
106 /// </summary>
107 /// <param name="sqlCommand"></param>
108 /// <param name="parameters"></param>
109 public static int ExecuteNonQuery(string sqlCommand)
110 {
111 int result = 0;
112 using (DbConnection connection = GetConnection())
113 {
114 DbCommand command = GetCommand(sqlCommand, CommandType.Text, connection);
115 connection.Open();
116 result = command.ExecuteNonQuery();
117 command.Parameters.Clear();
118 return result;
119 }
120 }
121
122 /// <summary>
123 /// 执行有参数的sql语句
124 /// </summary>
125 /// <param name="sqlCommand"></param>
126 /// <param name="para"></param>
127 /// <returns></returns>
128 public static int ExecuteNonQuery(string sqlCommand, Dictionary<string, object> para)
129 {
130 int result = 0;
131 using (DbConnection connection = GetConnection())
132 {
133 DbCommand command = GetCommand(sqlCommand, CommandType.Text, connection);
134 foreach (KeyValuePair<string, object> p in para)
135 {
136 command.Parameters.Add(GetParameter(p.Key, p.Value, command));
137 }
138 connection.Open();
139 result = command.ExecuteNonQuery();
140 command.Parameters.Clear();
141 return result;
142 }
143 }
144
145 /// <summary>
146 /// 执行有返回值无参数的存储过程
147 /// </summary>
148 /// <param name="cmdText"></param>
149 /// <returns></returns>
150 public static object ExecuteScalarProc(string cmdText)
151 {
152 using (DbConnection connection = GetConnection())
153 {
154 DbCommand command = GetCommand(cmdText, CommandType.StoredProcedure, connection);
155 connection.Open();
156 object val = command.ExecuteScalar();
157 command.Parameters.Clear();
158 return val;
159 }
160 }
161
162 /// <summary>
163 /// 执行有返回值的有参数的存储过程
164 /// </summary>
165 /// <param name="cmdText">存储过程名</param>
166 /// <param name="para">参数</param>
167 /// <returns></returns>
168 public static object ExecuteScalarProc(string cmdText, Dictionary<string, object> para)
169 {
170 using (DbConnection connection = GetConnection())
171 {
172 DbCommand command = GetCommand(cmdText, CommandType.StoredProcedure, connection);
173 foreach (KeyValuePair<string, object> p in para)
174 {
175 command.Parameters.Add(GetParameter(p.Key, p.Value, command));
176 }
177 connection.Open();
178 object val = command.ExecuteScalar();
179 command.Parameters.Clear();
180 return val;
181 }
182 }
183
184 /// <summary>
185 /// 执行有返回值的sql语句
186 /// </summary>
187 /// <param name="cmdText"></param>
188 /// <returns></returns>
189 public static object ExecuteScalar(string cmdText)
190 {
191 using (DbConnection connection = GetConnection())
192 {
193 DbCommand command = GetCommand(cmdText, CommandType.Text, connection);
194 connection.Open();
195 object val = command.ExecuteScalar();
196 command.Parameters.Clear();
197 return val;
198 }
199 }
200
201 /// <summary>
202 /// 执行有返回值有参数的sql语句
203 /// </summary>
204 /// <param name="cmdText"></param>
205 /// <param name="para"></param>
206 /// <returns></returns>
207 public static object ExecuteScalar(string cmdText, Dictionary<string, object> para)
208 {
209 using (DbConnection connection = GetConnection())
210 {
211 DbCommand command = GetCommand(cmdText, CommandType.Text, connection);
212 foreach (KeyValuePair<string, object> p in para)
213 {
214 command.Parameters.Add(GetParameter(p.Key, p.Value, command));
215 }
216 connection.Open();
217 object val = command.ExecuteScalar();
218 command.Parameters.Clear();
219 return val;
220 }
221
222 }
223
224 /// <summary>
225 /// 执行无参数的存储过程,返回DbDataReader对象
226 /// </summary>
227 /// <param name="sqlCommand">存储过程名</param>
228 /// <returns></returns>
229 public static DbDataReader GetReaderProc(string sqlCommand)
230 {
231
232 try
233 {
234
235 DbConnection connection = GetConnection();
236
237 DbCommand command = GetCommand(sqlCommand, CommandType.StoredProcedure, connection);
238
239 connection.Open();
240
241 DbDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
242
243 return reader;
244
245 }
246
247 catch (Exception ex)
248 {
249
250 Console.Write("" + ex.Message);
251
252 return null;
253
254 }
255
256 }
257
258 /// <summary>
259 /// 执行有参数的存储过程,返回DbDataReader对象
260 /// </summary>
261 /// <param name="sqlCommand"></param>
262 /// <param name="parameters"></param>
263 /// <returns></returns>
264 public static DbDataReader GetReaderProc(string sqlCommand, Dictionary<string, object> parameters)
265 {
266
267 try
268 {
269
270 DbConnection connection = GetConnection();
271
272 DbCommand command = GetCommand(sqlCommand, CommandType.StoredProcedure, connection);
273
274 foreach (KeyValuePair<string, object> p in parameters)
275 {
276
277 command.Parameters.Add(GetParameter(p.Key, p.Value, command));
278
279 }
280
281 connection.Open();
282
283 DbDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
284
285 command.Parameters.Clear();
286
287 return reader;
288
289 }
290
291 catch
292 {
293
294 return null;
295
296 }
297
298 }
299 #region
300
301 /// <summary>
302 /// 执行无参数的sql语句,返回DbDataReader对象
303 /// </summary>
304 /// <param name="sqlCommand"></param>
305 /// <returns></returns>
306 public static DbDataReader GetReader(string sqlCommand)
307 {
308
309 try
310 {
311
312 DbConnection connection = GetConnection();
313
314 DbCommand command = GetCommand(sqlCommand, CommandType.Text, connection);
315
316 connection.Open();
317
318 DbDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
319
320 command.Parameters.Clear();
321
322 return reader;
323
324 }
325
326 catch (Exception ex)
327 {
328
329 Console.Write("" + ex.Message);
330
331 return null;
332
333 }
334
335 }
336
337 #endregion
338 /// <summary>
339 /// 执行有参数的sql语句,返回DbDataReader对象
340 /// </summary>
341 /// <param name="sqlCommand"></param>
342 /// <param name="parameters"></param>
343 /// <returns></returns>
344 public static DbDataReader GetReader(string sqlCommand, Dictionary<string, object> parameters)
345 {
346 try
347 {
348 DbConnection connection = GetConnection();
349 DbCommand command = GetCommand(sqlCommand, CommandType.Text, connection);
350 foreach (KeyValuePair<string, object> p in parameters)
351 {
352 command.Parameters.Add(GetParameter(p.Key, p.Value, command));
353 }
354 connection.Open();
355 DbDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);
356 command.Parameters.Clear();
357 return reader;
358 }
359 catch (Exception ex)
360 {
361 Console.Write("" + ex.Message);
362 return null;
363 }
364 }
365
366 /// <summary>
367 /// 返回DataTable对象
368 /// </summary>
369 /// <param name="safeSql"></param>
370 /// <returns></returns>
371 public static DataTable GetDataSet(string safeSql)
372 {
373 /*DbProviderFactory _factory = DbProviderFactories.GetFactory(providerName);
374 DbConnection connection = GetConnection();
375 connection.Open();
376 DbDataAdapter da = _factory.CreateDataAdapter();
377 da.SelectCommand = connection.CreateCommand();
378 da.SelectCommand.CommandText = safeSql;
379 DataTable dt = new DataTable();
380 da.Fill(dt);
381 return dt;*/
382
383 using (DbConnection connection = GetConnection())
384 {
385 DbProviderFactory _factory = DbProviderFactories.GetFactory(providerName);
386 DbCommand command = GetCommand(safeSql, CommandType.Text, connection);
387 connection.Open();
388 DbDataAdapter da = _factory.CreateDataAdapter();
389 da.SelectCommand = command;
390 DataTable datatable = new DataTable();
391 da.Fill(datatable);
392 return datatable;
393 }
394 }
395 }
3.创建DAL实现要实现的 接口，主要是对原始数据（数据库或者文本文件等存放数据的形式）的操作层，而不是指原始数据，也就是说，是对数据的操作，而不是数据库,具体为业务逻辑层或表示层提供数据服务
引用Model
View Code
1 //增
2 public static bool Add(Model.LoginModel user)
3 {
4 string sql = "insert into admin(AdminUID,AdminPWD)values(@AdminUID,@AdminPWD)";
5 Dictionary<string, object> dic = new Dictionary<string, object>();
6 dic.Add("@AdminUID",user.AdminUID);
7 dic.Add("@AdminPWD", user.AdminPWD);
8 return DBHelp.ExecuteNonQuery(sql, dic) > 0 ? true : false;
9 }
10 //删
11 public static bool Delete(int UserID)
12 {
13 string sql = "delete from admin where AdminID=@AdminID";
14 Dictionary<string, object> dic = new Dictionary<string, object>();
15 dic.Add("AdminID",UserID);
16 return DBHelp.ExecuteNonQuery(sql, dic) > 0 ? true : false;
17 }
18 //改
19 public static bool Modelfy(Model.LoginModel user)
20 {
21 string sql = "update admin set AdminUID=@AdminUID and AdminPWD=@AdminPWD ";
22 Dictionary<string, object> dic = new Dictionary<string, object>();
23 dic.Add("AdminUID", user.AdminUID);
24 dic.Add("AdminPWD", user.AdminPWD);
25 return DBHelp.ExecuteNonQuery(sql, dic) > 0 ? true : false;
26 }
27 //查
28 public static List<LoginModel> GetAllUser()
29 {
30 string sql="select AdminUID,AdminPWD from admin";
31 DataTable dt = DBHelp.GetDataSet(sql);
32 List<LoginModel>list=new List<LoginModel>();
33 foreach (DataRow dr in dt.Rows)
34 {
35 LoginModel login = new LoginModel();
36 login.AdminUID = dr["AdminUID"].ToString();
37 login.AdminPWD = dr["AdminPWD"].ToString();
38 list.Add(login);
39 }
40 return list;
41 }
4.创建BLL层，主要是针对具体的问题的操作，也可以理解成对数据层的操作,对数据业务逻辑处理
View Code
1 public static bool Add(LoginModel user)
2 {
3 return DAL.LoginDAL.Add(user);
4 }
5 public static bool Delete(int UserID)
6 {
7 return DAL.LoginDAL.Delete(UserID);
8 }
9 public static bool Modify(LoginModel user)
10 {
11 return DAL.LoginDAL.Modelfy(user);
12 }
13 public static List<LoginModel> GetAllUsers()
14 {
15 return DAL.LoginDAL.GetAllUser();
16 }
5.显示数据到页面，后台CS代码
View Code
1 public SqlCommand com;
2
3 protected void Page_Load(object sender, EventArgs e)
4 {
5 SqlConnection sql = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=ThreeLayer");
6 string selSQL = "select AdminUID,AdminPWD,AdminId ,isExits from admin";
7 sql.Open();
8 com = new SqlCommand(selSQL, sql);
9
10 }
6.前台代码
View Code
1 <tr>
2 <td>用户名</td>
3 <td>密码</td>
4 <td colspan="2">操作</td>
5 </tr>
6 <%System.Data.SqlClient.SqlDataReader dr;
7 dr = com.ExecuteReader();
8 while (dr.Read())
9 { %>
10 `<tr>
11 <td><%=dr.GetString(0) %></td>
12 <td><%=dr.GetString(1) %></td>
13 <td><a href="Edit.aspx?id=<%=dr.GetInt32(2) %>">编辑</a></td>
14 <td><a href="Delete.aspx?id=<%=dr.GetInt32(2) %>">删除</a></td>
15 </tr>
16 <%} %>
7.就实现一个删除吧，其他的实现方法是一样的，不过这个删除前台没有代码，点击显示页面的删除跳转到这个页面，删除以后再跳转会显示页面
View Code
1 protected void Page_Load(object sender, EventArgs e)
2 {
3 int UserID = Convert.ToInt32(Request.QueryString["id"].ToString());
4 bool delete = BLL.LoginBLL.Delete(UserID);
5 if (delete == true)
6 {
7 Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript' defer>alert('删除成功')</script>");
8 }
9 else if (delete == false)
10 {
11 Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript' defer>alert('删除失败')</script>");
12 }
13 Response.Redirect("Show.aspx");
14 }
摘自 Myhfm
