Ant Design Blazor is a worldwide popular Blazor component library. The component Table is a particularly powerful one especially for Blazor Server project. It can seamlessly interact with Entity Framework, supporting pagination, sorting, and filtering, and can get ranged and ordered dataset with very little effort. In this post, we will witness how easy it is.
Step 1
We create a project with Visual Studio 2022 Blazor Server template.
Add Ant Design reference by following https://github.com/ant-design-blazor/ant-design-blazor#import-ant-design-blazor-into-an-existing-project
Step 2
To make it simple, we can use code first to create Entity Framework dbContext and initialize some seed data with a separate sql script.
Add connection string to appsettings.json,
"ConnectionStrings": {
"DefaultConnection": "Data Source=(localdb)\\MSSQLLocalDB;Initial Catalog=PropertyDB;Integrated Security=True;Connect Timeout=30;Encrypt=False;TrustServerCertificate=False;ApplicationIntent=ReadWrite;MultiSubnetFailover=False"
}
Define dbContext and entity,
public class PropertyDbContext: DbContext {
public PropertyDbContext(DbContextOptions < PropertyDbContext > options): base(options) {}
public DbSet < Property > Properties {
get;
set;
}
}
public class Property {
public int Id {
get;
set;
}
public string Address {
get;
set;
} = string.Empty;
public string Suburb {
get;
set;
} = string.Empty;
public string Postcode {
get;
set;
} = string.Empty;
public string State {
get;
set;
} = string.Empty;
}
Register dbContext
var config = new ConfigurationBuilder()
.AddJsonFile("appsettings.json")
.Build();
void BuildOptions(DbContextOptionsBuilder options) => options
.UseSqlServer(config.GetConnectionString("DefaultConnection"));
builder.Services.AddDbContextFactory<PropertyDbContext>(BuildOptions);
Run migrations
dotnet ef migrations add Initial
dotnet ef database update
Seed data
INSERT INTO [dbo].[Properties]
([Address]
,[Suburb]
,[Postcode]
,[State])
VALUES
('1 Elizabeth Rd', 'Sydney', '2000', 'NSW'),
('2 Elizabeth Rd', 'Sydney', '2000', 'NSW'),
('3 Elizabeth Rd', 'Sydney', '2000', 'NSW'),
('4 Elizabeth Rd', 'Sydney', '2000', 'NSW'),
('1 Queen St', 'Rosebery', '2018', 'NSW'),
('2 Queen St', 'Rosebery', '2018', 'NSW'),
('3 Queen St', 'Chatswood', '2067', 'NSW'),
('4 Queen St', 'Chatswood', '2067', 'NSW'),
('1 Ravenswood Ave', 'Carlingford', '2118', 'NSW'),
('2 Ravenswood Ave', 'Carlingford', '2118', 'NSW'),
('3 Ravenswood Ave', 'Epping', '2121', 'NSW'),
('4 Ravenswood Ave', 'Epping', '2121', 'NSW')
GO
For now, database and dbContext are ready.
Step 3
Add Ant Design Table to razor page, and bind its data source to dbContext.
Index.razor.cs
public partial class Index {
[Inject]
IDbContextFactory < PropertyDbContext > DbContextFactory {
get;
set;
}
private IQueryable < Property > Properties {
get;
set;
}
protected override async Task OnInitializedAsync() {
var context = DbContextFactory.CreateDbContext();
Properties = context.Properties;
}
}
Index.razor
<Table DataSource="Properties" TItem="Property">
<Column TData="int"
@bind-Field="context.Id"
Sortable
Filterable
ShowSorterTooltip="false">
</Column>
<Column TData="string"
Title="Address"
@bind-Field="context.Address"
Sortable
Filterable
ShowSorterTooltip="false">
</Column>
<Column TData="string"
Title="Suburb"
@bind-Field="context.Suburb"
Sortable
Filterable
ShowSorterTooltip="false" SorterMultiple="5"/>
<Column TData="string"
Title="Postcode"
@bind-Field="context.Postcode"
Sortable
Filterable
ShowSorterTooltip="false" SorterMultiple="3">
</Column>
<Column TData="string"
Title="State"
@bind-Field="context.State"
Sortable
Filterable
ShowSorterTooltip="false" SorterMultiple="4"/>
</Table>
That's all. Now let's see the effect. To verify it, we can refer to the SQL query.
Scenario 1: pagination
Ant Design table by default uses page size = 10, which we can customize if needed. When first time loaded, or page-navigated, it only fetches offset data range.
SQL
exec sp_executesql N'SELECT [p].[Id], [p].[Address], [p].[Postcode], [p].[State], [p].[Suburb]
FROM [Properties] AS [p]
ORDER BY (SELECT 1)
OFFSET @__p_0 ROWS FETCH NEXT @__p_1 ROWS ONLY',N'@__p_0 int,@__p_1 int',@__p_0=0,@__p_1=10
Scenario 2: sorting
Take column Postcode for instance.
SQL
exec sp_executesql N'SELECT [p].[Id], [p].[Address], [p].[Postcode], [p].[State], [p].[Suburb]
FROM [Properties] AS [p]
ORDER BY [p].[Postcode]
OFFSET @__p_0 ROWS FETCH NEXT @__p_1 ROWS ONLY',N'@__p_0 int,@__p_1 int',@__p_0=0,@__p_1=10
Scenario 3: Filtering
Take column Id for instance.
SQL
exec sp_executesql N'SELECT [p].[Id], [p].[Address], [p].[Postcode], [p].[State], [p].[Suburb]
FROM [Properties] AS [p]
WHERE ([p].[Id] > 5) AND ([p].[Id] < 10)
ORDER BY [p].[Id]
OFFSET @__p_0 ROWS FETCH NEXT @__p_1 ROWS ONLY',N'@__p_0 int,@__p_1 int',@__p_0=0,@__p_1=10
Scenario 4: combination with multiple sorting and filtering
SQL
exec sp_executesql N'SELECT [p].[Id], [p].[Address], [p].[Postcode], [p].[State], [p].[Suburb]
FROM [Properties] AS [p]
WHERE ([p].[Id] > 5) AND ([p].[Id] < 10)
ORDER BY [p].[Postcode], [p].[Suburb] DESC
OFFSET @__p_0 ROWS FETCH NEXT @__p_1 ROWS ONLY',N'@__p_0 int,@__p_1 int',@__p_0=0,@__p_1=10
From the Blazor Server sample above, we can tell that by just simply binding the Table data source to Entity Framework dbContext, we can achieve data query pagination/offset, sorting and filtering. We can utilize this advantage for data read purpose even in production and it would significantly improve development efficiency and database access performance.
GitHub: https://github.com/YongQuan-dotnet/AntTableSample