SQL Server REST Scaffolding Simple High Performance SQL JSON Usage

Introduction

 
This article demonstrates how to create ASP.NET web API REST with the usage of SQL JSON and repository pattern, along with a full project example auto-generated from my SQL Server REST Scaffolder demo project. You can learn these topic and create a high-performance SQL JSON REST project, or use my code generator to find out how my code works in your own project.
 

Part 1 Subjects

 
How to create a Web API and Use SQL JSON for a Repository Pattern
 
Sample Project (auto-generated and scaffolded by SSRS.exe).
 
Solution = VS-2015 Web API .Net 4.5
Database = SQL Server 2016+
Subject = Manage products and temp card factors by API.
 
Download From My Git.
 
Sample Project Usage
  1. Open solution
  2. Rebuild solution to restoring NUGET packages
  3. Execute App_Data\DataBase.sql in SQL Server 2016+
  4. Set connection string in web.config
  5. Press F5

Part 2 Subject

 
Donate my Web API SQL Server REST scaffolding demo project
 
Description
 
I always use store procedures with JSON return for my Web API projects and use a framework for designing tables and store procedures in my database to integrate them by repository pattern in C# for simple usage in controllers.
 
I shared my code generator demo version of this article to find out how my codes in this article work in your own project. Create a REST for your database in a short time by designing the database you want according to the intended framework in the SSRS user interface. 10 SQL tables are available for free code generation every 10 days.
 
In this article, I have examples and explanations for the following topics:
  1. SQL JSON: How to create JSON store procedures and add one-one relations as a JSON object or add one-many relations as JSON array
  2. Repository Pattern: Create a repository for use all store procedures by base inherited API Controller
  3. Controllers: How to get store procedures JSON string results and usage in controllers
  4. Models: Create models for use in controller action result parameters and convert for usage in-store procedures parameters and integrate with repository
  5. Authorization: Create custom JWT Authorization for login
  6. Donate: Creating your Web API project with this article teaches over 10K T-Sql Lines Of Codes and 1K C# Lines Of Code without writing any code in a minute (donate for you)

Topic 1 - SQL JSON

 
First, let's see what type of JSON data is needed for integrated results between your database relations structures and output JSON needed in your front projects then let start create all available data templates with examples.
 
JSON types
 
Objects:
  1. Product = {"Id": 1, "Title""Product 1"}  
Objects with one-one relation or drill downs:
  1. Product = {  
  2.     "Id": 1,  
  3.     "Title""Product 1",  
  4.     "Category": {  
  5.         "Id": 2,  
  6.         "Title""Sub Category 1",  
  7.         "ParentCategory": {  
  8.             "Id": 1,  
  9.             "Title""Parent Category 1"  
  10.         }  
  11.     }  
  12. }  
Objects with one-many relations array or drill downs:
  1. TempCard = {  
  2.     "Id": 1,  
  3.     "UserInfoId": 1,  
  4.     "FactorId": 1,  
  5.     " TempCardItems": [{  
  6.         "Id": 1,  
  7.         "Count": 13,  
  8.         "Product": {  
  9.             "Id": 1,  
  10.             "Title""Product 1",  
  11.             "Category": {  
  12.                 "Id": 2,  
  13.                 "Title""Sub Category 1",  
  14.                 "ParentCategory": {  
  15.                     "Id": 1,  
  16.                     "Title""Parent Category 1"  
  17.                 }  
  18.             }  
  19.         }  
  20.     }]  
  21. }  
Arrayswith one-one or one-many relations array and drill downs,
  1. [{  
  2.     "Id": 1,  
  3.     "Title""Product 1",  
  4.     "Category": {  
  5.         "Id": 2,  
  6.         "Title""Sub Category 1",  
  7.         "ParentCategory": {  
  8.             "Id": 1,  
  9.             "Title""Parent Category 1"  
  10.         }  
  11.     }  
  12. }, {  
  13.     "Id": 2,  
  14.     "Title""Product 2",  
  15.     "Category": {  
  16.         "Id": 2,  
  17.         "Title""Sub Category 1",  
  18.         "ParentCategory": {  
  19.             "Id": 1,  
  20.             "Title""Parent Category 1"  
  21.         }  
  22.     }  
  23. }]  
Now lets start writing SQL store procedures for get this JSON results. I categorized store procedures by the following names and the returns then write them for all tables in the database:
  1. ToList: relational values top count select order by desc JSON array (multiple filter and conditions)
  2. PageList: relational values offset fetch order by desc JSON array (multiple filter and conditions)
  3. AutoComplete: no relation values top count list order by desc JSON array used for auto complete form searches (multiple filter and conditions)
  4. FirstOrDefault: relational top one select order by desc JSON object (multiple filter and conditions)
  5. FirstOrDefaultById: relational top one select order by desc JSON object just id condition
  6. Count: Select Count(*) (multiple filter and conditions)
  7. AddNewData : Insert and return inserted data as JSON object
  8. UpdateData : update and return relational select for updated row as JSON object
  9. Delete : update IsDeleted property set true and return deleted row as JSON object
  10. Restore: update IsDeleted property set false and return restored row as JSON array
Selects in SQL for JSON results are the same, but selects are as 'WITH' command then select it as 'FOR JSON AUTO'command. The simple explanation is that you need use the following commands and structure to return your select as JSON.
 

Commands

 
FOR JSON AUTO: is required after selecting from for a store procedure result.
 
INCLUDE_NULL_VALUES: use this command if you want have null values in your output JSON.
 
WITHOUT_ARRAY_WRAPPER: required if you want to have an object result and your select should be Top (1)
 
JSON_QUERY(): required for drill-downs select is in this function
 
For Json PATH: required for drill-downs.
 
Select Top (1) sp structure,
  1. CREATE PROCEDURE [Schema].[SP_Name]   
  2.   @Parameter DATATYPE = optional   
  3. AS   
  4.   WITH dataforjson AS ( (   
  5.   --|| your top(1) selects goes here ||--   
  6.   ) )   
  7.   SELECT *   
  8.   FROM   dataforjson FOR json auto,   
  9.          include_null_values,   
  10.          without_array_wrappergo  
Select Top (n) sp structure,
  1. CREATE PROCEDURE [Schema].[SP_Name]   
  2.   @Parameter DATATYPE = optional   
  3. AS   
  4.   WITH dataforjson AS ( (   
  5.   --|| your top(n) selects goes here ||--   
  6.   ) )   
  7.   SELECT *   
  8.   FROM   dataforjson FOR json auto,   
  9.          include_null_valuesgo  
Relational store procedure FirstOrDefaultById example,
  1. CREATE PROCEDURE [SpJson].[Product_FirstOrDefaultById]   
  2.   @Id BIGINT = NULL   
  3. AS   
  4.   WITH dataforjson AS (   
  5.   (   
  6.          SELECT TOP (1)   
  7.                 [product].[Id] ,   
  8.                 [product].[Title] ,   
  9.                 [product].[CategoryId] ,   
  10.                 --|| Start One level parent drill down select one-one relation object ||--   
  11.                 (json_query (   
  12.                 (   
  13.                        SELECT TOP (1)   
  14.                               [product_category].[Id] ,   
  15.                               [product_category].[Name],   
  16.                               --|| Start Two level prent drill down select one-one relation object ||--   
  17.                               (json_query (   
  18.                               (   
  19.                                      SELECT TOP (1)   
  20.                                             [parent_product_category].[Id] ,   
  21.                                             [parent_product_category].[Name]   
  22.                                      FROM   [Shop].[Category] AS parent_product_category   
  23.                                      WHERE  parent_product_category.[Id] = [product_category].[Id] FOR json path,   
  24.                                             include_null_values,   
  25.                                             without_array_wrapper ))) AS [Parent_Category]   
  26.                               --|| End Two level prent drill down select one-one relation object ||--   
  27.                        FROM   [Shop].[Category] AS product_category   
  28.                        WHERE  product_category.[Id] = product.[CategoryId] FOR json path,   
  29.                               include_null_values,   
  30.                               without_array_wrapper ))) AS [Category],   
  31.                 --|| End One level prent drill down select one-one relation object ||--   
  32.                 --|| Start One level child drill down select one-many relation array ||--   
  33.                 ,   
  34.                 (json_query (   
  35.                 (   
  36.                          SELECT TOP (30)   
  37.                                   [product_tempcarditem].[Id] ,   
  38.                                   [product_tempcarditem].[Count] ,   
  39.                                   [product_tempcarditem].[ProductId] ,   
  40.                                   [product_tempcarditem].[TempCardId]   
  41.                          FROM     [Shop].[TempCardItem] AS [product_tempcarditem]   
  42.                          WHERE    [product_tempcarditem].[Id] = [product].[ProductId]   
  43.                          ORDER BY [product_tempcarditem].id DESC FOR json path,   
  44.                                   include_null_values) )) AS [TempCardItem_Id]   
  45.                 --|| End One level child drill down select one-many relation array ||--   
  46.          FROM   [Shop].[Product] [product]   
  47.          WHERE  [product].[Id] = @Id ) )   
  48.   SELECT *   
  49.   FROM   dataforjson FOR json auto,   
  50.          include_null_values,   
  51.          without_array_wrappergo  

Topic 2 - Repository Pattern

 
I know that we don’t have an Entity class for a repository, but I have an idea to create your repository based on SQL JSON. Just follow this in your database:
  1. Create a schema with name [SpJson] or anything else and create all JSON store procedures in this schema in your database
  2. Start each store procedure names for each table with the name of their table, like this for Product table: CREATEPROCEDURE [SpJson].[Product_ToList]
  3. Create many store procedures of any type for each table, like what I did in Topic 1
  4. Don’t forget to create your select store procedures with optional parameters and optional case conditions in where statement (this help your store procedures always have return optional data without any parameter)
  5. Return inserted or updated rows as JSON object after an update or insert transaction (don’t forget use try catch for insert and update procedures because you don’t have ORM to manage your errors, try catch help you detect your errors)
  6. Create all select stored procedures with all condition types in where statement and put all parameters for conditions
Now your database is ready to use a simple repository and work by the interfaces that I am going to explain.
 
Step 1 (Create base interfaces)
 
IBaseSpName
 
General store procedures names. This interface is used for general names and return names for request entity. In fact this interface return all names with the requested entity for usage in repository.
  1. public interface IBaseSpName  
  2. {  
  3.     string EntityName { getset; }  
  4.     string Count { get; }  
  5.     string ToList { get; }  
  6.     string PagedList { get; }  
  7.     string GetDeletedList { get; }  
  8.     string FirstOrDefault { get; }  
  9.     string FirstOrDefaultById { get; }  
  10.     string DeletedFirstOrDefault { get; }  
  11.     string AutoComplete { get; }  
  12.     string Add { get; }  
  13.     string Update { get; }  
  14.     string Delete { get; }  
  15.     string Restore { get; }  
  16. }  
IBaseSqlSpParam
 
Don’t forget to put GetSpParams() as string method in this interface for converting values to store procedures parameters.Then, add general properties that are used in tables. Like: Id.
  1. public interface IBaseSqlSpParam  
  2. {  
  3.     string GetSpParams();  
  4.     bool? IsDeleted { getset; }  
  5.     DateTimeOffset? SubmitDate { getset; }  
  6.     DateTimeOffset? ModifyDate { getset; }  
  7.   
  8. }  
Step 2 - (Create Base Models inherited from interfaces)
 
BaseSqlFilterParam
 
