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
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:
PgSqlCommand pgSqlCommand1;
...
pgSqlCommand1.CommandText = "INSERT INTO dept (deptno, dname, loc) VALUES (?, ?, ?)";
pgSqlCommand1.Parameters.Add("param1", 30);
pgSqlCommand1.Parameters.Add("param2", "SALES");
pgSqlCommand1.Parameters.Add("param3", "CHICAGO");
Dim pgSqlCommand1 as PgSqlCommand
...
pgSqlCommand1.CommandText = "INSERT INTO dept (deptno, dname, loc) VALUES (?, ?, ?)"
pgSqlCommand1.Parameters.Add("param1", 30)
pgSqlCommand1.Parameters.Add("param2", "SALES")
pgSqlCommand1.Parameters.Add("param3", "CHICAGO")
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:
PgSqlCommand pgSqlCommand1;
...
pgSqlCommand1.CommandText = "UPDATE dept SET dname = :dname, loc = :loc WHERE deptno = @deptno";
pgSqlCommand1.Parameters.Add("@deptno", 20);
pgSqlCommand1.Parameters.Add("dname", "SALES");
pgSqlCommand1.Parameters.Add("loc", "NEW YORK");
Dim pgSqlCommand1 as PgSqlCommand
...
pgSqlCommand1.CommandText = "UPDATE dept SET dname = :dname, loc = :loc WHERE deptno = @deptno"
pgSqlCommand1.Parameters.Add("@deptno", 20)
pgSqlCommand1.Parameters.Add("dname", "SALES")
pgSqlCommand1.Parameters.Add("loc", "NEW YORK")
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();
}
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();
}
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();
}
Sub Main()
Dim connection As PgSqlConnection = New PgSqlConnection("host=localhost;password=postgres;database=test;user=postgres;")
Dim command As PgSqlCommand
Dim retSalary, retJob, retCount As PgSqlParameter 'return value parameter
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()
End Sub
See Also
PgSqlCommand Class
| PgSqlParameter Class