NVelocity 实现简单的 CIUD

1, NVelocity 是 一般处理程序.ashx 和 前台页面模板的桥梁。 

2，我们现在建立一个简单的查询：  

    A，新建项目，把NVelocity.dll拉入项目中，并添加对其引用

　　

　　B，新建Common_Nvelocity.cs类， 借助NVelocity模板引擎把数据Data传递给对应的TemplateName页面

using Commons.Collections;
using NVelocity;
using NVelocity.App;
using NVelocity.Runtime;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Web;

namespace CIUD_NVelocitySample
{
    public class Common_Nvelocity
    {

        /// <summary>
        /// 通过NVelocity模板引擎，把Data数据参数中的数据传递到Template模板中
        /// </summary>
        /// <param name="TemplateName">模板页面名称</param>
        /// <param name="Data">参数数据</param>
        /// <returns>返回HTML直接渲染生成 页面</returns>
        public static string Template_Nvelocity(string TemplateName, object Data)
        {

            //创建NVlecocity模板引擎的实例对象
            VelocityEngine vlEngine = new VelocityEngine();

            //初始化实例对象，定义对象的部分属性
            ExtendedProperties props = new ExtendedProperties();
            props.AddProperty(RuntimeConstants.RESOURCE_LOADER, "file");//声明模板是在file中
            props.AddProperty(RuntimeConstants.FILE_RESOURCE_LOADER_PATH, System.Web.Hosting.HostingEnvironment.MapPath("~/Templates"));  //指定模板文件所在文件夹
            vlEngine.Init(props);

            //替换模板中的数据
            VelocityContext vltContext = new VelocityContext();
            vltContext.Put("data", Data);  //设置参数，在HTML模板中可以通过$Data读取数据


            //从文件中读取模板
            Template VltTemp = vlEngine.GetTemplate(TemplateName);
            //合并模板
            StringWriter writer = new StringWriter();
            VltTemp.Merge(vltContext, writer);

            //转化为字符串
            string html = writer.GetStringBuilder().ToString();

            return html;
        }
    }
}
View Code

　　C，新建SQLHelper.cs类，从数据库中读写数据：

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


namespace CIUD_NVelocitySample
{

