Agreement

Objective

There is a pathology laboratory in the city and they owned multiple branches over the state. Some of the branches are running in own premises and some of the branches running in third party building owner. When they plan to open a new outlet, they need to agreement with the building owner with terms and conditions.

  • Rental period
  • Monthly or yearly rental
  • Agreement renewal
  • Notice period to vacate the building
  • Maintenance cost
  • Electricity cost
  • Agreement closure

All these points are mentioned in the agreement paper and duly signed each party and then they keep the physical document in separate folder.

Difficulties

  • Whenever the need some information about the agreement, they need to search one by one.
  • They forget to renewal agreement on time.

Solution

  • The agreement details should be captured and stored in the database.
  • The scanned copy of the document should be stored in the system.
  • Need to develop the software to integrate the searching feature.

Technology

  • SQL Server 2012
  • Entity Framework 6.0
  • ASP.Net MVC 5.0
  • JavaScript
  • jQuery 3.3.1
  • Bootstrap 4.3.1

Where to start the development?

Let us start with blank .Net solution, then add the Web project and class library project

  • Create blank solution

Open Visual studio and select "create a new project"

new-project

Select "Blank solution"

blank-solution

Provide the solution name

solution-name

Add new MVC web project

  • Open Solution Explorer
  • Right click on the "Solution Name"
  • Select Add->New Project...
add-new-project

Provide the project name and ensure the folder structure

add-web-project

Select the web project type

  • Select the "MVC" from the list of project template
  • Continue with default option
web-project-selection

Web project folder structure

  • The required folders and files are created automatically.
web-project-structure

Add new class library project

  • Open Solution Explorer
  • Right click on the "Solution Name"
  • Select Add->New Project...
add-new-project

Select the "Class Library(.Net Standard)" from the list of project template

class-library-selection

Provide the class library project name and ensure the folder structure

class-library-location

Ensure the project with folder structure

project-structure

Run and test the project

run-and-test-project

Create the Database - Code First Approach

  • Open SQL server management studio
  • Right click on the "Databases"
  • Select "New Database..."
create-database-step1

Enter the database name and select the location where to save the database in your system

create-database-step2

Ensure the database has been created.

create-database-step3

Install "Entity Framework"

Why Entity Framework?

The entity framework is an ORM tool which is used to interact with SQL server database from .Net application.

  • Go to Tools->Nuget Package Manager->Manage Nuget Packages for solution...
  • Select Browse tab
open-nuget-package
  • Select Browse tab
  • type "entityframework" in the search box
  • Select "EntityFramework" from the list
install-entity-framework

Ensure the "Entity Framework" reference added the project

entity-framework-reference

Add the database connection string in "Agreement.Domain" project

  • Open the App.config file and the following connection string
  • Replace the Data source, initial catalog, user id, password with your database details.
                <connectionStrings>
                    <add name="AgreementDB" connectionString="Data Source=localhost;initial Catalog=AgreementDemo;user id=sa;password=****"
                    providerName="System.Data.SqlClient" />
                <connectionStrings>
                
app-config-connection-string

Add the database connection string in "Agreement.WebUI" project

web-config-connection-string

Create the DbContext class

Why DbContext class?
The DbContext is a class object in Entity Framework, which is used to interact web application with database.
How to interact with database using DbContext class?
We have to define a public class which should be inherited from DbContext class
Where to specify the database details?
We have to pass the database details as a base class parameter.
EFDbContext

Create the DBInitializer class

Why DBInitializer class?
DBInitializer is a class which specify, when the database should be created in database server. Possible values are CreateDatabaseIfNotExists, DropCreateDatabaseAlways, DropCreateDatabaseIfModelChanges
DBInitializer

Add the Agreement.Domain project reference with Agreement.WebUI

  • Right Click on "Agreement.Domain"
  • Select "Rebuild" from the menu
  • Right Click on "Agreement.WebUI"
  • Select "Add" from the menu
  • Select "Reference" from the menu
  • The "Reference Manager" window will appear.
  • Select the project and click the "OK" button
  • Refer below image.
select-project-reference

