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

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

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

<?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 name="connstr" connectionString="Data Source=.;Initial Catalog=CRUDTest;User Id=sa;Password=123456;"></add>
  </connectionStrings>
</configuration>      

View Code

<!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>      

View Code

<!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>      

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

<?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 name="connstr" connectionString="Data Source=.;Initial Catalog=LiuYanBanDB;User Id=sa;Password=123456;"/>
  </connectionStrings>
</configuration>      

View Code

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

View Code

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

View Code

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

View Code

<p style="text-align:center">關于我們|聯系我們|版權聲明|招賢納士|公司位址</p>
</body>
</html>      

View Code

<!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>$Data.Title</title>
</head>
<body>
    <p>
        <h1>
            超級留言闆</h1>
    </p>      

View Code

#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")       

View Code

#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")      

View Code