Create a simple filter model inherited from IBaseSqlSpParam with general properties in tables Like:Id. Put an empty GetSpParams() method in this class.
  1. public class BaseSqlFilterParam : IBaseSqlSpParam  
  2. {  
  3.     public virtual bool? IsDeleted { getset; }  
  4.     public virtual DateTimeOffset? SubmitDate { getset; }  
  5.     public virtual DateTimeOffset? ModifyDate { getset; }  
  6.     public virtual string GetSpParams()  
  7.     {  
  8.         return "";  
  9.     }  
  10. }  
BaseSqlListFilterParam
 
Create a list filter model inherited from IBaseSqlSpParam with general properties in tables Like:Id.
 
Don’t forget put int TopCount property for this model. This model is used for top count store procedures in entity sp.
  1. public class BaseSqlListFilterParam : IBaseSqlSpParam  
  2. {  
  3.     public virtual bool? IsDeleted { getset; } = false;  
  4.     public virtual int? TopCount { getset; } = 100;  
  5.     public virtual DateTimeOffset? SubmitDate { getset; }  
  6.     public virtual DateTimeOffset? ModifyDate { getset; }  
  7.     public virtual string GetSpParams()  
  8.     {  
  9.         return "";  
  10.     }  
  11. }  
BaseSqlPaginationFilterParam
 
Create a pagination filter model inherited from IBaseSqlSpParam with general properties in tables Like:Id.
 
Don’t forget to put int Skip and int Size properties for this model. This model used for fetching offset select store procedures in entity sp.
  1. public class BaseSqlPaginationFilterParam : IBaseSqlSpParam  
  2. {  
  3.     public virtual bool? IsDeleted { getset; } = false;  
  4.     public virtual int? Skip { getset; } = 0;  
  5.     public virtual int? Size { getset; } = 10;  
  6.     public virtual DateTimeOffset? SubmitDate { getset; }  
  7.     public virtual DateTimeOffset? ModifyDate { getset; }  
  8.     public virtual string GetSpParams()  
  9.     {  
  10.         return "";  
  11.     }  
  12. }  
BaseSqlAddParam
 
Create a base model for insert store procedures like this.
  1. public class BaseSqlAddParam : IBaseSqlSpParam  
  2. {  
  3.     public virtual bool? IsDeleted { getset; } = false;  
  4.     public virtual DateTimeOffset? SubmitDate { getset; }  
  5.     public virtual DateTimeOffset? ModifyDate { getset; }  
  6.     public virtual string GetSpParams()  
  7.     {  
  8.         return "";  
  9.     }  
  10. }   
BaseSqlUpdateParam
 
Create a base model for updating store procedures like this:
  1. public class BaseSqlUpdateParam : IBaseSqlSpParam  
  2. {  
  3.     public virtual bool? IsDeleted { getset; } = false;  
  4.     public virtual DateTimeOffset? SubmitDate { getset; }  
  5.     public virtual DateTimeOffset? ModifyDate { getset; }  
  6.   
  7.     public virtual string GetSpParams()  
  8.     {  
  9.         return "";  
  10.     }  
  11. }  
BaseSpName
 
Important model class for create and using store procedures names:
  1. public class BaseSpName : IBaseSpName  
  2.     {  
  3.         public virtual string EntityName { getset; }  
  4.         public virtual string Count { get { return $"[SpJson].[{EntityName}_Count] "; } }  
  5.         public virtual string ToList { get { return $"[SpJson].[{EntityName}_ToList] "; } }  
  6.         public virtual string PagedList { get { return $"[SpJson].[{EntityName}_PageList]"; } }  
  7.         public virtual string GetDeletedList { get { return $"[SpJson].[{EntityName}_ToList]"; } }  
  8.         public virtual string FirstOrDefault { get { return $"[SpJson].[{EntityName}_FirstOrDefault]"; } }  
  9.         public virtual string FirstOrDefaultById { get { return $"[SpJson].[{EntityName}_FirstOrDefaultById]"; } }  
  10.         public virtual string DeletedFirstOrDefault { get { return $"[SpJson].[{EntityName}_FirstOrDefault]"; } }  
  11.         public virtual string AutoComplete { get { return $"[SpJson].[{EntityName}_AutoComplete]"; } }  
  12.         public virtual string Add { get { return $"[SpJson].[{EntityName}_AddNewData]"; } }  
  13.         public virtual string Update { get { return $"[SpJson].[{EntityName}_UpdateData]"; } }  
  14.         public virtual string Delete { get { return $"[SpJson].[{EntityName}_DeleteData]"; } }  
  15.         public virtual string Restore { get { return $"[SpJson].[{EntityName}_RestoreData]"; } }  
  16.   
  17.           
  18.     }  
Step 3
 
(Create static entity name class),
  1. public static class EntityNames  
  2. {  
  3.    public static string LoginInfo { get { return "LoginInfo"; } }   
  4.    public static string UserInfo { get { return "UserInfo"; } }   
  5.    public static string Role { get { return "Role"; } }   
  6.    public static string Product { get { return "Product"; } }   
  7.    public static string Category { get { return "Category"; } }   
  8.    public static string TempCard { get { return "TempCard"; } }   
  9.    public static string TempCardItem { get { return "TempCardItem"; } }   
  10.    public static string ShippingType { get { return "ShippingType"; } }   
  11.    public static string Factor { get { return "Factor"; } }   
  12.    public static string FactorInfo { get { return "FactorInfo"; } }   
  13. }   
Step 4 (Create Sql service class)
 
Follow these steps:
  1. Create a static class
  2. Add a static get property for select connection string from configuration
  3. Add a async static Task<string> method for get JSON string from strore procedures
Create all convert parameters method for all your data types:
  1. public class SqlService  
  2. {  
  3.     public static string ConnectionString  
  4.     {  
  5.         get  
  6.         {  
  7.             return ConfigurationManager.ConnectionStrings["DataBase"].ConnectionString;  
  8.         }  
  9.     }  
  10.   
  11.     public async static Task<string> GetDataResult(string query)  
  12.     {  
  13.         try  
  14.         {  
  15.             var queryWithForJson = query;  
  16.             var conn = new SqlConnection(SqlService.ConnectionString);  
  17.             var cmd = new SqlCommand(queryWithForJson, conn);  
  18.             await conn.OpenAsync();  
  19.             var jsonResult = new StringBuilder();  
  20.             var reader = await cmd.ExecuteReaderAsync();  
  21.             if (!reader.HasRows)  
  22.             {  
  23.                 jsonResult.Append("null");  
  24.             }  
  25.             else  
  26.             {  
  27.                 while (reader.Read())  
  28.                 {  
  29.                     jsonResult.Append(reader.GetValue(0).ToString());  
  30.                 }  
  31.             }  
  32.             conn.Close();  
  33.             return ReturnData(jsonResult.ToString());  
  34.         }  
  35.         catch (Exception e)  
  36.         {  
  37.             return GeneralGetDataError(e.Message);  
  38.         }  
  39.     }  
  40.   
  41.     public static string ReturnData(string json)  
  42.     {  
  43.         return "{\"Message\":\"DONE\",\"Status\":true,\"Data\":" + json + "}";  
  44.     }  
  45.   
  46.     public static string GeneralGetDataError(string message)  
  47.     {  
  48.         return "{\"Message\":\"" + message + "\",\"Status\":false}";  
  49.     }  
  50.   
  51.     public static string Convert(long? value)  
  52.     {  
  53.         var result = $"{(value.HasValue ? value.ToString() : "NULL")}";  
  54.   
  55.         return result;  
  56.     }  
  57.   
  58.     public static string Convert(byte[] value)  
  59.     {  
  60.         var result = "NULL";  
  61.   
  62.         return result;  
  63.     }  
  64.   
  65.     public static string Convert(bool? value)  
  66.     {  
  67.         var result = $"{(value.HasValue ? value.Value ? (1).ToString() : (0).ToString() : "NULL")}";  
  68.   
  69.         return result;  
  70.     }  
  71.   
  72.     public static string Convert(string value)  
  73.     {  
  74.         var result = $"{(value != null && value.Length > 0 ? "'" + value.ToString() + "'" : "NULL")}";  
  75.   
  76.         return result;  
  77.     }  
  78.   
  79.     public static string Convert(DateTime? value)  
  80.     {  
  81.         var result = $"{(value.HasValue ? "'" + value.Value.ToLongDateString().Replace("/", "-") + "'" : "NULL")}";  
  82.   
  83.         return result;  
  84.     }  
  85.   
  86.     public static string Convert(DateTimeOffset? value)  
  87.     {  
  88.         var result = $"{(value.HasValue ? "'" + value.Value.Date.ToString("o").Replace("/","-") + "'" : "NULL")}";  
  89.   
  90.         return result;  
  91.     }  
  92.   
  93.     public static string Convert(decimal? value)  
  94.     {  
  95.         var result = $"{(value.HasValue ? value.ToString() : "NULL")}"; ;  
  96.   
  97.         return result;  
  98.     }  
  99.   
  100.     public static string Convert(double? value)  
  101.     {  
  102.         var result = $"{(value.HasValue ? value.ToString() : "NULL")}";  
  103.   
  104.         return result;  
  105.     }  
  106.   
  107.     public static string Convert(DbGeography value)  
  108.     {  
  109.         var result = "NULL";  
  110.   
  111.         return result;  
  112.     }  
  113.   
  114.     public static string Convert(DbGeometry value)  
  115.     {  
  116.         var result = "NULL";  
  117.   
  118.         return result;  
  119.     }  
  120.   
  121.     public static string Convert(int? value)  
  122.     {  
  123.         var result = $"{(value.HasValue ? value.ToString() : "NULL")}";  
  124.   
  125.         return result;  
  126.     }  
  127.   
  128.     public static string Convert(float? value)  
  129.     {  
  130.         var result = $"{(value.HasValue ? value.ToString() : "NULL")}";  
  131.   
  132.         return result;  
  133.     }  
  134.   
  135.     public static string Convert(short? value)  
  136.     {  
  137.         var result = $"{(value.HasValue ? value.ToString() : "NULL")}";  
  138.   
  139.         return result;  
  140.     }  
  141.   
  142.     public static string Convert(TimeSpan? value)  
  143.     {  
  144.         var result = $"{(value.HasValue ? "'" + value.Value.ToString() + "'" : "NULL")}";  
  145.   
  146.         return result;  
  147.     }  
  148.   
  149.     public static string Convert(byte? value)  
  150.     {  
  151.         var result = "NULL";  
  152.   
  153.         return result;  
  154.     }  
  155. }  
Step 5 (Create repository class)
 
Now create your repository class like this,
 
Don’t forget entity name string parameter in your Constractor.
 
Each method is for each store procedure name.
 
