Creating Insert Update and Delete Application In MVC 4 Using Razor

Creating an insert, update and delete application in MVC 4 using Razor syntax. I am providing a small demo application.

Server Part

Starting with creating the table: tbInsertMobile.

Creating Table

Create table tbInsertMobile
(
MobileID Bigint not null primary key IDENTITY(1,1),
MobileName Nvarchar(100),
MobileIMEno Nvarchar(50),
mobileprice numeric(19,2),
mobileManufacured Nvarchar(100),
CreatedDate datetime default Getdate()
)

Creating Stored Procedures

Create proc Usp_InsertUpdateDelete
@MobileID Bigint =0 ,
@MobileName Nvarchar(100) = null,
@MobileIMEno Nvarchar(50) = null,
@mobileprice numeric(19,2) = 0,
@mobileManufacured Nvarchar(100) = null,
@Query int
as
begin
if(@Query = 1)
begin
Insert into  tbInsertMobile
(
MobileName ,
MobileIMEno ,
mobileprice,
mobileManufacured
)
values
(
@MobileName ,
@MobileIMEno ,
@mobileprice,
@mobileManufacured
)
if(@@ROWCOUNT > 0)
begin
select 'Insert'
end
end
if(@Query = 2)
begin
update tbInsertMobile
set
MobileName =@MobileName ,
MobileIMEno =@MobileIMEno ,
mobileprice =@mobileprice,
mobileManufacured =@mobileManufacured
where tbInsertMobile.MobileID =@MobileID
select 'Update'
end
if(@Query = 3)
begin
Delete from tbInsertMobile where tbInsertMobile.MobileID =@MobileID
select 'Deleted'
end
if(@Query = 4)
begin
Select * from tbInsertMobile
end
End
if(@Query = 5)
begin
Select * from tbInsertMobile where tbInsertMobile.MobileID =@MobileID
end

Now for the code.

Begin by creating a MVC application as in the following:

After adding an application name the second screen will pop up prompting for a selection of a Project Template.

 

In this select Internet application and View Engine Razor and click OK.

Then your project is created successfully.

 

In this application I will first add a Model.

 

Provide the Model the name Mobiledata.cs.

 

After creating the model:

 

In the Model I will declare Properties and Validation (DataAnnotations).

 

Here are a number of DataAnnotations that we can use in a model.

The following are the Data Annotations we can use in a Model:

1. DisplayName: Provides a general-purpose attribute that lets you specify localizable strings to display.
2. Required: A value is required
3. DataType: The data type annotation can be used to specify the data type for validation.
4. StringLength: Max. length of an array or string data allowed.
5. DisplayFormat: Specify the display format for a property like various formats for the Date property.
6. ReqularExpression: Validate the value of a property by specifyng a regular expression pattern.
7. Range: Numeric range constraints for the data field value.
8. MaxLength: Specify max length for a string property.
9. Bind: Specify fields to include or exclude when adding parameter or form values to model properties.
10. Compare: The Compares property compares two properties
11. Key: Denotes one or more properties that uniquely identify an entity.

In the Model I added Properties and Validation.

Model Code

using System.ComponentModel.DataAnnotations;
namespace MymobilewalaMvc.Models
{
    public class Mobiledata
    {
        public int MobileID {get;set;}
        [Required(ErrorMessage="Please Enter Mobile Name")]
        [Display(Name="Enter Mobile Name")]
        [StringLength(50, MinimumLength = 3, ErrorMessage = "Mobile Name must be between 3 and 50 characters!")]
        public string MobileName {get;set;}
        [Required(ErrorMessage="Please Enter MobileIMEno")]
        [Display (Name="Enter MobileIMEno")]
        [MaxLength (100,ErrorMessage="Exceeding Limit")]
        public string MobileIMEno {get;set;}
        [Required(ErrorMessage = "Please Enter Mobile Price")]
        [Display (Name="Enter Mobile Price")]
        [DataType(DataType.Currency)]
        public string mobileprice {get;set;}
        [Required(ErrorMessage = "Please Enter Mobile Manufacured")]
        [Display(Name = "Enter Mobile Manufacured")]
        [DataType(DataType.Text)]
        public string mobileManufacured { get; set; }
    }
}

