articles

Home / DeveloperSection / Articles / Select Insert Update and Delete using Stored Procedure in ASP.NET MVC4

Select Insert Update and Delete using Stored Procedure in ASP.NET MVC4

Select Insert Update and Delete using Stored Procedure in ASP.NET MVC4

Vijay Shukla 94541 09-Jan-2013

Today, In this article I am creating an application in ASP.NET MVC4 which is inserting, update and Delete value via Stored Procedure.

We can use all Stored Procedures in this article

  • Insert Stored Procedure use when we create an account in this application
  • Update Stored Procedure and select Stored Procedure used when we change the password.
  • Delete Stored Procedure use when we delete the account in this application.
  • And here is also use select Stored Procedure in isUserAndPasswordExist() the method this method is also used in Update Stored Procedure.

After staring this article we will create a table and Stored Procedure in your SQL Server.

Create table:
CREATE TABLE [dbo].[LoginInfo]

(
  vName varchar(50),
  vID varchar(20)PrimaryKey,
  vPassword varchar(20)
)

Select Insert Update and Delete using Stored Procedure in ASP.NET MVC4

Create a Stored Procedure:

For more information about creating stored procedure read below articles

https://www.mindstick.com/Articles/429/implementing-stored-procedure

https://www.mindstick.com/blog/200/sql-stored-procedure

https://www.mindstick.com/blog/65/creating-and-using-stored-procedure

Insert:

CREATE PROCEDURE sp_AddLoginDetails 

@UserName varchar(50),
@LoginId varchar(20),
@Password varchar(20)
AS
begin
    SET NOCOUNT ON;
    insert into LoginInfo (vName,vID,vPassword)values (@UserName,@LoginId,@Password)
End

Update:

CREATE PROCEDURE sp_PasswordChange 

@Password varchar(20),
@ID varchar(20)
AS
begin
    SET NOCOUNT ON;
    UPDATE LoginInfo SET vPassword = @Password WHERE vID=@ID
    End

Delete:

CREATE PROCEDURE sp_DeleteAccount 

@ID varchar(20)
AS
begin
    SET NOCOUNT ON;
    DELETE FROM LoginInfo WHERE vID = @ID
    End

Select:

CREATE PROCEDURE sp_CheckUseAandPassword 

@ID varchar(20),
@Password varchar(20)
AS
begin
    SET NOCOUNT ON;
    select vID,vPassword from LoginInfo where vID=@ID and vPassword=@Password
    End

 Before creating Stored Procedures and table we create an MVC4 application and write the code below:

Now create a model class for properties:

  • Right-click on Models folder in Solution Explorer.
  • Click on Add.
  • Give the Class name.
  • Press OK.

Below is my Model Class Code:

using System.Collections.Generic;

using System.ComponentModel.DataAnnotations;
namespace Zytron.Models
{
    public class LoginDetails
    {
        [Required(ErrorMessage = "*")]
        public string UserName
        {
            get;
            set;
        }

        [Required(ErrorMessage = "*")]
        public string LoginID
        {
            get;
            set;
        }

        [DataType(DataType.Password)]

        public string Password
        {
            get;
            set;
        }
        [DataType(DataType.Password)]

        public string OldPassword
        {
            get;
            set;
        }
    }

}

 My Solution Explorer:

Select Insert Update and Delete using Stored Procedure in ASP.NET MVC4

After that, we will add the Connection String in the Web.Config file:

<connectionStrings>

        <add name="ConnectionName"connectionString="Data Source=Your Server Name; Initial Catalog= Database; User Id= Your userID; Password= Your Password;"providerName="System.Data.SqlClient"/>
  </connectionStrings>

 Insert:

This index Action method called when my MVC application load the first time.

[HttpGet]

        public ActionResult Index()
        {
            ViewBag.Message = "";

            return View();
        }

 Output:

Select Insert Update and Delete using Stored Procedure in ASP.NET MVC4

After clicking submit button control go on parameterizes Index Action method in Home Controller which will create an account that means insert value in the table via Stored Procedure.

Parameterizes Index Action method:

 [HttpPost]

public ActionResult Index(Zytron.Models.LoginDetails model)
{
using (var con = new SqlConnection
(ConfigurationManager.ConnectionStrings["ConnectionName"].ConnectionString))// This is give the connectionStrings on SqlConnection class’ Construrctor
{
var cmd = new SqlCommand("sp_AddLoginDetails", con);// Give The command to SqlCommand
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@UserName", SqlDbType.VarChar)).Value = model.UserName.Trim();//Pass the parameter
cmd.Parameters.Add(new SqlParameter("@LoginId", SqlDbType.VarChar)).Value = model.LoginID.Trim();//Pass the parameter
cmd.Parameters.Add(new SqlParameter("@Password", SqlDbType.VarChar)).Value = model.Password; //Pass the parameter
 try
 {
  if (con.State != ConnectionState.Open)
  con.Open();
  cmd.ExecuteNonQuery();
  ViewBag.message = "Account Created";
  return View();
 }
 finally
 {
 if (con.State != ConnectionState.Closed)
  con.Close();
 }
}
  return View();
}

 View:

