Create Razor Pages app that uses Customer Repository

In the previous article we completed the CustomerRepository class that performs CRUD operations on the Customers table. Now it's time to use the CustomerRepository in a Razor Pages application so that we can test the CRUD operations from the browser.

Open the same project that has CustomerRepository class in Visual Studio. And then go to the Program.cs file. Modify the Program.cs as shown below:

var builder = WebApplication.CreateBuilder(args);
builder.Services.AddRazorPages();
builder.Services.AddScoped<ICustomerRepository, 
CustomerRepository>();
var app = builder.Build();
app.MapRazorPages();
app.Run();

Before we write any code let's see how the page is going to work. When you run the application it shows a list of CustomerIDs in a drop down list. You can select a CustomerID and either update it or delete it. You can also add a new customer using the Insert button.

Upon selecting a CustomerID and clicking on the Update button, the selected customer is shown for editing like this:

You can make the changes and hit the Submit button to save them to the database. If all goes well you will be shown a success message.

On the same lines you can select a CustomerID and click on the Delete button to delete it.

Finally, clicking on the Insert button shows an empty data entry screen for entering details of a new customer.

Now that you know how the application is going to work, add Pages folder to the project root and add a new Razor Page named Index.cshtml into it.

Also add _ViewImports.cshtml file into the Pages folder. And enable Tag Helpers for your project by adding this line:

@addTagHelper *, Microsoft.AspNetCore.Mvc.TagHelpers

Open the IndexModel page model class and add the following code to it:

public class IndexModel : PageModel
{
    [BindProperty]
    public Customer CustomerModel { get; set; }

    public string Mode { get; set; }

    public string Message { get; set; }

    public List<SelectListItem> Items { get; set; }

    private readonly ICustomerRepository repository;

    public IndexModel(ICustomerRepository repository)
    {
        this.repository = repository;
    }
}

As you can see, this code defines four properties namely CustomerModel, Mode, Message, and Items. The CustomerModel property participates in the model binding process and hence it is decorated with the [BindProperty] attribute. Our data entry form will supply values for this model object.

The Mode property indicates the data entry mode such as Insert, Update, and Delete. Ideally this property should have been an enumeration. But here we used a string value just to save some time.

The Message property displays a message (success or error or some other prompt) to the end user.

The Items property holds a list of all the CustomerIDs in the forms of List<SelectListItem>. This list is bound with a drop down list on the page.

Our code will need the CustomerRepository to perform the CRUD operations. Hence, an ICustomerRepository object is injected into the constructor and saved in the repository variable for further use.

The drop down list showing CustomerIDs needs to be filled multiple times during the course of execution of our application. So, it's better to create a helper method that does that job. See the code below:

private async Task FillCustomers()
{
    List<Customer> data = await 
        repository.SelectAll();

    Items = (from i in data
                select new SelectListItem()
                {
                    Text = i.CustomerID,
                    Value = i.CustomerID
                }).ToList();
}

The FillCustomers() helper method fetches all the customers from the database using SelectAll() method of the repository and then converts them into a List of SelectListItem objects using a LINQ query.

The OnGet() page handler calls the FillCustomers() when the page is loaded.

public async Task<IActionResult> OnGet()
{
    await FillCustomers();
    return Page();
}

You can pick a CustomerID from the drop down list and click on the Update button to modify it. The following code shows the Update page handler.

public async Task<IActionResult> OnPostUpdate()
{
    CustomerModel = await 
    repository.SelectByID(CustomerModel.CustomerID);
    Message = "Update the selected customer below :";
    Mode = "Update";
    return Page();
}    

As you can see the Update page handler calls SelectByID() method of the repository to fetch the selected Customer record. It then sets the CustomerModel to this Customer object. The Message property is set to a message informing the user to modify the record. The Mode property is set to Update indicating that an existing Customer is being modified.

When you modify the selected Customer and click on the Submit button, the DoUpdate page handler is invoked. The following code shows the DoUpdate page handler.

public async Task<IActionResult> OnPostDoUpdate()
{
    await FillCustomers();
    if (ModelState.IsValid)
    {
        var count = await 
        repository.Update(CustomerModel);
        await FillCustomers();
        Message = $"{count} record(s) updated.";
        CustomerModel = null;
        Mode = "";
    }
    return Page();
}    

The DoUpdate page handler saves the modifications made to the selected Customer by calling the Update() method of the repository. The Update() method returns the number of records affected by the UPDATE action query (one - in this case). This count is displayed the Message property. Since the modification is successful the CustomerMode is set to null and Mode is also cleared.

The Delete and DoDelete page handlers are similar to the Update and DoUpdate page handlers with a little bit of change. The following code shows both of them.

public async Task<IActionResult> OnPostDelete()
{
    CustomerModel = await 
    repository.SelectByID(CustomerModel.CustomerID);
    Message = "Delete the selected customer :";
    Mode = "Delete";
    return Page();
}

public async Task<IActionResult> OnPostDoDelete()
{
    await FillCustomers();
    var count = await 
    repository.Delete(CustomerModel.CustomerID);
    Message = $"{count} record(s) deleted.";
    CustomerModel = null;
    Mode = "";
    return Page();
}

As you can see, the Delete page handler sets the Message and Mode to suit the delete operation. The DoDelete page handler calls Delete() method of the repository.

