Saturday, 26 November 2016

Part 11 - Insert data Into database in as Asp.net MVC application

TechnoGTB November 26, 2016 asp.net mvc tutorial, CRUD, Insert Record

In this video, You will find the way to insert data by submitting a form. You can submit your form either directly or by using Jquery (see example Insert data using Jquery ). Direct submission results in loading complete page. Now a days, every website using a popup to insert/ update/delete record as its easier to save multiple record without navigating to different page. Bootstrap has provided an attractive popup or modal which is pretty cool and easy to use ( see example Create popup with bootstrap )
Note: Before copying below code, I recommend you to watch above video completely.

#Controller Code

Add a controller named "Test" and replace everything with below code. In below code, you will find two methods
a) Index () : This method will return department list which will be shown in dropdownlist.

b) SaveRecord(EmployeeViewModel model): This method will insert employee record in Employee table and return to index page after loading department list into ViewBag.

using MVCTutorial.Models;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;

namespace MVCTutorial.Controllers
{
    public class TestController : Controller
    {
        public ActionResult Index()
        {

            MVCTutorialEntities db = new MVCTutorialEntities();
            List<Department> list = db.Departments.ToList();
            ViewBag.DepartmentList = new SelectList(list, "DepartmentId", "DepartmentName");
            return View();

        }

        [HttpPost]

        public ActionResult SaveRecord(EmployeeViewModel model)
        {
            try
            {
                MVCTutorialEntities db = new MVCTutorialEntities();

                List<Department> list = db.Departments.ToList();

                ViewBag.DepartmentList = new SelectList(list, "DepartmentId", "DepartmentName");

                Employee emp = new Employee();
                emp.Address = model.Address;
                emp.Name = model.Name;
                emp.DepartmentId = model.DepartmentId;
                db.Employees.Add(emp);
                db.SaveChanges();
                int latestEmpId = emp.EmployeeId;
                return RedirectToAction("Index");
            }

            catch (Exception ex)
            {
                throw ex;

            }
        }
    }
}

#Model code (EmployeeViewModel)

Right click on your model folder and add a class. Give name"EmployeeViewModel" and click ok button. Replace content with below code.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

public class EmployeeViewModel
{
    public int EmployeeId { get; set; }
    public string Name { get; set; }
    public Nullable<int> DepartmentId { get; set; }
    public string Address { get; set; }
    //Custom attribute

    public string DepartmentName { get; set; }
    public bool Remember { get; set; }

}

# View Page (Index.cshtml)

Right click on your controller' s Index method and add a view. After adding view, replace content with below code. Here you can see that how the department list is loaded into dropdown

@model MVCTutorial.Models.EmployeeViewModel
@{

    ViewBag.Title = "Index";

    Layout = null;

}
<link href="~/Content/bootstrap.min.css" rel="stylesheet" />
<div class="container" style="width:40%;margin-top:2%">

    @using (Html.BeginForm("SaveRecord", "Test", FormMethod.Post))
    {
        @Html.DropDownListFor(model => model.DepartmentId, ViewBag.DepartmentList as SelectList, "--select--", new { @class = "form-control" })
        @Html.TextBoxFor(model => model.Name, new { @class = "form-control", @placeholder = "Name" })

        @Html.TextBoxFor(model => model.Address, new { @class = "form-control", @placeholder = "Address" })

        <input type="submit" value="Submit" class="btn btn-block btn-primary" />

    }

</div>

#Database Script

Add a database named "MVCTutorial" in your local db. Then execute below script.

 USE [MVCTutorial]
GO
/****** Object:  Table [dbo].[Department]    Script Date: 27-11-2016 00:28:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Department](
 [DepartmentId] [int] IDENTITY(1,1) NOT NULL,
 [DepartmentName] [nvarchar](100) NULL,
 CONSTRAINT [PK_Department] PRIMARY KEY CLUSTERED 
(
 [DepartmentId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[Employee]    Script Date: 27-11-2016 00:28:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Employee](
 [EmployeeId] [int] IDENTITY(1,1) NOT NULL,
 [Name] [varchar](50) NULL,
 [DepartmentId] [int] NULL,
 [Address] [varchar](150) NULL,
 CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED 
(
 [EmployeeId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
/****** Object:  Table [dbo].[Sites]    Script Date: 27-11-2016 00:28:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Sites](
 [SiteId] [int] IDENTITY(1,1) NOT NULL,
 [EmployeeId] [int] NULL,
 [SiteName] [nvarchar](150) NULL,
 CONSTRAINT [PK_Sites] PRIMARY KEY CLUSTERED 
(
 [SiteId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
SET IDENTITY_INSERT [dbo].[Department] ON 

GO
INSERT [dbo].[Department] ([DepartmentId], [DepartmentName]) VALUES (1, N'IT')
GO
INSERT [dbo].[Department] ([DepartmentId], [DepartmentName]) VALUES (2, N'QA')
GO
INSERT [dbo].[Department] ([DepartmentId], [DepartmentName]) VALUES (3, N'Development ')
GO
INSERT [dbo].[Department] ([DepartmentId], [DepartmentName]) VALUES (4, N'Marketing')
GO
SET IDENTITY_INSERT [dbo].[Department] OFF
GO
SET IDENTITY_INSERT [dbo].[Employee] ON 

GO
INSERT [dbo].[Employee] ([EmployeeId], [Name], [DepartmentId], [Address]) VALUES (1, N'Ashish', 1, N'India')
GO
INSERT [dbo].[Employee] ([EmployeeId], [Name], [DepartmentId], [Address]) VALUES (2, N'John', 2, N'London')
GO
INSERT [dbo].[Employee] ([EmployeeId], [Name], [DepartmentId], [Address]) VALUES (3, N'Methew', 3, N'NewYork')
GO
INSERT [dbo].[Employee] ([EmployeeId], [Name], [DepartmentId], [Address]) VALUES (4, N'Brano', 4, N'France')
GO
INSERT [dbo].[Employee] ([EmployeeId], [Name], [DepartmentId], [Address]) VALUES (5, N'Smith', 1, N'London')
GO
INSERT [dbo].[Employee] ([EmployeeId], [Name], [DepartmentId], [Address]) VALUES (6, N'Sara', 4, N'New york')
GO
SET IDENTITY_INSERT [dbo].[Employee] OFF
GO
SET IDENTITY_INSERT [dbo].[Sites] ON 

GO
INSERT [dbo].[Sites] ([SiteId], [EmployeeId], [SiteName]) VALUES (1, 1005, N'google.com')
GO
INSERT [dbo].[Sites] ([SiteId], [EmployeeId], [SiteName]) VALUES (2, 1006, N'www.technotips.com')
GO
SET IDENTITY_INSERT [dbo].[Sites] OFF
GO
ALTER TABLE [dbo].[Employee]  WITH CHECK ADD  CONSTRAINT [FK_Employee_Department] FOREIGN KEY([DepartmentId])
REFERENCES [dbo].[Department] ([DepartmentId])
GO
ALTER TABLE [dbo].[Employee] CHECK CONSTRAINT [FK_Employee_Department]
GO
ALTER TABLE [dbo].[Sites]  WITH CHECK ADD  CONSTRAINT [FK_Sites_Employee] FOREIGN KEY([EmployeeId])
REFERENCES [dbo].[Employee] ([EmployeeId])
GO
ALTER TABLE [dbo].[Sites] CHECK CONSTRAINT [FK_Sites_Employee]
GO

All Code Factory

About TechnoGTB
alistarbot is a blogger resources site is a provider of high quality blogger template with premium looking layout and robust design. The main mission of alistarbot is to provide the best quality blogger templates.