一般处理程序对数据库的怎删改查操作
先建立一个一般处理程序，ListHandler.ashx，然后将数据库中的数据读取出来，并在网页中显示 代码如下：
<%@ WebHandler Language="C#" Class="ListHandler" %> using System; using System.Web; using System.Configuration; using System.Data.SqlClient; using System.Text; public class ListHandler : IHttpHandler { public void ProcessRequest (HttpContext context) { context.Response.ContentType = "text/html"; //context.Response.Write("Hello World"); StringBuilder sb = new StringBuilder();//拼接字符串 sb.Append("<html><head></head><body><a href='addDate.html'>增加站点信息</a>");//APPend()要成对出现 //下面拼接表头 sb.Append("<table><tr><th>Id</th> <th>站名</th> <th>站号</th> <th>登陆用户名</th> <th>登陆密码</th><th>操 作</th></tr>"); string str= ConfigurationManager.ConnectionStrings["BjQx"].ConnectionString; using (SqlConnection conn=new SqlConnection (str)) { string sql = "select * from Tbl_ZhanMing"; using (SqlCommand cmd=new SqlCommand (sql,conn)) { conn.Open(); using (var reader=cmd.ExecuteReader()) { while (reader.Read()) { //拼接字符串 sb.AppendFormat("<tr><td>{0}</td><td>{1}</td><td>{2}</td><td>{3}</td><td>{4}</td>"+ "<td><a href='DetailHandler.ashx?Id={0}' target=_blank>详情</a> "+ "<a onclick='return confirm(\"是否删除？\")' href='Delete.ashx?Id={0}' >删除</a> "+ " <a onclick='return confirm(\"是否修改\")' href='EditShow.ashx?Id={0}&action=show'>修改</a></td></tr>", reader.GetInt32(0), reader["站名"], reader["站号"], reader["登陆用户名"], reader["登陆密码"]); } } } } sb.Append("</table>"); sb.Append("</body></html>"); //把上面拼接的字符串写入网页中 context.Response.Write(sb.ToString());//一般要写为sb.ToString() } public bool IsReusable { get { return false; } } }
点击 “详情”时，则跳转到'DetailHandler.ashx页面，该页面显示出所点击的站点信息，代码如下：
<%@ WebHandler Language="C#" Class="DetailHandler" %> using System; using System.Web; using System.Configuration; using System.Data.SqlClient; using System.Text; using System.Data; using System.IO; public class DetailHandler : IHttpHandler { public void ProcessRequest (HttpContext context) { context.Response.ContentType = "text/html"; //context.Response.Write("Hello World"); StringBuilder sb = new StringBuilder(); //先接收点击“详情”发过来的信息 string Id = context.Request.QueryString["Id"]; int ZId = int.Parse(Id); //通过ID号，连接数据库并将内容显示在该网页中 用SQLDataAdapter string strLink = ConfigurationManager.ConnectionStrings["BjQx"].ConnectionString; string sql = "select * from Tbl_ZhanMing where Id=@Id"; using (SqlDataAdapter adapter = new SqlDataAdapter(sql, strLink)) { //传递参数过来 adapter.SelectCommand.Parameters.Add("@Id", ZId); //创建一个表格对象 DataTable dt = new DataTable(); adapter.Fill(dt);//在网页中填充表格 //下面进行填充表格 sb.AppendFormat("<tr><td>Id:</td><td>{0}</td></tr>", dt.Rows[0]["Id"]); sb.AppendFormat("<tr><td>站名:</td><td>{0}</td></tr>", dt.Rows[0]["站名"]); sb.AppendFormat("<tr><td>站号:</td><td>{0}</td></tr>", dt.Rows[0]["站号"]); sb.AppendFormat("<tr><td>登陆用户名:</td><td>{0}</td></tr>", dt.Rows[0]["登陆用户名"]); sb.AppendFormat("<tr><td>登陆密码:</td><td>{0}</td></tr>", dt.Rows[0]["登陆密码"]); } //写一个HTML模板 string path = context.Request.MapPath("/showresult.html"); string texttemp = File.ReadAllText(path);//将整个html网页赋值给他 string result = texttemp.Replace("@NBBiJie", sb.ToString()); context.Response.Write(result); } public bool IsReusable { get { return false; } } }
当点击“增加站点信息”按钮时，则跳转到addDate.html 页面，在该HTML表单里写入<form id="frmAdd" action="AddDateHandle.ashx">，提交表单时则跳转到
AddDateHandle.ashx页。
addDate.html 页面代码如下：
<!DOCTYPE html> <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8"/> <title></title> <meta charset="utf-8" /> </head> <body> <h1>请增加如下内容：</h1> <form id="frmAdd" action="AddDateHandle.ashx"> <table> <tr> <td>站名：</td> <td> <input type="text" name="zhanming" /> </td> </tr> <tr> <td>站号：</td> <td> <input type="text" name="zhanhao" /> </td> </tr> <tr> <td>登陆用户名：</td> <td> <input type="text" name="username"/> </td> </tr> <tr> <td>登录密码：</td> <td> <input type="text" name="password" /> </td> </tr> <tr> <td> <input type="submit" value="提交" /> </td> <td> <input type="reset" value="重置" /> </td> </tr> </table> </form> </body> </html>
AddDateHandle.ashx一般处理程序页面代码如下：
<%@ WebHandler Language="C#" Class="AddDateHandle" %> using System; using System.Web; using System.Configuration; using System.Data.SqlClient; public class AddDateHandle : IHttpHandler { public void ProcessRequest (HttpContext context) { context.Response.ContentType = "text/html"; //context.Response.Write("Hello World"); //拿到addDate.html上交来的数据 string zhanming = context.Request["zhanming"]; int zhanhao =int.Parse(context.Request["zhanhao"]); string username = context.Request["username"]; string passsword = context.Request["password"]; //接下来做数据库插入操作 string str = ConfigurationManager.ConnectionStrings["BJQx"].ConnectionString; using (SqlConnection conn=new SqlConnection (str)) { string sql = string.Format("insert into Tbl_ZhanMing (站名,站号,登陆用户名,登陆密码) values ('{0}',{1},'{2}','{3}') select * from Tbl_ZhanMing",zhanming,zhanhao,username,passsword); using (SqlCommand cmd=new SqlCommand (sql,conn)) { conn.Open(); int r= cmd.ExecuteNonQuery(); } } context.Response.Redirect("ListHandler.ashx"); } public bool IsReusable { get { return false; } } }
以上代码完成了对数据的增加，全部显示，和显示所点击那一条信息的详情的操作。
当在ListHandler.ashx所显示的页面上，点击“删除”按钮时，则跳转到Delete.ashx页。此步骤完成对数据库表中的数据进行删除操作。该页面的代码如下：
<%@ WebHandler Language="C#" Class="Delete" %> using System; using System.Web; using System.Configuration; using System.Data.SqlClient; public class Delete : IHttpHandler { public void ProcessRequest (HttpContext context) { context.Response.ContentType = "text/plain"; //context.Response.Write("Hello World"); //获取提交删除的Id string Id= context.Request["Id"]; int delId = int.Parse(Id); //连接数据库进行删除操作 string constr = ConfigurationManager.ConnectionStrings["BJQx"].ConnectionString; using (SqlConnection conn=new SqlConnection (constr)) { string sql = "delete from Tbl_ZhanMing where Id=@Id"; using (SqlCommand cmd=new SqlCommand (sql,conn)) { conn.Open(); cmd.Parameters.Add(new SqlParameter("@Id", delId)); int rows = cmd.ExecuteNonQuery(); if (rows>0) { //删除成功 context.Response.Redirect("ListHandler.ashx"); } else { context.Response.Write("删除失败！"); } } } } public bool IsReusable { get { return false; } } }
一下代码对数据库列表进行修改操作，当点击“修改”按钮时，则跳转到EditShow.ashx，代码如下：
<%@ WebHandler Language="C#" Class="Alter" %> using System; using System.Web; using System.Configuration; using System.Data.SqlClient; using System.Data; using System.IO; public class Alter : IHttpHandler { public void ProcessRequest (HttpContext context) { context.Response.ContentType = "text/html";// 切记 此处要改为html，否则就把源码输出来了！ //用于显示修改数据的 if (context.Request["action"] == "show") { //先拿到需要修改的那一行的Id int altId = int.Parse(context.Request["Id"]); //建立数据库连接 填充网页一般用SqlDataAdapter string str = ConfigurationManager.ConnectionStrings["BjQx"].ConnectionString; string sql = "select *from Tbl_ZhanMing where Id=@Id"; using (SqlDataAdapter adapter = new SqlDataAdapter(sql, str)) { //将ID传递过来 adapter.SelectCommand.Parameters.Add("@Id", altId); //填充表格 DataTable dt = new DataTable();//创建一个表格对象 adapter.Fill(dt); //直接在后台拼接字符串太麻烦，所以写一个模板 EditTemp.html string strResult = File.ReadAllText(context.Request.MapPath("EditTemp.html")); //File.ReadAllText()需要的是一个绝对路径 strResult = strResult.Replace("@txtZhanMing", dt.Rows[0]["站名"].ToString());//第一行站名这一列全部显示出来 strResult = strResult.Replace("@txtZhanHao", dt.Rows[0]["站号"].ToString()); strResult = strResult.Replace("@txtUserName", dt.Rows[0]["登陆用户名"].ToString()); strResult = strResult.Replace("@txtPassWord", dt.Rows[0]["登陆密码"].ToString()); strResult = strResult.Replace("@Id", dt.Rows[0]["Id"].ToString()); context.Response.Write(strResult); } } } public bool IsReusable { get { return false; } } }
EditShow.ashx需要一个html模板EditTemp.html，代码如下：
<!DOCTYPE html> <html> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8"/> <title></title> <meta charset="utf-8" /> </head> <body> <form method="post" action="EditProcess.ashx"> <input type="hidden" name="hidId" value="@Id" /> <table> <tr> <td>站名：</td> <td> <input type="text" name="txtZhanMing" value="@txtZhanMing"/> </td> </tr> <tr> <td>站号：</td> <td> <input type="text" name="txtZhanHao" value="@txtZhanHao" /> </td> </tr> <tr> <td>登陆用户名：</td> <td> <input type="text" name="txtUserName" value="@txtUserName"/> </td> </tr> <tr> <td>登录密码：</td> <td> <input type="text" name="txtPassWord" value="@txtPassWord" /> </td> </tr> <tr> <td colspan="2"> <input type="submit" value="修改" /> </td> </tr> </table> </form> </body> </html>
在EditTemp.html页面上，当点击“修改”按钮时，跳转到EditProcess.ashx,则可以直接对所选中的数据库列表进行修改操作。代码如下：
<%@ WebHandler Language="C#" Class="EditProcess" %> using System; using System.Web; using System.Configuration; using System.Data.SqlClient; public class EditProcess : IHttpHandler { public void ProcessRequest (HttpContext context) { context.Response.ContentType = "text/html"; //用于修改数据的 //先拿到要修改的这一行的信息，不知道Id,因此只能单独取，我们知道name的值 string txtZhanMing = context.Request["txtZhanMing"]; int txtZhanHao = int.Parse(context.Request["txtZhanHao"]); string txtUserName = context.Request["txtUserName"]; string txtPassWord = context.Request["txtPassWord"]; int editId = int.Parse(context.Request["hidId"]);//通过网页的名字来取ID //建立数据库连接 string str = ConfigurationManager.ConnectionStrings["BjQx"].ConnectionString; using (SqlConnection conn=new SqlConnection (str)) { string sql = "update Tbl_ZhanMing set 站名=@txtZhanMing,站号=@txtZhanHao,登陆用户名=@txtUserName,登陆密码=@txtPassWord where Id=@Id"; using (SqlCommand cmd=new SqlCommand(sql,conn)) { //将sql语句的参数全部给其赋值 cmd.Parameters.Add(new SqlParameter("@Id", editId)); cmd.Parameters.Add(new SqlParameter("@txtZhanMing", txtZhanMing)); cmd.Parameters.Add(new SqlParameter("@txtZhanHao", txtZhanHao)); cmd.Parameters.Add(new SqlParameter("@txtUserName", txtUserName)); cmd.Parameters.Add(new SqlParameter("@txtPassWord", txtPassWord)); conn.Open(); int r = cmd.ExecuteNonQuery(); if (r>0) { context.Response.Redirect("ListHandler.ashx"); } else { context.Response.Write("修改失败！"); } } } } public bool IsReusable { get { return false; } } }
以上代码实现了对数据库的增删改查操作。
