Download full solution from Github

If you are interested, you can read my other articles about Blazor dynamic forms:

If you don’t have Microsoft Visual Studio, you can download a free ‘Community’ version from here:

Microsoft Visual Studio

Create Demo Project

Using Visual Studio, click ‘Create a new Project’ and find Blazor Server App.

Click ‘Next’ and set project name and solution name, and click ‘Next’, then choose ‘.NET 5.0’ Target Framework and click ‘Create’.

Visual Studio should create a new project for you:

Add Platz Packages

The next step is to add NuGet packages - right click on the project and click on the menu item ‘Manage NuGet Packages…’.

Select ‘Browse’ tab and type ‘Platz’ in the search box.

We need to install ‘Platz.SqlForms’ and ‘Platz.ObjectBuilder’ and extend Startup.cs by adding Platz initialization code:

        public void ConfigureServices(IServiceCollection services)
        {
            services.AddRazorPages();
            services.AddServerSideBlazor();
            services.AddSingleton<WeatherForecastService>();
            // Platz
            services.AddPlatzSqlForms();
            services.AddPlatzObjectBuilder();
        }

Next, we need to add a project folder where the schema and query configurations will be stored – right click on the project and select ‘Add’, then ‘New Folder’ and type ‘SchemaStore’.

How Platz Object Builders are Used

Platz Schema Designer and Query Builder are no-code elements that we added to simplify and speed up the development process.

Using Schema Designer, we can visually define database tables and relationships and save them to a configuration file in json format. Then applying t4 template, we can generate C# code that will manage the defined database and provide CRUD operations for Platz dynamic UI components.

The Query Builder is used for visual design of database queries to retrieve data using the full power of SQL. The result query is stored in the configuration json file and can be used for C# code generation.

The code generated by T4 templates can be natively connected to Platz dynamic UI components, so you don’t need to do manual coding.

Setup Platz No-Code Builders

To set up no-code builders, we need to create two razor pages and register them in Shared\NavMenu.razor, also you can delete the Visual Studio demo pages (Counter.razor and FetchData.razor).

Add ‘SchemaDesigner.razor’ to ‘Pages’ folder:

@page "/SchemaDesigner"
@using Platz.ObjectBuilder

<SchemaComponent StoreDataPath="SchemaStore" DataService="PlatzDemoService" 
                 Namespace="PlatzDemo.SchemaStore"
                 TargetConnectionString="Server=(localdb)\mssqllocaldb;
                 Database=PlatzDemo;Trusted_Connection=True;MultipleActiveResultSets=true" />

Add ‘QueryDesigner.razor’ to ‘Pages’ folder:

@page "/QueryDesigner"
@using Platz.ObjectBuilder

<QueryComponent SourceSchemaFile="SchemaStore\PlatzDemo.schema.json" 
                StoreDataPath="SchemaStore"
                DataService="PlatzDemoDataContext" Namespace="PlatzDemo.SchemaStore" />

Add links to JQuery and bootstrap to ‘Pages\_Host.cshtml’:

...
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="utf-8" />
    <meta name="viewport" content="width=device-width, initial-scale=1.0" />
    <title>Platz.SqlForms.Demo</title>
    <base href="~/" />

    @*Added for Platz*@
    <link rel="stylesheet" 
     href="https://cdn.jsdelivr.net/npm/bootstrap@4.5.3/dist/css/bootstrap.min.css" 
     integrity="sha384-TX8t27EcRE3e/ihU7zmQxVncDAy5uIKz4rEkgIXeMed4M0jlfIDPvg6uqKI2xXr2" 
     crossorigin="anonymous">
    <script src="https://code.jquery.com/jquery-3.5.1.slim.min.js" 
     integrity="sha384-DfXdz2htPH0lsSSs5nCTpuj/zy4C+OGpamoFVy38MVBnE+IbbVYUew+OrCXaRkfj" 
     crossorigin="anonymous"></script>
    <script src="https://cdn.jsdelivr.net/npm/popper.js@1.16.1/dist/umd/popper.min.js" 
     integrity="sha384-9/reFTGAW83EW2RDu2S0VKaIzap3H66lZH81PoYlFhbGU+6BZp6G7niu735Sk7lN" 
     crossorigin="anonymous"></script>
    <script src="https://cdn.jsdelivr.net/npm/bootstrap@4.5.3/dist/js/bootstrap.min.js" 
     integrity="sha384-w1Q4orYjBQndcko6MimVbzY0tgp4pWB4lZ7lr30WKz0vr/aWKhXdBNmNb5D92v7s" 
     crossorigin="anonymous"></script>

    <link href="css/site.css" rel="stylesheet" />
    <link href="Platz.SqlForms.Demo.styles.css" rel="stylesheet" />
