Design question reading data
Hi
I sometimes include reports and charts in my apps. If I need fast cube like functionality I load lots of data into memory from the db. Then I use nested For Each... to read the data form the objects. Sometimes I have 4-5 levels (first object have a collections and each object in that collection have their own collections and etc....) I have never had any problems doing this, but clearly this is not best practise (first of all since the number of transactions is unknown). I have tried to find a vb.net sample code on this but no luck. Could someone point me in the right direction.
Thank you.
Hi imnotbillg,
It's hard to say what a best practice is. The reason this is hard to say is because there are many variables to determine this. Such as what DB Software are you connecting to, what type of DB architecture is it, what data provider are you using, etc..
The best answer I can think of is that today's best practice is to use ADO.Net when possible, then comes ODBC.Net and OLE DB.Net. Again the best practice choice of the data provider will be determined by what kind of DB architecture and what DB software you are connecting to and many other variables.
The following link may help you out; I have chosen this link as it shows a nested Do While Loop. It's primary focus is using the ADO.Net while connecting to an SQL database, it does also show an example using a different data provider and connecting to a different database. This is the best I can find that doesn't only show one example; with more information I may be able to find a better example. Here is the link: http://msdn2.microsoft.com/en-us/library/haa3afyz.aspx
I would also highly recommend picking up a book that is solely dedicated to Database Programming in VB.Net. There are many titles out there to choose from. These types of books will discuss what best practices are at the time of the writing and some will even get into discussing what replaced older VB Functions (I.E. ADO was replaced with ADO.Net, ODBC was replaced with ODBC.Net, etc).
I hope the above gets you pointed in the right direction. If you need further help then please specify the answers and other relevant information.
Thank you,
James
Examples are some of the best way to learn...
Check out the very robust SQL project examples as found in the Starter Kits.
Also there are 101 Samples for Visual Studio 2005 which is a robust, initial development tasks to more involved tasks for winforms, web development etc which may help you in other areas.
Also out some of the VB/C# examples for the basic operations .
Hi James
Thank you. I have some books on db apps in .net (eg. Wrox. vb.net beginning databases) they are all great at telling me how to write specific code, but when it comes to implementing report functionality (reports with speed and user interactions) I do the design after my poor design skills, and end up with reading tons of data into memory and having all the fun with it there. I might be looking for code that uses the sql sever like Im using the servers memory today, but I dont know and It would be great to get the expert preferd solution. I have now bought a book on crystal reports (Im still on VS2003) and hopefully it will improve my designs.
Thank you.
Frode
Hi !(BillG),
Did the posts resolve the problem?
If so mark the post(s) that helped you as the answer(s), so when others search the forums, they might be more inclined to look at a successful post than a non successful one...in the search results the Answered posts are bubbled to the top before the unanswered.
Or post why it failed for you. Thanks.
I recently "inherited" an application that did that - and when it was deployed it took over an hour to generate some of the reports.
Here is what it did:
1) Opened a data reader.
2) Read all of the data into many levels of collections.
3) When through the collections building datasets based on XML schemas.
4) Passed the dataset to Crystal Reports.
I stripped *all* of the object code (no building of collections and such) and replaced it all with stored procedures. Now the reports take a few seconds.
Here is what it does now:
1) Call a data access component with the name of an appropriate stored procedure.
2) Data access components calls the stored procedure and returns the dataset.
3) Passed the dataset to Crystal Reports.
It is not real "OO", but it is pragmatic...<G>
Hi
Thank you. The "before" version sounds just like what I would do. Doing it like this makes it simpler to extract all kinds of dimensions of the data, and you have to be a wiz on SQL to replicate it with storedprocedures. But I think for the future I will do like you did. I can not make the "cube simulation" and each report will take a few seconds longer to load, but I will sleep better at night knowing that the hours to load scenario will not happen.
Thank you
Frode