Don’t forget use where to Identify your base interfaces in your repository
  1. public class BaseSqlData  
  2. <  
  3.     BaseSqlFilterParam,  
  4.     BaseSqlListFilterParam,  
  5.     BaseSqlPaginationFilterParam,  
  6.     BaseSqlAddParam,  
  7.     BaseSqlUpdateParam,  
  8.     BaseSpName  
  9. >   
  10. : IDisposable  
  11. where BaseSqlFilterParam : IBaseSqlSpParam  
  12. where BaseSqlListFilterParam : class, IBaseSqlSpParam  
  13. where BaseSqlPaginationFilterParam : class, IBaseSqlSpParam  
  14. where BaseSqlAddParam : class, IBaseSqlSpParam  
  15. where BaseSqlUpdateParam : class, IBaseSqlSpParam  
  16. where BaseSpName : class, IBaseSpName  
  17. {  
  18.     protected BaseSpName _spNames;  
  19.   
  20.     public BaseSqlData(string entityName,BaseSpName spNames)  
  21.     {  
  22.         _spNames = spNames;  
  23.         _spNames.EntityName = entityName;  
  24.     }  
  25.   
  26.     public virtual async Task<string> Count(BaseSqlFilterParam filter)  
  27.     {  
  28.         filter.IsDeleted = false;  
  29.         var result = await SqlService.GetDataResult(_spNames.Count + " " + filter.GetSpParams());  
  30.         return result;  
  31.     }  
  32.   
  33.     public virtual async Task<string> ToList(BaseSqlListFilterParam filter)  
  34.     {  
  35.         filter.IsDeleted = false;  
  36.         var result = await SqlService.GetDataResult(_spNames.ToList + " " + filter.GetSpParams());  
  37.         return result;  
  38.     }  
  39.   
  40.     public virtual async Task<string> PagedList(BaseSqlPaginationFilterParam filter)  
  41.     {  
  42.         filter.IsDeleted = false;  
  43.         var result = await SqlService.GetDataResult(_spNames.PagedList + " " + filter.GetSpParams());  
  44.         return result;  
  45.     }  
  46.   
  47.     public virtual async Task<string> GetDeletedList(BaseSqlListFilterParam filter)  
  48.     {  
  49.         filter.IsDeleted = true;  
  50.         var result = await SqlService.GetDataResult(_spNames.GetDeletedList + " " + filter.GetSpParams());  
  51.         return result;  
  52.     }  
  53.   
  54.     public virtual async Task<string> FirstOrDefault(BaseSqlFilterParam filter)  
  55.     {  
  56.         filter.IsDeleted = false;  
  57.         var result = await SqlService.GetDataResult(_spNames.FirstOrDefault + " " + filter.GetSpParams());  
  58.         return result;  
  59.     }  
  60.   
  61.     public virtual async Task<string> FirstOrDefaultById(long? id)  
  62.     {  
  63.         var result = await SqlService.GetDataResult(_spNames.FirstOrDefaultById + " " + id);  
  64.         return result;  
  65.     }  
  66.   
  67.     public virtual async Task<string> DeletedFirstOrDefault(BaseSqlFilterParam filter)  
  68.     {  
  69.         filter.IsDeleted = true;  
  70.         var result = await SqlService.GetDataResult(_spNames.DeletedFirstOrDefault + " " + filter.GetSpParams());  
  71.         return result;  
  72.     }  
  73.   
  74.     public virtual async Task<string> AutoComplete(BaseSqlListFilterParam filter)  
  75.     {  
  76.         filter.IsDeleted = false;  
  77.         var result = await SqlService.GetDataResult(_spNames.AutoComplete + " " + filter.GetSpParams());  
  78.         return result;  
  79.     }  
  80.   
  81.     public virtual async Task<string> Add(BaseSqlAddParam item)  
  82.     {  
  83.         item.IsDeleted = false;  
  84.         item.SubmitDate = DateTimeOffset.Now;  
  85.         item.ModifyDate = DateTimeOffset.Now;  
  86.         var result = await SqlService.GetDataResult(_spNames.Add + " " + item.GetSpParams());  
  87.         return result;  
  88.     }  
  89.   
  90.     public virtual async Task<string> Update(BaseSqlUpdateParam item)  
  91.     {  
  92.         item.IsDeleted = false;  
  93.         item.ModifyDate = DateTimeOffset.Now;  
  94.         var result = await SqlService.GetDataResult(_spNames.Update + " " + item.GetSpParams());  
  95.         return result;  
  96.     }  
  97.   
  98.     public virtual async Task<string> Delete(long? id)  
  99.     {  
  100.         var result = await SqlService.GetDataResult(_spNames.Delete + " " + id);  
  101.         return result;  
  102.     }  
  103.   
  104.     public virtual async Task<string> Restore(long? id)  
  105.     {  
  106.         var result = await SqlService.GetDataResult(_spNames.Restore + " " + id);  
  107.         return result;  
  108.     }  
  109.   
  110.     public void Dispose()  
  111.     {  
  112.         Dispose(true);  
  113.         GC.SuppressFinalize(true);  
  114.     }  
  115.   
  116.     private void Dispose(bool disposing)  
  117.     {  
  118.         if (disposing)  
  119.         {  
  120.             //to do  
  121.         }  
  122.     }  
  123. }   
Step 6 (Create repository class for each entity)
 
This step is optional. Use it if you have another structure or sp names in your entity
 
Like this example for products. 
  1.  public class ProductSqlData : BaseSqlData<ProductFilter, ProductListFilter, ProductPaginationFilter, ProductAdd, ProductUpdate, BaseSpName>  
  2.  {  
  3.      public ProductSqlData(string entityName, BaseSpName spNames) : base(entityName, spNames)  
  4.      {  
  5.          _spNames.EntityName = entityName;  
  6.      }  
  7.   
  8.      public override async Task<string> Count(ProductFilter filter)  
  9.      {  
  10.          filter.IsDeleted = false;  
  11.          var result = await SqlService.GetDataResult(_spNames.Count + " " + filter.GetSpParams());  
  12.          return result;  
  13.      }  
  14.   
  15.      public override async Task<string> ToList(ProductListFilter filter)  
  16.      {  
  17.          filter.IsDeleted = false;  
  18.          var result = await SqlService.GetDataResult(_spNames.ToList + " " + filter.GetSpParams());  
  19.          return result;  
  20.      }  
  21.   
  22.      public override async Task<string> PagedList(ProductPaginationFilter filter)  
  23.      {  
  24.          filter.IsDeleted = false;  
  25.          var result = await SqlService.GetDataResult(_spNames.PagedList + " " + filter.GetSpParams());  
  26.          return result;  
  27.      }  
  28.   
  29.      public override async Task<string> GetDeletedList(ProductListFilter filter)  
  30.      {  
  31.          filter.IsDeleted = true;  
  32.          var result = await SqlService.GetDataResult(_spNames.GetDeletedList + " " + filter.GetSpParams());  
  33.          return result;  
  34.      }  
  35.   
  36.      public override async Task<string> FirstOrDefault(ProductFilter filter)  
  37.      {  
  38.          filter.IsDeleted = false;  
  39.          var result = await SqlService.GetDataResult(_spNames.FirstOrDefault + " " + filter.GetSpParams());  
  40.          return result;  
  41.      }  
  42.   
  43.      public override async Task<string> FirstOrDefaultById(long? id)  
  44.      {  
  45.          var result = await SqlService.GetDataResult(_spNames.FirstOrDefaultById + " " + id);  
  46.          return result;  
  47.      }  
  48.   
  49.      public override async Task<string> DeletedFirstOrDefault(ProductFilter filter)  
  50.      {  
  51.          filter.IsDeleted = true;  
  52.          var result = await SqlService.GetDataResult(_spNames.DeletedFirstOrDefault + " " + filter.GetSpParams());  
  53.          return result;  
  54.      }  
  55.   
  56.      public override async Task<string> AutoComplete(ProductListFilter filter)  
  57.      {  
  58.          filter.IsDeleted = false;  
  59.          var result = await SqlService.GetDataResult(_spNames.AutoComplete + " " + filter.GetSpParams());  
  60.          return result;  
  61.      }  
  62.   
  63.      public override async Task<string> Add(ProductAdd item)  
  64.      {  
  65.          item.IsDeleted = false;  
  66.          item.SubmitDate = DateTimeOffset.Now;  
  67.          item.ModifyDate = DateTimeOffset.Now;  
  68.          var result = await SqlService.GetDataResult(_spNames.Add + " " + item.GetSpParams());  
  69.          return result;  
  70.      }  
  71.   
  72.      public override async Task<string> Update(ProductUpdate item)  
  73.      {  
  74.          item.IsDeleted = false;  
  75.          item.ModifyDate = DateTimeOffset.Now;  
  76.          var result = await SqlService.GetDataResult(_spNames.Update + " " + item.GetSpParams());  
  77.          return result;  
  78.      }  
  79.   
  80.      public override async Task<string> Delete(long? id)  
  81.      {  
  82.          var result = await SqlService.GetDataResult(_spNames.Delete + " " + id);  
  83.          return result;  
  84.      }  
  85.   
  86.      public override async Task<string> Restore(long? id)  
  87.      {  
  88.          var result = await SqlService.GetDataResult(_spNames.Restore + " " + id);  
  89.          return result;  
  90.      }  
  91.  }  
Step 7 (Create repository manager class)
 