Define the "Agreement" class in "Agreement.Domain"

  • The above mentioned class is called as POCO object.
  • The table attribute specify in which name should be used in sql table. If we not specify, the class name will be a table name. i.e. "AgreementData"
  • Each getter and setter property consider as "Column" in the sql table
  • Specify additional attribute for each property if we need
  • The "System.ComponentModel.DataAnnotations" namespace has been used to specify the attribute for each column.
  • Based on attribute which you defined the POCO class, the table structure will be created.
  • Compare yourself, the POCO classes with sql table, to get know more ideas.
using System;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
namespace Agreement.Domain
{
    [Table("Agreement")]
    public class AgreementData
    {
        [Key]
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        [Required]
        public int AgreementId { get; set; }
        [Required]
        [Column(TypeName = "VARCHAR")]
        [MaxLength(250)]
        public string PremisesAddress1 { set; get; }
        [Column(TypeName = "VARCHAR")]
        [MaxLength(250)]
        public string PremisesAddress2 { set; get; }
        [Column(TypeName = "VARCHAR")]
        [MaxLength(250)]
        public string PremisesAddress3 { set; get; }
        [Column(TypeName = "VARCHAR")]
        [MaxLength(250)]
        public string PremisesCity { set; get; }
        [Column(TypeName = "VARCHAR")]
        [MaxLength(10)]
        public string PremisesPincode { set; get; }
        [Required]
        [Column(TypeName = "VARCHAR")]
        [MaxLength(1000)]
        public string PremisesDescription { set; get; }
        [Required]
        [Column(TypeName = "VARCHAR")]
        [MaxLength(250)]
        public string PaymentMethod { get; set; }
    }
}
            

Define the "AgreementData" as member of DbContext class

agreement-member

Access the "AgreementData" from Home controller

home-controller-index

Check your database

  • There are two tables will be created in database which are "dbo.Agreement" and "dbo.__MigrationHistory"
  • When we access the database object very first time, the database as well as table are created in the database. This is called as "Code First Approach"
  • The "dbo.Agreement" is a table which has been mapped with "AgreementData" class object.
  • The "dbo.__MigrationHistory" is a table which maintain the POCO class vs SQL table migration history. Later I will explain.
  • Earlier, we have specified "CreateDatabaseIfNotExists" in DBInitializer class. So, if the database is not available, it will create.
  • If we specified "DropCreateDatabaseAlways" in DBInitializer class, when you run the application, each time the database get deleted and created. If you have data in the tables, all are get lost.
  • If we specified "DropCreateDatabaseIfModelChanges" in DBInitializer class, if you make any changes in domain class, the database get deleted and created.
  • Look at the below image, which shows the table structure "Agreement"
db-table-structure-1

I add two more fields in the domain model "AgreementData"

                            [Required]
                            public DateTime EntryDate { get; set; }
                            [Required]
                            [Column(TypeName = "VARCHAR")]
                            [MaxLength(100)]
                            public string UserId { get; set; }
                

I run the application. The application throws the exception. Refer the below image.

add-new-field-error

Why this exception occur?

  • This exception indicates, there is a mismatch between the domain model and database table.
  • These field are available in "Domain model class" but not in database table.
  • Since we specify the table creation method as "CreateDatabaseIfNotExists", the database will not delete and create.
  • If we specify the table creation method as "DropCreateDatabaseAlways" or "DropCreateDatabaseIfModelChanges", the database will be deleted and created. All the fields will be available in table. So exception will not occur. But we will lose the data from the database.

How to solve this issue?

The answer is "migration" command

There are three migration commands

  • Enable-Migrations
  • Add-migration
  • Update-database
What is "Enable-Migrations"
This command enables the auto migration option in the database.
What is "Add-migration"
This command detects the schema level changes in the domain model and creates the C# class with two functions which are Up and Down
What is "Update-database"
This command updates the schema level changes in database table

Migration command example

  • enable-Migrations -projectname {Project Name} Example : Enable-Migrations -projectname Agreement.Domain
  • add-migration -projectname {Project Name} Example : add-migration -projectname Agreement.Domain
  • update-database -projectname {Project Name} Example : update-database -projectname Agreement.Domain
How to run the migration command?
Go to Tools->Nuget Package Manager->Package Manager Console

Run the following command at package manager console

Enable Migration

