Click here to Skip to main content
15,886,362 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I Have a DataSet ds which has 200 rows.

I created 2 new Datasets ds1, ds2.

Now On clicking a button in Windows forms application,

I need to retrieve first 100 rows from Dataset ds and store in the new dataset ds1,
then next 100 rows in dataset ds2.

What I have tried:

Can anyone please give me any suggestion on how to do this..

Thanks..
Posted
Updated 8-Aug-16 7:59am
Comments
njammy 8-Aug-16 10:25am    
Are you able to use Linq in your solution? let me know. otherwise I will give an alternative.
Member 12478311 8-Aug-16 10:26am    
No
Member 12478311 8-Aug-16 13:28pm    
Thanks.

try this

C#
DataSet ds = new DataSet();
            DataTable dt = new DataTable();
            dt.Columns.Add("ID");
            dt.Columns.Add("Name");
            for (int i = 1; i <= 200; i++)
                dt.Rows.Add(i, "Name - " + i);
            ds.Tables.Add(dt);

            DataTable dt1 = new DataTable();
            DataTable dt2 = new DataTable();

            dt1 = ds.Tables[0].Clone();
            for (int i = 0; i < 100; i++)
                dt1.Rows.Add(dt.Rows[i].ItemArray);

            dt2 = ds.Tables[0].Clone();
            for (int i = 100; i < 200; i++)
                dt2.Rows.Add(dt.Rows[i].ItemArray);

            DataSet ds1 = new DataSet();
            DataSet ds2 = new DataSet();
            ds1.Tables.Add(dt1); 
            ds2.Tables.Add(dt2);
 
Share this answer
 
Comments
Member 12478311 8-Aug-16 13:26pm    
Thanks
Karthik_Mahalingam 8-Aug-16 13:27pm    
welcome
Maciej Los 8-Aug-16 14:39pm    
Well... i do believe that giving someone ready-to-use solution, is bad idea, but sometimes it's the best idea...
A5!
Karthik_Mahalingam 8-Aug-16 22:43pm    
Thanks Maciej
:)
1. In your code keep variables startIndex, pageSize and totalRows.
2. On Init, set startIndex = 0.
3. Set pageSize to default e.g. pageSize = 10 or whatever you want/user wants.
4. Set currentPage to 1.
5. Set totalRows to record count from query result.
6. Take the number of source rows and copy to destination table
See example code (sorry for the verbosity but I used dotNetFiddle web site to try it for you).
7. All you need to do is specify the current page and it will get the rows for that range.

NOTE: this will not be very good performance on big sets. Use LINQ or move the paging logic shown here to Database side using parameters and temp table to get the Reference id columns.

C#
using System;
using System.Data;
using System.Collections.Generic;
using System.Xml;
					
