Home

Techniques of Commanding a Database
 

Parameterizing a SQL Expression
 

Introduction

So far, when using a command, we could create a SELECT statement that specified all fields that would be shown in the result. Here is an example:

SELECT * FROM Students;

We also saw how to select a group of fields that would be shown in the result. Here is an example:

SELECT * FROM Students
WHERE ParentsNames IS NOT NULL;

When studying data selection, we saw how you could let the user specify the value of a particular column so the table or view would display only the result(s) based on that value. Through the magic of data binding, we saw how you could let the user enter the receipt number of a record, then locate and display that record. Instead of performing these operations manually, Transact-SQL, the .NET Framework, and Microsoft Visual Studio provide you all the necessary means of performing the same action (of locating one particular record or a group of records) without writing a single line of code or by following just a few steps.

A Parameterized Statement

A SQL statement is referred to as parameterized if it expects an external value in order to be complete. The statement starts like a normal SELECT operation with a WHERE clause. Here is an example:

SELECT * FROM Students WHERE Sex = 'male';

Instead of specifying the value of the column(s) in the WHERE clause, you can wait for the user to do so. This is also referred to as a parameterized query.

To create a parameterized statement in the Table window, in the Criteria section, click the box under the Filter column corresponding to the field that holds the value, type @ followed by a variable name. Here is an example:

Parameterized Expression

If you are manually writing a SELECT expression, replace the value with the name of the variable starting with @. Here is an example (we have already studied everything else in this code):

Imports System.Data.SqlClient

Public Class Exercise

    Private Sub Exercise_Load(ByVal sender As Object, _
                              ByVal e As System.EventArgs) _
                              Handles Me.Load
        Dim strConnection As String = _
 		"Data Source=(local);" & _
          	"Database='ROSH';" & _
          	"Integrated Security='SSPI';"
        Dim strStatement As String = _
       		"SELECT StudentNumber, LastName, " & _
                "FirstName, City, State " & _
                "FROM Students " & _
                "WHERE StudentNumber = @StdNbr;"

        Using Connect As SqlConnection = _
		New SqlConnection(strConnection)

            Dim Command As SqlCommand = _
            New SqlCommand(strStatement, _
                        Connect)

            Connect.Open()
        End Using
    End Sub

End Class

You can also use a parameterized statement for a Windows application. To create it, after adding a data source to your application, you can open the DataSet in the Designer. Right-click the title bar of the table that holds the columns and click Configure... In the Query Builder, select the table(s) followed the columns that will be used. In the Criteria section of the window, click the Filter box that corresponds to the column on which the parameter will be applied and type @ followed by the name of a variable. Here is an example:

Query Builder

After specifying the parameter, you can click OK

TableAdapter Configuration Wizard

If you click Next, the TableAdapter Configuration Wizard would indicate the methods that would be created:

TableAdapter Configuration Wizard

You can then accept the options, click Next, and click Finish:

TableAdapter Configuration Wizard

Executing a Parameterized Statement

After creating a parameterized statement, you can test and/or use it. If you had created the statement in the Table window, when you run the query, the SQL interpreter would request a value for the column. If you create the statement in the Object Explorer in Microsoft SQL Server Management Studio or in the Server Explorer of Microsoft Visual Studio, when you execute the statement, a dialog box would come up, asking you to enter a value for the filtered field:

Executing a Parameterized Expression 

You can then type the appropriate value and click OK (or press Enter).

If you use a parameterized statement on a Windows form, the designer would have equipped the form with a text box and a button. You can type the value in that box and click the button. The controls on the form would then be filled with the values related. Here is an example:

Watts A Loan - A Customer Information

Introduction to the Parameters of a Command

Like a method, a database command can accept one or more arguments, referred to as parameters. This allows the command to serve as an intermediary between an object of the database, such as a table, a view, a function, or a stored procedure, and the user. To support the parameters passed to a command, the .NET Framework provides a class for each type of connection. The class used for a SQL connection is called SqlParameter. SqlParameter is based on the DbParameter class and it implements the IDbParameter, the IDadaParameter, and the IClonable interfaces. The DbParameter class is defined in the System.Data.Common namespace.

