ADO.NET_02_DataTable

本文内容

  • 行过滤 Select
  • 复制 Copy / 克隆 Clone
  • 行计算 Compute
  • 表关系 Relations
  • 表合并 Merge
  • 列过滤 ToTable
  • 行状态过滤 RowStateFilter

 

行过滤 Select

<%@ Page Language="C#" %>
 
<%@ Import Namespace="System.Data" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
 
    <script runat="server">
   1:  
   2:         protected void Page_Load(object sender, EventArgs e)
   3:         {
   4:             DataTable dt = ADODotNet.DS.CreateEmpDataTable();
   5:  
   6:             #region 无参的Select
   7:             DataRow[] rows = dt.Select();
   8:             for (int i = 0; i < rows.Length; i++)
   9:             {
  10:                 Response.Write(rows[i]["empno"] + " " + rows[i]["ename"]);
  11:             }
  12:             #endregion
  13:             #region 带一个参数的Select
  14:             string exp1 = "sal > 2000";
  15:             DataRow[] resRows1 = dt.Select(exp1);
  16:             for (int i = 0; i < resRows1.Length; i++)
  17:             {
  18:                 Response.Write(resRows1[i][0]);
  19:             }
  20:             #endregion
  21:             #region 带两个参数的Select
  22:             string exp2 = "sal > 2000";
  23:             string sortOrder = "empno ASC";
  24:             DataRow[] resRows2 = dt.Select(exp2, sortOrder);
  25:             for (int i = 0; i < resRows2.Length; i++)
  26:             {
  27:                 Response.Write(resRows2[i][0]);
  28:             }
  29:             #endregion
  30:             Response.Write("</br>");
  31:             #region 带三个参数的Select
  32:             DataTable myempDt = new DataTable("myemp");
  33:             myempDt.Columns.Add("empno", typeof(int));
  34:             myempDt.Columns.Add("ename", typeof(string));
  35:             myempDt.Columns["empno"].Unique = true;
  36:             myempDt.PrimaryKey = new DataColumn[] { myempDt.Columns["empno"] };
  37:             for (int id = 1; id <= 10; id++)
  38:             {
  39:                 myempDt.Rows.Add(new object[] { id, string.Format("myemp_{0}", id) });
  40:             }
  41:             myempDt.AcceptChanges();
  42:             for (int id = 11; id <= 20; id++)
  43:             {
  44:                 myempDt.Rows.Add(new object[] { id, string.Format("myemp_{0}", id) });
  45:             }
  46:             string expression = "empno > 10";
  47:             string sort = "ename ASC";
  48:             DataRow[] foundRows = myempDt.Select(expression, sort, DataViewRowState.Added);
  49:             PrintRows(foundRows, "过滤的行。");
  50:             foundRows = myempDt.Select();
  51:             PrintRows(foundRows, "所有行。");
  52:             #endregion
  53:         }
  54:         private void PrintRows(DataRow[] rows, string label)
  55:         {
  56:             Response.Write(label + "<br />");
  57:             if (rows.Length <= 0)
  58:             {
  59:                 Response.Write("没有此行。");
  60:                 return;
  61:             }
  62:             foreach (DataRow row in rows)
  63:             {
  64:                 foreach (DataColumn column in row.Table.Columns)
  65:                 {
  66:                     Response.Write(row[column] + " ");
  67:                 }
  68:             }
  69:             Response.Write("<br />");
  70:         }
  71:     
</script>
 
</head>
<body>
    <form id="form1" runat="server">
    <div>
    </div>
    </form>
</body>
</html>
说明

1) 该方法将返回获取DataRow对象的数组，这个对象数组相当于一个二维数组；

2) 该方法有四个重载的函数：

Select() 获取所有 DataRow 对象的数组。 

Select(String) 按照主键顺序（如果没有主键，则按照添加顺序）获取与筛选条件相匹配的所有 DataRow 对象的数组。 

Select(String, String) 获取按照指定的排序顺序且与筛选条件相匹配的所有 DataRow 对象的数组。 

Select(String, String, DataViewRowState) 获取与排序顺序中的筛选器以及指定的状态相匹配的所有 DataRow 对象的数组。

 

赋值 Copy / 克隆 Clone

<%@ Page Language="C#" %>
 
