How to use SQL parameters with dotConnect for Oracle
dotConnect for Oracle 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 Oracle, as well as parameters synchronization and some nuances related to usage of stored procedures.
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 Oracle.OracleParameter class. All parameters that take part in query execution constitute a collection that can be accessed through OracleCommand.Parameters property.
Parameters require accordance with names of OracleParameter instances in the collection. Parameters are declared using ':' prefix followed by name of the parameter. You do not have to care about the order in which parameters are created. 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 following code:
[C#]
OracleCommand myCommand1;
...
myCommand1.CommandText = "UPDATE dept SET dname = :dname, " +
"loc = :loc WHERE deptno = :deptno";
myCommand1.Parameters.Add("deptno", 20);
myCommand1.Parameters.Add("dname", "SALES");
myCommand1.Parameters.Add("loc", "NEW YORK");
[Visual Basic]
Dim myCommand1 as OracleCommand
...
myCommand1.CommandText = "UPDATE dept SET dname = :dname, " & _
"loc = :loc WHERE deptno = :deptno"
myCommand1.Parameters.Add("deptno", 20)
myCommand1.Parameters.Add("dname", "SALES")
myCommand1.Parameters.Add("loc", "NEW YORK")
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 names, quantity and types. Parameters that represent arguments to the procedure must have their Direction property set to Input, InputOutput or Output. 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 OracleCommand.ParameterCheck is false (by default). By turning it on you enable automatic synchronization of query text and OracleCommand.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 OracleCommand.Parameters collection, you can specify only the ones you really need. The synchronization is done as follows:
- If 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:
- OracleCommand.ParameterCheck is set to true
- OracleCommand.CommandType is set to "Text"
- OracleCommand.CommandText is altered
For stored procedures synchronization happens when:
- Query is prepared
- Query is executed
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
When OracleCommand.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 parameter's name must match exactly name of procedure's argument. However the ReturnValue parameter needs not to have any specific name. Moreover, the ReturnValue parameter is assigned name 'RESULT' on query preparation.
In synchronization mode first call to OracleCommand.Prepare or OracleCommand.Execute methods leads to recreation of all argument parameters. If name of a parameter is suitable for the description of stored procedure, parameter is preserved in the collection, otherwise it is lost. If OracleCommand.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 OracleCommand.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.
More information
There are two more important issues concerning parameters in dotConnect for Oracle that are available as separate articles. These techniques are not obligatory but using them you can help yourself a deal.
You can use REF CURSOR output parameters to return a result set or several result sets from a procedure or PL/SQL block. The article Working with Cursors covers this matter. You can also find there sample code that shows how to fill a dataset from REF CURSOR output parameters.
Another very useful feature related to usage of parameters is array binding. It allows executing some query many times with different values of parameters. This capability significantly increases query performance and reduces network load. The article Working with Array Binding demonstrates how to use this feature.
For information on how to use parameters with PL/SQL tables please refer to article Working with PL/SQL Tables.
Performance issues
In general, setting OracleCommand.ParameterCheck property to true leads to some performance loss.
When OracleCommand.CommandType is "Text" the synchronization is performed on client, so performance reduces very slightly.
When OracleCommand.CommandType is "StoredProcedure", dotConnect for Oracle 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 OracleCommand.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 OracleCommand.Text property because the OracleCommand becomes unprepared again. The recommended practice is to set up query text and parameters, execute OracleCommand.Prepare method, and afterwards change only parameters' values.
You can greatly speed up multiple execution with array binding. Refer to article Working with Array Binding for information on the issue.
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 OracleCommand object. When OracleCommand.ParameterCheck is set to true dotConnect for Oracle automatically creates collection of parameters you can access to set values. Second, OracleCommand.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 OracleCommand.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 OracleParameter.OracleDbType explicitly. Once you set a value to OracleDbType or DbType property autodetection of data type ceases to work.
[C#]
static void Main(string[] args)
{
OracleConnection myConn = new OracleConnection(
"User Id=Scott;Password=tiger;Data Source=Ora");
myConn.Open();
OracleCommand myCommand = new OracleCommand(
"INSERT INTO Dept VALUES (:p1, :p2, :p3)", 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=:p1";
myCommand.Parameters[0].Value = "SALES";
OracleDataReader myReader = myCommand.ExecuteReader();
while (myReader.Read())
{
Console.WriteLine(myReader.GetInt32(0) + ", " + myReader.GetString(2));
}
myReader.Close();
myConn.Close();
Console.ReadLine();
}
[Visual Basic]
Sub Main() Dim myConn As OracleConnection = New OracleConnection( _ "User Id=Scott;Password=tiger;Data Source=Ora") myConn.Open() Dim myCommand As OracleCommand = New OracleCommand( _ "INSERT INTO Dept VALUES (:p1, :p2, :p3)", 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=:p1" myCommand.Parameters(0).Value = "SALES" Dim myReader As OracleDataReader = 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 job name, selects employees with that job from table Emp, gets their total salary and returns it as function result. It may be described as follows:
CREATE FUNCTION GETTOTALSALARY (JobParam IN VARCHAR) RETURN NUMBER AS TotalSalary NUMBER; BEGIN SELECT SUM(Sal) INTO TotalSalary FROM Emp WHERE Job=JobParam GROUP BY Job; RETURN TotalSalary; END;
To pass a parameter to the function and obtain a return value you can use the following sample code.
[C#]
static void CallProc()
{
//Prepare connection and command
OracleConnection myConn = new OracleConnection(
"User Id=Scott;Password=tiger;Data Source=Ora");
myConn.Open();
OracleCommand myCommand = new OracleCommand("GETTOTALSALARY", myConn);
myCommand.CommandType = System.Data.CommandType.StoredProcedure;
//Prepare parameters manually
OracleParameter myInParam = myCommand.Parameters.Add("JobParam", "SALESMAN");
OracleParameter myReturParam = new OracleParameter();
myReturParam.Direction = System.Data.ParameterDirection.ReturnValue;
myCommand.Parameters.Add(myReturParam);
//Execute the function and render result
myCommand.ExecuteNonQuery();
Console.WriteLine(myReturParam.Value);
myConn.Close();
Console.ReadLine();
}
[Visual Basic]
Sub CallProc()
'Prepare(connection And Command())
Dim myConn As OracleConnection = New OracleConnection( _
"User Id=Scott;Password=tiger;Data Source=Ora")
myConn.Open()
Dim myCommand As OracleCommand = New OracleCommand("GETTOTALSALARY", myConn)
myCommand.CommandType = System.Data.CommandType.StoredProcedure
'Prepare parameters manually
Dim myInParam As OracleParameter = myCommand.Parameters.Add("JobParam", "SALESMAN")
Dim myReturParam As OracleParameter = New OracleParameter()
myReturParam.Direction = System.Data.ParameterDirection.ReturnValue
myCommand.Parameters.Add(myReturParam)
'Execute the function and render result
myCommand.ExecuteNonQuery()
Console.WriteLine(myReturParam.Value)
myConn.Close()
Console.ReadLine()
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 ADDEMP ( EmpName IN VARCHAR, Salary IN NUMBER ) AS e_No NUMBER; e_Name VARCHAR(40); e_Sal NUMBER; BEGIN e_Name := 'Unnamed'; --Default value e_Sal := 1100; --Default value IF EmpName IS NOT NULL THEN e_Name := EmpName; END IF; IF Salary IS NOT NULL THEN e_Sal := Salary; END IF; SELECT Max(EmpNo) INTO e_No FROM Emp; INSERT INTO Emp (EmpNo, EName, Sal, HireDate) VALUES (e_No+10, e_Name, e_Sal, SYSDATE); 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".
[C#]
static void CallProc()
{
//Establish connection
OracleConnection myConn = new OracleConnection(
"User Id=Scott;Password=tiger;Data Source=Ora");
myConn.Open();
//Set up myCommand to reference stored procedure 'AddEmp'
OracleCommand myCommand = new OracleCommand("AddEmp", myConn);
myCommand.CommandType = System.Data.CommandType.StoredProcedure;
myCommand.ParameterCheck = true;
//Create input parameter and assign a value
OracleParameter myInParam1 = new OracleParameter();
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();
}
[Visual Basic]
Sub CallProc()
'Establish connection
Dim myConn As OracleConnection = New OracleConnection( _
"User Id=Scott;Password=tiger;Data Source=Ora")
myConn.Open()
'Set up myCommand to reference stored procedure 'AddEmp'
Dim myCommand As OracleCommand = New OracleCommand("AddEmp", myConn)
myCommand.CommandType = System.Data.CommandType.StoredProcedure
myCommand.ParameterCheck = True
'Create input parameter and assign a value
Dim myInParam As OracleParameter = New OracleParameter
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
Back to list