Using SQL Server Multiple Times

[Jerry]

I am creating an ASP.NET page that does the following:

The Page_Load event connects to the database to populate a dropdown
list.

The Button_Click event connects to the database to check if the record
ID already exists and if it doens't, it gets the highest ID.
Another connection inserts the new records to the database.

The Button_Click even uses the same SqlConnection and two different
SqlCommands (one with a DataReader to check for the existing record and
get the highest ID).

Is this the proper way to do things or is there a more efficient way to
do it? I'm very new to .NET and wish to learn things the correct way
first (I ordered a book and it's on the way).

Thanks,

 

[Guest]

<<The Page_Load event connects to the database to populate a dropdown
list.>>

I wouldn't have it connect to the database to populate the drop list on
every postback. Heck I would only have it do it once on application start.
Cache it, it is pretty much static data and you can set the cache to expire
to your liking, once a day, etc. if you like. But hitting the database for
this each time a postback occurs is not practical.

<<The Button_Click event connects to the database to check if the record
ID already exists and if it doens't, it gets the highest ID.
Another connection inserts the new records to the database.

The Button_Click even uses the same SqlConnection and two different
SqlCommands (one with a DataReader to check for the existing record and
get the highest ID).>>

1. If you are using SQL Server, then you should have no problem setting the
ID field of the table to an identity field and allow it to increment. That
would take care of getting the next ID for your insert, let the database do
that job.

2. Write a stored procedure that will handle your insert and check for
existing record. If the record exists already and that violates a rule then
return an error to the calling code. You can trap that error to let the user
know the record already exists.

As far as the "proper way" of writting applications are concerned it depends
on what the application's scope is and what it's for. If you are writting an
app to keep track of your CD collections at home, then going the Microsoft
RAD route of using direct database connections in the UI may be the way to
go. No need for an n-tier achitecture.

However, if you are in an enterprise environment then an n-tier app may be
what you will likely need. Your button_Click event would call a service layer
(orchestration) and that would in turn call your business layer, which gets
its data from the data access layer. All the information is passed back up to
the UI through these calls.

For more information on passing data between layers read the following
article:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnbda/html/BOAGag.asp