Execute An Oracle Stored Procedure With Parameters (C#)

So you want to execute an Oracle stored procedure with parameters, huh? For this example I have an Oracle stored procedure called MEMBER_TYPE_UPDATE that will update what type of membership I have based on the numeric value. This sort of snippet can be used in a web application directly or called by some form of web service.

Here are some example values:

0 = Not a member

1 = Member

2 = Member with first tier privileges

3 = Member with highest level privileges

The update occurs based on their username and based on that username will attempt to update the numeric value. So below we will be calling a method (passing the two parameters to update with). I will then gather the connection string to Oracle and begin to execute my Oracle stored procedure (while giving it the values passed into the method to be used in the stored procedure).

Here’s my snippet:

using System;
using System.Data;
using System.Web.Services;
using Oracle.DataAccess.Client;
using System.Configuration;

public string SetUserMembership(string membershipNetworkUserName, int membershipStatusValue)
{
string errorString = string.Empty;
OracleCommand cmd = null;
try
{

string connectionString = string.Empty;
if (ConfigurationManager.AppSettings["location"].Contains("PROD"))
{
connectionString = ConfigurationManager.ConnectionStrings["ConnectionStringPROD"].ConnectionString;
}
else
{
connectionString = ConfigurationManager.ConnectionStrings["ConnectionStringDEV"].ConnectionString;
}

cmd = new OracleCommand();
cmd.Connection = new OracleConnection(connectionString);
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "ORACLE_USER.MEMBER_TYPE_UPDATE";
cmd.Parameters.Add("in_employeeUserName", OracleDbType.Varchar2).Value = membershipNetworkUserName;
cmd.Parameters.Add("in_status_id", OracleDbType.Decimal).Value = membershipStatusValue;
cmd.Parameters.Add("O_RETURN_STATUS", OracleDbType.Varchar2, 4000).Direction = ParameterDirection.Output;
cmd.Connection.Open();
cmd.ExecuteNonQuery();

string returnString = cmd.Parameters["O_RETURN_STATUS"].Value.ToString();
if (!returnString.Contains("SUCCESS"))
{
// obviously there was an issue and we want to display this somewhere
errorString = returnString;
}
}

For a specific call I would call it with SetUserMembership(“username”, “2”);

Ideally you would have variables there. Hope this helps people to see the parameters in use.