如何用三组课程(或多个表)来开发一个表? [英] How to develop one table with three groups of courses (or tables)?
问题描述
我具有以下数据库设计:
>员工表:用户名,名称,工作...等
>
>课程表:CourseID,CoursName,GroupID
>
> Employee_Courses表:EmployeeID,CourseID
>
>群组表:GroupID,GroupName
***注意:每个表中的第一个属性是外键***
我有三组课程,现在我需要开发一个培训矩阵,该矩阵显示所有员工和所有课程,并给每组课程指定特定的颜色,例如:蓝色代表第1组,黄色代表第2组,橙色代表第#组3.我做了一个StoredProcedure,它负责从数据库中检索数据.另外,我可以使用以下方法设计矩阵:Repeater,并在其中放入一个带有HiddenField的GridView,用于检索每个组的ID.这样,我得到了三个表或GridViews.每组课程的表格.
我现在想要的是生成一个包含所有这些组的表,并为每组课程赋予其颜色,那么该怎么做呢?
为了简单地说明我所做的事情,这是我在ASP.NET和C#中的代码:
ASP.NET:
I have the following database design:
> Employee Table: Username, Name, Job ...etc
>
> Courses Table: CourseID, CourseName, GroupID
>
> Employee_Courses Table: EmployeeID, CourseID
>
> Group Table: GroupID, GroupName
***NOTE: the first attribute in each table is the foreign key***
I have three groups of courses and I need now to develop a training matrix that shows all employees and all courses with giving each group of courses a specific color such as: Blue for Group#1, Yellow for Group#2 and Orange for Group#3. I made a StoredProcedure that takes care for retrieving the data from the Database. Also, I could be able to design the matrix by using the following method: Repeater and I put inside it one GridView with a HiddenField for retrieving the ID for each group. By doing this, I got three tables or GridViews; Table for each group of courses.
What I want now is to generate one table that contains all of theses groups with giving each group of courses its color, so how to do that?
To give a simple idea about what I did, this is my code in ASP.NET and C#:
ASP.NET:
<asp:Repeater ID="Repeater1" runat="server" DataSourceID="SqlDataSource1">
<ItemTemplate>
<asp:HiddenField ID="HiddenField1" runat="server" Value=''<%# Eval("GroupID")%>'' />
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:testConnectionString %>"
SelectCommandType="StoredProcedure" SelectCommand="kbiReport"
FilterExpression="[Division] like ''{0}%''">
<FilterParameters>
<asp:ControlParameter ControlID="ddlDivision" Name="DivisionShortcut"
PropertyName="SelectedValue" Type="String" />
</FilterParameters>
<SelectParameters>
<%--ControlParameter is linked to the HiddenField above to generate different GridView based on different values
of GroupID--%>
<asp:ControlParameter ControlID="HiddenField1" Name="GroupID" PropertyName="Value" />
</SelectParameters>
</asp:SqlDataSource>
<div class="grid_4">
<asp:GridView ID="GridView1" runat="server"
AllowSorting="True"
CellPadding="3"
DataSourceID="SqlDataSource1"
ClientIDMode="Static" CssClass="myTable04"
AlternatingRowStyle-CssClass="alt"
RowStyle-HorizontalAlign="Center"
OnRowDataBound="GridView1_RowDataBound" OnPreRender="GridView1_PreRender">
<AlternatingRowStyle BackColor="White" ForeColor="#284775" />
<HeaderStyle Font-Bold = "true" ForeColor="Black" />
<Columns>
<%--<asp:CommandField ShowSelectButton="True" />--%>
</Columns>
<EditRowStyle BackColor="#999999" />
<FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
<RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
<SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
<SortedAscendingCellStyle BackColor="#E9E7E2" />
<SortedAscendingHeaderStyle BackColor="#506C8C" />
<SortedDescendingCellStyle BackColor="#FFFDF8" />
<SortedDescendingHeaderStyle BackColor="#6F8DAE" />
</asp:GridView>
</div>
<br /> <br /><br /> <br /><br /> <br /><br /> <br />
</ItemTemplate>
</asp:Repeater>
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:testConnectionString %>"
SelectCommand="SELECT DISTINCT GroupID FROM courses">
</asp:SqlDataSource>
C#:
C#:
//This function is for checking each cell in each row.
protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
{
for (int i = 1; i < 5; i++)
{
e.Row.Cells[i].CssClass = "locked";
}
//This for deleting the showing the first column (Division) when the Filter value is ALL
if (ddlDivision.SelectedValue != "%")
{
e.Row.Cells[0].Visible = false;
}
if (e.Row.RowType == DataControlRowType.DataRow)
{
foreach (TableCell c in e.Row.Cells)
{
// Check if the cell vlaue = Yes
// if it is Yes, the cell will be colored with Light Green
if (c.Text.Contains(", Yes"))
{
c.BackColor = System.Drawing.Color.LightGreen;
c.Text = "•";
}
else if (c.Text.Contains(", NO"))
{
c.Text = "";
}
}
}
// The following is for changing the color of headers in each GridView based on the value of the HiddenFild
// BTW, the value of the HiddenField is the value of the GroupID in Group Table in the Database
else if (e.Row.RowType == DataControlRowType.Header)
{
switch (((HiddenField)((GridView)sender).Parent.FindControl("HiddenField1")).Value)
{
case "1":
for (int i = 5; i < e.Row.Cells.Count; i++)
e.Row.Cells[i].BackColor = System.Drawing.Color.LightBlue;
break;
case "2":
for (int i = 5; i < e.Row.Cells.Count; i++)
e.Row.Cells[i].BackColor = System.Drawing.Color.LightYellow;
break;
case "3":
for (int i = 5; i < e.Row.Cells.Count; i++)
e.Row.Cells[i].BackColor = System.Drawing.Color.Orange;
break;
}
}
}
protected void GridView1_PreRender(object sender, EventArgs e)
{
var gv = sender as GridView;
if (gv.Rows.Count > 0)
{
gv.UseAccessibleHeader = true;
gv.HeaderRow.TableSection = TableRowSection.TableHeader;
}
}
推荐答案
ConnectionStrings:testConnectionString%> SelectCommandType ="StoredProcedure" SelectCommand ="kbiReport" FilterExpression ="[Division] like``{0}%''"> < FilterParameters> < asp:ControlParameter ControlID ="ddlDivision" Name ="DivisionShortcut" PropertyName ="SelectedValue" Type ="String"/> </FilterParameters> < SelectParameters> <%-ControlParameter链接到上面的HiddenField,以基于不同的值生成不同的GridView 的GroupID-%> < asp:ControlParameter ControlID ="HiddenField1" Name ="GroupID" PropertyName ="Value"/> </SelectParameters> </asp:SqlDataSource> < div class ="grid_4"> < asp:GridView ID ="GridView1" runat ="server" AllowSorting =真" CellPadding ="3" DataSourceID ="SqlDataSource1" ClientIDMode ="Static" CssClass ="myTable04" AlternatingRowStyle-CssClass ="alt" RowStyle-HorizontalAlign ="Center" OnRowDataBound ="GridView1_RowDataBound" OnPreRender ="GridView1_PreRender"> < AlternatingRowStyle BackColor ="White" ForeColor =#284775"/> < HeaderStyle Font-Bold ="true" ForeColor ="Black"/> <列> <%-< asp:CommandField ShowSelectButton ="True"/>-%> </列> < EditRowStyle BackColor =#999999"/> < FooterStyle BackColor =#5D7B9D" Font-Bold ="True" ForeColor ="White"/> < PagerStyle BackColor =#284775" ForeColor ="White" HorizontalAlign ="Center"/> < RowStyle BackColor =#F7F6F3" ForeColor =#333333"/> < SelectedRowStyle BackColor =#E2DED6" Font-Bold ="True" ForeColor =#333333"/> < SortedAscendingCellStyle BackColor =#E9E7E2"/> < SortedAscendingHeaderStyle BackColor =#506C8C"/> < SortedDescendingCellStyle BackColor =#FFFDF8"/> < SortedDescendingHeaderStyle BackColor =#6F8DAE"/> </asp:GridView> </div> < br/> < br/>< br/> < br/>< br/> < br/>< br/> < br/> </ItemTemplate> </asp:Repeater> < asp:SqlDataSource ID ="SqlDataSource1" runat =服务器" ConnectionString =<%
ConnectionStrings:testConnectionString %>" SelectCommandType="StoredProcedure" SelectCommand="kbiReport" FilterExpression="[Division] like ''{0}%''"> <FilterParameters> <asp:ControlParameter ControlID="ddlDivision" Name="DivisionShortcut" PropertyName="SelectedValue" Type="String" /> </FilterParameters> <SelectParameters> <%--ControlParameter is linked to the HiddenField above to generate different GridView based on different values of GroupID--%> <asp:ControlParameter ControlID="HiddenField1" Name="GroupID" PropertyName="Value" /> </SelectParameters> </asp:SqlDataSource> <div class="grid_4"> <asp:GridView ID="GridView1" runat="server" AllowSorting="True" CellPadding="3" DataSourceID="SqlDataSource1" ClientIDMode="Static" CssClass="myTable04" AlternatingRowStyle-CssClass="alt" RowStyle-HorizontalAlign="Center" OnRowDataBound="GridView1_RowDataBound" OnPreRender="GridView1_PreRender"> <AlternatingRowStyle BackColor="White" ForeColor="#284775" /> <HeaderStyle Font-Bold = "true" ForeColor="Black" /> <Columns> <%--<asp:CommandField ShowSelectButton="True" />--%> </Columns> <EditRowStyle BackColor="#999999" /> <FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" /> <PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" /> <RowStyle BackColor="#F7F6F3" ForeColor="#333333" /> <SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" /> <SortedAscendingCellStyle BackColor="#E9E7E2" /> <SortedAscendingHeaderStyle BackColor="#506C8C" /> <SortedDescendingCellStyle BackColor="#FFFDF8" /> <SortedDescendingHeaderStyle BackColor="#6F8DAE" /> </asp:GridView> </div> <br /> <br /><br /> <br /><br /> <br /><br /> <br /> </ItemTemplate> </asp:Repeater> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%
ConnectionStrings:testConnectionString%>" SelectCommand =从课程中选择DISTINCT GroupID"> </asp:SqlDataSource>
ConnectionStrings:testConnectionString %>" SelectCommand="SELECT DISTINCT GroupID FROM courses"> </asp:SqlDataSource>
C#:
C#:
//This function is for checking each cell in each row.
protected void GridView1_RowDataBound(object sender, GridViewRowEventArgs e)
{
for (int i = 1; i < 5; i++)
{
e.Row.Cells[i].CssClass = "locked";
}
//This for deleting the showing the first column (Division) when the Filter value is ALL
if (ddlDivision.SelectedValue != "%")
{
e.Row.Cells[0].Visible = false;
}
if (e.Row.RowType == DataControlRowType.DataRow)
{
foreach (TableCell c in e.Row.Cells)
{
// Check if the cell vlaue = Yes
// if it is Yes, the cell will be colored with Light Green
if (c.Text.Contains(", Yes"))
{
c.BackColor = System.Drawing.Color.LightGreen;
c.Text = "•";
}
else if (c.Text.Contains(", NO"))
{
c.Text = "";
}
}
}
// The following is for changing the color of headers in each GridView based on the value of the HiddenFild
// BTW, the value of the HiddenField is the value of the GroupID in Group Table in the Database
else if (e.Row.RowType == DataControlRowType.Header)
{
switch (((HiddenField)((GridView)sender).Parent.FindControl("HiddenField1")).Value)
{
case "1":
for (int i = 5; i < e.Row.Cells.Count; i++)
e.Row.Cells[i].BackColor = System.Drawing.Color.LightBlue;
break;
case "2":
for (int i = 5; i < e.Row.Cells.Count; i++)
e.Row.Cells[i].BackColor = System.Drawing.Color.LightYellow;
break;
case "3":
for (int i = 5; i < e.Row.Cells.Count; i++)
e.Row.Cells[i].BackColor = System.Drawing.Color.Orange;
break;
}
}
}
protected void GridView1_PreRender(object sender, EventArgs e)
{
var gv = sender as GridView;
if (gv.Rows.Count > 0)
{
gv.UseAccessibleHeader = true;
gv.HeaderRow.TableSection = TableRowSection.TableHeader;
}
}
为创建视图会生成一个包含所有这些组的表,并为每组课程赋予其颜色
这篇关于如何用三组课程(或多个表)来开发一个表?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!