Solved

Retrieving SQL Server Data in ASP.NET Code

Posted on 2011-03-12
7
411 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
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 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

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 Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

803 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