Blogs

Bind SQL data to Blazor Smart.Grid

Setup The Blazor Application

Follow the Getting Started guide to set up your Blazor Application with Smart UI.

Create SQL Data

The following steps detail how to create a SQL Database in Visual Studio 2019 and fill it with data. If you already have SQL Data, continue to Connect Blazor to SQL Data.

1. To create a table, first you need to create a database for your application. Navigate to View -> SQL Server Object Explorer
2. Inside the localdb -> Databases directory, create a new SQL database by right-clicking on the Databases folder. For the purpose of the Demo, we will create people.db
3. To create a table, right-click on the database and select New Query…. Then paste the following SQL code to create a table of our clients:

   CREATE TABLE [dbo].[peopleTable] (
     [Id]      INT        NOT NULL,
     [Name]    NCHAR (50) NULL,
     [Balance] FLOAT (50) NULL,
     [City]    NCHAR (50) NULL,
     [Country] NCHAR (50) NULL,
     PRIMARY KEY CLUSTERED ([Id] ASC)
   );
   
   INSERT INTO [dbo].[peopleTable] ([Id], [Name], [Balance], [City], [Country]) VALUES (1, N'Maria Anders', 130.0000, N'Berlin', N'Germany')
   INSERT INTO [dbo].[peopleTable] ([Id], [Name], [Balance], [City], [Country]) VALUES (2, N'Ana Trujillo', 230.0000, N'Mxico D.F.', N'Mexico')
   INSERT INTO [dbo].[peopleTable] ([Id], [Name], [Balance], [City], [Country]) VALUES (3, N'Antonio Moreno', 3500.0000, N'Mxico D.F.', N'Mexico')
   INSERT INTO [dbo].[peopleTable] ([Id], [Name], [Balance], [City], [Country]) VALUES (4, N'Thomas Hardy', 55.0000, N'London', N'UK')
   INSERT INTO [dbo].[peopleTable] ([Id], [Name], [Balance], [City], [Country]) VALUES (5, N'Christina Berglund', 1500.0000, N'Lule', N'Sweden')
   INSERT INTO [dbo].[peopleTable] ([Id], [Name], [Balance], [City], [Country]) VALUES (6, N'Hanna Moos', 650.0000, N'Mannheim', N'Germany')
   INSERT INTO [dbo].[peopleTable] ([Id], [Name], [Balance], [City], [Country]) VALUES (7, N'Frdrique Citeaux', 50.0000, N'Strasbourg', N'France')
   INSERT INTO [dbo].[peopleTable] ([Id], [Name], [Balance], [City], [Country]) VALUES (8, N'Martn Sommer', 0.0000, N'Madrid', N'Spain')

   

Connect Blazor to SQL Data

The following steps detail how to connect your SQL Data to the Blazor Application. If your data is already connected, continue to Bind Grid to SQL Data

1. Inside the Solution Explorer, right-click on your Solution and add a new project of type Class Library and call it DataAccessLibrary
2. Using the Visual Studio NuGet Package Manager, add the following dependacnies to DataAccessLibrary:
   • Microsoft.Extensions.Configuration.Abstractions
   • System.Data.SqlClient
   • Dapper
   
3. Inside DataAcessLibrary, create a new folder “Models”, then create a new new item of type Class called PersonModel.cs
   This is where we will define the properties of each individual Person from our SQL table:

   using System;
   using System.Collections.Generic;
   using System.Text;
   
   namespace DataAccessLibrary.Models
   {
       public class PersonModel
       {
           public int Id { get; set; }
           public string Name { get; set; }
           public decimal Balance { get; set; }
           public string City { get; set; }
           public string Country { get; set; }
       }
   }

   
4. Inside DataAcessLibrary, create a new new item of type Class called SqlDataAccess.cs
   This is where we will create the LoadData function:

   using Dapper;
   using Microsoft.Extensions.Configuration;
   using System;
   using System.Collections.Generic;
   using System.Data;
   using System.Data.SqlClient;
   using System.Linq;
   using System.Text;
   using System.Threading.Tasks;
   
   namespace DataAccessLibrary
   {
       public class SqlDataAccess
       {
           private readonly IConfiguration _config;
   
           public string ConnectionStringName { get; set; } = "Default";
   
           public SqlDataAccess(IConfiguration config)
           {
               _config = config;
           }
   
           public async Task<List<T>> LoadData<T, U>(string sql, U parameters)
           {
               string connectionString = _config.GetConnectionString(ConnectionStringName);
   
               using (IDbConnection connection = new SqlConnection(connectionString))
               {
                   var data = await connection.QueryAsync<T>(sql, parameters);
   
                   return data.ToList();
               }
           }
       }
   }

   Select the SqlDataAccess class and create an Interface by navigating to Quick Actions & Refactoring -> Extract Interface -> OK
