DataTable或DataSet如何取前N行数据

[xiangaylian]

一个DataTable，比如有10W笔记录，我想拆分成1W笔记录为一组的10个DataTable，请问如何做？

[孙大诚_SunRobin]

分页加1，数据量大了很慢，而且耗资源

[phommy]

没用任何技巧生写了一个= =

using System;
using System.Data;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            var dt = new DataTable(); //测试数据
            dt.Columns.Add();
            for (var i = 0; i < 100000; i++)
            {
                dt.Rows.Add(i);
            }

            var dts = SplitDT(dt, 10000);
        }

        static DataTable[] SplitDT(DataTable dt, int p)
        {
            var totalRows = dt.Rows.Count; //总行数

            if (totalRows == 0) //一行没有就直接返回空的
            {
                return new[]
                       {
                           dt.Clone()
                       };
            }

            var totalTables = (totalRows - 1) / p + 1; //要返回的表的个数
            var result = new DataTable[totalTables]; //要返回的结果

            for (var i = 0; i < totalTables; i++)
            {
                var thisDT = result[i] = dt.Clone();
                thisDT.BeginLoadData();
                var end = Math.Max(i + i * 10000, totalRows);
                for (var j = i * 10000; j < end; j++)
                {
                    thisDT.Rows.Add(dt.Rows[j].ItemArray);
                }
                thisDT.EndLoadData();
            }

            return result;
        }
    }
}

[xiangaylian]

引用 10 楼 sinful 的回复:

'按区间取： dim r = from s in dt where s.id>0 and s.id<10 select s '按帅选后数量取 dim r = (from s in dt order by id asc dt select s).take(10) dim dt1 as new datatable for each dr as datarow in r dt1.imp……

我们落后了，这软件的运行环境还在.net framwork 2.0上，暂没敢升到3.5去。

[xiangaylian]

引用 1 楼 bdmh 的回复:

你可以在sql选择时，就选择出10个数据集

引用 15 楼 sundacheng1989 的回复:

分页加1，数据量大了很慢，而且耗资源

---- 我取的是别人写的存储过程查出的数据，有这样几个特点： 1.存储过程不确定，根据客户的设置来，这次调用A，下次调用B了，随时可能增加或更新； 2.查询的表结构不确定，貌似也不能生成到临时表处理； 3.运行库是.net 2.0，不知道2.0的环境下能否使用Linq或其它类似的数据处理组件； .... 谢谢楼上各位，但还是期待其它的方案!

[空格键]

分页，接分

[紫魂一号]

DataTable dt=new DataTable();
DataTable TakeTop10=dt.AsEnumerable().Take(10).CopyToDataTable<DataRow>();

[xiongxyt2]

看你的性能要求高不高，不高的话直接用Skip Take 高的话就在sql里面处理

[谁伴我闯荡ING]

引用 19 楼 xiangaylian 的回复:

引用 10 楼 sinful 的回复:'按区间取： dim r = from s in dt where s.id>0 and s.id<10 select s '按帅选后数量取 dim r = (from s in dt order by id asc dt select s).take(10) dim dt1 as new datatable for ea……

看看这个ORACLE的，基于ado，2.0就行： Dim iPageIndex As Integer If ViewState("PageIndex") IsNot Nothing Then iPageIndex = CType(ViewState("PageIndex"), Integer) End If 'Dim strSql = "SELECT kunnr,name_org1,stras,telf1,fname1 FROM (select rownum r,a.* from ZCSMS_GET_CUST a where rownum<=" + ((iPageIndex + 1) * Me.PageSize).ToString + " order by kunnr asc) where r>" + (iPageIndex * Me.PageSize).ToString Dim strSql As String = "" If ViewState("strSql") IsNot Nothing Then strSql = ViewState("strSql").ToString strSql += " where bb.aa between " + (iPageIndex * Me.PageSize).ToString + " and " + ((iPageIndex + 1) * Me.PageSize).ToString Dim dt As New DataTable dt = DBClass.GetDataSet(strSql).Tables(0) Me.ASPxGridView1.DataSource = dt Me.ASPxGridView1.DataBind() ViewState("PageCount") = DBClass.GetDataSet(ViewState("strCount").ToString).Tables(0).Rows(0)(0) Me.lblTotalIndex.InnerText = "\" + Me.GetPageTotal.ToString Me.lblCount.InnerText = "一共：" + ViewState("PageCount").ToString + "条记录" Me.SetPage() End If

