CODEWITHSUNDEEP
c#asp.netstored-proceduresplsql
Parkavi
Parkavi

Reputation: 157

Executing Stored Procedure using C#

I have a procedure named as Get_Added_Request_ID and using this procedure I need to get returned the Request_ID value (VARCHAR2). I have referrd plenty of docs released by Oracle and Microsoft but still I could not find a good solution may be because I am a new learner for Oracle and ASp.NET. Please someone help me in this issue. Thanks in advance enter image description here

-- Parameter Type Mode Default? -- ATTR_ VARCHAR2 IN
-- REQUEST_ID VARCHAR2 OUT

Upvotes: 0

Views: 87

Answers (2)

Surenthar Pitchai
Surenthar Pitchai

Reputation: 1319

Please view this link for the documentation on how to connect to oracle by using oracle data provider

You should use oracle data provider from this link for connecting oracle.

the example code for your scenario will be

Using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OracleClient;
using System.Data;

namespace CallingOracleStoredProc
{
    class Program
    {
        static void Main(string[] args)
        {
            using (OracleConnection objConn = new OracleConnection("Data Source=*your datasource*; User ID=*Your UserID*; Password=*Your Password*"))
            {
                OracleCommand objCmd = new OracleCommand();
                objCmd.Connection = objConn;
                objCmd.CommandText = "Get_Added_Request_ID";
                objCmd.CommandType = CommandType.StoredProcedure;
                objCmd.Parameters.Add("ATTR_", OracleType.NVarChar).Value = "test";
                objCmd.Parameters.Add("REQUEST_ID", OracleType.NVarChar).Direction = ParameterDirection.Output;

                try
                {
                    objConn.Open();
                    objCmd.ExecuteNonQuery();
                    System.Console.WriteLine("The Request ID is {0}", objCmd.Parameters["REQUEST_ID"].Value);
                }
                catch (Exception ex)
                {
                    System.Console.WriteLine("Exception: {0}",ex.ToString());
                }

                objConn.Close();

            }
        }
    }

}

Upvotes: 1

Sandeep Maurya
Sandeep Maurya

Reputation: 91

use execute scalar which return 1 row - 1 column value & store it in appropriate variable For e.g :- SqlHelper.ExecuteScalar(_connectionString, CommandType.StoredProcedure, "Get_Added_Request_ID", null);

note :- ExecuteScalar is came from nuget Microsoft.Application Block

Upvotes: 0

Related Questions