Data Binding Blazor FlexGrid to SQL Server with Real-Time Updates
The FlexGrid control and the powerful C1DataCollection library make it easy to data bind and show real-time updates from SQL Server in your Blazor web apps. FlexGrid is a fast Blazor datagrid control that supports displaying and editing data, and C1DataCollection helps manage the collection between the UI and the database.
In this post, we will show you how to display a SQL Server table in a Blazor application using FlexGrid. And with the help of C1DataCollection, the records will be updated automatically as they are modified in the database.
In order to implement this sample, I will show the following steps:
- Setup the SQLServer data base
- Create a Blazor application
- Create a SQL Server DataCollection
- Bind FlexGrid to the SQL Server DataCollection
Setup the SQL Server Database
For this sample, I will use a database named “TableDependencyDB” hosted in a SqlExpress instance, with a table named “Products”. The table was created using the following SQL statement:
CREATE TABLE [dbo].[Products](
[Code] [nvarchar](50) NULL,
[Name] [nvarchar](50) NULL,
[Price] [decimal](18, 0) NULL
) ON [PRIMARY]
I've added some records for demonstration purposes:
Create a Blazor Application
The next step is to create a server-side Blazor application and add the NuGet packages C1.Blazor.Grid, C1.DataCollection.AdoNet and SqlTableDependency, which we will use later.
On the Index.razor page define a FlexGrid with two columns as below:
<FlexGrid ItemsSource="Items" AutoGenerateColumns="false" Style="@("width:100%;")">
<FlexGridColumns>
<GridColumn Binding="Name" Width="GridLength.Star"></GridColumn>
<GridColumn Binding="Price"></GridColumn>
</FlexGridColumns>
</FlexGrid>
The grid is bound to a collection of Products, which we’ll populate later from SQL Server. For now I’ve also defined the Product class as below:
public class Product
{
public string Code { get; set; }
public string Name { get; set; }
public decimal Price { get; set; }
}
For more details about how to create the server Blazor application and bind FlexGrid you can read my previous article, upgrading a Blazor HTML table with FlexGrid.
Create a SQL Server DataCollection
The next step is to create a C1DataCollection to hold and manage the items from the database. C1DataCollection is familiar and easy to use because it is based off the standard .NET implementation of ICollectionView. Plus, it gives us more features such as data virtualization and asynchronous server operations.
The C1DataCollection library includes several additional data components that are especially designed for certain collection scenarios. Because we want the data to come from a SQL Server database, we will be inheriting the C1AdoNetCursorDataCollection, which already populates the data for you while you navigate in the Blazor FlexGrid.
The core implementation of this sample is the SQLServerDataCollection class below:
public class SQLServerDataCollection<T> : C1AdoNetCursorDataCollection<T>, IDisposable
where T : class, new()
{
public SQLServerDataCollection(DbConnection connection, string tableName, IEqualityComparer<T> comparer = null, IEnumerable<string> fields = null)
: base(connection, tableName, fields)
{
SynchronizationContext = SynchronizationContext.Current;
Comparer = comparer ?? EqualityComparer<T>.Default;
Notifier = new SqlTableDependency<T>(connection.ConnectionString, tableName);
Notifier.OnChanged += OnTableDependencyChanged;
Notifier.Start();
}
public void Dispose()
{
Notifier.OnChanged -= OnTableDependencyChanged;
Notifier.Stop();
}
public SynchronizationContext SynchronizationContext { get; }
public IEqualityComparer<T> Comparer { get; }
private SqlTableDependency<T> Notifier { get; }
private void OnTableDependencyChanged(object sender, RecordChangedEventArgs<T> e)
{
SynchronizationContext.Post(new SendOrPostCallback(o =>
{
switch (e.ChangeType)
{
case ChangeType.Delete:
{
var index = InternalList.FindIndex(o => Comparer.Equals(o, e.Entity));
if (index >= 0)
{
var oldItem = InternalList[index];
InternalList.RemoveAt(index);
OnCollectionChanged(new NotifyCollectionChangedEventArgs(NotifyCollectionChangedAction.Remove, oldItem, index));
}
}
break;
case ChangeType.Insert:
{
InternalList.Add(e.Entity);
OnCollectionChanged(new NotifyCollectionChangedEventArgs(NotifyCollectionChangedAction.Add, e.Entity, InternalList.Count - 1));
}
break;
case ChangeType.Update:
{
var index = InternalList.FindIndex(o => Comparer.Equals(o, e.Entity));
if (index >= 0)
{
var oldItem = InternalList[index];
InternalList[index] = e.Entity;
OnCollectionChanged(new NotifyCollectionChangedEventArgs(NotifyCollectionChangedAction.Replace, e.Entity, oldItem, index));
}
}
break;
}
}), e);
}
}
Because ADO.NET does not have a standard way to receive notifications from the database when it changes, we will use the NuGet package “SqlTableDependency” to simplify its development.
The collection will attach to the “OnChanged” event of SqlTableDependency, and it will update the internal items accordingly, as well as notifying the changes by calling the OnCollectionChanged method of the collection.
Notice the collection keeps a reference to the SynchronizationContext to raise CollectionChanged events in the same thread where the collection was created.
Implementing IDisposable is important to stop the listener and detach from the event, otherwise the collection will continue listening to the events even after the page is not used anymore.
Notice the collection takes a parameter of type IEqualityComparer which will be used to determine whether the items received from the SqlTableDependency are the same as the saved in the collection. Typically, the implementation will compare the primary key of the items.
I’ve written the following ProductComparer class to determine equal products:
public class ProductComparer : EqualityComparer<Product>
{
public override bool Equals([AllowNull] Product x, [AllowNull] Product y)
{
if (null == x && null == y)
return true;
if (null == x || null == y)
return false;
return x.Code == y.Code;
}
public override int GetHashCode([DisallowNull] Product obj)
{
return obj.Code.GetHashCode();
}
}
For more details about using ADO.Net sources from C1DataCollection, you can read our post, apply data virtualization in FlexGrid for WinForms with OData.
Bind FlexGrid to the SQL Server DataCollection
The final step is to populate the Items collection with the SQLServerDataCollection. In the razor page initialization, we will create the SqlConnection with your connection string and then we will pass that to the SQLServerDataCollection instance to get the items.
protected override void OnInitialized()
{
var sqlConnection = new SqlConnection(Configuration["ConnectionString"]);
Items = new SQLServerDataCollection<Product>(sqlConnection, "Products", new ProductComparer());
}
The connection string is saved in appsettings.json file and obtained through the IConfiguration interface injected in the page.
For example, it will look like this:
@inject Microsoft.Extensions.Configuration.IConfiguration Configuration
{
"ConnectionString": "Data Source=PC\\SQLEXPRESS;Initial Catalog=TableDependencyDB;User ID=XXXX;Password=XXXX;"
}
When running the application, you should see the SQL Server database items in the FlexGrid.
When you edit or change items in the database, the data grid will be automatically updated. This is because we handled the Update change event in the SqlServerDataCollection class.
While you can access the FlexGrid and C1DataCollection libraries from NuGet, I recommend you also download the complete Blazor Edition and Service Components libraries to get access to more samples for Blazor FlexGrid and the C1DataCollection.
Alvaro Rivoir