Store procedure names and entity name comes from constractor of repository manager class.
  1.   public class SqlDataManager  
  2.   {  
  3.       protected string _entityName = "";  
  4.       protected BaseSpName _spName;  
  5.       public SqlDataManager(string entityName, BaseSpName spName)  
  6.       {  
  7.           _spName = spName;  
  8.           _entityName = entityName;  
  9.       }  
  10.   
  11.               
  12. private BaseSqlData<LoginInfoFilter, LoginInfoListFilter, LoginInfoPaginationFilter, LoginInfoAdd, LoginInfoUpdate, BaseSpName> _LoginInfo;  
  13.   
  14.       public BaseSqlData<LoginInfoFilter, LoginInfoListFilter, LoginInfoPaginationFilter, LoginInfoAdd, LoginInfoUpdate, BaseSpName> LoginInfo  
  15.       {  
  16.           get { if (_LoginInfo == null)  _LoginInfo =  new LoginInfoSqlData(_entityName, _spName); return _LoginInfo; }  
  17.       }  
  18.   
  19. private BaseSqlData<UserInfoFilter, UserInfoListFilter, UserInfoPaginationFilter, UserInfoAdd, UserInfoUpdate, BaseSpName> _UserInfo;  
  20.   
  21.       public BaseSqlData<UserInfoFilter, UserInfoListFilter, UserInfoPaginationFilter, UserInfoAdd, UserInfoUpdate, BaseSpName> UserInfo  
  22.       {  
  23.           get { if (_UserInfo == null)  _UserInfo =  new UserInfoSqlData(_entityName, _spName); return _UserInfo; }  
  24.       }  
  25.   
  26. private BaseSqlData<RoleFilter, RoleListFilter, RolePaginationFilter, RoleAdd, RoleUpdate, BaseSpName> _Role;  
  27.   
  28.       public BaseSqlData<RoleFilter, RoleListFilter, RolePaginationFilter, RoleAdd, RoleUpdate, BaseSpName> Role  
  29.       {  
  30.           get { if (_Role == null)  _Role =  new RoleSqlData(_entityName, _spName); return _Role; }  
  31.       }  
  32.   
  33. private BaseSqlData<ProductFilter, ProductListFilter, ProductPaginationFilter, ProductAdd, ProductUpdate, BaseSpName> _Product;  
  34.   
  35.       public BaseSqlData<ProductFilter, ProductListFilter, ProductPaginationFilter, ProductAdd, ProductUpdate, BaseSpName> Product  
  36.       {  
  37.           get { if (_Product == null)  _Product =  new ProductSqlData(_entityName, _spName); return _Product; }  
  38.       }  
  39.   
  40. private BaseSqlData<CategoryFilter, CategoryListFilter, CategoryPaginationFilter, CategoryAdd, CategoryUpdate, BaseSpName> _Category;  
  41.   
  42.       public BaseSqlData<CategoryFilter, CategoryListFilter, CategoryPaginationFilter, CategoryAdd, CategoryUpdate, BaseSpName> Category  
  43.       {  
  44.           get { if (_Category == null)  _Category =  new CategorySqlData(_entityName, _spName); return _Category; }  
  45.       }  
  46.   
  47. private BaseSqlData<TempCardFilter, TempCardListFilter, TempCardPaginationFilter, TempCardAdd, TempCardUpdate, BaseSpName> _TempCard;  
  48.   
  49.       public BaseSqlData<TempCardFilter, TempCardListFilter, TempCardPaginationFilter, TempCardAdd, TempCardUpdate, BaseSpName> TempCard  
  50.       {  
  51.           get { if (_TempCard == null)  _TempCard =  new TempCardSqlData(_entityName, _spName); return _TempCard; }  
  52.       }  
  53.   
  54. private BaseSqlData<TempCardItemFilter, TempCardItemListFilter, TempCardItemPaginationFilter, TempCardItemAdd, TempCardItemUpdate, BaseSpName> _TempCardItem;  
  55.   
  56.       public BaseSqlData<TempCardItemFilter, TempCardItemListFilter, TempCardItemPaginationFilter, TempCardItemAdd, TempCardItemUpdate, BaseSpName> TempCardItem  
  57.       {  
  58.           get { if (_TempCardItem == null)  _TempCardItem =  new TempCardItemSqlData(_entityName, _spName); return _TempCardItem; }  
  59.       }  
  60.   
  61. private BaseSqlData<ShippingTypeFilter, ShippingTypeListFilter, ShippingTypePaginationFilter, ShippingTypeAdd, ShippingTypeUpdate, BaseSpName> _ShippingType;  
  62.   
  63.       public BaseSqlData<ShippingTypeFilter, ShippingTypeListFilter, ShippingTypePaginationFilter, ShippingTypeAdd, ShippingTypeUpdate, BaseSpName> ShippingType  
  64.       {  
  65.           get { if (_ShippingType == null)  _ShippingType =  new ShippingTypeSqlData(_entityName, _spName); return _ShippingType; }  
  66.       }  
  67.   
  68. private BaseSqlData<FactorFilter, FactorListFilter, FactorPaginationFilter, FactorAdd, FactorUpdate, BaseSpName> _Factor;  
  69.   
  70.       public BaseSqlData<FactorFilter, FactorListFilter, FactorPaginationFilter, FactorAdd, FactorUpdate, BaseSpName> Factor  
  71.       {  
  72.           get { if (_Factor == null)  _Factor =  new FactorSqlData(_entityName, _spName); return _Factor; }  
  73.       }  
  74.   
  75. private BaseSqlData<FactorInfoFilter, FactorInfoListFilter, FactorInfoPaginationFilter, FactorInfoAdd, FactorInfoUpdate, BaseSpName> _FactorInfo;  
  76.   
  77.       public BaseSqlData<FactorInfoFilter, FactorInfoListFilter, FactorInfoPaginationFilter, FactorInfoAdd, FactorInfoUpdate, BaseSpName> FactorInfo  
  78.       {  
  79.           get { if (_FactorInfo == null)  _FactorInfo =  new FactorInfoSqlData(_entityName, _spName); return _FactorInfo; }  
  80.       }  
  81.   
  82.   
  83.   
  84.   }  
Well, your repository is ready to use and we have some interfaced entities to working on it.
 

Topic 3 - Controllers

 
First lets create the base api controller and use our repository for controllers protected in base api controller and put jwtAuth attribute for Authorization,Roles And Permissions.
"Creating of jwtAuth class is in topic 5"
  1. [JwtAuth]  
  2. public class BaseApiController : ApiController  
  3. {  
  4.     protected SqlDataManager _sqlData;  
  5.     protected string _entityName = "";  
  6.     protected BaseSpName _spName;  
  7.   
  8.     public BaseApiController(string entityName)  
  9.     {  
  10.         BaseSpName spNames = new BaseSpName();  
  11.         _entityName = entityName;  
  12.         _sqlData = new SqlDataManager(entityName, spNames);  
  13.     }  
  14. }  
Now create your all controllers inherited from BaseApiController.
 
Product Contoller Example,
  1. [Route("Api/Product/")]  
  2. public class ProductController : BaseApiController  
  3. {  
  4.     public ProductController() : base (EntityNames.Product)  
  5.     {  
  6.   
  7.     }  
  8.   
  9.     [HttpGet, Route("Api/Product/ToList")]  
  10.     public async Task<dynamic> ToList([FromUri] ProductListFilter filter)  
  11.     {  
  12.         filter = filter ?? new ProductListFilter();  
  13.         var result = await _sqlData.Product.ToList(filter);  
  14.   
  15.         return new { Result = JsonConvert.DeserializeObject(result) };  
  16.     }  
  17.   
  18.     [HttpGet, Route("Api/Product/PagedList")]  
  19.     public async Task<dynamic> PagedList([FromUri] ProductPaginationFilter filter)  
  20.     {  
  21.         filter = filter ?? new ProductPaginationFilter();  
  22.         var result = await _sqlData.Product.PagedList(filter);  
  23.         return new { Result = JsonConvert.DeserializeObject(result) };  
  24.     }  
  25.   
  26.     [HttpGet, Route("Api/Product/GetDeletedList")]  
  27.     public async Task<dynamic> GetDeletedList([FromUri] ProductListFilter filter)  
  28.     {  
  29.         filter = filter ?? new ProductListFilter();  
  30.         var result = await _sqlData.Product.GetDeletedList(filter);  
  31.         return new { Result = JsonConvert.DeserializeObject(result) };  
  32.     }  
  33.   
  34.     [HttpGet, Route("Api/Product/FirstOrDefault")]  
  35.     public async Task<dynamic> FirstOrDefault([FromUri] ProductFilter filter)  
  36.     {  
  37.         filter = filter ?? new ProductFilter();  
  38.         var result = await _sqlData.Product.FirstOrDefault(filter);  
  39.         return new { Result = JsonConvert.DeserializeObject(result) };  
  40.     }  
  41.   
  42.     [HttpGet, Route("Api/Product/FirstOrDefaultById/{id}")]  
  43.     public async Task<dynamic> FirstOrDefaultById(long? id)  
  44.     {  
  45.         if (id == null)  
  46.             return new { JsonString = "Error" };  
  47.         var result = await _sqlData.Product.FirstOrDefaultById(id);  
  48.         return new { Result = JsonConvert.DeserializeObject(result) };  
  49.     }  
  50.   
  51.     [HttpGet, Route("Api/Product/DeletedFirstOrDefault")]  
  52.     public async Task<dynamic> DeletedFirstOrDefault([FromUri] ProductFilter filter)  
  53.     {  
  54.         filter = filter ?? new ProductFilter();  
  55.         var result = await _sqlData.Product.DeletedFirstOrDefault(filter);  
  56.         return new { Result = JsonConvert.DeserializeObject(result) };  
  57.     }  
  58.   
  59.     [HttpGet, Route("Api/Product/AutoComplete")]  
  60.     public async Task<dynamic> AutoComplete([FromUri] ProductListFilter filter)  
  61.     {  
  62.         filter = filter ?? new ProductListFilter();  
  63.         var result = await _sqlData.Product.AutoComplete(filter);  
  64.         return new { Result = JsonConvert.DeserializeObject(result) };  
  65.     }  
  66.   
  67.     [HttpPost, Route("Api/Product/Add")]  
  68.     public async Task<dynamic> Add([FromBody]ProductAdd item)  
  69.     {  
  70.         if(item == null)  
  71.             return new { JsonString = "Error" };  
  72.   
  73.         var currentUser = JwtIdentity.UserInfo(Thread.CurrentPrincipal.Identity);  
  74.         //item.SubmiterUserId = currentUser.Id;  
  75.         var result = await _sqlData.Product.Add(item);  
  76.         return new { Result = JsonConvert.DeserializeObject(result) };  
  77.     }  
  78.   
  79.     [HttpPut, Route("Api/Product/Update")]  
  80.     public async Task<dynamic> Update([FromBody]ProductUpdate item)  
  81.     {  
  82.         if (item == null)  
  83.             return new { JsonString = "Error" };  
  84.         var currentUser = JwtIdentity.UserInfo(Thread.CurrentPrincipal.Identity);  
  85.         //item.SubmiterUserId = currentUser.Id;  
  86.         var result = await _sqlData.Product.Update(item);  
  87.         return new { Result = JsonConvert.DeserializeObject(result) };  
  88.     }  
  89.   
  90.     [HttpDelete, Route("Api/Product/Delete/{id}")]  
  91.     public async Task<dynamic> Delete([FromUri] long? id)  
  92.     {  
  93.         var result = await _sqlData.Product.Delete(id);  
  94.         return new { Result = JsonConvert.DeserializeObject(result) };  
  95.     }  
  96.   
  97.     [HttpPut, Route("Api/Product/Restore/{id}")]  
  98.     public async Task<dynamic> Restore([FromUri] long? id)  
  99.     {  
  100.         var result = await _sqlData.Product.Restore(id);  
  101.         return new { Result = JsonConvert.DeserializeObject(result) };  
  102.     }  
  103. }  

Topic 4 - Models

 
You have to create models inherited from all your base models for each entity name in your entity name class, you can ignore sp names if you don’t have different names for your entity. Or if you have a different store procedure structure in your entity create your own sp name class for this entity and use it in entity repository class.
 
Lets see all inherited models for a product entity,
 
