1、先在数据库实创建好表,并写下联合查询语句
select a.Name,a.Address,b.Name from Companys a left join Managers b on a.ManagerId=b.id;
2、如下图:
3、完成公司列表展示的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>
<a href="CompanyAddnewEdit.ashx?action=addnew">新增</a>
<table style="border:1px solid ;margin:100px auto;border-color:blue;">
<thead>
<tr>
<th>公司名称</th><th>公司地址</th><th>公司经理</th>
</tr>
$tables
</thead>
</table>
</body>
</html>
4、完成公司列表展示数据库查询,和进行字符串的拼接
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Web;
namespace Web1.Day3.cps
{
/// <summary>
/// CompanyList 的摘要说明
/// </summary>
public class CompanyList : IHttpHandler
{
public void ProcessRequest(HttpContext context)
{
context.Response.ContentType = "text/html";
string html = CommonHelper.ReadHtml("~/Day3/cps/CompanyList.html");
DataTable dt = SqlHelper.ExecuteQuery
("select a.Name Name,a.Address Address,b.Name MangerName from Companys a left join Managers b on a.ManagerId=b.id;");
StringBuilder sb = new StringBuilder();
//拼接$tables
foreach (DataRow row in dt.Rows)
{
sb.Append("<tr>").Append("<td>").Append(row["Name"]).Append("</td>")
.Append("<td>").Append(row["Address"]).Append("</td>")
.Append("<td>").Append(row["MangerName"]).Append("</td>")
.Append("</tr>");
}
//替换$tables
html = html.Replace("$tables",sb.ToString());
//输出到浏览器
context.Response.Write(html);
}
public bool IsReusable
{
get
{
return false;
}
}
}
}
5、对html页面添加内容
<thead>
<tr>
<th>公司名称</th><th>公司地址</th><th>公司经理</th><th>删除</th><th>编辑</th>
</tr>
$tables
</thead>
6、对一般处理程序,进行“编辑”、“删除”内容的添加。问题:编辑的添加需要根据id值进行,所以这里要在数据库中查出来,公司表的id值。删除当然也要id;(着色为新添加的内容!!!!)
public void ProcessRequest(HttpContext context)
{
context.Response.ContentType = "text/html";
string html = CommonHelper.ReadHtml("~/Day3/cps/CompanyList.html");
DataTable dt = SqlHelper.ExecuteQuery
("select a.id id a.Name Name,a.Address Address,b.Name MangerName from Companys a left join Managers b on a.ManagerId=b.id;");
StringBuilder sb = new StringBuilder();
//拼接$tables
foreach (DataRow row in dt.Rows)
{
sb.Append("<tr>").Append("<td>").Append(row["Name"]).Append("</td>")
.Append("<td>").Append(row["Address"]).Append("</td>")
.Append("<td>").Append(row["MangerName"]).Append("</td>")
.Append("<td><a href='CompanyDelete.ashx?id=").Append(row["id"])
.Append("'>删除</td>")
.Append("<td><a href='CompanyAddnewEdit.ashx?action=edit&id=").Append(row["id"])
.Append("'>编辑</td>")
.Append("</tr>");
}
//替换$tables
html = html.Replace("$tables",sb.ToString());
//输出到浏览器
context.Response.Write(html);
}
7、添加CompanyDelete.ashx一般处理程序
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
namespace Web1.Day3.cps
{
/// <summary>
/// CompanyDelete 的摘要说明
/// </summary>
public class CompanyDelete : IHttpHandler
{
public void ProcessRequest(HttpContext context)
{
context.Response.ContentType = "text/html";
string strId = context.Request["id"];
//做非空检查
if (string.IsNullOrEmpty(strId))
{
CommonHelper.OutputError("id不能为空!");
return;
}
//id的合法性
int id;
if (int.TryParse(strId,out id ))
{
CommonHelper.OutputError("id不是整数");
}
//做完检查,开始执行删除
SqlHelper.ExecuteNonQuery("delete from Companys where id=@id",new SqlParameter("@id",id));
context.Response.Redirect("CompanyList.ashx");
}
public bool IsReusable
{
get
{
return false;
}
}
}
}
8、添加页面的新增和编辑页面处理,这两个公用一个模板页CompanyAddnewEdit.html
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
<title>公司#action</title>
</head>
<body>
<form action="CompanySave.ashx" method="post">
公司名称:<input type="text" name="name" value="#name" />
公司地址:<input type="text" name="address" value="#address" />
公司经理:<select name="managerId">
<!--<option value="llis"></option>-->
#manager
</select>
</form>
</body>
</html>
9、对页面进行处理CompanyAddnewEdit.ashx的一般处理程序;
A) 如何处理“新增”还是“编辑”;用action判读的;
public void ProcessRequest(HttpContext context)
{
context.Response.ContentType = "text/html";
//从url地址栏中读取action
string action=context.Request["action"];
if (action=="addnew")
{
//新增
}
else if (action == "edit")
{
//编辑
}
else
{
//action错误
}
A) 分析,新增不许要进行id的处理,因为id是自动增长的在数据库中。
B) 对于编辑就需要id的非空处理。
C) id的是否为整数的合法性处理。
D) 无论是新增还是删除都是需要 模板页CompanyAddnewEdit.html的读取
E) 调用封装好的类CommentHelper.ReadHtml();进行模板页中的指定值的替换;
10、asp.net引擎就是帮助我们做了html的拼接,如果和你希望的到的结果不一致,第一:想到的就是右键产看网页的源代码;
11、一般处理程序如下:
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Web;
namespace Web1.Day3.cps
{
/// <summary>
/// CompanyAddnewEdit 的摘要说明
/// </summary>
public class CompanyAddnewEdit : IHttpHandler
{
public void ProcessRequest(HttpContext context)
{
context.Response.ContentType = "text/html";
//1、从url地址栏中读取action
string action=context.Request["action"];
//3、无论”编辑“还是”新增“都需要读取模板页
string html = CommonHelper.ReadHtml("~/Day3/cps/CompanyAddnewEdit.html");
//5、查询公司的经理表
DataTable td= SqlHelper.ExecuteQuery("select * from Managers;");
//2、两种情况
if (action=="addnew")
{
//新增
//6、对经理表遍历人员信息进行,字符串的拼接生成<option values="id">雷军</option>
StringBuilder sb = new StringBuilder();
foreach (DataRow row in td.Rows)
{
sb.Append("<option values='").Append(row["id"]).Append("'>").Append(row["Name"]).Append("</option>");
}
//4、值替换,对下拉列表需要查询Managers表 的id 值就是 values ,,,Name 值就是下拉列表中显示的名字
html = html.Replace("#action","新增").Replace("#name", "").Replace("#address", "").Replace("#manager", sb.ToString());
//7、把拼接好的sb的<option>替换在这里,,注意sb的ToString()
//8、将所有的拼接好的都输出到浏览器张去
context.Response.Write(html);
}
else if (action == "edit")
{
//编辑
}
else
{
//action错误
CommonHelper.OutputError("action错误!");
}
}
public bool IsReusable
{
get
{
return false;
}
}
}
}
12、现在做编辑的功能;首先在这里进行id值的“非空”,“合法性”检查。
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Web;
namespace Web1.Day3.cps
{
/// <summary>
/// CompanyAddnewEdit 的摘要说明
/// </summary>
public class CompanyAddnewEdit : IHttpHandler
{
public void ProcessRequest(HttpContext context)
{
context.Response.ContentType = "text/html";
//1、从url地址栏中读取action
string action=context.Request["action"];
//3、无论”编辑“还是”新增“都需要读取模板页
string html = CommonHelper.ReadHtml("~/Day3/cps/CompanyAddnewEdit.html");
//5、查询公司的经理表
DataTable td= SqlHelper.ExecuteQuery("select * from Managers;");
//2、两种情况
if (action=="addnew")
{
//新增
//6、对经理表遍历人员信息进行,字符串的拼接生成<option values="id">雷军</option>
StringBuilder sb = new StringBuilder();
foreach (DataRow row in td.Rows)
{
sb.Append("<option values='").Append(row["id"]).Append("'>").Append(row["Name"]).Append("</option>");
}
//4、值替换,对下拉列表需要查询Managers表 的id 值就是 values ,,,Name 值就是下拉列表中显示的名字
html = html.Replace("#action","新增").Replace("#name", "").Replace("#address", "").Replace("#manager", sb.ToString());
//7、把拼接好的sb的<option>替换在这里,,注意sb的ToString()
//8、将所有的拼接好的都输出到浏览器张去
context.Response.Write(html);
}
else if (action == "edit")
{
//编辑
//9、从报文Url中取出id,,因为报文中是字符串,,检验是否非空
string strId=context.Request["id"];
if (string.IsNullOrEmpty(strId))
{
CommonHelper.OutputError("id不能为空!");
return;
}
//10、该id不为空,进行数据查询该id的信息
int id = Convert.ToInt32(context.Request["id"]);
DataTable table = SqlHelper.ExecuteQuery("Select * from Companys where Id=@Id", new SqlParameter("@Id", id));
//11、是否有该人id的记录
if (table.Rows.Count <= 0)
{
CommonHelper.OutputError("没找到id=" + id + "的公司");
return;
}
//12、或者该id有多条记录的出现呢
if (table.Rows.Count > 1)
{
CommonHelper.OutputError("找到多条id=" + id + "的公司");
return;
}
//13、来到这里说明,id已经完全的查出来了,对查出来的数据进行声明变量
//14、实例化一个行
DataRow row = table.Rows[0];
string name=(string)row["Name"];
string address = (string)row["Address"];
int managerid=(int)row["ManagerId"];
//15、得到变量进行字符串的替换
//16、对于ManagerId需要处理,可以拷贝上边的代码
StringBuilder sbMger = new StringBuilder();
foreach (DataRow rowb in td.Rows)
{
//18、对于ManagerId还需要考虑选中问题,,所以还要得到rowb行的id
//<option values="id">雷军</option>
//19、接着对这个id进行声明变量
int personId=(int)rowb["id"];
//20、如果这个id是这个经理的id就将id选中,添加selected
if (personId == managerid)
{
sbMger.Append("<option values='").Append(personId).Append("'selected>").Append(rowb["Name"]).Append("</option>");
}
else
{
//20、如果这个id是这个经理的id就将id选中,否则就不添加selected
sbMger.Append("<option values='").Append(personId).Append("'>").Append(rowb["Name"]).Append("</option>");
}
}
html = html.Replace("#action", "修改").Replace("#name", name).Replace("#address", address).Replace("#manager", sbMger.ToString());
//17、这里进行替换
context.Response.Write(html);
}
else
{
//action错误
CommonHelper.OutputError("action错误!");
}
}
public bool IsReusable
{
get
{
return false;
}
}
}
}
13、添加隐藏域,(因为http连接不记得上次与浏览器发生了什么,所以要用隐藏域)做保存功能 ;
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
<title>公司#action</title>
</head>
<body>
<form action="CompanySave.ashx" method="post">
<input type="hidden" name="action" value="{action}" />
<input type="hidden" name="id" value="{id}" />
公司名称:<input type="text" name="name" value="#name" />
公司地址:<input type="text" name="address" value="#address" />
公司经理:<select name="managerId">
<!--<option value="llis"></option>-->
#manager
</select>
</form>
</body>
</html>
15、添加隐藏域后对CompanyAddnewEdit.ashx 文件修改
html = html.Replace("#action", "新增").Replace("#name", "").Replace("#address", "")
.Replace("#manager", sb.ToString()).Replace("{action}", "addnew");
html = html.Replace("#action", "修改").Replace("#name", name).Replace("#address", address)
.Replace("#manager", sbMger.ToString()).Replace("{action}", "edit").Replace("{id}",id.ToString());
//17、这里进行替换
16、编写CompanySave.ashx一般处理程序
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
namespace Web1.Day3.cps
{
/// <summary>
/// CompanySave 的摘要说明
/// </summary>
public class CompanySave : IHttpHandler
{
public void ProcessRequest(HttpContext context)
{
context.Response.ContentType = "text/html";
//1、添加隐藏域,由action
//2、读取action属性
string action=context.Request["action"];
string strName=context.Request["name"];
string strAddress=context.Request["address"];
int valuesId =Convert.ToInt32(context.Request["managerId"]);
//3、做检查
if (string.IsNullOrEmpty(strName))
{
CommonHelper.OutputError("公司名称必填!");
return;
}
if (string.IsNullOrEmpty(strName))
{
CommonHelper.OutputError("公司地址必填!");
return;
}
//option默认都是显示的,所以这个不必判断
//4、写if-------else------框架
if (action=="addnew")
{
//5、执行插入
SqlHelper.ExecuteNonQuery("insert into Companys(Name,Address,ManagerId) values(@Name,@Address,@ManagerId)"
,new SqlParameter("@Name",strName)
, new SqlParameter("@Address",strAddress)
, new SqlParameter("@managerId", valuesId));//此时的MId才是整数类型
context.Response.Redirect("CompanyList.ashx");
}
else if(action=="edit")
{
//6、执行修改
//7、拿到id
int id = Convert.ToInt32(context.Request["id"]);
//8、根据id修改
SqlHelper.ExecuteNonQuery("Update Companys set Name=@Name,Address=@Address,ManagerId=@ManagerId where id=@id"
,new SqlParameter("@Name",strName)
, new SqlParameter("@Address", strAddress)
, new SqlParameter("@managerId", valuesId)
,new SqlParameter("@id",id));
//9、修改完毕重定向
context.Response.Redirect("CompanyList.ashx");
}
else
{
CommonHelper.OutputError("action错误!");
}
}
public bool IsReusable
{
get
{
return false;
}
}
}
}
17、结果图:
18、总结遇到的问题:写代码不要丢字母!!!!
19、这里有个
<selected name=”selected” >
<option value=”1”>雷军1</option>
<option value=”1”>雷军2</option>
<option value=”1”>雷军3</option>
</selected>
还是html标记不熟练的问题。。。加油!
USE [Persons]
GO
/****** Object: Table [dbo].[Managers] Script Date: 06/01/2015 19:00:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Managers](
[id] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NOT NULL,
[Age] [int] NOT NULL,
[Gender] [bit] NOT NULL,
CONSTRAINT [PK_Managers] 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
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[Managers] ON
INSERT [dbo].[Managers] ([id], [Name], [Age], [Gender]) VALUES (1, N'雷军', 30, 1)
INSERT [dbo].[Managers] ([id], [Name], [Age], [Gender]) VALUES (2, N'比尔', 40, 1)
INSERT [dbo].[Managers] ([id], [Name], [Age], [Gender]) VALUES (3, N'杨幂', 30, 0)
INSERT [dbo].[Managers] ([id], [Name], [Age], [Gender]) VALUES (4, N'李彦宏', 30, 1)
SET IDENTITY_INSERT [dbo].[Managers] OFF
/****** Object: Table [dbo].[Companys] Script Date: 06/01/2015 19:00:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Companys](
[id] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NOT NULL,
[Address] [varchar](50) NOT NULL,
[ManagerId] [int] NOT NULL,
CONSTRAINT [PK_Companys] 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
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[Companys] ON
INSERT [dbo].[Companys] ([id], [Name], [Address], [ManagerId]) VALUES (1, N'小米', N'北京', 1)
INSERT [dbo].[Companys] ([id], [Name], [Address], [ManagerId]) VALUES (2, N'微软', N'硅谷文', 2)
INSERT [dbo].[Companys] ([id], [Name], [Address], [ManagerId]) VALUES (5, N'京东', N'天津', 3)
INSERT [dbo].[Companys] ([id], [Name], [Address], [ManagerId]) VALUES (9, N'百度', N'河南', 4)
INSERT [dbo].[Companys] ([id], [Name], [Address], [ManagerId]) VALUES (11, N'沙发', N'asf', 1)
INSERT [dbo].[Companys] ([id], [Name], [Address], [ManagerId]) VALUES (12, N'asf', N'阿斯蒂芬', 1)
INSERT [dbo].[Companys] ([id], [Name], [Address], [ManagerId]) VALUES (13, N'阿斯蒂芬', N'爱上', 4)
INSERT [dbo].[Companys] ([id], [Name], [Address], [ManagerId]) VALUES (14, N'阿斯蒂芬', N'asf', 1)
INSERT [dbo].[Companys] ([id], [Name], [Address], [ManagerId]) VALUES (15, N'阿斯蒂芬', N'asf', 3)
INSERT [dbo].[Companys] ([id], [Name], [Address], [ManagerId]) VALUES (16, N'萨芬', N'as ', 3)
INSERT [dbo].[Companys] ([id], [Name], [Address], [ManagerId]) VALUES (17, N'萨芬', N'as ', 3)
INSERT [dbo].[Companys] ([id], [Name], [Address], [ManagerId]) VALUES (18, N'萨芬', N'as ', 3)
INSERT [dbo].[Companys] ([id], [Name], [Address], [ManagerId]) VALUES (19, N'方式', N'萨芬', 1)
INSERT [dbo].[Companys] ([id], [Name], [Address], [ManagerId]) VALUES (20, N'跟得上', N'的是', 1)
INSERT [dbo].[Companys] ([id], [Name], [Address], [ManagerId]) VALUES (21, N'挖法', N'啊大', 3)
SET IDENTITY_INSERT [dbo].[Companys] OFF
/****** Object: ForeignKey [FK_companys] Script Date: 06/01/2015 19:00:50 ******/
ALTER TABLE [dbo].[Companys] WITH CHECK ADD CONSTRAINT [FK_companys] FOREIGN KEY([ManagerId])
REFERENCES [dbo].[Managers] ([id])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[Companys] CHECK CONSTRAINT [FK_companys]
GO
USE [Persons]
GO
/****** Object: Table [dbo].[Managers] Script Date: 06/01/2015 18:54:05 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Managers](
[id] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](50) NOT NULL,
[Age] [int] NOT NULL,
[Gender] [bit] NOT NULL,
CONSTRAINT [PK_Managers] 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
SET ANSI_PADDING OFF
GO