?
Solved

DataBinding to large tables

Posted on 2006-11-08
7
Medium Priority
?
273 Views
Last Modified: 2008-01-09
I have a Windows Forms application with a form that has controls bound to a table with a large number of rows. Just populating a table with the primary key takes 10 seconds or so and uses a lot of memory on the client machine. Is there a "Best Practice" way of just reading in what I need instead of populating my DataTable with all rows from the backend data source? Thanks.
0
Comment
Question by:Paracom_Inc
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 3

Expert Comment

by:Birdbuster
ID: 17903648
Does your application require you to show all of your data at one time?
Does it use paging ? Does it have a drop down to limit rows with a default value? How long does it take to run the query in to query ananlyzer 10 seconds or shorter? If you could answer these questions and I will be able to give you a much more detailed answer.

Thanks,
Birdbuster
0
 

Author Comment

by:Paracom_Inc
ID: 17904015
The form only needs to display data for one row at a time.
I'm not sure what you mean be paging maybe that's my whole problem.
Navigating from row to row is done with a couple of ComboBoxes and also using first, next, previous, and last buttons. It is the navigation via the first, next, previous, and last buttons that is the issue. Right now I'm using a BindingNavigator with a BindingSource that has a DataTable as its DataSource. The DataTable has only one column, the primary key. The DataTable is populated using a stored procedure in the SQL Server database that returns all rows in the SQL Server table sorted by an indexed column. The TableAdapter.Fill() method that populates the DataTable via the storedprocedure takes 20 seconds to execute. The stored procedure run in query analyzer takes 10 seconds to execute.

What I'm looking for is a good way to be able to move sequentially through the rows without having to spend so much time up front populating a DataTable for this purpose when the form opens. Thanks.
0
 
LVL 29

Expert Comment

by:Gautham Janardhan
ID: 17905723
why do u want to populate the Datable on form load

cant u fetch row by row..

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 8

Assisted Solution

by:redpipe
redpipe earned 400 total points
ID: 17905775
Miscellaneous references to data paging:
  How To Perform Paging with the DataGrid Windows Control by Using Visual C# .NET
  http://www.123aspx.com/redir.aspx?res=27689

  101 Samples for Visual Studio 2005 => Data Access Samples => Performing Batch Updates and Data Paging
  http://msdn.microsoft.com/vstudio/downloads/101samples/default.aspx

If you like to display huge quantities of date in the grid then Virtual Mode is for you:
  Walkthrough: Implementing Virtual Mode in the Windows Forms DataGridView Control
  http://msdn2.microsoft.com/en-us/library/15a31akc.aspx
0
 

Author Comment

by:Paracom_Inc
ID: 17907266
I checked out the data paging references but they weren't very helpful. The DataGrid paging article talks about how to feed pages of an already populated table into a DataGrid, but I'm trying to avoid populating the table in the first place. The sample page lists a sample named Performing Batch Updates and Data Paging, but as far as I could tell it only demonstrates batch updates. I'm not using a grid, so the grid's virtual mode is not for me.

Fetching the data row by row is what I expected to have to do, but I was looking for some suggestions regarding best ways of keeping track of where I am in the backend data table, allowing the data to be sorted on user selected columns, and using a BindingNavigator in conjunction with all of this. Thanks.
0
 
LVL 3

Accepted Solution

by:
Birdbuster earned 1600 total points
ID: 17909826
Why don't you use a default value to get a default row to start with and then
just use a hashTable with a Previous value and Current value.
 If you are going to do this you may have to redo you SELECT statements to something like these.


Use This query to pouplate your DataTable

SELECT *
FROM tblProducts
WHERE Pro__ProductID = @Default

Use this qurey for when you click NEXT
SELECT *
FROM tblProducts
WHERE Pro__ProductID =
(
SELECT MIN(Pro__ProductID)
FROM tblProducts
WHERE Pro__ProductID > @CurrentValue
)


Use this query for when yyou click PREV
SELECT *
FROM tblProducts
WHERE Pro__ProductID =
(
SELECT MAX(Pro__ProductID)
FROM tblProducts
WHERE Pro__ProductID < @CurrentValue
)
0
 

Author Comment

by:Paracom_Inc
ID: 17917590
Thanks everyone for your suggestions. I've made use of many of them. It turned out that the biggest challenge was the fact that the browsing order was not based on the primary key so the simplest approaches would not work. I wrote a stored procedure that takes a prmary key as a parameter returns a range of rows in the complex sort order with the indicated row in the middle. This works very well, is very fast and is transparent to the code that cares about the DataTable.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: Ivo
C# And Nullable Types Since 2.0 C# has Nullable(T) Generic Structure. The idea behind is to allow value type objects to have null values just like reference types have. This concerns scenarios where not all data sources have values (like a databa…
This article describes a simple method to resize a control at runtime.  It includes ready-to-use source code and a complete sample demonstration application.  We'll also talk about C# Extension Methods. Introduction In one of my applications…
NetCrunch network monitor is a highly extensive platform for network monitoring and alert generation. In this video you'll see a live demo of NetCrunch with most notable features explained in a walk-through manner. You'll also get to know the philos…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question