DataSet、DataTable基础应用

[TOC]

```csharp
public class DataSetApplication
{
    /// <summary>
    /// 定义一个全局的DataSet
    /// </summary>
    private DataSet ds = new DataSet();
}
```

### 填充DataSet

```csharp
 /// <summary>
 /// 填充DataSet
 /// </summary>
 public void CreateDataSet()
 {
     DataTable dtUser = new DataTable();
     dtUser.TableName = "user";
     dtUser.Columns.Add("编号", typeof(Int32));//添加列
     dtUser.Columns.Add("用户名字", typeof(String));
     dtUser.Columns.Add("性别", typeof(String));
     dtUser.Columns.Add("生日", typeof(DateTime));
     dtUser.Columns.Add("地址", typeof(String));
     dtUser.PrimaryKey = new DataColumn[] { dtUser.Columns["编号"] } ;

DataRow dr = dtUser.NewRow();//NewRow创建
     dr.SetField("编号", 1);
     dr.SetField("用户名字", "曹操");
     dr.SetField("性别", "男");
     dr.SetField("生日", "2021-01-05");
     dr.SetField("地址", "魏");
     dtUser.Rows.Add(dr);

dr = dtUser.NewRow();//NewRow创建
     dr.SetField("编号", 2);
     dr.SetField("用户名字", "卞夫人");
     dr.SetField("性别", "女");
     dr.SetField("生日", "2021-01-05");
     dr.SetField("地址", "魏");
     dtUser.Rows.Add(dr);

dr = dtUser.NewRow();//NewRow创建
     dr.SetField("编号", 3);
     dr.SetField("用户名字", "曹丕");
     dr.SetField("性别", "男");
     dr.SetField("生日", "2021-01-05");
     dr.SetField("地址", "魏");
     dtUser.Rows.Add(dr);
     ds.Tables.Add(dtUser);

DataTable dtCustomer = new DataTable();
     dtCustomer.TableName = "customer";
     dtCustomer.Columns.Add("编号", typeof(Int32));
     dtCustomer.Columns.Add("客户名字", typeof(String));
     dtCustomer.Columns.Add("性别", typeof(String));
     dtCustomer.Columns.Add("生日", typeof(DateTime));
     dtCustomer.Columns.Add("地址", typeof(String));

dr = dtCustomer.NewRow();//NewRow创建
     dr.SetField("编号", 1);
     dr.SetField("客户名字", "刘备");
     dr.SetField("性别", "男");
     dr.SetField("生日", "2021-01-05");
     dr.SetField("地址", "蜀");
     dtCustomer.Rows.Add(dr);

dr = dtCustomer.NewRow();//NewRow创建
     dr.SetField("编号", 2);
     dr.SetField("客户名字", "孙尚香");
     dr.SetField("性别", "女");
     dr.SetField("生日", "2021-01-05");
     dr.SetField("地址", "吴");
     dtCustomer.Rows.Add(dr);

dr = dtCustomer.NewRow();//NewRow创建
     dr.SetField("编号", 3);
     dr.SetField("客户名字", "刘禅");
     dr.SetField("性别", "男");
     dr.SetField("生日", "2021-01-05");
     dr.SetField("地址", "蜀");
     dtCustomer.Rows.Add(dr);

ds.Tables.Add(dtCustomer);
     //Console.ReadKey();
 }
```

### 读取DataSet

```csharp
/// <summary>
/// 读取DataSet
/// </summary>
public void ReadDataSet()
{
    if (ds != null && ds.Tables != null && ds.Tables.Count > 0)
    {
        foreach (DataTable dt in ds.Tables)
        {
            if (dt.TableName == "user")//两种遍历方式(已知列名)
            {
                Console.WriteLine("---------user表---------");
                foreach (DataRow dr in dt.Rows)
                {
                    if (dr != null)
                    {
                        Console.WriteLine(dr["编号"] + ":" + dr["用户名字"] + ":" + dr["性别"] + ":" + dr["生日"] + ":" + dr["地址"]);
                    }
                }
            }
            else
            {
                Console.WriteLine("\r\n---------customer表---------");
                for (int i = 0; i < dt.Rows.Count; i++) //不知道列表的遍历方式，并显示出列名
                {
                    DataRow dr = dt.Rows[i];
                    string info = "";
                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        info += "[" + dt.Columns[j].ColumnName + "," + dr[j] + "]" + ":";//dt.Columns[j].ColumnName获取列命
                    }
                    Console.WriteLine(info.Trim(':'));
                }
            }
        }
    }
}
```
### 添加新列

