ASP.NET User Validation

Question

I get confused in my code, I need to validate the UserName and Password with ASP.Net and a stored procedure but all time I get -1 so how can I solve this problem?

Here's my project:

Carpet Business:

MngUser.cs:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Data.Models;

namespace Business
{
    public class MngUser
    {
        private string mSqlCnn;

        public MngUser(string pSqlCnn)
        {
            mSqlCnn = pSqlCnn;
        }

        public ObjRespuesta AccountLogin(User MyUser)
        {
           ObjRespuesta resp = new ObjRespuesta();

           try
           {
               if (string.IsNullOrEmpty(MyUser.UserName) && string.IsNullOrEmpty(MyUser.Password))
               {
                    resp.IsError = true;
                    resp.NumError = 200;
                    resp.Mensaje = "Usuario y Contraseña Inválida";
                    return resp;
               }

               Data.Interfaces.IUser mngUser = new Data.UserEntity(mSqlCnn);
               int LogResult = mngUser.Entrar(MyUser);

               if (LogResult > 0)
               {
                   resp.IsError = false;
                   resp.NumError = 0;
                   resp.Mensaje = "Error al conectar a la base de datos";
                   return resp;
               }
               else if (LogResult == -1)
               {
                    resp.IsError = true;
                    resp.NumError = 201;
                    resp.Mensaje = "OK";
                    return resp;
               }
           }
           catch (Exception ex)
           {
               resp.IsError = true;
               resp.NumError = 400;
               resp.Mensaje = ex.Message;
           }

           return resp;
       }
    }
}

Carpet Business

ObjRespuesta.cs:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Business
{
    public class ObjRespuesta
    {
        public bool IsError { get; set; }
        public int NumError { get; set; }
        public string Mensaje { get; set; }
    }
}

Carpet Data:

IUser.cs:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Data.Models;

namespace Data.Interfaces
{    
    public interface IUser
    {
        int Create(User user);

        int Entrar(User user);
    }
}

Models: User.cs:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Data.Models
{
    public class User
    {
        public string UserName { get; set; }
        public string Password { get; set; }
    }
}

Carpet Business UserEntity.cs:

using System; 
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Data.Interfaces;
using Data.Models;
using System.Data.SqlClient;
using System.Data;
using System.Web;

namespace Data
{
    public class UserEntity:IUser, IDisposable
    {
        private string mSqlCnn;
        private SqlCommand mSqlCommand;
        private SqlConnection mSqlConnection;
        public UserEntity(string SqlCnn)
        {
            mSqlCnn = SqlCnn;
        }

        private bool CreateConnection()
        {
            try
            {
                mSqlConnection = new SqlConnection(mSqlCnn);
                mSqlConnection.Open();
                return true;
            }
            catch (Exception ex)
            { throw ex; }
        }

        public void Dispose()
        {
             if (mSqlConnection != null)
             {
                 if (mSqlConnection.State == System.Data.ConnectionState.Open)
                 {
                     mSqlConnection.Close();
                     mSqlConnection = null;
                 }
            }

            if (mSqlCommand != null)
            {
                 mSqlCommand = null;
            }
        }

        public int Entrar(User pUser)
        {
            int logresult = 0;

            try
            {
                if (CreateConnection())
                {
                    if (mSqlConnection != null && mSqlConnection.State != ConnectionState.Open)
                    {
                         mSqlConnection.Open();
                    }

                    mSqlCommand = new SqlCommand("OpMngSys.USP_CostumerLogin");
                    mSqlCommand.Connection = mSqlConnection;
                    mSqlCommand.CommandType = System.Data.CommandType.StoredProcedure;

                    mSqlCommand.Parameters.Add("@UserName", SqlDbType.VarChar).Value = pUser.UserName;
                    mSqlCommand.Parameters.Add("@Password", SqlDbType.VarChar).Value = pUser.Password;

                    logresult = mSqlCommand.ExecuteNonQuery();
                }

                return logresult;
            }
            catch (Exception ex)
            { throw ex; }
        }

