ASP.NET学习笔记（3）——用户增删改查（三层）

说明（2017-10-6 11:21:58）：

1. 十一放假在家也没写几行代码，本来还想着利用假期把asp.net看完，结果天天喝酒睡觉，回去的票也没买到，惨。。

2. 断断续续的把用户信息的页面写完了，用了三层的方法，之前一直也没记下来，忘了的时候，每次都是从视频里找，这次好歹也要写下来，方便以后抄。

3. 希望十月份能把asp.net学完，然后看传说中的MVC。

代码：

1. 结构图

2. 建立三个类库，一个空web应用程序。DAL引Model，BLL引DAL和Model，WebApp引Model和BLL，反正各种引用。

3. Model里建一个UserInfo类，里面是用户字段和属性。

UserInfo.cs

 1 using System;
 2 using System.Collections.Generic;
 3 using System.Linq;
 4 using System.Text;
 5 
 6 namespace JJW.Model
 7 {
 8     public class UserInfo
 9     {
10         public int ID { get; set; }
11         public string UserName { get; set; }
12         public string PassWord { get; set; }
13     }
14 }

4. DAL里有两个类，一个SqlHelper类，里面有两个方法GetTable和ExecuteNonQuery，GetTable负责查询，ExecuteNonQuery负责增删改。另一个UserInfoDal类，里面调用SqlHelper类，细化了增删改查的方法。三层里最重要的就是这个DAL层了。

SqlHelper.cs

 1 using System;
 2 using System.Collections.Generic;
 3 using System.Linq;
 4 using System.Text;
 5 using System.Configuration;
 6 using System.Data;
 7 using System.Data.SqlClient;
 8 
 9 namespace JJW.DAL
10 {
11     public static class SqlHelper
12     {
13         private static readonly string conStr = ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
14         public static DataTable GetTable(string sql, CommandType type, params SqlParameter[] ps)
15         {
16             using (SqlConnection con = new SqlConnection(conStr))
17             {
18                 using (SqlDataAdapter sda = new SqlDataAdapter(sql, con))
19                 {
20                     DataTable dt = new DataTable();
21                     sda.SelectCommand.CommandType = type;
22                     if (ps != null)
23                     {
24                         sda.SelectCommand.Parameters.AddRange(ps);
25                     }
26                     sda.Fill(dt);
27                     return dt;
28                 }
29             }
30         }
31 
32         public static int ExecuteNonQuery(string sql, CommandType type, params SqlParameter[] ps)
33         {
34             using (SqlConnection con = new SqlConnection(conStr))
35             {
36                 using (SqlCommand cmd = new SqlCommand(sql, con))
37                 {
38                     cmd.CommandType = type;
39                     if (ps != null)
40                     {
41                         cmd.Parameters.AddRange(ps);
42                     }
43                     con.Open();
44                     return cmd.ExecuteNonQuery();
45                 }
46             }
47         }
48     }
49 }

UserInfoDal.cs

 1 using System;
 2 using System.Collections.Generic;
 3 using System.Linq;
 4 using System.Text;
 5 using JJW.Model;
 6 using System.Data;
 7 using System.Data.SqlClient;
 8 
 9 namespace JJW.DAL
