DataTable中的Select()方法以及对DataTable的探索与发现

本文介绍如何使用DataTable进行数据筛选、添加及合并等高级操作,并演示了具体实现代码。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

今天遇到了这样一个需求,移除掉以某列某个字符串开头的行。首先想到是如果数据存在数据库中,只需要在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其他的方法。如果不深入学习,一知半解,很可能下次还是不会!

评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值