今天遇到了这样一个需求,移除掉以某列某个字符串开头的行。首先想到是如果数据存在数据库中,只需要在SQL语句使用LIKE关键字即可!那么能不能在DataTable中也使用参数的形式传入表达式呢?想想觉得应该可以,于是乎去查找帮助文档,一不小心看到了Select()方法:获取DataRow对象的数组。
Select()有4个重载方法
DataTable.Select()
获取所有DataRow对象的数组。
DataTable.Select(String)
按照主键顺序(如果没有主键,则按照添加顺序)获取与筛选条件相匹配的所有DataRow对象的数组。
DataTable.Select(String, String)
获取按照指定的排序顺序且与筛选条件相匹配的所有DataRow的对象数组。
DataTable.Select(String, String, DataViewRowState)
获取与排序顺序中的筛选器以及指定的状态相匹配的所有DataRow对象的数组。
foreach (string strCorp in listCorpID) { expression = "TagName like ''" + strCorp + "'%'"; DataRow[] foundRows = dtBadTags.Select(expression); foreach (DataRow row in foundRows) { dtBadTags.Rows.Remove(row); } }
这个代码片段首先遍历一个List的集合,然后筛选以地区公司编码开头的字符串,最后移除这些记录。看似复杂的问题,就几行代码把它搞定了。
-----------------------------------------------------------------------------------------------------------------------------------------------------------
1、向数据表中添加列
DataTable workTable = new DataTable("Customers"); DataColumn workCol = workTable.Columns.Add("CustID", typeof(Int32)); workCol.AllowDBNull = false; workCol.Unique = true; workTable.Columns.Add("CustLName", typeof(String)); workTable.Columns.Add("CustFName", typeof(String)); workTable.Columns.Add("Purchases", typeof(Double));
2、向数据表中添加数据
DataTable workTable = new DataTable("Customers"); DataColumn workCol = workTable.Columns.Add("CustID", typeof(Int32)); workCol.AllowDBNull = false; workCol.Unique = true; workTable.Columns.Add("CustName", typeof(String)); DataRow workRow; for (int i = 0; i <= 9;i++ ) { workRow = workTable.NewRow(); workRow[0] = i; workRow[1] = "CustName" + i.ToString(); workTable.Rows.Add(workRow); } foreach(DataRow row in workTable.Rows) { foreach(DataColumn column in workTable.Columns) { Console.Write(row[column] + " "); } Console.WriteLine(); }
3、创建表并显示
using System; using System.Collections.Generic; using System.Linq; using System.Web; using System.Web.UI; using System.Web.UI.WebControls; using System.Data; public partial class _Default : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { MakeDataTables(); } DataSet dataSet; private void MakeParentTable() { DataTable table = new DataTable("ParentTable"); DataRow row; DataColumn column; column = new DataColumn(); column.DataType = Type.GetType("System.Int32"); column.ColumnName = "id"; column.ReadOnly = true; column.Unique = true; table.Columns.Add(column); column = new DataColumn(); column.DataType = Type.GetType("System.String"); column.ColumnName = "ParentItem"; column.AutoIncrement = false; column.Caption = "ParentItem"; column.ReadOnly = false; column.Unique = false; table.Columns.Add(column); DataColumn[] PrimaryKeyColumns = new DataColumn[1]; PrimaryKeyColumns[0] = table.Columns["id"]; table.PrimaryKey = PrimaryKeyColumns; dataSet = new DataSet(); dataSet.Tables.Add(table); for (int i = 0; i <= 2; i++) { row = table.NewRow(); row["id"] = i; row["ParentItem"] = "ParentItem " + i; table.Rows.Add(row); } } private void MakeChildTable() { DataTable table = new DataTable("ChildTable"); DataRow row; DataColumn column; column = new DataColumn(); column.DataType = Type.GetType("System.Int32"); column.ColumnName = "ChildID"; column.AutoIncrement = true; column.Caption = "ID"; column.ReadOnly = true; column.Unique = true; table.Columns.Add(column); column = new DataColumn(); column.DataType = Type.GetType("System.String"); column.ColumnName = "ChildItem"; column.AutoIncrement = false; column.Caption = "ChildItem"; column.ReadOnly = false; column.Unique = false; table.Columns.Add(column); column = new DataColumn(); column.DataType = Type.GetType("System.Int32"); column.ColumnName = "ParentID"; column.AutoIncrement = false; column.Caption = "ParentID"; column.ReadOnly = false; column.Unique = false; table.Columns.Add(column); dataSet.Tables.Add(table); for (int i = 0; i <= 4; i++) { row = table.NewRow(); row["ChildID"] = i; row["ChildItem"] = "Item " + i; row["ParentID"] = 0; table.Rows.Add(row); } for (int i = 0; i <= 4; i++) { row = table.NewRow(); row["ChildID"] = i + 5; row["ChildItem"] = "Item " + i; row["ParentID"] = 1; table.Rows.Add(row); } for (int i = 0; i <= 4; i++) { row = table.NewRow(); row["ChildID"] = i + 10; row["ChildItem"] = "Item " + i; row["ParentID"] = 2; table.Rows.Add(row); } } private void MakeDataRelation() { DataColumn parentColumn = dataSet.Tables["ParentTable"].Columns["id"]; DataColumn childColumn = dataSet.Tables["ChildTable"].Columns["ParentID"]; DataRelation relation = new DataRelation("parent2Child", parentColumn, childColumn); dataSet.Tables["ChildTable"].ParentRelations.Add(relation); } private void BindToDataGrid() { GridView1.DataSource = dataSet.Tables[1]; GridView1.DataBind(); } private void MakeDataTables() { MakeParentTable(); MakeChildTable(); MakeDataRelation(); BindToDataGrid(); } }
4、AcceptChanges()方法
提交至上次调用AcceptChanges以来对该表进行的所有修改。
private void AcceptOrReject(DataTable table) { if (table.HasErrors) { if (Reconcile(table)) { table.AcceptChanges(); } else { table.RejectChanges(); } } else table.AcceptChanges(); } private bool Reconcile(DataTable thisTable) { foreach (DataRow row in thisTable.Rows) { if (row.HasErrors) return false; } return true; }
5、Copy()和Clone()方法
Copy和Clone方法创建的新DataTable都与原始DataTable具有相同的结构。Copy方法创建的新DataTable与原始表具有相同的DataRows集,但Clone方法创建的新DataTable不包含任何DataRows。
6、CreateDataReader()方法
返回与此DataTable中的数据相对应的DataTableReader。
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data; namespace ConsoleApplication3 { class Program { static void Main(string[] args) { TestCreateDataReader(GetCustomers()); } private static DataTable GetCustomers() { DataTable table = new DataTable(); DataColumn idColumn = table.Columns.Add("ID", typeof(int)); table.Columns.Add("Name", typeof(string)); table.PrimaryKey = new DataColumn[] { idColumn }; table.Rows.Add(new object[] { 1, "Mary" }); table.Rows.Add(new object[] { 2, "Andy" }); table.Rows.Add(new object[] { 3, "Peter" }); table.Rows.Add(new object[] { 4, "Russ" }); return table; } private static void PrintColumns(DataTableReader reader) { while (reader.Read()) { for (int i = 0; i < reader.FieldCount; i++) { Console.Write(reader[i] + " "); } Console.WriteLine(); } } private static void TestCreateDataReader(DataTable dt) { using (DataTableReader reader = dt.CreateDataReader()) { do { if (!reader.HasRows) { Console.WriteLine("Empty DataTableReader"); } else { PrintColumns(reader); } } while (reader.NextResult()); } } } }
7、Merge()方法
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data; namespace ConsoleApplication3 { class Program { static void Main(string[] args) { DemonstrateMergeTable(); } private static void DemonstrateMergeTable() { DataTable table1 = new DataTable("Items"); DataColumn idColumn = new DataColumn("id", typeof(System.Int32)); DataColumn itemColumn = new DataColumn("item", typeof(System.Int32)); table1.Columns.Add(idColumn); table1.Columns.Add(itemColumn); table1.PrimaryKey = new DataColumn[] { idColumn }; table1.RowChanged += new DataRowChangeEventHandler(Row_Changed); DataRow row; for (int i = 0; i <= 9; i++) { row = table1.NewRow(); row["id"] = i; row["item"] = i; table1.Rows.Add(row); } table1.AcceptChanges(); PrintValues(table1, "Original values"); DataTable table2 = table1.Clone(); table2.Columns.Add("newColumn", typeof(System.String)); row = table2.NewRow(); row["id"] = 14; row["item"] = 774; row["newColumn"] = "new column 1"; table2.Rows.Add(row); row = table2.NewRow(); row["id"] = 12; row["item"] = 555; row["newColumn"] = "new column 2"; table2.Rows.Add(row); row = table2.NewRow(); row["id"] = 13; row["item"] = 665; row["newColumn"] = "new column 3"; table2.Rows.Add(row); Console.WriteLine("Merging"); table1.Merge(table2, false, MissingSchemaAction.Add); PrintValues(table1, "Merged With table1, schema added"); } private static void Row_Changed(object sender, DataRowChangeEventArgs e) { Console.WriteLine("Row changed {0}/t{1}", e.Action, e.Row.ItemArray[0]); } private static void PrintValues(DataTable table, string label) { Console.WriteLine(label); foreach (DataRow row in table.Rows) { foreach (DataColumn col in table.Columns) { Console.Write("/t " + row[col].ToString()); } Console.WriteLine(); } } } }
这是今天的一点点收获,让我感触最深刻莫过于:扩散思维。也许今天遇到的不是Select()方法的问题,还可能是DataTable其他的方法。如果不深入学习,一知半解,很可能下次还是不会!