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

Posted by  Vijay Shukla
on    January-09-2013 18:14 PM

ASP.NET MVC ASP.NET MVC 
Ratings:
3 Comment(s)
 18566  View(s)
Rate this:

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

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

1.       Insert Stored Procedure use when we create an account in this application

2.       Update Stored Procedure and select Stored Procedure use when we change the password.

3.       Delete Stored Procedure use when we delete the account in this application. And here is also use select Stored Procedure in isUserAndPasswordExist() method this method is also use in Update Stored Procedure.

After staring this article we will create 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 Stored Procedure:

For more information about creating stored procedure read below articles

http://www.mindstick.com/Articles/37dd5217-1856-418c-84af-a344fc353e0c/?Implementing%20Stored%20Procedure

http://www.mindstick.com/Beginner/79BE36EC-D4E6-4EF5-AE5D-C65FD7EDE2B9/SQL%20Server/Stored%20Procedure%20in%20SQL/

http://www.mindstick.com/Blog/200/SQL%20Stored%20Procedure

http://www.mindstick.com/Blog/65/Creating%20and%20Using%20Stored%20Procedure

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 a MVC4 application and write the code below:

Now create a model class for properties:

1.       Right click on Models folder in Solution Explorer.

2.       Click on Add.

3.       Give the Class name.

4.       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 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 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 means insert value in 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 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();

}

 

Above code will update the password from the table.

isUserAndPasswordExist():this is a Boolean return type method which is create for check the user id and password is exist or not. It has three parameters and we are also use the Stored procedure for select the data from tables.

1.  Password

2.  User Id

3.  Check where from call this method. 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 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.   


Code

By Dorababu M on   one year ago
Can you mail me the code please I am little bit confused, 

Help Me

By Horas Panjaitan on   one year ago

Hi Vijay Shukla can you help me? I have a dropdownlist that contains code / id.
I want to take the name of the code that is in the dropdownlist



this my code

 protected void cmbroom_SelectedIndexChanged(object sender, EventArgs e)
    {
        kon = new MySqlConnection(ConfigurationManager.ConnectionStrings["mydatabase"].ConnectionString);
        kon.Open();
        string cmdText = "SELECT  coderoom FROM tblroom WHERE coderoom=@coderoom";

        data = new DataTable();
        MySqlDataAdapter adapter = new MySqlDataAdapter(cmdText, kon);
       
      
        adapter.SelectCommand.Parameters.AddWithValue("@coderoom", cmbroom.SelectedValue);
      
        cmbroom.SelectedIndex = -0;
        adapter.Fill(data);
        cmbroom.DataSource = data;
        cmbroom.DataBind();


    }
I also want to display the name of the room after the select code room



how to Insert,edit images in with database using asp.net mvc

By anil babu on   one year ago
how to Insert,edit images in with database using asp.net mvc??
I am trying mindstick one article but not getting correct output,
Please prove me complete code for this















Recent Activities


Kamlakar Singh added new Blog Convert Text Document to PDF File   10 days ago

Kamlakar Singh added new Article Introduction to Backbone.js   10 days ago

Kamlakar Singh added new Question Explain the AdRotator Control.   10 days ago

Don't want to miss updates? Please click the below button!