Insert Data into DataBase using ASP.NET MVC with ADO.NET

There are many techniques in ASP.NET MVC to insert the data into the database including ORM such as Entity framework, Dapper etc and database access technology such as ADO.NET. In this article we will learn how to insert the data into the database using ADO.NET in ASP.NET MVC application.

Step 1 : Create an ASP.NET MVC Application.

Now let us start with a step by step approach from the creation of simple MVC application as in the following:
  1. "Start", then "All Programs" and select "Microsoft Visual Studio 2015".
  2. "File", then "New" and click "Project..." then select "ASP.NET Web Application Template", then provide the Project a name as you wish and click on OK. After clicking, the following window will appear:
  As shown in the preceding screenshot, click on Empty template and check the MVC option, then click OK. This will create an empty MVC web application.

Step 2: Create Model Class

Now let us create the model class named EmpModel.cs by right clicking on the model folder and write the model properties inside the EmpModel.cs class. After adding the properties, the class will look like as follows.
EmpModel.cs
public class EmpModel  
  {  
      [Display(Name = "Id")]  
      public int Empid { get; set; }  
  
      [Required(ErrorMessage = "First name is required.")]  
      public string Name { get; set; }  
  
      [Required(ErrorMessage = "City is required.")]  
      public string City { get; set; }  
  
      [Required(ErrorMessage = "Address is required.")]  
      public string Address { get; set; }  
  
  }  
In the above model class we have added some validation on properties with the help of Data Annotations.

Step 3:  Create Controller.

Now let us add the MVC 5 controller as in the following screenshot:


After clicking on the Add button, it will show the following window. Now specify the Controller name as an Employee with the suffix Controller. It will add the empty controller class.

Step 4 : Create Table and Stored procedures.

Now before creating the view, let us create the table name Employee in the database according to our model fields to store the details.


Now create the stored procedures to insert employee details into the database. The code snippet will look like the following.
Create procedure [dbo].[AddNewEmpDetails]  
(  
   @Name varchar (50),  
   @City varchar (50),  
   @Address varchar (50)  
)  
as  
begin  
   Insert into Employee values(@Name,@City,@Address)  
End  

Step 5 : Modify the EmployeeController.cs file.

Now open the EmployeeController.cs and create the action methods and database insert logic, after modifying the EmployeeController.cs class, the code snippet will look like as follows.

EmployeeController.cs

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using CRUDUsingMVC.Models;
using System.Linq;
using System.Web.Mvc;
namespace UsingMVC
{

public class EmployeeController : Controller    
    {    
          

        private SqlConnection con;
        //To Handle connection related activities
        private void connection()
        {
            string constr = ConfigurationManager.ConnectionStrings["getconn"].ToString();
            con = new SqlConnection(constr);

        }
          // GET: Employee/AddEmployee    
        public ActionResult AddEmployee()    
        {    
            return View();    
        }    
      
        // POST: Employee/AddEmployee    
        [HttpPost]    
        public ActionResult AddEmployee(EmpModel Emp)    
        {    
            try    
            {    
                if (ModelState.IsValid)    
                {    
                        
      
                    if (AddEmployee(Emp))    
                    {    
                        ViewBag.Message = "Employee details added successfully";    
                    }    
                }    
                  
                return View();    
            }    
            catch    
            {    
                return View();    
            }    
        }    
      
      //To Add Employee details
        public bool AddEmployee(EmpModel obj)
        {

            connection();
            SqlCommand com = new SqlCommand("AddNewEmpDetails", con);
            com.CommandType = CommandType.StoredProcedure;
            com.Parameters.AddWithValue("@Name", obj.Name);
            com.Parameters.AddWithValue("@City", obj.City);
            com.Parameters.AddWithValue("@Address", obj.Address);        
            con.Open();
            int i = com.ExecuteNonQuery();
            con.Close();
            if (i >= 1)
            {

                return true;

            }
            else
            {

                return false;
            }
        }
              
    } 
}       

Step 6: Create strongly typed view.

To create the view to add Employees, right click on ActionResult method and then click Add view. Now specify the view name, template name and model class in EmpModel.cs and click on the Add button. It will create the view named AddEmployee.cshtml, now open the AddEmployee.cshtml and modify the code as per our model class properties.

 AddEmployee.cshtml
@model UsingMVC.Models.EmpModel  
@using (Html.BeginForm())  
{  
    @Html.AntiForgeryToken()  
    <div class="form-horizontal">  
        <h4>Add Employee</h4>  
        <div>  
            @Html.ActionLink("Back to Employee List", "GetAllEmpDetails")  
        </div>  
        <hr />  
        @Html.ValidationSummary(true, "", new { @class = "text-danger" })  
  
  
        <div class="form-group">  
            @Html.LabelFor(model => model.Name, htmlAttributes: new { @class = "control-label col-md-2" })  
            <div class="col-md-10">  
                @Html.EditorFor(model => model.Name, new { htmlAttributes = new { @class = "form-control" } })  
                @Html.ValidationMessageFor(model => model.Name, "", new { @class = "text-danger" })  
            </div>  
        </div>  
  
        <div class="form-group">  
            @Html.LabelFor(model => model.City, htmlAttributes: new { @class = "control-label col-md-2" })  
            <div class="col-md-10">  
                @Html.EditorFor(model => model.City, new { htmlAttributes = new { @class = "form-control" } })  
                @Html.ValidationMessageFor(model => model.City, "", new { @class = "text-danger" })  
            </div>  
        </div>  
  
        <div class="form-group">  
            @Html.LabelFor(model => model.Address, htmlAttributes: new { @class = "control-label col-md-2" })  
            <div class="col-md-10">  
                @Html.EditorFor(model => model.Address, new { htmlAttributes = new { @class = "form-control" } })  
                @Html.ValidationMessageFor(model => model.Address, "", new { @class = "text-danger" })  
            </div>  
        </div>  
  
        <div class="form-group">  
            <div class="col-md-offset-2 col-md-10">  
                <input type="submit" value="Save" class="btn btn-default" />  
            </div>  
        </div>  
        <div class="form-group">  
            <div class="col-md-offset-2 col-md-10" style="color:green">  
                @ViewBag.Message  
  
            </div>  
        </div>  
    </div>  
  
}  
  
<script src="~/Scripts/jquery-1.10.2.min.js"></script>  
<script src="~/Scripts/jquery.validate.min.js"></script>  
<script src="~/Scripts/jquery.validate.unobtrusive.min.js"></script>

Step 7: Run the Application

After running the application, type the appropriate values into the textboxes and click the save button. It will insert the records into the database as shown in the following image.


From the preceding examples, we have learned how to insert data into the database in ASP.NET MVC using ADO.NET.

Note:
  • Configure the database connection in the web.config file depending on your database server location.
  • Since this is a demo, it might not be using proper standards, so improve it depending on your skills
  • This application is created completely focusing on beginners.

Summary

I hope this article is useful for all the readers. If you have any suggestions, please contact me.

Don't Forget To 

1 comments:

Post a Comment

www.CodeNirvana.in

Protected by Copyscape
Copyright © Compilemode