SQL Server JSON and ASP.NET Web API Scaffolder Generator Source Code

Introduction

This article demonstrates how to create an ASP.Net web API scaffolder based on SQL Server JSON store procedures. Documentation of the output web API project is in my previous article here. This code generator is a simple Windows form application with required text templates for C# and SQL Server JSON codes. You can change template files and create your own scaffolding structure. This generator is not familiar with all databases, and some conditions of generating limits have been effected. You need a normalized database to have a scaffolding with this generator, or you can have your own conditions in generating limits. You can create a generator web service, like what I did in my previous article, and create your user's projects with your own scaffolding template.

Backgrounds

Beginner C#
Beginner ASP.Net MVC Web API
Beginner SQL Server JSON
My Previous Article

Source Code

Download from my Git

Topics

Get entities from the database: select all needed entities for scaffolding from a database and convert to a generate model
Templates: all available text templates for code properties in C# and SQL server JSON store procedures
Save Output Solution: write codes into ASP.Net web API solution
How to use scaffolder application: how to connect with a database and start generating with a simple Windows form application

Topic 1 - Get entities from a database

There are 4 select queries required to select from the database for creating generate models. First, we need to get all tables, properties, relations, and keys. Then, you just need to replace them in text templates. There are 3 parts of the project for this operation.

Part 1 (Entity Queries)

Select database entities with these queries.

--|| Select Tables ||--
select * from INFORMATION_SCHEMA.TABLES where TABLE_TYPE <> N'VIEW
--|| Select Properties ||--
select * from INFORMATION_SCHEMA.COLUMNS
--|| Select Keys ||--
select * from INFORMATION_SCHEMA.KEY_COLUMN_USAGE
--|| Select Releations ||--
SELECT
fk.name as 'FK_NAME',
tp.name as 'TABLE_NAME',
cp.name as 'PARENT_COL_NAME',
cp.column_id as 'PARENT_COL_ID',
tr.name as 'REFRENCED_TABLE_NAME',
cr.name as 'REFRENCED_COL_NAME',
cr.column_id as 'REFRENCED_COL_ID'
FROM
sys.foreign_keys fk
INNER JOIN
sys.tables tp ON fk.parent_object_id = tp.object_id
INNER JOIN
sys.tables tr ON fk.referenced_object_id = tr.object_id
INNER JOIN
sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id
INNER JOIN
sys.columns cp ON fkc.parent_column_id = cp.column_id AND fkc.parent_object_id = cp.object_id
INNER JOIN
sys.columns cr ON fkc.referenced_column_id = cr.column_id AND fkc.referenced_object_id = cr.object_id
ORDER BY
tp.name, cp.column_id

Part 2 (Entity Models)

Convert your entities to these models.

public class SqlRelationNames
{
public string FK_NAME { get; set; }
public string TABLE_NAME { get; set; }
public string PARENT_COL_NAME { get; set; }
public int? PARENT_COL_ID { get; set; }
public string REFRENCED_TABLE_NAME { get; set; }
public string REFRENCED_COL_NAME { get; set; }
public int? REFRENCED_COL_ID { get; set; }
}
public class SqlTableNames
{
public string TABLE_CATALOG { get; set; }
public string TABLE_SCHEMA { get; set; }
public string TABLE_NAME { get; set; }
}
public class SqlKeys
{
public string TABLE_CATALOG { get; set; }
public string TABLE_SCHEMA { get; set; }
public string TABLE_NAME { get; set; }
public string COLUMN_NAME { get; set; }
public int? ORDINAL_POSITION { get; set; }
public string CONSTRAINT_NAME { get; set; }
public string CONSTRAINT_SCHEMA { get; set; }
public string REFRENCED_TABLE { get; set; }
public KeyType KeyType
{
get
{
if (CONSTRAINT_NAME.StartsWith("FK"))
{
return KeyType.FK;
}
else if (CONSTRAINT_NAME.StartsWith("UK"))
{
return KeyType.UK;
}
else if (CONSTRAINT_NAME.StartsWith("PK"))
{
return KeyType.PK;
}
else
{
return KeyType.None;
}
}
}
}
public class SqlNames
{
public string TABLE_CATALOG { get; set; }
public string TABLE_SCHEMA { get; set; }
public string TABLE_NAME { get; set; }
public string COLUMN_NAME { get; set; }
public int? ORDINAL_POSITION { get; set; }
public string IS_NULLABLE { get; set; }
public string DATA_TYPE { get; set; }
public int? CHARACTER_MAXIMUM_LENGTH { get; set; }
public int? CHARACTER_OCTET_LENGTH { get; set; }
public short? DATETIME_PRECISION { get; set; }
public byte? NUMERIC_PRECISION { get; set; }
public int? NUMERIC_SCALE { get; set; }
}
public enum KeyType
{
FK = 0,
UK = 1,
PK = 2,
None = 3
}

