Board index » Visual Studio » Newbie: Need advice with database manipulation decision

Visual Studio230
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_


-

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

www.dotnetjohn.com/articles/articleid78.aspx.">www.dotnetjohn.com/articles/articleid78.aspx.







"steve" wrote:

Hi Steve,



Here are my thoughts:



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.







http://support.microsoft.com/default.aspx?scid" rel="nofollow" target="_blank">support.microsoft.com/default.aspx=kb;en-us;Q306022





-

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" <cshelton@bsi-idea.com>a écrit dans le message de



-

U¿ytkownik "Cole" <cshelton@bsi-idea.com>napisa³ w wiadomo¶ci

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



-

I don't really have anything I can give you (proprietary code), but this

should be relatively simple for you to do.





-

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



"Cole" <cshelton@bsi-idea.com>a écrit dans le message de



-