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

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace JJW.Model
{
    public class UserInfo
    {
        public int ID { get; set; }
        public string UserName { get; set; }
        public string PassWord { get; set; }
    }
}

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

SqlHelper.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

namespace JJW.DAL
{
    public static class SqlHelper
    {
        private static readonly string conStr = ConfigurationManager.ConnectionStrings["conStr"].ConnectionString;
        public static DataTable GetTable(string sql, CommandType type, params SqlParameter[] ps)
        {
            using (SqlConnection con = new SqlConnection(conStr))
            {
                using (SqlDataAdapter sda = new SqlDataAdapter(sql, con))
                {
                    DataTable dt = new DataTable();
                    sda.SelectCommand.CommandType = type;
                    if (ps != null)
                    {
                        sda.SelectCommand.Parameters.AddRange(ps);
                    }
                    sda.Fill(dt);
                    return dt;
                }
            }
        }

        public static int ExecuteNonQuery(string sql, CommandType type, params SqlParameter[] ps)
        {
            using (SqlConnection con = new SqlConnection(conStr))
            {
                using (SqlCommand cmd = new SqlCommand(sql, con))
                {
                    cmd.CommandType = type;
                    if (ps != null)
                    {
                        cmd.Parameters.AddRange(ps);
                    }
                    con.Open();
                    return cmd.ExecuteNonQuery();
                }
            }
        }
    }
}

UserInfoDal.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using JJW.Model;
using System.Data;
using System.Data.SqlClient;

namespace JJW.DAL
{
    public class UserInfoDal
    {
        /// <summary>
        /// 返回列表
        /// </summary>
        /// <returns></returns>
        public List<UserInfo> GetEntity()
        {
            string sql = "SELECT * FROM userInfo";
            DataTable dt = SqlHelper.GetTable(sql, CommandType.Text);
            List<UserInfo> list = new List<UserInfo>();
            if (dt.Rows.Count > 0)
            {
                foreach (DataRow dr in dt.Rows)
                {
                    UserInfo userInfo = new UserInfo();
                    LoadEntity(userInfo, dr);
                    list.Add(userInfo);
                }
            }
            return list;
        }
        /// <summary>
        /// 将表转为属性
        /// </summary>
        /// <param name="userInfo"></param>
        /// <param name="dr"></param>
        private void LoadEntity(UserInfo userInfo, DataRow dr)
        {
            userInfo.ID = Convert.ToInt32(dr["id"]);
            userInfo.UserName = dr["userName"] != DBNull.Value ? dr["userName"].ToString() : string.Empty;
            userInfo.PassWord = dr["passWord"] != DBNull.Value ? dr["passWord"].ToString() : string.Empty;
        }
        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public int DeleteEntity(int id)
        {
            string sql = "DELETE FROM userInfo WHERE id = @id";
            return SqlHelper.ExecuteNonQuery(sql, CommandType.Text, new SqlParameter("@id", id));
        }
        /// <summary>
        /// 插入
        /// </summary>
        /// <param name="userName"></param>
        /// <param name="passWord"></param>
        /// <returns></returns>
        public int InsertEntity(UserInfo userInfo)
        {
            string sql = "INSERT INTO userInfo(userName,passWord) VALUES(@userName,@passWord)";
            return SqlHelper.ExecuteNonQuery(sql, CommandType.Text, new SqlParameter[]{
                new SqlParameter("@userName",userInfo.UserName),new SqlParameter("@passWord",userInfo.PassWord)
            });
        }
        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="userName"></param>
        /// <param name="passWord"></param>
        /// <param name="id"></param>
        /// <returns></returns>
        public int UpdateEntity(UserInfo userInfo)
        {
            string sql = "UPDATE userInfo SET userName=@userName, passWord=@passWord WHERE id=@id";
            SqlParameter[] ps = {new SqlParameter("@userName",SqlDbType.NVarChar,32),new SqlParameter("@passWord",SqlDbType.NVarChar,32),new SqlParameter("@id",SqlDbType.Int,4) };
            return SqlHelper.ExecuteNonQuery(sql, CommandType.Text, new SqlParameter[]{
                new SqlParameter("@userName",userInfo.UserName),new SqlParameter("@passWord",userInfo.PassWord),new SqlParameter("@id",userInfo.ID)
            });
        }
        /// <summary>
        /// 详细
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public UserInfo ShowDetail(int id)
        {
            string sql = "SELECT * FROM userInfo WHERE id = @id";
            DataTable dt = SqlHelper.GetTable(sql, CommandType.Text, new SqlParameter("@id", id));
            UserInfo userInfo = new UserInfo();
            if (dt.Rows.Count > 0)
            {
                LoadEntity(userInfo, dt.Rows[0]);
            }
            return userInfo;
        }
    }
}

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

