Monday, February 9, 2009

How to access an Oracle stored procedure in .NET?

Oracle procedure comprises of a set of parameters (Input or Output) and body which contains the actually logic written in it.
Oracle stored procedure:



So for a calling an oracle procedure in .NET class, we need to mention
1) Procedure name.
2) List of parameters with names, data type, type (i/p, o/p, i/o/p), length, value.
I would like to create a stored procedure that has 3 parameters.
Example:-
Step 1:




AInputNumber
BInputNumber
COutputNumber


So output parameter “C” is updated based upon given Input parameters (“A”, “B”) in procedure body.
Say suppose I would like to create this procedure to find sum of given “A” and “B” then,

CREATE OR REPLACE PROCEDURE PRC_SUM
(
A IN NUMBER,
B IN NUMBER,
C OUT NUMBER
)
AS
BEGIN
--Default value of C is Zero
c:=0;
--If A and B are not null then
IF(A IS NOT NULL AND B IS NOT NULL) THEN
--Finding sum of A and B, assigning it to C
C:=A+B;
END IF;
END;




Step 2:
Form that has been designed in C#.NET to accept 2 input parameters from user and display the sum of those 2 given parameters.



Code behind page
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using Oracle.DataAccess.Client;
namespace Sample
{
public partial class frm : Form
{
public frm()
{
InitializeComponent();
}
string strConnectionString = "Data Source=Source1; User Id=User1; Password=Fg$5gs;";
string strPrcName = "PRC_SUM";
string PARAM_A = "A";
string PARAM_B = "B";
string PARAM_C = "C";
private void btnSum_Click(object sender, EventArgs e)
{
try
{
//Checking given values are numeric or not
if (!IsNumber(txtA.Text))
throw new Exception("Enter numeric value for A");
if (!IsNumber(txtB.Text))
throw new Exception("Enter numeric value for B");
//Calling a function that returns the sum of the given 2 numbers
txtC.Text = GetSumOf(Convert.ToInt32(txtA.Text), Convert.ToInt32(txtB.Text)).ToString();
}
catch (Exception exp)
{
MessageBox.Show(exp.Message);
}
}
private int GetSumOf(int A, int B)
{
//creating oracle command object whose command text is
//name of the stored procedure that has been created to fnd
//sum of 2 input numeric parameters
OracleCommand cmd = new OracleCommand(strPrcName);
//creating oracle connection object which accepts connection
//string parameter
OracleConnection con = new OracleConnection(strConnectionString);
//opening the connection the created oracle connection object
con.Open();
//assigning the connection object to the oracle command
cmd.Connection = con;
//setting the type of the oracle command to stored procedure
cmd.CommandType = CommandType.StoredProcedure;
//adding the required oracle parameters to the oracle command object
//based upon the parameters that are defined while creating the procedure
//also assinging the values to the input parameters
cmd.Parameters.Add(PARAM_A, OracleDbType.Int32).Value=A;
cmd.Parameters.Add(PARAM_B, OracleDbType.Int32).Value=B;
cmd.Parameters.Add(PARAM_C, OracleDbType.Int32,ParameterDirection.Output);
//executing the oracle command object, which executes the body of the stored
//procedure considering the given input parameter values
cmd.ExecuteNonQuery();
//closing the oracle connection
con.Close();
//returning the value of parameter "C", which has been
//updated in stored procedure
return Convert.ToInt32(cmd.Parameters[PARAM_C].Value);
}
private bool IsNumber(string argValue)
{
try
{
//checking if the given value is numeric
Convert.ToInt32(argValue);
return true;
}
catch
{
return false;
}
}
}
}














Output:-
When user clicks on the “Sum” button after giving required “A” and “B” values.



No comments: