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

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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