@model Zytron.Models.LoginDetails

@{
    ViewBag.Title = "::Cretae Account::";
}
<h1>Cretae Account</h1>
<form id="frmLogin" method="post">
<div class="container">
    <fieldset>
        <legend class="legend"></legend>
        <table cellpadding="10" cellspacing="0" width="100%">
            <tr>
                <td style="width: 20%;">
                    <span class="LabelFormat">UserName</span>
                </td>
                <td>
                    @Html.TextBoxFor(m => m.UserName, null, new { @class = "TextboxFormat", @style = " tabindex :0;" })
                </td>
                <td style="width: 5px;">
                    @Html.ValidationMessageFor(m => m.UserName, null, new { @class = "ErrorMessage", @id = "valMsgForFName" })
                </td>
            </tr>
            <tr>
                <td>
                    <span class="LabelFormat">LoginID</span>
                </td>
                <td>
                    @Html.TextBoxFor(m => m.LoginID, null, new { @class = "TextboxFormat", @style = " tabindex :1;" })
                </td>
                <td style="width: 5px;">
                    @Html.ValidationMessageFor(m => m.LoginID, null, new { @class = "ErrorMessage", @id = "valMsgForUName" })
                </td>
            </tr>
            <tr>
                <td>
                    <span class="LabelFormat">Password</span>
                </td>
                <td>
                    @Html.PasswordFor(m => m.Password, new { @class = "TextboxFormat", @style = " tabindex :1;" })
                </td>
                <td style="width: 5px;">
                    @Html.ValidationMessageFor(m => m.Password, null, new { @class = "ErrorMessage", @id = "valMsgForPwd" })
                </td>
            </tr>
            <tr>
                <td><p style="color:Red;">@ViewBag.message</p>
                </td>
                <td>
                    <input type="submit" value="Submit" name="Submit" />
                </td>
                            </tr>

        </table>
    </fieldset>
</div>
</form>

 Update:

View:

@model Zytron.Models.LoginDetails

@{
    ViewBag.Title = "::Change Password::";
}
<h2>
    Change Password</h2>
<form id="frmLogin" method="post">
<div class="container">
    <fieldset>
        <legend class="legend"></legend>
        <table cellpadding="10" cellspacing="0" width="100%">
            <tr>
                <td>
                    <span class="LabelFormat">Enter LoginID</span>
                </td>
                <td>
                    @Html.TextBoxFor(m => m.LoginID, null, new { @class = "TextboxFormat", @style = " tabindex :1;" })
                </td>
                <td style="width: 5px;">
                    @Html.ValidationMessageFor(m => m.LoginID, null, new { @class = "ErrorMessage", @id = "valMsgForUName" })
                </td>
            </tr>
            <tr>
                <td>
                    <span class="LabelFormat">Enter Old Password</span>
                </td>
                <td>
                    @Html.PasswordFor(m => m.OldPassword, new { @class = "TextboxFormat", @style = " tabindex :1;" })
                </td>
                <td style="width: 5px;">
                    @Html.ValidationMessageFor(m => m.OldPassword, null, new { @class = "ErrorMessage", @id = "valMsgForPwd" })
                </td>
            </tr>
            <tr>
                <td>
                    <span class="LabelFormat">New Password</span>
                </td>
                <td>
                    @Html.PasswordFor(m => m.Password, new { @class = "TextboxFormat", @style = " tabindex :1;" })
                </td>
                <td style="width: 5px;">
                    @Html.ValidationMessageFor(m => m.Password, null, new { @class = "ErrorMessage", @id = "valMsgForPwd" })
                </td>
            </tr>
            <tr>
                <td><p style="color:Red;">@ViewBag.message</p>
                </td>
                <td>
                    <input type="submit" value="Change" name="Submit" />
                </td>
            </tr>
        </table>
    </fieldset>
</div>
</form> 

 Controller:

[HttpGet]

public ActionResult PassChange()
{
    return View();
}

Above code load the Change password Page the first time: 

Output: 

Select Insert Update and Delete using Stored Procedure in ASP.NET MVC4

When we click on Change Password menu and type the existing Login Id, old password and new password and click change button the control go to on the parameterizes PassChange() Action Method

In the PassChange() action method we write the code to update the password via Stored Procedure.

Code:

public ActionResult PassChange(Zytron.Models.LoginDetails model)

