You can execute SQL statements in two main ways. First you can simply write the relevant SQL statements inside your .NET application code (this is what we have been doing in our examples so far). Secondly, you can package the SQL statements inside a stored procedure and then execute that stored procedure from your .NET application.
The reason for the significant performance improvement is the fact that a stored procedure only needs to parsed, compiled and optimized once on the server side as opposed to a simple SQL statement which is parsed, compiled and optimized each time it is executed from a .NET application.
Another plus point about stored procedures is that you can package multiple statements inside a stored procedure and execute them in one go. Stored procedures for EnterpriseDB are written in SPL which supports complex conditional as well as looping constructs which give you more power over what you want to do as opposed to simple SQL statements.
Stored procedures have another added bonus. They allow you to separate your database layer from your application layer. If you need to make any changes in the way data is fetched you would only need to change your stored procedure without making any changes to your .NET application.
In order to use a stored procedure in your .NET application you need to do the following:
Create an SPL stored procedure on the database server containing the statements that you want to execute
Import the EnterpriseDB.EDBClient namespace
Pass the name of the stored procedure to the instance of the EDBCommand with which we are executing the stored procedure
Change the EDBCommand's CommandType to CommandType.StoredProcedure
Execute the command
Before anything else you need to create the SPL stored procedure. You can create the stored procedure either through EDB Studio or via EDB-PSQL+. The SPL for our sample stored procedure is as listed below:
CREATE OR REPLACE PROCEDURE list_dept10
IS
v_deptname VARCHAR2(30);
BEGIN
DBMS_OUTPUT.PUT_LINE('Dept No: 10');
SELECT dname INTO v_deptname FROM dept WHERE deptno = 10;
DBMS_OUTPUT.PUT_LINE('Dept Name: ' || v_deptname);
END;
Via EDB Studio
Launch EDB Studio
Expand the database you wish to create the stored procedure under
Go to Tool --> Query Tool
In the Query Tool window type the SPL code for your stored procedure
Compile the stored procedure by pressing F5.
Via EDB-PSQL+
Invoke EDB-PSQL+ and connect to the database you wish to create the stored procedure under
Type in the SPL code at command line
Go to Tool --> Query Tool
If your stored procedure compiled without any error messages you should see a "CREATE PROCEDURE" command echoed from command prompt
The list_dept10 stored procedure is fairly straight forward. This stored procedure takes no parameters, and returns no parameters but just prints the name of the department whose department number is 10 on the server end. The body of the stored procedure appears after the AS keyword, where we are declaring one variable called v_deptname of type VARCHAR which is to contain the name of the department.
Any variables that need to be used within the body of the stored procedure need to be declared after the AS keyword but before the BEGIN block. The actual meat of the stored procedure goes in the statements that lie between the BEGIN and END block.
For our example the BEGIN and END block primarily consist of one SQL statement which is returning the department name against department number 10 which is being stored into the v_deptname variable and then echoed out with DBMS_OUTPUT.PUT_LINE.
In order to access the stored procedure from a .NET client application, you use the EnterpriseDB.EDBClient namespace. This namespace contains the objects used to interact with EnterpriseDB. An EDBConnection object is used to establish a connection into the database. Once the connection is established an EDBCommand object is used to execute SQL statements or Stored Procedures.
The CommandType property of the EDBCommand object is used to indicate what type of command is being executed. The CommandType property is set to one of three possible CommandType enumeration values. The default Text value is used when a SQL string is passed in for execution. The StoredProcedure value is used when the name of a stored procedure is passed in to execute. The TableDirect value is used when a table name is being passed in. This setting will pass back all the records in the table.
The CommandType property of the EDBCommand object is used in conjunction with the CommandType property. The CommandText property will contain a SQL string, stored procedure name, or table name depending on the setting of the CommandType property.
In order to demonstrate the process of executing a stored procedure from a C# client, create a new ASP.NET page (although the same logic would apply for any other type of .NET application be it a Windows Form/Console application, web service etc.).
The following code listing shows this ASP.NET page's code.
Example 19-8. Example - Executing a simple Stored Procedure
<% @ Page Language="C#" Debug="true"%>
<% @Import Namespace="EnterpriseDB.EDBClient" %>
<% @Import Namespace="System.Data" %>
<% @Import Namespace="System.Configuration" %>
<script language="C#" runat="server" >
private void Page_Load(object sender, System.EventArgs e)
{
string strConnectionString = ConfigurationSettings.AppSettings["DB_CONN_STRING"];
EDBConnection conn = new EDBConnection(strConnectionString);
try {
conn.Open();
EDBCommand cmdStoredProc = new EDBCommand("list_dept10",conn);
cmdStoredProc.CommandType = CommandType.StoredProcedure;
cmdStoredProc.Prepare();
cmdStoredProc.ExecuteNonQuery();
Response.Write("Stored Procedure Executed Successfully");
}
catch(Exception exp) {
Response.Write(exp.ToString());
}
finally {
conn.Close();
}
}
</script>
Save the file as "storedProcSimple.aspx" and save it in your default web server's root then access it via typing http://localhost/storedProcSimple.aspx in your web browser. If no errors are generated then a "Stored Procedure Executed Successfully" message should be shown.
Now that you know how to create a basic stored procedure and call it from a .NET client, lets take a look at creating a more advanced stored procedure that includes IN parameters.
We will start of with creating the stored procedure itself first. You can create the stored procedure via EDB Studio or EDB-PSQL+ as shown for the previous example.
CREATE OR REPLACE PROCEDURE EMP_INSERT(pENAME IN VARCHAR,pJOB IN VARCHAR,pSAL IN FLOAT4, pCOMM IN FLOAT4, pDEPTNO IN INTEGER,pMgr IN INTEGER) AS DECLARE CURSOR TESTCUR IS SELECT MAX(EMPNO) FROM EMP; MAX_EMPNO INTEGER := 10; BEGIN OPEN TESTCUR; FETCH TESTCUR INTO MAX_EMPNO; INSERT INTO EMP(EMPNO,ENAME,JOB,SAL,COMM,DEPTNO,MGR) VALUES(MAX_EMPNO+1,pENAME,pJOB,pSAL,pCOMM,pDEPTNO,pMgr); close testcur; END;
Calling a stored procedure that contains parameters from a C# client is very similar to the previous process of executing a stored procedure without parameters. A EDBConnection object is used to establish a connection to the database and a EDBCommand object is used to execute the stored procedure. The difference when calling a parameterized stored procedure is the use of the Parameters collection of the EDBCommand object. When the parameter is added to the collection, the appropriate properties such as ParameterName, DbType, Direction, Size, and Value are set.
In order to demonstrate the process of executing a parameterized stored procedure from a C# client let's create another ASP.NET page. The code listing for this page is shown as below:
Example 19-9. Example - Executing a Stored Procedure with IN Parameters
<% @ Page Language="C#" Debug="true"%>
<% @Import Namespace="EnterpriseDB.EDBClient" %>
<% @Import Namespace="System.Data" %>
<% @Import Namespace="System.Configuration" %>
<script language="C#" runat="server" >
private void Page_Load(object sender, System.EventArgs e)
{
string strConnectionString = ConfigurationSettings.AppSettings["DB_CONN_STRING"];
EDBConnection conn = new EDBConnection(strConnectionString);
string empName = "EDB";
string empJob = "Manager";
double salary = 1000;
double commission = 0.0;
int deptno = 20;
int manager = 7839;
try
{
conn.Open();
EDBCommand cmdStoredProc = new EDBCommand("emp_insert(:EmpName,:Job,:Salary,:Commission,:DeptNo,:Manager)",conn);
cmdStoredProc.CommandType = CommandType.StoredProcedure;
cmdStoredProc.Parameters.Add(new EDBParameter("EmpName", EDBTypes.EDBDbType.Varchar));
cmdStoredProc.Parameters[0].Value = empName;
cmdStoredProc.Parameters.Add(new EDBParameter("Job", EDBTypes.EDBDbType.Varchar));
cmdStoredProc.Parameters[1].Value = empJob;
cmdStoredProc.Parameters.Add(new EDBParameter("Salary", EDBTypes.EDBDbType.Float));
cmdStoredProc.Parameters[2].Value = salary;
cmdStoredProc.Parameters.Add(new EDBParameter("Commission", EDBTypes.EDBDbType.Float));
cmdStoredProc.Parameters[3].Value = commission;
cmdStoredProc.Parameters.Add(new EDBParameter("DeptNo", EDBTypes.EDBDbType.Integer));
cmdStoredProc.Parameters[4].Value = deptno;
cmdStoredProc.Parameters.Add(new EDBParameter("Manager", EDBTypes.EDBDbType.Integer));
cmdStoredProc.Parameters[5].Value = manager;
cmdStoredProc.Prepare();
cmdStoredProc.ExecuteNonQuery();
Response.Write("Following Information Inserted Successfully<br>");
string empInfo = "Employee Name: " + empName + "<br>";
empInfo += "Job: " + empJob + "<br>";
empInfo += "Salary: " + salary + "<br>";
empInfo += "Commission: " + commission + "<br>";
empInfo += "Manager: " + manager + "<br>";
Response.Write(empInfo);
}
catch(Exception exp) {
Response.Write(exp.ToString());
}
finally {
conn.Close();
}
}
</script>
</script>
In the body of the Page_Load method for this page we are declaring and instantiating an EDBConnection object.
Next, we are creating an EDBCommand object and setting the appropriate properties needed to execute the EMP_INSERT stored procedure created earlier.
EDBCommand cmdStoredProc = new EDBCommand("emp_insert(:EmpName,:Job,:Salary,:Commission,:DeptNo,:Manager)",conn);
cmdStoredProc.CommandType = CommandType.StoredProcedure;
Using the Add method of the EDBCommand's Parameter collection adds 6 input parameters, assigns them values, these parameters are then are passed to EMP_INSERT stored procedure.
To execute the stored procedure we call the ExecuteNonQuery method of the EDBCommand object. Once the stored procedure is executed, a test record is inserted into the emp table and the values inserted should be displayed on the webpage.
In the previous example we looked at how to pass values in the shape of IN parameters to a stored procedure, now let us take a look at how to return values from a stored procedure. For this purpose we use Output parameters which are returned back to the caller and are designated by the OUT keyword.
The following stored procedure passes in the one IN parameter the department number, and returns two OUT parameters which are the corresponding location and name against the passed in department number.
CREATE OR REPLACE PROCEDURE DEPT_SELECT(pDEPTNO IN INTEGER,pDNAME OUT VARCHAR,pLOC OUT VARCHAR) AS DECLARE CURSOR TESTCUR IS SELECT DNAME,LOC FROM DEPT; REC RECORD; BEGIN OPEN TESTCUR; FETCH TESTCUR INTO REC ; pDNAME:=REC.DNAME; pLOC:=REC.LOC; close testcur; END;
Getting values from OUT parameters is fairly simple except that we have to explicitly specify the direction for our parameters as Output.
We can retrieve the values from Output parameters in 2 ways:
We can call the ExecuteReader method on the EDBCommand and explicitly loop through the returned EDBDataReader for the values of the OUT parameters.
Call the ExecuteNonQuery method of EDBCommand and explicitly get the value of a declared Output parameter by calling that EDBParameter's "value" property.
The following code listings show both methods of getting Output values from a stored procedure.
Example 19-10. Example - Executing a Stored Procedure with OUT Parameters (Method 1)
<% @ Page Language="C#" Debug="true"%>
<% @Import Namespace="EnterpriseDB.EDBClient" %>
<% @Import Namespace="System.Data" %>
<% @Import Namespace="System.Configuration" %>
<script language="C#" runat="server" >
private void Page_Load(object sender, System.EventArgs e)
{
string strConnectionString = ConfigurationSettings.AppSettings["DB_CONN_STRING"];
EDBConnection conn = new EDBConnection(strConnectionString);
try
{
conn.Open();
EDBCommand command = new EDBCommand("DEPT_SELECT(:pDEPTNO,:pDNAME,:pLOC)", conn);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(new EDBParameter("pDEPTNO", EDBTypes.EDBDbType.Integer,10,"pDEPTNO",ParameterDirection.Input,false ,2,2,System.Data.DataRowVersion.Current,1));
command.Parameters.Add(new EDBParameter("pDNAME", EDBTypes.EDBDbType.Varchar,10,"pDNAME",ParameterDirection.Output,false ,2,2,System.Data.DataRowVersion.Current,1));
command.Parameters.Add(new EDBParameter("pLOC", EDBTypes.EDBDbType.Varchar,10,"pLOC",ParameterDirection.Output,false ,2,2,System.Data.DataRowVersion.Current,1));
command.Prepare();
command.Parameters[0].Value = 10;
EDBDataReader result = command.ExecuteReader();
int fc=result.FieldCount;
while(result.Read())
{
for(int i=0;i<fc;i++)
{
Response.Write("RESULT["+i+"]="+ Convert.ToString(command.Parameters[i].Value));
Response.Write("<br>");
}
}
}
catch(EDBException exp)
{
Response.Write(exp.ToString());
}
finally
{
conn.Close();
}
}
</script>
Example 19-11. Example - Executing a Stored Procedure with OUT Parameters (Method 2)
<% @ Page Language="C#" Debug="true"%>
<% @Import Namespace="EnterpriseDB.EDBClient" %>
<% @Import Namespace="System.Data" %>
<% @Import Namespace="System.Configuration" %>
<script language="C#" runat="server" >
private void Page_Load(object sender, System.EventArgs e)
{
string strConnectionString = ConfigurationSettings.AppSettings["DB_CONN_STRING"];
EDBConnection conn = new EDBConnection(strConnectionString);
try
{
conn.Open();
EDBCommand command = new EDBCommand("DEPT_SELECT(:pDEPTNO,:pDNAME,:pLOC)", conn);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(new EDBParameter("pDEPTNO", EDBTypes.EDBDbType.Integer,10,"pDEPTNO",ParameterDirection.Input,false ,2,2,System.Data.DataRowVersion.Current,1));
command.Parameters.Add(new EDBParameter("pDNAME", EDBTypes.EDBDbType.Varchar,10,"pDNAME",ParameterDirection.Output,false ,2,2,System.Data.DataRowVersion.Current,1));
command.Parameters.Add(new EDBParameter("pLOC", EDBTypes.EDBDbType.Varchar,10,"pLOC",ParameterDirection.Output,false ,2,2,System.Data.DataRowVersion.Current,1));
command.Prepare();
command.Parameters[0].Value = 10;
command.ExecuteNonQuery();
Response.Write(command.Parameters["pDNAME"].Value.ToString());
Response.Write(command.Parameters["pLOC"].Value.ToString());
}
catch(EDBException exp)
{
Response.Write(exp.ToString());
}
finally
{
conn.Close();
}
}
</script>
The previous sections talked about passing both IN and OUT parameters to a stored procedure. But it is also possible to pass a third type of parameter called INOUT parameter to/from a stored procedure. An INOUT parameter is pretty much what it says it is. It's a parameter that you can pass as an IN parameter, change its value and then return that changed value back to the caller of your application. .
The follwing stored procedure "DEPT_UPDATE" accepts one IN parameter (department number) and then returns the respective information for an employee (via four OUT parameters) against that deptno.
CREATE OR REPLACE PROCEDURE DEPT_UPDATE(pDEPTNO IN INTEGER,pLOCATION IN OUT VARCHAR) AS DECLARE CURSOR TESTCUR IS SELECT LOC FROM DEPT WHERE DEPTNO=pDEPTNO; BEGIN UPDATE DEPT SET LOC='EDISON' WHERE DEPTNO=pDEPTNO; OPEN TESTCUR; FETCH TESTCUR INTO pLOCATION; END;
Setting and getting the values from an INOUT parameters is fairly simple except that we have to explicitly specify the direction for our parameters as InputOutput.
Example 19-12. Example - Executing a Stored Procedure with INOUT Parameters
<% @ Page Language="C#" Debug="true"%>
<% @Import Namespace="EnterpriseDB.EDBClient" %>
<% @Import Namespace="System.Data" %>
<% @Import Namespace="System.Configuration" %>
<script language="C#" runat="server" >
private void Page_Load(object sender, System.EventArgs e)
{
string strConnectionString = ConfigurationSettings.AppSettings["DB_CONN_STRING"];
EDBConnection conn = new EDBConnection(strConnectionString);
try
{
conn.Open();
EDBCommand command = new EDBCommand("dept_update(:DeptNo,:Location)", conn);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(new EDBParameter("DeptNo", EDBTypes.EDBDbType.Integer));
command.Parameters.Add(new EDBParameter("Location", EDBTypes.EDBDbType.Varchar,10,"Location",ParameterDirection.InputOutput,false ,2,2,System.Data.DataRowVersion.Current,1));
command.Prepare();
command.Parameters[0].Value = 40;
command.Parameters[1].Value = "BOSTON";
command.ExecuteNonQuery();
string changedLocation = command.Parameters[1].Value.ToString();
Response.Write("The new location of Department 40 is: " + changedLocation);
}
catch(EDBException exp)
{
Response.Write(exp.ToString());
}
finally
{
conn.Close();
}
}
</script>
Save the file as "storedProcInOut.aspx" and save it in your default web root and test it out by typing http://localhost/storedProcInOut.aspx in your browser window. If the stored procedure executed successfully you should see a message saying "The new location of Department 40 is EDISON"