```csharp
/// <summary>
/// 添加新列
/// </summary>
public void DataSetAddColumn()
{
    Console.WriteLine("\r\n------------添加列------------");
    if (ds != null && ds.Tables != null && ds.Tables.Count > 0)
    {
        DataTable dt = ds.Tables["user"];
        if (dt != null)
        {
            string[] position = new string[] { "王", "后", "太子" };
            dt.Columns.Add("职位", typeof(string));//给user表新增一列职位
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                DataRow dr = dt.Rows[i];
                dr.SetField("职位", position[i]);//给新增的职位列添加值
                string info = "";
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    info += "[" + dt.Columns[j].ColumnName + "," + dr[j] + "]" + ":";//dt.Columns[j].ColumnName获取列命
                }
                Console.WriteLine(info.Trim(':'));
            }
        }
    }
}
```

### 删除列

```csharp
/// <summary>
/// 删除列
/// </summary>
public void DataSetRemoveColumn()
{
    Console.WriteLine("\r\n------------删除列------------");
    if (ds != null && ds.Tables != null && ds.Tables.Count > 0)
    {
        DataTable dt = ds.Tables["user"];
        if (dt != null)
        {
            dt.Columns.Remove("生日");
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                DataRow dr = dt.Rows[i];
                string info = "";
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    info += "[" + dt.Columns[j].ColumnName + "," + dr[j] + "]" + ":";//dt.Columns[j].ColumnName获取列命
                }
                Console.WriteLine(info.Trim(':'));
            }
        }
        var list = dt.Select().Where(m => Convert.ToInt32(m["编号"]) == 1).ToList();
    }
}
```

### 查找数据

```csharp
/// <summary>
/// 查找数据
/// </summary>
public void DataSetSearch()
{
    Console.WriteLine("\r\n------------查找需要的信息------------");
    if (ds != null && ds.Tables != null && ds.Tables.Count > 0)
    {
        DataTable dt = ds.Tables["user"];
        if (dt != null)
        {
            var list = dt.Select().Where(m => Convert.ToInt32(m["编号"]) == 1 || Convert.ToInt32(m["编号"]) == 2).ToList();//通过linq查询出需要的数据
            foreach (var item in list)
            {
                string info = "";
                for (int j = 0; j < item.ItemArray.Length; j++)
                {
                    info += "[" + item.ItemArray[j] + "]" + ":";
                }
                Console.WriteLine(info.Trim(':'));
            }
        }
    }
}
```

### 删除行

```csharp
/// <summary>
/// 删除行
/// </summary>
public void DataSetRemoveRow()
{
    Console.WriteLine("\r\n------------删除行------------");
    if (ds != null && ds.Tables != null && ds.Tables.Count > 0)
    {
        DataTable dt = ds.Tables["user"];
        if (dt != null)
        {
            //find方法是查找主键使用find方法前必须给DataTable设置主键dtUser.PrimaryKey = new DataColumn[] { dtUser.Columns["编号"] }否则会报错提示没有主键
                    dt.Rows.Remove(dt.Rows.Find(1));//删除主键为1的数据
                    dt.Rows.Remove(dt.Select().Where(m => (m["用户名字"].ToString() == "卞夫人")).First());//删除名称是卞夫人的行
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                DataRow dr = dt.Rows[i];
                string info = "";
                for (int j = 0; j < dt.Columns.Count; j++)
                {
                    info += "[" + dt.Columns[j].ColumnName + "," + dr[j] + "]" + ":";//dt.Columns[j].ColumnName获取列命
                     }
                     Console.WriteLine(info.Trim(':'));
                 }
             }
    }
}
```
注意：1、dt.Rows.Find使用的先决条件是DataTble必须设置了主键，即PrimaryKey必须赋值，而find参数也查找的是主键列本例中dtUser.PrimaryKey = new DataColumn[] { dtUser.Columns["编号"] }就设置了主键列