Donate

ASP.NET MVC Web Application CRUD Using RepoDB ORM

-
Happy Sunday Awesome Programmers!

See the ASP.NET Core MVC version of this tutorial here Create An ASP.NET Core MVC CRUD Website Using RepoDB ORM And SQL Server

A week ago, I received a newsletter regarding a promising ORM called RepoDB. This ORM library is created by Michael Pendon who is an application architect in Europe. According to him, RepoDB is an open-source . NET ORM library that bridges the gaps of micro-ORMs and full-ORMs. It helps you simplify the switch-over of when to use the BASIC and ADVANCE operations during the development. It is your best alternative ORM to both Dapper and EntityFramework. It also provides query support to several databases such as MySQL, SQL Server, PostGreSQL and there's a survey that this ORM's performance is faster than Dapper. I've used Dapper before and Entity framework in my ASP.NET MVC applications so, it's time to try using this tool by creating a simple ASP.NET MVC CRUD Application(Create, Read, Update, Delete) Application. For the database part, create a ficticious table in your db called BookDetails.
USE [DemoDB]
GO

/****** Object:  Table [dbo].[BookDetails]    Script Date: 11/2/2020 12:26:53 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[BookDetails](
	[BookSerialNo] [int] IDENTITY(1,1) NOT NULL,
	[BookISBN] [nchar](15) NULL,
	[BookTitle] [varchar](120) NULL,
	[BookAuthor] [varchar](60) NULL,
	[BookPublisher] [varchar](50) NULL,
	[BookCategory] [varchar](20) NULL,
PRIMARY KEY CLUSTERED 
(
	[BookSerialNo] 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
Next is to create an ASP.NET MVC project and add via NuGet the RepoDb.SqlServer ORM. This library specifically targets the SQL Server database.
ASP.NET MVC CRUD (Create, Read, Update, Delete) Application Using RepoDB ORM
After that, call the bootstrapper to initialize all dependencies for SQL Server in Global.asax.cs Application_Start() method. 
protected void Application_Start()
{
	AreaRegistration.RegisterAllAreas();
	RouteConfig.RegisterRoutes(RouteTable.Routes);
	RepoDb.SqlServerBootstrap.Initialize();
}
Add a BookDetails model class in your project with attributes that would match the db schema. 
public class BookDetails
{
	public int BookSerialNo { get; set; }

	[Required]
	[Display(Name = "ISBN")]
	public string BookISBN { get; set; }

	[Required]
	[Display(Name = "Title")]
	public string BookTitle { get; set; }

	[Required]
	[Display(Name = "Author")]
	public string BookAuthor { get; set; }

	[Required]
	[Display(Name = "Publisher")]
	public string BookPublisher { get; set; }

	[Required]
	[Display(Name = "Category")]
	public string BookCategory { get; set; }
}
Add an interface called IBookDetailsRepository that defines the Data Access and CRUD (Create, Read, Update, Delete) functionalities. 
public interface IBookDetailsRepository
{
	List<BookDetails> GetAll();
	BookDetails FindById(int BookSerialNo);
	bool AddBookDetail(BookDetails book);
	bool UpdateBookDetail(BookDetails book);
	bool DeleteBookDetail(int BookSerialNo);
}
Then define a class that implements the methods of interface. This class contains the actual CRUD statements and uses the fluent syntax of the ORM. Make sure to add the using RepoDb; statement to be able to access the underlying methods of the ORM. 
public class BookDetailsRepository : IBookDetailsRepository
{
	private readonly string connectionString;

	public BookDetailsRepository()
	{
		this.connectionString = ConfigurationManager.ConnectionStrings["BookApplication"].ConnectionString;
	}

	public bool AddBookDetail(BookDetails book)
	{
		int serialNumber = 0;

		try
		{
			using (var connection = new SqlConnection(this.connectionString))
			{
				var item = new BookDetails()
				{
					BookAuthor = book.BookAuthor,
					BookCategory = book.BookCategory,
					BookISBN = book.BookISBN,
					BookTitle = book.BookTitle,
					BookPublisher = book.BookPublisher
				};

				serialNumber = (int)connection.Insert<BookDetails>(item);
			}
		}
		catch (Exception ex)
		{
			return false;
		}

		return true;
	}

	public bool DeleteBookDetail(int BookSerialNo)
	{
		int affectedRows = 0;

		try
		{
			using (var connection = new SqlConnection(this.connectionString))
			{
				affectedRows = (int)connection.Delete<BookDetails>(BookSerialNo);
			}
		}
		catch (Exception ex)
		{
			return false;
		}

		return true;
	}

	public BookDetails FindById(int BookSerialNo)
	{
		BookDetails result;

		result = new BookDetails();

		try
		{
			using (var connection = new SqlConnection(this.connectionString))
			{
				result = connection.Query<BookDetails>(x => x.BookSerialNo == BookSerialNo).FirstOrDefault();
			}
		}
		catch (Exception ex)
		{
			return null;
		}

		return result;
	}

	public List<BookDetails> GetAll()
	{
		List<BookDetails> lstBooks;

		lstBooks = new List<BookDetails>();

		try
		{
			using (var connection = new SqlConnection(this.connectionString))
			{
				connection.Open();
				lstBooks = connection.QueryAll<BookDetails>().ToList();
			}
		}
		catch (Exception ex)
		{
			return null;
		}

		return lstBooks;
	}

	public bool UpdateBookDetail(BookDetails book)
	{
		int affectedRows = 0;

		try
		{
			using (var connection = new SqlConnection(this.connectionString))
			{
				var item = new BookDetails()
				{
					BookSerialNo = book.BookSerialNo,
					BookAuthor = book.BookAuthor,
					BookCategory = book.BookCategory,
					BookISBN = book.BookISBN,
					BookTitle = book.BookTitle,
					BookPublisher = book.BookPublisher
				};

				affectedRows = (int)connection.Update<BookDetails>(item);
			}
		}
		catch (Exception ex)
		{
			return false;
		}

		return true;
	}
}
Add a service class that calls the repository methods. This will be the intermediary between the controller and repository classes. 
public class BookService
{
	private IBookDetailsRepository _repository;

	public BookService()
	{
		_repository = new BookDetailsRepository();
	}

	public List<BookDetails> GetAll()
	{
		return _repository.GetAll();
	}

	public BookDetails FindById(int SerialNo)
	{
		return _repository.FindById(SerialNo);
	}

	public bool AddBookDetails(BookDetails item)
	{
		return _repository.AddBookDetail(item);
	}

	public bool UpdateBookDetails(BookDetails item)
	{
		return _repository.UpdateBookDetail(item);
	}

	public bool DeleteBookDetail(int SerialNo)
	{
		return _repository.DeleteBookDetail(SerialNo);
	}
}
As stated above, the controller class instantiates the service class and calls its methods in the controller ActionResult functions. 
public class BooksController : Controller
{
	private BookService _service;

	public BooksController()
	{
		_service = new BookService();
	}
	
	public ActionResult Index()
	{
		var model = _service.GetAll();
		return View(model);
	}

	public ActionResult Create()
	{
		return View();
	}

	[HttpPost]
	public ActionResult Create(BookDetails book)
	{
		try
		{
			_service.AddBookDetails(book);
			return RedirectToAction("Index");
		}
		catch
		{
			return View();
		}
	}

	public ActionResult Edit(int id)
	{
		var model = _service.FindById(id);
		return View(model);
	}

	[HttpPost]
	public ActionResult Edit(BookDetails book)
	{
		try
		{
			_service.UpdateBookDetails(book);
			return RedirectToAction("Index");
		}
		catch
		{
			return View();
		}
	}

	public ActionResult Delete(int id)
	{
		var model = _service.FindById(id);
		return View(model);
	}

	[HttpPost]
	public ActionResult Delete(BookDetails book)
	{
		try
		{
			_service.DeleteBookDetail(book.BookSerialNo);
			return RedirectToAction("Index");
		}
		catch
		{
			return View();
		}
	}
}
The views that the user will interact with are the following.
Index.cshtml 
@model IEnumerable<RepoDB.Models.BookDetails>

@{
    ViewBag.Title = "Index";
}

<br />
<p>
    @Html.ActionLink("Create New", "Create", null, new { @class = "btn btn-primary" })
</p>
<div class="table-responsive">
    <table id="tblBooks" class="table table-bordered table-condensed table-striped">
        <tr>
            <th>
                @Html.Label("ISBN")
            </th>
            <th>
                @Html.Label("Title")
            </th>
            <th>
                @Html.Label("Author")
            </th>
            <th>
                @Html.Label("Publisher")
            </th>
            <th>
                @Html.Label("Category")
            </th>
            <th></th>
        </tr>

        @foreach (var item in Model)
        {
            <tr>
                <td>
                    @Html.DisplayFor(modelItem => item.BookISBN)
                </td>
                <td>
                    @Html.DisplayFor(modelItem => item.BookTitle)
                </td>
                <td>
                    @Html.DisplayFor(modelItem => item.BookAuthor)
                </td>
                <td>
                    @Html.DisplayFor(modelItem => item.BookPublisher)
                </td>
                <td>
                    @Html.DisplayFor(modelItem => item.BookCategory)
                </td>
                <td>
                    @Html.ActionLink("Edit", "Edit", new { id = item.BookSerialNo }, new { @class = "btn btn-info" })
                    @Html.ActionLink("Delete", "Delete", new { id = item.BookSerialNo }, new { @class = "btn btn-warning" })
                </td>
            </tr>
        }

    </table>
</div>
Create.cshtml 
@model RepoDB.Models.BookDetails

@{
    ViewBag.Title = "Create";
}

@using (Html.BeginForm())
{
    @Html.AntiForgeryToken()

    <div class="form-horizontal">
        <h4>New Book</h4>
        <hr />
        @Html.ValidationSummary(true, "", new { @class = "text-danger" })
        <div class="form-group">
            @Html.LabelFor(model => model.BookISBN, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.BookISBN, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.BookISBN, "", new { @class = "text-danger" })
            </div>
        </div>

        <div class="form-group">
            @Html.LabelFor(model => model.BookTitle, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.BookTitle, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.BookTitle, "", new { @class = "text-danger" })
            </div>
        </div>

        <div class="form-group">
            @Html.LabelFor(model => model.BookAuthor, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.BookAuthor, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.BookAuthor, "", new { @class = "text-danger" })
            </div>
        </div>

        <div class="form-group">
            @Html.LabelFor(model => model.BookPublisher, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.BookPublisher, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.BookPublisher, "", new { @class = "text-danger" })
            </div>
        </div>

        <div class="form-group">
            @Html.LabelFor(model => model.BookCategory, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.BookCategory, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.BookCategory, "", 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-primary" />
            </div>
        </div>
    </div>
}

<div>
    @Html.ActionLink("Back to List", "Index")
</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>
Edit.cshtml 
@model RepoDB.Models.BookDetails

@{
    ViewBag.Title = "Update";
}

@using (Html.BeginForm())
{
    @Html.AntiForgeryToken()
    @Html.HiddenFor(model => model.BookSerialNo)
    <div class="form-horizontal">
        <h4>Update Book</h4>
        <hr />
        @Html.ValidationSummary(true, "", new { @class = "text-danger" })
        <div class="form-group">
            @Html.LabelFor(model => model.BookISBN, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.BookISBN, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.BookISBN, "", new { @class = "text-danger" })
            </div>
        </div>

        <div class="form-group">
            @Html.LabelFor(model => model.BookTitle, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.BookTitle, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.BookTitle, "", new { @class = "text-danger" })
            </div>
        </div>

        <div class="form-group">
            @Html.LabelFor(model => model.BookAuthor, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.BookAuthor, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.BookAuthor, "", new { @class = "text-danger" })
            </div>
        </div>

        <div class="form-group">
            @Html.LabelFor(model => model.BookPublisher, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.BookPublisher, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.BookPublisher, "", new { @class = "text-danger" })
            </div>
        </div>

        <div class="form-group">
            @Html.LabelFor(model => model.BookCategory, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.BookCategory, new { htmlAttributes = new { @class = "form-control"} })
                @Html.ValidationMessageFor(model => model.BookCategory, "", 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-primary" />
            </div>
        </div>
    </div>
}

<div>
    @Html.ActionLink("Back to List", "Index")
</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>
Delete.cshtml 
@model RepoDB.Models.BookDetails

@{
    ViewBag.Title = "Delete";
}

<h3>Are you sure you want to delete this record?</h3>
<div>
    <hr />
    <dl class="dl-horizontal">
        <dt>
            @Html.DisplayNameFor(model => model.BookISBN)
        </dt>

        <dd>
            @Html.DisplayFor(model => model.BookISBN)
        </dd>

        <dt>
            @Html.DisplayNameFor(model => model.BookTitle)
        </dt>

        <dd>
            @Html.DisplayFor(model => model.BookTitle)
        </dd>

        <dt>
            @Html.DisplayNameFor(model => model.BookAuthor)
        </dt>

        <dd>
            @Html.DisplayFor(model => model.BookAuthor)
        </dd>

        <dt>
            @Html.DisplayNameFor(model => model.BookPublisher)
        </dt>

        <dd>
            @Html.DisplayFor(model => model.BookPublisher)
        </dd>

        <dt>
            @Html.DisplayNameFor(model => model.BookCategory)
        </dt>

        <dd>
            @Html.DisplayFor(model => model.BookCategory)
        </dd>
    </dl>

    @using (Html.BeginForm())
    {
        @Html.AntiForgeryToken()
        @Html.HiddenFor(model => model.BookSerialNo)
        <div class="form-actions no-color">
            @Html.ActionLink("Back to List", "Index", null, new { @class = "btn btn-primary" })
            <input type="submit" value="Delete" class="btn btn-danger" />
        </div>
    }
</div>
Output
ASP.NET MVC Web Application CRUD Using RepoDB ORM

Happy coding!

Comments

Post a Comment

Donate

Popular Posts From This Blog

WPF CRUD Application Using DataGrid, MVVM Pattern, Entity Framework, And C#.NET

-
Image
Good day to all! Here's an example of a WPF CRUD (Create,Update and Delete) project using the DataGrid control, ADO.NET Entity Framework 6.x, C#.NET and Model–View–Viewmodel(MVVM) architectural pattern. This post was based from this tutorial WPF CRUD With DataGrid, Entity Framework And C#.NET except that we are now using the MVVM framework. The steps below are pretty straightforward and easy to follow. I. Project Setup 1. Add a table called Students in you database. The complete script is found in this post WPF CRUD With DataGrid, Entity Framework And C#.NET 2. Create a WPF Project and add four folders called DataAccess, Model, View and ViewModel. 3. Your project structure may look similar with the screenshot provided below. II. Coding The Model and Repository Class 1. Inside the Model folder, add an ADO.NET Entity Data Model that connects to the Students table in your database. On my part, I named it StudentModel . 2. For the connectionstring name in App.config file, I
Read more

How To Insert Or Add Emojis In Microsoft Teams Status Message

-
Image
Good day Gents! I was trying to update my Microsoft Teams status and was wondering if I can add or insert emoji. For editing or adding new teams status, only text fields are available and there's no emoji icon similar to writing a teams message. After googling for a few minutes, I found this link Emoji's in status which is a feature request in Microsoft Teams User's Voice Forum. So far this upgrade request only has thirty votes which is relatively small and may be considered a low priority for the product team to implement. Given the limitations at hand, I tried several approach including copying an emoji in a chat message which didn't work and later found a solution using Microsoft Outlook 365. First is to create a new email message in Microsoft Outlook and press (Window Key + .). The emoji dropdown control will appear. Next is to select the emoji that you wanted to add to Teams. Copy the emoji from the newly created email message template and paste that to Micr
Read more

TypeScript Error Or Bug: The term 'tsc' is not recognized as the name of a cmdlet, function, script file, or operable program.

-
Image
Good Day Gents! During this free-time I started setting up Node.js on my machine as part of my career goals which is learning new frameworks in preparation for new projects. So the first thing I did was to install TypeScript language to get myself familiar with Angular 2+. After I finished setting up TypeScript, I then created a simple HelloWorld program and ran the application through Visual Studio Code using the "tsc: build - tsconfig.json" command. I encountered an error which says "The term 'tsc' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again." Below are the details on my machine and packages installed:   - Operating System: Windows 10 Pro   - Node.js: 14.16.0   - npm version: 6.14.11   - Typescript: 4.2.3 (using command: "npm install -g typescript") After doing some research and reading thr
Read more