public class Program
{
	private static DataTable dt;
	private static DataTable refDt;
	private static int startIndex;
	private static int pageSize = 5;
	private static int currentPage = 3;
	private static int totalRows;
	private static int totalPages;
	
	
	public static void Main()
	{
		// Simulating a query result data bind.
		dt = new DataTable("MyDataTable");
		dt.Columns.AddRange(new DataColumn[]
							{
								new DataColumn("col1"),
								new DataColumn("col2"),
								new DataColumn("col3")
							});
		List<object[]> data = new List<object[]>
		{
			new object[] { "1.1", "1.2", "1.3" },
			new object[] { "2.1", "2.2", "2.3" },
			new object[] { "3.1", "3.2", "3.3" },
			new object[] { "4.1", "4.2", "4.3" },
			new object[] { "5.1", "5.2", "5.3" },
			new object[] { "6.1", "6.2", "6.3" },
			new object[] { "7.1", "7.2", "7.3" },
			new object[] { "8.1", "8.2", "8.3" },
			new object[] { "9.1", "9.2", "9.3" },
			new object[] { "10.1", "10.2", "10.3" },
			new object[] { "11.1", "11.2", "11.3" },
			new object[] { "12.1", "12.2", "12.3" },
		};
		
		foreach(object[] dataItems in data)
		{
			dt.Rows.Add(dataItems);
		}
		
		// Now do the paging:		
		totalRows = dt.Rows.Count;
		
		// Set up a reference table to track ids as the source DT may be sorted etc and spoil the tracker field.
		refDt = new DataTable("RefDt");
		refDt.Columns.AddRange(new DataColumn[]
							   {
								   new DataColumn("Index"),
								   new DataColumn("Id")
							   });
		for(int i=0;i<dt.Rows.Count;i++)
		{
			// Here, the refDt data table is built up to store a reference to each ID field in source datatable
			// along with a row number
			refDt.Rows.Add(new object[] {Convert.ToString(i), dt.Rows[i]["col1"]});
			//Console.WriteLine("Index:" + refDt.Rows[i].ItemArray[0] + ", Id:" + refDt.Rows[i].ItemArray[1]);
		}
		
		
		// Now lets work out how many to take and copy to destination data table
		DataTable dest = new DataTable("Destination");
		dest = dt.Clone(); // <- copys columns/schema but not data.
		
		
		startIndex = (currentPage * pageSize) - pageSize;
		Console.WriteLine("Reading " + pageSize + " rows starting at row index " + startIndex);
		
		
		for(int i = startIndex; (pageSize * currentPage) > totalRows ? i < totalRows : i < pageSize * currentPage; i++)
		{
			string lookupFilter = "Index = '" + i.ToString() + "'";
			DataRow dr = (DataRow)refDt.Select(lookupFilter)[0];	
			
			string id = Convert.ToString(dr[1]);
			string selectFilter = "col1 = '" + id + "'";
						
			DataRow source = dt.Select(selectFilter)[0];
			
			dest.Rows.Add(source.ItemArray);
			
			// Just to prove it only copies the required range
			string output= "";
			for(int c = 0; c < dest.Columns.Count; c++)
			{
				output += Convert.ToString(dest.Rows[dest.Rows.Count-1][c]) + ", ";
			}
			Console.WriteLine(output.Remove(output.Length-2,2));
		}
		
		int remainderRows = dt.Rows.Count % pageSize;
		totalPages = remainderRows > 0 ? totalRows/pageSize + 1: totalRows/pageSize;
		Console.WriteLine("Last page has " + remainderRows + " rows");
		Console.WriteLine("Page " + currentPage + " of " + totalPages  + " (Total " + dt.Rows.Count + " rows)");
	}
}
 
Share this answer
 
v2
Comments
Maciej Los 8-Aug-16 14:39pm    
Well... i do believe that giving someone ready-to-use solution, is bad idea, but sometimes it's the best idea...
A5!
njammy 9-Aug-16 4:57am    
That's how we all learn anything.
Check past answer: How to divide the data from one Datatable to two separate Datatable?[^]

Another way is to use Linq to DataSet[^]:
C#
int j =0;
for(int i==0;i+=100;i<200)
{
    //get 100 records
    var copy = ds.Tables[0].AsEnumerable().Skip(i).Take(100);
    //CopyToDataTable method is used to copy data into another datatable
    ds.Table[j] = copy.CopyToDataTable();
    j+=1;
}


For further details, please check MSDN documentation: Creating a DataTable From a Query (LINQ to DataSet)[^]

Note:
1) for very large amount of data, linq solution may cause performance issues
2) destination datatable structure must be the same as source
 
Share this answer
 
Comments
Karthik_Mahalingam 8-Aug-16 22:49pm    
a 4, because in the comments section he has given 'No' to use 'linq'.

Good, Simplified code compared to above 2 solution.
Maciej Los 9-Aug-16 1:58am    
Thank you, Karthik.
Well... i do not see 'No to use linq' even in comments to the question and/or solutions. As i mentioned, linq solution is an alernative. I added two notes, where one of them is saying about possible performance issues when linq is used.
Do not get me wrong, i'm very appreciate your 4.
Karthik_Mahalingam 9-Aug-16 2:28am    
Maciej,
just below the question, a comment by njammy "Are you able to use Linq in your solution? let me know. otherwise I will give an alternative." and the op has replied 'no' for it.
for that comment only i have given 4, else this is the perfect solution for the question. Cheers :)
Maciej Los 9-Aug-16 2:30am    
Oooo... I see it now. Thank you. Cheers!
njammy 9-Aug-16 4:58am    
I agree well done for a solution none-the less, one day someone may come here and choose to adopt the Linq solution if in fact they are able to utilise it in their solution.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900