回味手写三层-增删改查

先看下全家福吧

切入正题

首先要有一个model，没有对象操作怎么行

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

namespace ClassModel
{
    public class ClassModels
    {
        private int cId;
        public int CId
        {
            get { return cId; }
            set { cId = value; }
        }
        private string cName;

        public string CName
        {
            get { return cName; }
            set { cName = value; }
        }
        private int cCount;

        public int CCount
        {
            get { return cCount; }
            set { cCount = value; }
        }
      

    }
}

　　

然后要有一个SqlHelper（不然操作数据库要累死了）

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

namespace ClassDal
{
    class SqlHelper
    {
        public static string connstr = ConfigurationManager.ConnectionStrings["sql"].ConnectionString;
        public static SqlDataReader ExcuteReader(string sql, params SqlParameter[] pams)
        {
            SqlConnection conn = new SqlConnection(connstr);
            using (SqlCommand cmd=new SqlCommand(sql,conn))
            {
                if (pams!=null)
                {
                    cmd.Parameters.AddRange(pams);
                }
                if (conn.State==System.Data.ConnectionState.Closed)
                {
                    conn.Open();
                }
                return cmd.ExecuteReader();
            }

        }

        public static DataTable ExcuteDataTable(string sql,params SqlParameter[] pams)
        {
            using (SqlDataAdapter adapter=new SqlDataAdapter(sql,connstr))
            {
                if (pams!=null)
                {
                    adapter.SelectCommand.Parameters.AddRange(pams);
                }
                DataTable dt = new DataTable();
                adapter.Fill(dt);
                return dt;
            }
        }
        //增删改
        public static int ExcuteNonQuery(string sql, params SqlParameter[] pams)
        {
            using (SqlConnection conn=new SqlConnection(connstr))
            {
                using (SqlCommand cmd=new SqlCommand(sql,conn))
                {
                    if (pams!=null)
                    {
                        cmd.Parameters.AddRange(pams);
                    }
                    if (conn.State==ConnectionState.Closed)
                    {
                        conn.Open();
                    }
                    return cmd.ExecuteNonQuery();
                }
            }
        }

        public static object ExcuteScalar(string sql, params SqlParameter[] pams)
        {
            using (SqlConnection conn=new SqlConnection(connstr))
            {
                using (SqlCommand cmd=new SqlCommand(sql,conn))
                {
                    if (pams!=null)
                    {
                        cmd.Parameters.AddRange(pams);
                    }
                    if (conn.State==ConnectionState.Closed)
                    {
                        conn.Open();
                    }
                    return cmd.ExecuteScalar();
                }
            }
        }
    }


}

然后写 数据访问层（DAL）：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace ClassDal
{
    public class ClassDals
    {
        //datetable 获取信息
        public DataTable GetAll()
        {
            string sql = "select *from Classes";
            return SqlHelper.ExcuteDataTable(sql);
        }
        // datereader 获取信息
        public SqlDataReader GetAllInfo()
        {
            string sql = "select *from Classes";
            return SqlHelper.ExcuteReader(sql);
        }
        public SqlDataReader GetInfoFromId(ClassModel.ClassModels model)
        {
            string sql = "select *from Classes where CID=@id";
            return SqlHelper.ExcuteReader(sql, new SqlParameter("@id",model.CId));
        }
        public int DeleteInfo(int id)
        {
            string sql = "delete from Classes where CID=@cid";
            SqlParameter pam = new SqlParameter("@cid",id);
            return SqlHelper.ExcuteNonQuery(sql,pam);
        }
        //CID, CName, CCount, CImage, CIsDel, CAddTime
        public int AddInfo(ClassModel.ClassModels model)
        {
            
            string sql = "insert into Classes(CName,CCount)values(@CName,@CCount)";
            SqlParameter[] pam = new SqlParameter[] {
            new SqlParameter("@CName",model.CName),
            new SqlParameter("@CCount",model.CCount)
            };
            return SqlHelper.ExcuteNonQuery(sql,pam);
        }
        public int UpdateInfo(ClassModel.ClassModels model)
        {
            //update Classes set CName='123',CCount='123' where CID='43'
            string sql = "update Classes set CName=@name,CCount=@count where CID=@id";
            SqlParameter[] pams = new SqlParameter[] {
            new SqlParameter("@id",model.CId),
            new SqlParameter("@name",model.CName),
            new SqlParameter("@count",model.CCount)
            };
            return SqlHelper.ExcuteNonQuery(sql,pams);
        }
       
    }
}

在写 业务逻辑层（BLL）：

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