Filter Model
  1.   public class ProductFilter : BaseSqlFilterParam  
  2.   {  
  3.   
  4. public long? Id { getset; }  
  5. public string MultiText_Id { getset; }  
  6. public long? MinId { getset; }  
  7. public long? MaxId { getset; }  
  8.   
  9. public double? Price { getset; }  
  10. public string MultiText_Price { getset; }  
  11. public double? MinPrice { getset; }  
  12. public double? MaxPrice { getset; }  
  13.   
  14. public int? Count { getset; }  
  15. public string MultiText_Count { getset; }  
  16. public int? MinCount { getset; }  
  17. public int? MaxCount { getset; }  
  18.   
  19. public string Title { getset; }  
  20. public string MultiText_Title { getset; }  
  21.   
  22. public int? OffPercent { getset; }  
  23. public string MultiText_OffPercent { getset; }  
  24. public int? MinOffPercent { getset; }  
  25. public int? MaxOffPercent { getset; }  
  26.   
  27. public long? CategoryId { getset; }  
  28. public string MultiText_CategoryId { getset; }  
  29. public long? MinCategoryId { getset; }  
  30. public long? MaxCategoryId { getset; }  
  31.   
  32. public bool? IsDeleted { getset; }  
  33. public string MultiText_IsDeleted { getset; }  
  34.   
  35. public DateTimeOffset? ModifyDate { getset; }  
  36. public string MultiText_ModifyDate { getset; }  
  37. public DateTimeOffset? MinModifyDate { getset; }  
  38. public DateTimeOffset? MaxModifyDate { getset; }  
  39.   
  40. public DateTimeOffset? SubmitDate { getset; }  
  41. public string MultiText_SubmitDate { getset; }  
  42. public DateTimeOffset? MinSubmitDate { getset; }  
  43. public DateTimeOffset? MaxSubmitDate { getset; }  
  44.   
  45.   
  46.       public override string GetSpParams()  
  47.       {  
  48.           var result = "";  
  49.   
  50.             
  51.     result += SqlService.Convert(Id) + ", ";  
  52.     result += SqlService.Convert(MultiText_Id) + ", ";  
  53.     result += SqlService.Convert(MinId) + ", ";  
  54.     result += SqlService.Convert(MaxId) + ",  ";  
  55.   
  56.     result += SqlService.Convert(Price) + ", ";  
  57.     result += SqlService.Convert(MultiText_Price) + ", ";  
  58.     result += SqlService.Convert(MinPrice) + ", ";  
  59.     result += SqlService.Convert(MaxPrice) + ",  ";  
  60.   
  61.     result += SqlService.Convert(Count) + ", ";  
  62.     result += SqlService.Convert(MultiText_Count) + ", ";  
  63.     result += SqlService.Convert(MinCount) + ", ";  
  64.     result += SqlService.Convert(MaxCount) + ",  ";  
  65.   
  66.     result += SqlService.Convert(Title) + ", ";  
  67.   
  68.     result += SqlService.Convert(MultiText_Title) + ",  ";  
  69.   
  70.   
  71.     result += SqlService.Convert(OffPercent) + ", ";  
  72.     result += SqlService.Convert(MultiText_OffPercent) + ", ";  
  73.     result += SqlService.Convert(MinOffPercent) + ", ";  
  74.     result += SqlService.Convert(MaxOffPercent) + ",  ";  
  75.   
  76.     result += SqlService.Convert(CategoryId) + ", ";  
  77.     result += SqlService.Convert(MultiText_CategoryId) + ", ";  
  78.     result += SqlService.Convert(MinCategoryId) + ", ";  
  79.     result += SqlService.Convert(MaxCategoryId) + ",  ";  
  80.   
  81.     result += SqlService.Convert(IsDeleted) + ", ";  
  82.   
  83.     result += SqlService.Convert(MultiText_IsDeleted) + ",  ";  
  84.   
  85.   
  86.     result += SqlService.Convert(ModifyDate) + ", ";  
  87.     result += SqlService.Convert(MultiText_ModifyDate) + ", ";  
  88.     result += SqlService.Convert(MinModifyDate) + ", ";  
  89.     result += SqlService.Convert(MaxModifyDate) + ",  ";  
  90.   
  91.     result += SqlService.Convert(SubmitDate) + ", ";  
  92.     result += SqlService.Convert(MultiText_SubmitDate) + ", ";  
  93.     result += SqlService.Convert(MinSubmitDate) + ", ";  
  94.     result += SqlService.Convert(MaxSubmitDate) + " ";  
  95.   
  96.   
  97.           return result;  
  98.       }  
  99.   }  
List Filter Model
  1. public class ProductListFilter : BaseSqlListFilterParam  
  2.   {  
  3. public override int? TopCount { getset; } = 100;  
  4.   
  5.   
  6. public long? Id { getset; }  
  7. public string MultiText_Id { getset; }  
  8. public long? MinId { getset; }  
  9. public long? MaxId { getset; }  
  10.   
  11. public double? Price { getset; }  
  12. public string MultiText_Price { getset; }  
  13. public double? MinPrice { getset; }  
  14. public double? MaxPrice { getset; }  
  15.   
  16. public int? Count { getset; }  
  17. public string MultiText_Count { getset; }  
  18. public int? MinCount { getset; }  
  19. public int? MaxCount { getset; }  
  20.   
  21. public string Title { getset; }  
  22. public string MultiText_Title { getset; }  
  23.   
  24. public int? OffPercent { getset; }  
  25. public string MultiText_OffPercent { getset; }  
  26. public int? MinOffPercent { getset; }  
  27. public int? MaxOffPercent { getset; }  
  28.   
  29. public long? CategoryId { getset; }  
  30. public string MultiText_CategoryId { getset; }  
  31. public long? MinCategoryId { getset; }  
  32. public long? MaxCategoryId { getset; }  
  33.   
  34. public bool? IsDeleted { getset; }  
  35. public string MultiText_IsDeleted { getset; }  
  36.   
  37. public DateTimeOffset? ModifyDate { getset; }  
  38. public string MultiText_ModifyDate { getset; }  
  39. public DateTimeOffset? MinModifyDate { getset; }  
  40. public DateTimeOffset? MaxModifyDate { getset; }  
  41.   
  42. public DateTimeOffset? SubmitDate { getset; }  
  43. public string MultiText_SubmitDate { getset; }  
  44. public DateTimeOffset? MinSubmitDate { getset; }  
  45. public DateTimeOffset? MaxSubmitDate { getset; }  
  46.   
  47.   
  48.       public override string GetSpParams()  
  49.       {  
  50.           var result = "";  
  51.   
  52.     result += SqlService.Convert(TopCount) + ", ";  
  53.             
  54.     result += SqlService.Convert(Id) + ", ";  
  55.     result += SqlService.Convert(MultiText_Id) + ", ";  
  56.     result += SqlService.Convert(MinId) + ", ";  
  57.     result += SqlService.Convert(MaxId) + ",  ";  
  58.   
  59.     result += SqlService.Convert(Price) + ", ";  
  60.     result += SqlService.Convert(MultiText_Price) + ", ";  
  61.     result += SqlService.Convert(MinPrice) + ", ";  
  62.     result += SqlService.Convert(MaxPrice) + ",  ";  
  63.   
  64.     result += SqlService.Convert(Count) + ", ";  
  65.     result += SqlService.Convert(MultiText_Count) + ", ";  
  66.     result += SqlService.Convert(MinCount) + ", ";  
  67.     result += SqlService.Convert(MaxCount) + ",  ";  
  68.   
  69.     result += SqlService.Convert(Title) + ", ";  
  70.   
  71.     result += SqlService.Convert(MultiText_Title) + ",  ";  
  72.   
  73.   
  74.     result += SqlService.Convert(OffPercent) + ", ";  
  75.     result += SqlService.Convert(MultiText_OffPercent) + ", ";  
  76.     result += SqlService.Convert(MinOffPercent) + ", ";  
  77.     result += SqlService.Convert(MaxOffPercent) + ",  ";  
  78.   
  79.     result += SqlService.Convert(CategoryId) + ", ";  
  80.     result += SqlService.Convert(MultiText_CategoryId) + ", ";  
  81.     result += SqlService.Convert(MinCategoryId) + ", ";  
  82.     result += SqlService.Convert(MaxCategoryId) + ",  ";  
  83.   
  84.     result += SqlService.Convert(IsDeleted) + ", ";  
  85.   
  86.     result += SqlService.Convert(MultiText_IsDeleted) + ",  ";  
  87.   
  88.   
  89.     result += SqlService.Convert(ModifyDate) + ", ";  
  90.     result += SqlService.Convert(MultiText_ModifyDate) + ", ";  
  91.     result += SqlService.Convert(MinModifyDate) + ", ";  
  92.     result += SqlService.Convert(MaxModifyDate) + ",  ";  
  93.   
  94.     result += SqlService.Convert(SubmitDate) + ", ";  
  95.     result += SqlService.Convert(MultiText_SubmitDate) + ", ";  
  96.     result += SqlService.Convert(MinSubmitDate) + ", ";  
  97.     result += SqlService.Convert(MaxSubmitDate) + " ";  
  98.   
  99.   
  100.           return result;  
  101.       }  
  102.   }  
Pagination Filter Model
  1.   public class ProductPaginationFilter : BaseSqlPaginationFilterParam  
  2.   {  
  3. public override int? Skip { getset; } = 0;  
  4. public override int? Size { getset; } = 10;  
  5.   
  6.   
  7. public long? Id { getset; }  
  8. public string MultiText_Id { getset; }  
  9. public long? MinId { getset; }  
  10. public long? MaxId { getset; }  
  11.   
  12. public double? Price { getset; }  
  13. public string MultiText_Price { getset; }  
  14. public double? MinPrice { getset; }  
  15. public double? MaxPrice { getset; }  
  16.   
  17. public int? Count { getset; }  
  18. public string MultiText_Count { getset; }  
  19. public int? MinCount { getset; }  
  20. public int? MaxCount { getset; }  
  21.   
  22. public string Title { getset; }  
  23. public string MultiText_Title { getset; }  
  24.   
  25. public int? OffPercent { getset; }  
  26. public string MultiText_OffPercent { getset; }  
  27. public int? MinOffPercent { getset; }  
  28. public int? MaxOffPercent { getset; }  
  29.   
  30. public long? CategoryId { getset; }  
  31. public string MultiText_CategoryId { getset; }  
  32. public long? MinCategoryId { getset; }  
  33. public long? MaxCategoryId { getset; }  
  34.   
  35. public bool? IsDeleted { getset; }  
  36. public string MultiText_IsDeleted { getset; }  
  37.   
  38. public DateTimeOffset? ModifyDate { getset; }  
  39. public string MultiText_ModifyDate { getset; }  
  40. public DateTimeOffset? MinModifyDate { getset; }  
  41. public DateTimeOffset? MaxModifyDate { getset; }  
  42.   
  43. public DateTimeOffset? SubmitDate { getset; }  
  44. public string MultiText_SubmitDate { getset; }  
  45. public DateTimeOffset? MinSubmitDate { getset; }  
  46. public DateTimeOffset? MaxSubmitDate { getset; }  
  47.   
  48.   
  49.       public override string GetSpParams()  
  50.       {  
  51.           var result = "";  
  52.   
  53.     result += SqlService.Convert(Skip) + ", ";  
  54.     result += SqlService.Convert(Size) + ", ";  
  55.   
  56.             
  57.     result += SqlService.Convert(Id) + ", ";  
  58.     result += SqlService.Convert(MultiText_Id) + ", ";  
  59.     result += SqlService.Convert(MinId) + ", ";  
  60.     result += SqlService.Convert(MaxId) + ",  ";  
  61.   
  62.     result += SqlService.Convert(Price) + ", ";  
  63.     result += SqlService.Convert(MultiText_Price) + ", ";  
  64.     result += SqlService.Convert(MinPrice) + ", ";  
  65.     result += SqlService.Convert(MaxPrice) + ",  ";  
  66.   
  67.     result += SqlService.Convert(Count) + ", ";  
  68.     result += SqlService.Convert(MultiText_Count) + ", ";  
  69.     result += SqlService.Convert(MinCount) + ", ";  
  70.     result += SqlService.Convert(MaxCount) + ",  ";  
  71.   
  72.     result += SqlService.Convert(Title) + ", ";  
  73.   
  74.     result += SqlService.Convert(MultiText_Title) + ",  ";  
  75.   
  76.   
  77.     result += SqlService.Convert(OffPercent) + ", ";  
  78.     result += SqlService.Convert(MultiText_OffPercent) + ", ";  
  79.     result += SqlService.Convert(MinOffPercent) + ", ";  
  80.     result += SqlService.Convert(MaxOffPercent) + ",  ";  
  81.   
  82.     result += SqlService.Convert(CategoryId) + ", ";  
  83.     result += SqlService.Convert(MultiText_CategoryId) + ", ";  
  84.     result += SqlService.Convert(MinCategoryId) + ", ";  
  85.     result += SqlService.Convert(MaxCategoryId) + ",  ";  
  86.   
  87.     result += SqlService.Convert(IsDeleted) + ", ";  
  88.   
  89.     result += SqlService.Convert(MultiText_IsDeleted) + ",  ";  
  90.   
  91.   
  92.     result += SqlService.Convert(ModifyDate) + ", ";  
  93.     result += SqlService.Convert(MultiText_ModifyDate) + ", ";  
  94.     result += SqlService.Convert(MinModifyDate) + ", ";  
  95.     result += SqlService.Convert(MaxModifyDate) + ",  ";  
  96.   
  97.     result += SqlService.Convert(SubmitDate) + ", ";  
  98.     result += SqlService.Convert(MultiText_SubmitDate) + ", ";  
  99.     result += SqlService.Convert(MinSubmitDate) + ", ";  
  100.     result += SqlService.Convert(MaxSubmitDate) + " ";  
  101.   
  102.   
  103.           return result;  
  104.       }  
  105.   }  
