Database setup | Create a Blazor CRM application with Radzen (Blazor)

Radzen applications typically start from the data. In this case we will use a MS SQL database that we will create from scratch.

Create the database

First you have to create the tables that will store the CRM data: Contacts, Opportunities and tasks.

Fire up SQL Management Studio (or your other favorite DB management tool) create a database called RadzenCRM and add the following tables and foreign key relationships.

You can either create those visually or run this SQL script.

Create a Radzen application

Now that we have a database for the CRM application it is time to start working in Radzen.

1. Start Radzen and create a new application.
2. Set the Framework to Blazor, set Name to RadzenCRM, select the Dark theme, pick a directory where the application files will reside.

3. Click the Create button. Radzen will create the application and load it in edit mode.

4. Click the new data source link that appears in the middle of Radzen. Then add a new MS SQL Server data source. Set Name to CRM and Database to RadzenCRM. Then enter the rest of the connection info - server name, user and password (if using SQL server authentication).
5. Click the Infer schema button. Radzen will connect to the RadzenCRM database and infer its schema - tables, views and stored procedures (if there are any).
6. Check Generate pages for CRUD operations. Then click Finish. This will make Radzen scaffold all checked tables as CRUD pages. This feature is only available in Radzen Professional and Enterprise..

You now have a complete CRUD application which is ready for test. Click the run button to see what you have created in your browser.

The application doesn’t show much at the moment because the database is still empty. Let’s add some data.

1. Click Opportunity Statuses from the navigation menu. This loads the Opportunity Statuses page that displays all records from the OpportunityStatuses table (which is still empty by the way).
2. Click the Add button above the DataGrid to add some records to the OpportunityStatuses table. In the Add Opportunity Status dialog enter Name then click Save.
3. Add the following statuses: Inactive, Won, Lost, Active. Those represent the state a CRM opportunity could be in.

Then go to the Task Statuses page and add the following statuses: Not Started, In Progress, Complete. Those represent the state a CRM task could be in.

Finally go to the Task Types page and add the following task types: Online Meeting, Email, Call.

Enable security

The typical web application has users that can log in and do certain things based on their role. Our RadzenCRM application is no exception!

Let’s enable security and see how this affects the RadzenCRM database.

1. Go back to Radzen and click the stop button. This stops the running application.
2. Click the security button in the top right corner of Radzen. It displays the application security configuration page.
3. From the Provider dropdown pick Default. This is the default security provider which uses ASP.NET Core Identity. Then select CRM from the Data Source dropdown. This tells Radzen what database to use to persist the users and roles. Check Auto generate pages for user and role management.
4. Click save and run the application again.

The application will start with the Login page. You can login with the test credentials admin/admin (available only during development).

When this happens the application will add a few more tables to the RadzenCRM database (all starting with Asp).

Create a foreign key

The final step is to create a foreign key relationship between the Opportunities table and the AspNetUsers table. We need this to associate users with opportunities in our CRM application.

This step is done in SQL Management Studio and your favorite code editor (Visual Studio Code, Visual Studio Professional or anything else really).

1. Open SQL Management Studio or your favourite database management tool capable of running a SQL script.

2. Add a foreign key between the Opportunities and AspNetUsers table (Opportunities.UserID -> AspNetUsers.Id). You can run this SQL script:

   USE [RadzenCRM]
   GO
   
   ALTER TABLE [dbo].[Opportunities] WITH CHECK
   ADD CONSTRAINT [FK_Opportunities_AspNetUsers] FOREIGN KEY([UserId])
   REFERENCES [dbo].[AspNetUsers] ([Id])
   GO
   
   ALTER TABLE [dbo].[Opportunities] CHECK CONSTRAINT [FK_Opportunities_AspNetUsers]
   GO
   

3. Open RadzenCrm.sln with Vusual Studio (or the server directory with Visual Studio Code).

4. Add a new file server\Models\CRM\Opportunity.Custom.cs with the following content.

   using System;
   using System.Collections.Generic;
   using System.ComponentModel.DataAnnotations;
   using System.ComponentModel.DataAnnotations.Schema;
   
   namespace RadzenCrm.Models.Crm
   {
       public partial class Opportunity
       {
           [ForeignKey("UserId")]
           public ApplicationUser User { get; set; }
       }
   }
   

This extends the Opportunity model (a class which represents a record from the Opportunities table) with a new property User - the CRM application user it is related to.

Here is how the RadzenCRM database should look like after the last modification.

You are now ready to proceed to Customize security.