.net简单的三层实现功能

在网上看到别人的代码,写下来留着看,欢迎各位指教,有没有工厂模式的学习资料啊,留下给小弟学习学习.

首先写个Model类，实现业务实体，我的比较简单，只有三个字段

 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数据库访问
 

  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         /// 

  7         /// GetConnection 用于获取连接数据库的 connection 对象
  8         /// 
  9         /// 
 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         /// 

 19         /// GetCommand 获取命令参数 command 对象
 20         /// 
 21         ///
 22         ///
 23         ///
 24         /// 
 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         /// 

 34         /// GetCommand 方法重载
 35         /// 
 36         ///sql语句
 37         ///
 38         /// 
 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         /// 

 48         /// GetParameter 用于为命令设置参数
 49         /// 
 50         ///
 51         ///
 52         ///
 53         /// 
 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         /// 

 63         /// 执行无参的存储过程
 64         /// 
 65         ///存储过程名
 66         /// 
 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         /// 

 81         /// 执行无返回值有参数的存储过程
 82         /// 
 83         ///存储过程名
 84         ///参数
 85         /// 
 86         public static int ExecuteNonQueryProc(string sqlCommand, Dictionary parameters)
 87         {
 88             int result = 0;
 89             using (DbConnection connection = GetConnection())
 90             {
 91                 DbCommand command = GetCommand(sqlCommand, CommandType.StoredProcedure, connection);
 92                 foreach (KeyValuePair 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         /// 

105         /// 执行无返回值的sql语句
106         /// 
107         ///
108         ///
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         /// 

123         /// 执行有参数的sql语句
124         /// 
125         ///
126         ///
127         /// 
128         public static int ExecuteNonQuery(string sqlCommand, Dictionary para)
129         {
130             int result = 0;
131             using (DbConnection connection = GetConnection())
132             {
133                 DbCommand command = GetCommand(sqlCommand, CommandType.Text, connection);
134                 foreach (KeyValuePair 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         /// 

146         /// 执行有返回值无参数的存储过程
147         /// 
148         ///
149         /// 
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         /// 

163         /// 执行有返回值的有参数的存储过程
164         /// 
165         ///存储过程名
166         ///参数
167         /// 
168         public static object ExecuteScalarProc(string cmdText, Dictionary para)
169         {
170             using (DbConnection connection = GetConnection())
171             {
172                 DbCommand command = GetCommand(cmdText, CommandType.StoredProcedure, connection);
173                 foreach (KeyValuePair 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         /// 

185         /// 执行有返回值的sql语句
186         /// 
187         ///
188         /// 
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         /// 

202         /// 执行有返回值有参数的sql语句
203         /// 
204         ///
205         ///
206         /// 
207         public static object ExecuteScalar(string cmdText, Dictionary para)
208         {
209             using (DbConnection connection = GetConnection())
210             {
211                 DbCommand command = GetCommand(cmdText, CommandType.Text, connection);
212                 foreach (KeyValuePair 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         /// 

225         /// 执行无参数的存储过程,返回DbDataReader对象
226         /// 
227         ///存储过程名
228         /// 
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         /// 

259         /// 执行有参数的存储过程,返回DbDataReader对象
260         /// 
261         ///
262         ///
263         /// 
264         public static DbDataReader GetReaderProc(string sqlCommand, Dictionary parameters)
265         {
266 
267             try
268             {
269 
270                 DbConnection connection = GetConnection();
271 
272                 DbCommand command = GetCommand(sqlCommand, CommandType.StoredProcedure, connection);
273 
274                 foreach (KeyValuePair 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         /// 

302         /// 执行无参数的sql语句,返回DbDataReader对象
303         /// 
304         ///
305         /// 
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         /// 

339         /// 执行有参数的sql语句,返回DbDataReader对象
340         /// 
341         ///
342         ///
343         /// 
344         public static DbDataReader GetReader(string sqlCommand, Dictionary parameters)
345         {
346             try
347             {
348                 DbConnection connection = GetConnection();
349                 DbCommand command = GetCommand(sqlCommand, CommandType.Text, connection);
350                 foreach (KeyValuePair 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         /// 

367         /// 返回DataTable对象
368         /// 
369         ///
370         /// 
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

 1   //增
 2         public static bool Add(Model.LoginModel user)
 3         {
 4             string sql = "insert into admin(AdminUID,AdminPWD)values(@AdminUID,@AdminPWD)";
 5             Dictionary dic = new Dictionary();
 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 dic = new Dictionary();
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 dic = new Dictionary();
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 GetAllUser()
29         {
30             string sql="select AdminUID,AdminPWD from admin";
31             DataTable dt = DBHelp.GetDataSet(sql);
32             Listlist=new List();
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层，主要是针对具体的问题的操作，也可以理解成对数据层的操作,对数据业务逻辑处理

 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 GetAllUsers()
14         {
15             return DAL.LoginDAL.GetAllUser();
16         }

5.显示数据到页面，后台CS代码

 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.前台代码

 1 
 2         用户名
 3         密码
 4         "2">操作
 5     
 6     <%System.Data.SqlClient.SqlDataReader dr;
 7       dr = com.ExecuteReader();
 8       while (dr.Read())
 9       { %>
10     `
11         <%=dr.GetString(0) %>
12         <%=dr.GetString(1) %>
13         "Edit.aspx?id=<%=dr.GetInt32(2) %>">编辑
14         "Delete.aspx?id=<%=dr.GetInt32(2) %>">删除
15     
16     <%} %>

7.就实现一个删除吧，其他的实现方法是一样的，不过这个删除前台没有代码，点击显示页面的删除跳转到这个页面，删除以后再跳转会显示页面

 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", "");
 8             }
 9             else if (delete == false)
10             {
11                 Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "");
12             }
13             Response.Redirect("Show.aspx");
14         }