Insert Model
  1.   public class ProductAdd : BaseSqlAddParam  
  2.   {  
  3.   
  4. //[Column(Order = 2)]  
  5. //[Timestamp]  
  6. //[Required]  
  7. //[MaxLength(0) , MinLength(1)]  
  8. //[StringLength (0)]  
  9. public double? Price { getset; }  
  10.   
  11. //[Column(Order = 3)]  
  12. //[Timestamp]  
  13. //[Required]  
  14. //[MaxLength(0) , MinLength(1)]  
  15. //[StringLength (0)]  
  16. public int? Count { getset; }  
  17.   
  18. //[Column(Order = 4)]  
  19. //[Timestamp]  
  20. //[Required]  
  21. //[MaxLength(150) , MinLength(1)]  
  22. //[StringLength (150)]  
  23. public string Title { getset; }  
  24.   
  25. //[Column(Order = 5)]  
  26. //[Timestamp]  
  27. //[Required]  
  28. //[MaxLength(0) , MinLength(1)]  
  29. //[StringLength (0)]  
  30. public int? OffPercent { getset; }  
  31.   
  32. //[Column(Order = 6)]  
  33. //[Timestamp]  
  34. //[Required]  
  35. //[MaxLength(0) , MinLength(1)]  
  36. //[StringLength (0)]  
  37. public long? CategoryId { getset; }  
  38.   
  39. //[Column(Order = 999999997)]  
  40. //[Timestamp]  
  41. //[Required]  
  42. //[MaxLength(0) , MinLength(1)]  
  43. //[StringLength (0)]  
  44. public bool? IsDeleted { getset; }  
  45.   
  46. //[Column(Order = 999999998)]  
  47. //[Timestamp]  
  48. //[Required]  
  49. //[MaxLength(0) , MinLength(1)]  
  50. //[StringLength (0)]  
  51. public DateTimeOffset? ModifyDate { getset; }  
  52.   
  53. //[Column(Order = 999999999)]  
  54. //[Timestamp]  
  55. //[Required]  
  56. //[MaxLength(0) , MinLength(1)]  
  57. //[StringLength (0)]  
  58. public DateTimeOffset? SubmitDate { getset; }  
  59.   
  60.   
  61.       public override string GetSpParams()  
  62.       {  
  63.           var result = "";  
  64.   
  65.             
  66.         result += SqlService.Convert(Price) + ",  ";  
  67.   
  68.         result += SqlService.Convert(Count) + ",  ";  
  69.   
  70.         result += SqlService.Convert(Title) + ",  ";  
  71.   
  72.         result += SqlService.Convert(OffPercent) + ",  ";  
  73.   
  74.         result += SqlService.Convert(CategoryId) + ",  ";  
  75.   
  76.         result += SqlService.Convert(IsDeleted) + ",  ";  
  77.   
  78.         result += SqlService.Convert(ModifyDate) + ",  ";  
  79.   
  80.         result += SqlService.Convert(SubmitDate) + " ";  
  81.   
  82.   
  83.           return result;  
  84.       }  
  85.   }  
Update Model
  1.   public class ProductUpdate : BaseSqlUpdateParam  
  2.   {  
  3.   
  4. //[Column(Order = 1)]  
  5. //[Timestamp]  
  6. //[Required]  
  7. //[MaxLength(0) , MinLength(1)]  
  8. //[StringLength (0)]  
  9. public long? Id { getset; }  
  10.   
  11. //[Column(Order = 2)]  
  12. //[Timestamp]  
  13. //[Required]  
  14. //[MaxLength(0) , MinLength(1)]  
  15. //[StringLength (0)]  
  16. public double? Price { getset; }  
  17.   
  18. //[Column(Order = 3)]  
  19. //[Timestamp]  
  20. //[Required]  
  21. //[MaxLength(0) , MinLength(1)]  
  22. //[StringLength (0)]  
  23. public int? Count { getset; }  
  24.   
  25. //[Column(Order = 4)]  
  26. //[Timestamp]  
  27. //[Required]  
  28. //[MaxLength(150) , MinLength(1)]  
  29. //[StringLength (150)]  
  30. public string Title { getset; }  
  31.   
  32. //[Column(Order = 5)]  
  33. //[Timestamp]  
  34. //[Required]  
  35. //[MaxLength(0) , MinLength(1)]  
  36. //[StringLength (0)]  
  37. public int? OffPercent { getset; }  
  38.   
  39. //[Column(Order = 6)]  
  40. //[Timestamp]  
  41. //[Required]  
  42. //[MaxLength(0) , MinLength(1)]  
  43. //[StringLength (0)]  
  44. public long? CategoryId { getset; }  
  45.   
  46. //[Column(Order = 999999997)]  
  47. //[Timestamp]  
  48. //[Required]  
  49. //[MaxLength(0) , MinLength(1)]  
  50. //[StringLength (0)]  
  51. public bool? IsDeleted { getset; }  
  52.   
  53. //[Column(Order = 999999998)]  
  54. //[Timestamp]  
  55. //[Required]  
  56. //[MaxLength(0) , MinLength(1)]  
  57. //[StringLength (0)]  
  58. public DateTimeOffset? ModifyDate { getset; }  
  59.   
  60.   
  61.       public override string GetSpParams()  
  62.       {  
  63.           var result = "";  
  64.   
  65.             
  66.         result += SqlService.Convert(Id) + ",  ";  
  67.   
  68.         result += SqlService.Convert(Price) + ",  ";  
  69.   
  70.         result += SqlService.Convert(Count) + ",  ";  
  71.   
  72.         result += SqlService.Convert(Title) + ",  ";  
  73.   
  74.         result += SqlService.Convert(OffPercent) + ",  ";  
  75.   
  76.         result += SqlService.Convert(CategoryId) + ",  ";  
  77.   
  78.         result += SqlService.Convert(IsDeleted) + ",  ";  
  79.   
  80.         result += SqlService.Convert(ModifyDate) + " ";  
  81.   
  82.   
  83.           return result;  
  84.       }  
  85.   }  

Topic 5 - Custom Jwt Authorization

 
First find a safe Encrypt Decrypt class with usage of a password,
  1. public class EncryptCode  
  2. {  
  3.     private static readonly string key = "apiTokenRequierd!!**KKKKKKKKKKKJJJJJ";  
  4.     public static string Encrypt(string data)  
  5.     {  
  6.         Encoding unicode = Encoding.Unicode;  
  7.   
  8.         return Convert.ToBase64String(Encrypt(unicode.GetBytes(key), unicode.GetBytes(data)));  
  9.     }  
  10.   
  11.     public static string Decrypt(string data)  
  12.     {  
  13.         Encoding unicode = Encoding.Unicode;  
  14.   
  15.         return unicode.GetString(Encrypt(unicode.GetBytes(key), Convert.FromBase64String(data)));  
  16.     }  
  17.   
  18.     public static byte[] Encrypt(byte[] key, byte[] data)  
  19.     {  
  20.         return EncryptOutput(key, data).ToArray();  
  21.     }  
  22.   
  23.     public static byte[] Decrypt(byte[] key, byte[] data)  
  24.     {  
  25.         return EncryptOutput(key, data).ToArray();  
  26.     }  
  27.   
  28.     private static byte[] EncryptInitalize(byte[] key)  
  29.     {  
  30.         byte[] s = Enumerable.Range(0, 256)  
  31.           .Select(i => (byte)i)  
  32.           .ToArray();  
  33.   
  34.         for (int i = 0, j = 0; i < 256; i++)  
  35.         {  
  36.             j = (j + key[i % key.Length] + s[i]) & 255;  
  37.   
  38.             Swap(s, i, j);  
  39.         }  
  40.   
  41.         return s;  
  42.     }  
  43.   
  44.     private static IEnumerable<byte> EncryptOutput(byte[] key, IEnumerable<byte> data)  
  45.     {  
  46.         byte[] s = EncryptInitalize(key);  
  47.   
  48.         int i = 0;  
  49.         int j = 0;  
  50.   
  51.         return data.Select((b) =>  
  52.         {  
  53.             i = (i + 1) & 255;  
  54.             j = (j + s[i]) & 255;  
  55.   
  56.             Swap(s, i, j);  
  57.   
  58.             return (byte)(b ^ s[(s[i] + s[j]) & 255]);  
  59.         });  
  60.     }  
  61.   
  62.     private static void Swap(byte[] s, int i, int j)  
  63.     {  
  64.         byte c = s[i];  
  65.   
  66.         s[i] = s[j];  
  67.         s[j] = c;  
  68.     }  
  69. }  
Step 1 (Create role permission class)
 
