11 Replies - 8978 Views - Last Post: 23 September 2009 - 10:18 AM Rate Topic: -----

#1 sur_kum  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 6
  • Joined: 14-September 09

Retrieving records in batches of 50

Posted 14 September 2009 - 11:03 AM

Hi Everyone,

I am quite new to .NET platform. I have a need to retrieve data from a large table in batches of 50 and push the data to another file. I have the following code. I am only getting the first 50 and loop quits after that. What am I doing wrong? The table is SQL Server 2000 based.

Thanks
Appreciate help

	dataConnection.Open();
				cmdOne.CommandText = "SELECT * from CUSTOMERS  ORDER BY CUSTOMER_NAME";
			  
				SqlDataReader drOne = cmdOne.ExecuteReader();
				  i = 0;

					ArrayList cust = new ArrayList();
					ArrayList custname = new ArrayList();
					ArrayList cust_city = new ArrayList();
					ArrayList cust_state = new ArrayList ();
					ArrayList cust_zip = new ArrayList();
 
				  while (drOne.Read() && i <= 50)

				  {
						  custname.Add(drOne.GetString(2).Trim());
						  cust_city.Add(drOne.GetString(3).Trim());
						  cust_state.Add(drOne.GetString(4).Trim());
						  cust_zip.Add(drOne.GetString(5).Trim());
						  i += 1;
				  }
					 cust_name = (string[])custname.ToArray(typeof(string));	 
					 customer = (string[])cust.ToArray(typeof(string));
					 city = (string[])cust_city.ToArray(typeof(string));
					 state = (string[])cust_state.ToArray(typeof(string));
					 zip = (string[])cust_zip.ToArray(typeof(string));



Is This A Good Question/Topic? 0
  • +

Replies To: Retrieving records in batches of 50

#2 urbanlemur  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 14-September 09

Re: Retrieving records in batches of 50

Posted 14 September 2009 - 01:36 PM

Hey I hope this helps ..

Here is a link to a website that gives basic instructions on the query needed to limit the number of entries.

http://www.petefreitag.com/item/59.cfm

also when you get a data set of only 50 records returned you can get away with

while(.Read())

Was This Post Helpful? 0
  • +
  • -

#3 sur_kum  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 6
  • Joined: 14-September 09

Re: Retrieving records in batches of 50

Posted 15 September 2009 - 05:29 AM

Thanks for your reply, but the links you sent me shows only reading a top 10, 50 or 100 records. What I really want to do is to read chunks of data as follows.

1 ... 50
51....101
102..152 so on and so forth until all records are read.

Each time I get a chunk of 50 records pass it on the secondary file and go get the next 50 and like wise.

This post has been edited by sur_kum: 15 September 2009 - 05:36 AM

Was This Post Helpful? 0
  • +
  • -

#4 janne_panne  Icon User is offline

  • WinRT Dev
  • member icon

Reputation: 428
  • View blog
  • Posts: 1,047
  • Joined: 09-June 09

Re: Retrieving records in batches of 50

Posted 15 September 2009 - 06:28 AM

At the moment it stops reading after the fiftieth record because of the second condition in your while statement. So to read forward, you'll have to delete the second condition and read everything in the while loop.

Inside the loop, check if it's the fiftieth record and if it is, write the file and clear the arraylists so they can hold another 50 records.

Like this:
// the while loop

while (drOne.Read()) // only one condition
{
  custname.Add(drOne.GetString(2).Trim());
  cust_city.Add(drOne.GetString(3).Trim());
  cust_state.Add(drOne.GetString(4).Trim());
  cust_zip.Add(drOne.GetString(5).Trim());
  i += 1;
  //check if it's the fiftieth record
  if (i % 50 == 0)
  {
	cust_name = (string[])custname.ToArray(typeof(string));	 
	customer = (string[])cust.ToArray(typeof(string));
	city = (string[])cust_city.ToArray(typeof(string));
	state = (string[])cust_state.ToArray(typeof(string));
	zip = (string[])cust_zip.ToArray(typeof(string));
	// add logic to write the file
	// add logic to clear the arraylists so they can store another 50 records.
  }
}


Was This Post Helpful? 0
  • +
  • -

#5 sithius92  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 36
  • View blog
  • Posts: 164
  • Joined: 01-August 08

Re: Retrieving records in batches of 50

Posted 15 September 2009 - 06:40 AM

I had to do the same thing so what I did was make a small change in my SQL statement. I used the LIMIT keyword followed by a starting record and then the number of records to retrieve. I had a button that when clicked would retrieve the next 50 records from the database.

int startingRec = 0;

cmdOne.CommandText = "SELECT * from CUSTOMERS  ORDER BY CUSTOMER_NAME LIMIT " + startingRec + ",50";
SqlDataReader drOne = cmdOne.ExecuteReader();
startingRec += 50;


Was This Post Helpful? 0
  • +
  • -

#6 sur_kum  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 6
  • Joined: 14-September 09

