Next


Stored Procedure / Function Handling

 This includes with calling a procedure / function that is created at backend database from frontend application.
 The procedures / functions can be created using PL/SQL at database.
 PL/SQL is supported by Sql Server, Oracle and MySql.

Purpose of Stored Procedures / Functions:

 To implement complex database logics.
 To retrieve multiple tables data at-a-time, with a single database call.
 To perform multiple query / non-query operations.
 To hide query / non-query statements in the code.

API:

 Connection: Maintains the connection with database.
 Command: Calls a database procedure / function.
 Parameter: Represents the argument value for the procedure / function.

Implementation of Stored Proc / Fun Handling with “System.Data.SqlClient”:

 Import the API:
using System.Data.SqlClient;
 Prepare the Connection string:
string cnstr = “data source=<name of the server>;user id=<user name>;password=<password>;initial catalog=<database name>”;
 Construct the “Connection” class object:
SqlConnection cn = new SqlConnection(cnstr);
 Construct the “Command” class object:
SqlCommand cmd = new SqlCommand();
 Assign the reference of “Connection” class object to “Command” class object:
cmd.Connection = cn;
 Assign the procedure / function name that is to be executed:
cmd.CommandText = “xxxxx”;
 Assign the command type to “Command” class object:
cmd.CommandType = CommandType.StoredProcedure;
 Construct the “Parameter” class object(s):
SqlParameter pobj1 = new SqlParameter(“parameter name”,”value”);
…………………;
…………………;
 Assign the reference of “Parameter” class object(s) into “Command” class object:
cmd.Parameters.Add(pobj1);
………………….;
………………….;
 Open the connection:
cn.Open();
 Execute the procedure / function:
cmd.ExecuteNonQuery();
 Close the connection:
cn.Close();

Implementation of Stored Proc / Fun Handling with “System.Data.OleDb”:

 Import the API:
using System.Data.OleDb;
 Prepare the Connection string::
string cnstr = “string cnstr = “provider=<provider name>;data source=<name of the server>;user id=<user name>;password=<password>”;
 Construct the “Connection” class object::
OleDbConnection cn = new OleDbConnection(cnstr);
 Construct the “Command” class object::
OleDbCommand cmd = new OleDbCommand();
 Assign the reference of “Connection” class object to “Command” class object::
cmd.Connection = cn;
 Assign the procedure / function name that is to be executed::
cmd.CommandText = “xxxxx”;
 Assign the command type to “Command” class object::
cmd.CommandType = CommandType.StoredProcedure;
 Construct the “Parameter” class object(s)::
OleDbParameter pobj1 = new OleDbParameter(“parameter name”,”value”);
…………………;
…………………;
 Assign the reference of “Parameter” class object(s) into “Command” class object::
cmd.Parameters.Add(pobj1);
………………….;
………………….;
 Open the connection::
cn.Open();
 Execute the procedure / function::
cmd.ExecuteNonQuery();
 Close the connection::
cn.Close();

Demo on Stored Procedure Calling

Create the following procedure at Sql Server in “demo” database.

create procedure calculateresults
as begin
update students set result = 'Distinction' where marks>=80
update students set result = 'First Class' where marks>=60 and marks<80
update students set result = 'Second Class' where marks>=50 and marks<60
update students set result = 'Third Class' where marks>=35 and marks<50
update students set result = 'Fail' where marks<35
end

Create the following procedure at Oracle.

create procedure calculateresults
is begin
update students set result = 'Distinction' where marks>=80;
update students set result = 'First Class' where marks>=60 and marks<80;
update students set result = 'Second Class' where marks>=50 and marks<60;
update students set result = 'Third Class' where marks>=35 and marks<50;
update students set result = 'Fail' where marks<35;
end;

Sql Server Code

using System.Data.SqlClient;
private void btnDisplayStudents_Click(object sender, EventArgs e)
{
string cnstr = "data source=.;user id=sa;password=123;initial catalog=demo";
SqlConnection cn = new SqlConnection(cnstr);
string sqlstr = "select * from students";
SqlDataAdapter adp = new SqlDataAdapter(sqlstr, cn);
DataSet ds = new DataSet();
DataTable dt;
cn.Open();
adp.Fill(ds);
dt = ds.Tables[0];
gridStudents.DataSource = dt;
cn.Close();
}
private void btnCalculateResults_Click(object sender, EventArgs e)
{
string cnstr = "data source=.;user id=sa;password=123;initial catalog=demo";
SqlConnection cn = new SqlConnection(cnstr);
SqlCommand cmd = new SqlCommand();
cmd.Connection = cn;
cmd.CommandText = "calculateresults";
cmd.CommandType = CommandType.StoredProcedure;
cn.Open();
cmd.ExecuteNonQuery();
MessageBox.Show("Results Calculated!");
cn.Close();
}