enable-migration

Add Migration

The add Migration command will ask the "Name" which is called as "migration name". A class will be created using the "migration name" in the migration folder. Refer the below image.

add-migration

New Migration class will be added in the migration folder.

add-migration

Content of migration class

add-migration

Update database

add-migration

After the "Update database" command execution completed,

  • The fields >"EntryDate" and "UserId" will be updated in the database.
  • The migration class name "202110172104164_AddnewfieldinAgreement.cs" will be inserted in the "__MigrationHistory" table.

add-migration

Clear the default content in "Agreement.WebUI" project

Open the following files and delete the content

  • Views->Home->Index.cshtml
  • Views->Home->Contact.cshtml
  • Views->Home->About.cshtml
  • Run and test the application
  • Refer the below image

home-page

Change the application name

Open the layout page and change the application name. Go to Views->Shared->_Layout.cshtml

home-page

Run and test application and refer the following image

home-page

What is Layout Page?

Layout page is just like master page which provide consistent appearance for all pages. The file should be placed in "Shared" folder. So all views will use this page and we have so specify the layout name in all view files.

layout-page-template

Define a view Model to get the user input

Why View Model?

View model is a class that contains the fields which are represented in the strongly-typed view. It is used to pass data from controller to strongly-typed view. When we use view model as parameter in post action method, the html input control value passed to view model. This is called as "Model Binding". The following line specifies the layout page.

Layout = "~/Views/Shared/_Layout.cshtml";

  • Add a class library project and the name should be "Agreement.ViewModel"
  • Add a class file in the Model folder
  • The file name should be "NewAgreementViewModel.cs" and class should be "NewAgreementViewModel"
  • Add the following properties. Each public property render(display) as input or text field in the html page to get the data from end user.
               public class NewAgreementViewModel
                {
                    public int? AgreementId { get; set; }

                    [Display(Name = "Address1")]
                    [Required]
                    [StringLength(250)]
                    public string PremisesAddress1 { set; get; }

                    [Display(Name = "Address2")]
                    [Required]
                    [StringLength(250)]
                    public string PremisesAddress2 { set; get; }

                    [Display(Name = "Address3")]
                    [StringLength(250)]
                    public string PremisesAddress3 { set; get; }

                    [Display(Name = "City")]
                    [Required]
                    [StringLength(250)]
                    public string PremisesCity { set; get; }

                    [Display(Name = "Pincode")]
                    [Required]
                    [StringLength(6)]
                    public string PremisesPincode { set; get; }

                    [Display(Name = "Detail of the Premises")]
                    [Required]
                    [StringLength(1000)]
                    public string PremisesDescription { set; get; }
                }
                

Create a new empty controller and the controller name should be "DocumentController". Refer the below image.

document-controller

Add a view for index action method. Create new view in the Views->Document folder->Index.html

  • Right click on "Views->Document folder"
  • Select "Add"->"View"
  • A pop up window appear
  • Enter the view name which is "Create"
  • Click "Add" button
  • Refer the below image

document-index-view

Add the navigation to agreement page

navigation-add

new-agreement

Pass the view model to view. I updated the code in index method in "Documentcontroller". Refer the image.

pass-model-view

