Home / Questions / 528310
Accepted
Daniel Rivas
Asked: 2022-06-17 19:02:33 +0800 CST 2022-06-17 19:02:33 +0800 CST

ASP.NET login using Oracle PL/SQL and C#

  • 772

I have a small login with two text fields (email and document number), which when I hit the "enter" button I want to be validated in my stored procedure, but it shows me an exception error:

System.NullReferenceException: Referencia a objeto no establecida como instancia de un objeto, error en la linea 31.

Línea 29:             com.Parameters.Add("id_usu_numerodocumento", Convert.ToInt32(numeroDocumento.Text.Trim()));
Línea 30:             com.Connection = conexion;
Línea 31:             int result = (Int32)com.ExecuteScalar();
Línea 32: 
Línea 33:             if (result == 1)

My c# code is as follows:

string con = ConfigurationManager.ConnectionStrings["connectOrcl"].ConnectionString;
            //conexión a la base de datos
            OracleConnection conexion = new OracleConnection(con);
            conexion.Open();
            OracleCommand com = new OracleCommand("INGRESO_USUARIO", conexion);
            com.CommandType = System.Data.CommandType.StoredProcedure;
            com.Parameters.Add("usu_email", correoElectronico.Text.Trim());
            com.Parameters.Add("id_usu_numerodocumento", Convert.ToInt32(numeroDocumento.Text.Trim()));
            com.Connection = conexion;
            int result = (Int32)com.ExecuteScalar();

            if (result == 1)
            {
                Response.Redirect("admin/dashboard.aspx");
            }
            if (result == 0)
            {
                Response.Redirect("ingreso.aspx");
            }
            conexion.Close();

And my procedure:

CREATE OR REPLACE PROCEDURE INGRESO_USUARIO
(
    usu_email varchar,
    id_usu_numerodocumento int
)
AS
    usuNumeroDoc INT;
BEGIN
    SELECT COUNT(*) INTO usuNumeroDoc  FROM LA_USUARIO WHERE usu_email = usu_email AND id_usu_numerodocumento = id_usu_numerodocumento;
END;

If the COUT is equal to 1, it passes, if it is equal to 0, it does not pass.

Thanks for your help!

c#

2 Answers

  1. You can read this tutorial.

    https://www.c-sharpcorner.com/article/calling-oracle-stored-procedures-from-microsoft-net/

    You have to define at least one parameter as OUT in your stored procedure or you can create a function.

    Greetings.

    • 1
  2. Best Answer

    I managed to do it by sending an OUT parameter of type RefCursor in the Oracle procedure:

    --PROCEDIMIENTO PARA INGRESAR AL SISTEMA SEGÚN CORREO Y NUMERO DE DOCUMENTO
CREATE OR REPLACE PROCEDURE INGRESAR_SISTEMA
(
    email VARCHAR,
    numeroDoc INT,
    registros out sys_refcursor
)
AS
BEGIN
     OPEN registros FOR SELECT COUNT(*) FROM LA_USUARIO WHERE USU_EMAIL = email AND ID_USU_NUMERODOCUMENTO = numeroDoc;
END;

    And in C# I took the value of that position and stored it in a variable to compare it:

    string valor = ds.Rows[0]["Count(*)"].ToString();
    • 1