Click here to Skip to main content
16,004,686 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi,
I want to insert a data in the table Employees(Empid,first name,lastname,salary) with the help of stored procedure.
I want this stored proc called on button click from asp.net code behind.
I am using oracle database.
How can i go about in creating the stored proc and call it from my code behind.
Thanks
-Akshay
Posted

 
Share this answer
 
Comments
aks.shan 14-Aug-13 2:16am    
Thanks syed.
syed shanu 14-Aug-13 2:18am    
If those links are helpful to you mark it as Answered.
create package in oracle
create procedure in that package


CREATE OR REPLACE PACKAGE BODY SCHEMA.TEST_PKG
AS
PROCEDURE TEST_PROC( P_EMP_ID IN VARCHAR2,P_FISRT_NAME IN VARCHAR2,P_LAST_NAME IN VARCHAR2,P_SALARY IN NUMBER, P_TRANSACTION_STATUS OUT VARCHAR2)
AS
BEGIN
INSERT INTO EMPLOYEE(EMP_ID,FISRT_NAME,LAST_NAME,SALARY)
VALUES (P_EMP_ID ,P_FISRT_NAME,P_LAST_NAME,P_SALARY);
P_TRANSACTION_STATUS := ‘SUCCESS’;

END;

In web code use microsoft enterprise libarry as ADO.net connectivity
and call above procedure from code behind


C#
public string insert_data(string id, string name, string last_name, int salary)
    {
        string transactionstatus = string.Empty;
        if (db != null)
        {
            cmd = (OracleCommand)db.GetStoredProcCommand("TEST_PKG.TEST_PROC");
            cmd.Parameters.Add("P_EMP-ID", OracleType.VarChar, 2000).Value = id;
            cmd.Parameters.Add("P_FIRST_NAME",OracleType.VarChar, 2000).Value = name;
            cmd.Parameters.Add("P_LAST_NAME", OracleType.VarChar, 2000).Value = last_name;
            cmd.Parameters.Add("P_SALARY", OracleType.number, 2000).Value = salary;
            cmd.Parameters.Add("P_TRANSACTION_STATUS", OracleType.VarChar, 2000).Direction = ParameterDirection.Output;
            int i = db.ExecuteNonQuery(cmd);
            if (i <= 0)
            {
                transactionstatus = "FAIL";
            }
            return transactionstatus = cmd.Parameters["P_TRANSACTION_STATUS"].Value.ToString();
        }
        return transactionstatus;
    }
 
Share this answer
 
Comments
aks.shan 14-Aug-13 6:17am    
Thanks Manoj.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900