Views->Document->Create.cshtml

        @model Agreement.ViewModel.NewAgreementViewModel
        @{
            ViewBag.Title = "New Agreement";
            Layout = "~/Views/Shared/_Layout.cshtml";
        }
         <div class="row">
             <div class="col-12">
                 <div class="card border border-0 pt-1 pb-1 text-danger">
                     <h3>New Agreement </h3>
                 </div>
             </div>
         </div>
         <div class="row">
             <div class="col-sm-12 col-md-12 col-lg-12">
                 <div class="agreement pt-0 pb-3 pl-3 pr-3">
                    @using (Html.BeginForm("SaveAgreement", "Document", FormMethod.Post,
                    new { enctype = "multipart/form-data", role = "form" }))
                    {
                         <div class="row">
                             <div class="col-md-3 col-sm-3 col-lg-3 p-1">
                                 <div class="card infobox">
                                     <div class="card-header p-1 text-center">
                                         <h4>Premises Details </h4>
                                     </div>
                                     <div class="card-body p-1">
                                         <div class="form-group">
                                            @Html.LabelFor(m => m.PremisesAddress1, new { @class = "control-label p-0 m-0" })
                                             <span class="requiredfiled">* </span>
                                            @Html.TextBoxFor(m => m.PremisesAddress1, new { @class = "form-control form-control-sm" })
                                            @Html.ValidationMessageFor(m => m.PremisesAddress1, "", new { @class = "text-danger" })
                                         </div>
                                         <div class="form-group">
                                            @Html.LabelFor(m => m.PremisesAddress2, new { @class = "control-label p-0 m-0" })
                                             <span class="requiredfiled">* </span>
                                            @Html.TextBoxFor(m => m.PremisesAddress2, new { @class = "form-control form-control-sm" })
                                            @Html.ValidationMessageFor(m => m.PremisesAddress2, "", new { @class = "text-danger" })
                                         </div>
                                         <div class="form-group">
                                            @Html.LabelFor(m => m.PremisesAddress3, new { @class = "control-label p-0 m-0" })

                                            @Html.TextBoxFor(m => m.PremisesAddress3, new { @class = "form-control form-control-sm" })
                                            @Html.ValidationMessageFor(m => m.PremisesAddress3, "", new { @class = "text-danger" })
                                         </div>
                                         <div class="form-group">
                                            @Html.LabelFor(m => m.PremisesCity, new { @class = "control-label p-0 m-0" })
                                             <span class="requiredfiled">* </span>
                                            @Html.TextBoxFor(m => m.PremisesCity, new { @class = "form-control form-control-sm" })
                                            @Html.ValidationMessageFor(m => m.PremisesCity, "", new { @class = "text-danger" })
                                         </div>
                                         <div class="form-group">
                                            @Html.LabelFor(m => m.PremisesPincode, new { @class = "control-label p-0 m-0" })
                                             <span class="requiredfiled">* </span>
                                            @Html.TextBoxFor(m => m.PremisesPincode,
                                             new { @class = "form-control form-control-sm", @maxlength = "6",
                                              @onkeypress = "return isNumberKey(this,event)" })
                                            @Html.ValidationMessageFor(m => m.PremisesPincode, "", new { @class = "text-danger" })
                                         </div>
                                         <div class="form-group">
                                            @Html.LabelFor(m => m.PremisesDescription, new { @class = "control-label p-0 m-0" })
                                             <span class="requiredfiled">* </span>
                                            @Html.TextAreaFor(m => m.PremisesDescription,
                                            new { @class = "form-control form-control-sm", @maxlength = "750", rows = "5" })
                                            @Html.ValidationMessageFor(m => m.PremisesDescription, "", new { @class = "text-danger" })
                                         </div>
                                         <div class="form-group">
                                            <input type="submit" value="Submit" class="btn btn-success" />
                                         </div>
                                     </div>
                                 </div>
                             </div>
                         </div>
                    }
                 </div>
             </div>
         </div>
       

How to pass the data from view to controller action method?

  • Strongly typed model binding to view
  • Using the FormCollection Object
  • Using the Parameters

I have used the first approach which is "Strongly typed view model". To achieve this concept, we have to use strongly typed html control. All these controls should be placed within Html.BeginForm()

                         @using (Html.BeginForm("SaveAgreement", "Document", FormMethod.Post,
                          new { enctype = "multipart/form-data", role = "form" }))
                          {
                            Your html control goes here
                            <input type="submit" value="Submit" class="btn btn-success" />
                          }
                    

Html.BeginForm() must have a "submit" button control. Then only the html control value will be passed to server post action method.

Some strongly typed html controls given below.

  • @Html.LabelFor()
  • @Html.TextBoxFor()
  • @Html.TextAreaFor()
  • @Html.ValidationMessageFor()

Razor view with html control and submit button.

form-submit

Model binding - All html input control value are submitted or passed to action method. That action method should be specified in Html.BeginForm().

view-model-data-post

When the user click the submit button, all html control values will be passed to the action method. The action method should be decorated with "HTTPPost" attribute. Here the action method is "SaveAgreement" and controller is "Document". This process is called as Model binding.

The following html code is the rendered content of the razor page(Create.cshtml). All the html input controls have the name and these names are the properties of view model(AgreementViewModel.cs). If we use strongly typed view and strongly type html control, the view model property name will be the name of html control. Look at the following html code.

<div class="row">
    <div class="col-12">
        <div class="card border border-0 pt-1 pb-1 text-danger">
            <h3>New Agreement</h3>
        </div>
    </div>
</div>
<div class="row">
    <div class="col-sm-12 col-md-12 col-lg-12">
        <div class="agreement pt-0 pb-3 pl-3 pr-3">
	<form action="/Document/SaveAgreement" enctype="multipart/form-data" method="post" role="form">
              <div class="row">
                    <div class="col-md-3 col-sm-3 col-lg-3 p-1">
           <div class="card infobox">
               <div class="card-header p-1 text-center">
                   <h4>Premises Details</h4>
               </div>
               <div class="card-body p-1">
                   <div class="form-group">
                       <label class="control-label p-0 m-0" for="PremisesAddress1">Address1</label>
                       <span class="requiredfiled">*</span>
                       <input class="form-control form-control-sm" data-val="true"
                       data-val-length="The field Address1 must be a string with a maximum length of 250."
                       data-val-length-max="250" data-val-required="The Address1 field is required."
                        id="PremisesAddress1" name="PremisesAddress1" type="text" value="" />
                       <span class="field-validation-valid text-danger" data-valmsg-for="PremisesAddress1"
                       data-valmsg-replace="true"></span>
                   </div>
                   <div class="form-group">
                       <label class="control-label p-0 m-0" for="PremisesAddress2">Address2</label>
                       <span class="requiredfiled">*</span>
                       <input class="form-control form-control-sm" data-val="true"
                       data-val-length="The field Address2 must be a string with a maximum length of 250."
                       data-val-length-max="250"
                       data-val-required="The Address2 field is required."
                       id="PremisesAddress2"
                       name="PremisesAddress2" type="text" value="" />
                       <span class="field-validation-valid text-danger"
                       data-valmsg-for="PremisesAddress2"
                       data-valmsg-replace="true"></span>
                   </div>
                   <div class="form-group">
                       <label class="control-label p-0 m-0" for="PremisesAddress3">Address3</label>

                       <input class="form-control form-control-sm" data-val="true"
                       data-val-length="The field Address3 must be a string with a maximum length of 250." data-val-length-max="250"
                        id="PremisesAddress3" name="PremisesAddress3" type="text" value="" />
                       <span class="field-validation-valid text-danger" data-valmsg-for="PremisesAddress3"
                       data-valmsg-replace="true"></span>
                   </div>
                   <div class="form-group">
                       <label class="control-label p-0 m-0" for="PremisesCity">City</label>
                       <span class="requiredfiled">*</span>
                       <input class="form-control form-control-sm" data-val="true"
                       data-val-length="The field City must be a string with a maximum length of 250."
                       data-val-length-max="250" data-val-required="The City field is required." id="PremisesCity" name="PremisesCity" type="text" value="" />
                       <span class="field-validation-valid text-danger"
                        data-valmsg-for="PremisesCity" data-valmsg-replace="true"></span>
                   </div>
                   <div class="form-group">
                       <label class="control-label p-0 m-0" for="PremisesPincode">Pincode</label>
                       <span class="requiredfiled">*</span>
                       <input class="form-control form-control-sm" data-val="true"
                       data-val-length="The field Pincode must be a string with a maximum length of 6." data-val-length-max="6"
                       data-val-required="The Pincode field is required." id="PremisesPincode" maxlength="6" name="PremisesPincode"
                        onkeypress="return isNumberKey(this,event)" type="text" value="" />
                       <span class="field-validation-valid text-danger"
                       data-valmsg-for="PremisesPincode" data-valmsg-replace="true"></span>
                   </div>
                   <div class="form-group">
                       <label class="control-label p-0 m-0" for="PremisesDescription">Detail of the Premises</label>
                       <span class="requiredfiled">*</span>
                       <textarea class="form-control form-control-sm" cols="20" data-val="true"
                        data-val-length="The field Detail of the Premises must be a string with a maximum length of 1000."
                         data-val-length-max="1000" data-val-required="The Detail of the Premises field is required."
                         id="PremisesDescription" maxlength="750" name="PremisesDescription" rows="2">
                         </textarea>
                         <span class="field-validation-valid text-danger" data-valmsg-for="PremisesDescription"
                          data-valmsg-replace="true"></span>
                     </div>
                     <div class="form-group">
                         <input type="submit" value="Submit" class="btn btn-success" />
                     </div>
                 </div>
             </div>
                    </div>
                </div>