Re: Retrieving records in batches of 50

Posted 15 September 2009 - 11:15 AM

pAnne,

Thanks for your reply. I tested your code, it works. The only problem I have is that if the number of records in the table cannot be divisible by 50, then those records don't get moved to the other file. Say for example I have 253 records, only 250 goes to the other table not the last 3

Thanks




View Postjanne_panne, on 15 Sep, 2009 - 05:28 AM, said:

At the moment it stops reading after the fiftieth record because of the second condition in your while statement. So to read forward, you'll have to delete the second condition and read everything in the while loop.

Inside the loop, check if it's the fiftieth record and if it is, write the file and clear the arraylists so they can hold another 50 records.

Like this:
// the while loop

while (drOne.Read()) // only one condition
{
  custname.Add(drOne.GetString(2).Trim());
  cust_city.Add(drOne.GetString(3).Trim());
  cust_state.Add(drOne.GetString(4).Trim());
  cust_zip.Add(drOne.GetString(5).Trim());
  i += 1;
  //check if it's the fiftieth record
  if (i % 50 == 0)
  {
	cust_name = (string[])custname.ToArray(typeof(string));	 
	customer = (string[])cust.ToArray(typeof(string));
	city = (string[])cust_city.ToArray(typeof(string));
	state = (string[])cust_state.ToArray(typeof(string));
	zip = (string[])cust_zip.ToArray(typeof(string));
	// add logic to write the file
	// add logic to clear the arraylists so they can store another 50 records.
  }
}


This post has been edited by sur_kum: 15 September 2009 - 11:17 AM

Was This Post Helpful? 0
  • +
  • -

#7 sur_kum  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 6
  • Joined: 14-September 09

Re: Retrieving records in batches of 50

Posted 15 September 2009 - 11:29 AM

sithius92.

Thanks for that suggestion. Will this work even if you have records that cannot be exactly divisible by 50? For example. I have 256 records in the table when it comes to the last records the starting position will be 250 and the ending range is 306. Will this select the remaining 6 records or will it select the 6 plus 46 blanks to make up last 50 chunk?

Also, , my program is a batch program. So how do I loop through?

Thanks for your help.

sur_kum


View Postsithius92, on 15 Sep, 2009 - 05:40 AM, said:

I had to do the same thing so what I did was make a small change in my SQL statement. I used the LIMIT keyword followed by a starting record and then the number of records to retrieve. I had a button that when clicked would retrieve the next 50 records from the database.

int startingRec = 0;

cmdOne.CommandText = "SELECT * from CUSTOMERS  ORDER BY CUSTOMER_NAME LIMIT " + startingRec + ",50";
SqlDataReader drOne = cmdOne.ExecuteReader();
startingRec += 50;


This post has been edited by sur_kum: 15 September 2009 - 12:19 PM

Was This Post Helpful? 0
  • +
  • -

#8 sithius92  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 36
  • View blog
  • Posts: 164
  • Joined: 01-August 08

Re: Retrieving records in batches of 50

Posted 16 September 2009 - 08:27 AM

View Postsur_kum, on 15 Sep, 2009 - 11:29 AM, said:

sithius92.

Thanks for that suggestion. Will this work even if you have records that cannot be exactly divisible by 50? For example. I have 256 records in the table when it comes to the last records the starting position will be 250 and the ending range is 306. Will this select the remaining 6 records or will it select the 6 plus 46 blanks to make up last 50 chunk?

Also, , my program is a batch program. So how do I loop through?

Thanks for your help.

sur_kum


View Postsithius92, on 15 Sep, 2009 - 05:40 AM, said:

I had to do the same thing so what I did was make a small change in my SQL statement. I used the LIMIT keyword followed by a starting record and then the number of records to retrieve. I had a button that when clicked would retrieve the next 50 records from the database.

int startingRec = 0;

cmdOne.CommandText = "SELECT * from CUSTOMERS  ORDER BY CUSTOMER_NAME LIMIT " + startingRec + ",50";
SqlDataReader drOne = cmdOne.ExecuteReader();
startingRec += 50;


Now that I think about it, the LIMIT was introduced into SQL 2005. It's a little bit more difficult in SQL 2000 (and I think the syntax I had was for MySQL, not SQL). According to some articles I've been reading, they only way to do this is by creating a stored procedure. Check out the urls below for a better explanation of how to limit the number of rows returned.

http://www.4guysfrom.../042606-1.shtml
http://msdn.microsof...8SQL.80%29.aspx
Was This Post Helpful? 0
  • +
  • -

#9 sur_kum  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 6
  • Joined: 14-September 09

Re: Retrieving records in batches of 50

Posted 18 September 2009 - 06:10 AM

sithu,

I managed to get the chunks of 50 records to work with Jan Panes code above but I have new requirement now. That is each time I read and send the 50 records I have to update a column called "ON_TABLE" in the source table to indicate that the records are sent over. How do I contruct the SQL string dynamically update this column? I have the following snippet.

