Introduction

• You do not want to create queries with variable values by concatenation.
• You do want to work with parameterized queries or stored procedures.
  • To avoid SQL injection attacks.
  • For efficiency.

SQL injection attack:

string query = "SELECT * FROM Student where LastName = '" + lastName + "'";

Try this when lastName =

Jones';DELETE FROM STUDENT;--

Even if your tables are read-only, an attacker can find out a lot of information. Do a search on "SQL injection attack".

Efficiency:

When repeatedly executing a query, say in a loop, if the query is newly created via concatention each iteration, the DB has to make a new plan each time, potentially very expensive.

If, instead, a parameterized query is used, a precompilation can be performed before entering the loop, building an execution plan. The single plan can be executed multiple times within the loop with different parameter values.

Code

Examples below can be found in the Connected\Param project.

Calling single-valued

Some queries and stored procedures return a single value. DbCommand.ExecuteScalar() works for those.

using ( DbConnection conn = GetDbConnection() )
{
    conn.Open();


    DbCommand cmd = conn.CreateCommand();
    cmd.CommandText = "select count(*) from Student";
    cmd.CommandType = CommandType.Text;

    int count = (int)cmd.ExecuteScalar();
    Console.WriteLine("DoSingleResult: Query: result = {0}",count);
}

If the query returns multiple columns and/or multiple rows, all except the first row/column value will be ignored.

If there is no result set and no returned value (for example, from an INSERT, DELETE or UPDATE query), then one can use DbCommand.ExecuteNonQuery(). Returned value is an int, the number of rows affected.

Calling a stored procedure

In the Student database, there is a stored procedure named GetTotalEnrollment defined by:

CREATE PROCEDURE GetTotalEnrollment
AS
     SELECT COUNT(*) from Registration
   

To call a stored procedure, set a command's CommandText to be the name of the stored procedure and set the CommandType to be CommandType.StoredProcedure:

using ( DbConnection conn = GetDbConnection() )
{
    conn.Open();


    DbCommand cmd = conn.CreateCommand();
    cmd.CommandText = "GetTotalEnrollment";
    cmd.CommandType = CommandType.StoredProcedure;

    int count = (int)cmd.ExecuteScalar();
    Console.WriteLine("DoSingleResult: Proc: result = {0}",count);
}

Using parameters

There is no distinction using parameters in a query versus using parameters in a stored procedure. However, with stored procedures, you are more likely to run into output parameters and return values.

Here is an example using a parameterized query.

using (DbConnection conn = GetDbConnection())
{
    DbCommand cmd = conn.CreateCommand();
    cmd.CommandText = "select count(*) from Student where Age >= @age";
    cmd.CommandType = CommandType.Text;

    DbParameter ageParam = GetProviderFactory().CreateParameter();
    ageParam.DbType = DbType.Int32;
    ageParam.Direction = ParameterDirection.Input;
    ageParam.ParameterName = "@age";

    cmd.Parameters.Add(ageParam);

    conn.Open();
    cmd.Prepare();

    foreach (int age in new int[] { 20, 30, 40, 50 })
    {
        ageParam.Value = age;
        int count = (int)cmd.ExecuteScalar();

        Console.WriteLine("# students at least age {0} = {1}", age, count);
    }
}

• Doing this with a stored procedure having this definition would be identical except for the CommandText and CommandType values.
• A DbCommand has a Parameters collection of objects of type DbParameter.
• We have to create the parameter, then add it to the collection.
• Here we have used a provider factory to create the parameter. There are lots of other ways. See below.
• Note the call to cmd.Prepare() to precompile the parameter on the database.
• Note setting cmd.Value on each iteration.

SQLServer versus OLE DB

• The SQLServer provider uses named parameters.
• The OLE DB provider uses anonymous parameters. Names do not matter, only position matters.

  using (DbConnection conn = GetDbConnection2())
  {
      DbCommand cmd = conn.CreateCommand();
      cmd.CommandText = "select count(*) from Student where Age >= ?";
      cmd.CommandType = CommandType.Text;
  
      DbParameter ageParam = GetProviderFactory2().CreateParameter();
      ageParam.DbType = DbType.Int32;
      ageParam.Direction = ParameterDirection.Input;
      ageParam.ParameterName = "@harvey"; // name irrelevant
  
      cmd.Parameters.Add(ageParam);
  
      conn.Open();
      cmd.Prepare();
  
      foreach (int age in new int[] { 20, 30, 40, 50 })
      {
          ageParam.Value = age;
          int count = (int)cmd.ExecuteScalar();
  
          Console.WriteLine("# students at least age {0} = {1}", age, count);
      }
  }

Output parameters

In the Student database is a stored procedure named GetHobby defined by:

CREATE PROCEDURE GetHobby
	@instructorId int,
	@hobby char(20) output
AS
	SELECT @hobby = Hobby from Instructor where Id = @instructorId

There is one input parameter and one output parameter. Calling code could look like this:

using (DbConnection conn = GetDbConnection())
{
    DbCommand cmd = conn.CreateCommand();
    cmd.CommandText = "GetHobby";
    cmd.CommandType = CommandType.StoredProcedure;

    DbParameter idParam = GetProviderFactory().CreateParameter();
    idParam.DbType = DbType.Int32;
    idParam.Direction = ParameterDirection.Input;
    idParam.ParameterName = "@instructorId";

    DbParameter hobbyParam = GetProviderFactory().CreateParameter();
    hobbyParam.DbType = DbType.String;
    hobbyParam.Direction = ParameterDirection.Output;
    hobbyParam.Size = 100;
    hobbyParam.ParameterName = "@hobby";

    cmd.Parameters.Add(idParam);
    cmd.Parameters.Add(hobbyParam);

    conn.Open();
    cmd.Prepare();

    foreach (int id in new int[] { 1,2,3 })
    {
        idParam.Value = id;
        int count = (int)cmd.ExecuteNonQuery();

        Console.WriteLine("Instructor #{0} hobby = {1}", id, hobbyParam.Value);
    }
}

• The SQL provider requires a Size on a variable-length output parameter.

Return values and results sets

The stored procedure GarbagePizza has an input parameter, a result set, an output parameter, and a return value.

CREATE PROCEDURE GarbagePizza 
   @lastName nchar(15),
   @enrollments int OUT
AS
   SELECT @enrollments = COUNT(*) from Registration;

   SELECT DISTINCT c.Id, c.Title FROM Course c, Registration r, Student s 
     WHERE c.id = r.CourseId and s.id = r.StudentId And s.LastName = @lastName;

   return len(@lastName)

In this case, because there is a result set, we will need to use ExecuteReader. When using a reader, output and return value parameters are not available until the reader is closed.

using (DbConnection conn = GetDbConnection())
{
    DbCommand cmd = conn.CreateCommand();
    cmd.CommandText = "GarbagePizza";
    cmd.CommandType = CommandType.StoredProcedure;

    DbParameter lastNameParam = GetProviderFactory().CreateParameter();
    lastNameParam.DbType = DbType.String;
    lastNameParam.Direction = ParameterDirection.Input;
    lastNameParam.ParameterName = "@lastName";

    DbParameter enrlParam = GetProviderFactory().CreateParameter();
    enrlParam.DbType = DbType.Int32;
    enrlParam.Direction = ParameterDirection.Output;
    enrlParam.ParameterName = "@enrollments";

    DbParameter retParam = GetProviderFactory().CreateParameter();
    retParam.DbType = DbType.Int32;
    retParam.Direction = ParameterDirection.ReturnValue;

    cmd.Parameters.Add(lastNameParam);
    cmd.Parameters.Add(enrlParam);
    cmd.Parameters.Add(retParam);

    conn.Open();
    cmd.Prepare();

    lastNameParam.Value = "Jones";
    DbDataReader reader = cmd.ExecuteReader();

    while ( reader.Read() )
    {
        Console.WriteLine("{0}\t{1}", reader.GetValue(0), reader.GetValue(1));
    }

    reader.Close();

    // output parameters and return value now available
    Console.WriteLine("Enrollments = {0}", enrlParam.Value);
    Console.WriteLine("Return value = {0}", retParam.Value);
}

Using the OLE DB provider, where order matters, the return value parameter must come first in the collection.

Null values

To set the value of a parameter to a null reference:

param.Value = DBNull.Value;

Parameter classes

Our examples above used a factory to create commands. If you work with provider-specific commands, you have a lot more options for creating commands:

// sets name and value
SqlParameter p1 = cmd.Parameters.Add("@CategoryName","Beverages"); 

or

// sets name, type, size
SqlParameter p1 = cmd.Parameters.Add("@CategoryName",SqlDbType.NVarChar,15); 
p1.Value = "Beverages";

or

// sets name and value
SqlParameter p1 = new SqlParameter("@CategoryName","Beverages"); 
cmd.Parameters.Add(p1);

There are more ways than we care to relate.

The interfaces are IDataParameter and IDbDataParameter.

IDataParameter

Method/property	Description
DbType	Gets or sets the DbType of the parameter.
Direction	Gets or sets a value indicating whether the parameter is input-only, output-only, bidirectional, or a stored procedure return value parameter.
IsNullable	Gets a value indicating whether the parameter accepts null values.
ParameterName	Gets or sets the name of the IDataParameter.
SourceColumn	Gets or sets the name of the source column that is mapped to the DataSet and used for loading or returning the Value.
SourceVersion	Gets or sets the DataRowVersion to use when loading Value.
Value	Gets or sets the value of the parameter.

SourceColumn and SourceVersion come into play when using a data adapter to make updates from a DataSet back to the data source. More on this elsewhere.

IDbDataParameter: extends IDataParameter

Method/property	Description
Precision	Indicates the precision of numeric parameters.
Scale	Indicates the scale of numeric parameters.
Size	The size of the parameter.