    public class SQLHelper
    {
        SqlConnection sqlConn = null;
        SqlCommand sqlCom = null;
        SqlDataReader sdr = null;
        public SQLHelper()
        {

        }
        private SqlConnection getConn()
        {
            string connStr = ConfigurationManager.ConnectionStrings["connString"].ConnectionString;
            if (sqlConn == null)
            {
                sqlConn = new SqlConnection(connStr);
                sqlConn.Open();

            }
            if (sqlConn.State == ConnectionState.Closed)
            {
                sqlConn.Open();
            }
            return sqlConn;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="strSql"></param>
        /// <param name="ct"></param>
        /// <returns></returns>
        public int ExecuteNonQuery(string strSql, CommandType ct)
        {
            int res = 0;
            try
            {
                sqlCom = new SqlCommand(strSql, getConn());
                sqlCom.CommandType = ct;
                res = (int)sqlCom.ExecuteNonQuery();

            }
            catch (Exception ex)
            {

                throw ex;
            }
            finally
            {
                if (sqlConn.State == ConnectionState.Open)
                {
                    sqlConn.Close();
                }
            }
            return res;
        }
        public int ExecuteNonQuery(string strSql, SqlParameter[] sqlPara, CommandType ct)
        {

            int res = 0;
            try
            {
                sqlCom = new SqlCommand(strSql, getConn());
                sqlCom.CommandType = ct;
                sqlCom.Parameters.AddRange(sqlPara);
                res = (int)sqlCom.ExecuteNonQuery();
                sqlCom.Parameters.Clear();
            }
            catch (Exception ex)
            {

                throw ex;
            }
            finally
            {
                if (sqlConn.State == ConnectionState.Open)
                {
                    sqlConn.Close();
                }
            }
            return res;
        }
        public object ExecuteScalar(string strSql, CommandType ct)
        {
            object obj;
            try
            {
                sqlCom = new SqlCommand(strSql, getConn());
                sqlCom.CommandType = ct;
                obj = sqlCom.ExecuteScalar();
            }
            catch (Exception ex)
            {

                throw ex;
            }
            finally
            {
                if (sqlConn.State == ConnectionState.Open)
                {
                    sqlConn.Close();
                }
            }
            return obj;
        }
        public object ExecuteScalar(string strSql, SqlParameter[] sqlPara, CommandType ct)
        {
            object obj;
            try
            {
                sqlCom = new SqlCommand(strSql, getConn());
                sqlCom.CommandType = ct;
                sqlCom.Parameters.AddRange(sqlPara);
                obj = sqlCom.ExecuteScalar();
                sqlCom.Parameters.Clear();
            }
            catch (Exception ex)
            {

                throw ex;
            }
            finally
            {
                if (sqlConn.State == ConnectionState.Open)
                {
                    sqlConn.Close();
                }
            }
            return obj;
        }
        public SqlDataReader ExecuteReader(string strSql, CommandType ct)
        {

            try
            {
                sqlCom = new SqlCommand(strSql, getConn());
                sqlCom.CommandType = ct;
                using (sdr = sqlCom.ExecuteReader(CommandBehavior.CloseConnection)) { }//释放sdr所占的资源
            }
            catch (Exception ex)
            {

                throw ex;
            }
            finally
            {
                if (sqlConn.State == ConnectionState.Open)
                {
                    sqlConn.Close();
                }
            }
            return sdr;
        }
        public SqlDataReader ExecuteReader(string strSql, SqlParameter[] sqlPara, CommandType ct)
        {

            try
            {
                sqlCom = new SqlCommand(strSql, getConn());
                sqlCom.CommandType = ct;
                sqlCom.Parameters.AddRange(sqlPara);
                using (sdr = sqlCom.ExecuteReader(CommandBehavior.CloseConnection)) { }
                sqlCom.Parameters.Clear();
            }
            catch (Exception ex)
            {
                throw ex;

            }
            finally
            {
                if (sqlConn.State == ConnectionState.Open)
                {
                    sqlConn.Close();
                }
            }
            return sdr;
        }
        public DataTable ExecuteQuery(string strSql, CommandType ct)
        {
            DataTable dt;
            try
            {
                dt = new DataTable();
                sqlCom = new SqlCommand(strSql, getConn());
                sqlCom.CommandType = ct;
                using (sdr = sqlCom.ExecuteReader(CommandBehavior.CloseConnection)) { dt.Load(sdr); }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (sqlConn.State == ConnectionState.Open)
                {
                    sqlConn.Close();
                }
            }
            return dt;
        }
        public DataTable ExecuteQuery(string strSql, SqlParameter[] sqlPara, CommandType ct)
        {
            DataTable dt;
            try
            {
                dt = new DataTable();
                sqlCom = new SqlCommand(strSql, getConn());
                sqlCom.CommandType = ct;
                sqlCom.Parameters.AddRange(sqlPara);
                using (sdr = sqlCom.ExecuteReader(CommandBehavior.CloseConnection))
                { dt.Load(sdr); }
                sqlCom.Parameters.Clear();

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (sqlConn.State == ConnectionState.Open)
                {
                    sqlConn.Close();
                }
            }
            return dt;
        }
    }
}
View Code

　　D，数据库中新建NVelocity_Sample数据库添加表T_Person，自己添加几条测试数据：

USE [NVelocity_Sample]
GO

/****** Object:  Table [dbo].[T_Person]    Script Date: 2015/5/13 22:07:39 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[T_Person](
    [ID] [decimal](18, 0) IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](50) NOT NULL,
    [Email] [nvarchar](250) NOT NULL,
 CONSTRAINT [PK_T_Person] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
View Code

　　E. WebConfig添加连接字符串：

<?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="connString" connectionString="Data Source=.; Initial Catalog=NVelocity_Sample; User Id=sa; Password=123456"/>
  </connectionStrings>
</configuration>
View Code

　　以上完成基本设置，下面我们 添加.ashx和html模板页面

　　F， 添加PersonList.ashx 一般处理程序，从数据库中读取数据

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

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

        public void ProcessRequest(HttpContext context)
        {
            context.Response.ContentType = "text/html";

            //调用SQLHelper查询数据
            SQLHelper sqlH = new SQLHelper();
            DataTable dt = sqlH.ExecuteQuery("SELECT  [ID],[Name],[Email] FROM [NVelocity_Sample].[dbo].[T_Person]", CommandType.Text);  //读取T_Person中的数据

            //Rows属性，代表表格中数据行的集合(DataRow集合)
            //传递DataRowCollection给模板，方便遍历
            string strHtml = Common_Nvelocity.Template_Nvelocity("PersonList.html", dt.Rows);

            context.Response.Write(strHtml);
        }

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

　　G， 添加Temple文件夹，并在文件夹中添加PersonList.html模板文件

<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    <title>人员列表</title>
</head>
<body>
    <ul>
        #foreach($d in $data)
        <li>姓名：$d.Name &nbsp; 邮箱：$d.email</li>
        #end
    </ul>
</body>
</html>
View Code

　　H, 运行程序后显示如下：

　　

　　以上完成查询功能！

 3，实现Insert功能， 我们直接在PersonAdd页面的中添加文本框实现 Insert 功能

　　A.在PersonAdd.html中添加 Form用于提交数据

<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    <title></title>
</head>
<body>
    <form action="PersonAdd.ashx" method="get">
        姓名：
        <input type="text" name="PersonName" />
        <br />
        邮箱：<input type="email" name="Email" />
        <input type="submit" value="新增" />
    </form>
    <ul>
        #foreach($d in $data)
        <li>姓名：$d.Name &nbsp; 邮箱：$d.email&nbsp; <a href="PersonEdit.ashx?ID=$d.ID&Name=$d.Name&Email=$d.email">编辑</a></li>
        #end
    </ul>
</body>
</html>
View Code

　　B. 点击Button提交数据以后，把Form表单中的数据提交到PersonAdd.ashx页面，如果文本框中的内容不为空则写入数据库

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

namespace CIUD_NVelocitySample
{
    /// <summary>
    /// PersonAdd 的摘要说明
    /// </summary>
    public class PersonAdd : IHttpHandler
    {

        public void ProcessRequest(HttpContext context)
        {
            context.Response.ContentType = "text/html";
            string strPersonName = context.Request.QueryString["PersonName"];
            string strEmail = context.Request.QueryString["Email"];
            SQLHelper sqlH = new SQLHelper();
            if (string.IsNullOrEmpty(strPersonName) == false && string.IsNullOrEmpty(strEmail) == false)
            {
                int numSucc = 0;
                //Insert
                numSucc = sqlH.ExecuteNonQuery("insert into [NVelocity_Sample].[dbo].[T_Person]([Name],[Email]) values('" + strPersonName + "','" + strEmail + "')", CommandType.Text);
            }


            //写入数据之后再读取出来，传递到模板页面
            //调用SQLHelper查询数据
            DataTable dt = sqlH.ExecuteQuery("SELECT  [ID],[Name],[Email] FROM [NVelocity_Sample].[dbo].[T_Person]", CommandType.Text);  //读取T_Person中的数据
            //Rows属性，代表表格中数据行的集合(DataRow集合)
            //传递DataRowCollection给模板，方便遍历
            string strHtml = Common_Nvelocity.Template_Nvelocity("PersonAdd.html", dt.Rows);


            context.Response.Write(strHtml);

        }

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

　　实现效果：　　

　　

4，实现Edit编辑功能，我们添加对应的PersonEdit.html和PersonEdit.ashx页面  来处理编辑。

　　PersonAdd页面中显示列表的后面添加 “编辑” 超链接把数据传递到 PersonEdit.ashx页面中进行编辑处理：

　　A. PersonAdd.html 的PersonList后添加 “编辑” 超链接：

<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    <title></title>
</head>
<body>
    <form action="PersonAdd.ashx" method="get">
        姓名：
        <input type="text" name="PersonName" />
        <br />
        邮箱：<input type="email" name="Email" />
        <input type="submit" value="新增" />
    </form>
    <ul>
        #foreach($d in $data)
        <li>姓名：$d.Name &nbsp; 邮箱：$d.email&nbsp; <a href="PersonEdit.ashx?ID=$d.ID&Name=$d.Name&Email=$d.email">编辑</a></li>
        #end
    </ul>
</body>
</html>
View Code

　　B.新增 PersonEdit.html模板页面和PersonEdit.ashx一般处理程序页面

<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    <title></title>
</head>
<body>
    <form action="PersonEdit.ashx" method="get">
        序号：<input type="text" name="ID" value="$data.ID" />
        姓名：
        <input type="text" name="Name" value="$data.Name" />
        <br />
        邮箱：<input type="email" name="Email" value="$data.Email" />
        <br />
        <input type="submit" value="保存" />
    </form>
</body>
</html>
View Code
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;

namespace CIUD_NVelocitySample
{
    /// <summary>
    /// PersonEdit 的摘要说明
    /// </summary>
    public class PersonEdit : IHttpHandler
    {

        public void ProcessRequest(HttpContext context)
        {
            context.Response.ContentType = "text/html";
            string strID = context.Request.QueryString["ID"];
            string strPersonName = context.Request.QueryString["Name"];
            string strEmail = context.Request.QueryString["Email"];
            string strFromPage = context.Request.UrlReferrer.ToString();
            SQLHelper sqlH = new SQLHelper();
            string strHtml = "";

            //判断URL参数来源 哪个页面
            //如果来自： PersonAdd.ashx 页面，则是点击了“编辑”进入编辑页面，此时需要  显示页面内容
            if (strFromPage.ToLower().Contains("personadd.ashx"))
            {
                var Data = new { ID = strID, Name = strPersonName, Email = strEmail };
                strHtml = Common_Nvelocity.Template_Nvelocity("PersonEdit.html", Data);
            }
            //如果来自： PersonEdit.html页面，则是点击 PersonEdit.html 页面的保存按钮，此时需要把数据更新到数据库后，返回到PersonAdd.html
            else if (strFromPage.ToLower().Contains("personedit.ashx"))
            {
                if (string.IsNullOrEmpty(strPersonName) == false && string.IsNullOrEmpty(strEmail) == false)
                {
                    int numSucc = 0;
                    //Insert
                    numSucc = sqlH.ExecuteNonQuery("  update [NVelocity_Sample].[dbo].[T_Person] set [Name]='" + strPersonName + "', [Email]='" + strEmail + "' where [ID]='" + strID + "'", CommandType.Text);
                    if (numSucc == 1)
                    {
                       //更新成功，跳转到 PersonAdd.ashx页面
                        context.Response.Redirect("personadd.ashx");
                    }
                    else
                    {   //加载失败，依旧显示 PersonEdit.html页面
                        var Data = new { ID = strID, Name = strPersonName, Email = strEmail };
                        strHtml = Common_Nvelocity.Template_Nvelocity("PersonEdit.html", Data);
                    }

                }
            }

            context.Response.Write(strHtml);
        }

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

　　C. 以上两个Edit页面实现了：我们点击编辑的时候，把通过URL传值把 ID,Name,Email属性参数传递到 PersonEdit.ashx页面中

　　　①PersonEdit.ashx显示传递过来的信息 ②对PersonEdit.ashx中的信息进行修改并提交  ③修改完成后跳转到PersonAdd.ashx页面显示PersonList列表

-----------------------------------------------GIF图片演示效果------------------------------------------------

 5， 实现Delete功能： ①PersonAdd.html中添加"删除"超链接 ②新建PersonDelete.ashx处理删除动作  ②点击 “删除”，通过URL把对应的ID传值到PersonDelete.ashx进行删除

　　PersonAdd.html 和 PersonDelete.ashx:

<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
    <title></title>
</head>
<body>
    <form action="PersonAdd.ashx" method="get">
        姓名：
        <input type="text" name="PersonName" />
        <br />
        邮箱：<input type="email" name="Email" />
        <input type="submit" value="新增" />
    </form>
    <ul>
        #foreach($d in $data)
        <li>姓名：$d.Name &nbsp; 邮箱：$d.email&nbsp; <a href="PersonEdit.ashx?ID=$d.ID&Name=$d.Name&Email=$d.email">编辑</a>&nbsp;&nbsp; <a href="PersonDelete.ashx?ID=$d.ID">删除</a></li>
        #end
    </ul>
</body>
</html>
View Code
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;

namespace CIUD_NVelocitySample
{
    /// <summary>
    /// PersonDelete 的摘要说明
    /// </summary>
    public class PersonDelete : IHttpHandler
    {

        public void ProcessRequest(HttpContext context)
        {
            context.Response.ContentType = "text/html";

            //实现Person的删除功能能后，跳转到 PersonAdd.ashx
            string strID = context.Request.QueryString["ID"];

            string sqlDel = "delete from [NVelocity_Sample].[dbo].[T_Person] where [ID]='" + strID + "' ";
            SQLHelper sqlH = new SQLHelper();
            int numSucc = sqlH.ExecuteNonQuery(sqlDel, CommandType.Text);

            if (numSucc == 1)
            {
                //删除成功，返回页面
                context.Response.Redirect("PersonAdd.ashx");
            }
            else
            {
                //输出删除失败
                context.Response.Write("删除失败");
            }
        }

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

-----------------------------------------------GIF图片演示效果------------------------------------------------

以上完成了简单的CIUD功能。

6，添加City表，T_Perons中新增字段：CityID

USE [NVelocity_Sample]
GO

/****** Object:  Table [dbo].[T_LiveCity]    Script Date: 2015/5/15 14:51:21 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[T_LiveCity](
    [ID] [decimal](18, 0) IDENTITY(1,1) NOT NULL,
    [CityName] [nvarchar](50) NOT NULL
) ON [PRIMARY]

GO
View Code

     

7，CIUD页面中 添加对应的居住城市：

Insert页面：PersonAdd.ashx

Update页面：PersonEdit.ashx

添加下拉框以后的完整代码下载： Demo Download

　　

坚持，菜鸟终有一天变大牛！
【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/chengzish/p/4501838.html