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

There are many techniques in ASP.NET MVC to insert the data into 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 database using ADO.NET in ASP.NET MVC application
Step 1 : Create an 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 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 model folder and write the model properties inside the EmpModel.cs class after adding the properties the class will be 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 Add button it will show the following window. Now specify the Controller name as Employee with 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 database according to our model fields to store the details

Now create the stored procedures to insert employee details into database , The code snippet will be look like as 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 the code snippet will be 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)    
                {    
                    EmpRepository EmpRepo = new EmpRepository();    
      
                    if (EmpRepo.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 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 on 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 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 

Post a Comment

www.CodeNirvana.in

Protected by Copyscape Online Copyright Protection
Copyright © Compile Mode