When you think ASP, think...
Recent Articles
All Articles
ASP.NET Articles
ASPFAQs.com
Message Board
Related Web Technologies
User Tips!
Coding Tips

Sections:
Sample Chapters
Commonly Asked Message Board Questions
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Security
Stump the SQL Guru!
XML Info
Information:
Feedback
Author an Article
ASP ASP.NET ASP FAQs Message Board Feedback
Print this page.
Published: Wednesday, September 26, 2001

Using Parameterized Query in ASP.NET, Part 2

By Hendry Cahyadi


  • Read Part 1

  • In Part 1 we examined the common approach developers use to issue a SQL query. In this part, we'll examine a better way: using parameterized queries!

    - continued -

    '

    Using Parameterized Queries
    With parameterized queries, instead of using the various markup signs, we can use a parameter as an input sign for our query. Now, here's how the former code appears if we write it over using parameterized queries:

    <script language="vb" runat="server">
    
    Sub Page_Load(sender as Object, e as EventArgs)
      'Get the Database Path, to be used in DB Connection String
      Dim sDBPath As String = Request.MapPath(".") & "\NWind.mdb"
    
      'Define DB Connection String
      Dim sCnnString As String = "Provider=Microsoft.Jet.Oledb.4.0;" & _
                                 "Data Source=" & sDBPath
    
      'Define variables as input for our sql query
      Dim sEmpId As Int32 = 1
      Dim sHireDate As String = "1 January 1993"
      Dim sCountry As String = "USA"
    
      'Populate Connection Object
      Dim oCnn As New OleDbConnection(sCnnString)
    
      'Define our sql query
      Dim sSQL As String = "SELECT FirstName, LastName, Title " & _
                           "FROM Employees " & _
                           "WHERE ((EmployeeID > ? AND HireDate > ?) AND Country = ?)"
    
      'Populate Command Object
      Dim oCmd As New OledbCommand(sSQL, oCnn)
    
      'Add up the parameter, associated it with its value
      oCmd.Parameters.Add("EmployeeID", sEmpId)
      oCmd.Parameters.Add("HireDate", sHireDate)
      oCmd.Parameters.Add("Country", sCountry)
    
      'Opening Connection for our DB operation  
      oCnn.Open()
    
      'Get the results of our query 
      Dim drEmployee As OleDbDataReader = oCmd.ExecuteReader()
    
      'Display Header	
      Response.Write ("<H4>Query Results:</H4>")
    
      Try
    	
        'Loop over our query results & print it	
        Do While (drEmployee.Read())
    	Response.Write("<BR>" & drEmployee.GetString(0) & " " & _
    	        drEmployee.GetString(1) & " - " & drEmployee.GetString(2) )
        Loop
    
      Finally
    
        'Closing the data reader & connection object
        drEmployee.Close()
        oCnn.Close()
       
      End Try
    End Sub
    </script>
    

    Take a look at the variable (sSQL) where we define our parameterized query. We use the question mark sign (?) for each input into the query. Notice that we use the question mark regardless of the type of our variable. To associated the parameter in the query we have to add parameters to the Parameters collection of the Command object. You can give the name of your parameter as whatever you want, as long as it's unique! Remember that you have to add the parameter in the same order as the order of the question mark signs that you put in your query!

      oCmd.Parameters.Add("EmployeeID", sEmpId)
      oCmd.Parameters.Add("HireDate", sHireDate)
      oCmd.Parameters.Add("Country", sCountry)
    

    That's it! You've now got a parameterized queries, not requiring any specialized markup characters or annoying escaping. If you want to do another parameterized query with the same Command object, don't forget to clear the Parameters collection. To accomplish this, simply call the Clear method of Parameters collection to remove all of the parameters from the collection.

    Using Parameterized Queries with SQL Server
    When using the SqlClient data provider, you will have to use a slightly different syntax for your parameterized queries. (Note that the syntax we examined thus far works fine in SQL Server when using the OleDb data provider.) SQL Server allows for a slightly different syntax for parameterized queries, though, one that closely resembles the syntax of a SQL stored procedure. In a future article, we'll examine how to use this special parameterized query syntax for Microsoft SQL Server.

    Conclusion
    Using parameterized query makes our tasks for doing query much more easy and simple. Your query is more readable, you just only need to remember single sign mark in your query. If you're using Sql Server, you can take advantage of using named parameter. And you don't have to bother to do escaping. So when you think query, always think parameterized query.

    Happy Programming

  • By Hendry Cahyadi



  • ASP.NET [1.x] [2.0] | ASPMessageboard.com | ASPFAQs.com | Advertise | Feedback | Author an Article