第七章 业务数据库的管理（十二）-- 设置业务数据共享

7.8 设置业务数据共享

    如图7.14，有权限的用户（如管理员）可以选择业务数据表进行共享，而且要设置用于查询的字段。这样，有权限的用户（共享业务数据的查看权限分配见图7.15）就可以查询和浏览已设置为共享的业务信息。

    如图7.16所示，用户在登录进系统后，点击查询相关业务信息板块中的“查询建设用地审批信息”菜单，系统主窗口将列表显示所有的建设用地审批业务数据记录，列表显示的字段和可以查询的字段就是图7.4中设置的字段。点击“详细信息”就可以看到某项审批业务的详细业务数据，并可以下载查阅相关的附件资料。

图7.14 选择和设置要共享的业务数据表

 

图7.15 （已设置为共享的）业务数据查阅权限的分配

 

图7.16 查询业务数据

    1、DisplayedTablesList.aspx文件代码：

    ......

    <form id="Form1" method="post" runat="server">
     <table cellSpacing="0" cellPadding="0" width="100%" align="center" border="0">
      <tr height="60">
       <td style="FONT-SIZE: 16px" align="center">设置共享的业务表</td>
      </tr>
      <tr height="30">
       <td align="right"><span id="Message" style="FONT-SIZE: 11pt; COLOR: red; FONT-FAMILY:

            Arial" runat="server" MaintainState="false"></span>&nbsp;</td>
      </tr>
     </table>
     <table cellSpacing="0" cellPadding="0" width="100%" align="center" border="0">
      <tr>
       <td><asp:datagrid id="DataGrid1" runat="server"

                HeaderStyle-HorizontalAlign="Center" DataKeyField="DisplayedTableID"
                 Width="100%" AllowPaging="True" PageSize="6">
            <ItemStyle Font-Size="13px" HorizontalAlign="Center" Height="28px"

                VerticalAlign="Bottom"></ItemStyle>
            <HeaderStyle Font-Size="16px" HorizontalAlign="Center" Height="30px"

                VerticalAlign="Middle" BackColor="#EEEEEE"></HeaderStyle>
            <Columns>
              <asp:BoundColumn DataField="Title" HeaderText="业务表名称"></asp:BoundColumn>
              <asp:BoundColumn DataField="fulltablename" ReadOnly="True"

                   HeaderText="关联表"></asp:BoundColumn>
              <asp:BoundColumn DataField="SearchedColumns" ReadOnly="True"

                     HeaderText="列表显示和查询的字段">
                <ItemStyle HorizontalAlign="Left" Width="46%"></ItemStyle>
              </asp:BoundColumn>
              <asp:EditCommandColumn ButtonType="LinkButton" UpdateText="更新"

                     CancelText="取消" EditText="编辑"></asp:EditCommandColumn>
              <asp:TemplateColumn>
               <ItemTemplate>
                 <asp:LinkButton id="LinkButton1" runat="server" CommandName="Delete">删除

                 </asp:LinkButton>
               </ItemTemplate>
              </asp:TemplateColumn>
              <asp:ButtonColumn Text="设置要查询的字段" CommandName="SetDisplayedColumns">

              </asp:ButtonColumn>
            </Columns>
            <PagerStyle Font-Size="9pt" HorizontalAlign="Right" ForeColor="#3366FF"

                 Mode="NumericPages"></PagerStyle>
          </asp:datagrid></td>
         </tr>
       </table>

       ......
       <table cellSpacing="2" cellPadding="2" width="100%" align="right" border="0">
        <tr>
         <td align="right" width="10%"><FONT face="宋体">选择关联业务表：</FONT></td>
         <td><asp:dropdownlist id="DropDownList1" runat="server" DataValueField="TableID"

                 DataTextField="Description"></asp:dropdownlist></td>
         <td width="55%"></td>
        </tr>
        <tr><td></td><td></td><td width="55%"></td></tr>
        <tr><td align="right">&nbsp;</td>
         <td align="left">&nbsp;&nbsp;&nbsp;

           <asp:button id="Button2" runat="server" Text="保存"></asp:button>&nbsp;

           <asp:label id="Label1" runat="server" ForeColor="Red"></asp:label>

         </td>
         <td width="55%"></td>
        </tr>
       </table>
    </form>

    ......

    DisplayedTablesList.aspx.cs文件代码：

    ......

    using DataAccess;

    namespace workflow.admin.DisplayedTable
    {
       public class DisplayedTablesList : System.Web.UI.Page
       {
          protected System.Web.UI.WebControls.DataGrid DataGrid1;
          protected System.Web.UI.WebControls.DropDownList DropDownList1;
          protected System.Web.UI.WebControls.Button Button2;
          protected System.Web.UI.HtmlControls.HtmlGenericControl Message;
          protected System.Web.UI.WebControls.Label Label1;
 
          private void Page_Load(object sender, System.EventArgs e)
          {
             // 在此处放置用户代码以初始化页面
             if(! IsPostBack)
             {
                string strSql="select * from RelatedTables";
                DataView dv=new Base().SQLExeDataView(strSql);
                DropDownList1.DataSource=dv;
                DropDownList1.DataBind();

                BindGrid();
             }
             Label1.Text="";
             Message.InnerHtml="";
         }

         void BindGrid()
         {
            string strSql="select DisplayedTableID,Title,SearchedColumns,

                isnull((select Ower from RelatedTables where TableID=RelatedTableID)+'.','')

                  +(select TableName from RelatedTables where TableID=RelatedTableID)

                     as fulltablename from DisplayedTables";
            DataSet ds=new Base().SQLExeDataSet(strSql);
            if(ds !=null)
            {
               DataGrid1.DataSource=ds;
               DataGrid1.DataBind();
            }
         }

         private void DataGrid1_ItemDataBound(object sender,

                System.Web.UI.WebControls.DataGridItemEventArgs e)
         {
           if(e.Item.ItemType == ListItemType.Item

                    ||e.Item.ItemType == ListItemType.AlternatingItem)
           {
              LinkButton btn = (LinkButton)(e.Item.Cells[4].Controls[1]);
              btn.Attributes.Add("onClick","JavaScript:return confirm('确定删除吗？')");
              e.Item.Cells[2].Attributes.Add("style",

                                        "WORD-BREAK:break-all;WORD-WRAP:break-word");
           }
         }

         private void Button2_Click(object sender, System.EventArgs e)
         {
           //判断选择的表是否重复
           string strSql="select DisplayedTableID from DisplayedTables where

                   RelatedTableID="+DropDownList1.SelectedValue;
           Base basecode=new Base();
           if(basecode.IfExistRecord(strSql))
           {
              Label1.Text="要显示的业务表已经存在";
              return;
           }
           strSql="insert into DisplayedTables(RelatedTableID,Title) values

                 ("+DropDownList1.SelectedValue+",'"+DropDownList1.SelectedItem.Text+"')";
           if(! basecode.SQLExeNonQuery(strSql))
           {
              Label1.Text=basecode.BaseSqlErrDes;
              return;
           }
           BindGrid();
         }

         private void DataGrid1_EditCommand(object source,

                System.Web.UI.WebControls.DataGridCommandEventArgs e)
         {
            DataGrid1.EditItemIndex=e.Item.ItemIndex;
            BindGrid();
         }

         private void DataGrid1_CancelCommand(object source,

                System.Web.UI.WebControls.DataGridCommandEventArgs e)
         {
            DataGrid1.EditItemIndex=-1;
            BindGrid();
         }

         private void DataGrid1_UpdateCommand(object source,

                System.Web.UI.WebControls.DataGridCommandEventArgs e)
         {
            string strSql="update DisplayedTables set Title=@Title where

                 DisplayedTableID="+DataGrid1.DataKeys[e.Item.ItemIndex].ToString();
            SqlCommand cmd=new SqlCommand(strSql);
            cmd.Parameters.Add("@Title",SqlDbType.VarChar);
            cmd.Parameters["@Title"].Value=((TextBox)e.Item.Cells[0].Controls[0]).Text.Trim();
            Base basecode=new Base();
            if(! basecode.SQLExeNonQuery_proc(cmd))
            {
              Message.InnerHtml=basecode.BaseSqlErrDes;
              return;
            }
            DataGrid1.EditItemIndex=-1;
            BindGrid();
          }

          private void DataGrid1_DeleteCommand(object source,

                 System.Web.UI.WebControls.DataGridCommandEventArgs e)
          {
            string displayededtableid=DataGrid1.DataKeys[e.Item.ItemIndex].ToString();
            string strSql="delete DisplayedTables where DisplayedTableID="+displayededtableid;
            Base basecode=new Base();
            if(! basecode.SQLExeNonQuery(strSql))
            {
              Label1.Text=basecode.BaseSqlErrDes;
              return;
            }
            BindGrid();
          }

          private void DataGrid1_ItemCommand(object source,

                 System.Web.UI.WebControls.DataGridCommandEventArgs e)
          {
             if(e.CommandName == "SetDisplayedColumns")
             Response.Redirect("SetSearchedColumns.aspx?id="

                    +DataGrid1.DataKeys[e.Item.ItemIndex].ToString());
          }
       }
    }

    2、SetSearchedColumns.aspx文件代码：

    ......

    <form id="Form1" method="post" runat="server">
     <table height="80" cellSpacing="0" cellPadding="0" width="100%" border="0">
      <tr><td><a href="javascript:history.back()">返回上一页</a></td></tr>
      <tr height="60"><td></td></tr>
     </table>
     <table cellSpacing="0" cellPadding="0" width="100%" align="center" border="0">
      <tr><td style="FONT-SIZE: 16px" align="center">定义用于打印的表格</td></tr>

      <tr height="50"><td align="right">&nbsp;</td></tr>
      <tr>
        <td><asp:table id="Tbl" style="BORDER-RIGHT: 1px solid; BORDER-TOP: 1px solid;

             BORDER-LEFT: 1px solid; BORDER-BOTTOM: 1px solid; FONT-FAMILY: 宋体;

             BORDER-COLLAPSE: collapse" runat="server" border="1" cellPadding="3"

             cellSpacing="0" Width="100%"></asp:table>

        </td>
      </tr>
     </table>
     <table height="100" cellSpacing="0" cellPadding="0" width="100%" border="0">
      <tr>
       <td>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;</td>
       <td align="center">&nbsp;&nbsp;

          <asp:button id="Button1" runat="server" Text="保存"></asp:button>&nbsp;
          <asp:Button id="Button2" runat="server" Text="放弃"></asp:Button>
          <asp:Label id="Label1" runat="server" ForeColor="Red"></asp:Label>

       </td>
      </tr>
     </table>
    </form>

    ......

    SetSearchedColumns.aspx.cs文件代码：

    ......

    using DataAccess;
    using CommonTools;

    namespace workflow.admin.DisplayedTable
    {
      public class SetSearchedColumns : System.Web.UI.Page
      {
        protected System.Web.UI.WebControls.Table Tbl;
        protected System.Web.UI.WebControls.Button Button1;
        protected System.Web.UI.WebControls.Button Button2;
        protected System.Web.UI.WebControls.Label Label1;
 
        private void Page_Load(object sender, System.EventArgs e)
        {
           if(! IsPostBack)
           {
              ViewState["displayedtableid"]=Request.QueryString["id"].ToString();
           }
           string displayedtableid=ViewState["displayedtableid"].ToString();
           string strSql="select RelatedTableID from DisplayedTables where

                DisplayedTableID="+displayedtableid;
           Base basecode=new Base();
           DataSet ds=basecode.SQLExeDataSet(strSql);
           if(ds == null || ds.Tables[0].Rows.Count<1)
           {
             Response.Write("You have encounter an error,please contact to the developer.");
             return;
           }
           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 || ds.Tables[0].Rows.Count<1)
           {
             Response.Write("You have encounter an error,please contact to the developer.");
             return;
           }
           string ower=ds.Tables[0].Rows[0]["Ower"].ToString();
           string tablename=ds.Tables[0].Rows[0]["TableName"].ToString();
           //根据关联表获得关联表的字段名及其中文描述.
           strSql="select * from RelatedTablesFields where TableName='"+tablename+"' and

                TableOwer='"+ower+"'";
           ds=basecode.SQLExeDataSet(strSql);
           ViewState["relatedfields"]=ds;

           TableRow tr=new TableRow();
           TableCell tc=new TableCell();   
           tc.Text="请选择要列表显示和查询的字段：";
           tc.Font.Size=FontUnit.Parse("14px");
           tc.ColumnSpan=9;
           tc.HorizontalAlign=HorizontalAlign.Left;
           tr.Cells.Add(tc);
           Tbl.Rows.Add(tr);

           tr=new TableRow();
           tc=new TableCell();
           tr.Cells.Add(tc);
           //为每一个关联字段设置单选框,并将字段名作为它的ID.
           CheckBox chk=null;
           Label lb=null;
           for(int i=0;i<ds.Tables[0].Rows.Count;i++)
           {
             //7个字段选择控件为一行
             if((i% 7) == 0 & i>0)
             {
               //一行尾部加一列
               tc=new TableCell();
               tc.Text="&nbsp;&nbsp;&nbsp;";
               tr.Cells.Add(tc);
               //另起一行并加一列空白列
               Tbl.Rows.Add(tr);
               tr=new TableRow();
               tc=new TableCell();
               tr.Cells.Add(tc);
             }

             tc=new TableCell();
             tc.HorizontalAlign=HorizontalAlign.Right;
             lb=new Label();
             lb.Text=ds.Tables[0].Rows[i]["FieldAlias"].ToString();
             tc.Controls.Add(lb);
             chk=new CheckBox();
             chk.ID=ds.Tables[0].Rows[i]["FieldName"].ToString();//为避免出现ID重复错误

             //如果是标识字段则默认不钩选并不可编辑.
             if(ds.Tables[0].Rows[i]["IsIdentity"].ToString() =="Y")
             {
               chk.Checked=false;
               chk.Enabled=false;
             }
             tc.Controls.Add(chk);
             tr.Cells.Add(tc);
           }                  
           //最后一行列跨度设置为最大值.
           tc=new TableCell();
           tc.ColumnSpan=9;
           tr.Cells.Add(tc);

           Tbl.Rows.Add(tr);

           //如果DisplayedTables已存在查询字段的内容(即编辑已有DisplayedTables表记录的情形),

           //要分割字符串,得到字段名并设置对应控件的钩选.
           strSql="select SearchedColumns from DisplayedTables where

                DisplayedTableID="+displayedtableid;
           ds=basecode.SQLExeDataSet(strSql);
           if(ds !=null & ds.Tables[0].Rows[0]["SearchedColumns"] !=DBNull.Value)
           {
             string searchedfieldsstr="";
             searchedfieldsstr=ds.Tables[0].Rows[0]["SearchedColumns"].ToString().Trim();
             if(searchedfieldsstr != "")
             {
               string[] fieldsarray=new Tools().StringSplit(searchedfieldsstr,",");
               for(int j=0;j<fieldsarray.Length;j++)
               {
                  //由于保存字段名字符串时最后还多一个",",使得字符串数组多一行空白字串，

                  //所以要判断.
                  if(fieldsarray[j] !="")
                  {
                    chk=(CheckBox)this.FindControl(fieldsarray[j]);
                    if(chk !=null)//避免找不到指定的控件
                        chk.Checked=true;
                  }
               }
             }
           }
         }

         ......（待续）