10 {
11     public class UserInfoDal
12     {
13         /// <summary>
14         /// 返回列表
15         /// </summary>
16         /// <returns></returns>
17         public List<UserInfo> GetEntity()
18         {
19             string sql = "SELECT * FROM userInfo";
20             DataTable dt = SqlHelper.GetTable(sql, CommandType.Text);
21             List<UserInfo> list = new List<UserInfo>();
22             if (dt.Rows.Count > 0)
23             {
24                 foreach (DataRow dr in dt.Rows)
25                 {
26                     UserInfo userInfo = new UserInfo();
27                     LoadEntity(userInfo, dr);
28                     list.Add(userInfo);
29                 }
30             }
31             return list;
32         }
33         /// <summary>
34         /// 将表转为属性
35         /// </summary>
36         /// <param name="userInfo"></param>
37         /// <param name="dr"></param>
38         private void LoadEntity(UserInfo userInfo, DataRow dr)
39         {
40             userInfo.ID = Convert.ToInt32(dr["id"]);
41             userInfo.UserName = dr["userName"] != DBNull.Value ? dr["userName"].ToString() : string.Empty;
42             userInfo.PassWord = dr["passWord"] != DBNull.Value ? dr["passWord"].ToString() : string.Empty;
43         }
44         /// <summary>
45         /// 删除
46         /// </summary>
47         /// <param name="id"></param>
48         /// <returns></returns>
49         public int DeleteEntity(int id)
50         {
51             string sql = "DELETE FROM userInfo WHERE id = @id";
52             return SqlHelper.ExecuteNonQuery(sql, CommandType.Text, new SqlParameter("@id", id));
53         }
54         /// <summary>
55         /// 插入
56         /// </summary>
57         /// <param name="userName"></param>
58         /// <param name="passWord"></param>
59         /// <returns></returns>
60         public int InsertEntity(UserInfo userInfo)
61         {
62             string sql = "INSERT INTO userInfo(userName,passWord) VALUES(@userName,@passWord)";
63             return SqlHelper.ExecuteNonQuery(sql, CommandType.Text, new SqlParameter[]{
64                 new SqlParameter("@userName",userInfo.UserName),new SqlParameter("@passWord",userInfo.PassWord)
65             });
66         }
67         /// <summary>
68         /// 修改
69         /// </summary>
70         /// <param name="userName"></param>
71         /// <param name="passWord"></param>
72         /// <param name="id"></param>
73         /// <returns></returns>
74         public int UpdateEntity(UserInfo userInfo)
75         {
76             string sql = "UPDATE userInfo SET userName=@userName, passWord=@passWord WHERE id=@id";
77             SqlParameter[] ps = {new SqlParameter("@userName",SqlDbType.NVarChar,32),new SqlParameter("@passWord",SqlDbType.NVarChar,32),new SqlParameter("@id",SqlDbType.Int,4) };
78             return SqlHelper.ExecuteNonQuery(sql, CommandType.Text, new SqlParameter[]{
79                 new SqlParameter("@userName",userInfo.UserName),new SqlParameter("@passWord",userInfo.PassWord),new SqlParameter("@id",userInfo.ID)
80             });
81         }
82         /// <summary>
83         /// 详细
84         /// </summary>
85         /// <param name="id"></param>
86         /// <returns></returns>
87         public UserInfo ShowDetail(int id)
88         {
89             string sql = "SELECT * FROM userInfo WHERE id = @id";
90             DataTable dt = SqlHelper.GetTable(sql, CommandType.Text, new SqlParameter("@id", id));
91             UserInfo userInfo = new UserInfo();
92             if (dt.Rows.Count > 0)
93             {
94                 LoadEntity(userInfo, dt.Rows[0]);
95             }
96             return userInfo;
97         }
98     }
99 }

5. BLL层，里面就是把DAL层里的每个方法返回一个值，感觉BLL层没什么用，可能就是DAL层和UI层之间的一个桥梁吧，避免DAL层直接暴露在UI层里。BLL层里只有一个方法UserInfoBll。

UserInfoBll.cs

 1 using System;
 2 using System.Collections.Generic;
 3 using System.Linq;
 4 using System.Text;
 5 using JJW.Model;
 6 
 7 namespace JJW.BLL
 8 {
 9     public class UserInfoBll
10     {
11         DAL.UserInfoDal UserInfoDal = new DAL.UserInfoDal();
12         public List<UserInfo> GetEntity()
13         {
14             return UserInfoDal.GetEntity();
15         }
16         public int DeleteEntity(int id)
17         {
18             return UserInfoDal.DeleteEntity(id);
19         }
20         public int InsertEntity(UserInfo userInfo)
21         {
22             return UserInfoDal.InsertEntity(userInfo);
23         }
24         public int UpdateEntity(UserInfo userInfo)
25         {
26             return UserInfoDal.UpdateEntity(userInfo);
27         }
28         public UserInfo ShowDetail(int id)
29         {
30             return UserInfoDal.ShowDetail(id);
31         }
32     }
33 }