        public int Create(User pUser)
        {
            return 0;
        }

        //public int Delete(User pUser)
        //{
        //    return 0;
        //}
    }
}

The ASP.Net Page:

<%@ Page Title="" Language="C#" MasterPageFile="~/Public/Site.Master" AutoEventWireup="true" CodeBehind="Usuario.aspx.cs" Inherits="Demo_CSP.Usuario" %>
<asp:Content ID="Content1" ContentPlaceHolderID="head" runat="server">
    <meta charset="utf-8" />
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" runat="server">
    <asp:ScriptManager ID="ScriptManager2" runat="server" ></asp:ScriptManager>
    <asp:TextBox ID="TextUserNameLog" runat="server"></asp:TextBox>
    <asp:RequiredFieldValidator ID="UserNameValid" ErrorMessage="Invalid" runat="server"  ForeColor="Red" ControlToValidate="TextUserNameLog">*</asp:RequiredFieldValidator>
    <asp:TextBox ID="TextPasswordLog" runat="server" TextMode="Password"></asp:TextBox>
    <asp:Button ID ="BtnLog" runat="server" Text="Ingresar" OnClick="BtnLog_Click" />

    <asp:RequiredFieldValidator ID="PasswordValid" ErrorMessage="Invalid" runat="server" ForeColor="Red" ControlToValidate="TextPasswordLog">*</asp:RequiredFieldValidator>
    <asp:Login ID="LogBot" LoginButtonText="Ingresar" runat="server" OnAuthenticate="LogBot_Authenticate"></asp:Login>
</asp:Content>

The Usuario.aspx.cs code-behind:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Configuration;

namespace Demo_CSP
{
    public partial class Usuario : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
        }

        protected void LogBot_Authenticate(object sender, AuthenticateEventArgs e)
        {
            Data.Models.User MyUser = new Data.Models.User();

            //MyUser.UserName = TextUserNameLog.Text;
            //MyUser.Password = TextPasswordLog.Text;

            MyUser.UserName = LogBot.UserName;
            MyUser.Password = LogBot.Password;

            Business.MngUser objuser = new     Business.MngUser(ConfigurationManager.AppSettings["SqlCnn"].ToString());
            Business.ObjRespuesta resp = objuser.AccountLogin(MyUser);
            int Id = 0;

            switch (Id)
            {
                case -1:
                   LogBot.FailureText = "Error de Usuario/Contraseña";
                   break;

                case -2:
                   LogBot.FailureText = "No existe Usuario";
                   break;

                default:
                   string message = "Prueba exitosa";
                   System.Text.StringBuilder sb = new System.Text.StringBuilder();
                   sb.Append("alert('");
                   sb.Append(message);
                   sb.Append("');");
                   ClientScript.RegisterOnSubmitStatement(this.GetType(), "alert", sb.ToString());
                   break;
            }    
        }

        protected void BtnLog_Click(object sender, EventArgs e)
        {
            Data.Models.User MyUser = new Data.Models.User();

            MyUser.UserName = TextUserNameLog.Text;
            MyUser.Password = TextPasswordLog.Text;

            Business.MngUser objuser = new Business.MngUser(ConfigurationManager.AppSettings["SqlCnn"].ToString());
            Business.ObjRespuesta resp = objuser.AccountLogin(MyUser);

            //MyUser.UserName = TextUserNameLog.Text;
            //MyUser.Password = TextPasswordLog.Text;
        }
    }
}

Here is the stored procedure:

IF EXISTS(SELECT 1 FROM sys.objects WHERE name = 'USP_CustomerLogin' AND type = 'P')
BEGIN
    DROP PROCEDURE OpMngSys.USP_Customer_NewCustomer
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE OpMngSys.USP_CostumerLogin
    @UserName VARCHAR(100),
    @Password VARCHAR(150),
    @IsExists INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    IF EXISTS (SELECT UserName, [Password] 
               FROM [Security].[Users] 
               WHERE UserName = @UserName and [Password] = @Password)
    BEGIN
        SET @IsExists = -1
    END
    ELSE 
    BEGIN
        SET @IsExists = 1
    END

    RETURN @IsExists
