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();
}
}
}