Article
    C#
    ADO.Net
    .NET
    ASP.Net & Web Forms
    Custom Controls
    Web Development
    Exception Handling
    XML
    Database
    Security in .Net
    Testing
    Web Services
    Windows Services
    Windows Controls
    WCF
    AJAX
    WPF
    XAML
    Reporting
    Setup
    VB.Net
    LINQ
    JQuery
    SilverLight
    JavaScript
    HTML5
    Crystal Report
    Cloud Computing
    Share Point
    Visual C++
    MVC
    Android
    PHP
    Java
    HTML
    WordPress
    Joomla
    Products
    Drupal
    Windows Phone
    JSON
    LightSwitch
    iPhone/iPad
    Ruby on Rails
    IIS 7
    Windows 8
    CSS/CSS3
    Excel
    MS Access
    Shortcut Keys
    Visual SourceSafe
    Team Foundation Server
    API(s)
    Sencha-Touch
    Single Page App
    Bootstrap
Follow Us
Follow _MindStick_ on Twitter View MindStick Software's LinkedIn profile View MindStick Software's Facebook profile
Top Contributor
Advertisement
Advertise with Us
Mindstick
Article Article  Forum Forum  Blog Blog  Quiz Quiz  Beginner Beginner  Careers Careers  Contact Contact  Login Login  
Home | Product | Services | About Us | Interview | DeveloperSection | Submit an Article | Submit Blog

Home >> MVC >> 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


by Vijay Shukla on 1/9/2013 6:14:21 PM

Views: 15965       Comments: 3

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) Primary Key,

  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.   

Report Abuse Form
Reason:    
 


Code
by Dorababu M 1/22/2013 10:12:44 PM
Can you mail me the code please I am little bit confused, 
Report Abuse

Help Me
by Horas Panjaitan 3/30/2013 11:10:45 AM

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


Report Abuse

how to Insert,edit images in with database using asp.net mvc
by anil babu 6/25/2013 7:40:20 AM
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
Report Abuse
Title :
Comment :
Text ColorBackground Color
BoldItalicUnderline
LeftCenterRightJustify
Ordered ListBulleted List
IndentOutdent
Horizontal Rule
SubscriptSuperscript
HyperlinkImage
Design ModeDesign
View HtmlHtml
     
 
Latest Article by Vijay ShuklaRSS Feed
    
    
    
    
    
    
    
    
    
    
More...
Latest BlogsRSS Feed
    
    
    
    
    
    
    
    
    
    
More...
Top Viewed ArticlesRSS Feed
    
    
    
    
    
    
    
    
    
    
Top Viewed BlogsRSS Feed
    
    
    
    
    
    
    
    
    
    
Latest Interview QuestionsRSS Feed
    
    
    
    
    
    
    
    
    
    
More...
Total Online Users: 5048
Advertisement
MindStick SurveyManager
Advertise with Us
  
Copyright © 2009 - 2014MindStick. All Rights Reserved.