Dapper - Code Generator

Our approach to using Dapper in this project will be to use SQL stored procedures to provide the CRUD operations (Create, Read, Update and Delete).  As well as the SQL stored procedures (which will 'live' in the SQL database) we will also require model classes, service classes and interface classes with the C# code.  We will need stored procedures and C# classes for every table - this would be a bore to write, but fortunately Alan Simpson has written a C# program that does most of the hard work for us automatically.

This YouTube video playlist explains Alan's Blazor-Dapper-Code-Generator.

Blazor-Dapper-Code-Generator

To get the code generator open browser and go to https://github.com/AlanSimpsonMe/ Select:

  • Repositories
  • Blazor-Dapper-Code-Generator
  • Code
  • Download zip file and save somewhere convenient (Desktop would be OK)
  • Close the web browser
  • Go to the downloaded file and Extract All.
  • Go to the extracted files and find 'Blazor-Dapper-Code-Generator-master.sln' and open with Visual Studio
  • Run the application - it will need to be run separately for each table
  • Enter the 'Namespace' - this is name of the project (BlazorPurchaseOrders)
  • We need the basic 'Create Table' SQL for the table. The easiest way to get this is
    • Open SQL Management Studio
    • Select the Purchase Orders database
    • Select Tables
    • Select the required table
    • Right-click and select 'Script Table as..' > Create to.. > New Query Editor Window
    • Copy from 'Create Table..' to the last column defined. i.e. do not copy the line that starts 'Constraint..'
  • Paste the 'Create Table' SQL (example shown below) into the large textbox and click the 'Go' button.
CREATE TABLE [dbo].[POHeader](
	[POHeaderID] [int] IDENTITY(1,1) NOT NULL,
	[POHeaderOrderNumber] [int] NOT NULL,
	[POHeaderOrderDate] [date] NOT NULL,
	[POHeaderSupplierID] [int] NOT NULL,
	[POHeaderSupplierAddress1] [nvarchar](50) NULL,
	[POHeaderSupplierAddress2] [nvarchar](50) NULL,
	[POHeaderSupplierAddress3] [nvarchar](50) NULL,
	[POHeaderSupplierPostCode] [nvarchar](10) NULL,
	[POHeaderSupplierEmail] [nvarchar](256) NULL,
	[POHeaderRequestedBy] [nvarchar](450) NULL,
	[POHeaderIsArchived] [bit] NOT NULL,

The code generator will produce a lot of code, together with instructions. Copy and paste all the generated code to Notepad++. (It just makes things a little simpler.)

Close the Code Generator to clear the generated code. Re-open the Code Generator and repeat with the Create Table SQL for each table, again copying and pasting all code to a separate file in Notepad++.

Generated Code

The generated code will enable us to add the following

  • SQL stored procedures for the CRUD operations
  • Model class for each table
  • Service class for each table/CRUD operation
  • Interface class for each service class

In Notepad ++, for each table, we need to do the following.

At the top of the file are instructions for 'Things to do in Visual Studio'.  As we have already carried this out, delete all rows down to Stored Procedures section.

Stored Procedures

Open SQL Management Studio and select the PurchaseOrders database by highlighting it.

Copy from 'Stored Proc for Insert...' down to, and including, 'END' statement just before 'Back in Visual Studio', and paste into a New Query window in SQL Management Studio.

We are going to make a few small changes before running the script.

  • The script to create _List only returns the first six columns, change this to include all columns (the _GetOne script lists them all, so copy and paste from there).
  • The script to create _List also only returns the 'Top 30' records - delete 'Top 30' so that all records are returned.
  • We don't need the _Search, _DateRange or _Delete stored procedures, so delete these sections.

Having made these changes, Execute the script.  To confirm that the stored procedures have been created, select 'Programmability > Stored Procedures.

There is no need to save the SQL query.

Model

If it is running, close the Code Generator in Visual Studio and open BlazorPurchaseOrders project.

In the Data folder add a class with the name of 'TableName'.cs where TableName is the name of the table. e.g. POHeader.cs

Copy the text from Notepad++ for the model class and paste into the newly created class in Visual Studio, replacing the code created when adding the class.

Service

In the Data folder add a class with the name of 'TableName'Service.cs where TableName is the name of the table. e.g. POHeaderService.cs

Copy the text from Notepad++ for the service into the new class,replacing the code created when adding the class.

We don't need the methods for TableNameSearch, TableNameDateRange and TableNameDelete, so they can be deleted.

Interface

In the Data folder add a class with the name of I'TableName'Service.cs where TableName is the name of the table. e.g. IPOHeaderService.cs

Copy the text from Notepad++ for the service into the new class, replacing the code created when adding the class.

We don't need the lines for TableNameSearch, TableNameDateRange and TableNameDelete, so they can be deleted.

Startup.cs

In Startup.cs add the following to public void ConfigureService {} block, substituting the correct table name for 'TableName'. 

services.AddScoped<ITableNameService, TableNameService>();

Repeat the whole process for each table...

A bit tedious, but better than the alternative!

YouTube Video

Blazor Project - Purchase Orders - Part 4

Project Code

The code for the stored procedures and C# up to this point in the project can be found here.

Powered by Publii