Connected mode. Class DbParameter. Using parameters

Before studying this topic, it is recommended that you familiarize yourself with the following topic:

• Class DbCommand. Examples

Search other resources:

Contents

• 1. Class DbParameter. The purpose
• 2. Properties of the DbParameter class. Review
• 3. Class DbParameter
  • 3.1. The Parameters collection. Adding a parameter to the SQL query. Methods Add(), AddWithValues(), AddRange()
  • 3.2. Creating a parameter using the constructor of the corresponding class. Adding a parameter to the Parameters collection using the Add() method
• 4. An example of using a parameter in a SQL query text
• 5. Properties ParameterName and Value. Specifying the name and value of the parameter. Example
• 6. Property DbType. Parameter data types
• 7. The direction of the parameter. Direction property. Example
• Related topics

1. Class DbParameter. The purpose

The DbParameter class is used to execute parameterized queries on an object of type DbCommand. DbCommand contains a Parameters collection. This collection can contain a set of parameters, each of which is represented by the DbParameter class (type). In accordance with the chosen data provider, instead of DbParameter, you can use an instance of one of the inherited classes SqlParameter, OledbParameter, OdbcParameter.

  ⇑

2. Properties of the DbParameter class. Review

Наиболее часто используемые свойства класса DbParameter следующие.

object SqlParameter.SqlValue { get; set; }

object DbParameter Value { get; set; }

  ⇑

3. Class DbParameter

3.1. The Parameters collection. Adding a parameter to the SQL query. Methods Add(), AddWithValues(), AddRange()

 The Parameters collection is implemented by a property that has a declaration

DbParameterCollection DbCommand.Parameters { get; }

The DbCommand.Parameters property can access the methods of the corresponding provider. So, for the Sql Server provider, the Parameters property looks like this:

SqlParameterCollection SqlCommand.Parameters { get; }

To add a parameter to the SqlCommand object (SQL Server provider), you need to use one of the methods

• Add();
• AddWithValue();
• AddRange().

Common implementations of these methods contain the following declarations:

SqlParameter SqlParameterCollection.Add(SqlParameter value);
SqlParameter SqlParameterCollection.AddWithValue(string parameterName, object value);
void SqlParameterCollection.AddRange(SqlParameter[] values);

here

• parameterName – parameter name in SQL language (for example, “@NameGroup”, “@Cost”);
• value – parameter value that is added;
• values – array of added parameters.

For example, adding a parameter named “@Cost” to the text of an SQL query using the AddWithValue() method will look like this

...

// 1. SQL-query string
string queryStr = ...

// 2. Connection string
string connStr = ...

// 3. Create database connection
SqlConnection connection = new SqlConnection(connStr);

// 4. Create an object that matches the SQL query queryStr
SqlCommand sqlCmd = connection.CreateCommand();
sqlCmd.CommandText = queryStr;

// 5. Add parameter parameterName with value 100
sqlCmd.Parameters.AddWithValue("@Cost", "100");

...

  ⇑

3.2. Creating a parameter using the constructor of the corresponding class. Adding a parameter to the Parameters collection using the Add() method

The object’s Parameters collection defines a list of parameters for a SQL query based on an object of type DbCommand (SqlCommand, OleDbCommand, OdbcCommand). A separate parameter of this collection can be created using the corresponding constructor of one or another provider of the SqlParameter, OdbcParameter, OleDbParameter classes.

So, for example, to create a SqlParameter object, 7 constructors are used

SqlParameter.SqlParameter();
SqlParameter.SqlParameter(string parameterName, object value);
SqlParameter.SqlParameter(string parameterName, SqlDbType dbType);
SqlParameter.SqlParameter(string parameterName, SqlDbType dbType, int size);
SqlParameter.SqlParameter(string parameterName, SqlDbType dbType,
    int size, string sourceColumn);
SqlParameter.SqlParameter(string parameterName, SqlDbType dbType,
    int size,
    ParameterDirection direction,
    bool isNullable,
    byte precision,
    byte scale,
    string sourceColumn,
    DataRowVersion sourceVersion,
    object value);