[luobing261314]

你可以在sql选择时，就选择出10个数据集

[段传涛]

这个没有什么问题啊。 在dataset做分组就可以了啊。 根据行分成10个table，

[令狐苦瓜]

DataTable dt = new DataTable();
DataRow[] dr=dt.Select("id < 10000");

[犇犇熊]

用存储过程分页展示数据，或者用linq也可以

[我是小李]

可以根据关键字段以top 10000 和not in的方式用sql分成10个记录集。然后再用dataset绑定。

[杰拉尔]

List<DataRow> DS= ds.Tables.Cast<DataTable>().SelectMany(c => c.AsEnumerable().Take(10)).ToList(); 一句话搞定

[ddgx]

public static DataSet GetDataSet(string sqlStr, int StartIndex, int MaxIndex, string tab)
    {
        SqlConnection Conn = connOpen();
        SqlDataAdapter Ada = new SqlDataAdapter(sqlStr, Conn);
        DataSet ds = new DataSet();
        Ada.Fill(ds, StartIndex, MaxIndex, tab);
        Ada.Fill(ds);
        connClose(Conn);
        return ds;
    }

[qxs]

看这个方法是否可以帮助到你 Public Function QueryDataAutoPage(ByVal PreSQL As String, _ ByVal DesiredNode As String, _ ByVal CompareCol As Int32, _ ByVal PageSize As Int32) As DataSet ' read until find desired record ' continiously fill table with only page worth of data If Trim(PreSQL) = "" Then Throw New Exception("PreSQL is empty") Dim SQL As String = ProcessSQL(PreSQL) Try mData.Connect() Dim reader As IDataReader reader = mData.SelectReader(SQL) ' create the datatable holder Dim DT As New DataTable For i As Int32 = 0 To reader.FieldCount - 1 DT.Columns.Add(reader.GetName(i), reader.GetFieldType(i)) Next Dim values(reader.FieldCount - 1) As Object Dim Found As Int32 Dim FirstPage As New DataTable ' first page of rows in case not found ' start to load Dim Rcount As Int32 = 1 Dim Pcount As Int32 = 1 DT.BeginLoadData() While reader.Read() reader.GetValues(values) If Rcount > PageSize Then ' ' next page If Found > 0 Then 'If Found = PageSize Then 'found on the extra row ' Found = 1 'Else ' Exit While 'End If Exit While End If If Pcount = 1 Then FirstPage = DT.Copy ' store first page of rows in case not found in all pages DT.Rows.Clear() Rcount = 1 Pcount += 1 End If If Not IsDBNull(values(CompareCol)) Then If values(CompareCol) = DesiredNode Then Found = Rcount End If DT.LoadDataRow(values, True) Rcount += 1 End While DT.EndLoadData() reader.Close() If Found = 0 AndAlso Pcount > 1 Then DT = FirstPage Pcount = 1 End If Dim DS As New DataSet DS.Tables.Add(DT) ' send back page count Dim DTex As New DataTable("PageCount") DTex.Columns.Add(New DataColumn("PageNum", GetType(Int32))) DTex.Rows.Add(DTex.NewRow()) DTex.Rows(0)(0) = Pcount DS.Tables.Add(DTex) Return DS Catch ex As Exception LogError(ex, SQL) Throw New Exception("Error in QueryDataAutoPage", ex) Finally mData.Close() End Try End Function

[luobing261314]

一个sql，两个参数：开始，结束数字。 或者一个存储过程。搞定

[qq346127416]

分页读取多好 速度快 省资源

[以专业开发人员为伍]

首先是一个概念问题，你到底非要得到 DataTable 还上一个 DataRow 集合就可以？

[不吃辣的阿银]

/// <summary> 按索引拆分DS </summary>
private DataSet GetNumMinToMaxDataSet(int min, int max, DataSet ds)
{
            DataSet dsReturn = ds.Clone();
            for (int i = min; i < max; i++)
            {
                dsReturn.Tables[0].Rows.Add(ds.Tables[0].Rows[i].ItemArray);
            }
            return dsReturn;
}