Part 3 (Limits)

We have some limits for the creation of parameters by data type, relations, conditions, and other complexities of databases. I tried to do the least in this project

Topic 2 - Templates

All templates contain strings flagged by ## for replacing entity names from selected models

C# templates

Create your C# code templates, like what I did in the sharpTemplates folder. For example, as shown below to add entity model.

namespace #PROJECTNAME#.Models.MetaData
{
using Bases;
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data.Entity.Spatial;
public class #NAME# : BaseSqlAddParam
{
#PROPERTY#
public override string GetSpParams()
{
var result = "";
#PROPERTYCONVERT#
return result;
}
}
}

SQL Server JSON Templates

Create your SQL Server JSON templates, like what I did in the sqlTemplates folder. Follow below to add an entity model.

CREATE PROCEDURE [SpJson].[#PROCNAME#]
#SPPARAMS#
AS
BEGIN TRY
INSERT INTO [#SCHEMA#].[#TABLE#]
(#SPNAMES#)
VALUES
(#SPVALUE#)
;
With DataForJson
As
(
(
Select Top(1)
#SELECTNAMES#
#RELATIONSJSON#
#MANYRELATIONSJSON#
From [#SCHEMA#].[#TABLE#] [#CONST#]
Where
[#CONST#].[#PKNAME#] = SCOPE_IDENTITY()
)
)
Select * From DataForJson FOR JSON AUTO,INCLUDE_NULL_VALUES,WITHOUT_ARRAY_WRAPPER
END TRY
BEGIN CATCH
Declare @ErrorJson nvarchar(MAX) = '{"Message":"' + ERROR_MESSAGE() + '","Status":false}'
Select @ErrorJson
END CATCH

Topic 3 - Save Output Solution

There is a general model for my entities that contains all replaced templates and is ready to use for saving code in the solution. Just run a loop to copy the template project in the tempProject folder to what's requested for a new solution. Save the folder, then replace the name spaces and file names. I have replaced the text place in "ApiProject.csproj" file to include new files for the new solution.

Topic 4 – How to use SQL Server JSON Scaffolder Application

Never move or change any file in this folders in the root folder of application if you have not any changes in your generator models,

"sharpTemplates" C# template text files are in this folder
"sqlTemplates" SQL Server JSON template text files are in this folder
"tempProject" empty output solution template text files are in this folder
"sqlCommands" SQL Entity queries text files are in this folder

Run REST Scaffolder.exe.

Set Database Connection String in text boxes.

Click on the Start Build Project button and select the path of the new solution.

Wait to create your ASP.Net web API solution and SQL Server JSON store procedures.

Summary

In this article, we learned how to create an ASP.Net web API Scaffolder based on SQL Server JSON store procedures, and have your own scaffolding template so you can begin to create your REST projects.

3 Comments

Join the conversation! Your thoughts help the community grow.

Venkatarama Uppugunduri6y ago
Hi Ahmad, this is great article and tool. Thanks for that. But, how to authenticate in real scenario. I am getting 401 as it is throwing UnAuthorized handler. Could you please explain what modifications I should make.
- Ahmad Farazmand6y ago
  Hi. you have to use Bearer Token to access the API controllers. create a token from AuthController in project then call all API actions with Bearer Token in your response header.
- Ahmad Farazmand6y ago
  Or you can remove [JwtAuth] attribute from top of BaseContoller class