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
<div>USE [LoginTest]</div> <div>GO</div> <div>/****** Object: StoredProcedure [dbo].[uspLogin] Script Date: 12/04/2019 11:10:41 ******/</div> <div>SET ANSI_NULLS ON</div> <div>GO</div> <div>SET
QUOTED_IDENTIFIER ON</div> <div>GO</div> <div>ALTER PROCEDURE [dbo].[uspLogin]</div> <div> @pLoginName NVARCHAR(254),</div> <div> @pPassword NVARCHAR(50),</div> <div> @responseMessage
NVARCHAR(250)='' OUTPUT</div> <div>AS</div> <div>BEGIN</div> <div></div> <div> SET NOCOUNT ON</div> <div></div> <div> DECLARE @userID INT</div> <div></div>
<div> IF EXISTS (SELECT TOP 1 UserID FROM [dbo].[User] WHERE LoginName=@pLoginName)</div> <div> BEGIN</div> <div> SET @userID=(SELECT UserID FROM [dbo].[User] WHERE LoginName=@pLoginName AND PasswordHash=HASHBYTES('SHA2_512',
@pPassword+CAST(Salt AS NVARCHAR(36))))</div> <div></div> <div> IF(@userID IS NULL)</div> <div> SET @responseMessage='Incorrect password'</div> <div> ELSE </div> <div>
SET @responseMessage='User successfully logged in'</div> <div> END</div> <div> ELSE</div> <div> SET @responseMessage='Invalid login'</div> <div></div> <div>END</div>
<div></div> <div></div> <div></div>
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.
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();
}
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.
Member
1 Points
2 Posts
ASP.NET MVC web app login form with SQL Server Stored Procedure
Apr 12, 2019 10:24 AM|Ryan_|LINK
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
<div>USE [LoginTest]</div> <div>GO</div> <div>/****** Object: StoredProcedure [dbo].[uspLogin] Script Date: 12/04/2019 11:10:41 ******/</div> <div>SET ANSI_NULLS ON</div> <div>GO</div> <div>SET QUOTED_IDENTIFIER ON</div> <div>GO</div> <div>ALTER PROCEDURE [dbo].[uspLogin]</div> <div> @pLoginName NVARCHAR(254),</div> <div> @pPassword NVARCHAR(50),</div> <div> @responseMessage NVARCHAR(250)='' OUTPUT</div> <div>AS</div> <div>BEGIN</div> <div> </div> <div> SET NOCOUNT ON</div> <div> </div> <div> DECLARE @userID INT</div> <div> </div> <div> IF EXISTS (SELECT TOP 1 UserID FROM [dbo].[User] WHERE LoginName=@pLoginName)</div> <div> BEGIN</div> <div> SET @userID=(SELECT UserID FROM [dbo].[User] WHERE LoginName=@pLoginName AND PasswordHash=HASHBYTES('SHA2_512', @pPassword+CAST(Salt AS NVARCHAR(36))))</div> <div> </div> <div> IF(@userID IS NULL)</div> <div> SET @responseMessage='Incorrect password'</div> <div> ELSE </div> <div> SET @responseMessage='User successfully logged in'</div> <div> END</div> <div> ELSE</div> <div> SET @responseMessage='Invalid login'</div> <div> </div> <div>END</div> <div></div> <div></div> <div></div>All-Star
43720 Points
15301 Posts
Re: ASP.NET MVC web app login form with SQL Server Stored Procedure
Apr 12, 2019 11:00 AM|PatriceSc|LINK
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.
Star
9151 Points
2797 Posts
Re: ASP.NET MVC web app login form with SQL Server Stored Procedure
Apr 15, 2019 07:48 AM|Brando ZWZ|LINK
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
Member
1 Points
2 Posts
Re: ASP.NET MVC web app login form with SQL Server Stored Procedure
Apr 16, 2019 08:16 AM|Ryan_|LINK
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.