SqlParameter.SqlParameter(string parameterName, 
    SqlDbType dbType,
    int size,
    ParameterDirection direction,
    byte precision);

here

• parameterName – parameter name in SQL language;
• value – parameter value that is substituted when executing the SQL query;
• dbType is the parameter type defined in the DbType calculation (SqlDbType, OleDbType, OdbcType). The dbType parameter can take the values Text, Char, DateTime, Decimal, Float, Int, Money, and others;
• size – parameter size in bytes;
• sourceColumn – table column name that matches the parameter;
• direction—direction of the parameter activation. Defined by the ParameterDirection enumeration, which can take the values Input, OutPut, InputOutput, ReturnValue;
• isNullable – if true, it means that the field can take the NULL value;
• precision – precision. This is the total number of digits from the decimal point to which value is calculated (number of decimal places);
• scale – total number of decimal places to which the value (output width) is calculated;
• sourceVersion – one of the DataRowVersion values describing the version of the DataRow row in the table;
• value – parameter value.

For OLE DB and ODBC providers, the parameters in constructor declarations are the same.

When using constructors that do not contain a DbType (SqlDbType, OleDbType, OdbcType), this type is determined automatically based on the value.

Example 1.

In our example, an object of type SqlParameter is created using a constructor with 2 parameters.

// create a parameter with a numerical value of 150
SqlParameter param = new SqlParameter("@Cost", 150.0);

The @Cost parameter type is automatically determined to be Float based on the value 150.0.

Example 2.

The example creates an object of type SqlParameter based on a constructor that takes 10 parameters

// Create a parameter
SqlDbType dbType = SqlDbType.Float;
SqlParameter paramName = new SqlParameter("@Price",
    dbType,
    8,
    ParameterDirection.InputOutput,
    true,
    2,
    3,
    "Price",
    DataRowVersion.Default,
    215.55);

The above example sets:

• parameter name “@Price”;
• Float parameter type;
• parameter size 8 bytes;
• parameter transfer direction – InputOutput (in the direction of input and output);
• the parameter can take the value NULL (true);
• precision 2 decimal places;
• scale 3 digits;
• name of the table column that matches the “Price” parameter;
• the data row version is set by default (DataRowVersion.Default). This means that DataRowVersion.Current is set – the row contains the current values in the database;
• parameter value is equal to 215.55.

  ⇑

4. An example of using a parameter in a SQL query text

To execute a parameterized query, you must add a Parameter object to the Parameters collection of the Command object. If you are using SQL Client .NET, you must use SqlParameter instead of the Parameter class. To create a SqlParameter class, one way is to call the AddWithValue() method of the Parameters property.

The example shows the execution of a SQL query using a parameter. The example is implemented for an application of type Windows Forms. The basis for getting data is a table named Group, which has a field named NameGroup.

Using the SqlCommand object containing the SQL query, the obtained results are formed in the SqlDataReader object. These results are then displayed in a ListBox. The SQL query body contains a @NameGroup parameter whose value is obtained from a component of type TextBox (textBox1).

// Button "Parameterized query"
private void button4_Click(object sender, EventArgs e)
{
  // Passing a parameter.
  // For example, you need to perform a filter by group.
  // The SQL command looks like this:
  // SELECT * FROM [Group] WHERE NameGroup = @NameGroup
  // here @NameGroup - parameter - group name

  // 1. Get connection based on connStr string
  string connStr = @"Data Source=(localdb)\ProjectModels;" +
    "Initial Catalog=Database4;Integrated Security=True;";
  SqlConnection connection = new SqlConnection(connStr);

  // Checking if a connection exists
  if (connection == null)
  {
    MessageBox.Show("Error! Cannot open connection");
    return;
  }

  // 2. Create SQL-query
  string strSQL = "SELECT * FROM [Group] WHERE ([NameGroup] = @NameGroup)";

  try
  {
    // 3. Open connection
    connection.Open();

    // 4. Create SQL-command
    SqlCommand command = new SqlCommand(strSQL, connection);

    // 5. Create a new parameter - AddWithValue() method,
    //    parameter value is obtained from textBox1.Text,
    //    @NameGroup <= textBox1.Text
    command.Parameters.AddWithValue("@NameGroup", textBox1.Text);

    // 6. Create an object for reading data and fill it with the results of the SQL command.
    SqlDataReader reader = command.ExecuteReader();

    // show data to listBox1
    listBox1.Items.Clear();
    while (reader.Read())
    {
      listBox1.Items.Add(reader["NameGroup"].ToString());
    }
  }
  catch (Exception ex)
  {
    MessageBox.Show(ex.Message);
  }
  finally
  {
    connection.Close();
  }
}

  ⇑

