How does one call a stored procedure in oracle from C#?
This question is related to
c#
.net
oracle
stored-procedures
ado.net
Connecting to Oracle is ugly. Here is some cleaner code with a using statement. A lot of the other samples don't call the IDisposable Methods on the objects they create.
using (OracleConnection connection = new OracleConnection("ConnectionString"))
using (OracleCommand command = new OracleCommand("ProcName", connection))
{
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("ParameterName", OracleDbType.Varchar2).Value = "Your Data Here";
command.Parameters.Add("SomeOutVar", OracleDbType.Varchar2, 120);
command.Parameters["return_out"].Direction = ParameterDirection.Output;
command.Parameters.Add("SomeOutVar1", OracleDbType.Varchar2, 120);
command.Parameters["return_out2"].Direction = ParameterDirection.Output;
connection.Open();
command.ExecuteNonQuery();
string SomeOutVar = command.Parameters["SomeOutVar"].Value.ToString();
string SomeOutVar1 = command.Parameters["SomeOutVar1"].Value.ToString();
}
It's basically the same mechanism as for a non query command with:
CommandType.StoredProcedure
There are plenty of examples out there, the first one returned by Google is this one
There's also a little trap you might fall into, if your SP is a function, your return value parameter must be first in the parameters collection
This Code works well for me calling oracle stored procedure
Add references by right clicking on your project name in solution explorer >Add Reference >.Net then Add namespaces.
using System.Data.OracleClient;
using System.Data;
then paste this code in event Handler
string str = "User ID=username;Password=password;Data Source=Test";
OracleConnection conn = new OracleConnection(str);
OracleCommand cmd = new OracleCommand("stored_procedure_name", conn);
cmd.CommandType = CommandType.StoredProcedure;
--Ad parameter list--
cmd.Parameters.Add("parameter_name", "varchar2").Value = value;
....
conn.Open();
cmd.ExecuteNonQuery();
And its Done...Happy Coding with C#
Instead of
cmd = new OracleCommand("ProcName", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("ParName", OracleDbType.Varchar2, ParameterDirection.Input).Value = "foo";
You can also use this syntax:
cmd = new OracleCommand("BEGIN ProcName(:p0); END;", con);
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add("ParName", OracleDbType.Varchar2, ParameterDirection.Input).Value = "foo";
Note, if you set cmd.BindByName = False
(which is the default) then you have to add the parameters in the same order as they are written in your command string, the actual names are not relevant. For cmd.BindByName = True
the parameter names have to match, the order does not matter.
In case of a function call the command string would be like this:
cmd = new OracleCommand("BEGIN :ret := ProcName(:ParName); END;", con);
cmd.CommandType = CommandType.Text;
cmd.Parameters.Add("ret", OracleDbType.RefCursor, ParameterDirection.ReturnValue);
cmd.Parameters.Add("ParName", OracleDbType.Varchar2, ParameterDirection.Input).Value = "foo";
// cmd.ExecuteNonQuery(); is not needed, otherwise the function is executed twice!
var da = new OracleDataAdapter(cmd);
da.Fill(dt);
In .Net through version 4 this can be done the same way as for SQL Server Stored Procs but note that you need:
using System.Data.OracleClient;
There are some system requirements here that you should verify are OK in your scenario.
Microsoft is deprecating this namespace as of .Net 4 so third-party providers will be needed in the future. With this in mind, you may be better off using Oracle Data Provider for .Net (ODP.NET) from the word go - this has optimizations that are not in the Microsoft classes. There are other third-party options, but Oracle has a strong vested interest in keeping .Net developers on board so theirs should be good.
I have now got the steps needed to call procedure from C#
//GIVE PROCEDURE NAME
cmd = new OracleCommand("PROCEDURE_NAME", con);
cmd.CommandType = CommandType.StoredProcedure;
//ASSIGN PARAMETERS TO BE PASSED
cmd.Parameters.Add("PARAM1",OracleDbType.Varchar2).Value = VAL1;
cmd.Parameters.Add("PARAM2",OracleDbType.Varchar2).Value = VAL2;
//THIS PARAMETER MAY BE USED TO RETURN RESULT OF PROCEDURE CALL
cmd.Parameters.Add("vSUCCESS", OracleDbType.Varchar2, 1);
cmd.Parameters["vSUCCESS"].Direction = ParameterDirection.Output;
//USE THIS PARAMETER CASE CURSOR IS RETURNED FROM PROCEDURE
cmd.Parameters.Add("vCHASSIS_RESULT",OracleDbType.RefCursor,ParameterDirection.InputOutput);
//CALL PROCEDURE
con.Open();
OracleDataAdapter da = new OracleDataAdapter(cmd);
cmd.ExecuteNonQuery();
//RETURN VALUE
if (cmd.Parameters["vSUCCESS"].Value.ToString().Equals("T"))
{
//YOUR CODE
}
//OR
//IN CASE CURSOR IS TO BE USED, STORE IT IN DATATABLE
con.Open();
OracleDataAdapter da = new OracleDataAdapter(cmd);
da.Fill(dt);
Hope this helps
Source: Stackoverflow.com