Blazor Grid – Bind to SQL

Setup The Blazor Software

Observe the Getting Began
information to arrange your Blazor Software with Good UI.

Create SQL Knowledge

The next steps element the way to create a SQL Database in Visible Studio 2019 and fill it with knowledge.
If you have already got SQL Knowledge, proceed to Join Blazor to SQL Knowledge.

1. To create a desk, first it is advisable create a database to your utility. Navigate to View -> SQL Server Object Explorer
2. Contained in the localdb -> Databases listing, create a brand new SQL database by right-clicking on the Databases folder.
   For the aim of the Demo, we are going to create folks.db
   
3. To create a desk, right-click on the database and choose New Question…. Then paste the next SQL code to create a desk of our purchasers:

   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')

   
   

Join Blazor to SQL Knowledge

The next steps element the way to join your SQL Knowledge to the Blazor Software.
In case your knowledge is already related, proceed to Bind Grid to SQL Knowledge

1. Contained in the Answer Explorer, right-click in your Answer and add a brand new challenge of kind Class Library and name it DataAccessLibrary
   
2. Utilizing the Visible Studio NuGet Bundle Supervisor, add the next dependacnies to DataAccessLibrary:
   • Microsoft.Extensions.Configuration.Abstractions
   • System.Knowledge.SqlClient
   • Dapper

   

3. Inside DataAcessLibrary, create a brand new folder “Fashions”, then create a brand new new merchandise of kind Class known as PersonModel.cs
   
   That is the place we are going to outline the properties of every particular person Particular person from our SQL desk:

   utilizing System;
   utilizing System.Collections.Generic;
   utilizing System.Textual content;
   
   namespace DataAccessLibrary.Fashions
   {
       public class PersonModel
       {
           public int Id { get; set; }
           public string Title { get; set; }
           public decimal Stability { get; set; }
           public string Metropolis { get; set; }
           public string Nation { get; set; }
       }
   }

   
   

4. Inside DataAcessLibrary, create a brand new new merchandise of kind Class known as SqlDataAccess.cs
   
   That is the place we are going to create the LoadData perform:

   utilizing Dapper;
   utilizing Microsoft.Extensions.Configuration;
   utilizing System;
   utilizing System.Collections.Generic;
   utilizing System.Knowledge;
   utilizing System.Knowledge.SqlClient;
   utilizing System.Linq;
   utilizing System.Textual content;
   utilizing System.Threading.Duties;
   
   namespace DataAccessLibrary
   {
       public class SqlDataAccess
       {
           personal readonly IConfiguration _config;
   
           public string ConnectionStringName { get; set; } = "Default";
   
           public SqlDataAccess(IConfiguration config)
           {
               _config = config;
           }
   
           public async Process<Listing<T>> LoadData<T, U>(string sql, U parameters)
           {
               string connectionString = _config.GetConnectionString(ConnectionStringName);
   
               utilizing (IDbConnection connection = new SqlConnection(connectionString))
               {
                   var knowledge = await connection.QueryAsync<T>(sql, parameters);
   
                   return knowledge.ToList();
               }
           }
       }
   }

   
   Choose the SqlDataAccess class and create an Interface by navigating to Fast Actions & Refactoring -> Extract Interface -> OK
   

5. Inside DataAcessLibrary, create a brand new new merchandise of kind Class known as PeopleData.cs
   
   Right here we are going to create the GetPeople technique, which executes a sql question and returns an array, the place every merchandise is a Particular person object:

   utilizing DataAccessLibrary.Fashions;
   utilizing System;
   utilizing System.Collections.Generic;
   utilizing System.Textual content;
   utilizing System.Threading.Duties;
   
   namespace DataAccessLibrary
   {
       public class PeopleData
       {
           personal readonly ISqlDataAccess _db;
   
           public PeopleData(ISqlDataAccess db)
           {
               _db = db;
           }
   
           public Process<Listing<PersonModel>> GetPeople()
           {
               string sql = "choose * from dbo.peopleTable";
   
               return _db.LoadData<PersonModel, dynamic>(sql, new { });
           }
       }
   }

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

   
   

6. Lastly, navigate to folks.db utilizing the SQL Server Object Explorer, right-click and choose properties.
   Then copy the worth of the “Connection string” property

   
   

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

   
   

Bind Grid to SQL Knowledge

1. Add the Grid part to the Pages/Index.razor file of your Blazor Software and set the Column you wish to dispplay:

   <Grid DataSource="@folks" DataSourceSettings="@dataSourceSettings">
     <Columns>
       <Column DataField="Title" Label="Shopper Title"></Column>
       <Column DataField="Stability" Label="Acccount Stability"></Column>
       <Column DataField="Metropolis" Label="Metropolis"></Column>
       <Column DataField="Nation" Label="Nation"></Column>
     </Columns>
   </Grid>

2. Inject the SQl database and the Fashions on the prime of the web page:

   @web page "https://www.jqwidgets.com/"
   @utilizing Good.Blazor
   @utilizing DataAccessLibrary
   @utilizing DataAccessLibrary.Fashions
   
   @inject IPeopleData _db
             

3. 
   Contained in the @code block, invoke GetPeople() when the web page has loaded and set the folks Array as a DataSource to the Grid. Then specify the DataSourceType inside a GridDataSourceSettings object and set it as a property of the Grid.
   Be aware that setting the DataType of the Columns will not be obligatory, however it is suggested in case you plan to make use of the Good.Grid’s Filtering & Sorting functionalities

   @web page "https://www.jqwidgets.com/"
   @utilizing Good.Blazor
   @utilizing DataAccessLibrary
   @utilizing DataAccessLibrary.Fashions
   
   @inject IPeopleData _db
   
   <h2>Purchasers Desk</h2>
   @if (folks == null)
   {
     <p><em>Loading...</em></p>
   }
   else
   {
     <Grid DataSource="@folks" DataSourceSettings="@dataSourceSettings">
       <Columns>
         <Column DataField="Title" Label="Shopper Title"></Column>
         <Column DataField="Stability" Label="Acccount Stability"></Column>
         <Column DataField="Metropolis" Label="Metropolis"></Column>
         <Column DataField="Nation" Label="Nation"></Column>
       </Columns>
     </Grid>
   }
   @code {
       GridDataSourceSettings dataSourceSettings = new GridDataSourceSettings()
       {
           DataFields = new Listing<IGridDataSourceSettingsDataField>()
   {
               new GridDataSourceSettingsDataField() { Title = "Title", DataType = GridDataSourceSettingsDataFieldDataType.String },
               new GridDataSourceSettingsDataField() { Title = "Stability", DataType = GridDataSourceSettingsDataFieldDataType.Quantity },
               new GridDataSourceSettingsDataField() { Title = "Metropolis", DataType = GridDataSourceSettingsDataFieldDataType.String },
               new GridDataSourceSettingsDataField() { Title = "Nation", DataType = GridDataSourceSettingsDataFieldDataType.String }
           },
           DataSourceType = GridDataSourceSettingsDataSourceType.Array
       };
   
       personal Listing<PersonModel> folks;
   
       protected override async Process OnInitializedAsync()
       {
           folks = await _db.GetPeople();
       }
   }
             

Proceed from right here

Observe the Get Began with Grid
information to study extra about most of the options provided by Blazor Good.Grid part.