6. 最后的UI层，也就是WebApp。里面就是增删改查的页面，目前用的都是ashx一般处理程序，后面可能会改成aspx。

首先是web.config设置，这里面有两套登录设置，一个是本地，一个是用户名。

 1 <?xml version="1.0" encoding="utf-8"?>
 2 
 3 <!--
 4   有关如何配置 ASP.NET 应用程序的详细消息，请访问
 5   http://go.microsoft.com/fwlink/?LinkId=169433
 6   -->
 7 
 8 <configuration>
 9   <system.web>
10     <compilation debug="true" targetFramework="4.0" />
11   </system.web>
12   <connectionStrings>
13     <!--<add connectionString="data source=.;initial catalog=jjwdb;integrated security=true;" name="conStr"/>-->
14     <add connectionString="server=.;uid=sa;pwd=123;database=jjwdb;" name="conStr"/>
15   </connectionStrings>
16 </configuration>

后面是一堆页面，虽然网上粘很繁琐，但为了以后抄的方便，没办法啊。。而且为了不漏下，按字母顺序粘了。

Add.ashx

 1 using System;
 2 using System.Collections.Generic;
 3 using System.Linq;
 4 using System.Web;
 5 using JJW.Model;
 6 
 7 namespace JJW.WebApp
 8 {
 9     /// <summary>
10     /// Add 的摘要说明
11     /// </summary>
12     public class Add : IHttpHandler
13     {
14 
15         public void ProcessRequest(HttpContext context)
16         {
17             context.Response.ContentType = "text/html";
18             UserInfo userInfo = new UserInfo();
19             userInfo.UserName = context.Request["userName"];
20             userInfo.PassWord = context.Request["passWord"];
21             BLL.UserInfoBll UserInfoBll = new BLL.UserInfoBll();
22             UserInfoBll.InsertEntity(userInfo);
23             context.Response.Redirect("UserInfoList.ashx");
24         }
25 
26         public bool IsReusable
27         {
28             get
29             {
30                 return false;
31             }
32         }
33     }
34 }

Add.html

 1 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
 2 <html xmlns="http://www.w3.org/1999/xhtml">
 3 <head>
 4     <title></title>
 5 </head>
 6 <body>
 7     <form action="Add.ashx" method="post">
 8     <table border="1">
 9         <tr>
10             <td>用户名</td>
11             <td><input type="text" name="userName"/></td>
12         </tr>
13         <tr>
14             <td>密码</td>
15             <td><input type="text" name="passWord"/></td>
16         </tr>
17         <tr>
18             <td colspan="2"><input type="submit" value="提交"/></td>
19         </tr>
20     </table>
21     </form>
22 </body>
23 </html>

DeleteUser.ashx

 1 using System;
 2 using System.Collections.Generic;
 3 using System.Linq;
 4 using System.Web;
 5 
 6 namespace JJW.WebApp
 7 {
 8     /// <summary>
 9     /// DeleteUser 的摘要说明
10     /// </summary>
11     public class DeleteUser : IHttpHandler
12     {
13 
14         public void ProcessRequest(HttpContext context)
15         {
16             context.Response.ContentType = "text/html";
17             int id = Convert.ToInt32(context.Request["id"]);
18             BLL.UserInfoBll UserInfoBll = new BLL.UserInfoBll();
19             UserInfoBll.DeleteEntity(id);
20             context.Response.Redirect("UserInfoList.ashx");
21 
22         }
23 
24         public bool IsReusable
25         {
26             get
27             {
28                 return false;
29             }
30         }
31     }
32 }