5. Inside DataAcessLibrary, create a new new item of type Class called PeopleData.cs
   Here we will create the GetPeople method, which executes a sql query and returns an array, where each item is a Person object:

   using DataAccessLibrary.Models;
   using System;
   using System.Collections.Generic;
   using System.Text;
   using System.Threading.Tasks;
   
   namespace DataAccessLibrary
   {
       public class PeopleData
       {
           private readonly ISqlDataAccess _db;
   
           public PeopleData(ISqlDataAccess db)
           {
               _db = db;
           }
   
           public Task<List<PersonModel>> GetPeople()
           {
               string sql = "select * from dbo.peopleTable";
   
               return _db.LoadData<PersonModel, dynamic>(sql, new { });
           }
       }
   }

   Then create a new interface for PeopleData by following the same steps as for SqlDataAccess

   

6. Finally, navigate to people.db using the SQL Server Object Explorer, right-click and select properties. Then copy the value of the “Connection string” property

   

   Inside your Blazor Application, navigate to appsettings.json and set ConnectionStrings.Default to the copied value:

   

Bind Grid to SQL Data

1. Add the Grid component to the Pages/Index.razor file of your Blazor Application and set the Column you want to dispplay:

   <Grid DataSource="@people" DataSourceSettings="@dataSourceSettings">
     <Columns>
       <Column DataField="Name" Label="Client Name"></Column>
       <Column DataField="Balance" Label="Acccount Balance"></Column>
       <Column DataField="City" Label="City"></Column>
       <Column DataField="Country" Label="Country"></Column>
     </Columns>
   </Grid>

2. Inject the SQl database and the Models at the top of the page:

   @page "/"
   @using Smart.Blazor
   @using DataAccessLibrary
   @using DataAccessLibrary.Models
   
   @inject IPeopleData _db
             

3. Inside the @code block, invoke GetPeople() when the page has loaded and set the people Array as a DataSource to the Grid. Then specify the DataSourceType inside a GridDataSourceSettings object and set it as a property of the Grid.
   Note that setting the DataType of the Columns is not mandatory, but it is recommended if you plan to use the Smart.Grid’s Filtering & Sorting functionalities

   @page "/"
   @using Smart.Blazor
   @using DataAccessLibrary
   @using DataAccessLibrary.Models
   
   @inject IPeopleData _db
   
   <h2>Clients Table</h2>
   @if (people == null)
   {
     <p><em>Loading...</em></p>
   }
   else
   {
     <Grid DataSource="@people" DataSourceSettings="@dataSourceSettings">
       <Columns>
         <Column DataField="Name" Label="Client Name"></Column>
         <Column DataField="Balance" Label="Acccount Balance"></Column>
         <Column DataField="City" Label="City"></Column>
         <Column DataField="Country" Label="Country"></Column>
       </Columns>
     </Grid>
   }
   @code {
       GridDataSourceSettings dataSourceSettings = new GridDataSourceSettings()
       {
           DataFields = new List<IGridDataSourceSettingsDataField>()
   {
               new GridDataSourceSettingsDataField() { Name = "Name", DataType = GridDataSourceSettingsDataFieldDataType.String },
               new GridDataSourceSettingsDataField() { Name = "Balance", DataType = GridDataSourceSettingsDataFieldDataType.Number },
               new GridDataSourceSettingsDataField() { Name = "City", DataType = GridDataSourceSettingsDataFieldDataType.String },
               new GridDataSourceSettingsDataField() { Name = "Country", DataType = GridDataSourceSettingsDataFieldDataType.String }
           },
           DataSourceType = GridDataSourceSettingsDataSourceType.Array
       };
   
       private List<PersonModel> people;
   
       protected override async Task OnInitializedAsync()
       {
           people = await _db.GetPeople();
       }
   }
             

Continue from here

Follow the Get Started with Grid guide to learn more about many of the features offered by Blazor Smart.Grid component.