Saturday, November 30, 2019

ASP.NET Core Blazor Filtering And Sorting Using Entity Framework And Web API

Introduction
This article will explain in detail about how to create an ASP.NET Core Blazor Filter and Sorting for HTML Table using Entity Framework and Web API.
This article will explain -
  1. How to create an ASP.NET Core Blazor Web application.
  2. How to install the Package for using Entity Framework and creating a DBContext class.
  3. How to get a result from Web API and bind result in Blazor client Razor view
  4. Adding Sorting and Filtering features for the HTML table
ASP.NET Core

Prerequisites

Make sure, you have installed all the prerequisites on your computer. If not, then download and install them all, one by one. Note that since Blazor is a new framework we must have installed a preview of Visual Studio 2017 (15.7) or above.
Step 1 - Create a database and a table
We will be using our SQL Server database for our WEB API and EF. First, we create a database named StudentsDB and a table as StudentMaster. Here is the SQL script to create a database table and sample record insert query in our table. Run the query given below in your local SQL Server to create a database and a table to be used in our project. 
  1. USE MASTER       
  2. GO       
  3.        
  4. -- 1) Check for the Database Exists .If the database is exist then drop and create new DB       
  5. IF EXISTS (SELECT [nameFROM sys.databases WHERE [name] = 'StudentsDB' )       
  6. DROP DATABASE StudentsDB       
  7. GO       
  8.        
  9. CREATE DATABASE StudentsDB       
  10. GO       
  11.        
  12. USE StudentsDB       
  13. GO       
  14.        
  15.        
  16. -- 1) //////////// StudentMasters       
  17.        
  18. IF EXISTS ( SELECT [nameFROM sys.tables WHERE [name] = 'StudentMasters' )       
  19. DROP TABLE StudentMasters       
  20. GO       
  21.        
  22. CREATE TABLE [dbo].[StudentMasters](       
  23.         [StdID] INT IDENTITY PRIMARY KEY,       
  24.         [StdName] [varchar](100) NOT NULL,          
  25.         [Email]  [varchar](100) NOT NULL,          
  26.         [Phone]  [varchar](20) NOT NULL,          
  27.         [Address]  [varchar](200) NOT NULL       
  28. )       
  29.        
  30. -- insert sample data to Student Master table       
  31. INSERT INTO [StudentMasters]   ([StdName],[Email],[Phone],[Address])       
  32.      VALUES ('Shanu','syedshanumcain@gmail.com','01030550007','Madurai,India')       
  33.        
  34. INSERT INTO [StudentMasters]   ([StdName],[Email],[Phone],[Address])       
  35.      VALUES ('Afraz','Afraz@afrazmail.com','01030550006','Madurai,India')       
  36.             
  37. INSERT INTO [StudentMasters]   ([StdName],[Email],[Phone],[Address])       
  38.      VALUES ('Afreen','Afreen@afreenmail.com','01030550005','Madurai,India')       
  39.             
  40.             
  41.      select * from [StudentMasters]    
Step 2 - Create ASP.NET Core Blazor Application
After installing all the prerequisites listed above and ASP.NET Core Blazor Language Services, click Start >> Programs >> Visual Studio 2017 >> Visual Studio 2017 on your desktop. Click New >> Project. Select Web >> ASP.NET Core Angular Web Application. Enter your project name and click OK.
ASP.NET Core
Select Blazor (ASP.NET Core hosted) and click ok.
 ASP.NET Core
After creating ASP.NET Core Blazor Application, wait for a few seconds. You will see the below structure in solution explorer.
ASP.NET Core
What is new in ASP.NET Core Blazor solution?
When we create our new ASP.NET Core Blazor application we can see there will be 3 projects which will be automatically created in the Solution Explorer.
Client Project
The first project created the Client project and it will be our Solutionname.Client and here we can see our Solutionname is “BlazorASPCORE”. As the project is named as a client this project will be mainly focused on all the client-side views. Here, we will be adding all our page views to be displayed on the client side in the browser.
ASP.NET Core
We can see a few sample page have been already added here and we can also see a shared folder like our MVC application where we will be having the Sharedfolder and Layout page for the Master page. Here in Blazor, we have the MainLayout which will be working like the Master page and NavMenu for the left side menu displayed.
Server Project
As the name indicates, this project will be used as a Server project. This project is mainly used to create all our Controllers and WEB API Controllers to perform all business logic and perform CRUD operations using WEB-API’s. In our demo application, we will be adding a Web API in this Server project and all the WEB API in our Client application. This Server project will be working to get/set the data from Database and from our Client project we bind or send the result to this server to perform the CRUD operation in the database.
ASP.NET Core
Shared Project
As the name indicates this project works like a shred project. This project works as a Model for our Server project and for the Client project. The Model declared in this Shared project will be used in both the Server and in the Client project. We also install all the packages needed for our project here, for example, to use the Entity Framework we install all the packages in this Shared project.
ASP.NET Core
Run to test the application
When we run the application, we can see that the left side has navigation and the right side contains the data. We can see the default sample pages and menus will be displayed in our Blazor web site. We can use the page or remove it and start with our own page.
ASP.NET Core
Now, let’s see how to add new page to perform the CRUD operation for maintaining student details.
Using Entity Framework
To use the Entity Framework in our Blazor application we need to install the below packages.
Install the Packages
Go to Tools and then select -> NuGet Package Manager -> Package Manager Console.
ASP.NET Core
You can see the Console at the bottom of the VS 2017 IDE and on the right side of the combobox on the console select the Default project as your shared project "Select Shared”.
ASP.NET Core
 
You can see the PM> and copy and paste the below line to install the Database Provider package. This package is used to set the database provider as SQL Server
Install-Package Microsoft.EntityFrameworkCore.SqlServer
ASP.NET Core
We can see as the package is installed in our Shared folder.
Install the Entity Framework
You can see the PM> and copy and paste the below line to install the EF package.
    Install-Package Microsoft.EntityFrameworkCore.Tools
    ASP.NET Core
    To Create DB Context and set the DB Connection string:
    You can see the PM> and copy and paste the below line, set the Connection string and create DB Context. This is an important part as we give our SQL Server name, Database Name and SQL server UID and SQL Server Password to connect to our database for performing the CRUD operation. We also give our SQL Table name to create the Model class in our Shared project.
      Scaffold-DbContext "Server= YourSqlServerName;Database=StudentsDB;user id= YourSqlUID;password= YourSqlPassword;Trusted_Connection=True;MultipleActiveResultSets=true" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models -Tables StudentMasters
      ASP.NET Core
      Press enter create a connection string, Model Class, and Database Context.
      ASP.NET Core
      We can see StudentMasters Model class and StudentsDBContext class has been created in our Shared project. We will be using this Model and DBContext in our Server project to create our Web API to perform the CRUD operations. 
      Creating Web API to Get Student details
      To create our WEB API Controller, right-click Controllers folder. Click Add New Controller.
      ASP.NET Core
      Here we will be using Scaffold method to create our WEB API. We select API Controller with actions, using Entity Framework.
      ASP.NET Core
      Select our Model and DatabaseContext from the Shared project.
      ASP.NET Core
      Select our StudentMasters Model from the Shared Project for performing the CRUD operation.
      ASP.NET Core
      Select the Data Context Class as our StudentsDBContext from the Shared project. Our Controller name will be automatically added if you need to you can change it and click  ADD.
      ASP.NET Core
      We will be using only the Get method from our Web API.
      ASP.NET Core
      To test Get Method, we can run our project and copy the GET method API path. Here, we can see our API path to get API/StudentMasters/

      Run the program and paste API path to test our output.
      ASP.NET Core
      Now, we will bind all the WEB API JSON results in out View page from our Client project
      Working with Client Project
      First, we need to add the new Razor view page.
      Add Razor View
      To add the Razor view page right click the Pages folder from the Client project. Click on Add >> New Item
      ASP.NET Core
      Select Razor View >> Enter your page name, Here we have given the name as Students.chtml
      ASP.NET Core
      In Razor view Page we have 3 parts of code. The first one is the Import part where we import all the references and models for using in the view, HTML design and data binding part and finally, we have the function part to call all the web APIs to bind in our HTML pages and also to perform client-side business logic to be displayed in the View page.
      Import part
      First, we import all the needed support files and references in our Razor View page. Here we have first imported our Model class to be used in our view and also imported HTTPClient for calling the Web API to perform the CRUD operations.
      1. @using BLAZORASPCORE.Shared  
      2. @using BLAZORASPCORE.Shared.Models  
      3. @page "/Students"  
      4. @using Microsoft.AspNetCore.Blazor.Browser.Interop  
      5. @inject HttpClient Http  
      ASP.NET Core
      HTML design and data Binding part
      Next, we design our Student details page to bind the student details from the Web API to HTML table.
      Adding Sorting features in Table Heading
      In the Table heading part for each heading we add the Sorting Image. In Sorting Image Button Click event we call the Sorting function and pass each Sorting Column name to the click event. In Function code part we sort the Web API and display the results by ascending and in descending order by each column header.
      1. <table style="background-color:#FFFFFF; border-style:inset;border-width:1px;border-color:#6D7B8D; padding:2px;width:100%;table-layout:fixed;" cellpadding="1" cellspacing="1">  
      2.        <tr style="background-color:#2d364d ; color:#FFFFFF ;border-style:dashed;border-width:2px;border-color:0A2464;" >  
      3.            <td width="30" align="center"></td>  
      4.            <td width="80" align="center">  
      5.                <img src="@ImageSortname" onclick="@(async () => await StudentSorting("StdId"))" height="24" width="24"/>  
      6.                Student ID  
      7.                 
      8.            </td>  
      9.            <td width="240" align="center">  
      10.                <img src="@ImageSortname" onclick="@(async () => await StudentSorting("StdName"))" height="24" width="24"/>     
      11.                Student Name    
      12.            </td>  
      13.            <td width="240" align="center">  
      14.                <img src="@ImageSortname" onclick="@(async () => await StudentSorting("Email"))" height="24" width="24"/>  
      15.                Email   
      16.            </td>  
      17.            <td width="120" align="center">  
      18.                <img src="@ImageSortname" onclick="@(async () => await StudentSorting("Phone"))" height="24" width="24"/>  
      19.                Phone   
      20.            </td>  
      21.            <td width="340" align="center">  
      22.                <img src="@ImageSortname" onclick="@(async () => await StudentSorting("Address"))" height="24" width="24"/>  
      23.                Address   
      24.            </td>  
      25.        </tr>  
      Adding Filtering features in Table Heading
      In the Table heading part we add a new row. In table row we add the Textbox for each column to perform the filtering for the bound result. In Textbox onChange event we call the method to perform the Filtering operation from the code function part
      1. <tr style="height: 30px; background-color:#336699 ; color:#FFFFFF ;">  
      2.             <td width="30" align="center"></td>  
      3.             <td width="80" align="center">Filter : </td>  
      4.             <td width="240" align="center">  
      5.                 <input width="70" onchange=@OnstdNameChanged oninput="(this.dispatchEvent(new CustomEvent('change', {bubbles: true})))" />  
      6.             </td>  
      7.             <td width="240" align="center">  
      8.                 <input width="70" onchange=@OnEmailChanged oninput="(this.dispatchEvent(new CustomEvent('change', {bubbles: true})))" />  
      9.             </td>  
      10.             <td width="120" align="center">  
      11.                 <input width="70" onchange=@OnPhoneChanged oninput="(this.dispatchEvent(new CustomEvent('change', {bubbles: true})))" />  
      12.             </td>  
      13.             <td width="340" align="center">  
      14.                 <input width="70" onchange=@OnAddressChanged oninput="(this.dispatchEvent(new CustomEvent('change', {bubbles: true})))" />  
      15.             </td>  
      16.         </tr>  
      Bind the result
      We bind the Web API result in the HTML component part.
      1. @foreach (var StudentMasters in stdMaster)  
      2.         {  
      3.             <tr style="border-style:dashed;border-width:2px;border-color:#659EC7;">  
      4.                 <td align="center" style="border: solid1px#659EC7; padding: 5px;table-layout:fixed;">  
      5.    
      6.   
      7.                 </td>  
      8.                 <td align="center">  
      9.   
      10.                     <span style="color:#9F000F">  
      11.                         @StudentMasters.StdId  
      12.                     </span>  
      13.                 </td>  
      14.                 <td align="center">  
      15.                     <span style="color:#9F000F">  
      16.                         @StudentMasters.StdName  
      17.                     </span>  
      18.                 </td>  
      19.                 <td align="center">  
      20.                     <span style="color:#9F000F">  
      21.                         @StudentMasters.Email  
      22.                     </span>  
      23.                 </td>  
      24.                 <td align="center">  
      25.                     <span style="color:#9F000F">  
      26.                         @StudentMasters.Phone  
      27.                     </span>  
      28.                 </td>  
      29.                 <td align="center">  
      30.                     <span style="color:#9F000F">  
      31.                         @StudentMasters.Address  
      32.                     </span>  
      33.                 </td>  
      34.             </tr>   
      35.         }  
      Function Part
      Function part is to call all the web APIs to bind in our HTML page and also to perform client-side business logic to be displayed in View page.
      Init Method
      In the Init method we get the result of Web API for student details and store it in the StudentMaster object and we use this object to be bound in our HTML table for each statement.
      1. @functions {  
      2.     StudentMasters[] stdMaster;  
      3.   
      4.     StudentMasters stdmst = new StudentMasters();  
      5.   
      6.     int ids = 0;  
      7.     int studentIDs = 0;  
      8.     string ImageSortname = "Images/sortAsc.png";  
      9.   
      10.     protected override async Task OnInitAsync()  
      11.     {  
      12.         stdMaster = await Http.GetJsonAsync<StudentMasters[]>("/api/StudentMasters/");  
      13.     }  
      Sorting Method
      In Sorting image click on each column heading; we call this method and pass the column name to this method. This method depends on column name . We sort the Web API result and bind the result in the HTML table. We also do the reverse sorting of ascending and descending.
      1. //Sorting  
      2.   protected async Task StudentSorting(string SortColumn)  
      3.   {  
      4.       stdMaster = await Http.GetJsonAsync<StudentMasters[]>("/api/StudentMasters/");  
      5.   
      6.   
      7.       if (ids == 0)  
      8.       {  
      9.           ImageSortname = "Images/sortDec.png";  
      10.           ids = 1;  
      11.   
      12.           switch (SortColumn)  
      13.           {  
      14.               case "StdId":  
      15.                   stdMaster = stdMaster.OrderBy(x => x.StdId).ToArray() ;  
      16.                   break;  
      17.               case "StdName":  
      18.                   stdMaster = stdMaster.OrderBy(x => x.StdName).ToArray();  
      19.                   break;  
      20.   
      21.               case "Email":  
      22.                   stdMaster = stdMaster.OrderBy(x => x.Email).ToArray();  
      23.                   break;  
      24.               case "Phone":  
      25.                   stdMaster = stdMaster.OrderBy(x => x.Phone).ToArray();  
      26.                   break;  
      27.               case "Address":  
      28.                   stdMaster = stdMaster.OrderBy(x => x.Address).ToArray();  
      29.                   break;  
      30.   
      31.           }  
      32.       }  
      33.       else  
      34.       {  
      35.           ImageSortname = "Images/sortAsc.png";  
      36.           ids = 0;  
      37.   
      38.           switch (SortColumn)  
      39.           {  
      40.               case "StdId":  
      41.                   stdMaster = stdMaster.OrderByDescending(x => x.StdId).ToArray();  
      42.                   break;  
      43.               case "StdName":  
      44.                   stdMaster = stdMaster.OrderByDescending(x => x.StdName).ToArray();  
      45.                   break;  
      46.   
      47.               case "Email":  
      48.                   stdMaster = stdMaster.OrderByDescending(x => x.Email).ToArray();  
      49.                   break;  
      50.               case "Phone":  
      51.                   stdMaster = stdMaster.OrderByDescending(x => x.Phone).ToArray();  
      52.                   break;  
      53.               case "Address":  
      54.                   stdMaster = stdMaster.OrderByDescending(x => x.Address).ToArray();  
      55.                   break;  
      56.           }  
      57.       }    
      58.   }  
      Filtering Method
      In each column heading part we have added a new row for performing the Filtering of the HTML grid. In each column filter Textbox Change event we pass the Textbox value. We call a common filtering method, studentFilteringList, and in this method we pass the filtering column Textbox value and column Name.
      1. // For Filtering by Student Name  
      2.     void OnstdNameChanged(UIChangeEventArgs args)  
      3.     {  
      4.         string values = args.Value.ToString();  
      5.         studentFilteringList(values, "StudentName");  
      6.     }  
      7.   
      8.   
      9.     // For Filtering by Email  
      10.     void OnEmailChanged(UIChangeEventArgs args)  
      11.     {  
      12.         string values = args.Value.ToString();  
      13.         studentFilteringList(values, "Email");  
      14.     }  
      15.   
      16.   
      17.     // For Filtering by Phone  
      18.     void OnPhoneChanged(UIChangeEventArgs args)  
      19.     {  
      20.         string values = args.Value.ToString();  
      21.         studentFilteringList(values, "Phone");  
      22.     }  
      23.   
      24.   
      25.     // For Filtering by Adress  
      26.     void OnAddressChanged(UIChangeEventArgs args)  
      27.     {  
      28.         string values = args.Value.ToString();  
      29.         studentFilteringList(values, "Address");  
      30.     }  
      Here, we create a common function named studentFilteringList and in this method we get the filtering column Textbox value and column Name. We do filtering from the Web API and bind the filtering result to the HTML Table.
      1. //Filtering  
      2. protected async Task studentFilteringList(String Value,string columnName)  
      3. {  
      4.     stdMaster = await Http.GetJsonAsync<StudentMasters[]>("/api/StudentMasters/");  
      5.   
      6.     if (Value.Trim().Length >0)  
      7.     {  
      8.         switch (columnName)  
      9.         {  
      10.             case "StudentName":  
      11.                 stdMaster = stdMaster.Where(x => x.StdName.Contains(Value)).ToArray();  
      12.                 break;  
      13.   
      14.             case "Email":  
      15.                 stdMaster = stdMaster.Where(x => x.Email.Contains(Value)).ToArray();  
      16.                 break;  
      17.             case "Phone":  
      18.                 stdMaster = stdMaster.Where(x => x.Phone.Contains(Value)).ToArray();  
      19.                 break;  
      20.             case "Address":  
      21.                 stdMaster = stdMaster.Where(x => x.Address.Contains(Value)).ToArray();  
      22.                 break;  
      23.             default:  
      24.                 stdMaster = await Http.GetJsonAsync<StudentMasters[]>("/api/StudentMasters/");  
      25.                 break;  
      26.         }  
      27.     }  
      28.     else  
      29.     {  
      30.         stdMaster = await Http.GetJsonAsync<StudentMasters[]>("/api/StudentMasters/");   
      31.     }   
      32. }  
      Navigation Menu
      Now, we need to add this newly added Student Razor page to our left Navigation. For adding this open the Shared Folder and open the NavMenu.cshtml page and add the menu.
      1. <li class="nav-item px-3">  
      2.             <NavLink class="nav-link" href="/Students">  
      3.                 <span class="oi oi-list-rich" aria-hidden="true"></span> Students Details  
      4.             </NavLink>  
      5.         </li>  
      ASP.NET Core
      Build and Run the application
      ASP.NET Core
      Conclusion
      Note that when creating the DBContext and setting the connection string, don’t forget to add your SQL connection string. Hope you all like this article and in the next article, we will see more examples to work with Blazor. It's really very cool and awesome to work with Blazor.

      No comments:

      Post a Comment

      Lab 09: Publish and subscribe to Event Grid events

        Microsoft Azure user interface Given the dynamic nature of Microsoft cloud tools, you might experience Azure UI changes that occur after t...