ADO.NET Parameters ado.net tutorials

Tags: VB.NET, VB 2008, VB 2010, VB 2012, VB 2013

This lesson describes how to use ADO.NET Parameters

Every serious database programming, regardless of how simple it might be, requires SQL statements that use parameters.

Using parameterized queries you treat SQL parameters as objects (more object-oriented manner), rather than as a simple blob of text. In that way you reduce the number of typos and make your code much faster, because the parameterized queries execute much faster than a literal SQL string. It's faster because the parametrized queries are parsed exactly once.

In addition parameterized queries protect you against possible SQL injection attacks (a well-known data access security issue).

To support parameterized queries, ADO.NET command objects maintain a collection of individual parameter objects and by default, this collection is empty, but you can insert any number of parameters that map to a placeholder parameter in the SQL query.

Creating a parameter is as simple as declaring an instance of the SqlParameter class and providing it the necessary information, such as parameter name, value, type, size, direction, and so on.

Properties of the SqlParameter class:

  • ParameterName - gets or sets the name of the parameter.
  • SqlDbType - gets or sets the SQL Server database type of the parameter value.
  • Size - gets or sets the size of the parameter value.
  • Direction - gets or sets the direction of the parameter, such as Input, Output, or InputOutput.
  • SourceColumn - maps a column from a DataTable to the parameter.
  • Value - gets or sets the value provided to the parameter object.

In order to associate a parameter within a SQL query to a member in the command object's parameters collection, you prefix it with the @ symbol. However not all DBMSs support this notation.

    Dim ConnectionString As String = _
    "Data Source=SqlServerNameOrIP;Initial Catalog=DBName;Integrated Security=True"
    Dim connection As New SqlConnection(ConnectionString)
    Try
      connection.Open()
      ' declare and create a sql command
      Dim command As SqlCommand = connection.CreateCommand

      ' set the sql statements
      command.CommandText = "SELECT UserID FROM aspnet_Users WHERE Username = @Username AND Password = @Password"

      ' add a SqlParameter way #1
      command.Parameters.Add("@Username", SqlDbType.VarChar, 50).Value = Username.Text

      ' add a SqlParameter way #2
      Dim param As New SqlParameter
      param.ParameterName = "@Password"
      param.Value = Password.Text
      param.SqlDbType = SqlDbType.VarChar
      param.Size = 50
      param.Direction = ParameterDirection.Input
      command.Parameters.Add(param)

      Dim reader As SqlDataReader = command.ExecuteReader
      ' check if an user exists
      If reader.HasRows Then
      ' such user exists e.g.
      Exists = True
      Else
      Exists = False
      End If
      reader.Close()
      command.Dispose()
    Catch ex As Exception
      Console.Write(ex.Message)
    Finally
      connection.Close()
    End Try
  

This code uses a parameterized SQL statement that receives the username and password to narrow the result set and in that way check if an user with the given username and password exists.


Share This

Home | About | Contact | Privacy Policy

Copyright visual-basic-tutorials.com 2017 - All Rights Reserved.