dotConnect for SQL Server enhances SQL handling capabilities with usage of parameters in SQL queries. You can make execution of a query or stored procedure very flexible using several simple techniques. This article describes some basics you must be acquainted with when working with parameters in dotConnect for SQL Server, as well as parameters synchronization and some nuances related to the usage of stored procedures.
The article consists of the following sections:

• Parameters basics
• Using automatic parameters synchronization
• Using parameters with stored procedures in autosynchronization mode
• Using OUT and INOUT parameters
• Performance issues
• Examples

Parameters basics

In general, parameter is a placeholder for a variable that contains some value of some type when executing a general-purpose query, or arguments and return values when a stored procedure is executed. Parameter is represented by SqlParameter class. All parameters that take part in query execution constitute a collection that can be accessed through SqlCommand.Parameters property.

dotConnect for SQL Server supports named parameters only.

Named parameters require accordance with names of SqlParameter instances in the collection. Named parameters are declared using '@' prefix followed by the name of the parameter. Note that names of SqlParameter objects must contain '@' symbol as well. You do not have to care about the order in which parameters are created. Also, named parameter can appear more than once in query text; you have to create only one instance of it in Parameters collection.

For example, a simple Update statement that requires parameters might look like the following:

    UPDATE dept SET dname = @dname, loc = @loc WHERE deptno = @deptno

To set parameters for this query you can use the next code:

SqlCommand mySqlCommand1;
...
mySqlCommand1.CommandText = "UPDATE dept SET dname = @dname, +
loc = @loc WHERE deptno = @deptno";
mySqlCommand1.Parameters.Add("@deptno", 20);
mySqlCommand1.Parameters.Add("@dname", "SALES");
mySqlCommand1.Parameters.Add("@loc", "NEW YORK");

Dim mySqlCommand1 as SqlCommand
...
mySqlCommand1.CommandText = "UPDATE dept SET dname = @dname, &_
loc = @loc WHERE deptno = @deptno"
mySqlCommand1.Parameters.Add("@deptno", 20)
mySqlCommand1.Parameters.Add("@dname", "SALES")
mySqlCommand1.Parameters.Add("@loc", "NEW YORK")

SQL Server variables are also denoted using '@' symbol. When encountered in query text, such fragments are treated as parameters only if they are found in SqlCommand.Parameters collection; otherwise they are considered server variables.

When you invoke a stored procedure you have to create collection of parameters that corresponds strictly to set of arguments for the stored procedure in quantity and types. Names of parameters do not matter unless you set SqlCommand.ParameterCheck property to true. Parameters that represent arguments to the procedure must have their Direction property set to Input. You retrieve value returned by a stored function through a parameter with Direction property set to ReturnValue.

Using automatic parameters synchronization

The behavior described above assumes that SqlCommand.ParameterCheck is false (by default). By turning it on you enable automatic synchronization of query text and SqlCommand.Parameters collection. In this mode all input parameters are checked for validity, new ones are added if necessary, and redundant parameters are deleted. Thus you do not have to take care about quantity of items in SqlCommand.Parameters collection, you can specify only the ones you really need. The synchronization is done as follows:

• If quantity of unnamed parameters in the query text is more than quantity of parameters in SqlCommand.Parameters collection, new parameters are created and added to it, with SqlParameter.Value assigned to DBNull. SqlParameter.SqlType is set to SqlType.VarChar. This property is updated to a correct type when a value is assigned to the parameter.
• If quantity of unnamed parameters in the query text is less than in SqlCommand.Parameters collection, redundant parameters are deleted from it.
• If named parameters are mentioned in the text but are missing in the collection, they are added to collection. If named parameters that exist in the collection are not found in the query text, they are deleted from the collection.
• Properties of parameters are not altered, except for the ones created during the synchronization process.

For queries that do not represent a stored procedure the synchronization takes place when:

• SqlCommand.ParameterCheck is set to true
• SqlCommand.CommandType is set to "Text"
• SqlCommand.CommandText is altered

For stored procedures synchronization happens when:

• Query is prepared
• Query is executed

A separate query is executed against the server to establish the correct types of the parameters when synchronization is performed for stored procedures.