UserInfoBll.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using JJW.Model;

namespace JJW.BLL
{
    public class UserInfoBll
    {
        DAL.UserInfoDal UserInfoDal = new DAL.UserInfoDal();
        public List<UserInfo> GetEntity()
        {
            return UserInfoDal.GetEntity();
        }
        public int DeleteEntity(int id)
        {
            return UserInfoDal.DeleteEntity(id);
        }
        public int InsertEntity(UserInfo userInfo)
        {
            return UserInfoDal.InsertEntity(userInfo);
        }
        public int UpdateEntity(UserInfo userInfo)
        {
            return UserInfoDal.UpdateEntity(userInfo);
        }
        public UserInfo ShowDetail(int id)
        {
            return UserInfoDal.ShowDetail(id);
        }
    }
}

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

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

<?xml version="1.0" encoding="utf-8"?>

<!--
  有关如何配置 ASP.NET 应用程序的详细消息，请访问
  http://go.microsoft.com/fwlink/?LinkId=169433
  -->

<configuration>
  <system.web>
    <compilation debug="true" targetFramework="4.0" />
  </system.web>
  <connectionStrings>
    <!--<add connectionString="data source=.;initial catalog=jjwdb;integrated security=true;" name="conStr"/>-->
    <add connectionString="server=.;uid=sa;pwd=123;database=jjwdb;" name="conStr"/>
  </connectionStrings>
</configuration>

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

Add.ashx

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using JJW.Model;

namespace JJW.WebApp
{
    /// <summary>
    /// Add 的摘要说明
    /// </summary>
    public class Add : IHttpHandler
    {

        public void ProcessRequest(HttpContext context)
        {
            context.Response.ContentType = "text/html";
            UserInfo userInfo = new UserInfo();
            userInfo.UserName = context.Request["userName"];
            userInfo.PassWord = context.Request["passWord"];
            BLL.UserInfoBll UserInfoBll = new BLL.UserInfoBll();
            UserInfoBll.InsertEntity(userInfo);
            context.Response.Redirect("UserInfoList.ashx");
        }

        public bool IsReusable
        {
            get
            {
                return false;
            }
        }
    }
}

Add.html

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
    <title></title>
</head>
<body>
    <form action="Add.ashx" method="post">
    <table border="1">
        <tr>
            <td>用户名</td>
            <td><input type="text" name="userName"/></td>
        </tr>
        <tr>
            <td>密码</td>
            <td><input type="text" name="passWord"/></td>
        </tr>
        <tr>
            <td colspan="2"><input type="submit" value="提交"/></td>
        </tr>
    </table>
    </form>
</body>
</html>

DeleteUser.ashx

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace JJW.WebApp
{
    /// <summary>
    /// DeleteUser 的摘要说明
    /// </summary>
    public class DeleteUser : IHttpHandler
    {

        public void ProcessRequest(HttpContext context)
        {
            context.Response.ContentType = "text/html";
            int id = Convert.ToInt32(context.Request["id"]);
            BLL.UserInfoBll UserInfoBll = new BLL.UserInfoBll();
            UserInfoBll.DeleteEntity(id);
            context.Response.Redirect("UserInfoList.ashx");

        }

        public bool IsReusable
        {
            get
            {
                return false;
            }
        }
    }
}

ShowDetail.ashx

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.IO;
using JJW.Model;

namespace JJW.WebApp
{
    /// <summary>
    /// ShowDetail 的摘要说明
    /// </summary>
    public class ShowDetail : IHttpHandler
    {

        public void ProcessRequest(HttpContext context)
        {
            context.Response.ContentType = "text/html";
            string filePath = context.Request.MapPath("ShowDetail.html");
            string fileContent = File.ReadAllText(filePath);
            int id = Convert.ToInt32(context.Request["id"]);
            BLL.UserInfoBll UserInfoBll = new BLL.UserInfoBll();
            UserInfo userInfo = UserInfoBll.ShowDetail(id);
            fileContent = fileContent.Replace("$id", userInfo.ID.ToString()).Replace("$userName", userInfo.UserName).Replace("$passWord", userInfo.PassWord);
            context.Response.Write(fileContent);


        }

        public bool IsReusable
        {
            get
            {
                return false;
            }
        }
    }
}

ShowDetail.html

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
    <title></title>
</head>
<body>
    <table border="1">
        <tr>
            <td>ID</td>
            <td>$id</td>
        </tr>
        <tr>
            <td>用户名</td>
            <td>$userName</td>
        </tr>
        <tr>
            <td>密码</td>
            <td>$passWord</td>
        </tr>
    </table>
</body>
</html>

Update.ashx

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using JJW.Model;
using System.IO;

namespace JJW.WebApp
{
    /// <summary>
    /// Update 的摘要说明
    /// </summary>
    public class Update : IHttpHandler
    {
        public void ProcessRequest(HttpContext context)
        {
            context.Response.ContentType = "text/html";
            int id;
            if (int.TryParse((context.Request["id"]), out id))
            {
                BLL.UserInfoBll UserInfoBll = new BLL.UserInfoBll();
                UserInfo userInfo = UserInfoBll.ShowDetail(id);
                //userInfo.UserName = context.Request["userName"];
                //userInfo.PassWord = context.Request["passWord"];
                //UserInfoBll.UpdateEntity(userInfo);

                string filePath = context.Request.MapPath("Update.html");
                string fileContent = File.ReadAllText(filePath);
                fileContent = fileContent.Replace("$userName", userInfo.UserName).Replace("$passWord", userInfo.PassWord).Replace("$id",userInfo.ID.ToString());
                context.Response.Write(fileContent);
                //context.Response.Redirect("UserInfoList.ashx");
            }

        }

        public bool IsReusable
        {
            get
            {
                return false;
            }
        }
    }
}

Update.html

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
    <title></title>
</head>
<body>
    <form action="Update2.ashx" method="post">
    <input type="hidden" name="id" value="$id" />
    <table>
        <tr>
            <td>
                用户名
            </td>
            <td>
                <input type="text" name="userName" value="$userName" />
            </td>
        </tr>
        <tr>
            <td>
                密码
            </td>
            <td>
                <input type="text" name="passWord" value="$passWord" />
            </td>
        </tr>
        <tr>
            <td colspan="2">
                <input type="submit" value="提交" />
            </td>
        </tr>
    </table>
    </form>
</body>
</html>

Update2.ashx

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using JJW.Model;

namespace JJW.WebApp
{
    /// <summary>
    /// Update2 的摘要说明
    /// </summary>
    public class Update2 : IHttpHandler
    {

        public void ProcessRequest(HttpContext context)
        {
            context.Response.ContentType = "text/html";
            UserInfo userInfo = new UserInfo();
            userInfo.ID = Convert.ToInt32(context.Request["id"]);
            userInfo.UserName = context.Request["userName"].ToString();
            userInfo.PassWord = context.Request["passWord"].ToString();
            BLL.UserInfoBll UserInfoBll = new BLL.UserInfoBll();
            UserInfoBll.UpdateEntity(userInfo);
            context.Response.Redirect("UserInfoList.ashx");
        }

        public bool IsReusable
        {
            get
            {
                return false;
            }
        }
    }
}

UserInfoList.ashx

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.IO;
using JJW.Model;
using System.Text;

namespace JJW.WebApp
{
    /// <summary>
    /// UserInfoList 的摘要说明
    /// </summary>
    public class UserInfoList : IHttpHandler
    {

        public void ProcessRequest(HttpContext context)
        {
            context.Response.ContentType = "text/html";
            string filePath = context.Request.MapPath("UserInfoList.html");
            string fileContent = File.ReadAllText(filePath);
            BLL.UserInfoBll UserInfoBll = new BLL.UserInfoBll();
            List<UserInfo> list = UserInfoBll.GetEntity();
            StringBuilder sb = new StringBuilder();
            foreach (UserInfo userInfo in list)
            {
                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);
            }
            fileContent = fileContent.Replace("$tbody", sb.ToString());
            context.Response.Write(fileContent);
        }

        public bool IsReusable
        {
            get
            {
                return false;
            }
        }
    }
}

UserInfoLIst.html

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
    <title></title>
</head>
<body>
    <h1>用户表</h1>
    <a href ="Add.html">添加用户</a>
    <table border="1">
        <tr>
            <th>
                ID
            </th>
            <th>
                用户名
            </th>
            <th>
                密码
            </th>
            <th>
                修改
            </th>
            <th>
                删除
            </th>
            <th>
                详细
            </th>
        </tr>
        $tbody
    </table>
</body>
</html>

运行结果：

总结：

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