Using Parameters

In This Topic

dotConnect for PostgreSQL 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 PostgreSQL, as well as parameters synchronization and some nuances related to usage of stored procedures.
The article consists of following sections:

Parameters Basics
Using Automatic Parameters Synchronization
Using Parameters with Stored Procedures
Stored Procedure Overloads
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 function is executed. Parameter is represented by PostgreSql.PgSqlParameter class. All parameters that take part in query execution constitute a collection that can be accessed through PgSqlCommand.Parameters property.

There are two kinds of parameters: named and unnamed.

Unnamed parameters can be specified as '?' symbol (the simplest form), of '$' symbol followed by ordinal of the parameter in the whole collection, for example, '$2' denotes second parameter in the collection. The following query

    INSERT INTO dept (deptno, dname, loc) VALUES (?, ?, ?)

declares that three parameters are required to run the query properly. To set parameters for this query you can use the next code:

Named parameters must correspond to the names of the PgSqlParameter instances in the collection. Named parameters are declared using ':' or '@' prefix followed by name of the parameter in SQL code. Note that the name of the PgSqlParameter object in the collection of the command should contain the '@' prefix if parameter in CommandText is used with the '@' prefix. If the parameter in CommandText contains the ':' prefix, the name of the PgSqlParameter object in the command's collection should be used without any prefix You can use these prefixes at any combinations to specify parameters. There are two main advantages of named parameters. First, you do not have to care about the order in which parameters are created. Second, named parameter can appear more than once in query text, but you have to create only one instance of it in Parameters collection.

For example, a simple Update statement that requires named 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:

Using Automatic Parameters Synchronization

The behavior described above assumes that PgSqlCommand.ParameterCheck is false (by default). By turning it on you enable automatic synchronization of query text and PgSqlCommand.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 PgSqlCommand.Parameters collection, you can specify only the ones you really need. The synchronization is done as follows:

If quantity of input parameters in the query text is more than quantity of input parameters in PgSqlCommand.Parameters collection, new parameters are created and added to it, with PgSqlParameter.Value assigned to DBNull. PgSqlParameter.PgSqlType is set to PgSqlDbType.VarChar. This property is updated to a correct type when a value is assigned to the parameter.
If quantity of parameters in the query text is less than in PgSqlCommand.Parameters collection, redundant parameters are deleted from it.
If named parameters are involved in the query, newly created parameters in the collection are named so that they correspond to ones in the text.
Values of parameters are not altered, except for the ones created during the synchronization process. But if PgSqlCommand.CommandText is altered, the parameter collection will be regenerated entirely.

The synchronization takes place when:

PgSqlCommand.ParameterCheck is set to true
PgSqlCommand.CommandType is set to "Text"
PgSqlCommand.CommandText is altered
Query is prepared
Query is executed

When PgSqlCommand.ParameterCheck is true, every change of PgSqlCommand.CommandText or PgSqlCommand.CommandType leads to deletion of all ReturnValue parameters. You have to add them manually each time you change one of these properties.

Using Parameters with Stored Procedures

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 PgSqlCommand.ParameterCheck to true to reoder parameters in a proper way.

When PgSqlCommand.ParameterCheck is true, first call to PgSqlCommand.Prepare or PgSqlCommand.Execute methods leads to recreation of all argument parameters. If type, position, and direction of a parameter is suitable for the new description of stored procedure, parameter's value is preserved in the collection, otherwise it is lost. If PgSqlCommand.CommandText and PgSqlParameter.Direction properties 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 Int values and then changed PgSqlCommand.CommandText to reference another stored procedure that accepts a Time and Int arguments. In this case you will have first argument of type Time with DbNull value, and second Int argument unchanged with its original value.

The return value parameters with Direction = ReturnValue commonly behave as described above. If the function returns a single value (including ROW and PL/SQL records), the parameter's name must be 'return_value', and type must be Row. This is what happens when ParameterCheck is true.

When ParameterCheck is false, you can also split the returning value into fields. To make this happen, the names of the parameters must match names of the corresponding columns in the result set, so that values of the first resultset row can be inserted into appropriate columns. The whole resultset that the command returns is accessible in common ways (just like a common SELECT statement).

Stored Procedure Overloads

Quantity of input parameters defines which stored procedure overload will be called when PgSqlCommand.Execute is called. If no proper overload can be found, an exception is raised in Execute or Prepare methods. You can obtain information on quantity of arguments for certain procedure by calling PgSqlCommandBuilder.DeriveParameters method which populates PgSqlCommand.Parameters collection with data obtained from server.

