Need advice on how to handle data and windows forms.

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.

Who is Participating?
Jeff CertainCommented:
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.

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 ...

Tigger996Author Commented:
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.
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.

Tigger996Author Commented:
Thanks for the suggestions
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.