opening/maintaincing connections

[Bijoy Naick]

I have a ASPX page which needs to make several writes to a database,
using data from a form. I am using an MS Access db.

Currently, I am managing my connections/writes as follows..

myConnection = New OleDBConnection(connectionString)
myConnection.Open()

sql = "first sql statment"
myCommand = New OleDbCommand(sql, myConnection)
myReader = myCommand.ExecuteReader()
myReader.Close()

sql = "2nd sql stmt"
myCommand = New OleDbCommand(sql, myConnection)
myReader = myCommand.ExecuteReader()
myReader.Close()

sql = "3rd sql stmt"
myCommand = New OleDbCommand(sql, myConnection)
myReader = myCommand.ExecuteReader()
myReader.Close()

etc etc.. Is this the optimal way of doing writes to database? The
repeated creation of teh command object seems "not so right" to me..

Thoughts?

 

[Kevin Spencer]

You should be worried about the use of an unused DataReader before you worry
about using the necessary Command object! Try using ExecuteNonQuery instead,
and remember that just because a function returns a value doesn't mean you
have to assign that value to anything.

--
HTH,
Kevin Spencer
..Net Developer
Microsoft MVP
Big things are made up
of lots of little things.

 

[CT]

Well, this is what disconnected data access is all about. Obviously, it's
more time consuming to create, open, and subsequently close a connection for
every request, but your alternative is to save the (open) connection in the
Session object, which is resource consuming. I'm not getting drawn into a
discussion of what's the best appraoch at this time; it's the job at hand
that should decide this. However, from what little I can see from your code,
your approach looks okay. Performing some tests with different approaches
might give you a different answer though.