rrn_num.Add(drOne.GetInt32(0)	<---------------  this is the relative record number of the records being read. I collect them in an array

i += 1;

 if (i % 50 == 0)  

				 {
							 cust_name = (string[])custname.ToArray(typeof(string));
							 customer = (string[])cust.ToArray(typeof(string));
							 city = (string[])cust_city.ToArray(typeof(string));
							 state = (string[])cust_state.ToArray(typeof(string));
							 zip = (string[])cust_zip.ToArray(typeof(string));
					

							 //close the existing connection and re-open a new
							  dataConnection.Close();
							  dataConnection.ConnectionString = DbLocation;
							  SqlCommand cmdxxx = new SqlCommand();
							  cmdxxx.Connection = dataConnection;
							  dataConnection.Open();


							   How do I dynamically contruct this string with the IN CLAUSE. IT HAS TO BE SOMETHING LIKE
							  
							   UPDATE CUSTOMER _MASTER_SOURCE SET ON_TABLE = 1 WHERE RECORD_ID IN 
								(1.24.35.45.50)
											  
								//Update the pushed customer data so only new customer data will be pushed on the next 
							   push
							  cmdxxx.CommandText = "UPDATE CUSTOMER_MASTER_SOURCE SET ON_TABLE  = 1 
							   WHERE RECORD_ID IN.......;
							  SqlDataReader drxxx = cmdxxx.ExecuteReader();

						  }









View Postsithius92, on 16 Sep, 2009 - 07:27 AM, said:

View Postsur_kum, on 15 Sep, 2009 - 11:29 AM, said:

sithius92.

Thanks for that suggestion. Will this work even if you have records that cannot be exactly divisible by 50? For example. I have 256 records in the table when it comes to the last records the starting position will be 250 and the ending range is 306. Will this select the remaining 6 records or will it select the 6 plus 46 blanks to make up last 50 chunk?

Also, , my program is a batch program. So how do I loop through?

Thanks for your help.

sur_kum


View Postsithius92, on 15 Sep, 2009 - 05:40 AM, said:

I had to do the same thing so what I did was make a small change in my SQL statement. I used the LIMIT keyword followed by a starting record and then the number of records to retrieve. I had a button that when clicked would retrieve the next 50 records from the database.

int startingRec = 0;

cmdOne.CommandText = "SELECT * from CUSTOMERS  ORDER BY CUSTOMER_NAME LIMIT " + startingRec + ",50";
SqlDataReader drOne = cmdOne.ExecuteReader();
startingRec += 50;


Now that I think about it, the LIMIT was introduced into SQL 2005. It's a little bit more difficult in SQL 2000 (and I think the syntax I had was for MySQL, not SQL). According to some articles I've been reading, they only way to do this is by creating a stored procedure. Check out the urls below for a better explanation of how to limit the number of rows returned.

http://www.4guysfrom.../042606-1.shtml
http://msdn.microsof...8SQL.80%29.aspx

Was This Post Helpful? 0
  • +
  • -

#10 sithius92  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 36
  • View blog
  • Posts: 164
  • Joined: 01-August 08

Re: Retrieving records in batches of 50

Posted 18 September 2009 - 11:26 AM

I guess I'm not exactly sure what you want. Is it possible for you to expand on it more?
Was This Post Helpful? 0
  • +
  • -

#11 sur_kum  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 6
  • Joined: 14-September 09

Re: Retrieving records in batches of 50

Posted 18 September 2009 - 01:57 PM

Well the the data will be select from the source data base in chunks of 50. Each time the data is sent in batches of 50 to destination table, a column in the source table has to update for the records sent. How do I contruct the SQL command for that? Instead of updating record by record, I want to update the whole batch.




View Postsithius92, on 18 Sep, 2009 - 10:26 AM, said:

I guess I'm not exactly sure what you want. Is it possible for you to expand on it more?

Was This Post Helpful? 0
  • +
  • -

#12 sithius92  Icon User is offline

  • D.I.C Head
  • member icon

Reputation: 36
  • View blog
  • Posts: 164
  • Joined: 01-August 08

Re: Retrieving records in batches of 50

Posted 23 September 2009 - 10:18 AM

View Postsur_kum, on 18 Sep, 2009 - 01:57 PM, said:

Well the the data will be select from the source data base in chunks of 50. Each time the data is sent in batches of 50 to destination table, a column in the source table has to update for the records sent. How do I contruct the SQL command for that? Instead of updating record by record, I want to update the whole batch.

You would have to build your sql command each time a record is retrieved. Then you could execute the sql command after the data is sent over.
cmdxxx += "UPDATE CUSTOMER_MASTER_SOURCE SET ON_TABLE = 1 WHERE RECORD_ID IN (1.24.35.45.50);"


The semi-colon at the end of the sql command signifies the end the sql statement. You can build your sql command this way and then you only have to execute it once after all the data has been sent.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1