Connecting to SQL 2000 - advice

  • Thread starter Thread starter Grant
  • Start date Start date
G

Grant

Hi there,

Im trying to find out how to create a front end ASP page that connects to a
SQL server and retrieves a whole bunch of data. The page needs to be able to
update, delete or add new records. The form itself will be showing
information about computer hardware on our domain ie ram, hd size, id tag,
os etc and I have about 200 computers sitting in a single table in SQL, so
not too much data to work with.

I was just wandering on the best approach to handling the data? Ive found
some samples on recordsets and datasets - also samples on using sqlCommand
and sqlDataReader. Id like to make it as quick access as possible so thought
of using the sqldatareader, but if I need to change or add data then
sqldatareader might not be the optimal choice. On the other hand if Im using
datasets and datables and the users not always going to be modifyiing the
data then I thought it would be overboard using this option. The upside I
gues to using this access method is I can get a one time snapshot of that
data and then when Ive finished doing what I need to do it updates at the
end. But is this way slow? And is it the easiest?

Can anyone give me advice on what the best access method would be? Im
probably going to have about 3 forms - a find page which shows results on
the same page, an add page to add data, and a display page to display the
selected computer.

Thanks for any suggestions,
Grant

by the way Im using C#, SQL 2000 dev on XP.
 
I could write a book on this but in general, I've found DataReaders as such
to be kind of limited in web scenarios in particular. I really need to
qualify that but by using a DataTable, you can store it in ViewState or
Session state and access it throughout postsbacks page flips. So if you
want to have a Print Friendly page for instance that displays a bunch of
tabular data, you'd need to hit the db again if you're using a reader (or
code around it) whereas using a datatable makes this quite easy.

Same for sorting.

Moreso for Remoting. You can't serialize a dataReader so you can't remote
it. Moreoever, even if you don't want remoting now, you may want to in the
future so you can ensure that your web server never talks directly to your
db server and the web server is outside in a DMZ.

I love datareaders don't get me wrong, but considering the statelessness of
HTML - and a whole host of other issues, I don't think the performance gain
is worth the hassle in most cases.
 
Sound advice, thanks. I created my own connection class and will just use
that to hold the dataset and to pass around to different pages. A lot easier
working dosconnected like that - if no updates required then no further
transactions - if updates required then it is a simple udpate with changes
that were already made offline.

Cheers,
Grant
 
Quick question.....how do I keep the class holding the dataset 'alive'
between pages? When I do anything on the form it is resubmitted and I lose
all that data - I have to repopulate the dataset.
 
You can throw it either in the session (even sql server session setting -
which is kinda cool) or the cache of ASP.NET - either solution makes your
app unscaleable - but mostly you have to choose between scaleability
(stateless) or performance (hitting the database as few times as possible).
If it's small enough, you can even rely on viewstate to do this job for you,
and keep it stateless and not hit the database.

Usually you can get 2 of the following 3 -

1. Stateless
2. Less download to the client.
3. Hitting the database too many times.

- Sahil Malik
http://dotnetjunkies.com/weblog/sahilmalik
 
Back
Top