</head>
...

and change ‘Shared\ NavMenu.razor’:

...
<div class="@NavMenuCssClass" @onclick="ToggleNavMenu">
    <ul class="nav flex-column">
        <li class="nav-item px-3">
            <NavLink class="nav-link" href="" Match="NavLinkMatch.All">
                <span class="oi oi-home" aria-hidden="true"></span> Home
            </NavLink>
        </li>
        <li class="nav-item px-3">
            <NavLink class="nav-link" href="SchemaDesigner">
                <span class="oi oi-list-rich" aria-hidden="true"></span> Admin Schemas
            </NavLink>
        </li>
        <li class="nav-item px-3">
            <NavLink class="nav-link" href="QueryDesigner">
                <span class="oi oi-list-rich" aria-hidden="true"></span> Admin Queries
            </NavLink>
        </li>
    </ul>
</div>
...

Design Demo Database

Now let’s run the application and click on 'Admin Schemas' page. You will see the page with a new Schema, that is ready to be created.

Enter ‘PlatzDemo’ as the Schema name and select the ‘Use INT autoincrement Id’ option. Now you can add tables by clicking the green ‘Add’ button.

Each table should have an ‘Id’ column, that identifies each record and allows us to create relationships between tables.

For each column, you should specify Name and Type.

I created a ‘Product’ table:

Now, I would like to add two more tables to create a client order entry system, you can see the added tables on the diagram. When all tables have been added, click the ‘Save’ button on the ‘Schema’ tab.

You can see that I added references from ‘OrderItem’ table to ‘Order’ and to ‘Product’.

Build Queries

When the schema is saved, we can click on ‘Admin Queries’ menu and use the defined tables to build a query.

In the screen below, you can see the designed tables joined to produce output that will be required for the Order Item List page.

When you close the browser, you should see that the project folder ‘SchemaStore’ now contains files: ‘GetOrderItemProductList.json’ with query definition, ‘PlatzDemo.schema.json’ and ‘PlatzDemo.schema.migrations.json’ with schema definition.

Generate Data Context Code

Now we can generate database ORM code using t4 template. For that, create a project folder ‘SchemaServices’ and create a text file inside it named ‘PlatzDemoDataContext.txt’, then open the project file ‘Platz.Config.Link\CopyMe.SchemaStoreDataContext.tt.txt’ and copy its content to the created file ‘PlatzDemoDataContext.txt’. Inside this file, modify path to your Schema json:

<# // ================================================================ 
Set JsonStorePath here, relative to solution folder ================================== #>
<#      string JsonStorePath = @"Platz.SqlForms.Demo\SchemaStore\PlatzDemo.schema.json"; #>
<# // =========================================================================
============================================================================== #>

Save the file and then rename it to ‘PlatzDemoDataContext.tt’ – this is the t4 extension that Visual Studio can recognize. Each time you make changes to this file or save it – the template will be run to generate code, so save it again – this operation should generate ‘PlatzDemoDataContext.cs’ file, that contains generated code for the database schema we designed:

// *********************************************************************************************
// This code is auto generated by Platz.ObjectBuilder template, 
// any changes made to this code will be lost
// *********************************************************************************************
using System;
using System.Collections.Generic;
using System.Text;
using System.Linq;
using Platz.ObjectBuilder;
using Platz.SqlForms;
using PlatzDemo.SchemaStore;

namespace PlatzDemo.SchemaStore
{
    #region Data Context

    public partial class PlatzDemoDataContext : DataContextBase
    {
        protected override void Configure(DataContextSettings settings)
        {
            settings.SetSchema("PlatzDemo");
            settings.SetDriver<SqlJsonStoreDatabaseDriver>();
            settings.MigrationsPath = @"\SchemaStore\PlatzDemo.schema.migrations.json";

            settings.AddTable<Order>();
            settings.AddTable<OrderItem>();
            settings.AddTable<Product>();
        }
    }

    #endregion

    #region Entities

    public partial class Order
    {
        public virtual int Id { get; set; }
        public virtual string ClientName { get; set; }
        public virtual DateTime Created { get; set; }
    }

    public partial class OrderItem
    {
        public virtual int Id { get; set; }
        public virtual int OrderId { get; set; }
        public virtual int ProductId { get; set; }
        public virtual int Qty { get; set; }
    }

    public partial class Product
    {
        public virtual int Id { get; set; }
        public virtual string Name { get; set; }
        public virtual decimal Price { get; set; }
    }

