如何使用ODP.NET在Asp.net C＃中调用用户定义的函数 [英] How to call user defined function in Asp.net C# using ODP.NET

问题描述

指导我如何在Asp.net中调用以下函数c＃

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

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;

我正在使用followin asp.net函数来定义用户函数

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

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

但我得到以下错误指导我。我怎么能解决这个问题。



Oracle.DataAccess.Client.OracleException ORA-00933：SQL命令未在Oracle.DataAccess.Client.OracleException.HandleError（Int32 errCode）上的Oracle.DataAccess.Client.OracleException.HandleErrorHelper（Int32 errCode，OracleConnection conn，IntPtr opsErrCtx，OpoSqlValCtx * pOpoSqlValCtx，Object src，String procedure，Boolean bCheck）中正确结束，OracleConnection conn，字符串过程，IntPtr opsErrCtx，OpoSqlValCtx * pOpoSqlValCtx，Object src，Boolean bCheck），位于D：\的SchoolsManagementSystem.DAL.AunthicateUser（字符串用户名，字符串密码）中的Oracle.DataAccess.Client.OracleCommand.ExecuteNonQuery（） c＃\\\ SchoolsManagementSystem \SchoolsManagementSystem \ App_Code \DAL.cs：40位于SchoolsManagementSystem.Login.btnLogin_Click（对象发件人，EventArgs e），位于d：\c＃\ SchoolsM anagementSystem\SchoolsManagementSystem\Login.aspx.cs:line 45

登录

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

推荐答案

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

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;

这篇关于如何使用ODP.NET在Asp.net C＃中调用用户定义的函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！