smart-html-elements

Blazor Kanban - Data Bind to SQL

Blazor Smart.Kanban Bind to SQL

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 2022 and fill it with data. If you already have SQL Data, continue to Connect Blazor to SQL Data.

To create a table, first you need to create a database for your application. Navigate to View -> SQL Server Object Explorer
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 tasks.db

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 kanban tasks:

CREATE TABLE [dbo].[KanbanTable] (
  [Id]       INT IDENTITY(1,1)  NOT NULL,
  [Text]     NVARCHAR (255) NULL,
  [Status]   NVARCHAR (50) NULL,
  [Priority] NVARCHAR (50) NULL,
  PRIMARY KEY CLUSTERED ([Id] ASC)
);

INSERT INTO [dbo].[KanbanTable] ([Text], [Status], [Priority]) VALUES (N'Editing', N'ToDo', N'high');
INSERT INTO [dbo].[KanbanTable] ([Text], [Status], [Priority]) VALUES (N'Header', N'ToDo', N'normal');
INSERT INTO [dbo].[KanbanTable] ([Text], [Status], [Priority]) VALUES (N'Dragging with feedback', N'ToDo', N'low');
INSERT INTO [dbo].[KanbanTable] ([Text], [Status], [Priority]) VALUES (N'Vertical virtualization', N'ToDo', N'high');
INSERT INTO [dbo].[KanbanTable] ([Text], [Status], [Priority]) VALUES (N'Observable columns array', N'ToDo', N'normal');
INSERT INTO [dbo].[KanbanTable] ([Text], [Status], [Priority]) VALUES (N'Infinite scrolling', N'InProgress', N'high');
INSERT INTO [dbo].[KanbanTable] ([Text], [Status], [Priority]) VALUES (N'Visible/hidden columns', N'InProgress', N'low');
INSERT INTO [dbo].[KanbanTable] ([Text], [Status], [Priority]) VALUES (N'Public methods', N'InProgress', N'high');
INSERT INTO [dbo].[KanbanTable] ([Text], [Status], [Priority]) VALUES (N'Expand/collapse arrow', N'Testing', N'low');
INSERT INTO [dbo].[KanbanTable] ([Text], [Status], [Priority]) VALUES (N'Virtual scrolling', N'Testing', N'high');
INSERT INTO [dbo].[KanbanTable] ([Text], [Status], [Priority]) VALUES (N'Deferred scrolling', N'Testing', N'normal');
INSERT INTO [dbo].[KanbanTable] ([Text], [Status], [Priority]) VALUES (N'Research', N'Done', N'normal');
INSERT INTO [dbo].[KanbanTable] ([Text], [Status], [Priority]) VALUES (N'Displaying data from data source', N'Done', N'normal');
INSERT INTO [dbo].[KanbanTable] ([Text], [Status], [Priority]) VALUES (N'Showing cover and title', N'Done', N'normal');
INSERT INTO [dbo].[KanbanTable] ([Text], [Status], [Priority]) VALUES (N'Property validation', N'Done', N'normal');
INSERT INTO [dbo].[KanbanTable] ([Text], [Status], [Priority]) VALUES (N'formatFunction and formatSettings', N'Done', N'normal');

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 Kanban to SQL Data

Inside the Solution Explorer, right-click on your Solution and add a new project of type Class Library and call it DataAccessLibrary
Using the Visual Studio NuGet Package Manager, add the following dependancies to DataAccessLibrary:
- Microsoft.Extensions.Configuration.Abstractions
- System.Data.SqlClient
- Dapper

Inside DataAcessLibrary, create a new folder "Models", then create a new new item of type Class called TaskModel.cs
This is where we will define the properties of each individual Task from our SQL table:

using System;
using System.Collections.Generic;
using System.Text;

namespace DataAccessLibrary.Models
{
    public class TaskModel
    {
        public int Id { get; set; }
        public string Text { get; set; }
        public string Status { get; set; }
        public string Priority { get; set; }
    }
}

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