If synchronization is already performed, subsequent calls to these methods do not result in re-synchronization, unless you had modified Parameters collection or one of the properties listed above.

Using parameters with stored procedures in synchronization mode

If parameters are added to the command collection in the order that is different from the function parameters order in database, it is necessary to describe the command by setting SqlCommand.ParameterCheck to true to reoder parameters in a proper way.

When SqlCommand.ParameterCheck is true you can specify only those parameters you really need. Omitted parameters will be created and assigned DBNull value. You can set up required parameters in any order you like. The collection will be filled up and sorted as a result of synchronization.

When working with stored procedures in synchronization mode parameter's name must match exactly the name of procedure's argument. However the ReturnValue parameter needs not to have any specific name.

In synchronization mode first call to SqlCommand.Prepare or SqlCommand.Execute methods leads to recreation of all argument parameters. If the name of a parameter is suitable for the description of stored procedure, parameter is preserved in the collection, otherwise it is lost. If SqlCommand.CommandText property and Parameters collection are unchanged all subsequent invocations of Prepare or Execute methods will not result in recreation of parameters.

For example, consider you had a stored procedure that accepts two arguments, deptno and dname, and then changed SqlCommand.CommandText to reference another stored procedure that accepts deptno and loc arguments. In this case you will have first parameter unchanged, and the second parameter recreated with value assigned to DBNull.

Using OUT and INOUT parameters

To assign initial value to the parameter use SET statement that preceedes a call to stored procedure. The next example shows how to call stored procedure using single INOUT parameter.

  CREATE PROCEDURE testproc(INOUT param1 INTEGER(11))
  BEGIN
  SET param1=param1*2;
  END

SqlConnection myConn = new SqlConnection(
"user id=root;database=demobase;DataSource=127.0.0.1;password=root");
myConn.Open();
SqlCommand command = new SqlCommand(
"SET @param1=11;CALL testproc(@param1);SELECT @param1", myConn);
using (IDataReader reader = command.ExecuteReader()) {
  if (reader.Read())
    Console.WriteLine("@param1 = " + reader[0]);
}
myConn.Close();

Dim myConn As SqlConnection = New SqlConnection( _
"user id=root;database=demobase;DataSource=127.0.0.1;password=root")
myConn.Open()
Dim command As SqlCommand = New SqlCommand( _
"SET @param1=11;CALL d.testproc(@param1);SELECT @param1", myConn)
Dim reader As IDataReader = command.ExecuteReader()
If (reader.Read()) Then
  Console.WriteLine("@param1 = " & reader(0))
End If
reader.Close()
myConn.Close()

Performance issues

In general, setting SqlCommand.ParameterCheck property to true leads to some performance loss.
When SqlCommand.CommandType is "Text" the synchronization is performed on client, so performance reduces very slightly.
When SqlCommand.CommandType is "StoredProcedure", dotConnect for SQL Server sends additional requests to server which are necessary to determine quantity, names, and other information about parameters. Thus performance mostly depends on how fast these additional round trips are performed.

To optimize query multiple execution you can manually call SqlCommand.Prepare method that creates compiled version of the query on server and forces synchronization. Moreover, for prepared query data is transferred and treated in different (binary) mode, which is much faster, especially when it concerns BLOB fields. After the query is prepared it is not advised to alter SqlCommand.Text property because the SqlCommand becomes unprepared again. The recommended practice is to set up query text and parameters, execute SqlCommand.Prepare method, and afterwards change only parameters' values.

Examples

The first example demonstrates how flexible and convenient usage of parameters can be. In the example two new Sales departments are added to table Dept; then all departments with this name are rendered to console.

First, query text is assigned to SqlCommand object. When SqlCommand.ParameterCheck is set to true dotConnect for SQL Server automatically creates collection of parameters you can access to set values. Second, SqlCommand.Prepare method call is issued to achieve the best performance. Two rows are added to table then, each referencing Sales department. Afterwards the query text is changed and again SqlCommand.Parameters collection is rebuilt. This time it has only one item in it. Notice that this parameter was assigned an Int32 value before and now it holds a string. This is possible since you do not assign SqlParameter.SqlType explicitly. Once you set a value to SqlType or DbType property autodetection of data type ceases to work.

