Steps to Call Stored Procedure with Parameters from C# .Net

Many Confusions while calling procedure from your Generally it’s because less practice of using oracle with .Net code.

Following is the example. Hope it’ll be useful to you.

Procedure :

create or replace
procedure ProcTest(param1 in varchar, param2 in out varchar2) is
param2 := param1;

C# Code

using System;
using System.Data;
using System.Data.Odbc;

public class SPTest
const string connectionString =
@"Driver={Microsoft ODBC for Oracle};" +
@"Server=TNS;" +
@"Uid=Schema;" +

public static string FuncTestSP()
var cxn = new OdbcConnection(connectionString);

OdbcCommand cmd = new OdbcCommand("{ CALL ProcTest(?, ?) }");
cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add("param1", OdbcType.VarChar).Value = "First Paramemeter";

var param2 = cmd.Parameters.Add("param2", OdbcType.VarChar, 50);
param2.Direction = ParameterDirection.InputOutput;
param2.Value = "Second Parameter";

cmd.Connection = cxn; //setting connection string
cxn.Open(); // opening connection
cxn.ExecuteNonQuery(); //executing query
cxn.Close(); // closing connection

return param2.Value as string; // your return value
catch(Exception ex)
// if any exception you are handling

Leave a Reply

Your email address will not be published.