Oracle Code

using System.Data.OleDb;
private void btnDisplayStudents_Click(object sender, EventArgs e)
{
string cnstr = "provider=oraoledb.oracle.1;data source=.;user id=scott;password=tiger";
OleDbConnection cn = new OleDbConnection(cnstr);
string sqlstr = "select * from students";
OleDbDataAdapter adp = new OleDbDataAdapter(sqlstr, cn);
DataSet ds = new DataSet();
DataTable dt;
cn.Open();
adp.Fill(ds);
dt = ds.Tables[0];
gridStudents.DataSource = dt;
cn.Close();
}
private void btnCalculateResults_Click(object sender, EventArgs e)
{
string cnstr = "provider=oraoledb.oracle.1;data source=.;user id=scott;password=tiger";
OleDbConnection cn = new OleDbConnection(cnstr);
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = cn;
cmd.CommandText = "calculateresults";
cmd.CommandType = CommandType.StoredProcedure;
cn.Open();
cmd.ExecuteNonQuery();
MessageBox.Show("Results Calculated!");
cn.Close();
}

Demo on Stored Function Calling

Demo on Stored Function Calling

Create the following function at Sql Server in “demo” database.

create function getmaximum(@a numeric,@b numeric,@c numeric) returns numeric
as begin
declare @big numeric
if @a>@b and @a>@c
set @big=@a
else if @b>@a and @b>@c
set @big=@b
else
set @big=@c
return @big
end

Create the following function at Oracle.

create function getmaximum(a number,b number,c number) return number is
big number;
begin
if a>b and a>c then
big:=a;
elsif b>a and b>c then
big:=b;
else
big:=c;
end if;
return big;
end;

Sql Server Code

using System.Data.SqlClient;
private void btnGetMaximum_Click(object sender, EventArgs e)
{
string cnstr = "data source=.;user id=sa;password=123;initial catalog=demo";
SqlConnection cn = new SqlConnection(cnstr);
SqlCommand cmd = new SqlCommand();
cmd.Connection = cn;
cmd.CommandText = "getmaximum";
cmd.CommandType = CommandType.StoredProcedure;
int a = Convert.ToInt32(txtFirstValue.Text);
int b = Convert.ToInt32(txtSecondValue.Text);
int c = Convert.ToInt32(txtThirdValue.Text);
SqlParameter p1 = new SqlParameter("@a", a);
SqlParameter p2 = new SqlParameter("@b", b);
SqlParameter p3 = new SqlParameter("@c", c);
SqlParameter p4 = new SqlParameter();
p4.Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add(p4);
cmd.Parameters.Add(p1);
cmd.Parameters.Add(p2);
cmd.Parameters.Add(p3);
cn.Open();
cmd.ExecuteNonQuery();
MessageBox.Show("Function Executed Successfully!");
txtResult.Text = Convert.ToString(p4.Value);
cn.Close();
}

Oracle Code

using System.Data.OleDb;
private void btnGetMaximum_Click(object sender, EventArgs e)
{
string cnstr = "provider=oraoledb.oracle.1;data source=.;user id=scott;password=tiger";
OleDbConnection cn = new OleDbConnection(cnstr);
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = cn;
cmd.CommandText = "getmaximum";
cmd.CommandType = CommandType.StoredProcedure;
int a = Convert.ToInt32(txtFirstValue.Text);
int b = Convert.ToInt32(txtSecondValue.Text);
int c = Convert.ToInt32(txtThirdValue.Text);
OleDbParameter p1 = new OleDbParameter("a", a);
OleDbParameter p2 = new OleDbParameter("b", b);
OleDbParameter p3 = new OleDbParameter("c", c);
OleDbParameter p4 = new OleDbParameter();
p4.Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add(p4);
cmd.Parameters.Add(p1);
cmd.Parameters.Add(p2);
cmd.Parameters.Add(p3);
cn.Open();
cmd.ExecuteNonQuery();
MessageBox.Show("Function Executed Successfully!");
txtResult.Text = Convert.ToString(p4.Value);
cn.Close();
}