DataTable小详解
@
目录
DataTable小详解
基础操作
public void CreateTable()
{
//创建表
DataTable dt = new DataTable();
//1、添加列
dt.Columns.Add("Name", typeof(string)); //数据类型为 文本
//2、通过列架构添加列
DataColumn age = new DataColumn("Age", typeof(Int32)); //数据类型为 整形
DataColumn Time = new DataColumn("Time", typeof(DateTime)); //数据类型为 时间
dt.Columns.Add(age);
dt.Columns.Add(Time);
//1、添加空行
DataRow dr1 = dt.NewRow();
dt.Rows.Add(dr1);
//2、添加空行
dt.Rows.Add();
//3、添加数据行
DataRow dr2 = dt.NewRow();
dr2[0] = "张三"; //通过索引赋值
dr2[1] = 23;
dr2["Time"] = DateTime.Now;//通过名称赋值
dt.Rows.Add(dr2);
//4、通过行框架添加
dt.Rows.Add("李四",25,DateTime.Now);//Add你们参数的数据顺序要和dt中的列顺对应
}
//已存在DataTable
public void CreateTable(DataTable vTable)
{
//为已有DataTable添加一新列
DataColumn dc1 = new DataColumn("Tol", typeof(string));
vTable.Columns.Add(dc1);
//添加一新列，其值为默认值
DataColumn dc2 = new DataColumn("Sex", typeof(string));
dc2.DefaultValue = "男";
dc2.AllowDBNull = false;//这在创建表的时候，起作用，在为已有表新增列的时候，不起作用
vTable.Columns.Add(dc2);
}
//删除
//使用DataTable.Rows.Remove(DataRow)方法
dt.Rows.Remove(dt.Rows[0]);
//使用DataTable.Rows.RemoveAt(index)方法
dt.Rows.RemoveAt(0);
//使用DataRow.Delete()方法
dt.Row[0].Delete();
dt.AcceptChanges();
//-----区别和注意点-----
//Remove()和RemoveAt()方法是直接删除
//Delete()方法只是将该行标记为deleted，但是还存在，还可DataTable.RejectChanges()回滚，使该行取消删除。
//用Rows.Count来获取行数时，还是删除之前的行数，需要使用DataTable.AcceptChanges()方法来提交修改。
//如果要删除DataTable中的多行，应该采用倒序循环DataTable.Rows，而且不能用foreach进行循环删除，因为正序删除时索引会发生变化，程式发生异常，很难预料后果。
for (int i = dt.Rows.Count - 1; i >= 0; i--)
{
dt.Rows.RemoveAt(i);
}
//复制表，同时复制了表结构和表中的数据
DataTable dtNew = new DataTable();
dtNew = dt.Copy();
//复制表
DataTable dtNew = dt.Copy(); //复制dt表数据结构
dtNew.Clear() //清空数据
for (int i = 0; i < dt.Rows.Count; i++)
{
if (条件语句)
{
dtNew.Rows.Add(dt.Rows[i].ItemArray); //添加数据行
}
}
//克隆表，只是复制了表结构，不包括数据
DataTable dtNew = new DataTable();
dtNew = dt.Clone();
//如果只需要某个表中的某一行
DataTable dtNew = new DataTable();
dtNew = dt.Copy();
dtNew.Rows.Clear();//清空表数据
dtNew.ImportRow(dt.Rows[0]);//这是加入的是第一行
DataTable dt = new DataTable();//创建表
dt.Columns.Add("ID", typeof(Int32));//添加列
dt.Columns.Add("Name", typeof(String));
dt.Columns.Add("Age", typeof(Int32));
dt.Rows.Add(new object[] { 1, "张三" ,20});//添加行
dt.Rows.Add(new object[] { 2, "李四" ,25});
dt.Rows.Add(new object[] { 3, "王五" ,30});
DataView dv = dt.DefaultView;//获取表视图
dv.Sort = "ID DESC";//按照ID倒序排序
dv.ToTable();//转为表
筛选DataTable数据
//使用select()方法，新结果保存到DataRow[] 或者另存为一个新的DataTable
public void SelectRowDataTable()
{
DataTable dt = new DataTable();//假设dt是由"SELECT C1,C2,C3 FROM T1"查询出来的结果
for (int i = 0; i < dt.Rows.Count; i++)
{
if (dt.Rows[i]["C1"].ToString() == "abc")//查询条件
{
//进行操作
}
}
//但这种做法用一两次还好说，用多了就累了。那有没有更好的方法呢？就是dt.Select()，上面的操作可以改成这样：
DataRow[] drArr = dt.Select("C1='abc'");//查询(如果Select内无条件，就是查询所有的数据)
//还可以这样操作：
DataRow[] drArr1 = dt.Select("C1 LIKE 'abc%'");//模糊查询（如果的多条件筛选，可以加 and 或 or ）
DataRow[] drArr2 = dt.Select("'abc' LIKE C1 + '%'", "C2 DESC");//另一种模糊查询的方法
DataRow[] drArr3 = dt.Select("C1='abc'", "C2 DESC");//排序
//问题又来了，如果要把DataRow赋值给新的DataTable，怎么赋值呢？你可能会想到：
DataTable dtNew1 = dt.Clone();
for (int i = 0; i < drArr.Length; i++)
{
dtNew1.Rows.Add(drArr[i]);
}
//但这样程序就会出错，说该DataRow是属于其他DataTable的，那要怎么做呢？很简单，这样就可以解决了：
DataTable dtNew2 = dt.Clone();
for (int i = 0; i < drArr.Length; i++)
{
dtNew2.ImportRow(drArr[i]);//ImportRow 是复制
}
}
//另存为一个新DataTable
public void SelectColumnDataTable(DataTable dt)
{
//对DataTable筛选指定字段，并保存为新表。这些字段，确保dt中存在，否则会报错误
DataTable dtNew = dt.DefaultView.ToTable(false, new string[] { "列名", "列名", "列名" });
}
DataTable.Select()小详解
Select();//全部查出来
Select(过滤条件);//根据过滤条件进行过滤，如Select("columnname1 like '%xx%'");
Select(过滤条件,排序字段);//过滤，并排序，如Select("columnname1 like '%xx%'",columnname2);
完成一个查询，返回一个DataTable后，很多时候都想在查询结果中继续搜索。这时可以使用DataTable.Select方法对结果进行再查询。
Select方法有4个重载，我们经常用到的就是DataTable.Select(String) ;
下面就说说带一个参数的DataTable.Select(String)：
这个String的参数是查询的限定式。相当于SQL查询语言中的WHERE语句（不含WHERE），其语法符合SQL语言语法。 （网友的说法）我觉得就是类似sql的语法而已。
但是不支持BETWEEN AND，举个成功的例子:
//FromTime 和ToTime 是两个DateTime类型的变量；occurTime是dTable里面的列名；
DataRow[] datarows = dTable.Select("occurTime >= '" + FromTime + "' and occurTime <= '" + ToTime+"'");
DataTable.Select()方法里面支持简单的过滤和排序，不支持复杂的条件过滤和排序。里面的字符串必须是列名和数据，以及>,<,=,<>等关系运算符。举几个例子：
DataRow[] row = Detailtb.Select("WZMC='"+MaterialName+"' and CZ='"+MaterialTexture+" and GG='"+MaterialSpecs+"'");
DataTable.Select("City Like 'B%'");
DataTable.Select("name='" + a +"'");
下面这条语句选择Pubdate字段日期在2000年1月1日当天或之后的DataRow
DataRow[] arrRows = table.Select("pubdate>='#1/1/2000#'");
操作符可以被支持:<,<=,=,>=,>和<>也可以使用IN和Like
返回"state"等于CA、TN或WA的所有行
DataRow[] arrRows = table.Select("state in('ca','tn','wa')");
返回"state"以CA开头的所有行
DataRow[] arrRows = table.Select("state like 'ca*'");
还可以使用一些Sql函数,在DataTable中选择State字段为空的记录
DataRow[] arrRows = table.Select("isnull(state,0)=0");
还可以使用And、Or和Not
DataRow[] arrRows = table.Select("state='tn' and zip like '37*'");
/*总之，可以用括号分组，创建复杂的布尔表达式*/
一定要注意单引号的问题，要先有双引号，再用单引号；即‘“变量”’；
排序
//建议在查询的时候就排序
//这是在数据已经出来后的排序
//方法一
public DataTable LinqSortDataTable(DataTable tmpDt)
{
DataTable dtsort = tmpDt.Clone();
dtsort = tmpDt.Rows.Cast<DataRow>().OrderBy(r =>r["villagecode"].ToDecimal()).CopyToDataTable();
return dtsort;
}
//方法二
public DataTable LinqSortDataTable(DataTable tmpDt)
{
int sortId = 3
DataView dv = tmpDt.DefaultView;
var query = (from item in tmpDt.AsEnumerable()
orderby (item[sortId].ToDecimal())
select item);
dv = query.AsDataView();
return dv.ToTable();
}
//方法三
public DataTable LinqSortDataTable(DataTable tmpDt)
{
dtTemplate.DefaultView.Sort = " villagecode asc ";
dtTemplate.DefaultView.RowFilter = "NAME = '张三'";
return dtTemplate.DefaultView.ToTable();
}
分组
//方式一 使用数据字典
public Dictionary<string, DataTable> Grouping(DataTable table){
Dictionary<string, DataTable> dict = new Dictionary<string, DataTable>();
DataView dv = new DataView(table);
//去重并取得列名为NAME的所有数据
DataTable dtTJ = dv.ToTable(true, "NAME");
for (int i = 0; i < dtTJ.Rows.Count; i++)
{
var res = data.Select("NAME = '" + dtTJ.Rows[i]["NAME"].ToString() + "'");//按条件查询出符合条件的行
DataTable resDt = data.Clone();//克隆一个壳子
foreach (var j in res)
{
resDt.ImportRow(j);//将符合条件的行放进壳子里
}
dict.Add(dtTJ.Rows[i]["NAME"].ToString(), resDt);//将键值对存起来
}
return dict;
}
//方式二 DataTable.Rows.Cast<DataRow>()
IEnumerable<IGrouping<string, DataRow>> result = dt.Rows.Cast<DataRow>()
.GroupBy<DataRow, string>(dr => dr["A"].ToString());//按A分组
foreach (IGrouping<string, DataRow> item in result)
{
}
//方式三 使用linq to DataTable group by实现
var query = from t in dt.AsEnumerable()
group t by new { t1 = t.Field<string>("name"), t2 = t.Field<string>("sex") } into m
select new
{
name = m.Key.t1,
sex = m.Key.t2,
score = m.Sum(n => n.Field<decimal>("score"))
};
if (query.ToList().Count > 0)
{
query.ToList().ForEach(q =>
{
Console.WriteLine(q.name + "," + q.sex + "," + q.score);
});
}
DataTable dtGroupBy = dataTable.DataTables.AsEnumerable()
.GroupBy(r => new { Name = r["NAME"], Times = r["Times"] }).Select(
g =>
{
var row = dataTable.DataTables.NewRow();
row["NAME"] = g.Key.Name;
row["Times"] = g.Key.Times;
row["Age"] = g.Sum(r => decimal.Parse(r["Age"].ToString()));
return row;
}).CopyToDataTable();
var dt = dtGroupBy.DefaultView.ToTable(true, new string[] { "NAME", "Times" });
//分组统计按次数排序
Dictionary<string, string> dicProjectExpectFiveParam = listProject.GroupBy(
x => new { x.LHCodeID, x.ParamName })
.Where(p => !sFiveParam.Contains(p.Key.LHCodeID))
.Select(group => new { group.Key, LHCodeIDCount = group.Count() })
.OrderByDescending(t => t.LHCodeIDCount)
.ToDictionary(o => o.Key.LHCodeID, p => p.Key.ParamName);
尾语： DataTable中AsEnumerable与Cast类似，都可转为Linq统计、排序等；
分页
//运用Linq将DataTable转换为集合进行分页（拆分Table）
int PageSize = 65536;
int totalCount = Convert.ToInt32(dtDataAll.Rows.Count);
int totalPage = Convert.ToInt32(Math.Ceiling((double)totalCount / PageSize));
var dtDataAllTemp = dtDataAll.AsEnumerable();
for (var i = 0; i<totalPage; i++)
{
DataTable dtNew = dtDataAllTemp.Skip(i * PageSize).Take(PageSize).CopyToDataTable();
}
去重
IEnumerable <DataRow> r = tbl.AsEnumerable().Distinct(new CityComparer());
class CityComparer : IEqualityComparer <DataRow>
{
public bool Equals(DataRow r1, DataRow r2)
{
return r1["City"] == r2["City"];
}
public int GetHashCode(DataRow obj)
{
return obj.ToString().GetHashCode();
}
}
穿插一下LinqDynamic
//调用AsQueryable()方法
static void Main(string[] args)
{
List<Phone> PhoneLists = new List<Phone>()
{
new Phone { Country = "中国", City = "北京", Name = "小米" },
new Phone { Country = "中国",City = "北京",Name = "华为"},
new Phone { Country = "中国",City = "北京",Name = "联想"},
new Phone { Country = "中国",City = "台北",Name = "魅族"},
new Phone { Country = "日本",City = "东京",Name = "索尼"},
new Phone { Country = "日本",City = "大阪",Name = "夏普"},
new Phone { Country = "日本",City = "东京",Name = "松下"},
new Phone { Country = "美国",City = "加州",Name = "苹果"},
new Phone { Country = "美国",City = "华盛顿",Name = "三星"},
new Phone { Country = "美国",City = "华盛顿",Name = "HTC"}
};
var Lists = PhoneLists.AsQueryable().Where("Country = @0 And City = @1", "中国", "北京").OrderBy("Name,City").Select("new (Country as Country,City as City,Name as Name)");
var dLists = Lists.ToDynamicList();
foreach (var list in dLists)
{
Console.WriteLine(list.Country + "-" + list.City + "-" + list.Name);
}
Console.Read();
}
//group by
static void Main(string[] args)
{
List<Phone> PhoneLists = new List<Phone>()
{
new Phone { Country = "中国", City = "北京", Name = "小米" },
new Phone { Country = "中国",City = "北京",Name = "华为"},
new Phone { Country = "中国",City = "北京",Name = "联想"},
new Phone { Country = "中国",City = "台北",Name = "魅族"},
new Phone { Country = "日本",City = "东京",Name = "索尼"},
new Phone { Country = "日本",City = "大阪",Name = "夏普"},
new Phone { Country = "日本",City = "东京",Name = "松下"},
new Phone { Country = "美国",City = "加州",Name = "苹果"},
new Phone { Country = "美国",City = "华盛顿",Name = "三星"},
new Phone { Country = "美国",City = "华盛顿",Name = "HTC"}
};
var Lists = PhoneLists.AsQueryable().GroupBy("new (Country as Country,City as City)");
var dLists = Lists.ToDynamicList();
foreach (var list in dLists)
{
Console.WriteLine(list + ":");
foreach (var lis in list)
{
Console.WriteLine(lis.Country + "-" + lis.City + "-" + lis.Name);
Console.WriteLine($"{lis.Country} - {lis.City} - {lis.Name}");
}
}
Console.Read();
}
利用反射 建立模糊查询通用工具类
public static IQueryable<T> TextFilter<T>(IQueryable<T> source, T param)
{
// 如果参数为空
if (param == null) { return source; }
// 获取对象参数
Type elementType = typeof(T);
// 在这个类型参数中获取所有的string类型
// Get all the string properties on this specific type.
PropertyInfo[] stringProperties =
elementType.GetProperties()
.Where(x => x.PropertyType == typeof(string))
.ToArray();
// 数组是否包含任何元素
if (!stringProperties.Any()) { return source; }
// Get the right overload of String.Contains
// 创建 String 方法中的 Contains 重载
MethodInfo containsMethod = typeof(string).GetMethod("Contains", new[] { typeof(string) });
// Create a parameter for the expression tree:
// the 'x' in 'x => x.PropertyName.Contains("term")'
// The type of this parameter is the query's element type
// 这是 Linq 表达式中的 x the 'x' in 'x => x.PropertyName.Contains("term")'
ParameterExpression prm = Expression.Parameter(elementType);
List<Expression> list = new List<Expression>();
// 遍历每个string类型
foreach (PropertyInfo prp in stringProperties)
{
// 获取string类型的值
String propertyValue = (String)prp.GetValue(param);
// 如果是空,或者是null,不做处理
if (String.IsNullOrEmpty(propertyValue))
{
continue;
}
// 定义表达式
var expression = Expression.Call( // .Contains(...)
Expression.Property( // .PropertyName
prm, // x
prp
),
containsMethod,
Expression.Constant(propertyValue) // "term"
);
list.Add(expression);
}
var expressions = list as IEnumerable<Expression>;
// Combine all the resultant expression nodes using ||
Expression body = expressions
.Aggregate(
(prev, current) => Expression.And(prev, current)
);
// Wrap the expression body in a compile-time-typed lambda expression
Expression<Func<T, bool>> lambda = Expression.Lambda<Func<T, bool>>(body, prm);
// Because the lambda is compile-time-typed (albeit with a generic parameter), we can use it with the Where method
return source.Where(lambda);
}
public void TEST(){
List<Person> src = new List<Person>() {
new Person { LastName = "abcd", FirstName = "abcd" },
new Person { LastName = "abcd", FirstName = "a" },
new Person { LastName = "a", FirstName = "abcd" }
};
List<Car> src1 = new List<Car>() {
new Car { Model = "abcd" },
new Car { Model = "a" },
new Car { Model = "c" }
};
var query1 = DynamicLinqUtils.TextFilter(
src.AsQueryable(),
new Person { LastName = "c", FirstName = "c" }
)
.Where(x => x.DateOfBirth < new DateTime(2001, 1, 1));
var list1 = query1.ToList();
var query2 = DynamicLinqUtils.TextFilter(
src1.AsQueryable(),
new Car { Model = "c" }
);
var list2 = query2.ToList();
}
穿插一下List分组
//List委托
List<Act_TypeListInfo> listFind = listAllTZ.FindAll(delegate( Act_TypeListInfo f)
{
return f.MainID == iMainId && f.KeyName == info.ColKeyName && f.CompID == iCompID);
});
//分组遍历
List<Cart_Model> list_CartModel = new List<Cart_Model>();
IEnumerable<IGrouping<string, Cart_Model>> query = list_CartModel.GroupBy(pet => pet.ShopId, pet => pet);
foreach (IGrouping<string, Cart_Model> info in query)
{
List<Cart_Model> sl = info.ToList<Cart_Model>();//分组后的集合
//也可循环得到分组后，集合中的对象，可以用info.Key去控制
foreach (KqiPageSetupInfo set in sl)
{
}
}
//多表分组排序统计
var temp = from ta in areaList
join tb in StationList on ta.AreaID equals tb.AreaID
join tc in dcMNComple on tb.MN equals tc.Key into temp1
//orderby ta.AreaID
group temp1 by new { AreaName = ta.AreaName, AreaID = ta.AreaID } into temp2
select new
{
AreaName = temp2.Key.AreaName,
AreaID = temp2.Key.AreaID,
MNCount = temp2.Count(),
Comple = temp2.Sum(s => s.Sum(t => t.Value))
};
//从List中查找另一个List的匹配项
var mnList = listFind.Where(d => listYQ.Select(d1 => d1.YQID).Contains(d.YQID)).ToList();
//或 (FindAll)
var mnList = listFind.Where(delegate (T_MNYQInfo f)
{
foreach (var itemYq in listYQ)
{
if (f.YQID == itemYq.YQID)
{
return true;
}
}
return false;
}).ToList();
