Need advice on how to handle data and windows forms.

Posted on 2006-12-01
Last Modified: 2010-04-23
I have an app that I developed for a client that keeps track of production info.  In a year they have about 10,000 entires into 1 of their tables.  Every year the DB gets backed up and a new year is created empty. There are also many other related tables. The exe file of the program sits in a folder on their server,where I allow sharing of the file.  Saves them time from having to go to each computer that someone uses it and re-install every time I make an update. The most connections that I have to the database at 1 time is maybe 3, and it's not being accessed all that often. I used a lot of form wizards and form binding to control this data.  So that's the background.

So my questions are:

1) What is the BEST and simplest way to connect, and display data, and keep things fast. Keep in mind I have approximately 75 fields on a form using tabs.  Also using grids.
2) I do have to load all the records for previous scrolling, is there a better way, again speed issue.
3) I find that when I empty my db I get errors because of null.  I'd like to try and avoid this.
4) Any other best practices?  I'm an intermediate programmer.

Question by:Tigger996
  • 2
  • 2
LVL 34

Expert Comment

ID: 18057844
Fairly random thoughts.

If you clear your database at the start of each year then, in the early part of the year, there will be no (or few) records available to meet the requirement that you state "I do have to load all the records for previous scrolling".  This makes me wonder whether ALL records are really necessary, especially when - towards the end of the year - the number is getting up to the 10,000 mark.  This leads to the thought - bearing in mind that it is the record transfer which is likely to slow things down - that it might be worth revisiting the "load all records" requirement.  No user is going to be able to make sense of 10,000 records at one time.  So how about loading the latest few hundred, but with a facility to bring over more if the user wants to scroll back beyond those?  Overall, it might take longer.  But the appearance for the user (which is what is usually important) may well be faster: a minimal wait to start with, and a minimal wait when any "extra" records are needed.  And the number of "extra" records could, in any event, probably be reduced by filtering options for the user and the judicious use of WHERE clauses in SELECT statements.

And how about a rolling back-up of the data, rather than an all-or-nothing, once a year bash?  Figures would depend on operational considerations but if, once every six months, you moved the earliest six months records from the operating to the back-up, archive database, leaving the latest six months' records where they were that would avoid the nulls problem.  And if you could reduce it to an operational file of, say, only six months length with the first three months being moved out on a rolling basis then, even if you did have to load ALL records there would, overall, be less to handle.

The basic programming (as opposed to design) point, I suppose, is that the less data that you need to transfer as a matter of course (that is, when it might not actually be needed) the better.

Apart from that, it is difficult to offer suggestions without knowing more detail of the setup (e.g. what is the database?) and the actual coding.  It sounds like a lot of controls: but if the data that you have to show requires that number ...

LVL 24

Accepted Solution

Jeff Certain earned 125 total points
ID: 18059854
To add my considerably less wise comments to Roger's....

1. In general data is useless if you can't get to it. This is the scenario you're advocating by starting with a new database every year. 10000 records is NOTHING for an enterprise-level database (SQL Server, Oracle). You'll just need to write a view to only return the current year's data. However, this still lets you do analysis on all your data as an aggregate -- which is really where the data becomes information, and is useful.

1a. If you really need to dump data every year, you might consider rolling it up into an OLAP cube so that you still have it available for analysis. You may want to consider doing this quarterly vice annually.

2. Simultaneous connections are no longer much of an issue, since ADO .NET uses a disconnected data model. As long as you don't have concurrency worries with a bunch of users changing data, you should be okay. If you're using VS 2005 with SQL Server 2000 or 2005, you can enable 2-way data binding, which really makes your disconnected data look and feel like it is connected.

3. The best and simplest way to connect.... in .NET that would be ADO .NET. :) DataReader performs slightly better than DataAdapter, but that's becuase it is read-only, and doesn't allow you to save changes easily.

4. Instead of "scrolling" you might want to consider "paging". If you want to be really sophisticated, you could get the top N records to begin your session, and use a separate thread to pre-fetch the next N records. I'd probably keep track of the last ID from each fetch, so I can use it as the input to my fetch routine.... SELECT TOP N <field>, <field> FROM <table> WHERE ID > lastID, Also, consider keeping 3 sets of data in memory -- current, previous and next. This way, any time a user pages, the next set of data is fetched.

4a. But 10000 records isn't that big a deal. Fetch them all into memory, and only load the one(s) you need. Two-way data binding will keep your data current for you.

5. NULL doesn't cast nicely to anything else, so if you're trying to assign it to a variable that is not of type object.... well, it'll break. This is probably one of the biggest pains, but it means that you have to handraulically validate against System.Dbnull.Value each time you make a variable assignment from a database field that could be null. I *believe* that grids are smart enough to deal with NULLs when you bind.


Author Comment

ID: 18070896
Ok some further explaination:

At the start of my app, they select the year they want to work with, and that selects the db (MS SQL 2000).

I constantly  update/save the record (single record entry) (when they click update). I know this probably isn't the best way, but I do need the db updated as they are entered .  

I designed this in VS2003.

I do agree with you on not loading all records.  Once the records are entered and verified (by an admin) They really don't need to look it up again.  And if they need to they can view a report.

I'm wondering if I shouldn't re-design this in 2005.  Sounds like there are some better data features available.  

I think I always get stuck with doing bound fields, or is it better to do it through code?

I don't mind creating an empty database for them every year.  Keeps things really clean for them and the only thing they do with the previous year's information is view reports.

So after all of that... How is the best way to handle data - in the sense of bound fields or manual coding?

Thanks for all your tips and suggestions.
LVL 34

Assisted Solution

Sancler earned 125 total points
ID: 18077321
So after all of that... How is the best way to handle data - in the sense of bound fields or manual coding?

Bound fields.  I can just about envisage scenarios in which the in-app data processing is so complex that the downside of having to "work-around" the automatic features of data-binding might be so great that it would be worth (in effect) coding your own "data-binding".  Where that dividing line might come would depend, to some extent, on the programmer's own knowledge of the intricacies and quirks of the built-in data-binding mechanisms.  But it would, I think, have to be VERY involved data-processing and a VERY inexperienced (and unwilling to learn) programmer before that would happen.


Author Comment

ID: 18305070
Thanks for the suggestions

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
It’s quite interesting for me as I worked with Excel using for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

810 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