CRUD Operation In ASP.NET Core 2.0 With Razor Pages Using ADO.NET

In this article, we will learn CRUD operation in ASP.NET Core 2.0 with Razor pages using ADO.NET step by step. 

We will use Visual Studio 2017 to develop the web application using ASP.NET Core 2.0 with Razor pages. If you don’t have a basic idea of ASP.NET Core and how to set up ASP.NET Core Environment, or want to know how to install Visual Studio 2017, please go to my previous article and click What ASP.NET Core Is And set up ASP.NET Core Environment.

Prerequisites

f

• Install .NET Core 2.0.0 or above SDK steps to install click on the link .NET Core 2.0.
• Install Visual Studio 2017 and steps to install click on the link Visual Studio 2017.
• SQL Server 2008 or above.

We will create one small "Group Meeting" web application using ASP.NET Core 2.0. as following. 

First of all, create a database script using SQL Server.

Scripts 1 

To create the database.

CREATE DATABASE ProjectMeeting

 

Scripts 2

To create the database table named as “GroupMeeting”.

USE [ProjectMeeting]
GO

/****** Object:  Table [dbo].[GroupMeeting]    Script Date: 27/07/2018 00:47:57 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[GroupMeeting](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [ProjectName] [varchar](50) NULL,
    [GroupMeetingLeadName] [varchar](50) NULL,
    [TeamLeadName] [varchar](50) NULL,
    [Description] [varchar](50) NULL,
    [GroupMeetingDate] [date] NULL,
 CONSTRAINT [PK_GroupMeeting-2] PRIMARY KEY CLUSTERED
(
    [Id] 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

Scripts 3

Create the stored procedure to get all the group meetings in detail. 

Create procedure [dbo].[GetGroupMeetingDetails]
AS
BEGIN
     SELECT * FROM GROUPMEETING
END

Scripts 4

Create the stored procedure to get the group meeting by Id.

Create procedure [dbo].[GetGroupMeetingByID](@Id int)
AS
BEGIN
     SELECT * FROM GROUPMEETING where id=@Id
END

Scripts 5

Create the stored procedure to create a new group meeting.

create procedure [dbo].[InsertGroupMeeting]
(
    @ProjectName varchar(50),
    @GroupMeetingLeadName varchar(50),
    @TeamLeadName varchar(50),
    @Description varchar(50),
    @GroupMeetingDate date
)
As
BEGIN

 INSERT INTO GroupMeeting(ProjectName,GroupMeetingLeadName,TeamLeadName,Description,GroupMeetingDate)
 VALUES(@ProjectName,@GroupMeetingLeadName,@TeamLeadName,@Description,@GroupMeetingDate)

END

Scripts 6

Create the stored procedure to update the group meeting.

create procedure [dbo].[UpdateGroupMeeting]
(
    @Id int,
    @ProjectName varchar(50),
    @GroupMeetingLeadName varchar(50),
    @TeamLeadName varchar(50),
    @Description varchar(50),
    @GroupMeetingDate date
)
As
BEGIN
     UPDATE GroupMeeting
     SET ProjectName =@ProjectName,
     GroupMeetingLeadName =@GroupMeetingLeadName,
     TeamLeadName = @TeamLeadName,
     Description = @Description,
     GroupMeetingDate =@GroupMeetingDate
     Where Id=@Id
END

Scripts 7

Create the stored procedure to delete the group meeting. 

create procedure [dbo].[DeleteGroupMeeting]
(
    @Id int
)
As
BEGIN
    DELETE FROM GroupMeeting WHERE Id=@Id
END

Now, we have completed our database related changes. Let’s go with the code base changes using Visual Studio 2017.

Step 1

Open Visual Studio 2017.

Step 2

Click on File => Open => New Project, as shown in the image.

 

Step 3

Select .NET Core from the left side and select the ‘ASP.NET Core Web Application’ from the new open project template. Then, provide the a meaningful name like “GroupMeetingASP.NETCoreWebApp”.

 

Step 4

Select Web Application(MVC) template from the list of templates and click on "OK" button as follows.

 

Step 5

The default ASP.NET Core MVC structure gets created as follows. The default model, view and controller folders get created with some default pages. So we will keep as it is the default controller and Views. I will add a new controller as per the requirement.

Step 6

Right click on Models => Click on Add => Click on Class.

 

Step 7

Provide a meaningful name like “GroupMeeting” and click on "Add" button as follow.

 

Step 8

Write code to create properties for group meeting class as follow. Also, use the required attribute to validate the class fields. Also, we have taken connection string in using string variable OR you can read from appSettings.json file.

public class GroupMeeting
    {
        public int GroupMeetingId { get; set; }
        [Required(ErrorMessage ="Enter Project Name!")]
        public string ProjectName { get; set; }

        [Required(ErrorMessage = "Enter Group Lead Name!")]
        public string GroupMeetingLeadName { get; set; }
        [Required(ErrorMessage = "Enter Team Lead Name!")]
        public string TeamLeadName { get; set; }
        [Required(ErrorMessage = "Enter Description!")]
        public string Description { get; set; }
        [Required(ErrorMessage = "Enter Group Meeting Date!")]
        public DateTime GroupMeetingDate { get; set; }

        static string strConnectionString = "User Id=sa;Password=Shri;Server=Shri\\SQL2014;Database=ProjectMeeting;";

}

Step 9

Write code to get all group meeting details from the database using the stored procedure. The method name is "GetGroupMeetings()" and return type is IEnumerable<GroupMeeting> or List<GroupMeeting> you can use either one of them. 

public static IEnumerable<GroupMeeting> GetGroupMeetings()
        {
            List<GroupMeeting> groupMeetingsList = new List<GroupMeeting>();
            using (SqlConnection con = new SqlConnection(strConnectionString))
            {
                SqlCommand command = new SqlCommand("GetGroupMeetingDetails", con);
                command.CommandType = CommandType.StoredProcedure;
                if (con.State == ConnectionState.Closed)
                    con.Open();

               SqlDataReader dataReader= command.ExecuteReader();
                while (dataReader.Read())
                {
                    GroupMeeting groupMeeting = new GroupMeeting();
                    groupMeeting.GroupMeetingId = Convert.ToInt32(dataReader["Id"]);
                    groupMeeting.ProjectName = dataReader["ProjectName"].ToString();
                    groupMeeting.GroupMeetingLeadName = dataReader["GroupMeetingLeadName"].ToString();
                    groupMeeting.TeamLeadName = dataReader["TeamLeadName"].ToString();
                    groupMeeting.Description = dataReader["Description"].ToString();
                    groupMeeting.GroupMeetingDate = Convert.ToDateTime(dataReader["GroupMeetingDate"]);
                    groupMeetingsList.Add(groupMeeting);
                }
            }
            return groupMeetingsList;
        }

Write code to get group meeting detail by groupId using ADO.NET as follows.

public static GroupMeeting GetGroupMeetingById(int? id)
        {
            GroupMeeting groupMeeting = new GroupMeeting();
            if (id == null)
                return groupMeeting;

            using (SqlConnection con = new SqlConnection(strConnectionString))
            {
                SqlCommand command = new SqlCommand("GetGroupMeetingByID", con);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.AddWithValue("@Id", id);
                if (con.State == ConnectionState.Closed)
                    con.Open();

                SqlDataReader dataReader = command.ExecuteReader();
                while (dataReader.Read())
                {
                    groupMeeting.GroupMeetingId = Convert.ToInt32(dataReader["Id"]);
                    groupMeeting.ProjectName = dataReader["ProjectName"].ToString();
                    groupMeeting.GroupMeetingLeadName = dataReader["GroupMeetingLeadName"].ToString();
                    groupMeeting.TeamLeadName = dataReader["TeamLeadName"].ToString();
                    groupMeeting.Description = dataReader["Description"].ToString();
                    groupMeeting.GroupMeetingDate = Convert.ToDateTime(dataReader["GroupMeetingDate"]);
                }
            }
            return groupMeeting;
        }

Step 10

Code to insert the group meeting into the database using ADO.NET is below.

public static int AddGroupMeeting(GroupMeeting groupMeeting)
        {
            int rowAffected = 0;
            using (SqlConnection con = new SqlConnection(strConnectionString))
            {
                SqlCommand command = new SqlCommand("InsertGroupMeeting", con);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.AddWithValue("@ProjectName", groupMeeting.ProjectName);
                command.Parameters.AddWithValue("@GroupMeetingLeadName", groupMeeting.GroupMeetingLeadName);
                command.Parameters.AddWithValue("@TeamLeadName", groupMeeting.TeamLeadName);
                command.Parameters.AddWithValue("@Description", groupMeeting.Description);
                command.Parameters.AddWithValue("@GroupMeetingDate", groupMeeting.GroupMeetingDate);

                if (con.State == ConnectionState.Closed)
                    con.Open();

                rowAffected = command.ExecuteNonQuery();
            }
            return rowAffected;
        }

Code to update the group meeting using ADO.NET is below.

public static int UpdateGroupMeeting(GroupMeeting groupMeeting)
        {
            int rowAffected = 0;
            using (SqlConnection con = new SqlConnection(strConnectionString))
            {
                SqlCommand command = new SqlCommand("UpdateGroupMeeting", con);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.AddWithValue("@Id", groupMeeting.GroupMeetingId);
                command.Parameters.AddWithValue("@ProjectName", groupMeeting.ProjectName);
                command.Parameters.AddWithValue("@GroupMeetingLeadName", groupMeeting.GroupMeetingLeadName);
                command.Parameters.AddWithValue("@TeamLeadName", groupMeeting.TeamLeadName);
                command.Parameters.AddWithValue("@Description", groupMeeting.Description);
                command.Parameters.AddWithValue("@GroupMeetingDate", groupMeeting.GroupMeetingDate);

                if (con.State == ConnectionState.Closed)
                    con.Open();

                rowAffected = command.ExecuteNonQuery();
            }
            return rowAffected;
        }

Code to delete the group meeting using ADO.NET is below.

public static int DeleteGroupMeeting(int id)
        {
            int rowAffected = 0;
            using (SqlConnection con = new SqlConnection(strConnectionString))
            {
                SqlCommand command = new SqlCommand("DeleteGroupMeeting", con);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.AddWithValue("@Id", id);

                if (con.State == ConnectionState.Closed)
                    con.Open();

                rowAffected = command.ExecuteNonQuery();
            }
            return rowAffected;
        }

As of now, we have completed the model related changes Get, Insert, Update, Delete as shown in the above code. After that, we will create a new controller and Razor Views.

Step 11

Right click on Controller folder => Click on “Add” => Click on Controller.

 

Step 12

Select "MVC Controller - Empty" and click on the "Add" button.

 

Provide a meaningful name like “GroupMeeting” as follow.

 

Step 13

After adding the controller, you need to import the required namespace. Then add the following code to get all group meeting details and pass to the view as follows. 

using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Mvc;
using GroupMeetingASP.NETCoreWebApp.Models;

namespace GroupMeetingASP.NETCoreWebApp.Controllers
{
    public class GroupMeetingController : Controller
    {
        public IActionResult Index()
        {
            return View(GroupMeeting.GetGroupMeetings());
        }
}
}

Step 14

Right click on ActionResult and click on Add Razor View.

 

Step 15

Write code for displaying group meeting data on Index.cshtml view as below.

@model IEnumerable<GroupMeetingASP.NETCoreWebApp.Models.GroupMeeting>
@{
    ViewData["Title"] = "Index";
}

<h4>Group Meeting Web App</h4><hr />
<h4>
    <a asp-action="AddGroupMeeting">Add GroupMeeting</a>
</h4>
<div>
    <table class="table table-responsive table-bordered panel-primary">
        <thead>
            <th>Project Name</th>
            <th>Group Lead Name</th>
            <th>Team Lead Name</th>
            <th>Description</th>
            <th>Meeting Date</th>
        <th></th>
        </thead>
        <tbody>
            @foreach (var item in Model)
            {
            <tr>
                <td>@Html.DisplayFor(model => item.ProjectName)</td>
                <td>@Html.DisplayFor(model => item.GroupMeetingLeadName)</td>
                <td>@Html.DisplayFor(model => item.TeamLeadName)</td>
                <td>@Html.DisplayFor(model => item.Description)</td>
                <td>@Html.DisplayFor(model => item.GroupMeetingDate)</td>
                <td>
                    <a asp-action="EditMeeting" asp-route-id="@item.GroupMeetingId">Edit</a>|
                    <a asp-action="DeleteMeeting" asp-route-id="@item.GroupMeetingId">Delete</a>
                </td>
            </tr>

            }
        </tbody>
    </table>
</div>

 Click on Startup.cs file and change the Controller name as "GroupMeeting" controller.

app.UseMvc(routes =>
           {
               routes.MapRoute(
                   name: "default",
                   template: "{controller=GroupMeeting}/{action=Index}/{id?}");
           });

 After changing the controller name in startup.cs file, click on IIS Express to run the application OR press F5.

 

 

 

Index page displays as below.

 

 Step 16

We will create “AddGroupMeeting” view in the GroupMeeting controller And write the following code. We have written two views - first is "HttpGet" and the second one is "HttpPost".

[HttpGet]
        public IActionResult AddGroupMeeting()
        {
            return View();
        }
        [HttpPost]
        public IActionResult AddGroupMeeting([Bind] GroupMeeting groupMeeting)
        {
            if (ModelState.IsValid)
            {
                if (GroupMeeting.AddGroupMeeting(groupMeeting) > 0)
                {
                    return RedirectToAction("Index");
                }
            }
            return View(groupMeeting);
        }

Step 16

We will create “AddGroupMeeting.cshtml” View and write the code to add the group meeting detail.

@model GroupMeetingASP.NETCoreWebApp.Models.GroupMeeting
@{
    ViewData["Title"] = "AddGroupMeeting";
}

<h2>Add Group Meeting</h2>
<div class="row">
    <div class="col-md-4">
        <form asp-action="AddGroupMeeting">
            <div class="">
                <label asp-for="ProjectName" class="control-label"></label>
                <input asp-for="ProjectName" class="form-control" />
                <span class="alert-danger" asp-validation-for="ProjectName"></span>
            </div>
            <div class="form-group">
                <label asp-for="GroupMeetingLeadName" class="control-label"></label>
                <input asp-for="GroupMeetingLeadName" class="form-control" />
                <span class="alert-danger" asp-validation-for="GroupMeetingLeadName"></span>
            </div>
            <div class="form-group">
                <label asp-for="TeamLeadName" class="control-label"></label>
                <input asp-for="TeamLeadName" class="form-control" />
                <span class="alert-danger" asp-validation-for="TeamLeadName"></span>
            </div>
            <div class="form-group">
                <label asp-for="Description" class="control-label"></label>
                <input asp-for="Description" class="form-control" />
                <span class="alert-danger" asp-validation-for="Description"></span>
            </div>
            <div class="form-group">
                <label asp-for="GroupMeetingDate" class="control-label"></label>
                <input asp-for="GroupMeetingDate" class="form-control" />
                <span class="alert-danger" asp-validation-for="GroupMeetingDate"></span>
            </div>
            <div class="form-group">
                <input type="submit" value="Create Meeting" class="btn btn-success btn-sm" />
            </div>
        </form>
    </div>
    <div class="col-md-8">

    </div>
</div>
<div>
    <a asp-action="Index">Back To Home</a>
</div>

"AddGroupMeeting" view displayed as below.

 

We have also added the validation using DataAnnotations on add group meeting page.

 

 

 

Enter the valid information in the group meeting page and click on “Create Meeting” button. Then it will redirect to the index view page.

Step 17

We will create “EditMeeting” view in the GroupMeeting Controller and write the code to edit the group meeting detail as follow. There are two views of EditMeeting "HttpGet" and "HttpPost". The "HttpGet" edit meeting view having id as a parameter gets called when clicking on "Edit" button on index view page. 

[HttpGet]
        public IActionResult EditMeeting(int? id)
        {
            if (id == null)
            {
                return NotFound();
            }
            GroupMeeting group = GroupMeeting.GetGroupMeetingById(id);
            if (group == null)
                return NotFound();
            return View(group);
        }

        [HttpPost]
        public IActionResult EditMeeting(int id, [Bind] GroupMeeting groupMeeting)
        {
            if (id != groupMeeting.GroupMeetingId)
                return NotFound();

            if (ModelState.IsValid)
            {
                GroupMeeting.UpdateGroupMeeting(groupMeeting);
                return RedirectToAction("Index");
            }
            return View(groupMeeting);
        }

Click on "Edit" button on index view page as follows:

 

It will open the following page when you click on "edit" button. Then the group meeting will get updated into the database and redirected to the index view page.

 

 

Step 18

We will create “DeleteMeeting” view in the GroupMeeting Controller and write the code to edit the group meeting detail as follows.

[HttpGet]
       public IActionResult DeleteMeeting(int id)
       {
           GroupMeeting group = GroupMeeting.GetGroupMeetingById(id);
           if (group==null)
           {
               return NotFound();
           }

           return View(group);
       }
       [HttpPost]
       public IActionResult DeleteMeeting(int id,GroupMeeting groupMeeting)
       {
           if (GroupMeeting.DeleteGroupMeeting(id) > 0)
           {
               return RedirectToAction("Index");
           }
           return View(groupMeeting);
       }

Step 18

Write the “DeleteMeeting.cshtml” view page code as follow.

@model GroupMeetingASP.NETCoreWebApp.Models.GroupMeeting
@{
    ViewData["Title"] = "DeleteMeeting";
}

<h3 class="alert">Are you sure you want to delete this?</h3>
<div>
    <dl class="dl-horizontal">
        <dt>
            @Html.DisplayNameFor(model => model.ProjectName)
        </dt>
        <dd>
            @Html.DisplayFor(model => model.ProjectName)
        </dd>
        <dt>
            @Html.DisplayNameFor(model => model.GroupMeetingLeadName)
        </dt>
        <dd>
            @Html.DisplayFor(model => model.GroupMeetingLeadName)
        </dd>
        <dt>
            @Html.DisplayNameFor(model => model.TeamLeadName)
        </dt>
        <dd>
            @Html.DisplayFor(model => model.TeamLeadName)
        </dd>
        <dt>
            @Html.DisplayNameFor(model => model.Description)
        </dt>
        <dd>
            @Html.DisplayFor(model => model.Description)
        </dd>
        <dt>
            @Html.DisplayNameFor(model => model.GroupMeetingDate)
        </dt>
        <dd>
            @Html.DisplayFor(model => model.GroupMeetingDate)
        </dd>
    </dl>

    <form asp-action="DeleteMeeting">
        <input type="hidden" asp-for="GroupMeetingId" />
        <input type="submit" value="YES" class="btn btn-danger btn-sm" /> <a asp-action="Index" class="btn btn-danger">NO</a>
    </form>
</div>

Run the appliation and the defualt index view page will open. Then click on delete link as follows.

 

 

After clicking on “delete” button on the index view page, the following page will open for confirmation to delete the group meeting.

After that click on “Yes” button, the selected group meeting will get deleted from the list of group meetings and the page will redirect to the index view page and the page will look as follows.

I hope you understand the basic concepts of CRUD operations using ASP.NET Core 2.0 with Razor view pages using ADO.NET.