How to call user defined function in Asp.net C# using ODP.NET

Question

0.00/5 (No votes)

See more:

Guide me How I call following function in Asp.net c#

SQL

create or replace FUNCTION AuthenticateUser(UserName IN VARCHAR2,Password IN VARCHAR2)
   RETURN NUMBER
   IS
   Counts NUMBER;
   rval  Number;
   BEGIN
      SELECT COUNT(USERNAMES)
      INTO Counts
      FROM tblUsers WHERE USERNAMES = UserName AND PASSWORDS = Password;
       IF Counts = 1 THEN
      rval:=1;
    ELSE
      rval:=-1;
    END IF;
      RETURN(rval);
    END;

I am using followin asp.net function to User Defined Funciton

C#

public int AunthicateUser(string username, string password)
        {
            using (OracleConnection con = GetConnection())
            {
                OracleCommand cmd = new OracleCommand("SELECT * FROM AuthenticateUser(@UserName, @Password)", con);

                cmd.Parameters.Add("@UserName", "username");
                cmd.Parameters.Add("@Password", "password");
               

             
                con.Open();
                int result = (int)cmd.ExecuteNonQuery();

                return result;
            }
        }

But I got Following error guide me.How can I solve this problem.

Oracle.DataAccess.Client.OracleException ORA-00933: SQL command not properly ended at Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure, Boolean bCheck) at Oracle.DataAccess.Client.OracleException.HandleError(Int32 errCode, OracleConnection conn, String procedure, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, Boolean bCheck) at Oracle.DataAccess.Client.OracleCommand.ExecuteNonQuery() at SchoolsManagementSystem.DAL.AunthicateUser(String username, String password) in d:\c#\SchoolsManagementSystem\SchoolsManagementSystem\App_Code\DAL.cs:line 40 at SchoolsManagementSystem.Login.btnLogin_Click(Object sender, EventArgs e) in d:\c#\SchoolsManagementSystem\SchoolsManagementSystem\Login.aspx.cs:line 45
Login

Posted 29-Jun-14 4:04am

Sheikh Waqas Ali

Add a Solution

Comments

[no name] 29-Jun-14 10:12am

http://www.google.com/search?q=c%23+call+function+oracle

1 solution

Add a Solution

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

DamithSL · Answer 1 · 2014-06-29T04:55:00

Solution 1

SQL

create or replace FUNCTION AuthenticateUser(UserName IN VARCHAR2,Password IN VARCHAR2)
   RETURN NUMBER
   IS
   Counts NUMBER;
   BEGIN
      SELECT COUNT(USERNAMES)
      INTO Counts
      FROM tblUsers WHERE USERNAMES = UserName AND PASSWORDS = Password;
       IF Counts = 1 THEN
      Counts:=1;
    ELSE
      Counts:=-1;
    END IF;
      RETURN(Counts);
    END;

try like below

C#

OracleCommand cmd = new OracleCommand("AuthenticateUser",con);
cmd.CommandType = CommandType.StoredProcedure; 
cmd.Parameters.Add("@UserName", "username");
cmd.Parameters.Add("@Password", "password");
var ret = cmd.Parameters.Add( "Counts", OracleDbType.Decimal );
ret.Direction = ParameterDirection.ReturnValue;
con.Open();
cmd.ExecuteNonQuery();
var count = ret.Value;

Posted 29-Jun-14 4:55am

DamithSL

Updated 29-Jun-14 16:21pm

v2

Comments

Sheikh Waqas Ali 29-Jun-14 13:43pm

Not working

public int AunthicateUser(string username, string password)
{
using (OracleConnection con = GetConnection())
{
// OracleCommand cmd = new OracleCommand("SELECT * FROM AuthenticateUser(@UserName, @Password)", con);

// cmd.Parameters.Add("@UserName", "username");
// cmd.Parameters.Add("@Password", "password");

// OracleDataAdapter da = new OracleDataAdapter(cmd);

// DataTable dt = new DataTable();
//da.Fill(dt);

//string str = dt.Rows[0][0].ToString();
// Response.Write(str.ToString());

OracleCommand cmd = new OracleCommand("AuthenticateUser", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@UserName", username);
cmd.Parameters.Add("@Password", password);

con.Open();
int result = (int)cmd.ExecuteNonQuery();

return result;
}
}

Sheikh Waqas Ali 29-Jun-14 13:43pm

Oracle.DataAccess.Client.OracleException ORA-06550: line 1, column 7: PLS-00221: 'AUTHENTICATEUSER' is not a procedure or is undefined ORA-06550: line 1, column 7: PL/SQL: Statement ignored at Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure, Boolean bCheck) at Oracle.DataAccess.Client.OracleException.HandleError(Int32 errCode, OracleConnection conn, String procedure, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, Boolean bCheck) at Oracle.DataAccess.Client.OracleCommand.ExecuteNonQuery() at SchoolsManagementSystem.DAL.AunthicateUser(String username, String password) in d:\c#\SchoolsManagementSystem\SchoolsManagementSystem\App_Code\DAL.cs:line 48 at SchoolsManagementSystem.Login.btnLogin_Click(Object sender, EventArgs e) in d:\c#\SchoolsManagementSystem\SchoolsManagementSystem\Login.aspx.cs:line 45

Above error show

How to call user defined function in Asp.net C# using ODP.NET

1 solution

Solution 1

Add your solution here

Preview 0

How to call user defined function in Asp.net C# using ODP.NET

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us