Home > Articles > Programming > Windows Programming

  • Print
  • + Share This
Like this article? We recommend

SQL Provider Parameterization

Sometimes you want the parameter capability of the stored procedure but don't want the hassle of creating one on the database server. The SQL .NET data provider provides a way to pass parameters to a SQL statement that saves the programmer from having to develop that hard-to-code SQL INSERT or UPDATE statement, especially when you're passing strings.

The SQL statement looks like a TSQL stored procedure in having @ characters preceding the variables in the SQL statements. These named parameters can be substituted programmatically with the Parameters collection of the Command object in the same manner as we used in the previous demo of stored procedure execution. Listing 9 shows an example of this technology with the SQL provider.

Listing 9 SQL Provider Named Parameter Query Demo

using System;
using System.Data;
using System.Data.SqlClient;

namespace ConnectedOpsDemo
{
class SqlCmdParamDemo
{
[STAThread]
static void Main(string[] args)
{
   SqlConnection conn = null;
   SqlDataReader rdr = null;

   try
   {
     // open connection to the data source
     conn = new
     SqlConnection("server=(local);database=Northwind;" +
      "integrated security=true;");
     conn.Open();

     // create a parameter for the name variable to the
     // parameterized query that returns DataReader via
     //ExecuteReader
     SqlCommand cmd = new SqlCommand(
      "SELECT * FROM Customers WHERE CustomerID LIKE @Name", 
      conn);
     SqlParameter name = new
        SqlParameter("@Name",SqlDbType.VarChar,7);
     name.Value = "%D%";
     cmd.Parameters.Add(name);

     // execute command to return a datareader that closes our
     // parent connection when it is closed
     rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

     Console.WriteLine("\nQUERY: {0} \nReturned the following " +
      "rows from ExecuteReader",
     cmd.CommandText);

     // read through all rows
     while (rdr.Read())
     {
        // display CustomerID (position 0),
        // ContactName (by name) columns
        // from the DataReader
        Console.WriteLine("CustomerID: {0} ContactName: {1}",
        rdr.GetString(0), rdr["ContactName"]);
     }
   }
   finally
   {
     // close the reader in the face of exceptions
     if (rdr != null)
     {
        if (!rdr.IsClosed)
          rdr.Close();
     }
   }
}
}
}

This example gives us a way to parameterize the rows selected from the Customers table without having to worry about quotes and building an ugly SQL string inside our program. The query has an @Name parameter to hold the value passed in.

A SQLParameter object represents the @Name parameter and is used in an identical way as in the previous demo. Listing 10 shows the output result.

Listing 10 SQL Provider Named Parameter Query Demo Results

QUERY: SELECT * FROM Customers WHERE CustomerID LIKE @Name
Returned the following rows from ExecuteReader
CustomerID: BOLID ContactName: Martín Sommer
CustomerID: DALEM ContactName: Dale Michalk
CustomerID: DRACD ContactName: Sven Ottlieb
CustomerID: DUMON ContactName: Janine Labrune
CustomerID: GALED ContactName: Eduardo Saavedra
CustomerID: GODOS ContactName: José Pedro Freyre
CustomerID: LINOD ContactName: Felipe Izquierdo
CustomerID: MAISD ContactName: Catherine Dewey
CustomerID: OLDWO ContactName: Rene Phillips
CustomerID: QUEDE ContactName: Bernardo Batista
CustomerID: SPECD ContactName: Dominique Perrier
CustomerID: SUPRD ContactName: Pascale Cartrain
CustomerID: TRADH ContactName: Anabela Domingues
CustomerID: WANDK ContactName: Rita Müller
  • + Share This
  • 🔖 Save To Your Account