namespace ClassBll
{
    public class ClassBll
    {
        ClassDals dal = new ClassDals();
        public List<ClassModel.ClassModels> GetAll()
        {
            DataTable da = dal.GetAll();
            List<ClassModel.ClassModels> list = new List<ClassModel.ClassModels>();
            if (da.Rows.Count > 0)
            {
                ClassModel.ClassModels mode = null;
                foreach (DataRow row in da.Rows)
                {
                    mode = new ClassModel.ClassModels();
                    mode.CId = Convert.ToInt32(row["CID"].ToString());
                    mode.CCount = Convert.ToInt32(row["CCount"].ToString());
                    mode.CName = row["CName"].ToString();
                    list.Add(mode);
                }
                return list;
            }
            else
            {
                return null;
            }
        }

        public List<ClassModel.ClassModels> GetAllInfo()
        {
            List<ClassModel.ClassModels> list = new List<ClassModel.ClassModels>();
            SqlDataReader reader = dal.GetAllInfo();
            while (reader.Read())
            {
                if (reader.HasRows)
                {
                    ClassModel.ClassModels model = new ClassModel.ClassModels();
                    model.CId = reader.GetInt32(0);
                    model.CName = reader.GetString(1);
                    model.CCount = reader.GetInt32(2);
                    list.Add(model);
                    
                }
            }
            return list;
        }
        public ClassModel.ClassModels GetInfoFromId(ClassModel.ClassModels model)
        {
           
            SqlDataReader reader = dal.GetInfoFromId(model);
            if (reader.Read())
            {
                if (reader.HasRows)
                {
                    //ClassModel.ClassModels model = new ClassModel.ClassModels();
                    model.CId = reader.GetInt32(0);
                    model.CName = reader.GetString(1);
                    model.CCount = reader.GetInt32(2);
                    

                }
            }
         
            return model;
        }
        public int DeleteInfo(int id)
        {
          return  dal.DeleteInfo(id);
        }
        public int AddInfo(ClassModel.ClassModels model)
        {
            return dal.AddInfo(model);
        }
        public int UpdateInfo(ClassModel.ClassModels model)
        {
            return dal.UpdateInfo(model);
        }
       
    }
}

然后要写一个list页面显示数据，不然怎么增删改呢？(好像是废话!)

前台：

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="List.aspx.cs" Inherits="List" %>

<!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 runat="server">
    <title>学生成绩管理系统</title>
    <style type="text/css">
        #tbList
        {
            border: 1px solid blue;
            
        }
        #tbList th, td
        {
            border-bottom: 1px solid blue;
            border-right: 1px solid blue;
            
        }
    </style>
</head>
<body>
    <form id="form1" method="post" action="AddInfo.aspx">
    <div>
        <table border="1" cellpadding="1" cellspacing="0">
            <tr>
                <th>编号</th><th>班级名称</th><th>人数</th><th colspan="2">操作</th>
            </tr>
            <%=GetAllInfo()%>
        </table>
        <input type="submit" name="btnAdd" value="添加" />
    </div>
    </form>
</body>
</html>

后台：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Text;

public partial class List : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    public string GetAllInfo()
    {
        StringBuilder sb = new StringBuilder();
        ClassBll.ClassBll bll = new ClassBll.ClassBll();
        List<ClassModel.ClassModels> list = bll.GetAllInfo();
        foreach (ClassModel.ClassModels model in list)
        {
            sb.Append("<tr>");
            sb.Append("<td>" + model.CId + "</td>");
            sb.Append("<td>" + model.CName + "</td>");
            sb.Append("<td>" + model.CCount + "</td>");
            sb.Append("<td><a href='Update.aspx?id=" + model.CId + "'>修改</td>");
            sb.Append("<td><a href='Delete.ashx?id=" + model.CId + "'>删除</td>");
            sb.Append("</tr>");
        }
        return sb.ToString();
    }
}

然后先写增加 吧！要不然一会辛辛苦苦写的数据 都删完了！

这里完全可以把 增加 写到 list页面的，太懒了，回头改下

前台：

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="AddInfo.aspx.cs" Inherits="AddInfo" %>

<!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 runat="server">
    <title></title>
</head>
<body>
    <form id="form1" method="post" action="AddInfo.aspx">
    <div>
       班级名称： <input type="text" name="txtCName" value="" /><br />
       班级人数： <input type="text" name="txtCCount" value="" /><br />
        <input type="hidden" name="isPostBack" value="2" />
        <input type="submit" name="btnAdd" value="保存" />
    </div>
    </form>
</body>
</html>

后台：

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