ShowDetail.ashx

 1 using System;
 2 using System.Collections.Generic;
 3 using System.Linq;
 4 using System.Web;
 5 using System.IO;
 6 using JJW.Model;
 7 
 8 namespace JJW.WebApp
 9 {
10     /// <summary>
11     /// ShowDetail 的摘要说明
12     /// </summary>
13     public class ShowDetail : IHttpHandler
14     {
15 
16         public void ProcessRequest(HttpContext context)
17         {
18             context.Response.ContentType = "text/html";
19             string filePath = context.Request.MapPath("ShowDetail.html");
20             string fileContent = File.ReadAllText(filePath);
21             int id = Convert.ToInt32(context.Request["id"]);
22             BLL.UserInfoBll UserInfoBll = new BLL.UserInfoBll();
23             UserInfo userInfo = UserInfoBll.ShowDetail(id);
24             fileContent = fileContent.Replace("$id", userInfo.ID.ToString()).Replace("$userName", userInfo.UserName).Replace("$passWord", userInfo.PassWord);
25             context.Response.Write(fileContent);
26 
27 
28         }
29 
30         public bool IsReusable
31         {
32             get
33             {
34                 return false;
35             }
36         }
37     }
38 }

ShowDetail.html

 1 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
 2 <html xmlns="http://www.w3.org/1999/xhtml">
 3 <head>
 4     <title></title>
 5 </head>
 6 <body>
 7     <table border="1">
 8         <tr>
 9             <td>ID</td>
10             <td>$id</td>
11         </tr>
12         <tr>
13             <td>用户名</td>
14             <td>$userName</td>
15         </tr>
16         <tr>
17             <td>密码</td>
18             <td>$passWord</td>
19         </tr>
20     </table>
21 </body>
22 </html>

Update.ashx

 1 using System;
 2 using System.Collections.Generic;
 3 using System.Linq;
 4 using System.Web;
 5 using JJW.Model;
 6 using System.IO;
 7 
 8 namespace JJW.WebApp
 9 {
10     /// <summary>
11     /// Update 的摘要说明
12     /// </summary>
13     public class Update : IHttpHandler
14     {
15         public void ProcessRequest(HttpContext context)
16         {
17             context.Response.ContentType = "text/html";
18             int id;
19             if (int.TryParse((context.Request["id"]), out id))
20             {
21                 BLL.UserInfoBll UserInfoBll = new BLL.UserInfoBll();
22                 UserInfo userInfo = UserInfoBll.ShowDetail(id);
23                 //userInfo.UserName = context.Request["userName"];
24                 //userInfo.PassWord = context.Request["passWord"];
25                 //UserInfoBll.UpdateEntity(userInfo);
26 
27                 string filePath = context.Request.MapPath("Update.html");
28                 string fileContent = File.ReadAllText(filePath);
29                 fileContent = fileContent.Replace("$userName", userInfo.UserName).Replace("$passWord", userInfo.PassWord).Replace("$id",userInfo.ID.ToString());
30                 context.Response.Write(fileContent);
31                 //context.Response.Redirect("UserInfoList.ashx");
32             }
33 
34         }
35 
36         public bool IsReusable
37         {
38             get
39             {
40                 return false;
41             }
42         }
43     }
44 }

Update.html

 1 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
 2 <html xmlns="http://www.w3.org/1999/xhtml">
 3 <head>
 4     <title></title>
 5 </head>
 6 <body>
 7     <form action="Update2.ashx" method="post">
 8     <input type="hidden" name="id" value="$id" />
 9     <table>
