使用代码创建一个DataTable
c#datatable分组groupby
c#datatable分组groupbyDataTable dt = new DataTable();dt.Columns.Add("A");dt.Columns.Add("B");dt.Columns.Add("C");dt.Rows.Add("a1", "b1", "c1");dt.Rows.Add("a1", "b2", "c2");dt.Rows.Add("a2", "b3", "c3");dt.Rows.Add("a3", "b4", "c4");dt.Rows.Add("a1", "b5", "c5");dt.Rows.Add("a2", "b6", "c6");IEnumerable<IGrouping<string, DataRow>> result = dt.Rows.Cast<DataRow>().GroupBy<DataRow, string>(dr => dr["A"].ToString());//按A分组foreach (IGrouping<string, DataRow> ig in result){Console.WriteLine("当A="+ig.Key + "：");Console.WriteLine("A".PadRight(10) + "B".PadRight(10) + "C".PadRight(10));Console.WriteLine("=====".PadRight(10) + "=====".PadRight(10) + "=====".PadRight(10));foreach (var dr in ig)Console.WriteLine(dr["A"].ToString().PadRight(10) + dr["B"].ToString().PadRight(10) + dr["C"].ToString().PadRight(10));}另外微软知识库⾥⾯有个DataSetHelper ，可以对DataSet中的DataTable进⾏Distinct、Group by、Join和Createusing System;using System.Collections;using System.Data;namespace Common{/**//**//**//// <summary>/// DataSet助⼿/// </summary>public class DataSetHelper{private class FieldInfo{public string RelationName;public string FieldName;public string FieldAlias;public string Aggregate;}private DataSet ds;private ArrayList m_FieldInfo;private string m_FieldList;private ArrayList GroupByFieldInfo;private string GroupByFieldList;public DataSet DataSet{get { return ds; }}ConstructionConstruction#region Constructionpublic DataSetHelper(){ds = null;}public DataSetHelper(ref DataSet dataSet){ds = dataSet;}#endregionPrivate MethodsPrivate Methods#region Private Methodsprivate bool ColumnEqual(object objectA, object objectB){if ( objectA == DBNull.Value && objectB == DBNull.Value ){return true;{return false;}return ( objectA.Equals( objectB ) );}private bool RowEqual(DataRow rowA, DataRow rowB, DataColumnCollection columns){bool result = true;for ( int i = 0; i < columns.Count; i++ ){result &= ColumnEqual( rowA[ columns[ i ].ColumnName ], rowB[ columns[ i ].ColumnName ] ); }return result;}private void ParseFieldList(string fieldList, bool allowRelation){if ( m_FieldList == fieldList ){return;}m_FieldInfo = new ArrayList();m_FieldList = fieldList;FieldInfo Field;string[] FieldParts;string[] Fields = fieldList.Split( ',' );for ( int i = 0; i <= Fields.Length - 1; i++ ){Field = new FieldInfo();FieldParts = Fields[ i ].Trim().Split( ' ' );switch ( FieldParts.Length ){case 1://to be set at the end of the loopbreak;case 2:Field.FieldAlias = FieldParts[ 1 ];break;default:return;}FieldParts = FieldParts[ 0 ].Split( '.' );switch ( FieldParts.Length ){case 1:Field.FieldName = FieldParts[ 0 ];break;case 2: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 );}}private DataTable CreateTable(string tableName, DataTable sourceTable, string fieldList){DataTable dt;if ( fieldList.Trim() == "" ){dt = sourceTable.Clone();dt.TableName = tableName;}else{dt = new DataTable( tableName );ParseFieldList( fieldList, false );DataColumn dc;foreach ( FieldInfo Field in m_FieldInfo ){dc = sourceTable.Columns[ Field.FieldName ];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 );}return dt;}private void InsertInto(DataTable destTable, DataTable sourceTable, string fieldList, string rowFilter, string sort){ParseFieldList( fieldList, false );DataRow[] rows = sourceTable.Select( rowFilter, sort );DataRow destRow;foreach ( DataRow sourceRow in rows ){destRow = destTable.NewRow();if ( fieldList == "" ){foreach ( DataColumn dc in destRow.Table.Columns ){if ( dc.Expression == "" ){destRow[ dc ] = sourceRow[ dc.ColumnName ];}}}else{foreach ( FieldInfo field in m_FieldInfo ){destRow[ field.FieldAlias ] = sourceRow[ field.FieldName ]; }}destTable.Rows.Add( destRow );}}private void ParseGroupByFieldList(string FieldList){if ( GroupByFieldList == FieldList ){return;}GroupByFieldInfo = new ArrayList();FieldInfo Field;string[] FieldParts;string[] Fields = FieldList.Split( ',' );for ( int i = 0; i <= Fields.Length - 1; i++ ){Field = new FieldInfo();FieldParts = Fields[ i ].Trim().Split( ' ' );switch ( FieldParts.Length ){case 1://to be set at the end of the loopbreak;case 2:Field.FieldAlias = FieldParts[ 1 ];break;default:return;}FieldParts = FieldParts[ 0 ].Split( '(' );switch ( FieldParts.Length ){case 1:Field.FieldName = FieldParts[ 0 ];break;case 2:Field.Aggregate = FieldParts[ 0 ].Trim().ToLower();Field.FieldName = FieldParts[ 1 ].Trim( ' ', ')' );break;default:return;}if ( Field.Aggregate == null ){Field.FieldAlias = Field.FieldName;}else{Field.FieldAlias = Field.Aggregate + "of" + Field.FieldName;}}GroupByFieldInfo.Add( Field );}GroupByFieldList = FieldList;}private DataTable CreateGroupByTable(string tableName, DataTable sourceTable, string fieldList) {if ( fieldList == null || fieldList.Length == 0 ){return sourceTable.Clone();}else{DataTable dt = new DataTable( tableName );ParseGroupByFieldList( fieldList );foreach ( FieldInfo Field in GroupByFieldInfo ){DataColumn dc = 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 );}return dt;}}private void InsertGroupByInto(DataTable destTable, DataTable sourceTable, string fieldList,string rowFilter, string groupBy){if ( fieldList == null || fieldList.Length == 0 ){return;}ParseGroupByFieldList( fieldList );ParseFieldList( groupBy, false );DataRow[] rows = sourceTable.Select( rowFilter, groupBy );DataRow lastSourceRow = null, destRow = null;bool sameRow;int rowCount = 0;foreach ( DataRow sourceRow in rows ){sameRow = false;if ( lastSourceRow != null ){sameRow = true;foreach ( FieldInfo Field in m_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;switch ( field.Aggregate.ToLower() ){case null: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 );}}private object Min(object a, object b){if ( ( a is DBNull ) || ( b is DBNull ) ){return DBNull.Value;}if ( ( (IComparable) a ).CompareTo( b ) == -1 ){return a;}else{return b;}}private object Max(object a, object b){if ( a is DBNull ){return b;}if ( b is DBNull ){return a;}if ( ( (IComparable) a ).CompareTo( b ) == 1 ){return a;}else{return b;}}private object Add(object a, object b){if ( a is DBNull ){if ( b is DBNull ){return a;}return ( (decimal) a + (decimal) b );}private DataTable CreateJoinTable(string tableName, DataTable sourceTable, string fieldList){if ( fieldList == null ){return sourceTable.Clone();}else{DataTable dt = new DataTable( tableName );ParseFieldList( fieldList, true );foreach ( FieldInfo field in m_FieldInfo ){if ( field.RelationName == null ){DataColumn dc = sourceTable.Columns[ field.FieldName ];dt.Columns.Add( dc.ColumnName, dc.DataType, dc.Expression );}else{DataColumn dc = sourceTable.ParentRelations[ field.RelationName ].ParentTable.Columns[ field.FieldName ]; dt.Columns.Add( dc.ColumnName, dc.DataType, dc.Expression );}}if ( ds != null ){ds.Tables.Add( dt );}return dt;}}private void InsertJoinInto(DataTable destTable, DataTable sourceTable,string fieldList, string rowFilter, string sort){if ( fieldList == null ){return;}else{ParseFieldList( fieldList, true );DataRow[] Rows = sourceTable.Select( rowFilter, sort );foreach ( DataRow SourceRow in Rows ){DataRow DestRow = destTable.NewRow();foreach ( FieldInfo Field in m_FieldInfo ){if ( Field.RelationName == null ){DestRow[ Field.FieldName ] = SourceRow[ Field.FieldName ];}else{DataRow ParentRow = SourceRow.GetParentRow( Field.RelationName );DestRow[ Field.FieldName ] = ParentRow[ Field.FieldName ];}}destTable.Rows.Add( DestRow );}}}#endregionSelectDistinct / DistinctSelectDistinct / Distinct#region SelectDistinct / Distinct/**//**//**//// <summary>/// 按照fieldName从sourceTable中选择出不重复的⾏，/// 相当于select distinct fieldName from sourceTable/// </summary>/// <param name="tableName">表名</param>/// <param name="sourceTable">源DataTable</param>/// <param name="fieldName">列名</param>/// <returns>⼀个新的不含重复⾏的DataTable，列只包括fieldName指明的列</returns>public DataTable SelectDistinct(string tableName, DataTable sourceTable, string fieldName)DataTable dt = new DataTable( tableName );dt.Columns.Add( fieldName, sourceTable.Columns[ fieldName ].DataType );object lastValue = null;foreach ( DataRow dr in sourceTable.Select( "", fieldName ) ){if ( lastValue == null || !( ColumnEqual( lastValue, dr[ fieldName ] ) ) ){lastValue = dr[ fieldName ];dt.Rows.Add( new object[]{lastValue} );}}if ( ds != null && !ds.Tables.Contains( tableName ) ){ds.Tables.Add( dt );}return dt;}/**//**//**//// <summary>/// 按照fieldName从sourceTable中选择出不重复的⾏，/// 相当于select distinct fieldName1,fieldName2,,fieldNamen from sourceTable/// </summary>/// <param name="tableName">表名</param>/// <param name="sourceTable">源DataTable</param>/// <param name="fieldNames">列名数组</param>/// <returns>⼀个新的不含重复⾏的DataTable，列只包括fieldNames中指明的列</returns>public DataTable SelectDistinct(string tableName, DataTable sourceTable, string[] fieldNames) {DataTable dt = new DataTable( tableName );object[] values = new object[fieldNames.Length];string fields = "";for ( int i = 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 );DataRow lastRow = null;foreach ( DataRow dr in sourceTable.Select( "", fields ) ){if ( lastRow == null || !( RowEqual( lastRow, dr, dt.Columns ) ) ){lastRow = dr;for ( int i = 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 );}return dt;}/**//**//**//// <summary>/// 按照fieldName从sourceTable中选择出不重复的⾏，/// 并且包含sourceTable中所有的列。
dataTables使用的详细说明整理
dataTables使⽤的详细说明整理本⽂共三个部分：官⽹|基本使⽤|遇到的问题⼀、官⽅⽹站：⼆、基本使⽤：1、dataTables的引⼊及初始化<!--第⼀步：引⼊Javascript / CSS （CDN）--><!-- DataTables CSS --><link rel="stylesheet" type="text/css" href="/1.10.15/css/jquery.dataTables.css"><!-- jQuery --><script type="text/javascript" charset="utf8" src="/jquery-1.10.2.min.js"></script><!-- DataTables --><script type="text/javascript" charset="utf8" src="/1.10.15/js/jquery.dataTables.js"></script><!--第三步：初始化Datatables-->$(document).ready( function () {$('#example').DataTable();} );2、dataTables的基础属性配置"bPaginate": true, //翻页功能"bLengthChange": true, //改变每页显⽰数据数量"bFilter": true, //过滤功能"bSort": false, //排序功能"bInfo": true,//页脚信息"bAutoWidth": true//⾃动宽度View Code3、关于排序的四个属性区分bSort : 初始化的时候可以定义整个表格是否需要进⾏排序$(document).ready( function () {$('#example').dataTable( {"bSort": false} );} );aaSorting : 初始化的时候可以⽤来定义表格根据哪⼀列进⾏排序$(document).ready( function() {$('#example').dataTable( {//先对第⼆列降序排列，在对第四列升序排列"aaSorting": [[1,'desc'], [3,'asc']]} );} );bSortable : 初始化的时候可以⽤来定义哪⼀列可以进⾏排序$(document).ready( function() {$('#example').dataTable( {"aoColumns": [{ "bSortable": false },null,null,null,null] } );} );asSorting : 可以⽤来设置某⼀⾏的排序规则$(document).ready( function() {$('#example').dataTable( {"aoColumns": [null,{ "asSorting": [ "asc" ] },{ "asSorting": [ "desc", "asc", "asc" ] },{ "asSorting": [ "desc" ] },null]} );} );4、展⽰⼀个表格的基本配置$('#myTable').dataTable({"bJQueryUI": true,//是否使⽤jquery中的ui theme"bProcessing": true, //DataTables载⼊数据时，是否显⽰‘进度’提⽰"bServerSide": false, //是否启动服务器端数据导⼊,也就是调⽤后台数据的时候"bStateSave": true, //是否打开客户端状态记录功能,此功能在ajax刷新纪录的时候不会将个性化设定回复为初始化状态"sPaginationType": "full_numbers",//详细分页组，可以⽀持直接跳转到某页"bScrollInfinite": false, //是否启动初始化滚动条"bScrollCollapse": false, //是否开启DataTables的⾼度⾃适应，当数据条数不够分页数据条数的时候，插件⾼度是否随数据条数⽽改变"bPaginate": true, //翻页功能"aLengthMenu": [5, 10, 15], //更改显⽰记录数选项"iDisplayLength": 9, //默认显⽰的记录数"sScrollY": 300,//规定表格⾼度，出现滚动条"sScrollX": "50%",////是否开启⽔平滚动，以及指定滚动区域⼤⼩,可设值：'disabled','2000%'"bLengthChange": true, //改变每页显⽰数据数量"bFilter": true, //过滤功能"bSort": false, //排序功能，初始化的时候定义整个表格是否进⾏排序"bInfo": true,//页脚信息，即表格左下⾓显⽰多少条数据的提⽰"bAutoWidth": true,//⾃动宽度，false为禁⽌⾃动计算列宽// 'aaSorting': [[2, 'asc']],//bSort失效"aaData": [{"Office": "IJD","Show": "X"},{"Id": "002","Name": "张三","Salary": "450,800", "Date": "2012-3-19", "Office": "DPD","Show": "X"},{"Id": "003","Name": "李四","Salary": "120,800", "Date": "2013-3-21", "Office": "ICD","Show": "C"},{"Id": "004","Name": "王五","Salary": "50,800", "Date": "2011-11-11", "Office": "ISD","Show": "X"},{"Id": "005","Name": "张张","Salary": "780,800", "Date": "2013-2-22", "Office": "ADM","Show": "X"},{"Id": "006","Name": "ni","Salary": "320,800", "Date": "2012-10-10", "Office": "IJD","Show": "X"},{"Id": "007","Name": "张三","Salary": "450,800", "Date": "2012-3-19", "Office": "DPD","Show": "X"},{"Id": "008","Name": "李四","Salary": "120,800", "Date": "2013-3-21", "Office": "ICD","Show": "C"},{"Id": "009","Name": "王五","Salary": "50,800", "Date": "2011-11-11", "Office": "ISD","Show": "X"},{"Id": "010","Name": "张张","Salary": "780,800", "Date": "2013-2-22", "Office": "ADM","Show": "X"},{"Id": "011","Name": "wo","Salary": "320,800", "Date": "2012-10-10", "Office": "IJD","Show": "X"},{"Id": "012","Name": "张三","Salary": "450,800", "Date": "2012-3-19", "Office": "DPD","Show": "X"},{"Id": "013","Name": "李四","Salary": "120,800", "Date": "2013-3-21", "Office": "ICD","Show": "C"},{"Id": "014","Name": "王五","Salary": "50,800", "Date": "2011-11-11", "Office": "ISD","Show": "X"},{"Office": "ADM","Show": "X"}],"aoColumns": [//sClass设置列的class属性值，bSearchable设置列的数据是否过滤，bVisible设置列是否显⽰，sTitle设置列的标题{"data": "Id", 'sTitle': 'Id', 'sWidth': '100px', 'sClass': 'center', "bVisible": false,},{"data": "Name", 'sTitle': '姓名', 'sWidth': '20%', 'sClass': 'center', "sDefaultContent": "", //此列默认值为""，以防数据中没有此值，DataTables加载数据的时候报错 },{ "data": "Salary", 'sTitle': '⼯资', 'sWidth': '20%', 'sClass': 'center' },{ "data": "Date", 'sTitle': '⼊职⽇期', 'sWidth': '20%', 'sClass': 'center' },{"data": "Office", 'sTitle': '所属部门', 'sWidth': '20%', 'sClass': 'center'},{"data": "Show", 'sTitle': '是否显⽰ID', 'sWidth': '20%', 'sClass': 'center', "bSearchable": false,"mRender": function (data, type, full) {//data表⽰该列的数据// return '<input type="text" class="userName" value="' + full + '"/>';if (data === 'X') {return '不显⽰'} else {return '显⽰'}}}],"oLanguage": {//国际化配置"sProcessing": "正在加载中......","sLengthMenu": "每页显⽰ _MENU_ 条记录","sZeroRecords": "对不起，查询不到相关数据！","sEmptyTable": "表中⽆数据存在！","sInfo": "当前显⽰ _START_ 到 _END_ 条，共 _TOTAL_ 条记录","sInfoFiltered": "数据表中共为 _MAX_ 条记录","sSearch": "搜索","oPaginate": {"sFirst": "⾸页","sPrevious": "上⼀页","sNext": "下⼀页","sLast": "末页"}}});View Code5、使⽤Ajax与后台交互数据时，将bServerSide属性置为true，涉及属性fnServerData"sAjaxSource": "url",//接⼝路径"fnServerData": function (sUrl, aoData, fnCallback) {$.ajax({"url": sUrl,"type": 'POST',"dataType": 'json',"contentType": "application/x-www-form-urlencoded; charset=utf-8","cache": false,"data": aoData,"success": (res) => {//console.log(res.data.data)fnCallback(res.data.data)}})}View Code三、实例展⽰1、引⼊⽂件2、创建js⽂件var iDisplayStart = 0;var iDisplayLength = 6;$('#tableList').dataTable({"bJQueryUI": true,//是否使⽤jquery中的ui theme"bProcessing": true, //DataTables载⼊数据时，是否显⽰‘进度’提⽰"bServerSide": true, //是否启动服务器端数据导⼊,也就是调⽤后台数据的时候"bStateSave": false, //是否打开客户端状态记录功能,此功能在ajax刷新纪录的时候不会将个性化设定回复为初始化状态"sPaginationType": "full_numbers",//详细分页组，可以⽀持直接跳转到某页"bScrollInfinite": false, //是否启动初始化滚动条"bScrollCollapse": false, //是否开启DataTables的⾼度⾃适应，当数据条数不够分页数据条数的时候，插件⾼度是否随数据条数⽽改变"bPaginate": true, //翻页功能// "aLengthMenu": [5, 10, 15], //更改显⽰记录数选项// "iDisplayLength": 9, //默认显⽰的记录数// "sScrollY": 100,//规定表格⾼度，出现滚动条// "sScrollX": "50%",////是否开启⽔平滚动，以及指定滚动区域⼤⼩,可设值：'disabled','2000%'// "bLengthChange": true, //改变每页显⽰数据数量"bFilter": false, //过滤功能"bSort": false, //排序功能，初始化的时候定义整个表格是否进⾏排序"bInfo": false,//页脚信息，即表格左下⾓显⽰多少条数据的提⽰"bAutoWidth": true,//⾃动宽度，false为禁⽌⾃动计算列宽// 'aaSorting': [[2, 'asc']],//bSort失效"iDisplayStart": iDisplayStart,"iDisplayLength": iDisplayLength,//默认显⽰的记录数// "aaData": [// {// }// ],"columns": [//sClass设置列的class属性值，bSearchable设置列的数据是否过滤，bVisible设置列是否显⽰，sTitle设置列的标题// {bVisible表⽰不再表格中显⽰// "data": "caseId", 'sTitle': '序号', 'sWidth': '8%', 'sClass': 'center', "bVisible": false, "bSortable": false, "bUseRendered": false// },{"data": "caseId", 'sTitle': '序号', 'sWidth': '2%', 'sClass': 'text-center', "bSortable": false, "bUseRendered": false},{"data": "caseName", 'sTitle': '案件名称', 'sWidth': '20%', 'sClass': 'text-center', "sDefaultContent": "", "bSortable": false, "bUseRendered": false//此列默认值为""，以防数据中没有此值，DataTables加载数据的时候报错 },{"data": "caseCode", 'sTitle': '序号', 'sWidth': '14%', 'sClass': 'text-center', "bSortable": false, "bUseRendered": false},{"data": "caseCauseName", 'sTitle': '原因', 'sWidth': '8%', 'sClass': 'text-center', "bSortable": false, "bUseRendered": false},{"data": "underTakerName", 'sTitle': '收押⼈', 'sWidth': '8%', 'sClass': 'text-center', "bSortable": false, "bUseRendered": false},{"data": "managerName", 'sTitle': '管理⼈员', 'sWidth': '8%', 'sClass': 'text-center', "bSortable": false, "bUseRendered": false},{"data": "registerDate", 'sTitle': '⽇期', 'sWidth': '8%', 'sClass': 'text-center', "bSortable": false, "bUseRendered": false},{//⾃定义列"mDataProp": "userType",'sTitle': '登录⽤户⾓⾊','sWidth': '10%','sClass': 'text-center',// "fnRender": function (oObj) {// if (erType == '1' || erType == '3') {// return '⽤户'// } else {// return '管理员'// }// },"bSortable": false,"bUseRendered": false},{"sDefaultContent": "",'sTitle': '操作','sWidth': '7%','sClass': 'text-center',"render": function (data, type, full) { // 返回⾃定义内容return"<span id='factorConfirm' style='display:inline-block;color:#01d1b8;text-decoration:underline;padding-bottom:3px;cursor:pointer'>跳转按钮</span>";}}],"fnRowCallback": function (nRow, aData, iDisplayIndex) {//格式化数据//iDisplayIndex指当前⾏的序号每⾏加载完成，将当前列进⾏替换if (erType == '1' || erType == '3') {$('td:eq(7)', nRow).html('⽤户');} else {$('td:eq(7)', nRow).html('管理员');}return nRow;},"oLanguage": {//国际化配置"sProcessing": "正在加载中......","sLengthMenu": "每页显⽰ _MENU_ 条记录","sZeroRecords": "对不起，查询不到相关数据！","sEmptyTable": "表中⽆数据存在！","sInfo": "当前显⽰ _START_ 到 _END_ 条，共 _TOTAL_ 条记录","sInfoFiltered": "数据表中共为 _MAX_ 条记录","sSearch": "搜索","oPaginate": {"sFirst": "⾸页","sPrevious": "上⼀页","sNext": "下⼀页","sLast": "末页"}},"sAjaxSource": requestUrl,//接⼝路径"fnServerData": function (sUrl, aoData, fnCallback) {$.ajax({"url": sUrl,"type": 'GET',"dataType": 'json',"dataSrc": '',"contentType": "application/x-www-form-urlencoded; charset=utf-8","cache": false,"data": aoData,"sDom": "frtlip","sPaginationType": "full_numbers","success": (res) => {//处理返回的数据fnCallback(handleData(res)); //处理返回的数据// 点击跳转按钮$("#factorConfirm").on('click', function (e) {//获取本⾏数据(对象）var currentData = $('#tableList').DataTable().row($(this).parents('tr')).data();window.location.href = window.location.origin + '/html/ceshi.html?name=' + name + "&age=" + age});},"error": function (xhr, error, thrown) {top.showInfoWinError("发⽣异常:" + error);}})}});function handleData(json) { // 处理返回的数据var obj = { code: 1 };obj.iTotalDisplayRecords = obj.iTotalRecords = json.body.data.length;obj.aaData = json.body.data;return obj;}View Code四、遇到的问题1、设置列属性'sClass': 'text-center',每列的内容⽆法居中，样式失效解决⽅法：引⼊bootstrap的js、css<!-- DataTables CSS --><link rel="stylesheet" type="text/css" href="/1.10.15/css/jquery.dataTables.css"><link rel="stylesheet" href="https:///bootstrap/3.3.7/css/bootstrap.min.css"integrity="sha384-BVYiiSIFeK1dGmJRAkycuHAHRg32OmUcww7on3RYdg4Va+PmSTsz/K68vbdEjh4u" crossorigin="anonymous"> <!-- jQuery --><script type="text/javascript" charset="utf8" src="/jquery-1.10.2.min.js"></script><!-- DataTables --><script type="text/javascript" charset="utf8"src="/1.10.15/js/jquery.dataTables.js"></script><script src="https:///bootstrap/3.3.7/js/bootstrap.min.js"integrity="sha384-Tc5IQib027qvyjSMfHjOMaLkfuWVxZxUPnCJA7l2mCWNIpG9mGCD8wGNIcPD7Txa"crossorigin="anonymous"></script>View Code2、与后台交互的时候，设置了 iDisplayLength 属性为6，属性失效解决⽅法：设置翻页属性为true，否则不起作⽤"bPaginate": true, //翻页功能3、当后台数据返回数值型1、2等数字，需要在表格内进⾏格式化内容解决⽅法：设置与columns属性同级的fnRowCallback属性进⾏列内容格式化"fnRowCallback": function (nRow, aData, iDisplayIndex) {//格式化数据//iDisplayIndex指当前⾏的序号每⾏加载完成，将当前列进⾏替换if (erType == '1' || erType == '3') {$('td:eq(7)', nRow).html('⽤户');} else {$('td:eq(7)', nRow).html('管理员');}return nRow;}4、需要在每⾏⼀列操作列、即后台没有返回该属性，我们需要⾃定义列解决⽅法{"sDefaultContent": "",//不是data属性'sTitle': '操作','sWidth': '7%','sClass': 'text-center',"render": function (data, type, full) { // 返回⾃定义内容return"<span id='factorConfirm' style='display:inline-block;color:#01d1b8;text-decoration:underline; padding-bottom:3px;cursor:pointer'>要素确认</span>";}}5、后台返回数据后，如何处理才能显⽰到表格中解决⽅法function handleData(json) { // 处理返回的数据var obj = { code: 1 };obj.iTotalDisplayRecords = obj.iTotalRecords = json.body.data.length;obj.aaData = json.body.data;return obj;}//success成功后，调⽤函数fnCallback(handleData(res))6、原⽣JS中，通过url传值，解析参数解决⽅法function parseURL(url) {var url = url.split("?")[1];var para = url.split("&");var len = para.length;var res = {};var arr = [];for (var i = 0; i < len; i++) {arr = para[i].split("=");res[arr[0]] = arr[1];}return res;}。
使用DataTable更新数据库（增,删,改）
使⽤DataTable更新数据库（增,删,改）1、修改数据复制代码代码如下:DataRow dr = hRDataSet.Tables["emp"].Rows.Find(textBox3.Text);//DataRow dr = hRDataSet.Tables["emp"].Select("id="+textBox3.Text)[0];dr.BeginEdit();dr["name"] = textBox1.Text;dr.EndEdit();SqlCommandBuilder cmd=new SqlCommandBuilder(empTableAdapter);empTableAdapter.Update(hRDataSet);this.empTableAdapter.Fill(this.hRDataSet.emp);2、插⼊数据复制代码代码如下:DataSet ds = new DataSet();using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["db"].ConnectionString)) {String sql = "select id,name,age from emp";SqlDataAdapter adp = new SqlDataAdapter(sql, conn);adp.Fill(ds);DataRow dr=ds.Tables[0].NewRow();dr["name"]=txtName.Text;dr["age"] = Convert.ToInt32(txtAge.Text);SqlCommandBuilder cmd=new SqlCommandBuilder(adp);ds.Tables[0].Rows.Add(dr);adp.Update(ds);}3、删除数据复制代码代码如下:DataRow dr = hRDataSet.Tables["emp"].Rows.Find(textBox3.Text);//DataRow dr = hRDataSet.Tables["emp"].Select("id="+textBox3.Text)[0];dr.Delete();SqlCommandBuilder cmd=new SqlCommandBuilder(empTableAdapter);empTableAdapter.Update(hRDataSet);this.empTableAdapter.Fill(this.hRDataSet.emp);。
cmbbox中方datasource的用法
cmbbox中方datasource的用法在使用C#开发Windows桌面应用程序时，我们经常需要在ComboBox控件中显示数据列表。
ComboBox控件可以通过两种方式来显示数据列表，一种是手动添加，另一种是使用DataSource属性绑定数据源。
在使用DataSource属性绑定数据源时，我们需要先创建一个数据源对象。
数据源可以是数组、集合、DataTable、DataSet等。
以DataTable为例，我们可以使用以下代码创建一个DataTable对象：DataTable dt = new DataTable();dt.Columns.Add('ID', typeof(int));dt.Columns.Add('Name', typeof(string));dt.Rows.Add(1, '张三');dt.Rows.Add(2, '李四');dt.Rows.Add(3, '王五');然后，我们可以将这个DataTable对象设置为ComboBox的DataSource属性：comboBox1.DataSource = dt;接着，我们需要指定ComboBox控件显示哪个字段。
比如，我们可以让ComboBox显示DataTable中的Name字段：comboBox1.DisplayMember = 'Name';最后，我们还需要指定ComboBox控件选中项的值是哪个字段。
比如，我们可以让ComboBox选中项的值为DataTable中的ID字段：comboBox1.ValueMember = 'ID';这样，ComboBox控件就可以显示DataTable中的数据了。
当用户选择某个选项时，ComboBox控件会返回该选项的ID值。
除了DataTable，我们还可以使用其他类型的数据源对象。
DATASET控件的用法详细
或者
ds.Tables["Customers"].RemoveAt(intIndex);
③检查 DatTable 中的 DataRow 对象
foreach(DataRow row in tbl.Rows) DisplayRow(row);
4、校验 DataSet 中的数据 ①校验 DataColumn 的属性：ReadOnly,AllowDBNull,MaxLength,Unique ②DataTable 对象的 Constrains 集合：UiqueConstraints,Primarykey, ForeignkeyConstraints 通常不必刻意去创建 ForeignkeyConstraints,因为当在 DataSet 的两个 DataTable 对象之间创建关 系时会创建一个。 ③用 SqlDataAdapter.Fill 模式来检索模式信息 5、编写代码创建 DataTable 对象 ①创建 DataTable 对象:
③处理 DataRow 的空值
//查看是否为空 DataRow rowCustomer; rowCustomer = ds.Tables["Customers"].Rows.Find("ALFKI"); if(rowCustomer.IsNull("Phone")) Console.WriteLine("It''''s Null"); else Console.WriteLine("It''''s not Null"); //赋予空值
DevexpressWinFormTreeList的三种数据绑定方式（DataSource。。。
DevexpressWinFormTreeList的三种数据绑定⽅式（DataSource。
第⼀种:DataSource绑定,这种绑定⽅式需要设置TreeList的ParentFieldName和KeyFieldName两个属性，这⾥需要注意的是KeyFieldName 的值必须是唯⼀的。
代码如下:private void Form1_Load(object sender, EventArgs e){try{//构建⼀个DataTable数据源DataTable table = new DataTable();table.Columns.Add("parentId");table.Columns.Add("Id");table.Columns.Add("parentName");table.Columns.Add("Name");DataRow row = table.NewRow();row["parentId"] = "";row["Id"] = "*";row["Name"] = "所有颜⾊";table.Rows.Add(row);row = table.NewRow();row["parentId"] = "*";row["Id"] = "1";row["Name"] = "红⾊";table.Rows.Add(row);row = table.NewRow();row["parentId"] = "*";row["Id"] = "2";row["Name"] = "黄⾊";table.Rows.Add(row);row = table.NewRow();row["parentId"] = "*";row["Id"] = "3";row["Name"] = "绿⾊";table.Rows.Add(row);row = table.NewRow();row["parentId"] = "1";row["Id"] = "01";row["Name"] = "粉红⾊";table.Rows.Add(row);row = table.NewRow();row["parentId"] = "2";row["Id"] = "02";row["Name"] = "鹅黄⾊";table.Rows.Add(row);treeList1.ParentFieldName = "parentId";treeList1.KeyFieldName = "Id";treeList1.DataSource = table;treeList1.ExpandAll();}catch (Exception ex){MessageBox.Show(ex.Message);}}private void treeList1_BeforeCheckNode(object sender, DevExpress.XtraTreeList.CheckNodeEventArgs e){e.State = (e.PrevState == CheckState.Checked ? CheckState.Unchecked : CheckState.Checked);}private void treeList1_AfterCheckNode(object sender, DevExpress.XtraTreeList.NodeEventArgs e){try{SetCheckedChildNodes(e.Node, e.Node.CheckState);SetCheckedParentNodes(e.Node, e.Node.CheckState);}catch (Exception ex){MessageBox.Show(ex.Message);}}///<summary>///设置⼦节点的状态///</summary>///<param name="node"></param>///<param name="check"></param>private void SetCheckedChildNodes(TreeListNode node, CheckState check){for (int i = 0; i < node.Nodes.Count; i++){node.Nodes[i].CheckState = check;SetCheckedChildNodes(node.Nodes[i], check);}}///<summary>///设置⽗节点的状态///</summary>///<param name="node"></param>///<param name="check"></param>private void SetCheckedParentNodes(TreeListNode node, CheckState check){if (node.ParentNode != null){bool b = false;CheckState state;for (int i = 0; i < node.ParentNode.Nodes.Count; i++){state = (CheckState)node.ParentNode.Nodes[i].CheckState;if (!check.Equals(state)){b = !b;break;}}node.ParentNode.CheckState = b ? CheckState.Checked : check;SetCheckedParentNodes(node.ParentNode, check);}}运⾏效果图:第⼆种:AppendNode添加节点代码如下:private void LoadTree(){//清空节点treeList1.BeginUnboundLoad();treeList1.ClearNodes();arrayList = new List<TreeListNode>();//绑定部位树树的第⼀层TreeListNode Node = treeList1.Nodes.Add(new object[] { "所有颜⾊", "*"});Node.Tag = "*";arrayList.Add(Node);//绑定第⼆层DataSet ds = SqlHelper.Query(@"select ctc.colorId as ParentNodeID,ctc.childcolorId as NodeID, s as NodeNamefrom C_colorTocolor ctc(nolock)inner join C_color cc(nolock) on ctc.childcolorId=cc.id");if (ds != null && ds.Tables.Count > 0){table = ds.Tables[0];DataRow[] rows = table.Select("ParentNodeID='*'");foreach (DataRow row in rows){TreeListNode tn = treeList1.AppendNode(new object[] { row["NodeName"].ToString(), row["NodeID"].ToString() }, Node.Id); tn.Tag = row["NodeID"].ToString();arrayList.Add(tn);BindNode(row["NodeID"].ToString(), tn);}}treeList1.EndUnboundLoad();treeList1.ExpandAll();}private void BindNode(string nodeId, TreeListNode tn){DataRow[] rows = table.Select("ParentNodeID='" + nodeId + "'");foreach (DataRow row in rows){TreeListNode tns = treeList1.AppendNode(new object[] { row["NodeName"].ToString(), row["NodeID"].ToString()}, tn.Id);tns.Tag = row["NodeID"].ToString();arrayList.Add(tns);BindNode(row["NodeID"].ToString(), tns);}}private void Form2_Load(object sender, EventArgs e){//绑定树LoadTree();}运⾏效果图:第三种:VirtualTreeGetChildNodes虚拟树加载。
使用Linq操作Datatable，按条件筛选数据，找出数量前10的数据，按字段分组
使⽤Linq操作Datatable，按条件筛选数据，找出数量前10的数据，按字段分组public string GetAreaAndPieJson(){DataTable dt = new DataTable();DataTable dt2 =new DataTable();DataTable dtt2 =new DataTable();string areapieJson = "";BaseBLL.MobileBLL mb = new BaseBLL.MobileBLL();BaseBLL.SMZ smz = new BaseBLL.SMZ();//如果是点击分公司，来赛选数据，则通过分公司单位编号来查找数据，否则则通过系统缓存来实现if (Session["deptID"] != "" && Session["deptID"]!=null){dt = mb.GetGDRT_ZCQK2(Session["deptID"].ToString());dt2 = smz.GetRLJandAllcount(Session["deptID"].ToString());dtt2 = smz.GetRJCS_Info(Session["deptID"].ToString());}else {dt = mb.GetGDRT_ZCQK2(CondictionInfo.CurrentDWBH);dt2 = smz.GetRLJandAllcount(CondictionInfo.CurrentDWBH);dtt2 = smz.GetRJCS_Info(CondictionInfo.CurrentDWBH);}areaChartData area = new areaChartData();area.zcgrs = dt.Rows.Count.ToString();if (dt2 != null){area.zsxms = dt2.Rows[0]["zsxms"].ToString();//在施项⽬数area.zcrs = dt2.Rows[0]["zcrs"].ToString();//在册⼈数area.rljjcrs = dt2.Rows[0]["rljs"].ToString();//在场⼯⼈数}areapieJson = Newtonsoft.Json.JsonConvert.SerializeObject(area);//List<PieData> pl = new List<PieData> { };PieDaList pls = new PieDaList();pls.main();var qys1 = from t in dt.AsEnumerable()group t by new{t1 = t.Field<string>("GZ"),} into jjjselect new{GZ = jjj.Key.t1};if (qys1 != null){foreach (var qy in qys1){int len = dt.Select("GZ='" + qy.GZ + "'").Length;Pievalue pv = new Pievalue();pv.value = len; = qy.GZ+ "(" + pv.value + "⼈)";pls.data.Add(qy.GZ + "(" + pv.value + "⼈)");pls.values.Add(pv);}}areapieJson += "---" + Newtonsoft.Json.JsonConvert.SerializeObject(pls); braChartData braLists = new braChartData();braLists.main();var qys2 = from t in dt.AsEnumerable()group t by new{t1 = t.Field<string>("GCMC"),t2 = t.Field<string>("GCBH"),} into jjjselect new{GCMC = jjj.Key.t1,GCBH = jjj.Key.t2,};DataTable GDRY = new DataTable();GDRY.Columns.Add("GCMC",typeof(string));GDRY.Columns.Add("Count", typeof(Int32));List<GDRYCount> List=new List<GDRYCount>();GDRYCount co = new GDRYCount();foreach (var qy in qys2){co = new GDRYCount();//取出前10的数据DataRow[] drr = dt.Select("GCMC='" + qy.GCMC + "'");GDRY.Rows.Add(qy.GCMC, drr.Length);co.GDMC = qy.GCMC;co.Count = drr.Length;List.Add(co);}var qys4 = (from t in GDRY.AsEnumerable()orderby t.Field<int>("Count") descendingselect t).Take(10);foreach (var qy in qys4){string GCMC = qy["GCMC"].ToString();bels.Add(GCMC);DataRow[] drr = dt.Select("GCMC='" + GCMC + "'");braLists.data1.Add(drr.Length);var q = from t in drr.AsEnumerable()group t by new{t1 = t.Field<string>("GZ"),} into jjjselect new{GZ = jjj.Key.t1};braLists.data2.Add(q.ToList().Count);braLists.data3.Add(dtt2.Select("GCBH='" +GCMC + "'").Length);}//foreach (var qy in qys2)//{// bels.Add(qy.GCMC);// DataRow[] drr = dt.Select("GCMC='" + qy.GCMC + "'");// braLists.data1.Add(drr.Length);// var q = from t in drr.AsEnumerable()// group t by new// {// t1 = t.Field<string>("GZ"),// } into jjj// select new// {// GZ = jjj.Key.t1// };// braLists.data2.Add(q.ToList().Count);// braLists.data3.Add(dtt2.Select("GCBH='" + qy.GCBH + "'").Length);//}areapieJson += "---" + Newtonsoft.Json.JsonConvert.SerializeObject(braLists); return areapieJson;}。
visual studio 做报表开发的步骤
在Visual Studio中进行报表开发的步骤大致如下：
1. 确认Visual Studio版本中包含Reportview控件。
2. 在项目上右键添加-新建项，选择Reporting中的报表。
同样右键添加-新建项，选择数据中的数据集。
3. 在新建的数据集中右键添加-DataTable。
通过添加列和修改表名建立一个和数据库中表项相同的DataTable。
4. 打开刚创建的报表（如Report1.rdlc），在弹出的界面中选择刚创建的数据源和数据集。
在报表设计界面，可以添加需要的列和其他元素。
5. 打开Form设计，拖入一个Reportview控件，选择刚配置好的报表，然后点击选择数据源。
此时会自动生成一个dataSetBindingSource，在其属性窗口DataMember中写入数据集的名称。
6. 双击窗体建立Load事件，编写用于加载和处理报表的代码。
例如，可以从数据库中提取数据并填充到数据集中，然后将数据集绑定到报表上。
7. 编译并运行程序，查看报表效果。
以上步骤是一个基本的报表开发流程，实际开发中可能需要根据具体需求进行调整和扩展。
同时，也需要注意Visual Studio版本和控件的兼容性，以及数据库连接和数据处理的安全性等问题。
Vb.netDataTable排序、检索、合并
这篇文章主要介绍了C#中DataTable排序、检索、合并等操作实例,其中详细介绍了DataTable.Select的一些注意问题和使用技巧等,需要的朋友可以参考下一、排序1.获取DataTable的默认视图2.对视图设置排序表达式3.用排序后的视图导出的新DataTable替换就DataTable(Asc升序可省略，多列排序用"，"隔开)1）、重生法复制代码代码如下:dstaset.Tables.Add(dt)dataset.Tables(0).DefaultView.Sort = "id desc"2）、直接法复制代码代码如下:dv = New DataView(dt)dv.Sort = "id desc"dt = dv.ToTable();3）、间接法复制代码代码如下:dv = New DataView(ds.Tables[0])dv.Sort = "id desc"dt = dv.ToTable();二、检索设置查询字符串使用Select方法获取到所有满足条件的数据行对象数组 (多项查询条件间，用and隔开) 复制代码代码如下:DataRow[] matches = dt.Select("id<'003' and name='名字11'");string strName = matches[0]["name"].ToString();三、合并假定有2个DataTable:Dt1 , Dt2。
表结构一样将Dt2接在Dt1后可采用此方法复制代码代码如下:dt1.Merge(dt2);四、在DataTable中查询应该注意的问题完成一个查询，返回一个DataTable后，很多时候都想在查询结果中继续搜索。
这时可以使用DataTable.Select方法对结果进行再查询Select方法有4个重载，我们经常用到的就是DataTable.Select(String)这个String的参数是查询的限定式。
使用linq将2个Datatable合并
使用linq将2个Datatable合并在LINQ中，我们可以使用`Union`方法将两个DataTable合并成一个。
`Union`方法返回两个DataTable中所有不同的行。
假设我们有两个DataTable：DataTable1和DataTable2、每个DataTable都有相同的结构和相同的列。
要将它们合并，我们需要按以下步骤进行操作：1. 导入System.Linq命名空间，以便我们可以在代码中使用LINQ扩展方法。
2. 使用`Union`方法合并两个DataTable，并将结果保存在一个新的DataTable中。
下面是一个使用LINQ将两个DataTable合并的示例代码：```csharpusing System;using System.Linq;using System.Data;public class Programpublic static void Main// 创建两个示例DataTableDataTable dataTable1 = new DataTable(;DataTable dataTable2 = new DataTable(;//添加示例列dataTable1.Columns.Add("Id", typeof(int));dataTable1.Columns.Add("Name", typeof(string));dataTable2.Columns.Add("Id", typeof(int));dataTable2.Columns.Add("Name", typeof(string));// 向示例DataTable中添加示例行dataTable1.Rows.Add(1, "John");dataTable1.Rows.Add(2, "Mary");dataTable2.Rows.Add(3, "Peter");dataTable2.Rows.Add(4, "Sara");DataTable mergedTable =dataTable1.AsEnumerable(.Union(dataTable2.AsEnumerable().CopyToD ataTable(;//输出合并结果foreach (DataRow row in mergedTable.Rows)Console.WriteLine(row["Id"] + " " + row["Name"]);}}```在上面的示例代码中，我们首先创建了两个示例DataTable：dataTable1和dataTable2、每个DataTable都有两个列（Id和Name）。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
1. 用new实例化一个DataTable
DataTable dt = new DataTable();
2. 为实例化的DataTable添加列
DataColumn col = null;
col = dt.Columns.Add("Year", Type.GetType("System.Int32"));
3. 为实例化的DataTable添加行
DataRow row = dt.NewRow();
row["Year"] = ++startYear;
dt.Rows.Add(row);
4． 得到了一个名为dt的
DataTable
5． 通过foreach循环将DataTable中的值写入你想要绑定DataTable的控件。
下面是我做的一个程序：
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
namespace xianshi_nianf
{
public partial class Form1 : Form
{
public Form1()
{
InitializeComponent();
}
DataTable dt = new DataTable();
private void Form1_Load(object sender, EventArgs e)
{
DataColumn col = null;
col = dt.Columns.Add("Year", Type.GetType("System.Int32"));
DateTime serverTime = DateTime.Now;
string year = serverTime.ToString("yyyy");
int startYear = Convert.ToInt32(year) - 11;
for (int i = 0; i < 20; i++)
{
DataRow row = dt.NewRow();
row["Year"] = ++startYear;
dt.Rows.Add(row);
}
foreach (DataRow row in dt.Rows)
{
comboBoxEdit1.Properties.Items.Add(row["Year"].ToString());
}
}
}
}
运行结果：