    #endregion
}

To be able to use the generated code, we only need to add connection string to ‘appsettings.json’ file:

{
  "ConnectionStrings": {
    "DefaultConnection": "Server=(localdb)\\mssqllocaldb;Database=PlatzDemo;
     Trusted_Connection=True;MultipleActiveResultSets=true"
  },
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft": "Warning",
      "Microsoft.Hosting.Lifetime": "Information"
    }
  },
  "AllowedHosts": "*"
}

As you can see, I used Microsoft SQL Local DB for this demo.

Create Dynamic Forms

We create a ‘SchemaForms’ project folder and place dynamic forms definitions code there:

using PlatzDemo.SchemaStore;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;

namespace Platz.SqlForms.Demo.SchemaForms
{
    public class ProductForm : StoreDynamicEditFormBase<PlatzDemoDataContext>
    {
        protected override void Define(DynamicFormBuilder builder)
        {
            builder.Entity<Product>(e =>
            {
                e.Property(p => p.Id).IsReadOnly();
                e.Property(p => p.Name).IsRequired();
                e.Property(p => p.Price).IsRequired();
                e.DialogButton(ButtonActionTypes.Cancel).DialogButton
                (ButtonActionTypes.Validate).DialogButton(ButtonActionTypes.Submit);
                e.DialogButtonNavigation("ProductList", 
                ButtonActionTypes.Delete, ButtonActionTypes.Cancel, ButtonActionTypes.Submit);
            });
        }
    }

    public class ProductListForm : StoreDataServiceBase<PlatzDemoDataContext>
    {
        protected override void Define(DataServiceFormBuilder builder)
        {
            builder.Entity<Product>(e =>
            {
                e.ExcludeAll();
                e.Property(p => p.Id).IsPrimaryKey();
                e.Property(p => p.Name);
                e.Property(p => p.Price);
                e.ContextButton("Edit", "ProductEdit/{0}").ContextButton
                               ("Delete", "ProductDelete/{0}");
                e.DialogButton("ProductEdit/0", ButtonActionTypes.Add);
            });

            builder.SetListMethod(GetProductList);
        }

        public List<Product> GetProductList(params object[] parameters)
        {
            var db = GetDbContext();
            var result = db.Get(typeof(Product)).Cast<Product>().ToList();
            return result;
        }
    }
}

Now we add Blazor pages to render product forms and register the product list page in ‘NavMenu.razor’.

ProductList.razor

@page "/ProductList/"
@using Platz.SqlForms.Demo.SchemaForms

<h1>Product Edit</h1>

<FormDataServiceListComponent TForm="ProductListForm" />

ProductEdit.razor

@page "/ProductEdit/{Id:int}"
@using Platz.SqlForms.Demo.SchemaForms

<h1>Product Edit</h1>

<FormDynamicEditComponent TForm="ProductForm" Id="@Id" />

@code {
    [Parameter]
    public int Id { get; set; }
}

ProductDelete.razor

@page "/ProductDelete/{Id:int}"
@using Platz.SqlForms.Demo.SchemaForms

<h1>Product Delete</h1>

<FormDynamicEditComponent TForm="ProductForm" Id="@Id" ForDelete="true"/>

@code {
    [Parameter]
    public int Id { get; set; }
}

Similarly, we add dynamic forms and pages for ‘Order’ and ‘OrderItem’ tables.

We omitted this code to reduce the reading time. You can find the full code on GitHub.

All explanations of how to define dynamic forms and use dynamic razor components can be found in my article, Microsoft Blazor - Rapid Development with SQL Forms Open-source Platz.SqlForms.

Test Application

When we start the application for the first time, it will create SQL database automatically and apply all migration scripts.

You will be able to find details on how to use migrations later, closer to the final release date, in Platz.SqlForms project documentation.

You can now add new products to the database, edit and delete them.

The full project demo also contains functionality for adding and editing Orders and Order Items.

Conclusion

In this article, we demonstrated how to use the embedded schema designer to define a database and use it in dynamic UI generation.

The Schema designer and Query builder add a no-code technique to Platz.SqlForms rapid development and prototyping. We understand that no-code can be painful from a maintenance perspective and this is why we propose a new approach – save your designs of schemas or queries to json and then you can use a number of t4 templates to generate result code.

Future releases may include t4 templates that generate Entity Framework data context, thus developers who started from no-code prototyping will be able to migrate to solid Microsoft ORM.

History

12^th April, 2021: Initial version

Microsoft Blazor Platz.SqlForms Open-source - Using Schema Builder to Design and Maintain SQL Server Database