10         <tr>
11             <td>
12                 用户名
13             </td>
14             <td>
15                 <input type="text" name="userName" value="$userName" />
16             </td>
17         </tr>
18         <tr>
19             <td>
20                 密码
21             </td>
22             <td>
23                 <input type="text" name="passWord" value="$passWord" />
24             </td>
25         </tr>
26         <tr>
27             <td colspan="2">
28                 <input type="submit" value="提交" />
29             </td>
30         </tr>
31     </table>
32     </form>
33 </body>
34 </html>

Update2.ashx

 1 using System;
 2 using System.Collections.Generic;
 3 using System.Linq;
 4 using System.Web;
 5 using JJW.Model;
 6 
 7 namespace JJW.WebApp
 8 {
 9     /// <summary>
10     /// Update2 的摘要说明
11     /// </summary>
12     public class Update2 : IHttpHandler
13     {
14 
15         public void ProcessRequest(HttpContext context)
16         {
17             context.Response.ContentType = "text/html";
18             UserInfo userInfo = new UserInfo();
19             userInfo.ID = Convert.ToInt32(context.Request["id"]);
20             userInfo.UserName = context.Request["userName"].ToString();
21             userInfo.PassWord = context.Request["passWord"].ToString();
22             BLL.UserInfoBll UserInfoBll = new BLL.UserInfoBll();
23             UserInfoBll.UpdateEntity(userInfo);
24             context.Response.Redirect("UserInfoList.ashx");
25         }
26 
27         public bool IsReusable
28         {
29             get
30             {
31                 return false;
32             }
33         }
34     }
35 }

UserInfoList.ashx

 1 using System;
 2 using System.Collections.Generic;
 3 using System.Linq;
 4 using System.Web;
 5 using System.IO;
 6 using JJW.Model;
 7 using System.Text;
 8 
 9 namespace JJW.WebApp
10 {
11     /// <summary>
12     /// UserInfoList 的摘要说明
13     /// </summary>
14     public class UserInfoList : IHttpHandler
15     {
16 
17         public void ProcessRequest(HttpContext context)
18         {
19             context.Response.ContentType = "text/html";
20             string filePath = context.Request.MapPath("UserInfoList.html");
21             string fileContent = File.ReadAllText(filePath);
22             BLL.UserInfoBll UserInfoBll = new BLL.UserInfoBll();
23             List<UserInfo> list = UserInfoBll.GetEntity();
24             StringBuilder sb = new StringBuilder();
25             foreach (UserInfo userInfo in list)
26             {
27                 sb.AppendFormat("<tr><td>{0}</td><td>{1}</td><td>{2}</td><td><a href='Update.ashx?id={0}'>修改</a></td><td><a href='DeleteUser.ashx?id={0}'>删除</a></td><td><a href='ShowDetail.ashx?id={0}'>详细</a></td></tr>", userInfo.ID, userInfo.UserName, userInfo.PassWord);
28             }
29             fileContent = fileContent.Replace("$tbody", sb.ToString());
30             context.Response.Write(fileContent);
31         }
32 
33         public bool IsReusable
34         {
35             get
36             {
37                 return false;
38             }
39         }
40     }
41 }

UserInfoLIst.html

 1 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
 2 <html xmlns="http://www.w3.org/1999/xhtml">
 3 <head>
 4     <title></title>
 5 </head>
 6 <body>
 7     <h1>用户表</h1>
 8     <a href ="Add.html">添加用户</a>
 9     <table border="1">
10         <tr>
11             <th>
12                 ID
13             </th>
14             <th>
15                 用户名
16             </th>
17             <th>
18                 密码
19             </th>
20             <th>
21                 修改
22             </th>
23             <th>
24                 删除
25             </th>
26             <th>
27                 详细
28             </th>
29         </tr>
30         $tbody
31     </table>
32 </body>
33 </html>

运行结果：

总结：

至今没有一天之内完整的写一遍，其实需要每天都练习的，里面有很多细节，特别是DAL层里的数据库操作，再次许愿，十月份能把asp.net部分学习完！嘻嘻(#^.^#)