Like a method, a command can take more than one parameter. Unlike a method, the parameters passed to a command must be stored in a collection. To support a group of parameters, the .NET Framework provides a collection class for each type of connection. For a SQL connection, the class is called SqlParameterCollection. SqlParameterCollection is based on the DbParameterCollection class. The DbParameter class is defined in the System.Data.Common namespace. Like a normal .NET collection class, DbParameterCollection implements the IList, the ICollection, and the IEnumerable interfaces.

To be able to receive one or more parameters, the command classes are equipped with a property named Parameters which is of the type of the collection (as mentioned already, the name of the class depends on the type of database or collection).

The Parameters of a Command
 

Introduction

In order to pass a parameter to a command, you must build it, which is done in various steps. As mentioned already, a parameter is an object of type SqlParameter. To start, you can declare a variable of type SqlParameter but this is only an option (you can pass a parameter directly to a command). To assist you with declaring a variable, the parameter class is equipped with various constructors. The default constructor allows you to declare the variable without giving much information. Here is an example of using it:

Imports System.Data.SqlClient

Public Class Exercise

    Private Sub Exercise_Load(ByVal sender As Object, _
                              ByVal e As System.EventArgs) _
                              Handles Me.Load
        Dim strConnection As String = _
		 "Data Source=(local);" & _
         	 "Database='ROSH';" & _
         	 "Integrated Security='SSPI';"
        Dim strStatement As String = _
     		"SELECT StudentNumber, LastName, " & _
                "FirstName, City, State " & _
                "FROM Students " & _
                "WHERE StudentNumber = @StdNbr;"

        Using Connect As SqlConnection = _
		 New SqlConnection(strConnection)

            Dim Command As SqlCommand = _
         	  New SqlCommand(strStatement, _
                         	 Connect)

            Dim prmStudent As SqlParameter = New SqlParameter

            Connect.Open()
        End Using
    End Sub

End Class

After creating the parameter, you must pass it to the command object. As mentioned already, a command class has a property named Parameters that is from a class that implements the IList interface. This allows you to call its Add() method to add the parameter. The Add() method comes in various versions. One of the versions allows you to pass a parameter object. Here is an example:

Private Sub Exercise_Load(ByVal sender As Object, _
                          ByVal e As System.EventArgs) _
                          Handles Me.Load
    Using Connect As SqlConnection = _
	 New SqlConnection(strConnection)

        Dim Command As SqlCommand = _
        	New SqlCommand(strStatement, _
                	       Connect)

        Dim prmStudent As SqlParameter = New SqlParameter

        Command.Parameters.Add(prmStudent)

        Connect.Open()
        Dim rdrStudents As SqlDataReader = Command.ExecuteReader()
    End Using
End Sub

We will review other versions of this method as we move on.

The Name of a Parameter

Before passing a parameter to a command, you must specify the name of the parameter. To support this, the parameter class is equipped with a property named ParameterName. To specify the name of the argument, assign it to this property. Here is an example of using it:

Private Sub Exercise_Load(ByVal sender As Object, _
                          ByVal e As System.EventArgs) _
                          Handles Me.Load
    Using Connect As SqlConnection = _
		New SqlConnection(strConnection)

        Dim Command As SqlCommand = _
    	       New SqlCommand(strStatement, _
                              Connect)

        Dim prmStudent As SqlParameter = New SqlParameter()

        prmStudent.ParameterName = "@StdNumber"

        Command.Parameters.Add(prmStudent)

        Connect.Open()
        Dim rdrStudents As SqlDataReader = Command.ExecuteReader()
    End Using
End Sub

An alternative is to declare a parameter variable using a constructor other than the default. As we will see in future sections, the name of the argument is passed as the first argument to the constructor.

You may remember that when calling a procedure that takes an argument, you must provide it (the argument). You can pass a value to the procedure or you can also pass the argument using a name. The name of the argument in the method implementation and the name passed to it when called can be different. This is valid in the Visual Basic language but not in Transact-SQL. For a parameter object, you must use the same name used in the SQL statement for the parameter:

