Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Retrieving SQL Server Data in ASP.NET Code

Posted on 2011-03-12
7
Medium Priority
?
423 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 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 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 143

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 143

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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 143

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 143

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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…

572 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