Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Retrieving SQL Server Data in ASP.NET Code

Posted on 2011-03-12
7
Medium Priority
?
421 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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

IntroductionWhile developing web applications, a single page might contain many regions and each region might contain many number of controls with the capability to perform  postback. Many times you might need to perform some action on an ASP.NET po…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
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 .
Integration Management Part 2

885 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