<%@ Import Namespace="System.Data" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
 
    <script runat="server">
   1:  
   2:         protected void Page_Load(object sender, EventArgs e)
   3:         {
   4:             DataTable dt = ADODotNet.DS.CreateEmpDataTable();
   5:             #region 克隆 只复制表结构
   6:             DataTable cloneTable;
   7:             cloneTable = dt.Clone();
   8:             Response.Write("克隆后的DataTable的相关信息：<br />");
   9:             Response.Write("克隆后的DataTable的行数：" + cloneTable.Rows.Count + "<br />");
  10:             Response.Write("克隆后的DataTable的表名：" + cloneTable.TableName + "<br />");
  11:  
  12:             this.PrintColumns(cloneTable, "克隆后的DataTable的各列：");
  13:             this.PrintRows(cloneTable, "克隆后的DataTable的各行：");
  14:  
  15:             #endregion
  16:             #region 复制 表结构和内容行都复制
  17:             DataTable copyDataTable;
  18:             copyDataTable = dt.Copy();
  19:             Response.Write("复制后的DataTable的相关信息：<br />");
  20:             Response.Write("复制后的DataTable的行数：" + copyDataTable.Rows.Count + "<br />");
  21:             Response.Write("复制后的DataTable的表名：" + copyDataTable.TableName + "<br />");
  22:  
  23:             this.PrintColumns(copyDataTable, "复制后的DataTable的各列：");
  24:             this.PrintRows(copyDataTable, "复制后的DataTable的各行：");
  25:             #endregion
  26:         }
  27:         public void PrintRows(DataTable table, string label)
  28:         {
  29:             Response.Write(label + "<br />");
  30:             if (table.Rows.Count <= 0)
  31:             {
  32:                 Response.Write("没有数据.");
  33:                 return;
  34:             }
  35:             foreach (DataRow dr in table.Rows)
  36:             {
  37:                 Response.Write(dr[0].ToString() + ", " + dr[1].ToString() + "," + dr[2].ToString() + "<br />");
  38:             }
  39:             Response.Write("<br />");
  40:         }
  41:         public void PrintColumns(DataTable table, string label)
  42:         {
  43:             Response.Write(label + "<br />");
  44:             for (int i = 0; i < table.Columns.Count; i++)
  45:             {
  46:                 Response.Write(table.Columns[i].ColumnName + " <br />");
  47:             }
  48:             Response.Write("<br />");
  49:         }
  50:     
</script>
 
</head>
<body>
    <form id="form1" runat="server">
    <div>
    </div>
    </form>
</body>
</html>
说明

1) 本例演示了 DataTable 的 Clone 和 Copy 方法。

2) 它们的区别是 Copy 和 Clone 方法都创建与原来 DataTable 对象相同的表结构，但 Copy 方法还创建与原始表相同的 DataRows 集（数据行），而Clone方法创建的新 DataTable 不包含任何 DataRows；

3) 也就是说，Clone 方法只创建与原来 DataTable 对象相同的表结构，而 Copy 方法除了表结构还有数据。

 

行计算 Compute

<%@ Page Language="C#" %>
 
<%@ Import Namespace="System.Data" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
 
    <script runat="server">
   1:  
   2:         protected void Page_Load(object sender, EventArgs e)
   3:         {
   4:             DataTable dt = ADODotNet.DS.CreateEmpDataTable();
   5:             object sumObj;
   6:             sumObj = dt.Compute("Sum(sal)", "empno>50");
   7:             Response.Write(sumObj.ToString());
   8:         }
   9:     
</script>
 
</head>
<body>
    <form id="form1" runat="server">
    <div>
    </div>
    </form>
</body>
</html>
说明

1) DataTable.Compute方法的函数原型为：public Object Compute(string expression, string filter)，第一参数是要计算的表达式，需要聚合函数，第二个参数是要限制在表达式中进行计算的行的筛选器，确定在表达式中使用哪些行；

2) 这个方法相当于在SQL语句中使用单行多行函数。

3) 说实在的，实际项目中，Compute方法我的还真不多。原因是只要是处理是DataTable中的数据，就不会太简单。

 

表关系 Relations

<%@ Page Language="C#" %>
 
