7.8 设置业务数据共享
2、SetSearchedColumns.aspx.cs文件代码:
......(接前页)
//保存按钮
private void Button1_Click(object sender, System.EventArgs e)
{
//获取字段单选框列表的值
string displayedtableid=ViewState["displayedtableid"].ToString();
string chkedfields="";
DataSet ds=(DataSet)ViewState["relatedfields"];
for(int i=0;i<ds.Tables[0].Rows.Count;i++)
{
CheckBox chk=(CheckBox)this.FindControl(ds.Tables[0]
.Rows[i]["FieldName"].ToString());
if(chk.Checked)
chkedfields=chkedfields+ds.Tables[0].Rows[i]["FieldName"].ToString()+",";
}
string strSql="update DisplayedTables set SearchedColumns='"+chkedfields+"'
where DisplayedTableID="+displayedtableid;
Base basecode=new Base();
if(! basecode.SQLExeNonQuery(strSql))
{
Label1.Text=basecode.BaseSqlErrDes;
return;
}
Response.Redirect("DisplayedTablesList.aspx");
}
private void Button2_Click(object sender, System.EventArgs e)
{
Response.Redirect("DisplayedTablesList.aspx");
}
}
}
7.9 查询和浏览已共享的业务数据
在设置了业务数据共享后,DisplayedTables表中的每条记录代表了一个共享的业务数据表。这样,查询和显示共享数据的页面DisplayTable.aspx以及作为参数的共享表编号DisplayedTableID(DisplayedTables的一条记录编号,代表某个已设置为共享的业务数据表),就可以作为一个链接资源进行管理。如图7.17所示:
![](https://img-my.csdn.net/uploads/201209/19/1348019197_6295.jpg)
图7.17 把共享的业务数据表作为资源管理
作为资源的共享业务表可以分配给角色用户(如图7.15),这样,该用户登录后,左侧菜单栏中就会出现查询业务数据的链接菜单(其链接地址类似DisplayTable.aspx?id=2),如图7.16所示,用户点击菜单链接就可以查询和浏览有关业务数据。
1、DisplayTable.aspx文件代码:
......
<form id="Form1" method="post" runat="server">
<table height="60" cellSpacing="0" cellPadding="0" width="100%" border="0">
<tr><td style="FONT-SIZE: 16px" align="center">业务信息列表</td></tr>
<tr height="50">
<td align="right"><span id="Message" style="FONT-SIZE: 11pt; COLOR: red;
FONT-FAMILY: Arial" runat="server" MaintainState="false"></span> </td>
</tr>
<tr>
<td><asp:datagrid id="DataGrid1" runat="server"
HeaderStyle-HorizontalAlign="Center" DataKeyField="ResourceID"
Width="100%" AllowPaging="True">
<ItemStyle Font-Size="13px" HorizontalAlign="Center" Height="28px"
VerticalAlign="Bottom"></ItemStyle>
<HeaderStyle Font-Size="16px" HorizontalAlign="Center" Height="30px"
VerticalAlign="Middle" BackColor="#EEEEEE"></HeaderStyle>
<PagerStyle Font-Size="9pt" HorizontalAlign="Right" ForeColor="#3366FF"
Mode="NumericPages"></PagerStyle>
</asp:datagrid></td>
</tr>
</table>
<table width="100%" border="0" cellPadding="0" cellSpacing="0">
<tr height="25"><TD>查询业务数据</TD></tr>
<tr>
<td>
<asp:Table id="Tbl" runat="server" Width="100%"></asp:Table>
</td>
</tr>
<tr>
<td>
<asp:Button id="Button1" runat="server" Text="查询"></asp:Button>
<asp:Label id="Label1" runat="server" ForeColor="Red"></asp:Label></td>
</tr>
</table>
</form>
......
DisplayTable.aspx.cs文件代码:
......
using DataAccess;
using CommonTools;
namespace workflow.Process
{
public class DisplayTable : System.Web.UI.Page
{
protected System.Web.UI.WebControls.DataGrid DataGrid1;
protected System.Web.UI.WebControls.Button Button1;
protected System.Web.UI.WebControls.Label Label1;
protected System.Web.UI.WebControls.Table Tbl;
protected System.Web.UI.HtmlControls.HtmlGenericControl Message;
private void Page_Load(object sender, System.EventArgs e)
{
if(! IsPostBack)
{
//验证用户是否登录
if(Session["userid"] == null)
Response.Redirect("../Message.aspx");
ViewState["displayedtableid"]=Request.QueryString["id"].ToString();
//针对查询重指向该页面的情况
ViewState["recordids"]="";
ViewState["pageindex"]="";//该变量实际上是多余的,下面有关语句应该进行修改
if(Request.QueryString["recordids"] != null)
ViewState["recordids"]=Request.QueryString["recordids"].ToString();
if(Request.QueryString["pageindex"] != null)
ViewState["pageindex"]=Request.QueryString["pageindex"].ToString();
}
//获取显示表的要列表显示(查询)的字段
string displayedtableid=ViewState["displayedtableid"].ToString();
string strSql="select RelatedTableID,SearchedColumns from DisplayedTables where
DisplayedTableID="+displayedtableid;
Base basecode=new Base();
DataSet ds=basecode.SQLExeDataSet(strSql);
if(ds == null)
{
Message.InnerHtml=basecode.BaseSqlErrDes;
return;
}
if(ds.Tables[0].Rows[0]["SearchedColumns"] == DBNull.Value
|| ds.Tables[0].Rows[0]["SearchedColumns"].ToString().Trim() == "")
{
Message.InnerHtml="未设置要列表显示和查询的字段,请与管理员联系.";
return;
}
string searchedcolumns=ds.Tables[0].Rows[0]["SearchedColumns"].ToString();
string[] fieldsarray=new Tools().StringSplit(searchedcolumns,",");//得到字段名数组
string relatedtableid=ds.Tables[0].Rows[0]["RelatedTableID"].ToString();
//获取关联的表名称
strSql="select TableName,Ower from RelatedTables where TableID="+relatedtableid;
ds=basecode.SQLExeDataSet(strSql);
if(ds == null)
{
Message.InnerHtml=basecode.BaseSqlErrDes;
return;
}
string tableower=ds.Tables[0].Rows[0]["Ower"].ToString();
string tablename=ds.Tables[0].Rows[0]["TableName"].ToString();
//保存tableower和tablename用于查询
ViewState["tablename"]=tablename;
ViewState["tableower"]=tableower;
//获取关联业务表的标识字段名及列表显示(查询)字段的中文名
strSql="select FieldName from RelatedTablesFields where TableName='"+tablename+"'
and TableOwer='"+tableower+"' and IsIdentity='Y';";
strSql=strSql+"select FieldName,FieldAlias from RelatedTablesFields where
TableName='"+tablename+"' and TableOwer='"+tableower+"' and (1 != 1";
for(int i=0;i<fieldsarray.Length-1;i++)//考虑数组最后一项为空格
{
strSql=strSql+" or FieldName='"+fieldsarray[i]+"'";
}
strSql=strSql+")";
ds=basecode.SQLExeDataSet(strSql);
if(ds == null)
{
Message.InnerHtml=basecode.BaseSqlErrDes;
return;
}
ViewState["columnscount"]=ds.Tables[1].Rows.Count;
string identityfield=ds.Tables[0].Rows[0]["FieldName"].ToString();
//获取关联业务表的记录集
strSql="select "+identityfield;
for(int j=0;j<ds.Tables[1].Rows.Count;j++)
{
strSql=strSql+","+ds.Tables[1].Rows[j]["FieldName"].ToString();
}
strSql=strSql+" from "+tableower+"."+tablename;
//如果是查询重指向该页面,还要加上条件子句.
if(ViewState["recordids"].ToString().Trim() != "")
{
string[] identityidarray=new Tools().StringSplit(ViewState["recordids"]
.ToString().Trim(),",");
strSql=strSql+" where 1 !=1";
//identityidarray.Length-1考虑数组最后一项是空格
for(int j=0;j<identityidarray.Length-1;j++)
{
strSql=strSql+" or "+identityfield+"="+identityidarray[j];
}
}
DataSet ds1=basecode.SQLExeDataSet(strSql);
if(ds1 == null)
{
Message.InnerHtml=basecode.BaseSqlErrDes;
return;
}
DataGrid1.AutoGenerateColumns=false;
for(int j=0;j<ds.Tables[1].Rows.Count;j++)
{
BoundColumn bc = new BoundColumn();
bc.DataField = ds.Tables[1].Rows[j]["FieldName"].ToString();
bc.HeaderText=ds.Tables[1].Rows[j]["FieldAlias"].ToString();
//设置每一列的宽度(考虑增加按钮列的情况)
bc.HeaderStyle.Width=Unit.Percentage(1/(ds.Tables[1].Rows.Count+2)*100);
DataGrid1.Columns.Add(bc);
}
//增加详细信息按钮列
ButtonColumn btncol=new ButtonColumn();
btncol.ButtonType=ButtonColumnType.LinkButton;
btncol.Text="详细信息";
btncol.CommandName="linkcommand";
btncol.HeaderStyle.Width=Unit.Percentage(1/(ds.Tables[1].Rows.Count+2)*100);
DataGrid1.Columns.Add(btncol);
//增加业务过程按钮列
ButtonColumn btncol2=new ButtonColumn();
btncol2.ButtonType=ButtonColumnType.LinkButton;
btncol2.Text="业务过程";
btncol2.CommandName="processcommand";
btncol2.HeaderStyle.Width=Unit.Percentage(1/(ds.Tables[1].Rows.Count+2)*100);
DataGrid1.Columns.Add(btncol2);
DataGrid1.ItemCommand+=new DataGridCommandEventHandler(DataGrid1_ItemCommand);
DataGrid1.DataKeyField=identityfield;
DataGrid1.DataSource=ds1;
if(ViewState["pageindex"].ToString() != "")
DataGrid1.CurrentPageIndex=int.Parse(ViewState["pageindex"].ToString());
DataGrid1.DataBind();
//设置查询控件
TableRow tr=null;
TableCell tc;
for(int i=0;i<ds.Tables[1].Rows.Count;i++)
{
//每行3个查询字段
if(i % 3 ==0)
tr=new TableRow();
tc=new TableCell();
tc.Text=ds.Tables[1].Rows[i]["FieldAlias"].ToString();
tc.Width=Unit.Percentage(15);
tc.HorizontalAlign=HorizontalAlign.Right;
tr.Cells.Add(tc);
tc=new TableCell();
tc.HorizontalAlign=HorizontalAlign.Left;
TextBox tx=new TextBox();
tx.ID=ds.Tables[1].Rows[i]["FieldName"].ToString();
tx.Columns=36;
tc.Controls.Add(tx);
tr.Cells.Add(tc);
//一行的第3个查询字段
if(i % 3 == 2)
Tbl.Rows.Add(tr);
}
//最后一行不足3个查询字段
if(ds.Tables[1].Rows.Count % 3 > 0)
Tbl.Rows.Add(tr);
//保存字段列表用于查询
ViewState["fieldslist"]=ds;
}
private void DataGrid1_ItemDataBound(object sender,
System.Web.UI.WebControls.DataGridItemEventArgs e)
{
if( e.Item.ItemType == ListItemType.Item
|| e.Item.ItemType == ListItemType.AlternatingItem)
{
//获取控件列数
int i=(int)ViewState["columnscount"];
//设置每一列自动换行
for(int j=0;j<i;j++)
e.Item.Cells[j].Attributes.Add("style","WORD-BREAK:break-all;
WORD-WRAP:break-word");
}
}
//自定义的DataGrid事件处理函数
private void DataGrid1_ItemCommand(object source, DataGridCommandEventArgs e)
{
if(e.CommandName=="linkcommand")
{
string displaytableid=ViewState["displayedtableid"].ToString();
string identityvalue=DataGrid1.DataKeys[e.Item.ItemIndex].ToString();
string strlink="<script>window.open('DisplayTableInfo.aspx?
id1="+displaytableid+"&id2="+identityvalue
+"','newwindow','top=0,left=0,toolbar=no,menubar=no,
scrollbars=yes,resizable=yes,location=no,status=no');</script>";
Response.Write(strlink);
}
//显示当前业务数据记录的办理过程(显示业务办理过程的程序将在后续章节介绍)
if(e.CommandName=="processcommand")
{
string tableower=ViewState["tableower"].ToString();
string tablename=ViewState["tablename"].ToString();
string identityvalue=DataGrid1.DataKeys[e.Item.ItemIndex].ToString();
string strSql="select ProcessInstanceID from ProcessInstance where
RelatedTable='"+tableower+"."+tablename+"'
and IdentityFieldValue="+identityvalue;
Base basecode=new Base();
DataSet ds=basecode.SQLExeDataSet(strSql);
if(ds != null)
{
if(ds.Tables[0].Rows[0]["ProcessInstanceID"] != DBNull.Value)
{
string strlink="<script>window.open('DisplayMessage.aspx?id="
+ds.Tables[0].Rows[0]["ProcessInstanceID"].ToString()
+"','newwindow','top=0,left=0,toolbar=no,menubar=no,
scrollbars=yes,resizable=yes,location=no,status=no');</script>";
Response.Write(strlink);
}
}
}
}
private void Button1_Click(object sender, System.EventArgs e)
{
DataSet ds=(DataSet)ViewState["fieldslist"];
string identityfield=ds.Tables[0].Rows[0]["FieldName"].ToString();
string tableower=ViewState["tableower"].ToString();
string tablename=ViewState["tablename"].ToString();
string strSql="select "+identityfield+" from "+tableower+"."+tablename+"
where 1=1";
string fieldname;
TextBox tx;
string childsentence="";
for(int i=0;i<ds.Tables[1].Rows.Count;i++)
{
fieldname=ds.Tables[1].Rows[i]["FieldName"].ToString();
tx=(TextBox)this.FindControl(fieldname);
if(tx.Text.Trim() != "")
{
childsentence=childsentence+" and "+fieldname+" like @"+fieldname;
}
}
//如果未填写任何查询条件则不执行查询,避免url参数过长(url参数长度最大2083字节).
if(childsentence != "")
{
strSql=strSql+childsentence;
SqlCommand cmd=new SqlCommand(strSql);
for(int i=0;i<ds.Tables[1].Rows.Count;i++)
{
fieldname=ds.Tables[1].Rows[i]["FieldName"].ToString();
tx=(TextBox)this.FindControl(fieldname);
if(tx.Text.Trim() != "")
{
//这里假设所有查询字段均为字符串类型,实际上应该判断数据类型(可以参照后
//面将要介绍的任务实例处理程序TaskInstance.aspx.cs)
cmd.Parameters.Add("@"+fieldname,SqlDbType.VarChar);
cmd.Parameters["@"+fieldname].Value="%"+tx.Text.Trim()+"%";
}
}
Base basecode=new Base();
ds=basecode.SQLExeDataSet(cmd);
if(ds == null)
{
Label1.Text=basecode.BaseSqlErrDes;
return;
}
string ids="";
for(int i=0;i<ds.Tables[0].Rows.Count;i++)
{
ids=ids+ds.Tables[0].Rows[i][identityfield].ToString()+",";
}
Response.Redirect("DisplayTable.aspx?id="+ViewState["displayedtableid"]
.ToString()+"&recordids="+ids);
}
else
Response.Redirect("DisplayTable.aspx?id="+ViewState["displayedtableid"]
.ToString());
}
private void DataGrid1_PageIndexChanged(object source,
System.Web.UI.WebControls.DataGridPageChangedEventArgs e)
{
DataGrid1.CurrentPageIndex=e.NewPageIndex;
DataGrid1.DataBind();
}
}
}