从SQL Server表总标准的HTML表格 [英] Standard HTML table with total from SQL Server table

问题描述

请参考以下链接： SqlDataReader的GetDateTime格式

结果应该是：

ID     Number01     TheDate     Number02
----------------------------------------
1      10           01/06/2014  5
2      20           02/06/2014  10
3      30           03/06/2014  15

有没有有人能告诉我我该怎么总在包含一些数据类型的每一列？其总数应该放在桌子上的页脚。

Is there someone can tell me how I can put the total on each column that contains a number datatype? The total should be on the footer of the table.

例如，1躯，是 SUM（number02），然后在第二躯，是平均..
AVG（number02）。

For example, 1st footer, is the SUM(number02), then on the 2nd footer, is the average.. AVG(number02).

所以我可以说，多页脚。

So I can say, multiple footer.

结果应该是：

ID     Number01     TheDate     Number02
----------------------------------------
1      10           01/06/2014  5
2      20           02/06/2014  10
3      30           03/06/2014  15
TOTAL  60           -           30
AVE    20           -           10

请帮忙。谢谢你。

推荐答案

您可以使用此HTML

you can use this html

<tfoot>
    <tr>
      <td>Tot</td>
      <td>60</td>
      <td></td>
      <td>30</td>
    </tr>
    <tr>
       <td>Avg</td>
       <td>20</td>
       <td></td>
       <td>10</td>
    </tr>
  </tfoot>

这在表的末尾添加两行。

this add two lines at the end of the table.

要计算总与avarage
在定义

to calculate the total and avarage in the definitions

int totnum1 = 0;
decimal totnum2 = 0;
int numRow = 0;
decimal avg1 = 0;
decimal avg2 = 0;

在环

totnum1 += reader.GetInt32(1);
totnum2 += reader.GetInt32(3);
numRow ++;

在循环结束

avg1 = totnum1 / numRow;
avg2 = totnum2 / numRow;

可以作为在最后一个问题使用totnum1，totnum2 AVG1和AVG2就位在示例的数目的上述

you could write the html as in the last question using totnum1, totnum2 avg1 and avg2 in place of the number in the example above

public string getWhileLoopData() 
{
 string htmlStr = "";
 SqlConnection thisConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString);
 SqlCommand thisCommand = thisConnection.CreateCommand();
 thisCommand.CommandText = "SELECT * FROM MyTable WHERE TheDate = @TheDate";
 thisCommand.Parameters.AddWithValue("@TheDate", txtDate.Text);


int totnum1 = 0;
decimal totnum2 = 0;
int numRow = 0;
decimal avg1 = 0;
decimal avg2 = 0;



 thisConnection.Open();
 SqlDataReader reader = thisCommand.ExecuteReader();

 while (reader.Read()) {
     int id = reader.GetInt32(0);

     int Number01 = reader.GetInt32(1);
     DateTime TheDate = reader.GetDateTime(2);
     Decimal Number02 = reader.GetDecimal(3);

     totnum1 += reader.GetInt32(1);
     totnum2 += reader.GetInt32(3);
     numRow ++;

     //string Pass = reader.GetString(2);
     htmlStr += "<tr><td>" + id + "</td><td>" + Number01 + "</td><td>" + TheDate + "</td><td>" + Number02 + "</td></tr>";
 }

 thisConnection.Close();

avg1 = totnum1 / numRow;
avg2 = totnum2 / numRow;

htmlStr += string.Format("<tfoot><tr><td>Tot</td><td>{0}</td><td></td><td>{1}</td></tr>", totnum1 , totnum2 );
htmlStr += string.Format("<tfoot><tr><td>Avg</td><td>{0}</td><td></td><td>{1}</td></tr></tfoot>", avg1 , avg2 );


 return htmlStr;
}

这篇关于从SQL Server表总标准的HTML表格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！