5. Properties ParameterName and Value. Specifying the name and value of the parameter. Example

You can use the ParameterName and Value properties to set the parameter name and value. To do this, you must first create an object of the type of provider used (SqlParameter, OleDbParameter, OdbcParameter). Depending on which provider, these properties have corresponding declarations

string SqlParameter.ParameterName { get; set; }
string OleDbParameter.ParameterName { get; set; }
string OdbcParameter.ParameterName { get; set; }

object SqlParameter.Value { get; set; }
object OleDbParameter.Value { get; set; }
object OdbcParameter.Value { get; set; }

here

• SqlParameter, OleDbParameter, OdbcParameter – names of classes that implement parameters for SQL Server, OLE DB, and ODBC data providers, respectively. To set parameters, you must first create objects of these classes.

The values of the ParameterName and Value properties are written to the type of the provider. So, for a SQL Server provider, properties are written to an object of type SqlParameter like this:

SqlParameter paramObj = new SqlParameter();
paramObj.ParameterName = "@NameOfParameter";
paramObj.Value = "ValueAsString";

here, instead of fictitious NameOfParameter and ValueAsString, you need to specify real parameter names and their values. The parameter names and their values can be derived from different data fields (for example, a TextBox field for a Windows Forms application).

Example.

The example shows a code snippet for a Windows Forms application that sets the value of a parameter for an SQL query implemented in a command object. Then, in the components label1 and label2, the set values are output.

...

// create the SQL-command
DbCommand command = connection.CreateCommand();
command.CommandText = "SELECT * FROM [Table] WHERE ([Price] = @Cost)"; // текст SQL-запиту
command.CommandType = CommandType.Text;

// create a parameter
SqlParameter param = new SqlParameter();

// fill parameter with ParameterName:Value
param.ParameterName = "@Cost";
param.Value = "150";

// add a parameter to the list of command parameters - the Add() method
command.Parameters.Add(param);

// show the name of parameter to label1
label1.Text = command.Parameters[0].ParameterName.ToString();

// show the value of parameter to label2
label2.Text = command.Parameters[0].Value.ToString();

// Open the connection
connection.Open();

// perform actions based on SQL query command

...

// Close the connection
dataReader.Close();

  ⇑

6. Property DbType. Parameter data types

The DbType property is an enum. For different providers, this property is implemented according to the SqlDbType, OleDbType, OdbcType classes. The property is used to control the type of data used when passing it to the database. These data types are set for columns in database tables.

The DbType property defines the names of the following data types:

