Databases

Radzen Blazor Studio makes connecting to your data a breeze. You can connect to your MSSQL, MySQL, PostreSQL, Oracle or SQLite relational database ans scaffold a complete CRUD application in a few steps.

Follow this tutorial to find out how.

How-to video

Create a database data source.

First open an existing application or create a new one.

Click the button.
Select a database type.
Enter the database connection settings - user name, password etc.
Pick the database entities you wish to scaffold models and services for - tables, views, stored procedures.
Optionally generate CRUD pages from a set of templates (this is a premium feature). You can pick the entities you want pages created for.
Run your application.

Update database data source

Your database usually changes over time as the application evolves. To reflect the changes of your database to your Radzen Blazor application you need to update the data source.

Click the button.
Pick your existing database data source from the available options.
Check the new database entities (tables, views, stored procedures) that you wish to include in your app. Do not uncheck the existing entities otherwise they will be removed from the Entity Framework database context.
Optionally generate CRUD pages. You can scaffold CRUD pages only for the updated entities - to do so check them in the entity tree.

Database scaffold settings

During scaffolding you can set a few options that affect the naming of generated classes and properties.

Use table names directly from the database

When this option is enabled Radzen Blazor Studio generates class and property names that follow the original database table and column names as closely as possible e.g. the table ORDERS maps to the class ORDER or ORDERS (depending on pluralization settings). Some name changes are still made to comply with C# identifier naming rules.

When disabled (the default) Radzen Blazor Studio will convert table and column names to C# naming conventions e.g. the table ORDERS maps to the class Order or Orders (depending on pluralization settings).

Pluralize or singularize generated object names

When this options is enabled (the default) Radzen Blazor Studio will use singular form for generated classes e.g. the table Orders maps to the class Order.

When this option is disabled Radzen Blazor Studio will not change the object names e.g. the table Orders maps to the class Orders.

Generated code

After you add a database data source to your Blazor application Radzen Blazor Studio will generate the following code fore you:

Model classes for the selected database entities (tables, views). They are located in the Models\<DataSourceName> directory (Blazor server apps) or Server\Models\<DataSourceName> (Blazor WASM or Auto apps).
Entity Framework data context for performing CRUD operations. Located in the Data or Server\Data directory and named <DataSourceName>Context.cs.
OData controllers for accessing and updating database entities (in Blazor WASM or Auto apps). Located in Server\Controllers\<DataSourceName>\<TableName>Controller.cs e.g. Server\Controllers\Northwind\CustomersController.cs.
Server data service (Blazor server apps). Located in Services\<DataSourceName>Service.cs e.g. Services\NorthwindService.cs. Used in the generated pages to access the data source.
Client data service (Blazor WASM or Auto apps). Located in Client\Services\<DataSourceName>Service.cs e.g. Client\Services\NorthwindService.cs. Used in the generated pages to access the data source.

Note: The server and client data services are similar in naming and in the methods they provide. The main difference is the implementation. The server data service is generated in Blazor server applications and uses Entity Framework to access the database. The client data service is generated in WASM and Auto applications and relies on the OData controllers to access the database.

OData Controller

The OData controller exposes an entity from your database as a REST endpoint that follows the OData specification. They are used in WASM or Auto applications to bring the data from the server to the client. Radzen Blazor Studio generates a single controller per entity (e.g. table). For example for the Customers table of the Northwind database it would generate the CustomersController class:

[Route("odata/Northwind/Customers")] // URL of the OData endpoint e.g. https://localhost:5001/odata/Northwind/Customers
public partial class CustomersController : ODataController
{
    // Returns items from the Customers table. Accepts OData query parameters e.g. https://localhost:5001/odata/Northwind/Customers?$skip=10&$top=10
    public IEnumerable<Models.Northwind.Customer> GetCustomers();
    // Invoked by GetCustomers and has no default implementation. Use it to modify the query before returning the result (for example to apply additional filtering).
    partial void OnCustomersRead(ref IQueryable<Models.Northwind.Customer> items);

    // Returns a single item from the Customers table. Requires the key e.g. https://localhost:5001/odata/Northwind/Customers(CustomerID=ALFKI)
    public SingleResult<Models.Northwind.Customer> GetCustomer(string key);
    // Invoked by GetCustomer and has no default implementation. Use it to modify the item (for example to set custom properties).
    partial void OnCustomerGet(ref SingleResult<Models.Northwind.Customer> item);

