Solved

Retrieving SQL Server Data in ASP.NET Code

Posted on 2011-03-12
7
410 Views
Last Modified: 2012-05-11
Hello -

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?

Thanks!
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

            'connection 
            Dim strConnSQL As String = "data source=.\SQLEXPRESS;Integrated Security=SSPI;" &
                "AttachDBFilename=|DataDirectory|\aspnetdb.mdf;User Instance=true"
            Dim connSQL As New SqlConnection(strConnSQL)
            connSQL.Open()

            '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
            While myReader.Read
                prof.iFAuserID = myReader("UserID")
                prof.iFAacctID = myReader("AcctID")
                prof.AcctMaster = myReader("AcctMaster")
            End While

            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
            While myReaderA.Read
                prof.AcctInactive = myReaderA("AcctInactive")
            End While
            prof.Save()
            prof = Nothing
            myReaderA.Close() : myReaderA = Nothing
            qryProfileA = Nothing
        End If
    End Sub

Open in new window

0
Comment
Question by:JMS1965
  • 5
  • 2
7 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 35116939
you have 3 steps to improve you code

step 1: as all the data comes from the sam db, query it in 1 go (JOIN)

step 2: to avoid the SQL injection issues, work with sqlparameter objects

step 3: to avoid cluttering the C# code with sql code, but all the sql code into sql stored procedures

let me post the 3 steps
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35116957
step 1 method:

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

            'connection 
            Dim strConnSQL As String = "data source=.\SQLEXPRESS;Integrated Security=SSPI;" &
                "AttachDBFilename=|DataDirectory|\aspnetdb.mdf;User Instance=true"
            Dim connSQL As New SqlConnection(strConnSQL)
            connSQL.Open()

            'profile settings from iFAusers
            Dim sqlProfile As String
            sqlProfile = "SELECT u.UserID, u.AcctID, u.AcctMaster, a.AcctInactive FROM iFAusers u JOIN iFAaccounts a ON a.AcctID= u.AcctID WHERE u.UserName= '" & strUser & "';"
            Dim qryProfile As New SqlCommand(sqlProfile, connSQL)

            Dim myReader As SqlDataReader = qryProfile.ExecuteReader
            While myReader.Read
                prof.iFAuserID = myReader("UserID")
                prof.iFAacctID = myReader("AcctID")
                prof.AcctMaster = myReader("AcctMaster")
                prof.AcctInactive = myReader("AcctInactive")
            End While

            myReader.Close() : myReader = Nothing

        End If
    End Sub

Open in new window

0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35116995
step 2, use SqlParameter to avoid sql injection:

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

            'connection 
            Dim strConnSQL As String = "data source=.\SQLEXPRESS;Integrated Security=SSPI;" &
                "AttachDBFilename=|DataDirectory|\aspnetdb.mdf;User Instance=true"
            Dim connSQL As New SqlConnection(strConnSQL)
            connSQL.Open()

            'profile settings from iFAusers
            Dim sqlProfile As String
            sqlProfile = "SELECT u.UserID, u.AcctID, u.AcctMaster, a.AcctInactive FROM iFAusers u JOIN iFAaccounts a ON a.AcctID= u.AcctID WHERE u.UserName= @username "
            Dim qryProfile As New SqlCommand(sqlProfile, connSQL)
            Dim prmUserName as New SqlParameter("@username", SqlDbType.VarChar, 11, ParameterDirection.Input, strUser)
            qryProfile.Parameters.Append prmUserName
 
            Dim myReader As SqlDataReader = qryProfile.ExecuteReader
            While myReader.Read
                prof.iFAuserID = myReader("UserID")
                prof.iFAacctID = myReader("AcctID")
                prof.AcctMaster = myReader("AcctMaster")
                prof.AcctInactive = myReader("AcctInactive")
            End While

            myReader.Close() : myReader = Nothing

        End If
    End Sub 

Open in new window


to see what sql injection is:
http://www.google.lu/search?q=Sql+Injection&ie=utf-8&oe=utf-8&aq=t&rls=org.mozilla:en-US:official&client=firefox-a
0
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35117005
step 3: put sql code into stored procedure:

CREATE PROCEDURE dbo.GetUserProfile ( @username varchar(100) )
AS
SELECT u.UserID, u.AcctID, u.AcctMaster, a.AcctInactive 
   FROM iFAusers u 
   JOIN iFAaccounts a 
     ON a.AcctID= u.AcctID 
  WHERE u.UserName= @username 

Open in new window


and your .net code goes like this (only the name of stored procedure remains):

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

            'connection 
            Dim strConnSQL As String = "data source=.\SQLEXPRESS;Integrated Security=SSPI;" &
                "AttachDBFilename=|DataDirectory|\aspnetdb.mdf;User Instance=true"
            Dim connSQL As New SqlConnection(strConnSQL)
            connSQL.Open()

            'profile settings from iFAusers
            Dim sqlProfile As String
            sqlProfile = "dbo.GetUserProfile"
            Dim qryProfile As New SqlCommand(sqlProfile, connSQL)
            qryProfile.CommandType = CommandType.StoredProcedure
            Dim prmUserName as New SqlParameter("@username", SqlDbType.VarChar, 11, ParameterDirection.Input, strUser)
            qryProfile.Parameters.Append prmUserName
 
            Dim myReader As SqlDataReader = qryProfile.ExecuteReader
            While myReader.Read
                prof.iFAuserID = myReader("UserID")
                prof.iFAacctID = myReader("AcctID")
                prof.AcctMaster = myReader("AcctMaster")
                prof.AcctInactive = myReader("AcctInactive")
            End While

            myReader.Close() : myReader = Nothing

        End If
    End Sub 

Open in new window

0
 

Author Comment

by:JMS1965
ID: 35122162
Hi Angelll -

Thanks very much for your response; illustrating this by modifying the code made it very easy to understand.

One other question ... I am starting to learn a little about LINQ. Do you know if that is a better approach to working with data?

Thanks again!
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35125546
I would only suggest LINQ for non-database "queries", like filtering from lists, files and the like.
for database queries, I simply don't see any sense in adding another database layer/syntax than just calling a stored procedure which hides all the complexity of the db (so to keep the fine-tuning to the dba) from the application code itself.
0
 

Author Closing Comment

by:JMS1965
ID: 35131526
Thanks again for the code examples you provided in subsequent comments ... very helpful!
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).
Both in life and business – not all partnerships are created equal. As the demand for cloud services increases, so do the number of self-proclaimed cloud partners. Asking the right questions up front in the partnership, will enable both parties …

912 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now