Solved

Retrieving SQL Server Data in ASP.NET Code

Posted on 2011-03-12
7
412 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 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 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
VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

 
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: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

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 …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

856 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