Newbie: Need advice with database manipulation decision

[steve]

Hello,

Firstly, I want to apologize if this question reaches the borders of "out of
topic" but i think both the ado.net and the vb.net groups have a lot to do
with it.

I created a small application that is supposed to manipulate meteorological
and Air Quality data stored in an SQL Server Database.

So far I've created the first "view" which only shows station info and
details. I did this using datasets, since the number of stations is quite
small. I needed to relate a couple of tables so i used
dataset.relations.add( ), and synchronized/binded the data to various
controls. So far so good .

Now comes the harder part:
I need, *after* an involved query(ies) to get a whole bunch of data (e.g.
20,000 rows X 30 columns) and then perform mathematical/statistical
operations on them. For example get a moving average. Also allow the user to
save them in a text file to be imported later into Excel.
-OR-
Scan through all the data from a specific station and through Date/Time
methods find out *exactly* how many actual *calendar* days are missing.
-ETC.-

From what you see, there are TWO important points:
1) Quite a "big" number of data
2) Data will need to be manipulated through programming since SQL would not
be enough (or too complicated)., therefore store the data locally.

Can you give me the keywords of the tools I should be looking into?
-Datareader VS Dataset
-Where should I store my data for easier manipulation? Multidimensional
array?
-How can i export to Excel? or i have to save as text and then import to
Excel?
-????

Any help/suggestions would be *greatly* appreciated!

TIA
_steve_

 

[Guest]

well I do not know the answers to all of your questions but I believe that
you should do a lot of the calculations on sql server because your db
connection will become bottle neck very quickly if you are working with a lot
of info. Use TSQL and write stored procedures to do your mathemetical and
statistical calculations if possible and send only the relevent info back to
the dataset.

In the case of export to excel spreadsheets is the only time you should send
the whole result back to your dataset/data adapter. After that is done there
are a number of ways that you can use to export to excel. Here is a very
simple way to export the data into a simple spreadsheet ( will not let you
format your spreadsheet) go here
http://www.dotnetjohn.com/articles/articleid78.aspx.

 

[Cole]

Hi Steve,

Here are my thoughts:
This really depends on the system and architecture you're running your
application on. If you have a decent system that will be connected to the
DB via LAN , I don't think you're 20,000 rows is that big a deal. I would
just use the standard approach first: SqlDataAdapter.Fill(DataSet) and
then use the data in the DataSet for your calculations. If you want to get
a little faster performance you could build a structure for your data and
then use a SqlDataReader to fill an array of structures. The structures
might be less of a hassle when doing calculations because the syntax will be
cleaner.
DataSet or array, as mentioned above.

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q316934
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q306022

 

[steve]

Thanx for the reply.
I was thinking along similar lines from what I read on the web and my notes.
BTW do you have a link of an example that will show a relatively general
piece of code that would construct SQL strings depending on the user choices
? The choices are done through a series of comboboxes and/or dropdown lists
?

 

[Cole]

BTW do you have a link of an example that will show a relatively general

piece of code that would construct SQL strings depending on the user choices
? The choices are done through a series of comboboxes and/or dropdown

lists

I don't really have anything I can give you (proprietary code), but this
should be relatively simple for you to do.

 

[Grzegorz Danowski]

Hi Steve,

Here are my thoughts:

This really depends on the system and architecture you're running your
application on. If you have a decent system that will be connected to the
DB via LAN , I don't think you're 20,000 rows is that big a deal. I would
just use the standard approach first: SqlDataAdapter.Fill(DataSet) and
then use the data in the DataSet for your calculations. If you want to
get
a little faster performance you could build a structure for your data and
then use a SqlDataReader to fill an array of structures. The structures
might be less of a hassle when doing calculations because the syntax will
be
cleaner.

But if there is needs to update data in database after calculations (for
example writing averages to server to future use), performance fall will be
very big. Simply time of realization 20 thousands updates is very long even
on local machine. In so situation using TSQL procedures will be much more
efficient.

Regards,
Grzegorz

 

[steve]

Thanx again!
I have a pdf describing a similar example but i guess I am more stuck on the
algorithm than the actual code to construct the various strings...