Call Oracle from ADO.NET: C#, OCI: Call the Procedure count_emp_by_dept from .NET: Listing 1
First, establish a connection to the server by creating an instance of OracleConnection (objConn), set up the OracleCommand instance (objCmd), and then invoke its ExecuteNonQuery method in order to call the stored procedure.
using System.Text;
using System.Data.OracleClient;
using System.Data;
namespace CallingOracleStoredProc
{
class Program
{
static void CallProcedure()
{
using (OracleConnection objConn = new
OracleConnection("Data Source=ORCL; User
ID=scott; Password=tiger"))
{
OracleCommand objCmd = new
OracleCommand();
objCmd.Connection = objConn;
objCmd.CommandText = "count_emp_by_dept";
objCmd.CommandType =
CommandType.StoredProcedure;
objCmd.Parameters.Add("pin_deptno",
OracleType.Number).Value = 20;
objCmd.Parameters.Add("pout_count",
OracleType.Number).Direction =
ParameterDirection.Output;
try
{
objConn.Open();
objCmd.ExecuteNonQuery();
System.Console.WriteLine(
"Number of employees in department 20 is {0}",
objCmd.Parameters["pout_count"].Value);
}
catch (Exception ex)
{
System.Console.WriteLine(
"Exception: {0}", ex.ToString());
}
objConn.Close();
}
}
static void Main(string[] args)
{
CallProcedure();
}
}
}