public partial class AddInfo : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        string name = Request.Form["txtCName"];//txtCCount  isPostBack
        string countstr = Request.Form["txtCCount"];
        string ispostBack=Request.Form["isPostBack"];
        if (!string.IsNullOrEmpty(ispostBack))
        {
            int count = 0;
            if (int.TryParse(countstr,out count))
            {
                ClassBll.ClassBll bll = new ClassBll.ClassBll();
                ClassModel.ClassModels model = new ClassModel.ClassModels();
                model.CName = name;
                model.CCount = count;
                if (bll.AddInfo(model)>0)
                {
                    Response.Write("<script type='text/javascript'>alert('添加成功！');window.location='List.aspx'</script>");
                }
                else
                {
                    Response.Write("添加失败！");
                }

            }
        }
    }
}

该写删除了 一个一般处理程序搞定！

<%@ WebHandler Language="C#" Class="Delete" %>

using System;
using System.Web;

public class Delete : IHttpHandler {
    
    public void ProcessRequest (HttpContext context) {
        context.Response.ContentType = "text/html";
        string id=context.Request.QueryString["id"];
        if (!string.IsNullOrEmpty(id))
        {
            int sid = 0;
            if (int.TryParse(id,out sid))
            {
                ClassBll.ClassBll bll = new ClassBll.ClassBll();
                
                if (bll.DeleteInfo(sid)>0)
                {
                    context.Response.Write("<script type='text/javascript'>alert('删除成功！');window.location='List.aspx'</script>");
                }
                else
                {
                    context.Response.Write("删除失败！");
                }
            }
        }
    }
 
    public bool IsReusable {
        get {
            return false;
        }
    }

}

最后就是修改了，这个才是有点意思的

前台：

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Update.aspx.cs" Inherits="Update" %>

<!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 runat="server">
    <title></title>
</head>
<body>
    <form id="form1" method="post" action="Update.aspx">
    <div>
        班级名称：
        <%--用方法返回一个字符串--%>
        <%-- <input type="text" name="txtCName" value="<%=CCNameInfo() %>" /><br />--%>
        <input type="text" name="txtCName" value="<%=model.CName %>" /><br />
        班级人数：
        <input type="text" name="txtCCount" value="<%=model.CCount %>" /><br />
        <input type="hidden" name="isPostBack" value="<%=model.CId %>" />
        <input type="submit" name="btnUpdate" value="保存" />
    </div>
    </form>
</body>
</html>

后台：

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

public partial class Update : System.Web.UI.Page
{
    ClassBll.ClassBll bll = new ClassBll.ClassBll();
    protected  ClassModel.ClassModels model = new ClassModel.ClassModels();
    protected void Page_Load(object sender, EventArgs e)
    {
        string isPostback = Request.Form["isPostBack"];
        //如果是准备提交保存
        if (!string.IsNullOrEmpty(isPostback))
        {
            int idCount = 0;
            if (int.TryParse(isPostback,out idCount))
            {
                string cName = Request.Form["txtCName"];// txtCName txtCCount
                string cCount = Request.Form["txtCCount"];
                model.CId = idCount;
                model.CName = cName;
                model.CCount =Convert.ToInt32(cCount);
                if (bll.UpdateInfo(model)>0)
                {
                    Response.Write("<script type='text/javascript'>alert('保存成功！');window.location='List.aspx'</script>");
                }
                else
                {
                    Response.Write("修改失败！");
                }
            }
        }
        else//如果是第一次访问
        {
            string id = Request.QueryString["id"];

            int idcount = 0;
            if (int.TryParse(id, out idcount))
            {
                model.CId = idcount;
                //查寻数据并存到model中
                model = bll.GetInfoFromId(model);
            }
        }



    }
    //protected string CId()
    //{
    //    return model.CId.ToString();
    //}
    //protected string CCountInfo()
    //{
    //    return model.CCount.ToString();
    //}
    //protected string CCNameInfo()
    //{
    //    return model.CName;
    //}
}

其实 修改 和 添加完全可以共用一个页面，或者一个div就行了，这里我就分开写了，看着简单点！

对了别忘了配置webconfig 哦！

<?xml version="1.0"?>
<!--
  有关如何配置 ASP.NET 应用程序的详细信息，请访问
  http://go.microsoft.com/fwlink/?LinkId=169433
  -->
<configuration>
  <connectionStrings>
    <add name="sql" connectionString="Data Source=.;Initial Catalog=ProManager;Integrated Security=true;"/>
  </connectionStrings>
  <system.web>
    <compilation debug="true" targetFramework="4.0"/>
  </system.web>
</configuration>

这样就大功搞成了，挺简单的吧！