    // Deletes the specified item from the database. Requires the HTTP DELETE method e.g. DELETE https://localhost:5001/odata/Northwind/Customers(CustomerID=ALFKI)
    public IActionResult DeleteCustomer(string key);
    // Invoked by DeleteCustomer before updating the database.
    partial void OnCustomerDeleted(Models.Northwind.Customer item);
    // Invoked by DeleteCustomer after updating the database.
    partial void OnAfterCustomerDeleted(Models.Northwind.Customer item);


    // Updates an existing item from the Customers table by replacing it. Requires the HTTP PUT method.
    public IActionResult PutCustomer(string key, [FromBody]Models.Northwind.Customer item);
    // Updates properties of an existing item from the Customers table. Requires the HTTP PATCH method.
    public IActionResult PatchCustomer(string key, [FromBody]Delta<Models.Northwind.Customer> patch);
    // Invoked by PutCustomer and PatchCustomer before updating the database.
    partial void OnCustomerUpdated(Models.Northwind.Customer item);
    // Invoked by PutCustomer and PatchCustomer after updating the database.
    partial void OnAfterCustomerUpdated(Models.Northwind.Customer item);

    // Inserts a new item in the Customers table.
    public IActionResult Post([FromBody] Models.Northwind.Customer item);
    // Invoked by the Post method before updating the database.
    partial void OnCustomerCreated(Models.Northwind.Customer item);
    // Invoked by the Post method after updating the database.
    partial void OnAfterCustomerCreated(Models.Northwind.Customer item);
}

Server Data Service

The server data service class is generated in Blazor Server applications and provides CRUD methods for the entire data source. It operates with the generated Entity Framework context to perform CRUD operations over your database.

For example for the Northwind database it would contain the following methods for the Customers table.

public partial class NorthwindService
{
    // Returns items from the Customers table.
    public async Task<IQueryable<Models.Northwind.Customer>> GetCustomers(Query query = null)
    // Invoked by GetCustomers and has no default implementation. Use to modify the returned result (for example to apply additional filtering).
    partial void OnCustomersRead(ref IQueryable<Models.Northwind.Customer> items);

    // Return a single item from the Customers table by its primary key (CustomerId)
    public async Task<EFServer.Models.Northwind.Customer> GetCustomerByCustomerId(string customerid);
    // Invoked by GetCustomerByCustomerId.
    partial void OnCustomerGet(Models.Northwind.Customer item);
    // Invoked by GetCustomerByCustomerId.
    partial void OnGetCustomerByCustomerId(ref IQueryable<Models.Northwind.Customer> items);

    // Inserts a new item in the Customers table.
    public async Task<Models.Northwind.Customer> CreateCustomer(Models.Northwind.Customer customer)
    // Invoked by CreateCustomer before updating the database.
    partial void OnCustomerCreated(Models.Northwind.Customer item);
    // Invoked by CreateCustomer after updating the database.
    partial void OnAfterCustomerCreated(Models.Northwind.Customer item);

    // Cancels all changes made to an item from the Customers table by reverting it to its original state from the database.
    public async Task<Models.Northwind.Customer> CancelCustomerChanges(Models.Northwind.Customer item);

    // Updates an existing item from the Customers table.
    public async Task<Models.Northwind.Customer> UpdateCustomer(string customerid, Models.Northwind.Customer customer)
    // Invoked by UpdateCustomer before updating the database.
    partial void OnCustomerUpdated(Models.Northwind.Customer item);
    // Invoked by UpdateCustomer after updating the database.
    partial void OnAfterCustomerUpdated(Models.Northwind.Customer item);

    // Deletes an existing item from the Customers table.
    public async Task<Models.Northwind.Customer> DeleteCustomer(string customerId)
    // Invoked by DeleteCustomer before updating the database.
    partial void OnCustomerDeleted(Models.Northwind.Customer item);
    // Invoked by DeleteCustomer after updating the database.
    partial void OnAfterCustomerDeleted(Models.Northwind.Customer item);


    /* Methods for the other tables from the data source */
}

An instance of the client data service is injected in all scaffolded pages for that data source.

[Inject]
public NorthwindService NorthwindService { get; set; }

You can use the injected service to access your data source.

protected IEnumerable<Models.Northwind.Customer> customers;

protected override async Task OnInitializedAsync()
{
    customers = await NorthwindService.GetCustomers();
}

Client Data Service