{
 if (isUserAndPasswordExist(model.OldPassword.Trim(),model.LoginID.Trim(),"PasswordChange"))
 {
  using (var con = new SqlConnection
(ConfigurationManager.ConnectionStrings["ConnectionName"].ConnectionString))
  {
   var cmd = new SqlCommand("sp_PasswordChange", con);
   cmd.CommandType = CommandType.StoredProcedure;
   cmd.Parameters.Add(new SqlParameter("@Password", SqlDbType.VarChar)).Value = model.Password;
   cmd.Parameters.Add(new SqlParameter("@ID", SqlDbType.VarChar)).Value = model.LoginID.Trim();
   try
   {
    if (con.State != ConnectionState.Open)
    con.Open();
    cmd.ExecuteNonQuery();
    }
   finally
   {
    if (con.State != ConnectionState.Closed)
     con.Close();
   }
   ViewBag.message = "Password Update Successfully...!!!";
   return View();
 }

}
else
ViewBag.message = "Password Not Changed...!!!";
return View();
}

 The above code will update the password from the table.

isUserAndPasswordExist():this is a Boolean return type method which is created to check the user id and password is existed or not.

It has three parameters and we are also using the Stored procedure for select the data from tables.

1.  Password

2.  User Id

3.  Check where from call this method. This means I am calling this method from passChange() action method as well Delete() action method.

Below is isUserAndPasswordExist() code:

private bool isPasswordExist(string OldPass, string userID,string str)

{
 using (var con = new SqlConnection
(ConfigurationManager.ConnectionStrings["ConnectionName"].ConnectionString))
 {
 //var cmd = new SqlCommand("select vPassword from LoginInfo where vPassword='" + pass + "'", con);
 var cmd = new SqlCommand("sp_CheckUseAandPassword", con); // here use Select Procedure
 cmd.CommandType = CommandType.StoredProcedure;
 cmd.Parameters.Add(new SqlParameter("@ID", SqlDbType.VarChar)).Value = userID;
 cmd.Parameters.Add(new SqlParameter("@Password", SqlDbType.VarChar)).Value = OldPass;
 con.Open();
 var reader = cmd.ExecuteReader();
 if (reader.Read())
  {
   if (str == "PasswordChange")
    {
     if (OldPass == reader[1].ToString())
      return true;
      else
       return false;
     }
     else if (str == "Delete")
     {
      if (userID==reader[0].ToString() && OldPass == reader[1].ToString())
       return true;
          else
       return false;
     }
  }
                return false;
}
}

Delete:

View:

DeleteAccount.CHTML

@model Zytron.Models.LoginDetails

@{
    ViewBag.Title = "DeleteAccount";
}

<h2>DeleteAccount</h2>
<form id="frmLogin" method="post">
<div class="container">
    <fieldset>
        <legend class="legend"></legend>
        <table cellpadding="10" cellspacing="0" width="100%">
            <tr>
                <td>
                    <span class="LabelFormat">Enter LoginID</span>
                </td>
                <td>
                    @Html.TextBoxFor(m => m.LoginID, null, new { @class = "TextboxFormat", @style = " tabindex :1;" })
                </td>
                <td style="width: 5px;">
                    @Html.ValidationMessageFor(m => m.LoginID, null, new { @class = "ErrorMessage", @id = "valMsgForUName" })
                </td>
            </tr>
            <tr>
                <td>
                    <span class="LabelFormat">Password</span>
                </td>
                <td>
                    @Html.PasswordFor(m => m.Password, new { @class = "TextboxFormat", @style = " tabindex :1;" })
                </td>
                <td style="width: 5px;">
                    @Html.ValidationMessageFor(m => m.Password, null, new { @class = "ErrorMessage", @id = "valMsgForPwd" })
                </td>
            </tr>


            <tr>
                <td>@ViewBag.message
                </td>
                <td>
                    <input type="submit" value="Delete" name="Submit" />
                </td>

            </tr>
        </table>
    </fieldset>
</div>
</form>

 Controller:

[HttpGet]

public ActionResult DeleteAccount()
{
  return View();
}

 Above code is loading the Delete Account view first time.

After loading the view on the browser the output is below:

Select Insert Update and Delete using Stored Procedure in ASP.NET MVC4

This is before pressing the Delete button your table:

Select Insert Update and Delete using Stored Procedure in ASP.NET MVC4

After pressing Delete key:

Select Insert Update and Delete using Stored Procedure in ASP.NET MVC4

Above you will see the vs1491’s information will remove permanently from our table.

Conclusion:

In this article we understand how we insert, update, delete and select the data from your table using Stored Procedure.   


Updated 19-Aug-2020

Leave Comment

Comments

Liked By