</form>        </div>
    </div>
</div>
                  

Some additional attribute are available with html control. I will explain later. The html control name is playing main role for model binding process. But Id values are used in script library such JavaScript, jQuery.

The following image explains how the view model property rendered as an html controls.

html-input-model-name-map

How to validate the data?

What is data validation?

Data validation refers to the process of ensuring the accuracy and quality of data. The validation is a condition and the data should satisfy condition.

If the data is not satisfy the condition, then the data become invalid and should not process further with the data

The application developer should not trust the end user input data. They may enter any data, any order and any format. The software developers have the responsibility to write the code to validate the input data and provide user friendly message to the end user if it is invalid data. Based on the validation message, the end user may correct the data and re-submit the data. Even if there is single invalid data, should not allow the end user to go further.

Mobile Number
  • The Mobile number should not be empty string.
  • The Mobile number should be 10 digits.
  • All digits should not be zero.
  • Valid Data : 9575781025, 8575011154
  • Invalid Data : 957578,11154,0000000000
Pin code
  • Pin code should not be empty string.
  • The Pin code should be 6 digits.
  • All digits should not be zero.
  • Based on the region/state/country the sequence will be varying.
  • Valid Data : 600857, 524857
  • Invalid Data :654xyz,34545
Date of Birth
  • The date should be in specified format(dd/mm/yyyy, dd-mmm-yyyy,mm-dd-yyyy,mmm-dd-yyyy, etc..)
  • The day value should be numeric
  • 31 Days month - January, March, May, July, August, October, and December.
  • 30 Days month - April, June, September, and November
  • 29 days month - February if leap year
  • 28 days month - February if not leap year
  • The month value should be numeric and the value between 1 and 12
  • Valid Data : 25-May-2001,25-05-2001,1-12-2001,01-1-2001,11-25-2014,10-31-1998
  • Invalid Data : 31-Feb-2001,29-02-2001,31-04-2001,45-01-2001,10-60-2014

Usually, the developer has to write code to validate each and every input field value. But, now days so many script libraries are available, we can use them. In MVC, the data annotations have been used to implement the validation with minimum effort. If you look at above html code, you can find some validation attribute with html control. This is the magic of data annotations.

Some sample data validation attribute for html input controls

  • data-val="true"
  • data-val-length="The field Address1 must be a string with a maximum length of 250."
  • data-val-length-max="250"
  • data-val-required="The Address1 field is required."

.Net MVC support to use build in method called "ModelState.IsValid" to validate the model.

                    [HttpPost]
                    public ActionResult SaveAgreement(NewAgreementViewModel viewModel)
                    {
                          if(!ModelState.IsValid)
                          {
                            return View("Create", viewModel);
                          }
                        return View("Create",viewModel);
                    }
                

How to save the records?

Add a class in "Agreement.Domain" project. Refer the below image.

agreement-repository

Complete code to save the agreement details. Refer below image

agreement-repository-save

Enter the agreement details and save the data.

data-entry-1

data-entry-2

Check the records in database

data-entry-3

Exception Handling

When the end user using the application, if any error occurs, the application will be stopped and user cannot continue further. The invalid data can cause the error or some unexpected behavior may cause the error such as server error, database connection error or network failure. These all are unexpected behavior. If any unexpected error occurs, we have to handle the error and provide the user friendly message to the end user. We have to use exception handling mechanism to handle this issue and have to log the error information for further or future analysis. The developer should not display the actual error message in the front application. If the developer displays the actual exception in the front end application, the hacker may misuse the application or break the application.

agreement-repository-save-exception

So far, I have explained, how to develop a module to save the information. Next, I going to add some additional table and migrate the database. The following image shows complete design view.

agreement-comple-page