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

Many Confusions while calling procedure from your asp.net. 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
begin
param2 := param1;
end;


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;" +
@"Pwd=Password;";

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

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
}
finally
{
cxn.Close();
cxn.Dispose();
}
}
}

Leave a Reply

Your email address will not be published.