DataTable

using System.Data

• DataRowCollection----->DataRow

• DataColumnCollection---->DataColumns

• DataView

1.添加列数据

//第一列
DataColumn id = new DataColumn();
id.ColumnName = "id";
id.Caption = "学号";
id.DataType = typeof(string);
id.ReadOnly = true;
id.AllowDBNull = false;
id.Unique = true;
dt.Columns["id"].AutoIncrement = true;
dt.Columns["id"].AutoIncrementSeed = 1;//开始值
dt.Columns["id"].AutoIncrementStep = 1;//增值
dt.Columns.Add(id);
//第二列
DataColumn name = dt.Columns.Add("name", typeof(string));
name.Caption = "姓名";
//第三、四列
dt.Columns.AddRange(new DataColumn[] {
    new DataColumn("sex",typeof(char)),
    new DataColumn("age",typeof(int)),
});
dt.Columns["sex"]!.DefaultValue = "女";
//设置主键
dt.PrimaryKey = new DataColumn[] { dt.Columns["id"] };

2.添加行数据

//第一行
DataRow rowId = dt.NewRow();
rowId["id"] = "S001";
rowId["name"] = "小春";
rowId["sex"] = "女";
rowId["age"] = "23";
dt.Rows.Add(rowId);
//.....行
var newRow = new Object[] {
    new object[]{ "S002","日富美",'女',22},
    new object[]{ "S003","花子", '女', 21 },
    new object[]{ "S004","梓", '女', 23},
};
foreach (Object[] item in newRow)
{
    dt.Rows.Add(item);
}

3.SqlAdapter

SqlAdapter adapter=..........;
//获取
adpter.Fill(DataTable);
//更新
SqlCommandBuilder thisBuilder = new SqlCommandBuilder(adapter);
adapter.Update(dt!);

4.获取行数据

//配合DataGridView选中行使用
int scalar = dgv.Rows.GetRowCount(DataGridViewElementStates.Selected);
if (scalar > 0)
{
    int Y = dgv.CurrentCellAddress.Y;
    txtName.Text = dt.Rows[Y]["names"].ToString();
}
//
int x = dgv.CurrentCellAdress.X;//列
int y = dgv.CurrentCellAdress.y;//行
dt.Rows[y][x] = dgv.Rows[y].Cells[x].Value;

5.排序

dt.DefaultView.Sort = "birthday ASC";
dt.DefaultView.Sort = "birthday DESC";

6.表复制

dt.Copy();//表结构+数据
dt.Clone();//表结构

7.筛选

//RowFilter-->DataGridView会重新渲染
 dt.DefaultView.RowFilter = null
 dt.DefaultView.RowFilter = String.Format("EName LIKE '%{0}%'", name);
 dt.DefaultView.RowFilter="city='千禧年'";
//Select-->DataGridView不会更新
 DataRow[] result = dt.Select("city='千禧年'");
 DataTable copy = dt.Clone();
 foreach (DataRow item in result)
 {
     copy.ImportRow(item);
 }
 dgv.DataSource = copy;
//Computed-->给定筛选条件,然后表达式计算
 object res=dt.Compute("Count(sex)", "sex='男'");
//Find-->只会在PrimaryKey中查找,无主键-MissingPrimaryKeyException
 DataRow res = dt.Rows.Find("S001");
 txtName.Text = res[1].ToString();

8.Linq

txtCount.Text = dt.AsEnumerable().Count().ToString();
txtMax.Text = dt.AsEnumerable().Max(x => x["birthday"]).ToString();
txtMin.Text = dt.AsEnumerable().Min(x => x["birthday"]).ToString();

9.DataTable转List

 public static List<T> DataTableToList<T>(this DataTable dt)
 {
     List<T> lstEntity = new List<T>();
     PropertyInfo[] arrProInfo = typeof(T).GetProperties();
     foreach (DataRow rowItem in dt.Rows)
     {
         T entity = Activator.CreateInstance<T>();
         for (int i = 0; i < dt.Columns.Count; i++)
         {
             PropertyInfo? proInfo = arrProInfo.FirstOrDefault(X => X.Name.Equals(dt.Columns[i].ColumnName));
             if (!Convert.IsDBNull(rowItem[i]))//T-SQL中==NULL,则不添加。(System.DBNull无法转换)
                 proInfo?.SetValue(entity, rowItem[i]);
         }
         lstEntity.Add(entity);
     }
     return lstEntity;
 }

10.List转DataTable

        public static DataTable ListToDataTable<T>(this List<T> lst)
        {
            PropertyInfo[] arrPro = typeof(T).GetProperties();
            DataTable dt = new DataTable(typeof(T).Name);
            //添加列
            foreach (PropertyInfo item in arrPro)
            {
                dt.Columns.Add(item.Name, item.PropertyType);
            }
            //添加行
            foreach (T item in lst)
            {
                if (item != null)
                {
                    var values = new object[arrPro.Length];
                    for (int i = 0; i < arrPro.Length; i++)
                    {
#pragma warning disable CS8601
                        values[i] = arrPro[i].GetValue(item);
                    }
                    dt.Rows.Add(values);
                }
            }
            return dt;
        }
        values[i] = arrPro[i].GetValue(item);
                    }
                    dt.Rows.Add(values);
                }
            }
            return dt;
        }