Monday, February 9, 2009

How to access an Oracle function in .NET?

Oracle function comprises of a set of parameters (Input or Output) and body which contains the actually logic written in it. Return type of the function is defined as well.
Finally the oracle function is expected to return a value.
Oracle stored function:



So for calling an oracle function in .NET class,
1) we can write a simple sql query that calls the oracle function and returns the value.
I would like to create a function that has 2 parameters.
Example:-
Step 1:



AInputNumber
BInputNumber


So function returns value based upon given Input parameters (“A”, “B”) in function body.
Say suppose, I would like to create this function to find sum of given “A” and “B” then,

CREATE OR REPLACE FUNCTION Fun_Sum
(
A IN NUMBER,
B IN NUMBER
)
RETURN NUMBER
IS
c NUMBER;
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;
--Return value of C
RETURN C;
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 strSelectQuery = "select Fun_Sum({0},{1}) as C from dual";
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 connection object which accepts connection
//string parameter
OracleConnection con = new OracleConnection(strConnectionString);
try
{
//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(String.Format(strSelectQuery, A, B));
//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 text
cmd.CommandType = CommandType.Text;
//executing the oracle command object, which executes the body of the
//function considering the given input parameter values
return Convert.ToInt32(cmd.ExecuteScalar());
}
catch
{
throw;
}
finally
{
//closing the oracle connection
con.Close();
}

}
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: