Home

Using a Parameterized Query

 

Introduction

A query is a technique of selecting one, a few, or all records from a table. A parameterized query is one that uses a condition that the query would have to follow in order to select the necessary records. Imagine you are creating a database for a car rental company. Here is an example of a database with a table:

--CREATE DATABASE MelbourneCarHire;
--GO
USE MelbourneCarHire;
GO
CREATE TABLE Cars
(
    CarID int identity(1,1) NOT NULL,
    TagNumber varchar(10),
    Make varchar(20),
    Model varchar(20),
    CarYear varchar(5),
    HasK7Player bit,
    HasCDPlayer bit,
    HasDVDPlayer bit,
    PicturePath varchar(200),
    Available bit,
    CONSTRAINT PK_Cars PRIMARY KEY(CarID)
);
GO
INSERT INTO Cars(TagNumber, Make, Model, CarYear,
    HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
VALUES('PGG280', 'Toyota', 'Corolla', '2006', 0, 1, 0, 1);
GO
INSERT INTO Cars(TagNumber, Make, Model, CarYear,
    HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
VALUES('KDG914', 'Holden', 'Calais', '2006', 0, 1, 1, 1);
GO
INSERT INTO Cars(TagNumber, Make, Model, CarYear,
    HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
VALUES('485800', 'Toyota', 'Camry', '2004', 0, 0, 0, 1);
GO
INSERT INTO Cars(TagNumber, Make, Model, CarYear,
    HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
VALUES('PRT146', 'Mitsubishi', 'Magna', '2005', 0, 1, 1, 1);
GO
INSERT INTO Cars(TagNumber, Make, Model, CarYear,
    HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
VALUES('294075', 'Daewoo', 'Kalos', '2007', 1, 0, 0, 0);
GO
INSERT INTO Cars(TagNumber, Make, Model, CarYear,
    HasK7Player, HasCDPlayer, HasDVDPlayer, Available)
VALUES('920745', 'Hyundai', 'Getz', '2006', 0, 1, 0, 1);
GO
INSERT INTO Cars(TagNumber, Make, Model, CarYear,
                 HasK7Player,  HasCDPlayer, HasDVDPlayer,
                 PicturePath, Available)
VALUES('LLR722', 'Toyota', 'Tarago', '2007', 0, 1, 1,
    'C:\functionx\tarago1.gif', 1);
GO
INSERT INTO Cars(TagNumber, Make, Model, CarYear,
                 HasK7Player, HasCDPlayer, HasDVDPlayer,
                 PicturePath, Available)
VALUES('LRT882', 'Holden', 'Commodore', '2004', 1, 1, 0,
    'C:\functionx\commodore1.gif', 1);
GO

You can anticipate that, sometimes when the customers would want to rent a car, they may request some information about the car they are trying to order. One way you can get this information ready is to create a query without completely defining the condition.

To get ready for the customer, you can create a forms as follows:

With this form, you can train the clerk to enter the vehicle tag number in the first text box. After entering the tag number, when the user press Tab or clicks somewhere else, you take over to find the car. Here is the code that would help with this (notice that it includes a parameter):

using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;

public partial class _Default : System.Web.UI.Page 
{
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void btnLocate_Click(object sender, EventArgs e)
    {
        SqlConnection conDatabase = null;

        try
        {
            conDatabase =
            new SqlConnection("Data Source=(local);" +
                                "Database='MelbourneCarHire';" +
                                "Integrated Security=true");
            SqlCommand cmdDatabase =
            new SqlCommand("SELECT * FROM dbo.Cars " +
                                 "WHERE TagNumber = @TagNbr;", conDatabase);

            cmdDatabase.Parameters.Add("@TagNbr", SqlDbType.VarChar);
            cmdDatabase.Parameters["@TagNbr"].Value = txtTagNumber.Text;

            DataSet dsCars = new DataSet("CarsSet");
            SqlDataAdapter sdaCars = new SqlDataAdapter();
            sdaCars.SelectCommand = cmdDatabase;
            sdaCars.Fill(dsCars);

            try
            {
                DataRow recCar = dsCars.Tables[0].Rows[0];

                if (recCar.IsNull("CarID"))
                    throw new IndexOutOfRangeException("Invalid Tag Number");

                txtMake.Text = (String)recCar["Make"];
                txtModel.Text = (String)recCar["Model"];
                txtYear.Text = (String)recCar["CarYear"];

                chkK7Player.Checked = (bool)recCar["HasK7Player"];
                chkCDPlayer.Checked = (bool)recCar["HasCDPlayer"];
                chkDVDPlayer.Checked = (bool)recCar["HasDVDPlayer"];
                chkAvailable.Checked = (bool)recCar["Available"];

                imgCar.ImageUrl = (String)recCar["PicturePath"];
            }
            catch (IndexOutOfRangeException)
            {
                txtTagNumber.Text = "";
                txtMake.Text = "";
                txtModel.Text = "";
                txtYear.Text = "";
                chkK7Player.Checked = false;
                chkCDPlayer.Checked = false;
                chkDVDPlayer.Checked = false;
                chkAvailable.Checked = false;
            }
        }
        finally
        {
            conDatabase.Close();
        }
    }   
}

Here is an example of running the program:

 

 

Home Copyright 2007 FunctionX, Inc.