04-人员增删改查
案例：個(gè)人信息管理。
l 使用NVelocity的開發(fā)方式重寫登錄程序，把NVelocity封裝成
RenderTemplate方法。
l 這種HttpHandler+ NVelocity的方式非常類似于PHP+smarty的開
發(fā)方式，也有利于理解asp.net mvc。HttpHandler就是Controller
，模板就是View， Controller不操作View的內(nèi)部細(xì)節(jié)，只是把數(shù)
據(jù)給View，由View自己控制怎樣顯示。
l 字段：Id、姓名、年齡、個(gè)人網(wǎng)址（可以為空）。
l 列表頁(yè)面和編輯界面：PersonList.aspx、
PersonEdit.aspx?Action=AddNew、
PersonEdit.aspx?Action=Edit&Id=2（在PersonEdit頁(yè)面判斷是
否傳過來了save按鈕來判斷是加載還是保存。渲染頁(yè)面的時(shí)候把
Action和Id保存在隱藏字段中。保存成功后Redirect回List頁(yè)面）
l 進(jìn)一步案例：有關(guān)聯(lián)字段，比如班級(jí)，實(shí)現(xiàn)備注
?
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屬性//代表表格中的數(shù)據(jù)行的集合（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=3string action = context.Request["Action"];if (action == "AddNew"){//判斷是否含有Save并且等于true，如果是的話就說明是點(diǎn)擊【保存】按鈕請(qǐng)求來的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");//保存成功返回列表頁(yè)面 }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");//保存成功返回列表頁(yè)面 }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 + "的數(shù)據(jù)");return;}else if (dt.Rows.Count > 1){context.Response.Write("找到多條Id=" + id + "的數(shù)據(jù)");}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參數(shù)錯(cuò)誤！");}}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數(shù)據(jù)填充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);//設(shè)置參數(shù)，在模板中可以通過$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"?><!--有關(guān)如何配置 ASP.NET 應(yīng)用程序的詳細(xì)消息，請(qǐng)?jiān)L問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>班級(jí)：</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 能夠發(fā)表留言：標(biāo)題、內(nèi)容（多行普通文本）、昵稱、是否匿名
l 展示留言列表（標(biāo)題、內(nèi)容、昵稱、IP地址、日期）
l 發(fā)表留言界面和留言列表界面的頭體是統(tǒng)一的。
l （*）深入：增加留言的分頁(yè)。SQLServer2005后增加了
Row_Number函數(shù)簡(jiǎn)化實(shí)現(xiàn)。
l 限制結(jié)果集。返回第3行到第5行的數(shù)據(jù)（ ROW_NUMBER 不能用在
where子句中，所以將帶行號(hào)的執(zhí)行結(jié)果作為子查詢，就可以將結(jié)果當(dāng)
成表一樣用了）：
? select * from
? (select *,row_number() over (order by Id asc) as num from student) as s
? where s.num between 3 and 5
?
轉(zhuǎn)載于:https://www.cnblogs.com/liuslayer/p/4737964.html
總結(jié)
- 上一篇： django创建一个管理员用户
- 下一篇： JAVA中的break[标签]conti