<%@ Import Namespace="System.Data" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
 
    <script runat="server">
   1:  
   2:         private System.Data.DataSet dataSet = new DataSet();
   3:         private void MakeDataTables()
   4:         {
   5:             MakeParentTable();
   6:             MakeChildTable();
   7:             MakeDataRelation();
   8:             BindToDataGrid();
   9:         }
  10:         #region 创建父表
  11:         private void MakeParentTable()
  12:         {
  13:             System.Data.DataTable table = new DataTable("ParentTable");
  14:             DataColumn column;
  15:             DataRow row;
  16:             // 创建两个列，并添加到表
  17:             column = new DataColumn();
  18:             column.DataType = System.Type.GetType("System.Int32");
  19:             column.ColumnName = "id";
  20:             column.ReadOnly = true;
  21:             column.Unique = true;
  22:             table.Columns.Add(column);
  23:             column = new DataColumn();
  24:             column.DataType = System.Type.GetType("System.String");
  25:             column.ColumnName = "ParentItem";
  26:             column.AutoIncrement = false;
  27:             column.Caption = "ParentItem";
  28:             column.ReadOnly = false;
  29:             column.Unique = false;
  30:             table.Columns.Add(column);
  31:             // 设置主键
  32:             DataColumn[] PrimaryKeyColumns = new DataColumn[1];
  33:             PrimaryKeyColumns[0] = table.Columns["id"];
  34:             table.PrimaryKey = PrimaryKeyColumns;
  35:             dataSet.Tables.Add(table);
  36:             for (int i = 0; i <= 2; i++)
  37:             {
  38:                 row = table.NewRow();
  39:                 row["id"] = i;
  40:                 row["ParentItem"] = "ParentItem_" + i;
  41:                 table.Rows.Add(row);
  42:             }
  43:             Response.Write("ID 父项");
  44:             foreach (DataRow dr in table.Rows)
  45:             {
  46:                 Response.Write(dr["id"].ToString() + " " + dr["ParentItem"].ToString() + "</br>");
  47:             }
  48:         }
  49:         #endregion
  50:         #region 创建子表
  51:         private void MakeChildTable()
  52:         {
  53:             DataTable table = new DataTable("childTable");
  54:             DataColumn column;
  55:             DataRow row;
  56:             // 创建列并添加到表
  57:             column = new DataColumn();
  58:             column.DataType = System.Type.GetType("System.Int32");
  59:             column.ColumnName = "ChildID";
  60:             column.AutoIncrement = true;
  61:             column.Caption = "ID";
  62:             column.ReadOnly = true;
  63:             column.Unique = true;
  64:             table.Columns.Add(column);
  65:             column = new DataColumn();
  66:             column.DataType = System.Type.GetType("System.String");
  67:             column.ColumnName = "ChildItem";
  68:             column.AutoIncrement = false;
  69:             column.Caption = "ChildItem";
  70:             column.ReadOnly = false;
  71:             column.Unique = false;
  72:             table.Columns.Add(column);
  73:             column = new DataColumn();
  74:             column.DataType = System.Type.GetType("System.Int32");
  75:             column.ColumnName = "ParentID";
  76:             column.AutoIncrement = false;
  77:             column.Caption = "ParentID";
  78:             column.ReadOnly = false;
  79:             column.Unique = false;
  80:             table.Columns.Add(column);
  81:             dataSet.Tables.Add(table);
  82:             // Create three sets of DataRow objects, 
  83:             // five rows each, and add to DataTable.
  84:             for (int i = 0; i <= 4; i++)
  85:             {
  86:                 row = table.NewRow();
  87:                 row["childID"] = i;
  88:                 row["ChildItem"] = "Item " + i;
  89:                 row["ParentID"] = 0;
  90:                 table.Rows.Add(row);
  91:             }
  92:             for (int i = 0; i <= 4; i++)
  93:             {
  94:                 row = table.NewRow();
  95:                 row["childID"] = i + 5;
  96:                 row["ChildItem"] = "Item " + i;
  97:                 row["ParentID"] = 1;
  98:                 table.Rows.Add(row);
  99:             }
 100:             for (int i = 0; i <= 4; i++)
 101:             {
 102:                 row = table.NewRow();
 103:                 row["childID"] = i + 10;
 104:                 row["ChildItem"] = "Item " + i;
 105:                 row["ParentID"] = 2;
 106:                 table.Rows.Add(row);
 107:             }
 108:             Response.Write("子ID 子项 父项");
 109:             foreach (DataRow dr in table.Rows)
 110:             {
 111:                 Response.Write(dr["childID"].ToString() + " " + dr["ChildItem"].ToString() + " " + dr["ParentID"].ToString() + "</br>");
 112:             }
 113:         }
 114:         #endregion
 115:         #region 创建关系
 116:         private void MakeDataRelation()
 117:         {
 118:             // DataRelation方法要求父表和子表的DataColumn和一个名字
 119:             DataColumn parentColumn = dataSet.Tables["ParentTable"].Columns["id"];
 120:             DataColumn childColumn = dataSet.Tables["ChildTable"].Columns["ParentID"];
 121:             DataRelation relation = new DataRelation("parent2Child", parentColumn, childColumn);
 122:             dataSet.Tables["ChildTable"].ParentRelations.Add(relation);
 123:         }
 124:         #endregion
 125:         private void BindToDataGrid()
 126:         {
 127:             GridView1.DataSource = dataSet;
 128:             GridView1.DataBind();
 129:             //dataGrid1.SetDataBinding(dataSet, "ParentTable");
 130:         }
 131:         protected void Page_Load(object sender, EventArgs e)
 132:         {
 133:             MakeDataTables();
 134:         }
 135:     