You can also use the following syntax for executing or describing specific overloaded procedure: "StoredProcName:1" or "StoredProcName:5". The first example executes the first overloaded stored procedure, while the second example executes the fifth overloaded procedure. This notation is only available when ParameterCheck is true.

Performance Issues

In general, setting PgSqlCommand.ParameterCheck property to true leads to some performance loss.
When PgSqlCommand.CommandType is "Text" the synchronization is performed on client, so performance reduces slightly.
When PgSqlCommand.CommandType is "StoredProcedure", dotConnect for PostgreSQL sends additional requests to server which are necessary to determine quantity and type of parameters. Thus performance mostly depends on how fast these additional queries are executed.

To optimize query multiple execution you can manually call PgSqlCommand.Prepare method that creates compiled version of the query on server and forces synchronization. After the query is prepared it is not advised to alter PgSqlCommand.Text or PgSqlParameter.Direction properties because the PgSqlCommand becomes unprepared again. The recommended practice is to set up query text and parameters, execute PgSqlCommand.Prepare method, and afterwards change only parameters' values.

Examples

The following example demonstrates use of PgSqlParameter class with a function that returns a row of data. It assumes that you have a stored procedure on the server like this one:

    CREATE OR REPLACE FUNCTION getdept(int4, int4)
      RETURNS SETOF dept AS
    $BODY$DECLARE
       rc dept%ROWTYPE;
       minDeptNo alias for $1;
       maxDeptNo alias for $2;
      BEGIN
         FOR rc IN SELECT * FROM public.dept  WHERE deptno >= minDeptNo AND deptno <= maxDeptNo ORDER BY deptno LOOP
             RETURN NEXT rc;
         END LOOP;
         RETURN;
      END;
     $BODY$
      LANGUAGE 'plpgsql' VOLATILE;

    static void Main(string[] args) {

      PgSqlConnection connection = new PgSqlConnection("host=localhost;database=test;user=postgres");
      PgSqlCommand command;
      PgSqlParameter minDeptNo, maxDeptNo;  //input parameters
      PgSqlParameter retDeptNo, retDeptName, retDeptLoc;  //return value parameters

      connection.Open();
      command = connection.CreateCommand();
      command.CommandText = "getdept";
      command.CommandType = CommandType.StoredProcedure;
      minDeptNo = command.Parameters.Add("minDeptNo", PgSqlType.Int);
      maxDeptNo = command.Parameters.Add("maxDeptNo", PgSqlType.Int);

      retDeptNo = command.Parameters.Add("deptno", PgSqlType.Int);
      retDeptName = command.Parameters.Add("dname", PgSqlType.VarChar, 12);
      retDeptLoc = command.Parameters.Add("loc", PgSqlType.VarChar, 13);

      retDeptNo.Direction = ParameterDirection.ReturnValue;
      retDeptName.Direction = ParameterDirection.ReturnValue;
      retDeptLoc.Direction = ParameterDirection.ReturnValue;

      command.Prepare();

      minDeptNo.Value = 10;
      maxDeptNo.Value = 20;

      command.ExecuteNonQuery();
      connection.Close();

      Console.Write("deptno = {0}, deptname = {1}, loc = {2}", retDeptNo.Value, retDeptName.Value, retDeptLoc.Value);
      Console.ReadLine();
    }

Visual Basic


Sub Main()
    Dim connection As PgSqlConnection = New PgSqlConnection("host=localhost;database=test;user=postgres")
    Dim command As PgSqlCommand
    Dim minDeptNo, maxDeptNo As PgSqlParameter  'input parameters
    Dim retDeptNo, retDeptName, retDeptLoc As PgSqlParameter  'return value parameters

    connection.Open()
    command = connection.CreateCommand()
    command.CommandText = "getdept"
    command.CommandType = CommandType.StoredProcedure
    minDeptNo = command.Parameters.Add("minDeptNo", PgSqlType.Int)
    maxDeptNo = command.Parameters.Add("maxDeptNo", PgSqlType.Int)

    retDeptNo = command.Parameters.Add("deptno", PgSqlType.Int)
    retDeptName = command.Parameters.Add("dname", PgSqlType.VarChar, 12)
    retDeptLoc = command.Parameters.Add("loc", PgSqlType.VarChar, 13)

    retDeptNo.Direction = ParameterDirection.ReturnValue
    retDeptName.Direction = ParameterDirection.ReturnValue
    retDeptLoc.Direction = ParameterDirection.ReturnValue

    command.Prepare()

    minDeptNo.Value = 10
    maxDeptNo.Value = 20

    command.ExecuteNonQuery()
    connection.Close()

    Console.Write("deptno = {0}, deptname = {1}, loc = {2}", retDeptNo.Value, retDeptName.Value, retDeptLoc.Value)
    Console.ReadLine()