• AnsiString – variable length stream in non-Unicode encoding;
• AnsiStringFixedLength – fixed length stream in non-Unicode encoding;
• Binary – type of binary data stream;
• Byte – 8-bit unsigned integer in the range from 0 to 255;
• Boolean – type for representation of logical values true or false;
• Currency – currency data type in the range from -263..263-1 with an accuracy of 0.0001 currency units (one ten-thousandth);
• Date – date value;
• DateTime – date and time value;
• DateTimeOffset – date and time value with support for time zones;
• Decimal – representation of values in the range from 1.0×10^-28 to 7.9×10²⁸ with 28–29 significant digits;
• Double – floating point type representing a value in the range from 5.0×10^-324 to 1.7×10³⁰⁸ with an accuracy of 15–16 characters;
• Guid – Globally Unique Identifier (or GUID);
• Int16 – integer type for representing 16-bit signed integers in the range from -32768 to 32767;
• Int32 – integer type for representing 32-bit signed integers in the range from -2147483648 to 2147483647;
• Int64 – integer type for representing 32-bit signed integers in the range from -9223372036854775808 to 9223372036854775807;
• Object – general type of representation of all values and references;
• SByte – integer type for representing 8-bit signed integers in the range from -128 to 127;
• Single – floating point data type representing a value in the range from 1.5×10-45 to 3.4×1038 with an accuracy of up to 7 digits;
• String – type for representing Unicode character strings;
• StringFixedLength is a type that represents a string of Unicode characters of a fixed length;
• Time is a type that represents a value of the DateTime type for the SQL Server provider;
• UInt – integer type for representing 16-bit integers with values from 0 to 65535;
• UInt32 – integer type for representing 32-bit integers with values from 0 to 4294967295;
• UInt64 – integer type for representations of 64-bit integers with values from 0 to 18446744073709551615;
• VarNumeric – numerical value of variable length;
• Xml – parsed representation of an XML document or fragment.

Example.

The example sets the @StudentName parameter to String.

// Set @StudentName to NVarChar
SqlParameter param = new SqlParameter();
SqlDbType type = SqlDbType.NVarChar;
param.ParameterName = "@StudentName";
param.SqlDbType = type;

  ⇑

7. The direction of the parameter. Direction property. Example

The data in the database can be passed or received (pulled). Accordingly, the parameter can be used:

• to pass data to the database. In this case, the parameter is considered an Input parameter;
• to read data from the database (getting a fixed set of records from the database). This parameter is called the output parameter.

Example.

The example shows how to use the @Rating output parameter to get the value of a student’s rating in the StudentName column of the Student table. The student’s name is given by the @StName input parameter. After the query is executed, the student’s rating is returned in the @Rating parameter.

// 1. Connection string
string connStr = @"Data Source=(localdb)\ProjectModels;" +
  "Initial Catalog=MyDatabase;Integrated Security=True;";

// 2. The text of SQL-query
string strSQL = "SELECT @Rating = Rating " +
  "FROM [Student] " +
  "WHERE StudentName = @StName";

// 3. Create parameters for SQL-query
// 3.1. Parameter @Rating
SqlParameter paramRating = new SqlParameter();
paramRating.Direction = ParameterDirection.Output; // input parameter
paramRating.DbType = DbType.Double; // the type of parameter

// 3.2. Parameter @StudentName
SqlParameter paramStName = new SqlParameter();
paramStName.Direction = ParameterDirection.Input; // input parameter
paramStName.DbType = DbType.String; // parameter type

// 4. Create a connection
SqlConnection connection = new SqlConnection(connStr);

// 5. Create the object that is associated
//    with an SQL command that contains @Rating and @StName parameters
// 5.1. Create an object
SqlCommand command = connection.CreateCommand();

// 5.2. Set the object type
command.CommandText = strSQL;
command.CommandType = CommandType.Text;   // SQL-string

// 5.3. Add parameters
command.Parameters.Add(paramRating);   // @Rating
command.Parameters.Add(paramStName);   // @StName

// 6. Open database connection
connection.Open();

// 7. Execute SQL-command
command.ExecuteNonQuery();

// 8. Checking if a student is found
if (paramRating.Value == DBNull.Value)
{
  label1.Text = "No student found";
}
else
{
  // Output the value of the received @Rating parameter to the label1 component
  label1.Text = paramRating.Value.ToString();
}

// 9. Close database connection
connection.Close();

To indicate that paramRating is an output parameter, use the string

paramRating.Direction = ParameterDirection.Output;

Accordingly, the input parameter paramStName is defined

paramStName.Direction = ParameterDirection.Input;

If a row corresponding to the criterion is not found in the Student table

...

WHERE StudentName = @StName

then the paramRating parameter will be set to DbNull.Value.

  ⇑

Related topics

• Class DbConnection. Examples
• Class DbCommand. Examples
• Class DbDataReader. Examples
• Class DbTransaction. Examples

  ⇑