C#DataTable详解

添加引用 using System.Data; 创建表 // 创建一个空表 DataTable dt = new DataTable(); // 创建一个名为"Table_New"的空表 DataTable dt = new DataTable( " Table_New " ); 创建列 // 1.创建空列 DataColumn dc = new DataColumn();dt.Columns.Add(dc); //

添加引用

<span style="color: #0000ff;">using</span> System.Data;

创建表

<span style="color: #008000;">//</span><span style="color: #008000;">创建一个空表</span>
DataTable dt = <span style="color: #0000ff;">new</span><span style="color: #000000;"> DataTable();
</span><span style="color: #008000;">//</span><span style="color: #008000;">创建一个名为"Table_New"的空表</span>
DataTable dt = <span style="color: #0000ff;">new</span> DataTable(<span style="color: #800000;">"</span><span style="color: #800000;">Table_New</span><span style="color: #800000;">"</span>);

创建列

<span style="color: #008000;">//</span><span style="color: #008000;">1.创建空列</span>
DataColumn dc = <span style="color: #0000ff;">new</span><span style="color: #000000;"> DataColumn();
dt.Columns.Add(dc);
</span><span style="color: #008000;">//</span><span style="color: #008000;">2.创建带列名和类型名的列(两种方式任选其一)</span>
dt.Columns.Add(<span style="color: #800000;">"</span><span style="color: #800000;">column0</span><span style="color: #800000;">"</span>, System.Type.GetType(<span style="color: #800000;">"</span><span style="color: #800000;">System.String</span><span style="color: #800000;">"</span><span style="color: #000000;">));
dt.Columns.Add(</span><span style="color: #800000;">"</span><span style="color: #800000;">column0</span><span style="color: #800000;">"</span>, <span style="color: #0000ff;">typeof</span><span style="color: #000000;">(String));
</span><span style="color: #008000;">//</span><span style="color: #008000;">3.通过列架构添加列</span>
DataColumn dc = <span style="color: #0000ff;">new</span> DataColumn(<span style="color: #800000;">"</span><span style="color: #800000;">column1</span><span style="color: #800000;">"</span>,System.Type.GetType(<span style="color: #800000;">"</span><span style="color: #800000;">System.DateTime</span><span style="color: #800000;">"</span><span style="color: #000000;">));
DataColumn dc </span>= <span style="color: #0000ff;">new</span> DataColumn(<span style="color: #800000;">"</span><span style="color: #800000;">column1</span><span style="color: #800000;">"</span>, <span style="color: #0000ff;">typeof</span><span style="color: #000000;">(DateTime));
dt.Columns.Add(dc);</span>

创建行

<span style="color: #008000;">//</span><span style="color: #008000;">1.创建空行</span>
DataRow dr =<span style="color: #000000;"> dt.NewRow();
dt.Rows.Add(dr);
</span><span style="color: #008000;">//</span><span style="color: #008000;">2.创建空行</span>
<span style="color: #000000;">dt.Rows.Add();
</span><span style="color: #008000;">//</span><span style="color: #008000;">3.通过行框架创建并赋值</span>
dt.Rows.Add(<span style="color: #800000;">"</span><span style="color: #800000;">张三</span><span style="color: #800000;">"</span>,DateTime.Now);<span style="color: #008000;">//</span><span style="color: #008000;">Add里面参数的数据顺序要和dt中的列的顺序对应 <br>//4.通过复制dt2表的某一行来创建<br></span>dt.Rows.Add(dt2.Rows[i].ItemArray);

赋值和取值

<span style="color: #008000;">//</span><span style="color: #008000;">新建行的赋值</span>
DataRow dr =<span style="color: #000000;"> dt.NewRow();
dr[</span><span style="color: #800080;">0</span>] = <span style="color: #800000;">"</span><span style="color: #800000;">张三</span><span style="color: #800000;">"</span>;<span style="color: #008000;">//</span><span style="color: #008000;">通过索引赋值</span>
dr[<span style="color: #800000;">"</span><span style="color: #800000;">column1</span><span style="color: #800000;">"</span>] = DateTime.Now; <span style="color: #008000;">//</span><span style="color: #008000;">通过名称赋值
</span><span style="color: #008000;">//</span><span style="color: #008000;">对表已有行进行赋值</span>
dt.Rows[<span style="color: #800080;">0</span>][<span style="color: #800080;">0</span>] = <span style="color: #800000;">"</span><span style="color: #800000;">张三</span><span style="color: #800000;">"</span>; <span style="color: #008000;">//</span><span style="color: #008000;">通过索引赋值</span>
dt.Rows[<span style="color: #800080;">0</span>][<span style="color: #800000;">"</span><span style="color: #800000;">column1</span><span style="color: #800000;">"</span>] = DateTime.Now;<span style="color: #008000;">//</span><span style="color: #008000;">通过名称赋值
</span><span style="color: #008000;">//</span><span style="color: #008000;">取值</span>
<span style="color: #0000ff;">string</span> name=dt.Rows[<span style="color: #800080;">0</span>][<span style="color: #800080;">0</span><span style="color: #000000;">].ToString();
</span><span style="color: #0000ff;">string</span> time=dt.Rows[<span style="color: #800080;">0</span>][<span style="color: #800000;">"</span><span style="color: #800000;">column1</span><span style="color: #800000;">"</span>].ToString();

筛选行

<span style="color: #008000;">//</span><span style="color: #008000;">选择column1列值为空的行的集合</span>
DataRow[] drs = dt.Select(<span style="color: #800000;">"</span><span style="color: #800000;">column1 is null</span><span style="color: #800000;">"</span><span style="color: #000000;">);
</span><span style="color: #008000;">//</span><span style="color: #008000;">选择column0列值为"李四"的行的集合</span>
DataRow[] drs = dt.Select(<span style="color: #800000;">"</span><span style="color: #800000;">column0 = '李四'</span><span style="color: #800000;">"</span><span style="color: #000000;">);
</span><span style="color: #008000;">//</span><span style="color: #008000;">筛选column0列值中有"张"的行的集合(模糊查询)</span>
DataRow[] drs = dt.Select(<span style="color: #800000;">"</span><span style="color: #800000;">column0 like '张%'</span><span style="color: #800000;">"</span>);<span style="color: #008000;">//</span><span style="color: #008000;">如果的多条件筛选，可以加 and 或 or
</span><span style="color: #008000;">//</span><span style="color: #008000;">筛选column0列值中有"张"的行的集合并按column1降序排序</span>
DataRow[] drs = dt.Select(<span style="color: #800000;">"</span><span style="color: #800000;">column0 like '张%'</span><span style="color: #800000;">"</span>, <span style="color: #800000;">"</span><span style="color: #800000;">column1 DESC</span><span style="color: #800000;">"</span>);

删除行

<span style="color: #008000;">//</span><span style="color: #008000;">使用DataTable.Rows.Remove(DataRow)方法</span>
dt.Rows.Remove(dt.Rows[<span style="color: #800080;">0</span><span style="color: #000000;">]);
</span><span style="color: #008000;">//</span><span style="color: #008000;">使用DataTable.Rows.RemoveAt(index)方法</span>
dt.Rows.RemoveAt(<span style="color: #800080;">0</span><span style="color: #000000;">);
</span><span style="color: #008000;">//</span><span style="color: #008000;">使用DataRow.Delete()方法</span>
dt.Row[<span style="color: #800080;">0</span><span style="color: #000000;">].Delete();
dt.AcceptChanges();

</span><span style="color: #008000;">//-----</span><span style="color: #008000;">区别和注意点-----
</span><span style="color: #008000;">//</span><span style="color: #008000;">Remove()和RemoveAt()方法是直接删除
</span><span style="color: #008000;">//</span><span style="color: #008000;">Delete()方法只是将该行标记为deleted，但是还存在，还可DataTable.RejectChanges()回滚，使该行取消删除。
</span><span style="color: #008000;">//</span><span style="color: #008000;">用Rows.Count来获取行数时，还是删除之前的行数，需要使用DataTable.AcceptChanges()方法来提交修改。
</span><span style="color: #008000;">//</span><span style="color: #008000;">如果要删除DataTable中的多行，应该采用倒序循环DataTable.Rows，而且不能用foreach进行循环删除，因为正序删除时索引会发生变化，程式发生异常，很难预料后果。</span>
<span style="color: #0000ff;">for</span> (<span style="color: #0000ff;">int</span> i = dt.Rows.Count - <span style="color: #800080;">1</span>; i >= <span style="color: #800080;">0</span>; i--<span style="color: #000000;">)
{
　　dt.Rows.RemoveAt(i);
}</span>

复制表

<span style="color: #008000;">//</span><span style="color: #008000;">复制表，同时复制了表结构和表中的数据</span>
DataTable dtNew = <span style="color: #0000ff;">new</span><span style="color: #000000;"> DataTable();
dtNew </span>=<span style="color: #000000;"> dt.Copy();
</span><span style="color: #008000;">//</span><span style="color: #008000;">复制表</span>
DataTable dtNew = dt.Copy();  <span style="color: #008000;">//</span><span style="color: #008000;">复制dt表数据结构</span>
dtNew.Clear()  <span style="color: #008000;">//</span><span style="color: #008000;">清空数据</span>
<span style="color: #0000ff;">for</span> (<span style="color: #0000ff;">int</span> i = <span style="color: #800080;">0</span>; i )
{
    <span style="color: #0000ff;">if</span><span style="color: #000000;"> (条件语句)
    {
         dtNew.Rows.Add(dt.Rows[i].ItemArray);  </span><span style="color: #008000;">//</span><span style="color: #008000;">添加数据行</span>
<span style="color: #000000;">    }
}
</span><span style="color: #008000;">//</span><span style="color: #008000;">克隆表，只是复制了表结构，不包括数据</span>
DataTable dtNew = <span style="color: #0000ff;">new</span><span style="color: #000000;"> DataTable();
dtNew </span>=<span style="color: #000000;"> dt.Clone();
</span><span style="color: #008000;">//</span><span style="color: #008000;">如果只需要某个表中的某一行</span>
DataTable dtNew = <span style="color: #0000ff;">new</span><span style="color: #000000;"> DataTable();
dtNew </span>=<span style="color: #000000;"> dt.Copy();
dtNew.Rows.Clear();</span><span style="color: #008000;">//</span><span style="color: #008000;">清空表数据</span>
dtNew.ImportRow(dt.Rows[<span style="color: #800080;">0</span>]);<span style="color: #008000;">//</span><span style="color: #008000;">这是加入的是第一行</span>

表排序

DataTable dt = <span style="color: #0000ff;">new</span> DataTable();<span style="color: #008000;">//</span><span style="color: #008000;">创建表</span>
dt.Columns.Add(<span style="color: #800000;">"</span><span style="color: #800000;">ID</span><span style="color: #800000;">"</span>, <span style="color: #0000ff;">typeof</span>(Int32));<span style="color: #008000;">//</span><span style="color: #008000;">添加列</span>
dt.Columns.Add(<span style="color: #800000;">"</span><span style="color: #800000;">Name</span><span style="color: #800000;">"</span>, <span style="color: #0000ff;">typeof</span><span style="color: #000000;">(String));
dt.Columns.Add(</span><span style="color: #800000;">"</span><span style="color: #800000;">Age</span><span style="color: #800000;">"</span>, <span style="color: #0000ff;">typeof</span><span style="color: #000000;">(Int32));
dt.Rows.Add(</span><span style="color: #0000ff;">new</span> <span style="color: #0000ff;">object</span>[] { <span style="color: #800080;">1</span>, <span style="color: #800000;">"</span><span style="color: #800000;">张三</span><span style="color: #800000;">"</span> ,<span style="color: #800080;">20</span>});<span style="color: #008000;">//</span><span style="color: #008000;">添加行</span>
dt.Rows.Add(<span style="color: #0000ff;">new</span> <span style="color: #0000ff;">object</span>[] { <span style="color: #800080;">2</span>, <span style="color: #800000;">"</span><span style="color: #800000;">李四</span><span style="color: #800000;">"</span> ,<span style="color: #800080;">25</span><span style="color: #000000;">});
dt.Rows.Add(</span><span style="color: #0000ff;">new</span> <span style="color: #0000ff;">object</span>[] { <span style="color: #800080;">3</span>, <span style="color: #800000;">"</span><span style="color: #800000;">王五</span><span style="color: #800000;">"</span> ,<span style="color: #800080;">30</span><span style="color: #000000;">});
DataView dv </span>= dt.DefaultView;<span style="color: #008000;">//</span><span style="color: #008000;">获取表视图</span>
dv.Sort = <span style="color: #800000;">"</span><span style="color: #800000;">ID DESC</span><span style="color: #800000;">"</span>;<span style="color: #008000;">//</span><span style="color: #008000;">按照ID倒序排序</span>
dv.ToTable();<span style="color: #008000;">//</span><span style="color: #008000;">转为表</span>