End Sub

Another way to invoke a stored procedure is to use SELECT statement. This sample demonstrates usage of parameters with PgSqlCommand.ParameterCheck property set to true.

    static void Main(string[] args) {

      PgSqlConnection connection = new PgSqlConnection("host=localhost;database=test;user=postgres");
      PgSqlCommand command;
      PgSqlParameter minDeptNo, maxDeptNo;  //input parameters
      PgSqlParameter retDeptNo, retDeptName, retDeptLoc;  //return value parameters

      connection.Open();
      command = connection.CreateCommand();
      command.ParameterCheck = true;
      command.CommandText = "SELECT * FROM getdept(?, ?)";
      minDeptNo = command.Parameters[0];
      maxDeptNo = command.Parameters[1];

      retDeptNo = command.Parameters.Add("deptno", PgSqlType.Int);
      retDeptName = command.Parameters.Add("dname", PgSqlType.VarChar, 12);
      retDeptLoc = command.Parameters.Add("loc", PgSqlType.VarChar, 13);

      retDeptNo.Direction = ParameterDirection.ReturnValue;
      retDeptName.Direction = ParameterDirection.ReturnValue;
      retDeptLoc.Direction = ParameterDirection.ReturnValue;

      command.Prepare();

      minDeptNo.Value = 10;
      maxDeptNo.Value = 20;

      command.ExecuteNonQuery();
      connection.Close();

      Console.Write("deptno = {0}, deptname = {1}, loc = {2}", retDeptNo.Value, retDeptName.Value, retDeptLoc.Value);
      Console.ReadLine();
    }

Visual Basic


  Sub Main()
    Dim connection As PgSqlConnection = New PgSqlConnection("host=localhost;database=test;user=postgres;")
    Dim command As PgSqlCommand
    Dim minDeptNo, maxDeptNo As PgSqlParameter  'input parameters
    Dim retDeptNo, retDeptName, retDeptLoc As PgSqlParameter  'return value parameters

    connection.Open()
    command = connection.CreateCommand()
    command.ParameterCheck = True

    command.CommandText = "SELECT * FROM getdept(?, ?)"
    minDeptNo = command.Parameters(0)
    maxDeptNo = command.Parameters(1)

    retDeptNo = command.Parameters.Add("deptno", PgSqlType.Int)
    retDeptName = command.Parameters.Add("dname", PgSqlType.VarChar, 12)
    retDeptLoc = command.Parameters.Add("loc", PgSqlType.VarChar, 13)

    retDeptNo.Direction = ParameterDirection.ReturnValue
    retDeptName.Direction = ParameterDirection.ReturnValue
    retDeptLoc.Direction = ParameterDirection.ReturnValue

    command.Prepare()

    minDeptNo.Value = 10
    maxDeptNo.Value = 20

    command.ExecuteNonQuery()
    connection.Close()

    Console.Write("deptno = {0}, deptname = {1}, loc = {2}", retDeptNo.Value, retDeptName.Value, retDeptLoc.Value)
    Console.ReadLine()
  End Sub

This example shows how to get results through parameters for a query that returns aliased and non-aliased columns. Note that you have to check how PostgreSQL names columns that represent compound statements.

    static void Main(string[] args) {

      PgSqlConnection connection = new PgSqlConnection("host=localhost;database=test;user=postgres;");
      PgSqlCommand command;
      PgSqlParameter retSalary, retJob, retCount;  //return value parameters

      connection.Open();
      command = connection.CreateCommand();
      command.ParameterCheck = true;
      command.CommandText = "SELECT Sum(Sal) AS Salary, Count(*), Job FROM emp GROUP BY Job ORDER BY Salary DESC";

      retCount = command.Parameters.Add("count", PgSqlType.Int);
      retSalary = command.Parameters.Add("Salary", PgSqlType.Int);
      retJob = command.Parameters.Add("Job", PgSqlType.VarChar, 40);

      retCount.Direction = ParameterDirection.ReturnValue;
      retSalary.Direction = ParameterDirection.ReturnValue;
      retJob.Direction = ParameterDirection.ReturnValue;

      command.ExecuteNonQuery();
      connection.Close();

      Console.Write("count = {0}, salary = {1}, job = {2}", retCount.Value, retSalary.Value, retJob.Value);
      Console.ReadLine();
    }

Visual Basic