The client data serice class is generated in Blazor WASM and Auto applications. It provides access to your database by making HTTP requests to the generated OData controllers. It doesn’t have direct access to the generated Entity Framework context as opposed to a server data service.

For example for the Northwind database it would contain the following methods for the Customers table.

public partial class NorthwindService
{
    // Returns items from the Customers OData endpoint.
    public async Task<Radzen.ODataServiceResult<Models.Northwind.Customer>> GetCustomers(Query query);
    // Returns items from the Customers OData endpoint.
    public async Task<Radzen.ODataServiceResult<Models.Northwind.Customer>> GetCustomers(string filter, string orderby, string expand, int? top, int? skip, bool? count, string format, string select);
    // Invoked by GetCustomers.
    partial void OnGetCustomers(HttpRequestMessage requestMessage);

    // Inserts a new item via the Customers OData endpoint.
    public async Task<Models.Northwind.Customer> CreateCustomer(Models.Northwind.Customer customer);
    // Invoked by CreateCustomer.
    partial void OnCreateCustomer(HttpRequestMessage requestMessage);


    // Deletes an item via the Customers OData endpoint.
    public async Task<HttpResponseMessage> DeleteCustomer(string customerId);
    // Invoked by DeleteCustomer.
    partial void OnDeleteCustomer(HttpRequestMessage requestMessage);

    // Returns an item via the Customers OData endpoint.
    public async Task<Models.Northwind.Customer> GetCustomerByCustomerId(string expand = null, string customerId = null);
    // Invoked by GetCustomerByCustomerId.
    partial void OnGetCustomerByCustomerId(HttpRequestMessage requestMessage);

    // Updates an item via the Customers OData endpoint.
    public async Task<HttpResponseMessage> UpdateCustomer(string customerId = null, Models.Northwind.Customer customer)
    // Invoked by UpdateCustomer.
    partial void OnUpdateCustomer(HttpRequestMessage requestMessage);


    /* Methods for the other entities from the data source */
}

An instance of the server data service is injected in all scaffolded pages for that data source.

[Inject]
public NorthwindService NorthwindService { get; set; }

You can use the injected service to access your data source.

protected IEnumerable<Models.Northwind.Customer> customers;

protected override async Task OnInitializedAsync()
{
    var result = await NorthwindService.GetCustomers();
    customers = result.Value.AsODataEnumerable();
}

Customizing the generated code

Radzen Blazor Studio generates data source related files after infer. If you update your data source those files will be generated again and any changes made to them will be overwritten.

To avoid information loss we recommend following these patterns for customizing or extending the generated code.

Partial classes

All data source related classes generated by Radzen Blazor Studio are partial - models, EF data contexts, controllers and services. This allows you to extend them in a new file: add new properties and methods or implement partial methods that are invoked by the generated code.

Models

A common task is to extend a model with a new property which doesn’t map to a database column (a.k.a. computed or calculated property). Consider a table named Customer that has two columns FirstName and LastName. A business requirement emerges that needs a new property of the model - FullName. We add it by extending the generated Customer class.

Create a new file e.g. Customer.Custom.cs in the same directory as the generated Customer.cs.
Copy the namespace from Customer.cs and paste it in Customer.Custom.cs. This is very important - partial classes must reside in the same namespace.

Add a partial class within the namespace and implement the FullName property.

namespace <Namespace>
{
   public partial class Customer
   {
       [NotMapped]  // Tell Entity Framework to not try mapping this property to a database column
       public string FullName
       {
           get
           {
               return $"{FirstName} {LastName}"; // You can use any generated properties in the custom partial class
           }
       }
   }
}

Entity Framework Context

Partial classes allow you to change or extend the generated Entity Framework data context. It exposes a partial method called OnModelBuilding which has no implementation by default and is invoked at the end of OnModelCreating which configures the mapping of entities to classes. You can extend the default mapping by providing your own implementation of OnModelBuilding in a new file.

Create a new file in the same directory as the generated data context.
Copy the namespace in the new file. Use

Add a partial class within the namespace with the same name as the generated context.

namespace <Namespace>
{
   public partial class <DataSource>Context
   {
       partial void OnModelBuilding(ModelBuilder builder)
       {
           // Use the provided ModelBuilder to create additional mappings or modify the existing ones
       }
   }
}

OData controllers and data services

OData controllers and data services also rely on partial methods to extend the default CRUD implementation generated by Radzen Blazor Studio. A typical customization is to filter entities based on the current user.

By Initiative