Create a class with the following data structure or save your permissions roles in your database.
  1. public class RoleControllerModel  
  2. {  
  3.     public IEnumerable<RoleActionModel> Actions { getset; }  
  4.   
  5.     public string Name { getset; }  
  6.   
  7.     public string[] Roles { getset; }  
  8. }  
  9.   
  10. public class RoleActionModel  
  11. {  
  12.     public string Name { getset; }  
  13.   
  14.     public string[] Roles { getset; }  
  15. }  
  16.   
  17.   
  18. public static class RolePermission  
  19. {  
  20.     public const string Admin = "admin";  
  21.     public static IEnumerable<RoleControllerModel> RoleList  
  22.     {  
  23.         get  
  24.         {  
  25.             var result = new List<RoleControllerModel>();  
  26.   
  27.             result.Add(new RoleControllerModel()  
  28.             {  
  29.                 Name = "Values",  
  30.                 Roles = new string[] { Admin },  
  31.                 Actions = new List<RoleActionModel>()  
  32.                 {  
  33.                     new RoleActionModel() { Name = "Token", Roles = new string[] { Admin } },  
  34.                       
  35.                 },  
  36.             });  
  37.   
  38.                               
  39.             result.Add(new RoleControllerModel()  
  40.             {  
  41.                 Name = "LoginInfo",  
  42.                 Roles = new string[] { Admin },  
  43.                 Actions = new List<RoleActionModel>()  
  44.                 {  
  45.                     new RoleActionModel() { Name = "DeletedFirstOrDefault", Roles = new string[] { Admin } },  
  46.                     new RoleActionModel() { Name = "FirstOrDefaultById", Roles = new string[] { Admin } },  
  47.                     new RoleActionModel() { Name = "FirstOrDefault", Roles = new string[] { Admin } },  
  48.                     new RoleActionModel() { Name = "GetDeletedList", Roles = new string[] { Admin } },  
  49.                     new RoleActionModel() { Name = "AutoComplete", Roles = new string[] { Admin } },  
  50.                     new RoleActionModel() { Name = "PagedList", Roles = new string[] { Admin } },  
  51.                     new RoleActionModel() { Name = "Restore", Roles = new string[] { Admin } },  
  52.                     new RoleActionModel() { Name = "ToList", Roles = new string[] { Admin } },  
  53.                     new RoleActionModel() { Name = "Update", Roles = new string[] { Admin } },  
  54.                     new RoleActionModel() { Name = "Delete", Roles = new string[] { Admin } },  
  55.                     new RoleActionModel() { Name = "Add", Roles = new string[] { Admin } }  
  56.                 },  
  57.             });  
  58.                   
  59.             result.Add(new RoleControllerModel()  
  60.             {  
  61.                 Name = "UserInfo",  
  62.                 Roles = new string[] { Admin },  
  63.                 Actions = new List<RoleActionModel>()  
  64.                 {  
  65.                     new RoleActionModel() { Name = "DeletedFirstOrDefault", Roles = new string[] { Admin } },  
  66.                     new RoleActionModel() { Name = "FirstOrDefaultById", Roles = new string[] { Admin } },  
  67.                     new RoleActionModel() { Name = "FirstOrDefault", Roles = new string[] { Admin } },  
  68.                     new RoleActionModel() { Name = "GetDeletedList", Roles = new string[] { Admin } },  
  69.                     new RoleActionModel() { Name = "AutoComplete", Roles = new string[] { Admin } },  
  70.                     new RoleActionModel() { Name = "PagedList", Roles = new string[] { Admin } },  
  71.                     new RoleActionModel() { Name = "Restore", Roles = new string[] { Admin } },  
  72.                     new RoleActionModel() { Name = "ToList", Roles = new string[] { Admin } },  
  73.                     new RoleActionModel() { Name = "Update", Roles = new string[] { Admin } },  
  74.                     new RoleActionModel() { Name = "Delete", Roles = new string[] { Admin } },  
  75.                     new RoleActionModel() { Name = "Add", Roles = new string[] { Admin } }  
  76.                 },  
  77.             });  
  78.                   
  79.             result.Add(new RoleControllerModel()  
  80.             {  
  81.                 Name = "Role",  
  82.                 Roles = new string[] { Admin },  
  83.                 Actions = new List<RoleActionModel>()  
  84.                 {  
  85.                     new RoleActionModel() { Name = "DeletedFirstOrDefault", Roles = new string[] { Admin } },  
  86.                     new RoleActionModel() { Name = "FirstOrDefaultById", Roles = new string[] { Admin } },  
  87.                     new RoleActionModel() { Name = "FirstOrDefault", Roles = new string[] { Admin } },  
  88.                     new RoleActionModel() { Name = "GetDeletedList", Roles = new string[] { Admin } },  
  89.                     new RoleActionModel() { Name = "AutoComplete", Roles = new string[] { Admin } },  
  90.                     new RoleActionModel() { Name = "PagedList", Roles = new string[] { Admin } },  
  91.                     new RoleActionModel() { Name = "Restore", Roles = new string[] { Admin } },  
  92.                     new RoleActionModel() { Name = "ToList", Roles = new string[] { Admin } },  
  93.                     new RoleActionModel() { Name = "Update", Roles = new string[] { Admin } },  
  94.                     new RoleActionModel() { Name = "Delete", Roles = new string[] { Admin } },  
  95.                     new RoleActionModel() { Name = "Add", Roles = new string[] { Admin } }  
  96.                 },  
  97.             });  
  98.                   
  99.             result.Add(new RoleControllerModel()  
  100.             {  
  101.                 Name = "Product",  
  102.                 Roles = new string[] { Admin },  
  103.                 Actions = new List<RoleActionModel>()  
  104.                 {  
  105.                     new RoleActionModel() { Name = "DeletedFirstOrDefault", Roles = new string[] { Admin } },  
  106.                     new RoleActionModel() { Name = "FirstOrDefaultById", Roles = new string[] { Admin } },  
  107.                     new RoleActionModel() { Name = "FirstOrDefault", Roles = new string[] { Admin } },  
  108.                     new RoleActionModel() { Name = "GetDeletedList", Roles = new string[] { Admin } },  
  109.                     new RoleActionModel() { Name = "AutoComplete", Roles = new string[] { Admin } },  
  110.                     new RoleActionModel() { Name = "PagedList", Roles = new string[] { Admin } },  
  111.                     new RoleActionModel() { Name = "Restore", Roles = new string[] { Admin } },  
  112.                     new RoleActionModel() { Name = "ToList", Roles = new string[] { Admin } },  
  113.                     new RoleActionModel() { Name = "Update", Roles = new string[] { Admin } },  
  114.                     new RoleActionModel() { Name = "Delete", Roles = new string[] { Admin } },  
  115.                     new RoleActionModel() { Name = "Add", Roles = new string[] { Admin } }  
  116.                 },  
  117.             });  
  118.                   
  119.             result.Add(new RoleControllerModel()  
  120.             {  
  121.                 Name = "Category",  
  122.                 Roles = new string[] { Admin },  
  123.                 Actions = new List<RoleActionModel>()  
  124.                 {  
  125.                     new RoleActionModel() { Name = "DeletedFirstOrDefault", Roles = new string[] { Admin } },  
  126.                     new RoleActionModel() { Name = "FirstOrDefaultById", Roles = new string[] { Admin } },  
  127.                     new RoleActionModel() { Name = "FirstOrDefault", Roles = new string[] { Admin } },  
  128.                     new RoleActionModel() { Name = "GetDeletedList", Roles = new string[] { Admin } },  
  129.                     new RoleActionModel() { Name = "AutoComplete", Roles = new string[] { Admin } },  
  130.                     new RoleActionModel() { Name = "PagedList", Roles = new string[] { Admin } },  
  131.                     new RoleActionModel() { Name = "Restore", Roles = new string[] { Admin } },  
  132.                     new RoleActionModel() { Name = "ToList", Roles = new string[] { Admin } },  
  133.                     new RoleActionModel() { Name = "Update", Roles = new string[] { Admin } },  
  134.                     new RoleActionModel() { Name = "Delete", Roles = new string[] { Admin } },  
  135.                     new RoleActionModel() { Name = "Add", Roles = new string[] { Admin } }  
  136.                 },  
  137.             });  
  138.                   
  139.             result.Add(new RoleControllerModel()  
  140.             {  
  141.                 Name = "TempCard",  
  142.                 Roles = new string[] { Admin },  
  143.                 Actions = new List<RoleActionModel>()  
  144.                 {  
  145.                     new RoleActionModel() { Name = "DeletedFirstOrDefault", Roles = new string[] { Admin } },  
  146.                     new RoleActionModel() { Name = "FirstOrDefaultById", Roles = new string[] { Admin } },  
  147.                     new RoleActionModel() { Name = "FirstOrDefault", Roles = new string[] { Admin } },  
  148.                     new RoleActionModel() { Name = "GetDeletedList", Roles = new string[] { Admin } },  
  149.                     new RoleActionModel() { Name = "AutoComplete", Roles = new string[] { Admin } },  
  150.                     new RoleActionModel() { Name = "PagedList", Roles = new string[] { Admin } },  
  151.                     new RoleActionModel() { Name = "Restore", Roles = new string[] { Admin } },  
  152.                     new RoleActionModel() { Name = "ToList", Roles = new string[] { Admin } },  
  153.                     new RoleActionModel() { Name = "Update", Roles = new string[] { Admin } },  
  154.                     new RoleActionModel() { Name = "Delete", Roles = new string[] { Admin } },  
  155.                     new RoleActionModel() { Name = "Add", Roles = new string[] { Admin } }  
  156.                 },  
  157.             });  
  158.                   
  159.             result.Add(new RoleControllerModel()  
  160.             {  
  161.                 Name = "TempCardItem",  
  162.                 Roles = new string[] { Admin },  
  163.                 Actions = new List<RoleActionModel>()  
  164.                 {  
  165.                     new RoleActionModel() { Name = "DeletedFirstOrDefault", Roles = new string[] { Admin } },  
  166.                     new RoleActionModel() { Name = "FirstOrDefaultById", Roles = new string[] { Admin } },  
  167.                     new RoleActionModel() { Name = "FirstOrDefault", Roles = new string[] { Admin } },  
  168.                     new RoleActionModel() { Name = "GetDeletedList", Roles = new string[] { Admin } },  
  169.                     new RoleActionModel() { Name = "AutoComplete", Roles = new string[] { Admin } },  
  170.                     new RoleActionModel() { Name = "PagedList", Roles = new string[] { Admin } },  
  171.                     new RoleActionModel() { Name = "Restore", Roles = new string[] { Admin } },  
  172.                     new RoleActionModel() { Name = "ToList", Roles = new string[] { Admin } },  
  173.                     new RoleActionModel() { Name = "Update", Roles = new string[] { Admin } },  
  174.                     new RoleActionModel() { Name = "Delete", Roles = new string[] { Admin } },  
  175.                     new RoleActionModel() { Name = "Add", Roles = new string[] { Admin } }  
  176.                 },  
  177.             });  
  178.                   
  179.             result.Add(new RoleControllerModel()  
  180.             {  
  181.                 Name = "ShippingType",  
  182.                 Roles = new string[] { Admin },  
  183.                 Actions = new List<RoleActionModel>()  
  184.                 {  
  185.                     new RoleActionModel() { Name = "DeletedFirstOrDefault", Roles = new string[] { Admin } },  
  186.                     new RoleActionModel() { Name = "FirstOrDefaultById", Roles = new string[] { Admin } },  
  187.                     new RoleActionModel() { Name = "FirstOrDefault", Roles = new string[] { Admin } },  
  188.                     new RoleActionModel() { Name = "GetDeletedList", Roles = new string[] { Admin } },  
  189.                     new RoleActionModel() { Name = "AutoComplete", Roles = new string[] { Admin } },  
  190.                     new RoleActionModel() { Name = "PagedList", Roles = new string[] { Admin } },  
  191.                     new RoleActionModel() { Name = "Restore", Roles = new string[] { Admin } },  
  192.                     new RoleActionModel() { Name = "ToList", Roles = new string[] { Admin } },  
  193.                     new RoleActionModel() { Name = "Update", Roles = new string[] { Admin } },  
  194.                     new RoleActionModel() { Name = "Delete", Roles = new string[] { Admin } },  
  195.                     new RoleActionModel() { Name = "Add", Roles = new string[] { Admin } }  
  196.                 },  
  197.             });  
  198.                   
  199.             result.Add(new RoleControllerModel()  
  200.             {  
  201.                 Name = "Factor",  
  202.                 Roles = new string[] { Admin },  
  203.                 Actions = new List<RoleActionModel>()  
  204.                 {  
  205.                     new RoleActionModel() { Name = "DeletedFirstOrDefault", Roles = new string[] { Admin } },  
  206.                     new RoleActionModel() { Name = "FirstOrDefaultById", Roles = new string[] { Admin } },  
  207.                     new RoleActionModel() { Name = "FirstOrDefault", Roles = new string[] { Admin } },  
  208.                     new RoleActionModel() { Name = "GetDeletedList", Roles = new string[] { Admin } },  
  209.                     new RoleActionModel() { Name = "AutoComplete", Roles = new string[] { Admin } },  
  210.                     new RoleActionModel() { Name = "PagedList", Roles = new string[] { Admin } },  
  211.                     new RoleActionModel() { Name = "Restore", Roles = new string[] { Admin } },  
  212.                     new RoleActionModel() { Name = "ToList", Roles = new string[] { Admin } },  
  213.                     new RoleActionModel() { Name = "Update", Roles = new string[] { Admin } },  
  214.                     new RoleActionModel() { Name = "Delete", Roles = new string[] { Admin } },  
  215.                     new RoleActionModel() { Name = "Add", Roles = new string[] { Admin } }  
  216.                 },  
  217.             });  
  218.                   
  219.             result.Add(new RoleControllerModel()  
  220.             {  
  221.                 Name = "FactorInfo",  
  222.                 Roles = new string[] { Admin },  
  223.                 Actions = new List<RoleActionModel>()  
  224.                 {  
  225.                     new RoleActionModel() { Name = "DeletedFirstOrDefault", Roles = new string[] { Admin } },  
  226.                     new RoleActionModel() { Name = "FirstOrDefaultById", Roles = new string[] { Admin } },  
  227.                     new RoleActionModel() { Name = "FirstOrDefault", Roles = new string[] { Admin } },  
  228.                     new RoleActionModel() { Name = "GetDeletedList", Roles = new string[] { Admin } },  
  229.                     new RoleActionModel() { Name = "AutoComplete", Roles = new string[] { Admin } },  
  230.                     new RoleActionModel() { Name = "PagedList", Roles = new string[] { Admin } },  
  231.                     new RoleActionModel() { Name = "Restore", Roles = new string[] { Admin } },  
  232.                     new RoleActionModel() { Name = "ToList", Roles = new string[] { Admin } },  
  233.                     new RoleActionModel() { Name = "Update", Roles = new string[] { Admin } },  
  234.                     new RoleActionModel() { Name = "Delete", Roles = new string[] { Admin } },  
  235.                     new RoleActionModel() { Name = "Add", Roles = new string[] { Admin } }  
  236.                 },  
  237.             });  
  238.   
  239.   
  240.             return result;  
  241.         }  
  242.     }  
  243. }  