END
GO

I get seriously frustrated really... I need this validation for my template...

.ExecuteNonQuery() is intended for queries that do not return any data (like INSERT, DELETE etc.). The return value represents the number of rows affected (by the INSERT etc.). You on the other hand should return a value - and you should return it as a result set from your stored procedure (using SELECT @IsExists) and then you should call that procedure using .ExecuteScalar() which works if you return only a single row, single column - which is exactly what you would do here! — marc_s
– marc_s, Commented Nov 26, 2015 at 21:33
Nope... it not works really... i tried with .ExecureScalar() and do not return anything... — AnubisVolga
– AnubisVolga, Commented Nov 26, 2015 at 21:50

Antonio González · Accepted Answer · 2015-11-27 15:29:16Z

I see 2 things.

First the StoredProcedure is going to give you reversed results, this is, 1 (Valid) for Incorrect credentials and -1 (Invalid). This is because the IF-ELSE logic is not correct. I would change the results -1 by 1 and viceversa.

The full Transact-SQL Script for the StoredProcedure would be like:

IF EXISTS(SELECT 1 FROM sys.objects WHERE name = 'USP_CustomerLogin' AND type = 'P')
BEGIN
    DROP PROCEDURE OpMngSys.USP_Customer_NewCustomer
END
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE OpMngSys.USP_CostumerLogin
    @UserName VARCHAR(100),
    @Password VARCHAR(150),
    @IsExists INT OUTPUT
AS
BEGIN
    SET NOCOUNT ON;

    IF EXISTS (SELECT UserName, [Password] 
               FROM [Security].[Users] 
               WHERE UserName = @UserName and [Password] = @Password)
    BEGIN
        SET @IsExists = 1
    END
    ELSE 
    BEGIN
        SET @IsExists = -1
    END

    RETURN @IsExists
END
GO

Second I would say that Donal is right. You're not storing the output declared on the StoredProcedure, and that's a necessary step.

Imho the CreateConnection() function and the Dispose() logic in the UserEntity.cs are not necessary since the SqlConnection is already IDisposable

The full UserEntity.cs would see like this:

using System; 
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Data.Interfaces;
using Data.Models;
using System.Data.SqlClient;
using System.Data;
using System.Web;

namespace Data
{
    public class UserEntity:IUser, IDisposable
    {
        private string mSqlCnn;

        public UserEntity(string SqlCnn)
        {
            mSqlCnn = SqlCnn;
        }

        public void Dispose()
        {
            mSqlCnn = null;
        }

        public int Entrar(User pUser)
        {
            int logresult = 0;

            //SqlConnection is IDisposable, and it occupies from closing connection.
            using(SqlCOnnection mSqlConnection = new SqlConnection(mSqlCnn))
            {
                try
                {
                    mSqlConnection.Open();                  
                    mSqlCommand = new SqlCommand("OpMngSys.USP_CostumerLogin",mSqlConnection);
                    mSqlCommand.CommandType = CommandType.StoredProcedure;

                    //Add Input Parameters
                    mSqlCommand.Parameters.Add("@UserName", SqlDbType.VarChar).Value = pUser.UserName;
                    mSqlCommand.Parameters.Add("@Password", SqlDbType.VarChar).Value = pUser.Password;

                    //Declare output parameter that will receive the result
                    SqlParameter outputIsExists = new SqlParameter("@IsExists", SqlDbType.Int) {
                        Direction = ParameterDirection.Output
                        };
                    mSqlCommand.Parameters.Add(outputIsExists);

                    //ExecuteNonQuery won't give any result
                    mSqlCommand.ExecuteNonQuery();

                    //Get the value that has been set by the StoredProcedure in the output parameter
                    logresult = (int)outputIsExists.Value
                }
                catch (Exception ex)
                { throw ex; }
            }

            return logresult;
        }

        public int Create(User pUser)
        {
            return 0;
        }