Private Sub Exercise_Load(ByVal sender As Object, _
                          ByVal e As System.EventArgs) _
                          Handles Me.Load
    Using Connect As SqlConnection = _
	 	New SqlConnection(strConnection)

        Dim Command As SqlCommand = _
        	New SqlCommand(strStatement, _
                         Connect)

        Dim prmStudent As SqlParameter = New SqlParameter()

        prmStudent.ParameterName = "@StdNbr"

        Command.Parameters.Add(prmStudent)

        Connect.Open()
        Dim rdrStudents As SqlDataReader = Command.ExecuteReader()
    End Using
End Sub

The Type of Parameter

As you should know from your knowledge of the Visual Basic language, when calling a method that takes an argument, you must pass the argument of the appropriate type. In the same way, a command requires that you specify the type of argument you are passing. To assist you with this, the parameter class is equipped with a property named DbType. The DbType property is of type DbType. This class provides various data types that correspond to the data types of Transact-SQL.

To specify the data type of a parameter, access the DbType property of your parameter variable. Assign it the corresponding data type accessed as a static field of the DbType class. Here is an example:

Private Sub Exercise_Load(ByVal sender As Object, _
                          ByVal e As System.EventArgs) _
                          Handles Me.Load
    Using Connect As SqlConnection = _
		New SqlConnection(strConnection)

        Dim Command As SqlCommand = _
	        New SqlCommand(strStatement, _
                               Connect)

        Dim prmStudent As SqlParameter = New SqlParameter()

        prmStudent.ParameterName = "@StdNbr"
        prmStudent.DbType = DbType.String

        Command.Parameters.Add(prmStudent)

        Connect.Open()
    End Using
End Sub

Instead of specifying the data type from the DbType property, you can use the following constructor of the SqlParameter class:

Public Sub New(parameterName As String, dbType As SqlDbType)

The first argument to this constructor is the name of the parameter. The second parameter is the data type:

Private Sub Exercise_Load(ByVal sender As Object, _
                          ByVal e As System.EventArgs) _
                          Handles Me.Load
    Using Connect As SqlConnection = _
		New SqlConnection(strConnection)

        Dim Command As SqlCommand = _
                New SqlCommand(strStatement, _
                         Connect)

        Dim prmStudent As SqlParameter = New SqlParameter("@StdNbr", DbType.String)

        Command.Parameters.Add(prmStudent)

        End Using
    End Sub

End Class

The Value of a Parameter

In order to produce a result from a parameterized SQL statement, you must provide the value that the command will apply. To support this, the parameter class is equipped with a property named Value. This value is of type object, which means it is your responsibility to provide the appropriate value for the parameter. Here is an example:

Imports System.Data.SqlClient

Public Class Exercise

    Private Sub Exercise_Load(ByVal sender As Object, _
                              ByVal e As System.EventArgs) _
                              Handles Me.Load
        Dim strConnection As String = _
	  	"Data Source=(local);" & _
          	"Database='ROSH';" & _
          	"Integrated Security='SSPI';"
        Dim strStatement As String = _
       		"SELECT StudentNumber, LastName, " & _
                "FirstName, City, State " & _
                "FROM Students " & _
                "WHERE StudentNumber = @StdNbr;"

        Using Connect As SqlConnection = _
		New SqlConnection(strConnection)

            Dim Command As SqlCommand = _
 	         New SqlCommand(strStatement, _
        	                Connect)

            Dim prmStudent As SqlParameter = New SqlParameter

            prmStudent.ParameterName = "@StdNbr"
            prmStudent.DbType = DbType.String
            prmStudent.Value = "723897"

            Command.Parameters.Add(prmStudent)

        End Using
    End Sub

End Class

Of course if the value is a number, you can assign it as such. If the user will provide the value from a control, you can access the value of that control and assign it to the Value property. Here is an example:

Private Sub Exercise_Load(ByVal sender As Object, _
                          ByVal e As System.EventArgs) _
                          Handles Me.Load

    Using Connect As SqlConnection = _
		New SqlConnection(strConnection)

        Dim Command As SqlCommand = _
           	New SqlCommand(strStatement, _
                         Connect)

        Dim prmStudent As SqlParameter = New SqlParameter()

        prmStudent.ParameterName = "@StdNbr"
        prmStudent.DbType = DbType.String
        prmStudent.Value = TxtStudentNumber.Text

        Command.Parameters.Add(prmStudent)
    End Using
End Sub

After providing all the information required by the command and adding the parameter to the Parameters collection, you can execute the command to retrieve its values and use them as you see fit. Here is an example:

Imports System.Data.SqlClient

Public Class Exercise

    Private Sub BtnLocate_Click(ByVal sender As System.Object, _
                                ByVal e As System.EventArgs) _
                                Handles BtnLocate.Click
        Dim strConnection As String = _
        	  "Data Source=(local);" & _
                  "Database='ROSH';" & _
                  "Integrated Security='SSPI';"
        Dim strStatement As String = _
     	        "SELECT StudentNumber, LastName, " & _
                "FirstName, City, State " & _
                "FROM Students " & _
                "WHERE StudentNumber = @StdNbr;"

        Using Connect As SqlConnection = _
		New SqlConnection(strConnection)

            Connect.Open()
            Dim Command As SqlCommand = _
	           New SqlCommand(strStatement, _
                  	          Connect)

            Dim prmStudent As SqlParameter = New SqlParameter

            prmStudent.ParameterName = "@StdNbr"
            prmStudent.DbType = DbType.String
            prmStudent.Value = TxtStudentNumber.Text
            Command.Parameters.Add(prmStudent)

            Dim rdrStudents As SqlDataReader = Command.ExecuteReader()

            While rdrStudents.Read()
                TxtLastName.Text = rdrStudents(1)
                TxtFirstName.Text = rdrStudents(2)
                TxtCity.Text = rdrStudents(3)
                TxtState.Text = rdrStudents(4)
            End While
        End Using
    End Sub
End Class

Red Oak High School

The Parameters of a Stored Procedure
 

Introduction

Although commands can benefit from parameters, the primary use of parameters is on stored procedures. This is because stored procedures can receive arguments, either or both by value and/or by reference. Before using a stored procedure in a command, obviously you must first create it. It could be as simple as selecting one or a few columns of a table or a view. From the previous lesson, we saw the following example:

CREATE PROCEDURE GetStudentIdentification
AS
BEGIN
    SELECT FirstName, LastName, DateOfBirth, Sex
    FROM Students
END

The Type of Command

There are a SQL statement and two types of objects a command object can process. When using a command, before executing it, you must specify its type of action. To assist you with this, the command class is equipped with a property named CommandType. The CommandType property is based on the CommandType enumeration.

One of the members of the CommandType enumeration is Text. This is applied for a regular SELECT operation and this is the default type used by the command. This means that if you do not specify the type, Text is applied. That has been the case for all the commands (SELECT statements and functions) we have used in previous sections and lessons. Still, if you want to specify that you are using a regular SQL statement for your action, select Text as the CommandType and assign it to the CommandType property. Here is an example:

Private Sub Exercise_Load(ByVal sender As Object, _
                          ByVal e As System.EventArgs) _
                          Handles Me.Load

    Using Connect As SqlConnection = _
		New SqlConnection(strConnection)

        Dim Command As SqlCommand = _
           	New SqlCommand(strStatement, _
                         Connect)
	Command.CommandType = CommandType.Text

        Dim prmStudent As SqlParameter = New SqlParameter()

        prmStudent.ParameterName = "@StdNbr"
        prmStudent.DbType = DbType.String
        prmStudent.Value = TxtStudentNumber.Text

        Command.Parameters.Add(prmStudent)
    End Using
End Sub

Because a function is treated like a SELECT statement, if you using it to select some columns, its type is also the Text value. For example, imagine you had created a function as follows:

CREATE FUNCTION dbo.GetFullName
(
    @FName varchar(20),
    @LName varchar(20)
)
RETURNS varchar(41)
AS
    BEGIN
	RETURN @LName + ', ' + @FName;
    END

