I am working on my first web application using ASP.NET 4 and SQL Server 2008 Express.
So far when I need to read or write data from/to the database using VB code, I have been using this approach (see code insert for example):
1. Open a new SqlConnection
2. Create a new SqlCommand with a "SELECT" or "INSERT" statement.
3. Create a new SqlDataReader that uses the SqlCommand and SqlConnection
4. ... work with the data as needed
5. Close all of the above.
This is all working fine, but my question is ... is this the best (cleanest, efficient, etc.) approach? Are there other options that I should consider, and if so, why?
I guess I just want to make sure I'm on the right track going forward; it seems like a lot of code especially when I just need to retrieve a single value from a single record (as in lines 33-39). (My background is in Access / VBA, and an equivalent to the Dlookup command would be wonderful!)
A related question is ... when using the above approach, is it necessary to open the connection in each process that is using it, or is a way to set that once for the entire app?
Protected Sub SetProfileValues()
'called from Login.aspx at LoggedIn event.
'checks if profile.iFAacctID = nothing, which means profile values haven't been set.
'should only execute profile settings for newly registered user.
Dim strUser As String = LoginUser.UserName
Dim prof As ProfileCommon = Profile.GetProfile(strUser)
If prof.iFAacctID = Nothing Then
Dim strConnSQL As String = "data source=.\SQLEXPRESS;Integrated Security=SSPI;" &
Dim connSQL As New SqlConnection(strConnSQL)
'profile settings from iFAusers
Dim sqlProfile As String
sqlProfile = "SELECT UserID, AcctID, AcctMaster FROM iFAusers WHERE UserName='" & strUser & "';"
Dim qryProfile As New SqlCommand(sqlProfile, connSQL)
Dim myReader As SqlDataReader = qryProfile.ExecuteReader
prof.iFAuserID = myReader("UserID")
prof.iFAacctID = myReader("AcctID")
prof.AcctMaster = myReader("AcctMaster")
myReader.Close() : myReader = Nothing
qryProfile = Nothing
'profile settings from iFAaccounts
sqlProfile = "SELECT AcctInactive FROM iFAaccounts WHERE AcctID=" & prof.iFAacctID & ";"
Dim qryProfileA As New SqlCommand(sqlProfile, connSQL)
Dim myReaderA As SqlDataReader = qryProfileA.ExecuteReader
prof.AcctInactive = myReaderA("AcctInactive")
prof = Nothing
myReaderA.Close() : myReaderA = Nothing
qryProfileA = Nothing