        //public int Delete(User pUser)
        //{
        //    return 0;
        //}
    }
}

NOTE: If you're getting the error.

"Procedure or function USP_CostumerLogin has too many arguments specified."

It could be that your StoredProcedure doesn't accept the OUTPUT Parameter. In other words, maybe you're not using the code you wrote here for the StoredProcedure.

Thank you so very much !!!! this is the real solution for my problem, thanks man !!!!

Donal · Accepted Answer · 2015-11-26 20:53:19Z

1

I believe you need to add an output parameter in UserEntity in the Entrar method.

For example:

SqlParameter outputIsExists = new SqlParameter("@IsExists", SqlDbType.Int)
{
    Direction = ParameterDirection.Output 
};

mSqlCommand.Parameters.Add(outputIsExists);

You can get the value from the output parameter after calling ExecuteNonQuery. For example:

mSqlCommand.ExecuteNonQuery();

logresult = (int)outputIsExists.Value;

The full Entrar method would look something like this:

public int Entrar(User pUser) {
    int logresult = 0;
    try {
        if (CreateConnection())
        {
            if (mSqlConnection != null && mSqlConnection.State != ConnectionState.Open) {
                mSqlConnection.Open();
            }

            mSqlCommand = new SqlCommand("OpMngSys.USP_CostumerLogin");
            mSqlCommand.Connection = mSqlConnection;
            mSqlCommand.CommandType = System.Data.CommandType.StoredProcedure;

            mSqlCommand.Parameters.Add("@UserName", SqlDbType.VarChar).Value = pUser.UserName;
            mSqlCommand.Parameters.Add("@Password", SqlDbType.VarChar).Value = pUser.Password;

            SqlParameter outputIsExists = new SqlParameter("@IsExists", SqlDbType.Int) {
                Direction = ParameterDirection.Output
            };

            mSqlCommand.Parameters.Add(outputIsExists);

            mSqlCommand.ExecuteNonQuery();

            logresult = (int)outputIsExists.Value;
        }

        return logresult;

    }catch (Exception ex)
    {
        throw ex;
    }
}

edited Nov 26, 2015 at 20:53

answered Nov 26, 2015 at 20:43

Donal

33.2k10 gold badges69 silver badges75 bronze badges

2 Comments

AnubisVolga Over a year ago

nope it not works... it send a error: {"Procedure or function USP_CostumerLogin has too many arguments specified."} ...

Donal Over a year ago

Based on your stored procedure definition sql, the code above is correct.

marc_s · Accepted Answer · 2015-11-27 05:59:35Z

You need to change your stored procedure to return the value as a result set (not a RETURN statement):

CREATE PROCEDURE OpMngSys.USP_CostumerLogin
    @UserName VARCHAR(100),
    @Password VARCHAR(150)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @IfExists BIT

    IF EXISTS (SELECT UserName, [Password] 
               FROM [Security].[Users] 
               WHERE UserName = @UserName and [Password] = @Password)
    BEGIN
        SET @IfExists = 1
    END
    ELSE 
    BEGIN
        SET @IfExists = 0
    END

    SELECT @IfExists  -- return as a result set
END

and then you need to change your C# code to use ExecuteScalar() to get that value:

mSqlCommand = new SqlCommand("OpMngSys.USP_CostumerLogin", mSqlConnection);
mSqlCommand.CommandType = System.Data.CommandType.StoredProcedure;

mSqlCommand.Parameters.Add("@UserName", SqlDbType.VarChar, 100).Value = pUser.UserName;
mSqlCommand.Parameters.Add("@Password", SqlDbType.VarChar, 150).Value = pUser.Password;

mSqlConnection.Open();
object resultset = mSqlCommand.ExecuteScalar();
mSqlConnection.Close();

bool userDoesExist;

if(resultset != null)
{
    if (bool.TryParse(resultset.ToString(), out userDoesExist))
    {
       return userDoesExist;
    }
    else
    {
        // ERROR: cannot convert result to a "bool"
    }
}

Collectives™ on Stack Overflow

ASP.NET User Validation

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related