Post
Ask Question

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
  1. Get entities from the database: select all needed entities for scaffolding from a database and convert to a generate model
  2. Templates: all available text templates for code properties in C# and SQL server JSON store procedures
  3. Save Output Solution: write codes into ASP.Net web API solution
  4. 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.
  1. --|| Select Tables ||--    
  2. select * from INFORMATION_SCHEMA.TABLES where TABLE_TYPE <> N'VIEW  
  3.   
  4. --|| Select Properties ||--  
  5. select * from INFORMATION_SCHEMA.COLUMNS  
  6.   
  7. --|| Select Keys ||--  
  8. select * from INFORMATION_SCHEMA.KEY_COLUMN_USAGE  
  9.   
  10. --|| Select Releations  ||--  
  11. SELECT  
  12. fk.name as 'FK_NAME',  
  13. tp.name as 'TABLE_NAME',  
  14. cp.name as 'PARENT_COL_NAME',  
  15. cp.column_id as 'PARENT_COL_ID',  
  16. tr.name as 'REFRENCED_TABLE_NAME',  
  17. cr.name as 'REFRENCED_COL_NAME',  
  18. cr.column_id as 'REFRENCED_COL_ID'  
  19. FROM  
  20. sys.foreign_keys fk  
  21. INNER JOIN  
  22. sys.tables tp ON fk.parent_object_id = tp.object_id  
  23. INNER JOIN  
  24. sys.tables tr ON fk.referenced_object_id = tr.object_id  
  25. INNER JOIN  
  26. sys.foreign_key_columns fkc ON fkc.constraint_object_id = fk.object_id  
  27. INNER JOIN  
  28. sys.columns cp ON fkc.parent_column_id = cp.column_id AND fkc.parent_object_id = cp.object_id  
  29. INNER JOIN  
  30. sys.columns cr ON fkc.referenced_column_id = cr.column_id AND fkc.referenced_object_id = cr.object_id  
  31. ORDER BY  
  32. tp.name, cp.column_id  
Part 2 (Entity Models)
 
Convert your entities to these models.
  1. public class SqlRelationNames  
  2. {  
  3. public string FK_NAME { get; set; }  
  4. public string TABLE_NAME { get; set; }  
  5. public string PARENT_COL_NAME { get; set; }  
  6. public int? PARENT_COL_ID { get; set; }  
  7. public string REFRENCED_TABLE_NAME { get; set; }  
  8. public string REFRENCED_COL_NAME { get; set; }  
  9. public int? REFRENCED_COL_ID { get; set; }  
  10. }  
  11.   
  12. public class SqlTableNames  
  13. {  
  14. public string TABLE_CATALOG { get; set; }  
  15. public string TABLE_SCHEMA { get; set; }  
  16. public string TABLE_NAME { get; set; }  
  17. }  
  18.   
  19. public class SqlKeys  
  20. {  
  21. public string TABLE_CATALOG { get; set; }  
  22. public string TABLE_SCHEMA { get; set; }  
  23. public string TABLE_NAME { get; set; }  
  24. public string COLUMN_NAME { get; set; }  
  25. public int? ORDINAL_POSITION { get; set; }  
  26. public string CONSTRAINT_NAME { get; set; }  
  27. public string CONSTRAINT_SCHEMA { get; set; }  
  28. public string REFRENCED_TABLE { get; set; }  
  29. public KeyType KeyType  
  30. {  
  31. get  
  32. {  
  33.    if (CONSTRAINT_NAME.StartsWith("FK"))  
  34.    {  
  35.       return KeyType.FK;  
  36.    }  
  37.    else if (CONSTRAINT_NAME.StartsWith("UK"))  
  38.    {  
  39.       return KeyType.UK;  
  40.    }  
  41.    else if (CONSTRAINT_NAME.StartsWith("PK"))  
  42.    {  
  43.       return KeyType.PK;  
  44.    }  
  45.    else  
  46.    {  
  47.       return KeyType.None;  
  48.    }  
  49. }  
  50. }  
  51. }  
  52.   
  53. public class SqlNames  
  54. {  
  55. public string TABLE_CATALOG { get; set; }  
  56. public string TABLE_SCHEMA { get; set; }  
  57. public string TABLE_NAME { get; set; }  
  58. public string COLUMN_NAME { get; set; }  
  59. public int? ORDINAL_POSITION { get; set; }  
  60. public string IS_NULLABLE { get; set; }  
  61. public string DATA_TYPE { get; set; }  
  62. public int? CHARACTER_MAXIMUM_LENGTH { get; set; }  
  63. public int? CHARACTER_OCTET_LENGTH { get; set; }  
  64. public short? DATETIME_PRECISION { get; set; }  
  65. public byte? NUMERIC_PRECISION { get; set; }  
  66. public int? NUMERIC_SCALE { get; set; }  
  67. }  
  68.   
  69. public enum KeyType  
  70. {  
  71. FK = 0,  
  72. UK = 1,  
  73. PK = 2,  
  74. None = 3  
  75. }   
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.
  1. namespace #PROJECTNAME#.Models.MetaData  
  2. {  
  3.     using Bases;  
  4.     using System;  
  5.     using System.Collections.Generic;  
  6.     using System.ComponentModel.DataAnnotations;  
  7.     using System.ComponentModel.DataAnnotations.Schema;  
  8.     using System.Data.Entity.Spatial;  
  9.   
  10.     public class #NAME# : BaseSqlAddParam  
  11.     {  
  12.         #PROPERTY#  
  13.   
  14.         public override string GetSpParams()  
  15.         {  
  16.             var result = "";  
  17.  
  18.             #PROPERTYCONVERT#  
  19.   
  20.             return result;  
  21.         }  
  22.     }  
  23. }   
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.
  1. CREATE PROCEDURE [SpJson].[#PROCNAME#]  
  2.     #SPPARAMS#  
  3. AS  
  4.   
  5. BEGIN TRY    
  6.      INSERT INTO [#SCHEMA#].[#TABLE#]  
  7.     (#SPNAMES#)   
  8.     VALUES   
  9.     (#SPVALUE#)  
  10. ;  
  11.     With DataForJson  
  12.     As  
  13.     (  
  14.     (  
  15.       
  16.     Select Top(1)  
  17.     #SELECTNAMES#  
  18.     #RELATIONSJSON#  
  19.     #MANYRELATIONSJSON#  
  20.     From [#SCHEMA#].[#TABLE#] [#CONST#]  
  21.     Where   
  22.     [#CONST#].[#PKNAME#] = SCOPE_IDENTITY()  
  23.     )  
  24.     )  
  25.       
  26.     Select * From DataForJson FOR JSON AUTO,INCLUDE_NULL_VALUES,WITHOUT_ARRAY_WRAPPER  
  27.            
  28. END TRY    
  29. BEGIN CATCH  
  30.   
  31. Declare @ErrorJson nvarchar(MAX) = '{"Message":"' + ERROR_MESSAGE() + '","Status":false}'  
  32. Select @ErrorJson  
  33. 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.


Similar Articles