04-人员增删改查

案例：个人信息管理。

l 使用NVelocity的开发方式重写登录程序，把NVelocity封装成
RenderTemplate方法。
l 这种HttpHandler+ NVelocity的方式非常类似于PHP+smarty的开
发方式，也有利于理解asp.net mvc。HttpHandler就是Controller
，模板就是View， Controller不操作View的内部细节，只是把数
据给View，由View自己控制怎样显示。
l 字段：Id、姓名、年龄、个人网址（可以为空）。
l 列表页面和编辑界面：PersonList.aspx、
PersonEdit.aspx?Action=AddNew、
PersonEdit.aspx?Action=Edit&Id=2（在PersonEdit页面判断是
否传过来了save按钮来判断是加载还是保存。渲染页面的时候把
Action和Id保存在隐藏字段中。保存成功后Redirect回List页面）
l 进一步案例：有关联字段，比如班级，实现备注

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

namespace CRUDTest1
{
    /// <summary>
    /// PersonList 的摘要说明
    /// </summary>
    public class PersonList : IHttpHandler
    {

        public void ProcessRequest(HttpContext context)
        {
            context.Response.ContentType = "text/html";
            DataTable dt =
                SqlHelper.ExecuteDataTable("select * from T_Persons");
            //DataTable不是集合，所以无法foreach遍历，DataTable的Rows属性
            //代表表格中的数据行的集合（DataRow的集合），一般传递DataRowCollection
            //给模板方便遍历
            string html = CommonHelper.RenderHtml("PersonList.htm", dt.Rows);
            
            context.Response.Write(html);

            //MVC：
        }

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

View Code

using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Data.SqlClient; using System.Data; namespace CRUDTest1 { /// <summary> /// PersonEdit 的摘要说明 /// </summary> public class PersonEdit : IHttpHandler { public void ProcessRequest(HttpContext context) { context.Response.ContentType = "text/html"; //PersonEdit.ashx?action=AddNew //PersonEdit.ashx?action=Edit&Id=3 string action = context.Request["Action"]; if (action == "AddNew") { //判断是否含有Save并且等于true，如果是的话就说明是点击【保存】按钮请求来的 bool save = Convert.ToBoolean(context.Request["Save"]); if (save)//是保存 { string name = context.Request["Name"]; int age = Convert.ToInt32(context.Request["Age"]); string email = context.Request["Email"]; long classId = Convert.ToInt32(context.Request["ClassId"]); SqlHelper.ExecuteNonQuery("Insert into T_Persons(Name,Age,Email,ClassId) values(@Name,@Age,@Email,@ClassId)", new SqlParameter("@Name", name) , new SqlParameter("@Age", age) , new SqlParameter("@Email", email) , new SqlParameter("@ClassId", classId)); context.Response.Redirect("PersonList.ashx");//保存成功返回列表页面 } else { //string html = CommonHelper.RenderHtml("PersonEdit.htm", new { Name = "", Age = 20, Email = "@rupeng.com" }); //var data = new { Name = "", Age = 20, Email = "@rupeng.com" }; DataTable dtClasses = SqlHelper.ExecuteDataTable("select * from T_Classes"); var data = new { Action = "AddNew", Person = new { Name = "", Age = 20, Email = "@rupeng.com" }, Classes = dtClasses.Rows }; string html = CommonHelper.RenderHtml("PersonEdit.htm", data); context.Response.Write(html); } } else if (action == "Edit") { bool save = Convert.ToBoolean(context.Request["Save"]); if (save) { string name = context.Request["Name"]; int age = Convert.ToInt32(context.Request["Age"]); string email = context.Request["Email"]; long id = Convert.ToInt64(context.Request["Id"]); long classId = Convert.ToInt64(context.Request["ClassId"]); SqlHelper.ExecuteNonQuery("update T_Persons set Name=@Name,Age=@Age,Email=@Email,ClassId=@ClassId where Id=@Id", new SqlParameter("@Name", name) , new SqlParameter("@Age", age) , new SqlParameter("@Email", email) , new SqlParameter("@Id", id) , new SqlParameter("@ClassId", classId)); context.Response.Redirect("PersonList.ashx");//保存成功返回列表页面 } else { long id = Convert.ToInt64(context.Request["Id"]); DataTable dt = SqlHelper.ExecuteDataTable("select * from T_Persons where Id=@Id", new SqlParameter("@Id", id)); if (dt == null || dt.Rows == null || dt.Rows.Count <= 0) { context.Response.Write("没有找到Id=" + id + "的数据"); return; } else if (dt.Rows.Count > 1) { context.Response.Write("找到多条Id=" + id + "的数据"); } else { DataRow row = dt.Rows[0]; DataTable dtClasses = SqlHelper.ExecuteDataTable("select * from T_Classes"); var data = new { Action = "Edit", Person = row, Classes = dtClasses.Rows }; string html = CommonHelper.RenderHtml("PersonEdit.htm", data); context.Response.Write(html); } } } else if (action == "Delete") { long id = Convert.ToInt64(context.Request["Id"]); SqlHelper.ExecuteNonQuery("delete from T_Persons where Id=@Id", new SqlParameter("@Id", id)); context.Response.Redirect("PersonList.ashx"); } else { context.Response.Write("Action参数错误！"); } } public bool IsReusable { get { return false; } } } }

using System; using System.Collections.Generic; using System.Linq; using System.Web; using NVelocity.App; using NVelocity.Runtime; using NVelocity; namespace CRUDTest1 { public class CommonHelper { /// <summary> /// 用data数据填充templateName模板，渲染生成html返回 /// </summary> /// <param name="templateName"></param> /// <param name="data"></param> /// <returns></returns> public static string RenderHtml(string templateName, object data) { VelocityEngine vltEngine = new VelocityEngine(); vltEngine.SetProperty(RuntimeConstants.RESOURCE_LOADER, "file"); vltEngine.SetProperty(RuntimeConstants.FILE_RESOURCE_LOADER_PATH, System.Web.Hosting.HostingEnvironment.MapPath("~/templates"));//模板文件所在的文件夹 vltEngine.Init(); VelocityContext vltContext = new VelocityContext(); vltContext.Put("Data", data);//设置参数，在模板中可以通过$data来引用 Template vltTemplate = vltEngine.GetTemplate(templateName); System.IO.StringWriter vltWriter = new System.IO.StringWriter(); vltTemplate.Merge(vltContext, vltWriter); string html = vltWriter.GetStringBuilder().ToString(); return html; } } }

using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Configuration; using System.Data.SqlClient; using System.Data; namespace CRUDTest1 { public static class SqlHelper { public static readonly string connstr = ConfigurationManager.ConnectionStrings["connstr"].ConnectionString; public static SqlConnection OpenConnection() { SqlConnection conn = new SqlConnection(connstr); conn.Open(); return conn; } public static int ExecuteNonQuery(string cmdText, params SqlParameter[] parameters) { using (SqlConnection conn = new SqlConnection(connstr)) { conn.Open(); return ExecuteNonQuery(conn, cmdText, parameters); } } public static object ExecuteScalar(string cmdText, params SqlParameter[] parameters) { using (SqlConnection conn = new SqlConnection(connstr)) { conn.Open(); return ExecuteScalar(conn, cmdText, parameters); } } public static DataTable ExecuteDataTable(string cmdText, params SqlParameter[] parameters) { using (SqlConnection conn = new SqlConnection(connstr)) { conn.Open(); return ExecuteDataTable(conn, cmdText, parameters); } } public static int ExecuteNonQuery(SqlConnection conn,string cmdText, params SqlParameter[] parameters) { using (SqlCommand cmd = conn.CreateCommand()) { cmd.CommandText = cmdText; cmd.Parameters.AddRange(parameters); return cmd.ExecuteNonQuery(); } } public static object ExecuteScalar(SqlConnection conn, string cmdText, params SqlParameter[] parameters) { using (SqlCommand cmd = conn.CreateCommand()) { cmd.CommandText = cmdText; cmd.Parameters.AddRange(parameters); return cmd.ExecuteScalar(); } } public static DataTable ExecuteDataTable(SqlConnection conn, string cmdText, params SqlParameter[] parameters) { using (SqlCommand cmd = conn.CreateCommand()) { cmd.CommandText = cmdText; cmd.Parameters.AddRange(parameters); using (SqlDataAdapter adapter = new SqlDataAdapter(cmd)) { DataTable dt = new DataTable(); adapter.Fill(dt); return dt; } } } public static object ToDBValue(this object value) { return value == null ? DBNull.Value : value; } public static object FromDBValue(this object dbValue) { return dbValue == DBNull.Value ? null : dbValue; } } }

<?xml version="1.0" encoding="utf-8"?>  <configuration> <system.web> <compilation debug="true" targetFramework="4.0" /> </system.web> <connectionStrings> <add name="connstr" connectionString="Data Source=.;Initial Catalog=CRUDTest;User Id=sa;Password=123456;"></add> </connectionStrings> </configuration>

<!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> <a href="PersonEdit.ashx?Action=AddNew">新增人员</a> <table> <thead> <tr><td>编辑</td><td>删除</td><td>姓名</td><td>年龄</td><td>邮箱</td></tr> </thead> <tbody> #foreach($person in $Data) <tr><td><a href="PersonEdit.ashx?Action=Edit&Id=$person.Id">编辑</a></td><td><a href="PersonEdit.ashx?Action=Delete&Id=$person.Id">删除</a></td><td>$person.Name</td><td>$person.Age</td><td>$person.Email</td></tr> #end </tbody> </table> </body> </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> #if($Data.Action="AddNew") 新增用户 #else 编辑用户$Data.Person.Name #end </title> </head> <body> <form action="PersonEdit.ashx" method="post"> <input type="hidden" name="Action" value="$Data.Action" /> <input type="hidden" name="Save" value="true" /> <input type="hidden" name="Id" value="$Data.Person.Id" /> <table> <tr><td>姓名：</td><td><input type="text" name="Name" value="$Data.Person.Name" /></td></tr> <tr><td>年龄：</td><td><input type="text" name="Age" value="$Data.Person.Age" /></td></tr> <tr><td>邮箱：</td><td><input type="text" name="Email" value="$Data.Person.Email" /></td></tr> <tr><td>班级：</td><td> <select name="ClassId"> #foreach($class in $Data.Classes) #if($class.Id==$Data.Person.ClassId) <option value="$class.Id" selected>$class.Name</option> #else <option value="$class.Id">$class.Name</option> #end #end </select> </td></tr> <tr><td></td><td><input type="submit" value="保存"/></td></tr> </table> </form> </body> </html>

using System; using System.Collections.Generic; using System.Linq; using System.Web; using NVelocity.App; using NVelocity.Runtime; using NVelocity; namespace WebApplication1 { public static class CommonHelper { public static string ReaderHtml(string templateName, object data) { VelocityEngine vltEngine = new VelocityEngine(); vltEngine.SetProperty(RuntimeConstants.RESOURCE_LOADER, "file"); vltEngine.SetProperty(RuntimeConstants.FILE_RESOURCE_LOADER_PATH, System.Web.Hosting.HostingEnvironment.MapPath("~/templates"));//模板文件所在的文件夹 vltEngine.Init(); VelocityContext vltContext = new VelocityContext(); vltContext.Put("Data", data);//设置参数，在模板中可以通过$data来引用 Template vltTemplate = vltEngine.GetTemplate(templateName); System.IO.StringWriter vltWriter = new System.IO.StringWriter(); vltTemplate.Merge(vltContext, vltWriter); string html = vltWriter.GetStringBuilder().ToString(); return html; } } }

using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Configuration; using System.Data.SqlClient; using System.Data; namespace WebApplication1 { public static class SqlHelper { public static readonly string connstr = ConfigurationManager.ConnectionStrings["connstr"].ConnectionString; public static SqlConnection OpenConnection() { SqlConnection conn = new SqlConnection(connstr); conn.Open(); return conn; } public static int ExecuteNonQuery(string sql, params SqlParameter[] parameters) { using (SqlConnection conn = new SqlConnection(connstr)) { conn.Open(); using (SqlCommand cmd = conn.CreateCommand()) { cmd.CommandText = sql; cmd.Parameters.AddRange(parameters); return cmd.ExecuteNonQuery(); } } } public static object ExecuteScalar(string sql, params SqlParameter[] parameters) { using (SqlConnection conn = new SqlConnection(connstr)) { conn.Open(); using (SqlCommand cmd = conn.CreateCommand()) { cmd.CommandText = sql; cmd.Parameters.AddRange(parameters); return cmd.ExecuteScalar(); } } } public static DataTable ExecuteDataTable(string sql, params SqlParameter[] parameters) { using (SqlConnection conn = new SqlConnection(connstr)) { conn.Open(); using (SqlCommand cmd = conn.CreateCommand()) { cmd.CommandText = sql; cmd.Parameters.AddRange(parameters); DataSet dataset = new DataSet(); SqlDataAdapter adapter = new SqlDataAdapter(cmd); adapter.Fill(dataset); return dataset.Tables[0]; } } } public static object ToDBValue(this object value) { return value == null ? DBNull.Value : value; } public static object FromDBValue(this object dbValue) { return dbValue == DBNull.Value ? null : dbValue; } } }

<?xml version="1.0" encoding="utf-8"?>  <configuration> <system.web> <compilation debug="true" targetFramework="4.0" /> </system.web> <connectionStrings> <add name="connstr" connectionString="Data Source=.;Initial Catalog=LiuYanBanDB;User Id=sa;Password=123456;"/> </connectionStrings> </configuration>

using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Data; namespace WebApplication1 { /// <summary> /// ViewMsg 的摘要说明 /// </summary> public class ViewMsg : IHttpHandler { public void ProcessRequest(HttpContext context) { context.Response.ContentType = "text/html"; DataTable dt = SqlHelper.ExecuteDataTable("select * from T_Msgs"); var data = new { Title = "查看所有留言", Msgs = dt.Rows }; string html = CommonHelper.ReaderHtml("ViewMsg.htm", data); context.Response.Write(html); } public bool IsReusable { get { return false; } } } }

using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Data.SqlClient; namespace WebApplication1 { /// <summary> /// PostMsg 的摘要说明 /// </summary> public class PostMsg : IHttpHandler { public void ProcessRequest(HttpContext context) { context.Response.ContentType = "text/html"; string save = context.Request["Save"]; if (string.IsNullOrEmpty(save)) { var data = new { Title = "超级留言板" }; string html = CommonHelper.ReaderHtml("PostMsg.htm", data); context.Response.Write(html); } else { string title = context.Request["Title"]; string msg = context.Request["Msg"]; string nickName = context.Request["NickName"]; bool isAnonymous = context.Request["IsAnonymous"]=="on"; string ipAddress = context.Request.UserHostAddress; SqlHelper.ExecuteNonQuery("insert into T_Msgs(Title,Msg,NickName,IsAnonymous,IPAddress,PostDate) values(@Title,@Msg,@NickName,@IsAnonymous,@IPAddress,GetDate())", new SqlParameter("@Title", title), new SqlParameter("@Msg", msg), new SqlParameter("@NickName", nickName), new SqlParameter("@IsAnonymous", isAnonymous), new SqlParameter("@IPAddress", ipAddress)); context.Response.Redirect("ViewMsg.ashx"); } } public bool IsReusable { get { return false; } } } }

using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Data.SqlClient; namespace WebApplication1 { /// <summary> /// DelMsg 的摘要说明 /// </summary> public class DelMsg : IHttpHandler { public void ProcessRequest(HttpContext context) { context.Response.ContentType = "text/html"; long id=Convert.ToInt64(context.Request["Id"]); SqlHelper.ExecuteNonQuery("delete from T_Msgs where Id=@Id", new SqlParameter("@Id", id)); context.Response.Redirect("ViewMsg.ashx"); } public bool IsReusable { get { return false; } } } }

#parse("Head.htm") <form action="PostMsg.ashx" method="post"> <table> <tr> <td> 昵称：<input type="text" name="NickName" /><input type="checkbox" name="IsAnonymous" id="IsAnonymous" /><label for="IsAnonymous">匿名</label> </td> </tr> <tr> <td> 标题：<input type="text" name="Title" /> </td> </tr> <tr> <td> 正文：<textarea name="Msg" cols="50" rows="5"></textarea> </td> </tr> <tr> <td> <input type="submit" name="Save" value="发表" /> </td> </tr> </table> </form> #parse("Foot.htm")

#parse("Head.htm") <a href="PostMsg.ashx">发表留言</a> <ol> #foreach($Msg in $Data.Msgs) <li> <table> <tr><td>昵称：</td><td>$Msg.NickName</td></tr> <tr><td>发表日期：</td><td>$Msg.PostDate</td></tr> <tr><td>IP地址：</td><td>$Msg.IPAddress</td></tr> <tr><td colspan="2">$Msg.Title</td></tr> <tr><td colspan="2">$Msg.Msg</td></tr> <tr><td><a href="DelMsg.ashx?Id=$Msg.Id">删除</a></td></tr> </table> </li> #end </ol> #parse("Foot.htm")

04-人员增删改查

相关推荐