Step 2 (Create jwtAuth class)
  • Create a class inherited from AuthorizationFilterAttribute
  • Select parameter in bearer scheme, from request headers authorization
  • Use Decryption method in Encryption class
  • Get request action method to use detect permission is in right action or not
Now split decrypted string by your role in token creation time (for example I did split my token by | in 3 parts, then I get the expire date, role and user id from the split parts)
  1. public class JwtAuth : AuthorizationFilterAttribute  
  2. {  
  3.     public override void OnAuthorization(HttpActionContext actionContext)  
  4.     {  
  5.         try  
  6.         {  
  7.             if (actionContext.Request.Headers.Authorization != null)  
  8.             {  
  9.                 if(actionContext.Request.Headers.Authorization.Scheme == "Bearer")  
  10.                 {  
  11.                     var authToken = actionContext.Request.Headers.Authorization.Parameter;  
  12.   
  13.                     var decoAuthToken = EncryptCode.Decrypt(authToken);  
  14.   
  15.                     //split by colon : and store in variable    
  16.                     //var UserNameAndPassword = decoAuthToken.Split(':');  
  17.                     //Passing to a function for authorization    
  18.   
  19.                     //var controller = (string)actionContext.RequestContext.RouteData.Values["controller"];  
  20.                     var action = (HttpActionDescriptor[])actionContext.RequestContext.RouteData.Route.DataTokens["actions"];  
  21.                     if (action.Length > 0 && action[0].ControllerDescriptor != null)  
  22.                     {  
  23.                         var rightPlace = action[0];  
  24.                         var controllerName = rightPlace.ControllerDescriptor.ControllerName;  
  25.                         var actionName = rightPlace.ActionName;  
  26.                         var infoes = decoAuthToken.Split('|');  
  27.                         if(infoes != null && infoes.Length == 3)  
  28.                         {  
  29.                             DateTime expDate = DateTime.Now.AddDays(-30);  
  30.                             bool haveDate = false;  
  31.                             haveDate = DateTime.TryParse(infoes[2], out expDate);  
  32.                             if (infoes[0].StartsWith("userId=") && haveDate && (expDate > DateTime.Now))  
  33.                             {  
  34.                                 if(RolePermission.RoleList.Any(a=> a.Name == controllerName && a.Roles.Any(r=> r == infoes[1]) && a.Actions.Any(ac=> ac.Name == actionName && ac.Roles.Any(ro=> ro == infoes[1]))))  
  35.                                 {  
  36.                                     var genericIdentity = new GenericIdentity(decoAuthToken);  
  37.                                     var princ = new GenericPrincipal(genericIdentity, null);  
  38.                                     Thread.CurrentPrincipal = princ;  
  39.                                 }  
  40.                                       
  41.                                 else  
  42.                                     actionContext.Response = actionContext.Request.CreateResponse(HttpStatusCode.Unauthorized);  
  43.                             }  
  44.                             else  
  45.                                 actionContext.Response = actionContext.Request.CreateResponse(HttpStatusCode.Unauthorized);  
  46.                         }  
  47.   
  48.                         else  
  49.                             actionContext.Response = actionContext.Request.CreateResponse(HttpStatusCode.Unauthorized);  
  50.                     }  
  51.                     else  
  52.                         actionContext.Response = actionContext.Request.CreateResponse(HttpStatusCode.Unauthorized);  
  53.                 }  
  54.                 else  
  55.                     actionContext.Response = actionContext.Request.CreateResponse(HttpStatusCode.Unauthorized);  
  56.   
  57.             }  
  58.             else  
  59.                 actionContext.Response = actionContext.Request.CreateResponse(HttpStatusCode.Unauthorized);  
  60.         }  
  61.         catch (Exception ex)  
  62.         {  
  63.             actionContext.Response = actionContext.Request.CreateResponse(HttpStatusCode.Unauthorized);  
  64.             //ex.Message.ToString();  
  65.         }  
  66.     }  
  67.   
  68. }  
Step 3 (Create jwt Identity class)
 
Use a model and a static class to split token information
  1. public class JwtIdentity  
  2. {  
  3.     public static JwtUserInfo UserInfo(IIdentity identity)  
  4.     {  
  5.         if(identity.IsAuthenticated)  
  6.         {  
  7.             try  
  8.             {  
  9.                 var infoes = identity.Name.Split('|');  
  10.                 DateTime expDate = DateTime.Now.AddDays(-30);  
  11.                 bool haveDate = false;  
  12.                 haveDate = DateTime.TryParse(infoes[2], out expDate);  
  13.                 return new JwtUserInfo()  
  14.                 {  
  15.                     ExpireDate = expDate,  
  16.                     Id = long.Parse(infoes[0].Replace("userId=""")),  
  17.                     Role = infoes[1],  
  18.                     TokenString = identity.Name,  
  19.                     IsAuthenticated = true,  
  20.                     IsInvalidToken = false,  
  21.                 };  
  22.             }  
  23.             catch (Exception)  
  24.             {  
  25.                 return new JwtUserInfo() { IsInvalidToken = true, IsAuthenticated = false, TokenString = identity.Name, Id = -999999999999999999, ExpireDate = DateTime.Now.AddSeconds(-1), Role = "" };  
  26.             }  
  27.         }  
  28.   
  29.         return new JwtUserInfo() { IsInvalidToken = false, IsAuthenticated = false, TokenString = identity.Name, Id = -999999999999999999, ExpireDate = DateTime.Now.AddSeconds(-1), Role = "" };  
  30.     }  
  31. }  
  32.   
  33. public class JwtUserInfo  
  34. {  
  35.     public bool IsAuthenticated { getset; }  
  36.   
  37.     public bool IsInvalidToken { getset; }  
  38.   
  39.     public long Id { getset; }  
  40.   
  41.     public string Role { getset; }  
  42.   
  43.     public DateTime ExpireDate { getset; }  
  44.   
  45.     public string TokenString { getset; }  
  46. }   

Topic 6 - Donate

 
SQL Server REST Scaffolding
 
SSRS is a code generation framework for ASP.NET Web API based on SQL Server JSON result and c# repository pattern manager.
 
The best feature that can be used for this scaffolding is the removal of Entity Framework and the removal of Auto Mapper in the development of the REST projects.
 
In my projects, I always implement the project scaffold with SQL in this way, and then I change the data based on more complex conditions or update and edit operations based on the parameters of the final design. With JSON SQL, I don't need to create output models for slow methods like Auto Mapper and EF.
 
I shared my code generator demo version. Create a REST for your database in a short time by designing the database you want according to the intended framework in SSRS user interface. 10 SQL table available for free code generation every 10 days.
 
Download sample output solution or download SSRS exe project and start creating your REST in a few minutes.
 
SQL Server Auto Generate Output Results:
  1. Generate script for designed tables and relations
  2. SQL Server Stored Procedures with JSON results (10 SP for each table)
  3. Auto-generate conditions and parameters in all store procedures
  4. All search parameters with multi-search types for each property in all select stored procedures for each table
  5. All search conditions by data types for all search parameters
  6. Include Insert, Update, Delete and Restore SP for each table
  7. Include JSON object in one-one relations
  8. Include Select Top (30) JSON array for one-many relations
  9. Include Select Count (*) INT for one-many relations in Top (n) select search stored procedures
  10. Include Select Offset Fetch for pagination search type stored procedures
  11. Select Top (1) store procedures with all parameters or just by ID
And…
 
Visual Studio 2015 ASP.NET Web API SolutionOutput Results:
  1. Interface classes to integrate any type of store procedure JSON output into controllers using by repository pattern
  2. Generate all API controllers inherited from base API controller in the project (asynchronous methods)
  3. Generate SQL Server repository manager class used in all controllers
  4. Generate all repository classes for SQL manager and their all methods for each table (asynchronous methods)
  5. Generate 5 model type for each table, like Insert, Update, Filter, Search and Pagination
  6. Include jwtAuth for controllers
  7. Generate permission class for jwtAuth
And …
 

SSRS Framework

 
To integrate SQL and ASP.Net outputs, there are defaults in the user interface that you can use to scaffold your REST. Check if your requirements are within this framework
Tables,
 
Default tables (Role, LoginInfo and UserInfo)
 
There are three default tables for creating a user, and a default SP for the user's login is available in the project according to these tables. Your other tables can have foreign keys from these tables.
 
Default properties (Id, IsDeleted, SubmitDate, ModifyDate)
 
This property are always included in any table and have interfaces in c# code generated and models
  1. Id: identity for each table with BIGINT data type
  2. IsDeleted: bit data type to use delete or restore each row
  3. SubmitDate: datetimeoffset data type when a row is created managed in c#
  4. MofiyDate: datetimeoffset data type when a row is updated managed in c#

Store Procedures

 
Auto-generated store procedures with different return results
 
Names and returns
  1. ToList: relational values top count select order by desc JSON array (multiple filter and conditions)
  2. PageList: relational values offset fetch order by desc JSON array (multiple filter and conditions)
  3. AutoComplete: no relation values top count list order by desc JSON array used for auto-complete form searches (multiple filter and conditions)
  4. FirstOrDefault: relational top one select order by desc JSON object (multiple filter and conditions)
  5. FirstOrDefaultById: relational top one select order by desc JSON object just id condition
  6. Count: Select Count(*) (multiple filter and conditions)
  7. AddNewData: Insert and return inserted data as a JSON object
  8. UpdateData: update and return relational select for the updated row as a JSON object
  9. Delete: update IsDeleted property set true and return deleted row as a JSON object
  10. Restore: update IsDeleted property set false and return restored row as a JSON array

C# Repository Pattern

 
Generated methods and models are working with repository based on JSON results. Models generated to create SQL repositories and create interfaces based on SP names.
 
Create a project with SSRS.exe
  1. Open a new project in the file menu
  2. Open add table tab and enter your table name or select schema to create tables
  3. Add property or FK to your table and save table data. After saving a table Fk of saved auto-created in the select property list box and you can use the FK of the created table
  4. Enter a project name and click start build project
  5. Open projects in the account menu and wait for the download link
  6. Download link is available for 1 day and 5 download times

Summary

 
In this article, I discussed how we can create an ASP.Net web API with the usage of SQL JSON and integrate it with a repository pattern for usage in controllers. You can use my SSRS.exe to create your web API project with this article topics without writing any code and get over 10K T-SQL Lines Of Code. In fact, the example project is auto-generated from SSRS.exe too.