04-人员增删改查

阅读量：7305 次

发布时间：2019-06-30

本文共 19071 字，大约阅读时间需要 63 分钟。

案例：个人信息管理。

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{    ///     /// PersonList 的摘要说明    /// 
    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{    ///     /// PersonEdit 的摘要说明    /// 
    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;            }        }    }}

View Code

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    {        ///         /// 用data数据填充templateName模板，渲染生成html返回        /// 
        ///         ///         /// 
              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;        }    }}

View Code

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;        }    }}

View Code

    人员列表新增人员
      
                #foreach($person in $Data)        
        #end    
                                    编辑          删除          姓名          年龄          邮箱         
           
                            编辑          删除          $person.Name          $person.Age          $person.Email

编辑	删除	姓名	年龄	邮箱
编辑	删除	$person.Name	$person.Age	$person.Email

View Code

            #if($Data.Action="AddNew")        新增用户        #else        编辑用户$Data.Person.Name        #end    
          
           
           
             
       
                                   姓名：                    
                           年龄：                    
                           邮箱：                    
                           班级：

View Code

案例：留言板

l 能够发表留言：标题、内容（多行普通文本）、昵称、是否匿名

l 展示留言列表（标题、内容、昵称、IP地址、日期）

l 发表留言界面和留言列表界面的头体是统一的。

l （*）深入：增加留言的分页。SQLServer2005后增加了

Row_Number函数简化实现。

l 限制结果集。返回第3行到第5行的数据（ ROW_NUMBER 不能用在

where子句中，所以将带行号的执行结果作为子查询，就可以将结果当

成表一样用了）：

• select * from

• (select *,row_number() over (order by Id asc) as num from student) as s

• where s.num between 3 and 5

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;        }    }}

View Code

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;        }    }}

View Code

using System;using System.Collections.Generic;using System.Linq;using System.Web;using System.Data;namespace WebApplication1{    ///     /// ViewMsg 的摘要说明    /// 
    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;            }        }    }}

View Code

using System;using System.Collections.Generic;using System.Linq;using System.Web;using System.Data.SqlClient;namespace WebApplication1{    ///     /// PostMsg 的摘要说明    /// 
    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;            }        }    }}

View Code

using System;using System.Collections.Generic;using System.Linq;using System.Web;using System.Data.SqlClient;namespace WebApplication1{    ///     /// DelMsg 的摘要说明    /// 
    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;            }        }    }}

View Code

关于我们|联系我们|版权声明|招贤纳士|公司地址

View Code

$Data.Title

超级留言板

View Code

#parse("Head.htm")
      
       
                                                   昵称：匿名                     
                                           标题：                     
                                           正文：                     
                                                                
        
       
      
#parse("Foot.htm")

View Code

#parse("Head.htm")发表留言
      
       #foreach($Msg in $Data.Msgs)
       
        
                              昵称：            $Msg.NickName           
                     发表日期：            $Msg.PostDate           
                     IP地址：            $Msg.IPAddress           
                     $Msg.Title           
                     $Msg.Msg           
                     删除           
         
        
#end
      
#parse("Foot.htm")