</script>
 
</head>
<body>
    <form id="form1" runat="server">
    <asp:GridView ID="GridView1" runat="server">
    </asp:GridView>
    </form>
</body>
</html>
说明

1) 首先创建一个全局的 DataSet 变量，MakeParentTable() 方法创建父表，MakeChildTable() 方法创建子表，MakeDataRelation() 方法创建父子表之间的关系（主外键关系），将父子表和关系都放到 DataSet 中，最后用 BindToDataGrid() 方法绑定到控件上。

 

表合并 Merge

<%@ Page Language="C#" %>
 
<%@ Import Namespace="System.Data" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
 
    <script runat="server">
   1:  
   2:         protected void Page_Load(object sender, EventArgs e)
   3:         {
   4:             #region 表1
   5:             DataTable table1 = new DataTable("Items");
   6:             DataColumn idColumn = new DataColumn("id", typeof(System.Int32));
   7:             DataColumn itemColumn = new DataColumn("item", typeof(System.Int32));
   8:             table1.Columns.Add(idColumn);
   9:             table1.Columns.Add(itemColumn);
  10:             table1.PrimaryKey = new DataColumn[] { idColumn };
  11:             table1.RowChanged += new System.Data.DataRowChangeEventHandler(Row_Changed);
  12:             DataRow row;
  13:             for (int i = 0; i <= 9; i++)
  14:             {
  15:                 row = table1.NewRow();
  16:                 row["id"] = i;
  17:                 row["item"] = i;
  18:                 table1.Rows.Add(row);
  19:             }
  20:             table1.AcceptChanges();
  21:             PrintValues(table1, "Original values");
  22:             #endregion
  23:             #region 表2
  24:             DataTable table2 = table1.Clone();
  25:             table2.Columns.Add("newColumn", typeof(System.String));
  26:             row = table2.NewRow();
  27:             row["id"] = 14;
  28:             row["item"] = 774;
  29:             row["newColumn"] = "new column 1";
  30:             table2.Rows.Add(row);
  31:             row = table2.NewRow();
  32:             row["id"] = 12;
  33:             row["item"] = 555;
  34:             row["newColumn"] = "new column 2";
  35:             table2.Rows.Add(row);
  36:             row = table2.NewRow();
  37:             row["id"] = 13;
  38:             row["item"] = 665;
  39:             row["newColumn"] = "new column 3";
  40:             table2.Rows.Add(row);
  41:             #endregion
  42:             Response.Write("Merging");
  43:             table1.Merge(table2, false, MissingSchemaAction.Add);
  44:             PrintValues(table1, "Merged With table1, schema added");
  45:         }
  46:         public void Row_Changed(object sender, DataRowChangeEventArgs e)
  47:         {
  48:             Response.Write("Row changed " + e.Action + " " + e.Row.ItemArray[0] + "</br>");
  49:         }
  50:         public void PrintValues(DataTable table, string label)
  51:         {
  52:             Response.Write(label);
  53:             foreach (DataRow row in table.Rows)
  54:             {
  55:                 foreach (DataColumn col in table.Columns)
  56:                 {
  57:                     Response.Write(row[col].ToString() + " ");
  58:                 }
  59:             }
  60:         }
  61:     
</script>
 
</head>
<body>
    <form id="form1" runat="server">
    <div>
    </div>
    </form>
</body>
</html>
说明

1) 创建表1，利用表1创建表2，并向表2中增加一列，此时表1和表2的表结构不同；

2) 利用MissingSchemaAction.Add将表1和表2合并；

3) DataTable.Merge方法将指定的DataTable与当前的DataTable合并，该方法有三个重载函数：

Merge(DataTable) 将指定的DataTable与当前的DataTable合并。 