The Insert and DoInsert page handlers take care of adding a new Customer to the database. Since the insert screen doesn't need to load any existing Customer there is a small change in these page handlers. These page handlers are shown below:

public async Task<IActionResult> OnPostInsert()
{
    await FillCustomers();
    Message = "Insert a new customer below :";
    Mode = "Insert";
    return Page();
}

public async Task<IActionResult> OnPostDoInsert()
{
    if (ModelState.IsValid)
    {
        var count = await 
        repository.Insert(CustomerModel);
        await FillCustomers();
        Message = $"{count} record(s) inserted.";
        CustomerModel = null;
        Mode = "";
    }
    return Page();
}    

In the preceding code we haven't taken care to display the validation errors. But you can easily display them using validation tag helpers. The Customer record already has data validation attributes. So, that should be quite straightforward.

Finally, we will add DoCancel page handler that handles the click of the Cancel button.

public async Task<IActionResult> OnPostDoCancel()
{
    await FillCustomers();
    CustomerModel = null;
    Mode = "";
    return Page();
}

The complete markup that makes the UI of Index razor page is shown below:

<h1>Customer CRUD Operations</h1>
<h2>@Model.Message</h2>
<form method="post">
    <table border="0" cellpadding="15">
        @if (Model.Items != null)
        {
            @if (string.IsNullOrWhiteSpace(Model.Mode))
            {
                <tr>
                    <td><label asp-for="CustomerModel.CustomerID">
                    </label> :</td>
                    <td>
                        <select asp-for="CustomerModel.CustomerID" 
                        asp-items="@Model.Items"></select>
                        <button type="submit" 
                        asp-page-handler="Update">Update</button>
                        <button type="submit" 
                        asp-page-handler="Delete">Delete</button>
                          OR  
                        <button type="submit" 
                        asp-page-handler="Insert">Insert</button>
                    </td>
                </tr>
            }
        }
        @if (Model.CustomerModel != null && Model.Mode=="Insert")
        {
            <tr>
                <td><label asp-for="CustomerModel.CustomerID" >
                </label> :</td>
                <td><input type="text" 
                asp-for="CustomerModel.CustomerID" /></td>
            </tr>
            <tr>
                <td><label asp-for="CustomerModel.CompanyName" >
                </label> :</td>
                <td><input type="text" 
                asp-for="CustomerModel.CompanyName"/></td>
            </tr>
            <tr>
                <td><label asp-for="CustomerModel.ContactName" >
                </label> :</td>
                <td><input type="text" 
                asp-for="CustomerModel.ContactName"/></td>
            </tr>
            <tr>
                <td><label asp-for="CustomerModel.Country" >
                </label> :</td>
                <td><input type="text" 
                asp-for="CustomerModel.Country"/></td>
            </tr>
            <tr>
                <td colspan="2">
                    <button type="submit" 
                    asp-page-handler="DoInsert">Submit</button>
                    <button type="submit" 
                    asp-page-handler="DoCancel">Cancel</button>
                </td>
            </tr>
        }
        @if (Model.CustomerModel != null && Model.Mode=="Update")
        {
            <tr>
                <td><label asp-for="CustomerModel.CustomerID" >
                </label> :</td>
                <td><input type="text" 
                asp-for="CustomerModel.CustomerID" 
                readonly="readonly" /></td>
            </tr>
            <tr>
                <td><label asp-for="CustomerModel.CompanyName" >
                </label> :</td>
                <td><input type="text" 
                asp-for="CustomerModel.CompanyName"/></td>
            </tr>
            <tr>
                <td><label asp-for="CustomerModel.ContactName" >
                </label> :</td>
                <td><input type="text" 
                asp-for="CustomerModel.ContactName"/></td>
            </tr>
            <tr>
                <td><label asp-for="CustomerModel.Country" >
                </label> :</td>
                <td><input type="text" 
                asp-for="CustomerModel.Country"/></td>
            </tr>
            <tr>
                <td colspan="2">
                    <button type="submit" 
                    asp-page-handler="DoUpdate">Submit</button>
                    <button type="submit" 
                    asp-page-handler="DoCancel">Cancel</button>
                </td>
            </tr>
        }
        @if (Model.CustomerModel != null && Model.Mode=="Delete")
        {
            <tr>
                <td>CustomerID :</td>
                <td>
                    @Model.CustomerModel.CustomerID
                    <input type="hidden" 
                    asp-for="CustomerModel.CustomerID" />
                </td>
            </tr>
            <tr>
                <td>CompanyName :</td>
                <td>@Model.CustomerModel.CompanyName</td>
            </tr>
            <tr>
                <td>ContactName :</td>
                <td>@Model.CustomerModel.ContactName</td>
            </tr>
            <tr>
                <td>Country :</td>
                <td>@Model.CustomerModel.Country</td>
            </tr>
            <tr>
                <td colspan="2">
                    <button type="submit" 
                    asp-page-handler="DoDelete">Submit</button>
                    <button type="submit" 
                    asp-page-handler="DoCancel">Cancel</button>
                </td>
            </tr>
        }
    </table>
</form>

This completes the CRUD page for testing our CustomerRepository. Run the application and try performing the insert, update, and delete operations.

In the next article we will discuss how stored procedures can be called using the Microsoft.Data.SqlClient data provider.

That's it for now! Keep coding!!