locked
ASP.NET MVC web app login form with SQL Server Stored Procedure RRS feed

  • Question

  • User1480885595 posted

    Hi Guys,

    New to coding in both C# and MVC, working on a project to create a login form. Read a few things online and not seen much which is applied in the way i would like to do it. I am using SQL Server to store the a hashed password and a stored procedure to validate user input. This works with test data in SQL. I would like to take user input and use it to run the stored procedure and relay the response message back to the user.

    Stored Procedure

    USE [LoginTest]
    GO
    /****** Object:  StoredProcedure [dbo].[uspLogin]    Script Date: 12/04/2019 11:10:41 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    ALTER PROCEDURE [dbo].[uspLogin]
        @pLoginName NVARCHAR(254),
        @pPassword NVARCHAR(50),
        @responseMessage NVARCHAR(250)='' OUTPUT
    AS
    BEGIN
     
        SET NOCOUNT ON
     
        DECLARE @userID INT
     
        IF EXISTS (SELECT TOP 1 UserID FROM [dbo].[User] WHERE LoginName=@pLoginName)
        BEGIN
            SET @userID=(SELECT UserID FROM [dbo].[User] WHERE LoginName=@pLoginName AND PasswordHash=HASHBYTES('SHA2_512', @pPassword+CAST(Salt AS NVARCHAR(36))))
     
           IF(@userID IS NULL)
               SET @responseMessage='Incorrect password'
           ELSE 
               SET @responseMessage='User successfully logged in'
        END
        ELSE
           SET @responseMessage='Invalid login'
     
    END
    Friday, April 12, 2019 10:24 AM

Answers

  • User283571144 posted

    Hi Ryan_,

    According to your description, I suggest you could try to use ADO.NET in MVC to call the SP and get the output parameter.

    More details, you could refer to below codes:

                //Read the connection string from Web.Config file
                string ConnectionString = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
                using (SqlConnection con = new SqlConnection(ConnectionString))
                {
                    //Create the SqlCommand object
                    SqlCommand cmd = new SqlCommand("uspLogin", con);
    
                    //Specify that the SqlCommand is a stored procedure
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;
    
                    //Add the input parameters to the command object
                    cmd.Parameters.AddWithValue("@pLoginName", "");
                    cmd.Parameters.AddWithValue("@pPassword", "");
        
    
                    //Add the output parameter to the command object
                    SqlParameter outPutParameter = new SqlParameter();
                    outPutParameter.ParameterName = "@responseMessage";
                    outPutParameter.SqlDbType = System.Data.SqlDbType.Int;
                    outPutParameter.Direction = System.Data.ParameterDirection.Output;
                    cmd.Parameters.Add(outPutParameter);
    
                    //Open the connection and execute the query
                    con.Open();
                    cmd.ExecuteNonQuery();
    
                    //Retrieve the value of the output parameter
                    string ResponseMessage = outPutParameter.Value.ToString();
    
                }

    Best Regards,

    Brando

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, April 15, 2019 7:48 AM

All replies

  • User753101303 posted

    Hi,

    And so the problem is about how to call this SP from ADO.NET ? Or you could use Entity Framework or maybe even https://docs.microsoft.com/en-us/aspnet/identity/overview/getting-started/introduction-to-aspnet-identity#aspnet-identity

    The later would allow to have a defined API for handling that allowing to replace or use the same model in all your app regardless of how user data are handled behing the scene.

    Friday, April 12, 2019 11:00 AM
  • User283571144 posted

    Hi Ryan_,

    According to your description, I suggest you could try to use ADO.NET in MVC to call the SP and get the output parameter.

    More details, you could refer to below codes:

                //Read the connection string from Web.Config file
                string ConnectionString = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
                using (SqlConnection con = new SqlConnection(ConnectionString))
                {
                    //Create the SqlCommand object
                    SqlCommand cmd = new SqlCommand("uspLogin", con);
    
                    //Specify that the SqlCommand is a stored procedure
                    cmd.CommandType = System.Data.CommandType.StoredProcedure;
    
                    //Add the input parameters to the command object
                    cmd.Parameters.AddWithValue("@pLoginName", "");
                    cmd.Parameters.AddWithValue("@pPassword", "");
        
    
                    //Add the output parameter to the command object
                    SqlParameter outPutParameter = new SqlParameter();
                    outPutParameter.ParameterName = "@responseMessage";
                    outPutParameter.SqlDbType = System.Data.SqlDbType.Int;
                    outPutParameter.Direction = System.Data.ParameterDirection.Output;
                    cmd.Parameters.Add(outPutParameter);
    
                    //Open the connection and execute the query
                    con.Open();
                    cmd.ExecuteNonQuery();
    
                    //Retrieve the value of the output parameter
                    string ResponseMessage = outPutParameter.Value.ToString();
    
                }

    Best Regards,

    Brando

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, April 15, 2019 7:48 AM
  • User1480885595 posted

    Hi Brando, Thanks for the reply. This works perfectly for calling the stored procedure in and manages to get a response message from the database, However, i am not sure where to place it? I had read it is bad practice to have it in the Model, but i think where i have it (controller) is also bad practice. Where should this be placed? I am also unsure how to store user input from a textbox to then pass into the cmd.Parameters.AddWithValue command object. Any help would be greatly appreciated. Cheers, Ryan.

    Tuesday, April 16, 2019 8:16 AM