数据库动态数据的处理+Repeater前台动态列的绑定

问题描述：

如图所示：数据库中有两张表，一张表可以根据课程id选出所有该课程的学生信息，另外一张表可以根据课程id选出该课程所有学生各次考试的成绩。由于考试次数是由老师自己动态设定的，事先并不清楚考试次数到底是多少。（.NET+Oracle网站实现）

1、首先，解决此类问题可以通过Repeater嵌套实现，对于数据量不大的时候可以这么用，但是一旦数据量稍大处理时间会变得很长，效率太差。

2、写个存储过程将所有数据整理成一张表，然后一次绑定到前台控件。

下面是该案例的存储过程：

create or replace procedure sp_get_all_cj(p_xkkh in varchar2, p_cursor out sys_refcursor)
   is
   sqlstr varchar2(32767);
   ct integer;
   begin

   sqlstr:='with cjj as ( select a.xh, b.xm, ';

   for v_cur in (select distinct id, ksmc,ksbh  from gcgl.ksxxb where xkkh = p_xkkh
     order by ksbh )
   loop
        sqlStr := sqlstr || 'sum(case ksid when '''|| v_cur.id ||''' then xscj else null end ) as c'|| v_cur.ksbh ||',';
   end loop;

   sqlstr := substr(sqlstr,0,length(sqlstr)-1) ||' from gcgl.xsksb a join gcgl.v_xsb b on a.xh = b.xh ' ||
         'where exists ( select * from gcgl.ksxxb b where a.ksid = b.id and b.xkkh = '''
         || p_xkkh || ''') group by a.xh, b.xm ) ' ;
         
         
   select count(*) into ct from gcgl.xszzcjb where xkkh = p_xkkh;
   
   if (ct >0) then--说明已经提交了
      sqlstr := sqlstr || ' select rank() over( order by b.xh) rowno, b.*, a.zpcj  '
         || ' from gcgl.xszzcjb a left join cjj b on a.xh = b.xh '
         || ' where a.xkkh = '''
         || p_xkkh 
         || ''' order by b.xh';
   else
       sqlstr := sqlstr || ' select rank() over( order by b.xh) rowno, b.*, 0 zpcj  '
         || ' from cjj b  order by b.xh';
   
   end if; 

   open p_cursor for sqlstr;

  end;

然后是以下C#语句，读出这张表，放到dataTable中

using (OleDbConnection con = new OleDbConnection(ConfigurationManager.ConnectionStrings["JSCJLR"].ToString()))//连接字串
        {

            OleDbCommand myCMD = new OleDbCommand
             ("{call gcgl.sp_get_all_cj(?, {resultset 0, p_cursor})}", con);
            myCMD.Parameters.Add("p_xkkh", OleDbType.VarChar, 100).Value = xkkh;
            
            OleDbDataAdapter oda = new OleDbDataAdapter(myCMD);

            DataSet ds = new DataSet();

            oda.Fill(ds);
            dt = ds.Tables[0];

        }

读出的表结构大致是

xh,c1,c2,c3,c4...等，其中c1,,c2...是动态列，表示的是第几次考试

接下来的问题就是，有一个动态列的表，如何将该表绑定到前台控件中。

前台：

<table id="table" cellspacing="0" summary="The technical specifications of the Apple PowerMac G5 series">
    <caption> </caption>
    
    <asp:Repeater runat="server" ID="Repeater" OnItemDataBound=" rptClass1_ItemDataBound">
        <headerTemplate>   
            <asp:Literal ID="lit_head" runat="server"></asp:Literal>
        </headertemplate>   
        <itemtemplate>   
            <asp:Literal ID="lit_item" runat="server"></asp:Literal>
        </itemtemplate>   
    </asp:Repeater>
</table>

后台：

protected void rptClass1_ItemDataBound(object sender, RepeaterItemEventArgs e)
    {
        StringBuilder sb = new StringBuilder();
        if (e.Item.ItemType == ListItemType.Header)
        {
            sb.Append("<tr>");
            for (int i = 0; i < dt.Columns.Count; i++)
            {
                DataColumn col = dt.Columns[i];
                string name = col.ColumnName;
                switch(col.ColumnName){
                    case "ROWNO": name = "序号"; break;
                    case "XH": name = "学号"; break;
                    case "XM": name = "姓名"; break;
                    case "ZPCJ": name = "总评成绩"; break;
                    default:
                        string s = col.ColumnName;
                        name = s.Substring(1, s.Length - 1);
                        break;

                }
                sb.AppendFormat("<th>{0}</th>",name );
            }
            //foreach (DataColumn col in dt.Columns)
            //{
            //    sb.AppendFormat("<th>{0}</th>", col.ColumnName);
            //}
            sb.Append("<th>平均分</th><th>最高分</th><th>最低分</th>");
            sb.Append("</tr>");
            Literal literal = (Literal)e.Item.FindControl("lit_head");
            literal.Text = sb.ToString();
        }
        else if (e.Item.ItemType == ListItemType.Item||e.Item.ItemType == ListItemType.AlternatingItem)
        {
            int index=e.Item.ItemIndex;
            sb.Append("<tr rel=''>");
            sb.AppendFormat("<td>{0}</td>", index + 1);
            for (int j = 1; j < 3; j++)
            {
                sb.AppendFormat("<td>{0}</td>", dt.Rows[index][j]);
            }
            for (int j = 3; j < dt.Columns.Count - 1; j++)
            {
                sb.AppendFormat("<td rel='cj'>{0}</td>", dt.Rows[index][j]);
            }
            for (int j = dt.Columns.Count - 1; j < dt.Columns.Count; j++)
            {
                sb.AppendFormat("<td>{0}</td>", dt.Rows[index][j]);
            }
            sb.Append("<td rel=\"pjf\"></td><td rel=\"zgf\"></td><td rel=\"zdf\"></td>");
            sb.Append("</tr>");

            Literal liter = (Literal)e.Item.FindControl("lit_item");
            liter.Text = sb.ToString();
            }
    }

自此就实现了数据的动态操作了。