利用前台程序控制行转列数据的输出,可能从效率和扩展性上更好.一个例子
来源:互联网 发布:js调用客户端应用程序 编辑:程序博客网 时间:2024/05/29 21:18
采用 sql2k + .net2005 + xslt完成.
效率情况分析:
若表中数据量比较大, 而统计出的结果数据比较少,那么效果比较好.
因为,数据库得出统计数据, 在少量的数据下由xslt完成格式输出工作.
当然,xslt完成的输出,特别是输出岗位数据这一步分,还是比较占web服务器资源的. 如果想省掉,那也很简单, 还是一句话,直接把数据以xml格式输出,用js来完成table的布局,让它去占访客的ie资源吧.
create TABLE pm(id varchar(20),name varchar(20),DeptName varchar(20),DeptID varchar(20),DeptSequence int,tnum int,Extension varchar(20))
go
insert into pm(id,name,DeptName,DeptID,DeptSequence,tnum,Extension)values(1, '张三', '1部',1,10,1,'123')
insert into pm(id,name,DeptName,DeptID,DeptSequence,tnum,Extension)values(2, '李四', '1部',1,10,2,'456')
insert into pm(id,name,DeptName,DeptID,DeptSequence,tnum,Extension)values(3, '王五', '2部',2,20,1,'321')
go
create TABLE RI(id varchar(20),perid varchar(20),StateName varchar(20),partid varchar(20),RegisterDate datetime)
go
insert into RI(id,perid,StateName,partid,RegisterDate)values(1, 1, '面试通过',1,getdate())
insert into RI(id,perid,StateName,partid,RegisterDate)values( 2, 1, '面试不通过',3,getdate())
insert into RI(id,perid,StateName,partid,RegisterDate)values( 3, 2, '很黄很暴力',4,getdate())
insert into RI(id,perid,StateName,partid,RegisterDate)values( 4, 3, '面试不通过',4,getdate())
insert into RI(id,perid,StateName,partid,RegisterDate)values( 5, 1, '放弃',1,getdate())
insert into RI(id,perid,StateName,partid,RegisterDate)values( 6, 2, '面试不通过',5,getdate())
insert into RI(id,perid,StateName,partid,RegisterDate)values( 7, 3, '面试通过',6,getdate())
insert into RI(id,perid,StateName,partid,RegisterDate)values( 8, 1, '好',7,getdate())
go
create TABLE part(id varchar(20),name varchar(20))
go
insert into part(id,name)values( 1, '文员')
insert into part(id,name)values( 2, '法师')
insert into part(id,name)values( 3, '牧师')
insert into part(id,name)values( 4, '盗贼')
insert into part(id,name)values( 5, '骑士')
insert into part(id,name)values( 6, '萨满')
insert into part(id,name)values( 7, '战士')
insert into part(id,name)values( 8, '德鲁伊')
insert into part(id,name)values( 9, '猎人')
insert into part(id,name)values( 10, '术士')
go
alter proc getDemoData
AS
begin
select a.DeptName,a.DeptSequence,a.Name,a.tnum,a.Extension,a.id perid,
sum(case statename when '面试通过' then 1 else 0 end) passNum,
count(*) viewNum
from pm a
inner join ri b
on a.id=b.perid
group by a.DeptName,a.DeptSequence,a.Name,a.tnum,a.Extension,a.id
for xml raw
select c.name name,isnull(b.perid,0) perid,isnull(count(b.perid),0) perNum
from part c
left join ri b
on b.partid=c.id
group by b.perid,c.name
for xml raw
end
go
go
--drop table pm,ri,part
--go
go
insert into pm(id,name,DeptName,DeptID,DeptSequence,tnum,Extension)values(1, '张三', '1部',1,10,1,'123')
insert into pm(id,name,DeptName,DeptID,DeptSequence,tnum,Extension)values(2, '李四', '1部',1,10,2,'456')
insert into pm(id,name,DeptName,DeptID,DeptSequence,tnum,Extension)values(3, '王五', '2部',2,20,1,'321')
go
create TABLE RI(id varchar(20),perid varchar(20),StateName varchar(20),partid varchar(20),RegisterDate datetime)
go
insert into RI(id,perid,StateName,partid,RegisterDate)values(1, 1, '面试通过',1,getdate())
insert into RI(id,perid,StateName,partid,RegisterDate)values( 2, 1, '面试不通过',3,getdate())
insert into RI(id,perid,StateName,partid,RegisterDate)values( 3, 2, '很黄很暴力',4,getdate())
insert into RI(id,perid,StateName,partid,RegisterDate)values( 4, 3, '面试不通过',4,getdate())
insert into RI(id,perid,StateName,partid,RegisterDate)values( 5, 1, '放弃',1,getdate())
insert into RI(id,perid,StateName,partid,RegisterDate)values( 6, 2, '面试不通过',5,getdate())
insert into RI(id,perid,StateName,partid,RegisterDate)values( 7, 3, '面试通过',6,getdate())
insert into RI(id,perid,StateName,partid,RegisterDate)values( 8, 1, '好',7,getdate())
go
create TABLE part(id varchar(20),name varchar(20))
go
insert into part(id,name)values( 1, '文员')
insert into part(id,name)values( 2, '法师')
insert into part(id,name)values( 3, '牧师')
insert into part(id,name)values( 4, '盗贼')
insert into part(id,name)values( 5, '骑士')
insert into part(id,name)values( 6, '萨满')
insert into part(id,name)values( 7, '战士')
insert into part(id,name)values( 8, '德鲁伊')
insert into part(id,name)values( 9, '猎人')
insert into part(id,name)values( 10, '术士')
go
alter proc getDemoData
AS
begin
select a.DeptName,a.DeptSequence,a.Name,a.tnum,a.Extension,a.id perid,
sum(case statename when '面试通过' then 1 else 0 end) passNum,
count(*) viewNum
from pm a
inner join ri b
on a.id=b.perid
group by a.DeptName,a.DeptSequence,a.Name,a.tnum,a.Extension,a.id
for xml raw
select c.name name,isnull(b.perid,0) perid,isnull(count(b.perid),0) perNum
from part c
left join ri b
on b.partid=c.id
group by b.perid,c.name
for xml raw
end
go
go
--drop table pm,ri,part
--go
demoXsltReport.aspx.cs
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Xml;
using System.Xml.Xsl;
using System.Text;
using System.Data.SqlClient;
public partial class demoXsltReport : System.Web.UI.Page
...{
protected void Page_Load(object sender, EventArgs e)
...{
Response.Write(TransForm(buildXML(), "demoTransformData.xsl"));
}
private string getConnStr()
...{
return ConfigurationManager.AppSettings["connstr"];
}
private XmlDocument buildXML()
...{
StringBuilder sb = new StringBuilder();
sb.Append("<?xml version="1.0" encoding="utf-8"?>");
sb.Append("<root>");
SqlConnection cn = new SqlConnection(getConnStr());
cn.Open();
SqlCommand cmd = new SqlCommand("getDemoData", cn);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataReader dr = cmd.ExecuteReader();
if (dr.Read())
...{
sb.Append(" <DeptData>" + dr[0].ToString() + "</DeptData>");
}
dr.NextResult();
if (dr.Read())
...{
sb.Append(" <PertData>" + dr[0].ToString() + "</PertData>");
}
dr.Close();
cn.Close();
cn.Dispose();
sb.Append("</root>");
XmlDocument xd = new XmlDocument();
xd.LoadXml(sb.ToString());
return xd;
}
public string TransForm(XmlDocument xd, string xslURL)
...{
XslTransform xsl = new XslTransform();
xsl.Load(Server.MapPath(xslURL));
System.IO.MemoryStream t = new System.IO.MemoryStream();
xsl.Transform(xd, null, t, null);
return System.Text.UTF8Encoding.UTF8.GetString(t.ToArray());
}
}
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Xml;
using System.Xml.Xsl;
using System.Text;
using System.Data.SqlClient;
public partial class demoXsltReport : System.Web.UI.Page
...{
protected void Page_Load(object sender, EventArgs e)
...{
Response.Write(TransForm(buildXML(), "demoTransformData.xsl"));
}
private string getConnStr()
...{
return ConfigurationManager.AppSettings["connstr"];
}
private XmlDocument buildXML()
...{
StringBuilder sb = new StringBuilder();
sb.Append("<?xml version="1.0" encoding="utf-8"?>");
sb.Append("<root>");
SqlConnection cn = new SqlConnection(getConnStr());
cn.Open();
SqlCommand cmd = new SqlCommand("getDemoData", cn);
cmd.CommandType = CommandType.StoredProcedure;
SqlDataReader dr = cmd.ExecuteReader();
if (dr.Read())
...{
sb.Append(" <DeptData>" + dr[0].ToString() + "</DeptData>");
}
dr.NextResult();
if (dr.Read())
...{
sb.Append(" <PertData>" + dr[0].ToString() + "</PertData>");
}
dr.Close();
cn.Close();
cn.Dispose();
sb.Append("</root>");
XmlDocument xd = new XmlDocument();
xd.LoadXml(sb.ToString());
return xd;
}
public string TransForm(XmlDocument xd, string xslURL)
...{
XslTransform xsl = new XslTransform();
xsl.Load(Server.MapPath(xslURL));
System.IO.MemoryStream t = new System.IO.MemoryStream();
xsl.Transform(xd, null, t, null);
return System.Text.UTF8Encoding.UTF8.GetString(t.ToArray());
}
}
dataTransform.xsl
<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns="http://www.w3.org/1999/xhtml">
<xsl:key name="pert_group" match="//PertData/row[@perid!=0]" use="@name" />
<xsl:variable name="pertNum" select="count(//PertData/row[@perid!=0 and count(.|key('pert_group',./@name)[1])=1])"/>
<xsl:template match="/">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>demo</title>
</head>
<body>
<table border="1">
<!--制作表头-->
<tr>
<td>DeptName</td>
<td>DeptSequence</td>
<td>PersonName</td>
<td>tNum</td>
<td>Extension</td>
<td>perid</td>
<td>PassNum</td>
<td>viewNum</td>
<!--
<xsl:for-each select="//PertData/row[@perid!=0]">
<td>
<xsl:value-of select="@name"/>
</td>
</xsl:for-each>
-->
<xsl:call-template name="getPertNameList"/>
</tr>
<!--输出数据-->
<xsl:for-each select="//DeptData/row">
<tr>
<td>
<xsl:value-of select="@DeptName"/>
</td>
<td>
<xsl:value-of select="@DeptSequence"/>
</td>
<td>
<xsl:value-of select="@Name"/>
</td>
<td>
<xsl:value-of select="@tnum"/>
</td>
<td>
<xsl:value-of select="@Extension" disable-output-escaping="yes"/>
</td>
<td>
<xsl:value-of select="@perid"/>
</td>
<td>
<xsl:value-of select="@passNum"/>
</td>
<td>
<xsl:value-of select="@viewNum"/>
</td>
<xsl:call-template name="getPertNumForDept">
<xsl:with-param name="perid" select="@perid"/>
</xsl:call-template>
</tr>
</xsl:for-each>
</table>
</body>
</html>
</xsl:template>
<xsl:template name="getPertNumForDept">
<xsl:param name="perid"/>
<xsl:for-each select="//PertData/row[@perid!=0 and count(.|key('pert_group',./@name)[1])=1]">
<xsl:sort data-type="text" select="@name"/>
<td>
<xsl:choose>
<xsl:when test="@perid=$perid">
<xsl:value-of select="@perNum"/>
</xsl:when>
<xsl:otherwise>0</xsl:otherwise>
</xsl:choose>
</td>
</xsl:for-each>
</xsl:template>
<xsl:template name="getPertNameList">
<xsl:for-each select="//PertData/row[@perid!=0 and count(.|key('pert_group',./@name)[1])=1]">
<xsl:sort data-type="text" select="@name"/>
<td>
<xsl:value-of select="./@name"/>
</td>
</xsl:for-each>
</xsl:template>
</xsl:stylesheet>
<xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns="http://www.w3.org/1999/xhtml">
<xsl:key name="pert_group" match="//PertData/row[@perid!=0]" use="@name" />
<xsl:variable name="pertNum" select="count(//PertData/row[@perid!=0 and count(.|key('pert_group',./@name)[1])=1])"/>
<xsl:template match="/">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>demo</title>
</head>
<body>
<table border="1">
<!--制作表头-->
<tr>
<td>DeptName</td>
<td>DeptSequence</td>
<td>PersonName</td>
<td>tNum</td>
<td>Extension</td>
<td>perid</td>
<td>PassNum</td>
<td>viewNum</td>
<!--
<xsl:for-each select="//PertData/row[@perid!=0]">
<td>
<xsl:value-of select="@name"/>
</td>
</xsl:for-each>
-->
<xsl:call-template name="getPertNameList"/>
</tr>
<!--输出数据-->
<xsl:for-each select="//DeptData/row">
<tr>
<td>
<xsl:value-of select="@DeptName"/>
</td>
<td>
<xsl:value-of select="@DeptSequence"/>
</td>
<td>
<xsl:value-of select="@Name"/>
</td>
<td>
<xsl:value-of select="@tnum"/>
</td>
<td>
<xsl:value-of select="@Extension" disable-output-escaping="yes"/>
</td>
<td>
<xsl:value-of select="@perid"/>
</td>
<td>
<xsl:value-of select="@passNum"/>
</td>
<td>
<xsl:value-of select="@viewNum"/>
</td>
<xsl:call-template name="getPertNumForDept">
<xsl:with-param name="perid" select="@perid"/>
</xsl:call-template>
</tr>
</xsl:for-each>
</table>
</body>
</html>
</xsl:template>
<xsl:template name="getPertNumForDept">
<xsl:param name="perid"/>
<xsl:for-each select="//PertData/row[@perid!=0 and count(.|key('pert_group',./@name)[1])=1]">
<xsl:sort data-type="text" select="@name"/>
<td>
<xsl:choose>
<xsl:when test="@perid=$perid">
<xsl:value-of select="@perNum"/>
</xsl:when>
<xsl:otherwise>0</xsl:otherwise>
</xsl:choose>
</td>
</xsl:for-each>
</xsl:template>
<xsl:template name="getPertNameList">
<xsl:for-each select="//PertData/row[@perid!=0 and count(.|key('pert_group',./@name)[1])=1]">
<xsl:sort data-type="text" select="@name"/>
<td>
<xsl:value-of select="./@name"/>
</td>
</xsl:for-each>
</xsl:template>
</xsl:stylesheet>
- 利用前台程序控制行转列数据的输出,可能从效率和扩展性上更好.一个例子
- 一个小例子看编程的扩展性
- 从PCI上读取数据 线程和定时器效率
- 开发一个业务逻辑复杂的系统,应该怎么样设计才能使项目的扩展性更好?
- 开发一个业务逻辑复杂的系统,应该怎么样设计才能使项目的扩展性更好
- oracle编程入门笔记2015-01-17--一个例子告诉你共享池和数据缓存对效率的影响
- JSP页面小脚本实现日期比较,Java同理,精简过后的,可能在效率上不太好,有大大可以给优化下就更好了
- 利用Google成为一个更好的程序员
- socket连接服务端和客户端的新demo,扩展性更好,更适合用于项目中。
- [转]收藏一个利用SCN回复数据的例子
- SpringMVC中利用json从后台穿数据到前台
- 如何利用Keras的扩展性
- 如何利用Keras的扩展性
- 前台接收sql存储过程输出参数的例子
- CSS和JS动画,那个的效率更好。
- 设计一个程序,从键盘上输入若干字符串,利用算法库中的查找函数对给定的字符串进行查找,将查找后的结果输出
- 计算机组成.更好的利用处理器.从输入输出看中断
- N个数组,从每个数组中取出一个数,组成一个序列,输出所有可能的情况
- 设置表格行背影色渐变
- TC2.0库函数大全
- 表格排序
- 怎样对一个网站进行系统的测试?
- 浮躁的国内测试界
- 利用前台程序控制行转列数据的输出,可能从效率和扩展性上更好.一个例子
- 编程修养
- C# winform 获取汉字首字母拼音
- C# winform 调用系统声音
- 当老板问你「今天忙不忙?」时,你怎么回答呢?
- My Linux Tips (1)
- Visual C#中实现窗体间的数据传递之一
- MD5加密
- Web用户控件(WebUserControl)嵌套问题-别忘了注册子用户控件