static void Main(string[] args)
{
  SqlConnection myConn = new SqlConnection(
  "user id=root;database=demobase;DataSource=127.0.0.1");
  myConn.Open();
  SqlCommand myCommand = new SqlCommand("INSERT INTO Dept VALUES (?, ?, ?)", myConn);
  myCommand.ParameterCheck = true;
  myCommand.Prepare();
  myCommand.Parameters[0].Value = 60;
  myCommand.Parameters[1].Value = "SALES";
  myCommand.Parameters[2].Value = "LA";
  myCommand.ExecuteNonQuery();
  myCommand.Parameters[0].Value = 70;
  myCommand.Parameters[2].Value = "DETROIT";
  myCommand.ExecuteNonQuery();
  myCommand.CommandText = "SELECT * FROM Dept WHERE DName=?";
  myCommand.Parameters[0].Value = "SALES";
  SqlDataReader myReader = myCommand.ExecuteReader();
  while (myReader.Read())
  {
    Console.WriteLine(myReader.GetInt32(0) + ", " + myReader.GetString(2));
  }
  myReader.Close();
  myConn.Close();
  Console.ReadLine();
}

Sub Main()
  Dim myConn As SqlConnection = New SqlConnection( _
  "user id=root;database=demobase;DataSource=127.0.0.1")
  myConn.Open()
  Dim myCommand As SqlCommand = New SqlCommand("INSERT INTO Dept VALUES (?, ?, ?)", myConn)
  myCommand.ParameterCheck = True
  myCommand.Prepare()
  myCommand.Parameters(0).Value = 60
  myCommand.Parameters(1).Value = "SALES"
  myCommand.Parameters(2).Value = "LA"
  myCommand.ExecuteNonQuery()
  myCommand.Parameters(0).Value = 70
  myCommand.Parameters(2).Value = "DETROIT"
  myCommand.ExecuteNonQuery()
  myCommand.CommandText = "SELECT * FROM Dept WHERE DName=?"
  myCommand.Parameters(0).Value = "SALES"
  Dim myReader As SqlDataReader = myCommand.ExecuteReader()
  While myReader.Read()
    Console.WriteLine(myReader.GetInt32(0).ToString() + ", " _
                    + myReader.GetString(2))
  End While
  myReader.Close()
  myConn.Close()
  Console.ReadLine()
End Sub

The following example shows how to get a stored function to work. We will not use parameters autosynchronisation here.

Consider you have a stored procedure that accepts a user name, adds some string to it, then adds server version and returns the outcome as function result. It may be described as follows:

CREATE FUNCTION demobase.myfunc(uname CHAR(20))
  RETURNS CHAR(60)
  RETURN CONCAT(uname,' works with server ',@@version);

To pass a parameter to the function and obtain a return value you can use the following sample code.

static void CallProc()
{
  //Establish connection
  SqlConnection myConn = new SqlConnection(
  "user id=root;database=demobase;DataSource=127.0.0.1");
  myConn.Open();
  //Set up myCommand to reference stored procedure 'myfunc'
  SqlCommand myCommand = new SqlCommand("myfunc", myConn);
  myCommand.CommandType = System.Data.CommandType.StoredProcedure;

  //Create input parameter and assign a value
  SqlParameter myInParam = new SqlParameter();
  myInParam.Value = "Mark";
  myCommand.Parameters.Add(myInParam);
  myInParam.Direction = System.Data.ParameterDirection.Input;

  //Create placeholder for return value
  SqlParameter myRetParam = new SqlParameter();
  myRetParam.Direction = System.Data.ParameterDirection.ReturnValue;
  myCommand.Parameters.Add(myRetParam);

  //Execute the function. ReturnValue parameter receives result of the stored function
  myCommand.ExecuteNonQuery();
  Console.WriteLine(myRetParam.Value.ToString());
  myConn.Close();
}

