上篇介绍了
交叉表的简单实现1:使用存储过程,这里采取在前端程序实现。
实现要点:
1。读取所有目标成绩(flatScroreTable)
2。从目标成绩中提取考试时间(不重复),作为交叉表的列表头
3。从目标成绩中提取考试科目(不重复),作为交叉表的行表头
4。根据2动态构建一个DataTable(crossScroreTable),此DataTable具有一个科目列,若干考试时间列,以及其他信息。
5。将3中的考试科目写入DataTable
6。从flatScroreTable中读取成绩值填入crossScroreTable对应单元格
说明:便于理解,这里将交叉表crossScroreTable想象成一张Excel表,列名就当作列表头,科目名就当作行表头。
主要代码:
void LoadScoreData()
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
{
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
读取指定学生的所有成绩#region 读取指定学生的所有成绩
string stuName = drpStu.SelectedValue;
string connStr = "SERVER=.;DATABASE=DemoLib;UID=sa";
string sql = "SELECT SubjectName, StudentName, ScoreValue, ExamDate FROM t_Score WHERE StudentName = @StuName";
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter(sql, connStr);
da.SelectCommand.CommandType = CommandType.Text;
da.SelectCommand.Parameters.Add("@StuName", stuName);
da.Fill(ds, "FlatScore");
#endregion 读取指定学生的所有成绩
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
DataTable flatScoreTable = ds.Tables[0];
DataView flatScoreView = new DataView(flatScoreTable);
// 读取科目列表,排除重复
DataTable subjectList;
// .NET 2 using the ToTable method supported by DataView
//subjectList = flatScoreView.ToTable("SubjectList", true, "SubjectName");
subjectList = SelectDistinct(flatScoreTable, "SubjectList", true, "SubjectName");
// 读取考试时间列表,排除重复
// .NET 2
DataTable examDateList;
// .NET 2 using the ToTable method supported by DataView
//examDateList = flatScoreView.ToTable("ExamDateList", true, "ExamDate");
examDateList = SelectDistinct(flatScoreTable, "ExamDateList", true, "ExamDate");
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
创建 交叉表#region 创建 交叉表
DataTable crossScoreTable = new DataTable();
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
构造表模式#region 构造表模式
// 科目 列
crossScoreTable.Columns.Add("科目", typeof(string));
// 考试时间 列,以时间为列名
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
foreach (DataRow r in examDateList.Rows)
{
crossScoreTable.Columns.Add(((DateTime)r[0]).ToString("yyyy年M月dd日"), typeof(string));
}
#endregion 构造表模式
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedSubBlock.gif)
从成绩表读取并写入数据#region 从成绩表读取并写入数据
DataColumnCollection cols = crossScoreTable.Columns;
// 按先科目(行)后时间(列)遍历
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
foreach (DataRow r in subjectList.Rows)
{
DataRow newRow = crossScoreTable.NewRow();
// 科目
newRow[0] = r[0];
// 成绩,遍历时间
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
for(int i=1; i< cols.Count; i++)
{
// 返回具有指定科目和指定考试的成绩
// eg. "SubjectName='孙光' AND ExamDate='2007-5-19'"
flatScoreView.RowFilter = String.Format("SubjectName='{0}' AND ExamDate='{1}'", r[0], examDateList.Rows[i-1][0]);
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
if (flatScoreView.Count > 0)
{
newRow[cols[i]] = flatScoreView[0]["ScoreValue"];
}
}
// 加入新表
crossScoreTable.Rows.Add(newRow);
}
#endregion 从成绩表读取并插入数据
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
#endregion 创建 交叉表
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
// 绑定,输出数据
grdCrossScore.DataSource = crossScoreTable;
grdCrossScore.DataBind();
grdFlatScore.DataSource = flatScoreTable;
grdFlatScore.DataBind();
grdSubjectList.DataSource = subjectList;
grdSubjectList.DataBind();
grdExamDate.DataSource = examDateList;
grdExamDate.DataBind();
}
看下这个方法:
DataTable SelectDistinct(DataTable sourceTable, string newTableName, bool distinct, params string[] columnNames)
扩展 ADO.NET 1.x 中DataTable 以及 DataView 均无法支持的:SELECT DISTINCT Column1,Column2.... 语法,(选择不重复的行)
如果 ADO.NET 2.0 中我们就可以直接使用 DataView 的 ToTable 方法的重载版本:
public DataTable ToTable(bool distinct, params string[] columnNames);
![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
public DataTable ToTable(string tableName, bool distinct, params string[] columnNames);
SelectDistinct 网上有很多版本,原理基本一样,只是效率问题,这里是修改于 Erik Porter 的
Select DISTINCT on DataTable。
SelectDistinct完整代码:
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedBlockStart.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
implements such as 'SELECT DISTINCT Column0, Column1
' sql clause for DataTable.#region implements such as 'SELECT DISTINCT Column0, Column1
' sql clause for DataTable.
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
/**//*
* The implement detail refers to http://weblogs.asp.net/eporter/archive/2005/02/10/370548.aspx. Many thanks to Erik Porter.
* NOTE:The .NET 2.0's DataView provider a ToTable method that will build a DataTable based off of the current DataView and
* also allow you to specify which columns you want in the DataTable as all as being able to say Distinct rows only.
* So, recommends to consume the override method directly as following:
* <example>
* DataTable srcTable;
* // ![](https://www.cnblogs.com/Images/dot.gif)
* DataView srcView = srcTable.DefaultView;
* DataTable dstTable = srcView.ToTable("NewTableName", true, "ColumnName1", "ColumnName2", "ColumnNameN");
* </example>
*/
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
private static DataTable SelectDistinct(DataTable sourceTable, string newTableName, bool distinct, params string[] columnNames)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
object[] lastValues;
DataTable newTable;
DataRow[] orderedRows;
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
if (columnNames == null || columnNames.Length == 0)
throw new ArgumentNullException("columnNames");
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
lastValues = new object[columnNames.Length];
newTable = new DataTable();
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
for (int i = 0; i < columnNames.Length; i++)
{
DataColumn column = sourceTable.Columns[columnNames[i]];
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
if (column == null)
{
throw new ArgumentException(
String.Format("The specified column (columnNames[{0}] with value of '{1}') is not in the sourceTable.", i.ToString(), columnNames[i]),
"columnNames");
}
newTable.Columns.Add(columnNames[i], column.DataType);
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
orderedRows = sourceTable.Select("", string.Join(", ", columnNames));
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
if (distinct)
{
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
foreach (DataRow row in orderedRows)
{
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
if (!ColumnValuesAreEqual(lastValues, row, columnNames))
{
newTable.Rows.Add(CreateRowClone(row, newTable.NewRow(), columnNames));
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
SetLastValues(lastValues, row, columnNames);
}
}
}
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
else
{
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
foreach (DataRow row in orderedRows)
{
newTable.Rows.Add(CreateRowClone(row, newTable.NewRow(), columnNames));
}
}
return newTable;
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
private static bool ColumnValuesAreEqual(object[] lastValues, DataRow currentRow, string[] columnNames)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
bool areEqual = true;
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
for (int i = 0; i < columnNames.Length; i++)
{
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
if (lastValues[i] == null || !lastValues[i].Equals(currentRow[columnNames[i]]))
{
areEqual = false;
break;
}
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
return areEqual;
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
private static DataRow CreateRowClone(DataRow sourceRow, DataRow newRow, string[] columnNames)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
foreach (string field in columnNames)
{
newRow[field] = sourceRow[field];
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
return newRow;
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
private static void SetLastValues(object[] lastValues, DataRow sourceRow, string[] columnNames)
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
{
![](https://www.cnblogs.com/Images/OutliningIndicators/ExpandedSubBlockStart.gif)
for (int i = 0; i < columnNames.Length; i++)
{
lastValues[i] = sourceRow[columnNames[i]];
}
}
![](https://www.cnblogs.com/Images/OutliningIndicators/InBlock.gif)
#endregion
说明:由于朋友要求是在 1.1 环境下,所以只有自己写SelectDistinct,只有用DataGrid演示了^_^
源码:
下载