Merge(DataTable, Boolean) 将指定的DataTable与当前的DataTable合并，指示是否在当前的DataTable中保留更改。 

Merge(DataTable, Boolean, MissingSchemaAction) 将指定的DataTable与当前的DataTable合并，指示是否在当前的DataTable中保留更改以及如何处理缺失的架构。

4) 合并通常是在客户端应用程序上将数据源中最近的更改合并到现有的DataTable中，使客户端应用程序能够拥有用数据源中的最新数据刷新的DataTable。

 

列过滤 ToTable

<%@ Page Language="C#" %>
 
<%@ Import Namespace="System.Data" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
 
    <script runat="server">
   1:  
   2:         protected void Page_Load(object sender, EventArgs e)
   3:         {
   4:             DataTable dt = ADODotNet.DS.CreateEmpDataTable();
   5:             DataView dv = new DataView(dt);
   6:             DataTable dtColumnFilter = dv.ToTable(false, new string[] { "empno", "ename" });
   7:             this.PrintRows(dtColumnFilter, "过滤列 \"empno\"和\"ename\"：");
   8:         }
   9:         public void PrintRows(DataTable table, string label)
  10:         {
  11:             Response.Write(label + "<br />");
  12:             if (table.Rows.Count <= 0)
  13:             {
  14:                 Response.Write("没有数据.");
  15:                 return;
  16:             }
  17:             foreach (DataRow dr in table.Rows)
  18:             {
  19:                 Response.Write(dr[0].ToString() + ", " + dr[1].ToString() + "<br />");
  20:             }
  21:             Response.Write("<br />");
  22:         }
  23:     
</script>
 
</head>
<body>
    <form id="form1" runat="server">
    <div>
    </div>
    </form>
</body>
</html>

 

行状态过滤 RowStateFilter

<%@ Page Language="C#" %>
 
<%@ Import Namespace="System.Data" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title></title>
 
    <script runat="server">
   1:  
   2:         protected void Page_Load(object sender, EventArgs e)
   3:         {
   4:             // 创建一个只有一行的表，并添加五行
   5:             DataTable table = new DataTable("table");
   6:             DataColumn colItem = new DataColumn("item", Type.GetType("System.String"));
   7:             table.Columns.Add(colItem);
   8:             DataRow NewRow;
   9:             for (int i = 0; i < 5; i++)
  10:             {
  11:                 NewRow = table.NewRow();
  12:                 NewRow["item"] = "Item_" + i;
  13:                 table.Rows.Add(NewRow);
  14:             }
  15:             // 改变表的前两行
  16:             table.Rows[0]["item"] = "cat";
  17:             table.Rows[1]["item"] = "dog";
  18:             table.AcceptChanges();
  19:             // 用这个表创建两个视图
  20:             DataView firstView = new DataView(table);
  21:             DataView secondView = new DataView(table);
  22:             // 输出表的当前值
  23:             PrintTableOrView(table, "Current Values in Table");
  24:             // 设置第一个视图只显示被修改的行，并输出
  25:             firstView.RowStateFilter = DataViewRowState.ModifiedOriginal;
  26:             PrintTableOrView(firstView, "First DataView: ModifiedOriginal");
  27:             // 向第二个视图增加一行
  28:             DataRowView rowView;
  29:             rowView = secondView.AddNew();
  30:             rowView["item"] = "fish";
  31:             // 设置第二个视图只显示被修改和增加的版本，并输出
  32:             secondView.RowStateFilter = DataViewRowState.ModifiedCurrent | DataViewRowState.Added;
  33:             PrintTableOrView(secondView, "Second DataView: ModifiedCurrent | Added");
  34:         }
  35:         private void PrintTableOrView(DataTable table, string label)
  36:         {
  37:             Response.Write(" " + label + " ");
  38:             for (int i = 0; i < table.Rows.Count; i++)
  39:             {
  40:                 Response.Write(table.Rows[i]["item"] + " ");
  41:             }
  42:         }
  43:         private void PrintTableOrView(DataView view, string label)
  44:         {
  45:             Response.Write(" " + label + " ");
  46:             for (int i = 0; i < view.Count; i++)
  47:             {
  48:                 Response.Write(view[i]["item"] + " ");
  49:             }
  50:         }
  51:     
</script>
 
</head>
<body>
    <form id="form1" runat="server">
    <div>
    </div>
    </form>
</body>
</html>

 

下载 Demo

posted @ 2011-08-22 23:30  船长&CAP  阅读(365)  评论(0)  编辑  收藏  举报
免费流量统计软件