You can call it as follows:

Red Oak High School

Imports System.Data.SqlClient

Public Class Exercise

    Private Sub BtnSubmit_Click(ByVal sender As System.Object, _
                                ByVal e As System.EventArgs) _
                                Handles BtnSubmit.Click
        Dim strConnection As String = _
		"Data Source=(local);" & _
       		"Database='ROSH';" & _
       		"Integrated Security='SSPI';"
        Dim strStatement As String = _
        	"SELECT dbo.GetFullName('" & _
          	TxtFirstName.Text & "', '" & _
          	TxtLastName.Text & "');"

        Using Connect As SqlConnection = _
	       New SqlConnection(strConnection)

            Dim Command As SqlCommand = _
            New SqlCommand(strStatement, Connect)
            Command.CommandType = CommandType.Text

            Connect.Open()
            Dim rdrStudents As SqlDataReader = Command.ExecuteReader()

            While rdrStudents.Read()
                TxtFullName.Text = rdrStudents(0)
            End While
        End Using
    End Sub

    Private Sub BtnClose_Click(ByVal sender As System.Object, _
                               ByVal e As System.EventArgs) _
                               Handles BtnClose.Click
        End
    End Sub
End Class

Another member of the CommandType enumeration is named TableDirect. This member can be applied if you are using an OLE DB connection.

Executing a Parameterized Stored Procedure

If you are planning to use a stored procedure, the CommandType enumeration supports it through the StoredProcedure member.

When declaring a command variable, you can pass the name of the stored procedure as the first argument. Here is an example:

Private Sub Exercise_Load(ByVal sender As Object, _
                          ByVal e As System.EventArgs) _
                          Handles Me.Load
    Using Connect As SqlConnection = _
	 New SqlConnection("Data Source=(local);" & _
     			   "Database='ROSH';" & _
		           "Integrated Security='SSPI';")

        Dim Command As SqlCommand = _
    	    New SqlCommand("GetStudentIdentification", Connect)

        Command.CommandType = CommandType.StoredProcedure

    End Using
End Sub

Alternatively, if you declare the command variable using its default constructur, you can access its CommandText property and assign the name of the stored procedure to it. Here is an example:

Private Sub Exercise_Load(ByVal sender As Object, _
                          ByVal e As System.EventArgs) _
                          Handles Me.Load
    Using Connect As SqlConnection = _
	 New SqlConnection("Data Source=(local);" & _
     			   "Database='ROSH';" & _
		           "Integrated Security='SSPI';")

        Dim Command As SqlCommand = New SqlCommand()

        Command.CommandText = "GetStudentIdentification;"
        Command.Connection = Connect

        Command.CommandType = CommandType.StoredProcedure

        Connect.Open()
    End Using
End Sub

Once the command is ready, you can execute it. If the stored procedure contains a simple or normal SELECT statement, you can retrieve its values and use them as you see fit, such as displaying them in data grid view. Here is an example:

Red Oak High School

Private Sub Exercise_Load(ByVal sender As Object, _
                          ByVal e As System.EventArgs) _
                          Handles Me.Load
    Using Connect As SqlConnection = _
	 New SqlConnection("Data Source=(local);" & _
     			   "Database='ROSH';" & _
     			   "Integrated Security='SSPI';")

        Dim Command As SqlCommand = _
             New SqlCommand("GetStudentIdentification", _
                  Connect)
        Command.CommandType = CommandType.StoredProcedure

        Connect.Open()
        Dim sdaStudents As SqlDataAdapter = New SqlDataAdapter(Command)
        Dim dsStudents As DataSet = New DataSet("StudentsSet")

        sdaStudents.Fill(dsStudents)
        DgvStudents.DataSource = dsStudents
        DgvStudents.DataMember = dsStudents.Tables(0).TableName
    End Using
End Sub

Red Oak High School

When it comes to parameters of a stored procedure, if you have one that takes arguments, follow the same rules we reviewed earlier for the commands, except that if the argument has a default value, you can omit specifying its value.
 
Home Copyright © 2008-2016, FunctionX, Inc.