Sub CallProc()
  'Establish connection
  Dim myConn As SqlConnection = New SqlConnection( _
  "user id=root;database=demobase;DataSource=127.0.0.1;password=root")
  myConn.Open()
  'Set up myCommand to reference stored procedure 'myfunc'
  Dim myCommand As SqlCommand = New SqlCommand("myfunc", myConn)
  myCommand.CommandType = System.Data.CommandType.StoredProcedure

  'Create input parameter and assign a value
  Dim myInParam As SqlParameter = New SqlParameter()
  myInParam.Value = "Mark"
  myCommand.Parameters.Add(myInParam)
  myInParam.Direction = System.Data.ParameterDirection.Input

  'Create placeholder for return value
  Dim myRetParam As SqlParameter = New SqlParameter()
  myRetParam.Direction = System.Data.ParameterDirection.ReturnValue
  myCommand.Parameters.Add(myRetParam)

  'Execute the function. ReturnValue parameter receives result of the stored function
  myCommand.ExecuteNonQuery()
  Console.WriteLine(myRetParam.Value.ToString())
  myConn.Close()
End Sub

The last example demonstrates how to call a stored procedure in autosynchronization mode.
Consider you have a stored procedure that adds a new employee to table Emp. It determines next suitable EmpNo, pastes current date, and checks for input parameters. If they contain a reasonable value, the procedure pastes this value as well; if a parameter contains NULL value, some defaults are used. Here is how source code for this procedure may look:

CREATE PROCEDURE `demobase`.`AddEmp`(EmpName CHAR(20), Salary INT)
BEGIN
DECLARE e_No INT;
DECLARE e_Name CHAR(20) DEFAULT 'Unnamed';
DECLARE e_Sal INT DEFAULT 1100;
IF EmpName IS NOT NULL THEN SET e_Name = EmpName; END IF;
IF Salary  IS NOT NULL THEN SET  e_Sal = Salary;  END IF;
SELECT Max(EmpNo) INTO e_No FROM demobase.emp;
INSERT INTO demobase.emp (EmpNo, EName, Sal, HireDate)
       VALUES (e_No+10, e_Name, e_Sal, CurDate());
END

We will invoke this procedure and pass it single parameter - EmpName. Since ParameterCheck is true, the second parameter will be created in the moment of calling ExecuteNonQuery method. So this code will result in adding of new employee with name Roger and default salary (1100).

Note that in autosynchronisation mode the only thing that matters is name of a parameter. You do not have to take care of creation order and there's no need to create parameters that are intended to have NULL value. Put another words, if we need to add an employee with default name but with specific salary, we can create single argument with ParameterName set to "Salary".

static void CallProc()
{
  //Establish connection
  SqlConnection myConn = new SqlConnection(
  "user id=root;database=demobase;DataSource=127.0.0.1;password=root");
  myConn.Open();
  //Set up myCommand to reference stored procedure 'AddEmp'
  SqlCommand myCommand = new SqlCommand("AddEmp", myConn);
  myCommand.CommandType = CommandType.StoredProcedure;
  myCommand.ParameterCheck = true;

  //Create input parameter and assign a value
  SqlParameter myInParam1 = new SqlParameter();
  myInParam1.Value = "Roger";
  myInParam1.ParameterName = "EmpName";
  myCommand.Parameters.Add(myInParam1);
  myInParam1.Direction = System.Data.ParameterDirection.Input;

  //Execute the procedure.
  myCommand.ExecuteNonQuery();
  Console.WriteLine("Done");
  myConn.Close();
}

Sub CallProc()
  'Establish connection
  Dim myConn As SqlConnection = New SqlConnection( _
  "user id=root;database=demobase;DataSource=127.0.0.1;password=root")
  myConn.Open()
  'Set up myCommand to reference stored procedure 'AddEmp'
  Dim myCommand As SqlCommand = New SqlCommand("AddEmp", myConn)
  myCommand.CommandType = System.Data.CommandType.StoredProcedure
  myCommand.ParameterCheck = True

  'Create input parameter and assign a value
  Dim myInParam As SqlParameter = New SqlParameter
  myInParam.ParameterName = "EmpName"
  myInParam.Value = "Roger"
  myCommand.Parameters.Add(myInParam)
  myInParam.Direction = System.Data.ParameterDirection.Input

  'Execute the procedure
  myCommand.ExecuteNonQuery()
  Console.WriteLine("Done.")
  myConn.Close()
End Sub

See Also

SqlParameter Class