Filtering records in a DataGrid
-
Hello everyone! I'm making an application interfaced with a DataBase (SQL). If a table contains 1,000,000 records, i can't fill the table with all of them because it will take lot of time. Is it possible to filter the record ten by ten (for example)? I don't wanna load all the records and then display them ten by ten, but load everytime ten elements. I hope you understand! All suggestions are welcome... Thanks in advance. Enrico VentoEngine corp. Program your life ^^
-
Hello everyone! I'm making an application interfaced with a DataBase (SQL). If a table contains 1,000,000 records, i can't fill the table with all of them because it will take lot of time. Is it possible to filter the record ten by ten (for example)? I don't wanna load all the records and then display them ten by ten, but load everytime ten elements. I hope you understand! All suggestions are welcome... Thanks in advance. Enrico VentoEngine corp. Program your life ^^
Here is an idea you could try: Read the records and store in DataTable "A" Create another DataTable "B" and set your grid's datasource to this table. Launch a thread to copy the records from DataTable A to DataTable B. Use the System.Threading.Thread.Sleep to pause for a few milliseconds every 10 records. Using the Sleep method allows other processes to continue. Hope that helps. Regards, Rudy.
-
Here is an idea you could try: Read the records and store in DataTable "A" Create another DataTable "B" and set your grid's datasource to this table. Launch a thread to copy the records from DataTable A to DataTable B. Use the System.Threading.Thread.Sleep to pause for a few milliseconds every 10 records. Using the Sleep method allows other processes to continue. Hope that helps. Regards, Rudy.
-
it can be a good solution but i must do in the way i explained... i just can't read all the records in the same time. but great idea, i'll keep on mind ^^ VentoEngine corp. Program your life ^^
You could just keep track of the last record that was displayed. Something like this:
// Make sure this isnt in front of the query though or you will always get the same records
// declare and initialize this at the top of a class or something
int lastRecord = 0;...
// *** This would occur when the user hits next, for example ***//Execute this query on the database to populate the table
"SELECT * FROM someTable WHERE someColumn <= " + (lastRecord + 10) "AND someColumn >" + lastRecord//increment lastRecord by 10 so next time you will get records 11 - 20 and so on...
int lastRecord += 10;// Do databinding and what not...
If the user wanted to go back:
if (lastRecord != 10)
{
//Execute this query on the database to populate the table
// If your last record was 50 you will get records 31 - 40
"SELECT * FROM someTable WHERE someColumn <= " + (lastRecord - 10) "AND someColumn >" + (lastRecord - 20)//decrement lastRecord by 10
int lastRecord -= 10;// Do databinding and what not...
}
Hope that helps ;P
-
You could just keep track of the last record that was displayed. Something like this:
// Make sure this isnt in front of the query though or you will always get the same records
// declare and initialize this at the top of a class or something
int lastRecord = 0;...
// *** This would occur when the user hits next, for example ***//Execute this query on the database to populate the table
"SELECT * FROM someTable WHERE someColumn <= " + (lastRecord + 10) "AND someColumn >" + lastRecord//increment lastRecord by 10 so next time you will get records 11 - 20 and so on...
int lastRecord += 10;// Do databinding and what not...
If the user wanted to go back:
if (lastRecord != 10)
{
//Execute this query on the database to populate the table
// If your last record was 50 you will get records 31 - 40
"SELECT * FROM someTable WHERE someColumn <= " + (lastRecord - 10) "AND someColumn >" + (lastRecord - 20)//decrement lastRecord by 10
int lastRecord -= 10;// Do databinding and what not...
}
Hope that helps ;P
-
have i to build a stored procedure for that or...? And what does "someColumn" is? VentoEngine corp. Program your life ^^
No that wouldnt be necessary just put that query in a DataAdapter and fill a table:
string selectText = "SELECT * FROM someTable WHERE someColumn <= " + (lastRecord + 10) "AND someColumn >" + lastRecord;
// if you insert the query in here you and fill a DataTable you will get the 10 records
// that you need and no more. Then just bind the table to the grid.
SqlDataAdapter dAdapt = new SqlDataAdapt(selectText, connectionHere);DataTable table = new DataTable();
dAdapt.Fill(table, "tableName");
//Bind the table to a grid
//update last record
lastRecord += 10;Just do this every time the user hits next or something.
-
No that wouldnt be necessary just put that query in a DataAdapter and fill a table:
string selectText = "SELECT * FROM someTable WHERE someColumn <= " + (lastRecord + 10) "AND someColumn >" + lastRecord;
// if you insert the query in here you and fill a DataTable you will get the 10 records
// that you need and no more. Then just bind the table to the grid.
SqlDataAdapter dAdapt = new SqlDataAdapt(selectText, connectionHere);DataTable table = new DataTable();
dAdapt.Fill(table, "tableName");
//Bind the table to a grid
//update last record
lastRecord += 10;Just do this every time the user hits next or something.
-
how can i do if i don't have an ID column? i can't compare the index...grrr VentoEngine corp. Program your life ^^