asp.net DataTable相关操作集锦(筛选,取前N条数据,去重复行,获取指定列数据等)
本文实例总结了asp.netDataTable相关操作。分享给大家供大家参考，具体如下：
#regionDataTable筛选，排序返回符合条件行组成的新DataTable或直接用DefaultView按条件返回
///<summary>
///DataTable筛选，排序返回符合条件行组成的新DataTable或直接用DefaultView按条件返回
///eg:SortExprDataTable(dt,"Sex='男'","TimeDesc",1)
///</summary>
///<paramname="dt">传入的DataTable</param>
///<paramname="strExpr">筛选条件</param>
///<paramname="strSort">排序条件</param>
///<paramname="mode">1,直接用DefaultView按条件返回,效率较高;2,DataTable筛选，排序返回符合条件行组成的新DataTable</param>
publicstaticDataTableSortDataTable(DataTabledt,stringstrExpr,stringstrSort,intmode)
{
switch(mode)
{
case1:
//方法一直接用DefaultView按条件返回
dt.DefaultView.RowFilter=strExpr;
dt.DefaultView.Sort=strSort;
returndt;
case2:
//方法二DataTable筛选，排序返回符合条件行组成的新DataTable
DataTabledt1=newDataTable();
DataRow[]GetRows=dt.Select(strExpr,strSort);
//复制DataTabledt结构不包含数据
dt1=dt.Clone();
foreach(DataRowrowinGetRows)
{
dt1.Rows.Add(row.ItemArray);
}
returndt1;
default:
returndt;
}
}
#endregion
#region获取DataTable前几条数据
///<summary>
///获取DataTable前几条数据
///</summary>
///<paramname="TopItem">前N条数据</param>
///<paramname="oDT">源DataTable</param>
///<returns></returns>
publicstaticDataTableDtSelectTop(intTopItem,DataTableoDT)
{
if(oDT.Rows.Count<TopItem)returnoDT;
DataTableNewTable=oDT.Clone();
DataRow[]rows=oDT.Select("1=1");
for(inti=0;i<TopItem;i++)
{
NewTable.ImportRow((DataRow)rows[i]);
}
returnNewTable;
}
#endregion
#region获取DataTable中指定列的数据
///<summary>
///获取DataTable中指定列的数据
///</summary>
///<paramname="dt">数据源</param>
///<paramname="tableName">新的DataTable的名词</param>
///<paramname="strColumns">指定的列名集合</param>
///<returns>返回新的DataTable</returns>
publicstaticDataTableGetTableColumn(DataTabledt,stringtableName,paramsstring[]strColumns)
{
DataTabledtn=newDataTable();
if(dt==null)
{
thrownewArgumentNullException("参数dt不能为null");
}
try
{
dtn=dt.DefaultView.ToTable(tableName,true,strColumns);
}
catch(Exceptione)
{
thrownewException(e.Message);
}
returndtn;
}
#endregion
usingSystem;
usingSystem.Collections.Generic;
usingSystem.Linq;
usingSystem.Data;
usingSystem.Collections;
usingSystem.Text;
namespaceGuanEasy
{
///<summary>
///DataSet助手
///</summary>
publicclassDataSetHelper
{
privateclassFieldInfo
{
publicstringRelationName;
publicstringFieldName;
publicstringFieldAlias;
publicstringAggregate;
}
privateDataSetds;
privateArrayListm_FieldInfo;
privatestringm_FieldList;
privateArrayListGroupByFieldInfo;
privatestringGroupByFieldList;
publicDataSetDataSet
{
get{returnds;}
}
#regionConstruction
publicDataSetHelper()
{
ds=null;
}
publicDataSetHelper(refDataSetdataSet)
{
ds=dataSet;
}
#endregion
#regionPrivateMethods
privateboolColumnEqual(objectobjectA,objectobjectB)
{
if(objectA==DBNull.Value&&objectB==DBNull.Value)
{
returntrue;
}
if(objectA==DBNull.Value||objectB==DBNull.Value)
{
returnfalse;
}
return(objectA.Equals(objectB));
}
privateboolRowEqual(DataRowrowA,DataRowrowB,DataColumnCollectioncolumns)
{
boolresult=true;
for(inti=0;i<columns.Count;i++)
{
result&=ColumnEqual(rowA[columns[i].ColumnName],rowB[columns[i].ColumnName]);
}
returnresult;
}
privatevoidParseFieldList(stringfieldList,boolallowRelation)
{
if(m_FieldList==fieldList)
{
return;
}
m_FieldInfo=newArrayList();
m_FieldList=fieldList;
FieldInfoField;
string[]FieldParts;
string[]Fields=fieldList.Split(',');
for(inti=0;i<=Fields.Length-1;i++)
{
Field=newFieldInfo();
FieldParts=Fields[i].Trim().Split('');
switch(FieldParts.Length)
{
case1:
//tobesetattheendoftheloop
break;
case2:
Field.FieldAlias=FieldParts[1];
break;
default:
return;
}
FieldParts=FieldParts[0].Split('.');
switch(FieldParts.Length)
{
case1:
Field.FieldName=FieldParts[0];
break;
case2:
if(allowRelation==false)
{
return;
}
Field.RelationName=FieldParts[0].Trim();
Field.FieldName=FieldParts[1].Trim();
break;
default:
return;
}
if(Field.FieldAlias==null)
{
Field.FieldAlias=Field.FieldName;
}
m_FieldInfo.Add(Field);
}
}
privateDataTableCreateTable(stringtableName,DataTablesourceTable,stringfieldList)
{
DataTabledt;
if(fieldList.Trim()=="")
{
dt=sourceTable.Clone();
dt.TableName=tableName;
}
else
{
dt=newDataTable(tableName);
ParseFieldList(fieldList,false);
DataColumndc;
foreach(FieldInfoFieldinm_FieldInfo)
{
dc=sourceTable.Columns[Field.FieldName];
DataColumncolumn=newDataColumn();
column.ColumnName=Field.FieldAlias;
column.DataType=dc.DataType;
column.MaxLength=dc.MaxLength;
column.Expression=dc.Expression;
dt.Columns.Add(column);
}
}
if(ds!=null)
{
ds.Tables.Add(dt);
}
returndt;
}
privatevoidInsertInto(DataTabledestTable,DataTablesourceTable,
stringfieldList,stringrowFilter,stringsort)
{
ParseFieldList(fieldList,false);
DataRow[]rows=sourceTable.Select(rowFilter,sort);
DataRowdestRow;
foreach(DataRowsourceRowinrows)
{
destRow=destTable.NewRow();
if(fieldList=="")
{
foreach(DataColumndcindestRow.Table.Columns)
{
if(dc.Expression=="")
{
destRow[dc]=sourceRow[dc.ColumnName];
}
}
}
else
{
foreach(FieldInfofieldinm_FieldInfo)
{
destRow[field.FieldAlias]=sourceRow[field.FieldName];
}
}
destTable.Rows.Add(destRow);
}
}
privatevoidParseGroupByFieldList(stringFieldList)
{
if(GroupByFieldList==FieldList)
{
return;
}
GroupByFieldInfo=newArrayList();
FieldInfoField;
string[]FieldParts;
string[]Fields=FieldList.Split(',');
for(inti=0;i<=Fields.Length-1;i++)
{
Field=newFieldInfo();
FieldParts=Fields[i].Trim().Split('');
switch(FieldParts.Length)
{
case1:
//tobesetattheendoftheloop
break;
case2:
Field.FieldAlias=FieldParts[1];
break;
default:
return;
}
FieldParts=FieldParts[0].Split('(');
switch(FieldParts.Length)
{
case1:
Field.FieldName=FieldParts[0];
break;
case2:
Field.Aggregate=FieldParts[0].Trim().ToLower();
Field.FieldName=FieldParts[1].Trim('',')');
break;
default:
return;
}
if(Field.FieldAlias==null)
{
if(Field.Aggregate==null)
{
Field.FieldAlias=Field.FieldName;
}
else
{
Field.FieldAlias=Field.Aggregate+"of"+Field.FieldName;
}
}
GroupByFieldInfo.Add(Field);
}
GroupByFieldList=FieldList;
}
privateDataTableCreateGroupByTable(stringtableName,DataTablesourceTable,stringfieldList)
{
if(fieldList==null||fieldList.Length==0)
{
returnsourceTable.Clone();
}
else
{
DataTabledt=newDataTable(tableName);
ParseGroupByFieldList(fieldList);
foreach(FieldInfoFieldinGroupByFieldInfo)
{
DataColumndc=sourceTable.Columns[Field.FieldName];
if(Field.Aggregate==null)
{
dt.Columns.Add(Field.FieldAlias,dc.DataType,dc.Expression);
}
else
{
dt.Columns.Add(Field.FieldAlias,dc.DataType);
}
}
if(ds!=null)
{
ds.Tables.Add(dt);
}
returndt;
}
}
privatevoidInsertGroupByInto(DataTabledestTable,DataTablesourceTable,stringfieldList,
stringrowFilter,stringgroupBy)
{
if(fieldList==null||fieldList.Length==0)
{
return;
}
ParseGroupByFieldList(fieldList);
ParseFieldList(groupBy,false);
DataRow[]rows=sourceTable.Select(rowFilter,groupBy);
DataRowlastSourceRow=null,destRow=null;
boolsameRow;
introwCount=0;
foreach(DataRowsourceRowinrows)
{
sameRow=false;
if(lastSourceRow!=null)
{
sameRow=true;
foreach(FieldInfoFieldinm_FieldInfo)
{
if(!ColumnEqual(lastSourceRow[Field.FieldName],sourceRow[Field.FieldName]))
{
sameRow=false;
break;
}
}
if(!sameRow)
{
destTable.Rows.Add(destRow);
}
}
if(!sameRow)
{
destRow=destTable.NewRow();
rowCount=0;
}
rowCount+=1;
foreach(FieldInfofieldinGroupByFieldInfo)
{
switch(field.Aggregate.ToLower())
{
casenull:
case"":
case"last":
destRow[field.FieldAlias]=sourceRow[field.FieldName];
break;
case"first":
if(rowCount==1)
{
destRow[field.FieldAlias]=sourceRow[field.FieldName];
}
break;
case"count":
destRow[field.FieldAlias]=rowCount;
break;
case"sum":
destRow[field.FieldAlias]=Add(destRow[field.FieldAlias],sourceRow[field.FieldName]);
break;
case"max":
destRow[field.FieldAlias]=Max(destRow[field.FieldAlias],sourceRow[field.FieldName]);
break;
case"min":
if(rowCount==1)
{
destRow[field.FieldAlias]=sourceRow[field.FieldName];
}
else
{
destRow[field.FieldAlias]=Min(destRow[field.FieldAlias],sourceRow[field.FieldName]);
}
break;
}
}
lastSourceRow=sourceRow;
}
if(destRow!=null)
{
destTable.Rows.Add(destRow);
}
}
privateobjectMin(objecta,objectb)
{
if((aisDBNull)||(bisDBNull))
{
returnDBNull.Value;
}
if(((IComparable)a).CompareTo(b)==-1)
{
returna;
}
else
{
returnb;
}
}
privateobjectMax(objecta,objectb)
{
if(aisDBNull)
{
returnb;
}
if(bisDBNull)
{
returna;
}
if(((IComparable)a).CompareTo(b)==1)
{
returna;
}
else
{
returnb;
}
}
privateobjectAdd(objecta,objectb)
{
if(aisDBNull)
{
returnb;
}
if(bisDBNull)
{
returna;
}
return((decimal)a+(decimal)b);
}
privateDataTableCreateJoinTable(stringtableName,DataTablesourceTable,stringfieldList)
{
if(fieldList==null)
{
returnsourceTable.Clone();
}
else
{
DataTabledt=newDataTable(tableName);
ParseFieldList(fieldList,true);
foreach(FieldInfofieldinm_FieldInfo)
{
if(field.RelationName==null)
{
DataColumndc=sourceTable.Columns[field.FieldName];
dt.Columns.Add(dc.ColumnName,dc.DataType,dc.Expression);
}
else
{
DataColumndc=sourceTable.ParentRelations[field.RelationName].ParentTable.Columns[field.FieldName];
dt.Columns.Add(dc.ColumnName,dc.DataType,dc.Expression);
}
}
if(ds!=null)
{
ds.Tables.Add(dt);
}
returndt;
}
}
privatevoidInsertJoinInto(DataTabledestTable,DataTablesourceTable,
stringfieldList,stringrowFilter,stringsort)
{
if(fieldList==null)
{
return;
}
else
{
ParseFieldList(fieldList,true);
DataRow[]Rows=sourceTable.Select(rowFilter,sort);
foreach(DataRowSourceRowinRows)
{
DataRowDestRow=destTable.NewRow();
foreach(FieldInfoFieldinm_FieldInfo)
{
if(Field.RelationName==null)
{
DestRow[Field.FieldName]=SourceRow[Field.FieldName];
}
else
{
DataRowParentRow=SourceRow.GetParentRow(Field.RelationName);
DestRow[Field.FieldName]=ParentRow[Field.FieldName];
}
}
destTable.Rows.Add(DestRow);
}
}
}
#endregion
#regionSelectDistinct/Distinct
///<summary>
///按照fieldName从sourceTable中选择出不重复的行，
///相当于selectdistinctfieldNamefromsourceTable
///</summary>
///<paramname="tableName">表名</param>
///<paramname="sourceTable">源DataTable</param>
///<paramname="fieldName">列名</param>
///<returns>一个新的不含重复行的DataTable，列只包括fieldName指明的列</returns>
publicDataTableSelectDistinct(stringtableName,DataTablesourceTable,stringfieldName)
{
DataTabledt=newDataTable(tableName);
dt.Columns.Add(fieldName,sourceTable.Columns[fieldName].DataType);
objectlastValue=null;
foreach(DataRowdrinsourceTable.Select("",fieldName))
{
if(lastValue==null||!(ColumnEqual(lastValue,dr[fieldName])))
{
lastValue=dr[fieldName];
dt.Rows.Add(newobject[]{lastValue});
}
}
if(ds!=null&&!ds.Tables.Contains(tableName))
{
ds.Tables.Add(dt);
}
returndt;
}
///<summary>
///按照fieldName从sourceTable中选择出不重复的行，
///相当于selectdistinctfieldName1,fieldName2,,fieldNamenfromsourceTable
///</summary>
///<paramname="tableName">表名</param>
///<paramname="sourceTable">源DataTable</param>
///<paramname="fieldNames">列名数组</param>
///<returns>一个新的不含重复行的DataTable，列只包括fieldNames中指明的列</returns>
publicDataTableSelectDistinct(stringtableName,DataTablesourceTable,string[]fieldNames)
{
DataTabledt=newDataTable(tableName);
object[]values=newobject[fieldNames.Length];
stringfields="";
for(inti=0;i<fieldNames.Length;i++)
{
dt.Columns.Add(fieldNames[i],sourceTable.Columns[fieldNames[i]].DataType);
fields+=fieldNames[i]+",";
}
fields=fields.Remove(fields.Length-1,1);
DataRowlastRow=null;
foreach(DataRowdrinsourceTable.Select("",fields))
{
if(lastRow==null||!(RowEqual(lastRow,dr,dt.Columns)))
{
lastRow=dr;
for(inti=0;i<fieldNames.Length;i++)
{
values[i]=dr[fieldNames[i]];
}
dt.Rows.Add(values);
}
}
if(ds!=null&&!ds.Tables.Contains(tableName))
{
ds.Tables.Add(dt);
}
returndt;
}
///<summary>
///按照fieldName从sourceTable中选择出不重复的行，
///并且包含sourceTable中所有的列。
///</summary>
///<paramname="tableName">表名</param>
///<paramname="sourceTable">源表</param>
///<paramname="fieldName">字段</param>
///<returns>一个新的不含重复行的DataTable</returns>
publicDataTableDistinct(stringtableName,DataTablesourceTable,stringfieldName)
{
DataTabledt=sourceTable.Clone();
dt.TableName=tableName;
objectlastValue=null;
foreach(DataRowdrinsourceTable.Select("",fieldName))
{
if(lastValue==null||!(ColumnEqual(lastValue,dr[fieldName])))
{
lastValue=dr[fieldName];
dt.Rows.Add(dr.ItemArray);
}
}
if(ds!=null&&!ds.Tables.Contains(tableName))
{
ds.Tables.Add(dt);
}
returndt;
}
///<summary>
///按照fieldNames从sourceTable中选择出不重复的行，
///并且包含sourceTable中所有的列。
///</summary>
///<paramname="tableName">表名</param>
///<paramname="sourceTable">源表</param>
///<paramname="fieldNames">字段</param>
///<returns>一个新的不含重复行的DataTable</returns>
publicDataTableDistinct(stringtableName,DataTablesourceTable,string[]fieldNames)
{
DataTabledt=sourceTable.Clone();
dt.TableName=tableName;
stringfields="";
for(inti=0;i<fieldNames.Length;i++)
{
fields+=fieldNames[i]+",";
}
fields=fields.Remove(fields.Length-1,1);
DataRowlastRow=null;
foreach(DataRowdrinsourceTable.Select("",fields))
{
if(lastRow==null||!(RowEqual(lastRow,dr,dt.Columns)))
{
lastRow=dr;
dt.Rows.Add(dr.ItemArray);
}
}
if(ds!=null&&!ds.Tables.Contains(tableName))
{
ds.Tables.Add(dt);
}
returndt;
}
#endregion
#regionSelectTableInto
///<summary>
///按sort排序，按rowFilter过滤sourceTable，
///复制fieldList中指明的字段的数据到新DataTable，并返回之
///</summary>
///<paramname="tableName">表名</param>
///<paramname="sourceTable">源表</param>
///<paramname="fieldList">字段列表</param>
///<paramname="rowFilter">过滤条件</param>
///<paramname="sort">排序</param>
///<returns>新DataTable</returns>
publicDataTableSelectInto(stringtableName,DataTablesourceTable,
stringfieldList,stringrowFilter,stringsort)
{
DataTabledt=CreateTable(tableName,sourceTable,fieldList);
InsertInto(dt,sourceTable,fieldList,rowFilter,sort);
returndt;
}
#endregion
#regionGroupByTable
publicDataTableSelectGroupByInto(stringtableName,DataTablesourceTable,stringfieldList,
stringrowFilter,stringgroupBy)
{
DataTabledt=CreateGroupByTable(tableName,sourceTable,fieldList);
InsertGroupByInto(dt,sourceTable,fieldList,rowFilter,groupBy);
returndt;
}
#endregion
#regionJoinTables
publicDataTableSelectJoinInto(stringtableName,DataTablesourceTable,stringfieldList,stringrowFilter,stringsort)
{
DataTabledt=CreateJoinTable(tableName,sourceTable,fieldList);
InsertJoinInto(dt,sourceTable,fieldList,rowFilter,sort);
returndt;
}
#endregion
#regionCreateTable
publicDataTableCreateTable(stringtableName,stringfieldList)
{
DataTabledt=newDataTable(tableName);
DataColumndc;
string[]Fields=fieldList.Split(',');
string[]FieldsParts;
stringExpression;
foreach(stringFieldinFields)
{
FieldsParts=Field.Trim().Split("".ToCharArray(),3);//allowforspacesintheexpression
//addfieldnameanddatatype
if(FieldsParts.Length==2)
{
dc=dt.Columns.Add(FieldsParts[0].Trim(),Type.GetType("System."+FieldsParts[1].Trim(),true,true));
dc.AllowDBNull=true;
}
elseif(FieldsParts.Length==3)//addfieldname,datatype,andexpression
{
Expression=FieldsParts[2].Trim();
if(Expression.ToUpper()=="REQUIRED")
{
dc=dt.Columns.Add(FieldsParts[0].Trim(),Type.GetType("System."+FieldsParts[1].Trim(),true,true));
dc.AllowDBNull=false;
}
else
{
dc=dt.Columns.Add(FieldsParts[0].Trim(),Type.GetType("System."+FieldsParts[1].Trim(),true,true),Expression);
}
}
else
{
returnnull;
}
}
if(ds!=null)
{
ds.Tables.Add(dt);
}
returndt;
}
publicDataTableCreateTable(stringtableName,stringfieldList,stringkeyFieldList)
{
DataTabledt=CreateTable(tableName,fieldList);
string[]KeyFields=keyFieldList.Split(',');
if(KeyFields.Length>0)
{
DataColumn[]KeyFieldColumns=newDataColumn[KeyFields.Length];
inti;
for(i=1;i==KeyFields.Length-1;++i)
{
KeyFieldColumns[i]=dt.Columns[KeyFields[i].Trim()];
}
dt.PrimaryKey=KeyFieldColumns;
}
returndt;
}
#endregion
}
}
更多关于asp.net相关内容感兴趣的读者可查看本站专题：《asp.net操作json技巧总结》、《asp.net字符串操作技巧汇总》、《asp.net操作XML技巧总结》、《asp.net文件操作技巧汇总》、《asp.netajax技巧总结专题》及《asp.net缓存操作技巧总结》。
希望本文所述对大家asp.net程序设计有所帮助。