CRUD Screen using ASP.NET MVC, dapper and jTable in 15 Minutes

• Download source code from GitHub

Introduction

The most requested feature in any business application is a CRUD screen to enter master or even transaction data. I have been working with ASP.NET MVC for a few years now and the easiest way to have a working CRUD screen in under 20-30 minutes is using jTable, C# POCO classes and Dapper .

jTable is an awesome jQuery plug in developed by Halil Ibrahim Kalkan which can auto generate HTML tables and modal dialogs for editing rows using jQuery UI without hand coding HTML or JavaScript.

In the course of the article, I will show you how easy it is to create a working CRUD interface with ASP.NET MVC using jTable. Let us say the requirement is to create a simple CRUD for a list of Patient Names with Height (in cm) and date of birth.

Prerequisites

This article assumes that you have prior knowledge of ASP.NET MVC, jQuery and T-Sql. You will need Visual Studio Express/Professional 2010 and above with ASP.NET MVC installed along with SQL Server Express or Professional edition.

Getting Started - Design Your Table Structure and handcode SQL Scripts for CRUD

Open SQL Server Management Studio and create a Table Patients with Id, PatientName, Height and DOB columns.

Dapper is a micro-orm and needs SQL scripts to be handcoded. There are Nuget packages like Dapper Rainbow which will autogenerate SQL scripts like EF but for the sake of simplicity, let's handcode SQL for now:

//INSERT
INSERT INTO Patients(PatientName,Height,DOB) Values(@PatientName,@Height,@DOB)

//UPDATE
UPDATE Patients SET PatientName=@PatientName,Age=@Height,DOB=@DOB WHERE Id=@Id

//DELETE
DELETE FROM Patients WHERE Id=@Id

//SELECT
SELECT * FROM Patients

Setup Your Visual Studio Project

Open Visual Studio and start a New Project using an empty ASP.NET MVC 5 project template. Install nuget packages for jTable and Dapper from Package Manager Console.

Install-Package Dapper

From <https://www.nuget.org/packages/Dapper/>

Install-Package jTable

From <https://www.nuget.org/packages/jTable/>

Create a Model and a Repository for your CRUD App

Once you have your project setup, create a Model which mimics the table structure for patients:

public class Patient
{
        public int Id { get; set; }
        public string PatientName { get; set; }
        public int Height { get; set; }
        public DateTime DOB { get; set; }
}

Create a Repository using the CRUD Scripts:

        public List<Patient> GetAllRecords()
        {
            //SELECT
            string selectSql = "SELECT * FROM Patients";
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();

                var records = connection.Query<Patient>(selectSql);

                return records.ToList();
            }  
        }      

        public bool Update(Patient patient)
        {
            //UPDATE
            string updateSql = "UPDATE Patients SET PatientName=@PatientName,
                                Height=@Height,DOB=@DOB WHERE Id=@Id";
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();

                return connection.Execute(updateSql, new { patient.PatientName, 
                                          patient.Height, patient.DOB, patient.Id }) > 0;
            }  
        }

        public int Insert(Patient patient)
        {
            //INSERT
            string insertSql = "INSERT INTO Patients(PatientName,Height,DOB) 
                               Values(@PatientName,@Height,@DOB);
                               SELECT CAST(SCOPE_IDENTITY() as int)";
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                return connection.Query<int>(insertSql, new {  patient.PatientName,  
                                                        patient.Height, patient.DOB }).Single();
            }  
        }       

        public bool Delete(int id)
        {
            //DELETE
            string deleteSql = "DELETE FROM Patients WHERE Id=@id";
            using (SqlConnection connection = new SqlConnection(connectionString))
            {
                connection.Open();
                return connection.Execute(deleteSql, new { id }) > 0;
            }  
        }

Create a Crud Controller and Bind It to a View using jTable

Almost done! Just wire up the controller with the repository and bind it to a view using jTable. And your CRUD App is ready.

<div id="Container"></div>

<script type="text/javascript">
    $(document).ready(function () {
        $('#Container').jtable({
            title: 'Patient List',
            paging: false,
            actions: {
                listAction: '@Url.Action("GetAllRecords")',
                updateAction: '@Url.Action("Update")',
                createAction: '@Url.Action("Insert")',
                deleteAction: '@Url.Action("Delete")'
            },
            fields: {
                Id: {
                    key: true,
                    create: false,
                    edit: false,
                    list: false
                },
                PatientName: {
                    title: 'Patient Name',
                    width: '23%'
                },
                Height: {
                    title: 'Height(in cm)',
                },
                DOB: {
                    title: 'Birth date',
                    width: '15%',
                    type: 'date',
                    displayFormat: 'yy-mm-dd'
                }
            }
        });
        //Load patient list from server
        $('#Container').jtable('load');
    });
</script>

Conclusion

You have the base framework ready for your CRUD app. You can enhance this CRUD app adding paged results, filters and master-child tables if needed. Please check http://jtable.org/Demo for a complete list of available options.