We have completed creating the Model. Let's start adding the controller.

 

Select Controller; a new Window will then pop up asking for the Controller name.

 

Provide the name MobilestoreController. (We should add "Controller" as a suffix of all Controllers.)

And click on the Add button to add it.

If you do not understand what a controller is then see my first tutorial.

Now we will add a folder and class for accessing a Database Connection and doing the inserting, updating and deleting.

The following shows the added folder name DataAccessLayer.

 

Right-click on the DataAccessLayer folder and select add class then provide the class the name DBdata.cs.

 

Namespace used.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using MymobilewalaMvc.Models;

The namespace MymobilewalaMvc.Models is used to access the model we created.

In the Model I have created the following 5 methods:

1.  InsertData
2. UpdateData
3. DeleteData
4. SelectAllData
5. SelectAllDatabyID

InsertData

public string InsertData(Mobiledata MD)
{
    SqlConnection con = null;
    string result = "";
    try
    {
        con = new SqlConnection(ConfigurationManager.ConnectionStrings["mycon"].ToString());
        SqlCommand cmd = new SqlCommand("Usp_InsertUpdateDelete", con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@MobileID", 0);
        // i will pass zero to MobileID beacause its Primary .
        cmd.Parameters.AddWithValue("@MobileName", MD.MobileName);
        cmd.Parameters.AddWithValue("@MobileIMEno", MD.MobileIMEno);
        cmd.Parameters.AddWithValue("@mobileprice", MD.mobileprice);
        cmd.Parameters.AddWithValue("@mobileManufacured", MD.mobileManufacured);
        cmd.Parameters.AddWithValue("@Query", 1);
        con.Open();
        result = cmd.ExecuteScalar().ToString();
        return result;
    }
    catch
    {
        return result = "";
    }
    finally
    {
        con.Close();
    }
}

Update Data

public string UpdateData(Mobiledata MD)
{
    SqlConnection con = null;
    string result = "";
    try
    {
        con = new SqlConnection(ConfigurationManager.ConnectionStrings["mycon"].ToString());
        SqlCommand cmd = new SqlCommand("Usp_InsertUpdateDelete", con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@MobileID", MD.MobileID);
        cmd.Parameters.AddWithValue("@MobileName", MD.MobileName);
        cmd.Parameters.AddWithValue("@MobileIMEno", MD.MobileIMEno);
        cmd.Parameters.AddWithValue("@mobileprice", MD.mobileprice);
        cmd.Parameters.AddWithValue("@mobileManufacured", MD.mobileManufacured);
        cmd.Parameters.AddWithValue("@Query", 2);
        con.Open();
        result = cmd.ExecuteScalar().ToString();
        return result;
    }
    catch
    {
        return result = "";
    }
    finally
    {
        con.Close();
    }
}

Delete Data

public string DeleteData(Mobiledata MD)
{
    SqlConnection con = null;
    string result = "";
    try
    {
        con = new SqlConnection(ConfigurationManager.ConnectionStrings["mycon"].ToString());
        SqlCommand cmd = new SqlCommand("Usp_InsertUpdateDelete", con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@MobileID", MD.MobileID);
        cmd.Parameters.AddWithValue("@MobileName", null);
        cmd.Parameters.AddWithValue("@MobileIMEno", null);
        cmd.Parameters.AddWithValue("@mobileprice", 0);
        cmd.Parameters.AddWithValue("@mobileManufacured", null);
        cmd.Parameters.AddWithValue("@Query", 3);
        con.Open();
        result = cmd.ExecuteScalar().ToString();
        return result;
    }
    catch
    {
        return result = "";
    }
    finally
    {
        con.Close();
    }
}

SelectAllData

public DataSet SelectAllData()
{
    SqlConnection con = null;
    string result = "";
    DataSet ds = null;
    try
    {
        con = new SqlConnection(ConfigurationManager.ConnectionStrings["mycon"].ToString());
        SqlCommand cmd = new SqlCommand("Usp_InsertUpdateDelete", con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@MobileID",0);
        cmd.Parameters.AddWithValue("@MobileName", null);
        cmd.Parameters.AddWithValue("@MobileIMEno", null);
        cmd.Parameters.AddWithValue("@mobileprice", 0);
        cmd.Parameters.AddWithValue("@mobileManufacured", null);
        cmd.Parameters.AddWithValue("@Query", 4);
        con.Open();
        SqlDataAdapter da = new SqlDataAdapter();
        da.SelectCommand = cmd;
        ds = new DataSet();   da.Fill(ds);
        return ds;
    }
    catch
    {
        return ds;
    }
    finally
    {
        con.Close();
    }
}

SelectAllDatabyID

public DataSet SelectAllDatabyID(string MobileID)
{
    SqlConnection con = null;
    string result = "";
    DataSet ds = null;
    try
    {
        con = new SqlConnection(ConfigurationManager.ConnectionStrings["mycon"].ToString());
        SqlCommand cmd = new SqlCommand("Usp_InsertUpdateDelete", con);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@MobileID", MobileID); // i will pass zero to MobileID beacause its Primary .
        cmd.Parameters.AddWithValue("@MobileName", null);
        cmd.Parameters.AddWithValue("@MobileIMEno", null);
        cmd.Parameters.AddWithValue("@mobileprice", 0);
        cmd.Parameters.AddWithValue("@mobileManufacured", null);
        cmd.Parameters.AddWithValue("@Query", 4);
        con.Open();
        SqlDataAdapter da = new SqlDataAdapter();
        da.SelectCommand = cmd;
        ds = new DataSet();
        da.Fill(ds);
        return ds;
     }
     catch
     {
        return ds;
    }
    finally
    {
        con.Close();
    }
}

Ha ha, finally we have completed the Transcation Part.

Let's return to the Controller we added.

Just Rebuild the application.

In the Controller add the following two methods:

public ActionResult InsertMobile() // Calling when we first hit controller.
{
   return View();
}
[HttpPost]
public ActionResult InsertMobile(Mobiledata MB) // Calling on http post (on Submit)
{
    return View();
}

Now I will add a View to the Controller.

Just right-click on Action result Insertmobile add select "Add View...".

After selecting Add View we will get a New Window.

Just select (create a strongly-typed view).

Inside that select the Model Name we created and click Add.

 

After adding like this a View will be generated.

With extension .cshtml.

 

In the design we will use a HTMLHELPER Class.

To begin with form we use:

@using (Html.BeginForm())
{
}

For the Label, TextBox and validation message we use:

@Html.LabelFor(a => a.MobileName)
@Html.TextBoxFor(a => a.MobileName)
@Html.ValidationMessageFor(a => a.MobileName)

You will be thinking, what is "a"? It is a lamda expressiom.

The advantage of using a lamda expression is that you get compile-time checking of your properties. For example, if you rename ViewModel.Name to ViewModel.ClientName then all your Html.DisplayFor(x => model.Name) won't compile, thus making sure you change them. If you don't use lamda expressions then all your Html.Display() calls will work, but you will get hidden bugs with model binding that will not be immediately obvious of what's wrong.

The following is the complete design of InsertMobile.

@model MymobilewalaMvc.Models.Mobiledata
@{
    ViewBag.Title = "InsertMobile";
}
<h2>
    InsertMobile</h2>
<table>
    <tr>
        <td>
            @Html.ActionLink("Show All Mobile List", "ShowAllMobileDetails")
        </td>
    </tr>
</table>
@using (Html.BeginForm())
{
    <table width="100%">
        <tr>
            <td>
                @Html.LabelFor(a => a.MobileName)
            </td>
        </tr>
        <tr>
            <td>
                @Html.TextBoxFor(a => a.MobileName)
                @Html.ValidationMessageFor(a => a.MobileName)
            </td>
        </tr>
        <tr>
            <td>
                @Html.LabelFor(a => a.MobileIMEno)
            </td>
        </tr>
        <tr>
            <td>
                @Html.TextBoxFor(a => a.MobileIMEno)
                @Html.ValidationMessageFor(a => a.MobileIMEno)
            </td>
        </tr>
        <tr>
            <td>
                @Html.LabelFor(a => a.mobileManufacured)
            </td>
        </tr>
        <tr>
            <td>
                @Html.TextBoxFor(a => a.mobileManufacured)
                @Html.ValidationMessageFor(a => a.mobileManufacured)
            </td>
        </tr>
        <tr>
            <td>
                @Html.LabelFor(a => a.mobileprice)
            </td>
        </tr>
        <tr>
            <td>
                @Html.TextBoxFor(a => a.mobileprice)
                @Html.ValidationMessageFor(a => a.mobileprice)
            </td>
        </tr>
        <tr>
            <td colspan="2">
                <input id="Submit1" type="submit" value="submit" />
            </td>
        </tr>
    </table>
}

Now just run your application and check output.

 

After Adding Insert code on [Httppost]:

[HttpPost]
public ActionResult InsertMobile(Mobiledata MB) // Calling on http post (on Submit)
{
    if (ModelState.IsValid) //checking model is valid or not
    {
        DataAccessLayer.DBdata objDB = new DataAccessLayer.DBdata(); //calling class DBdata
        string result = objDB.InsertData(MB); // passing Value to DBClass from model
        ViewData["result"] = result;
        ModelState.Clear(); //clearing model
        return View();
    }
    else
    {
        ModelState.AddModelError("", "Error in saving data");
        return View();
    }
}

On cs.html

Added this to display message after saving data.

@{
    if (ViewData["result"] != "" && ViewData["result"] != null)
    {
        ViewData["result"] = null;
    <script type="text/javascript" language="javascript">
        alert("Data saved Successfully");
    </script>
    }
}

Run the application and insert records into it.

 

 

Now we have completed the Insert part.

Next we will create a basic grid view for displaying records.

For that we will add a new View but using the same Controller.

Let's begin with adding a View. A strongly typed View.

public ActionResult ShowAllMobileDetails(Mobiledata MB)
{
    return View();
}

After creating Action Result just right-click on ActionResult and select Add View.

And also check:

1. Create a strongly-typed view option
2. Use a layout or master page

Select the same Model as we used when creating InsertMobile.

Just click on the Add button.

A new View has been created.

After this in the Model I am adding new Properties as in the following:

public DataSet StoreAllData { get; set; }

For storing values in a dataset and displaying values from a dataset on the View.

Because in MVC you can access a complete Model in a View.

In ActionResult I will access DBdata and get the dataset and pass it to a model dataset name.

(StoreAlldata)

public ActionResult ShowAllMobileDetails(Mobiledata MB)
{
     DataAccessLayer.DBdata objDB = new DataAccessLayer.DBdata(); //calling class DBdata
     MB.StoreAllData = objDB.SelectAllData();
     return View(MB);
}

After this on the view I will display data from the dataset using a for loop.

And also adding the 2 linkbuttons Edit and Delete.

@Html.ActionLink("EDIT", "EDITMOBILEDATA", new { id = Model.StoreAllData.Tables[0].Rows[i]["MobileID"].ToString() })
@Html.ActionLink("Delete", "DELETEMOBILEDATA", new { id = Model.StoreAllData.Tables[0].Rows[i]["MobileID"].ToString() })

Example 

EDIT name of Button.

EDITMOBILEDATA is the name of the page; on a click it will redirect with id.

@model MymobilewalaMvc.Models.Mobiledata
@{
    ViewBag.Title = "ShowAllMobileDetails";
}
<h2>
    ShowAllMobileDetails</h2>
<table>
    <tr>
        <td>
            @Html.ActionLink("Add New Mobiles", "InsertMobile")
        </td>
    </tr>
</table>
@{
    for (int i = 0; i < Model.StoreAllData.Tables[0].Rows.Count; i++)
    {
        var MobileID = Model.StoreAllData.Tables[0].Rows[i]["MobileID"].ToString();
        var MobileName = Model.StoreAllData.Tables[0].Rows[i]["MobileName"].ToString();
        var MobileIMEno = Model.StoreAllData.Tables[0].Rows[i]["MobileIMEno"].ToString();
        var Mobileprice = Model.StoreAllData.Tables[0].Rows[i]["mobileprice"].ToString();
        var MobileManufacured = Model.StoreAllData.Tables[0].Rows[i]["mobileManufacured"].ToString();
    <table width="100%">
        <tr>
            <td>
                MobileID
            </td>
            <td>
                MobileName
            </td>
            <td>
                Mobile IMEI No
            </td>
            <td>
                Mobileprice
            </td>
            <td>
                Mobile Manufactured
            </td>
            <td>
                EDIT
            </td>
            <td>
                DELETE
            </td>
        </tr>
        <tr>
            <td>
                @MobileID
            </td>
            <td>
                @MobileName
            </td>
            <td>
                @MobileIMEno
            </td>
            <td>
                @Mobileprice
            </td>
            <td>
                @MobileManufacured
            </td>
            <td>
                @Html.ActionLink("EDIT", "EDITMOBILEDATA", new { id = Model.StoreAllData.Tables[0].Rows[i]["MobileID"].ToString() })
            </td>
            <td>
                @Html.ActionLink("Delete", "DELETEMOBILEDATA", new { id = Model.StoreAllData.Tables[0].Rows[i]["MobileID"].ToString() })
            </td>
        </tr>
        <tr>
            <td>
                @Html.ActionLink("Add New Mobiles", "InsertMobile")
            </td>
        </tr>
    </table>
    }
}

View of ShowAllMobileDetails:

 

Let's now add two new Views to the same controller.

1. EDITMOBILEDATA

In this ActionResult I have provided a string id to the method to receive an ID when I click on the Edit button.

After getting the ID, get data from the database depending on that id and display record.

public ActionResult EDITMOBILEDATA(string id)
{
    DataAccessLayer.DBdata objDB = new DataAccessLayer.DBdata();
    //calling class DBdata
    DataSet ds = objDB.SelectAllDatabyID(id);
    Mobiledata MB = new Mobiledata();
    MB.MobileID = Convert.ToInt32(ds.Tables[0].Rows[0]["MobileID"].ToString());
    MB.MobileName = ds.Tables[0].Rows[0]["MobileName"].ToString();
    MB.MobileIMEno = ds.Tables[0].Rows[0]["MobileIMEno"].ToString();
    MB.mobileprice = ds.Tables[0].Rows[0]["mobileprice"].ToString();
    MB.mobileManufacured = ds.Tables[0].Rows[0]["mobileManufacured"].ToString();
    return View(MB);
}

Just right-click on ActionResult and add a View of a strong type.

 

After adding the view I will design the View to edit and update data.

@model MymobilewalaMvc.Models.Mobiledata
@{
    ViewBag.Title = "EDITMOBILEDATA";
}
<h2>
    EDITMOBILEDATA</h2>
<table>
    <tr>
        <td>
            @Html.ActionLink("Show All Mobile List", "ShowAllMobileDetails")
        </td>
    </tr>
</table>
<br />
@using (Html.BeginForm())
{
    <table width="100%">
        <tr>
            <td colspan="2">
                @Html.HiddenFor(a => a.MobileID)
            </td>
        </tr>
        <tr>
            <td>
                @Html.LabelFor(a => a.MobileName)
            </td>
        </tr>
        <tr>
            <td>
                @Html.TextBoxFor(a => a.MobileName)
                @Html.ValidationMessageFor(a => a.MobileName)
            </td>
        </tr>
        <tr>
            <td>
                @Html.LabelFor(a => a.MobileIMEno)
            </td>
        </tr>
        <tr>
            <td>
                @Html.TextBoxFor(a => a.MobileIMEno)
                @Html.ValidationMessageFor(a => a.MobileIMEno)
            </td>
        </tr>
        <tr>
            <td>
                @Html.LabelFor(a => a.mobileManufacured)
            </td>
        </tr>
        <tr>
            <td>
                @Html.TextBoxFor(a => a.mobileManufacured)
                @Html.ValidationMessageFor(a => a.mobileManufacured)
            </td>
        </tr>
        <tr>
            <td>
                @Html.LabelFor(a => a.mobileprice)
            </td>
        </tr>
        <tr>
            <td>
                @Html.TextBoxFor(a => a.mobileprice)
                @Html.ValidationMessageFor(a => a.mobileprice)
            </td>
        </tr>
        <tr>
            <td colspan="2">
                <input id="Submit1" type="submit" value="Update" />
            </td>
        </tr>
    </table>
}
@{
    if (ViewData["resultUpdate"] != "" && ViewData["resultUpdate"] != null)
    {
        ViewData["resultUpdate"] = null;
    <script type="text/javascript" language="javascript">
        alert("Data Updated Successfully");
    </script>
    }
}

 

After clicking on the Edit Button from ShowAllmobileDetails.

Creating the same ActionResult of EDITMOBILEDATA with a model as a parameter for postdata.

[HttpPost]
public ActionResult EDITMOBILEDATA(Mobiledata MD)
{
     DataAccessLayer.DBdata objDB = new DataAccessLayer.DBdata(); //calling class DBdata
     string result = objDB.UpdateData(MD); // passing Value to DBClass from model
     ViewData["resultUpdate"] = result; // for dislaying message after updating data.
     return RedirectToAction("ShowAllMobileDetails", "Mobilestore");
}

This will post data when the user will click the Update Button.

 

Adding the last View to delete records.

2. DELETEMOBILEDATA

In this ActionResult I have given a string id to the method to receive an ID when I click on the Delete button.

1. After getting an ID get data from the database and depending on that id display complete records and then it is possible to delete records.

public ActionResult DELETEMOBILEDATA(string id)
{
    DataAccessLayer.DBdata objDB = new DataAccessLayer.DBdata();
    //calling class DBdata
    DataSet ds = objDB.SelectAllDatabyID(id);
    Mobiledata MB = new Mobiledata();
    MB.MobileID = Convert.ToInt32(ds.Tables[0].Rows[0]["MobileID"].ToString());
    MB.MobileName = ds.Tables[0].Rows[0]["MobileName"].ToString();
    MB.MobileIMEno = ds.Tables[0].Rows[0]["MobileIMEno"].ToString();
    MB.mobileprice = ds.Tables[0].Rows[0]["mobileprice"].ToString();
    MB.mobileManufacured = ds.Tables[0].Rows[0]["mobileManufacured"].ToString();
    return View(MB);
}

Right-click on Actionresult then select Add View.

 

After adding a Design View to show records when deleting.

@model MymobilewalaMvc.Models.Mobiledata
@{
    ViewBag.Title = "DELETEMOBILEDATA";
}
<h2>
    DELETEMOBILEDATA</h2>
<table>
    <tr>
        <td>
            @Html.ActionLink("Add New Mobiles", "InsertMobile")
        </td>
        <td>
            @Html.ActionLink("Show All Mobile List", "ShowAllMobileDetails")
        </td>
    </tr>
</table>
<br />
@using (Html.BeginForm())
{
    <table width="100%">
        <tr>
            <td colspan="2">
                @Html.HiddenFor(a => a.MobileID)
            </td>
        </tr>
        <tr>
            <td>
                MobileName :-
                @Html.DisplayFor(a => a.MobileName)
            </td>
        </tr>
        <tr>
            <td>
                MobileIMEI Number:-
                @Html.DisplayFor(a => a.MobileIMEno)
            </td>
        </tr>
        <tr>
            <td>
                Mobile Manufacured :-
                @Html.DisplayFor(a => a.mobileManufacured)
            </td>
        </tr>
        <tr>
            <td>
                Mobileprice :-
                @Html.DisplayFor(a => a.mobileprice)
            </td>
        </tr>
        <tr>
            <td colspan="2">
                <input id="Submit1" onclick="return confirm('Are you sure you want delete');" type="submit"
                    value="Delete" />
            </td>
        </tr>
    </table>
}

Creating the same ActionResult of DELETEMOBILEDATA with a model as a parameter for postdata.

[HttpPost]
public ActionResult DELETEMOBILEDATA(Mobiledata MD)
{
    DataAccessLayer.DBdata objDB = new DataAccessLayer.DBdata();
    //calling class DBdata
    string result = objDB.DeleteData(MD);
    return RedirectToAction("ShowAllMobileDetails", "Mobilestore");
}

This will post data when the user clicks the Delete Button.

 

Finally we are completed with insert, update, and delete in MVC.