Inside DataAcessLibrary, create a new new item of type Class called TasksData.cs
Here we will create the GetTasks method, which executes a sql query and returns an array, where each item is a Task object:

using DataAccessLibrary.Models;
using System;
using System.Collections.Generic;
using System.Text;
using System.Threading.Tasks;

namespace DataAccessLibrary
{
    public class TasksData
    {
        private readonly ISqlDataAccess _db;

        public TasksData(ISqlDataAccess db)
        {
            _db = db;
        }

        public Task<List<TaskModel>> GetTasks()
        {
            string sql = "select * from dbo.KanbanTable";

            return _db.LoadData<TaskModel, dynamic>(sql, new { });
        }
    }
}

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

Add a reference of DataAcessLibrary in the original Blazor project by right-clicking on the BlazorApp and selecting Add -> Project Reference

Inside the Program.cs file, add the SqlDataAccess and TasksData Services:

For .NET 6 and .NET 7:

using MyBlazorApp.Data;
using Microsoft.AspNetCore.Components;
using Microsoft.AspNetCore.Components.Web;
using Smart.Blazor;
using DataAccessLibrary;

var builder = WebApplication.CreateBuilder(args);

// Add services to the container.  
builder.Services.AddRazorPages();
builder.Services.AddServerSideBlazor();
builder.Services.AddSingleton<WeatherForecastService>();
builder.Services.AddTransient<ISqlDataAccess, SqlDataAccess>();
builder.Services.AddTransient<ITasksData, TasksData>();

// Add Smart UI for Blazor.  
builder.Services.AddSmart();

var app = builder.Build();
.....

For .NET 5:

.....
public void ConfigureServices(IServiceCollection services)
{
    services.AddRazorPages();
    services.AddServerSideBlazor();
    services.AddSingleton<WeatherForecastService>();
    services.AddTransient<ISqlDataAccess, SqlDataAccess>();
    services.AddTransient<ITasksData, TasksData>();
    services.AddSmart();
}
.....

Finally, navigate to tasks.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 Kanban to SQL Data

Add the Kanban component to the Pages/Index.razor file of your Blazor Application. Set the task columns as well as the DataSourceMap.
The DataSourceMap property allows us to map the properties of the SQL table to the properties of the Kanban tasks.
```
<Kanban @ref="kanban" Columns="columns" DataSource="tasks" DataSourceMap="dataSourceMap"></Kanban>
```

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

@page "/"
@using DataAccessLibrary
@using DataAccessLibrary.Models

@inject ITasksData _db

Inside the @code block, invoke GetTaskse() when the page has loaded and set the tasks Array as a DataSource to the Kanban

@page "/"
@using DataAccessLibrary
@using DataAccessLibrary.Models

@inject ITasksData _db
<Kanban @ref="kanban" Columns="columns" DataSource="tasks" DataSourceMap="dataSourceMap"></Kanban>


@code{
    Kanban kanban;
    List<KanbanColumn> columns = new List<KanbanColumn>()
      {
        new KanbanColumn()
        {
            DataField = "ToDo",
            Label = "To do",
        },
        new KanbanColumn()
        {
            DataField = "InProgress",
            Label = "In progress"
        },
        new KanbanColumn()
        {
            DataField = "Testing",
            Label = "Testing"
        },
        new KanbanColumn()
        {
            DataField = "Done",
            Label = "Done"
        }
      };

    private object dataSourceMap = new
    {
        id = "Id",
        text = "Text",
        status = "Status",
        priority = "Priority"
    };

    private List<TaskModel> tasks;


    protected override async Task OnInitializedAsync()
    {
        tasks = await _db.GetTasks();
    }
}

Complex Components

Core Components

Blazor Smart.Kanban Bind to SQL

Setup The Blazor Application

Create SQL Data

Connect Blazor to SQL Data

Bind Kanban to SQL Data