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:

Product = {"Id": 1, "Title": "Product 1"}

Objects with one-one relation or drill downs:

Product = {
    "Id": 1,
    "Title": "Product 1",
    "Category": {
        "Id": 2,
        "Title": "Sub Category 1",
        "ParentCategory": {
            "Id": 1,
            "Title": "Parent Category 1"
        }
    }
}

Objects with one-many relations array or drill downs:

TempCard = {
    "Id": 1,
    "UserInfoId": 1,
    "FactorId": 1,
    " TempCardItems": [{
        "Id": 1,
        "Count": 13,
        "Product": {
            "Id": 1,
            "Title": "Product 1",
            "Category": {
                "Id": 2,
                "Title": "Sub Category 1",
                "ParentCategory": {
                    "Id": 1,
                    "Title": "Parent Category 1"
                }
            }
        }
    }]
}

Arrayswith one-one or one-many relations array and drill downs,

[{
    "Id": 1,
    "Title": "Product 1",
    "Category": {
        "Id": 2,
        "Title": "Sub Category 1",
        "ParentCategory": {
            "Id": 1,
            "Title": "Parent Category 1"
        }
    }
}, {
    "Id": 2,
    "Title": "Product 2",
    "Category": {
        "Id": 2,
        "Title": "Sub Category 1",
        "ParentCategory": {
            "Id": 1,
            "Title": "Parent Category 1"
        }
    }
}]

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,

CREATE PROCEDURE [Schema].[SP_Name]
  @Parameter DATATYPE = optional
AS
  WITH dataforjson AS ( (
  --|| your top(1) selects goes here ||--
  ) )
  SELECT *
  FROM   dataforjson FOR json auto,
         include_null_values,
         without_array_wrappergo

Select Top (n) sp structure,

CREATE PROCEDURE [Schema].[SP_Name]
  @Parameter DATATYPE = optional
AS
  WITH dataforjson AS ( (
  --|| your top(n) selects goes here ||--
  ) )
  SELECT *
  FROM   dataforjson FOR json auto,
         include_null_valuesgo

Relational store procedure FirstOrDefaultById example,

CREATE PROCEDURE [SpJson].[Product_FirstOrDefaultById]
  @Id BIGINT = NULL
AS
  WITH dataforjson AS (
  (
         SELECT TOP (1)
                [product].[Id] ,
                [product].[Title] ,
                [product].[CategoryId] ,
                --|| Start One level parent drill down select one-one relation object ||--
                (json_query (
                (
                       SELECT TOP (1)
                              [product_category].[Id] ,
                              [product_category].[Name],
                              --|| Start Two level prent drill down select one-one relation object ||--
                              (json_query (
                              (
                                     SELECT TOP (1)
                                            [parent_product_category].[Id] ,
                                            [parent_product_category].[Name]
                                     FROM   [Shop].[Category] AS parent_product_category
                                     WHERE  parent_product_category.[Id] = [product_category].[Id] FOR json path,
                                            include_null_values,
                                            without_array_wrapper ))) AS [Parent_Category]
                              --|| End Two level prent drill down select one-one relation object ||--
                       FROM   [Shop].[Category] AS product_category
                       WHERE  product_category.[Id] = product.[CategoryId] FOR json path,
                              include_null_values,
                              without_array_wrapper ))) AS [Category],
                --|| End One level prent drill down select one-one relation object ||--
                --|| Start One level child drill down select one-many relation array ||--
                ,
                (json_query (
                (
                         SELECT TOP (30)
                                  [product_tempcarditem].[Id] ,
                                  [product_tempcarditem].[Count] ,
                                  [product_tempcarditem].[ProductId] ,
                                  [product_tempcarditem].[TempCardId]
                         FROM     [Shop].[TempCardItem] AS [product_tempcarditem]
                         WHERE    [product_tempcarditem].[Id] = [product].[ProductId]
                         ORDER BY [product_tempcarditem].id DESC FOR json path,
                                  include_null_values) )) AS [TempCardItem_Id]
                --|| End One level child drill down select one-many relation array ||--
         FROM   [Shop].[Product] [product]
         WHERE  [product].[Id] = @Id ) )
  SELECT *
  FROM   dataforjson FOR json auto,
         include_null_values,
         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.

public interface IBaseSpName
{
    string EntityName { get; set; }
    string Count { get; }
    string ToList { get; }
    string PagedList { get; }
    string GetDeletedList { get; }
    string FirstOrDefault { get; }
    string FirstOrDefaultById { get; }
    string DeletedFirstOrDefault { get; }
    string AutoComplete { get; }
    string Add { get; }
    string Update { get; }
    string Delete { get; }
    string Restore { get; }
}

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.

public interface IBaseSqlSpParam
{
    string GetSpParams();
    bool? IsDeleted { get; set; }
    DateTimeOffset? SubmitDate { get; set; }
    DateTimeOffset? ModifyDate { get; set; }

}

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.

public class BaseSqlFilterParam : IBaseSqlSpParam
{
    public virtual bool? IsDeleted { get; set; }
    public virtual DateTimeOffset? SubmitDate { get; set; }
    public virtual DateTimeOffset? ModifyDate { get; set; }
    public virtual string GetSpParams()
    {
        return "";
    }
}

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.

public class BaseSqlListFilterParam : IBaseSqlSpParam
{
    public virtual bool? IsDeleted { get; set; } = false;
    public virtual int? TopCount { get; set; } = 100;
    public virtual DateTimeOffset? SubmitDate { get; set; }
    public virtual DateTimeOffset? ModifyDate { get; set; }
    public virtual string GetSpParams()
    {
        return "";
    }
}

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.

public class BaseSqlPaginationFilterParam : IBaseSqlSpParam
{
    public virtual bool? IsDeleted { get; set; } = false;
    public virtual int? Skip { get; set; } = 0;
    public virtual int? Size { get; set; } = 10;
    public virtual DateTimeOffset? SubmitDate { get; set; }
    public virtual DateTimeOffset? ModifyDate { get; set; }
    public virtual string GetSpParams()
    {
        return "";
    }
}

BaseSqlAddParam

 

Create a base model for insert store procedures like this.

public class BaseSqlAddParam : IBaseSqlSpParam
{
    public virtual bool? IsDeleted { get; set; } = false;
    public virtual DateTimeOffset? SubmitDate { get; set; }
    public virtual DateTimeOffset? ModifyDate { get; set; }
    public virtual string GetSpParams()
    {
        return "";
    }
}

BaseSqlUpdateParam

 

Create a base model for updating store procedures like this:

public class BaseSqlUpdateParam : IBaseSqlSpParam
{
    public virtual bool? IsDeleted { get; set; } = false;
    public virtual DateTimeOffset? SubmitDate { get; set; }
    public virtual DateTimeOffset? ModifyDate { get; set; }

    public virtual string GetSpParams()
    {
        return "";
    }
}

BaseSpName

 

Important model class for create and using store procedures names:

public class BaseSpName : IBaseSpName
    {
        public virtual string EntityName { get; set; }
        public virtual string Count { get { return $"[SpJson].[{EntityName}_Count] "; } }
        public virtual string ToList { get { return $"[SpJson].[{EntityName}_ToList] "; } }
        public virtual string PagedList { get { return $"[SpJson].[{EntityName}_PageList]"; } }
        public virtual string GetDeletedList { get { return $"[SpJson].[{EntityName}_ToList]"; } }
        public virtual string FirstOrDefault { get { return $"[SpJson].[{EntityName}_FirstOrDefault]"; } }
        public virtual string FirstOrDefaultById { get { return $"[SpJson].[{EntityName}_FirstOrDefaultById]"; } }
        public virtual string DeletedFirstOrDefault { get { return $"[SpJson].[{EntityName}_FirstOrDefault]"; } }
        public virtual string AutoComplete { get { return $"[SpJson].[{EntityName}_AutoComplete]"; } }
        public virtual string Add { get { return $"[SpJson].[{EntityName}_AddNewData]"; } }
        public virtual string Update { get { return $"[SpJson].[{EntityName}_UpdateData]"; } }
        public virtual string Delete { get { return $"[SpJson].[{EntityName}_DeleteData]"; } }
        public virtual string Restore { get { return $"[SpJson].[{EntityName}_RestoreData]"; } }


    }

Step 3

 

(Create static entity name class),

public static class EntityNames
{
   public static string LoginInfo { get { return "LoginInfo"; } }
   public static string UserInfo { get { return "UserInfo"; } }
   public static string Role { get { return "Role"; } }
   public static string Product { get { return "Product"; } }
   public static string Category { get { return "Category"; } }
   public static string TempCard { get { return "TempCard"; } }
   public static string TempCardItem { get { return "TempCardItem"; } }
   public static string ShippingType { get { return "ShippingType"; } }
   public static string Factor { get { return "Factor"; } }
   public static string FactorInfo { get { return "FactorInfo"; } }
}

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:

public class SqlService
{
    public static string ConnectionString
    {
        get
        {
            return ConfigurationManager.ConnectionStrings["DataBase"].ConnectionString;
        }
    }

    public async static Task<string> GetDataResult(string query)
    {
        try
        {
            var queryWithForJson = query;
            var conn = new SqlConnection(SqlService.ConnectionString);
            var cmd = new SqlCommand(queryWithForJson, conn);
            await conn.OpenAsync();
            var jsonResult = new StringBuilder();
            var reader = await cmd.ExecuteReaderAsync();
            if (!reader.HasRows)
            {
                jsonResult.Append("null");
            }
            else
            {
                while (reader.Read())
                {
                    jsonResult.Append(reader.GetValue(0).ToString());
                }
            }
            conn.Close();
            return ReturnData(jsonResult.ToString());
        }
        catch (Exception e)
        {
            return GeneralGetDataError(e.Message);
        }
    }

    public static string ReturnData(string json)
    {
        return "{\"Message\":\"DONE\",\"Status\":true,\"Data\":" + json + "}";
    }

    public static string GeneralGetDataError(string message)
    {
        return "{\"Message\":\"" + message + "\",\"Status\":false}";
    }

    public static string Convert(long? value)
    {
        var result = $"{(value.HasValue ? value.ToString() : "NULL")}";

        return result;
    }

    public static string Convert(byte[] value)
    {
        var result = "NULL";

        return result;
    }

    public static string Convert(bool? value)
    {
        var result = $"{(value.HasValue ? value.Value ? (1).ToString() : (0).ToString() : "NULL")}";

        return result;
    }

    public static string Convert(string value)
    {
        var result = $"{(value != null && value.Length > 0 ? "'" + value.ToString() + "'" : "NULL")}";

        return result;
    }

    public static string Convert(DateTime? value)
    {
        var result = $"{(value.HasValue ? "'" + value.Value.ToLongDateString().Replace("/", "-") + "'" : "NULL")}";

        return result;
    }

    public static string Convert(DateTimeOffset? value)
    {
        var result = $"{(value.HasValue ? "'" + value.Value.Date.ToString("o").Replace("/","-") + "'" : "NULL")}";

        return result;
    }

    public static string Convert(decimal? value)
    {
        var result = $"{(value.HasValue ? value.ToString() : "NULL")}"; ;

        return result;
    }

    public static string Convert(double? value)
    {
        var result = $"{(value.HasValue ? value.ToString() : "NULL")}";

        return result;
    }

    public static string Convert(DbGeography value)
    {
        var result = "NULL";

        return result;
    }

    public static string Convert(DbGeometry value)
    {
        var result = "NULL";

        return result;
    }

    public static string Convert(int? value)
    {
        var result = $"{(value.HasValue ? value.ToString() : "NULL")}";

        return result;
    }

    public static string Convert(float? value)
    {
        var result = $"{(value.HasValue ? value.ToString() : "NULL")}";

        return result;
    }

    public static string Convert(short? value)
    {
        var result = $"{(value.HasValue ? value.ToString() : "NULL")}";

        return result;
    }

    public static string Convert(TimeSpan? value)
    {
        var result = $"{(value.HasValue ? "'" + value.Value.ToString() + "'" : "NULL")}";

        return result;
    }

    public static string Convert(byte? value)
    {
        var result = "NULL";

        return result;
    }
}

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

public class BaseSqlData
<
    BaseSqlFilterParam,
    BaseSqlListFilterParam,
    BaseSqlPaginationFilterParam,
    BaseSqlAddParam,
    BaseSqlUpdateParam,
    BaseSpName
>
: IDisposable
where BaseSqlFilterParam : IBaseSqlSpParam
where BaseSqlListFilterParam : class, IBaseSqlSpParam
where BaseSqlPaginationFilterParam : class, IBaseSqlSpParam
where BaseSqlAddParam : class, IBaseSqlSpParam
where BaseSqlUpdateParam : class, IBaseSqlSpParam
where BaseSpName : class, IBaseSpName
{
    protected BaseSpName _spNames;

    public BaseSqlData(string entityName,BaseSpName spNames)
    {
        _spNames = spNames;
        _spNames.EntityName = entityName;
    }

    public virtual async Task<string> Count(BaseSqlFilterParam filter)
    {
        filter.IsDeleted = false;
        var result = await SqlService.GetDataResult(_spNames.Count + " " + filter.GetSpParams());
        return result;
    }

    public virtual async Task<string> ToList(BaseSqlListFilterParam filter)
    {
        filter.IsDeleted = false;
        var result = await SqlService.GetDataResult(_spNames.ToList + " " + filter.GetSpParams());
        return result;
    }

    public virtual async Task<string> PagedList(BaseSqlPaginationFilterParam filter)
    {
        filter.IsDeleted = false;
        var result = await SqlService.GetDataResult(_spNames.PagedList + " " + filter.GetSpParams());
        return result;
    }

    public virtual async Task<string> GetDeletedList(BaseSqlListFilterParam filter)
    {
        filter.IsDeleted = true;
        var result = await SqlService.GetDataResult(_spNames.GetDeletedList + " " + filter.GetSpParams());
        return result;
    }

    public virtual async Task<string> FirstOrDefault(BaseSqlFilterParam filter)
    {
        filter.IsDeleted = false;
        var result = await SqlService.GetDataResult(_spNames.FirstOrDefault + " " + filter.GetSpParams());
        return result;
    }

    public virtual async Task<string> FirstOrDefaultById(long? id)
    {
        var result = await SqlService.GetDataResult(_spNames.FirstOrDefaultById + " " + id);
        return result;
    }

    public virtual async Task<string> DeletedFirstOrDefault(BaseSqlFilterParam filter)
    {
        filter.IsDeleted = true;
        var result = await SqlService.GetDataResult(_spNames.DeletedFirstOrDefault + " " + filter.GetSpParams());
        return result;
    }

    public virtual async Task<string> AutoComplete(BaseSqlListFilterParam filter)
    {
        filter.IsDeleted = false;
        var result = await SqlService.GetDataResult(_spNames.AutoComplete + " " + filter.GetSpParams());
        return result;
    }

    public virtual async Task<string> Add(BaseSqlAddParam item)
    {
        item.IsDeleted = false;
        item.SubmitDate = DateTimeOffset.Now;
        item.ModifyDate = DateTimeOffset.Now;
        var result = await SqlService.GetDataResult(_spNames.Add + " " + item.GetSpParams());
        return result;
    }

    public virtual async Task<string> Update(BaseSqlUpdateParam item)
    {
        item.IsDeleted = false;
        item.ModifyDate = DateTimeOffset.Now;
        var result = await SqlService.GetDataResult(_spNames.Update + " " + item.GetSpParams());
        return result;
    }

    public virtual async Task<string> Delete(long? id)
    {
        var result = await SqlService.GetDataResult(_spNames.Delete + " " + id);
        return result;
    }

    public virtual async Task<string> Restore(long? id)
    {
        var result = await SqlService.GetDataResult(_spNames.Restore + " " + id);
        return result;
    }

    public void Dispose()
    {
        Dispose(true);
        GC.SuppressFinalize(true);
    }

    private void Dispose(bool disposing)
    {
        if (disposing)
        {
            //to do
        }
    }
}

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. 

 public class ProductSqlData : BaseSqlData<ProductFilter, ProductListFilter, ProductPaginationFilter, ProductAdd, ProductUpdate, BaseSpName>
 {
     public ProductSqlData(string entityName, BaseSpName spNames) : base(entityName, spNames)
     {
         _spNames.EntityName = entityName;
     }

     public override async Task<string> Count(ProductFilter filter)
     {
         filter.IsDeleted = false;
         var result = await SqlService.GetDataResult(_spNames.Count + " " + filter.GetSpParams());
         return result;
     }

     public override async Task<string> ToList(ProductListFilter filter)
     {
         filter.IsDeleted = false;
         var result = await SqlService.GetDataResult(_spNames.ToList + " " + filter.GetSpParams());
         return result;
     }

     public override async Task<string> PagedList(ProductPaginationFilter filter)
     {
         filter.IsDeleted = false;
         var result = await SqlService.GetDataResult(_spNames.PagedList + " " + filter.GetSpParams());
         return result;
     }

     public override async Task<string> GetDeletedList(ProductListFilter filter)
     {
         filter.IsDeleted = true;
         var result = await SqlService.GetDataResult(_spNames.GetDeletedList + " " + filter.GetSpParams());
         return result;
     }

     public override async Task<string> FirstOrDefault(ProductFilter filter)
     {
         filter.IsDeleted = false;
         var result = await SqlService.GetDataResult(_spNames.FirstOrDefault + " " + filter.GetSpParams());
         return result;
     }

     public override async Task<string> FirstOrDefaultById(long? id)
     {
         var result = await SqlService.GetDataResult(_spNames.FirstOrDefaultById + " " + id);
         return result;
     }

     public override async Task<string> DeletedFirstOrDefault(ProductFilter filter)
     {
         filter.IsDeleted = true;
         var result = await SqlService.GetDataResult(_spNames.DeletedFirstOrDefault + " " + filter.GetSpParams());
         return result;
     }

     public override async Task<string> AutoComplete(ProductListFilter filter)
     {
         filter.IsDeleted = false;
         var result = await SqlService.GetDataResult(_spNames.AutoComplete + " " + filter.GetSpParams());
         return result;
     }

     public override async Task<string> Add(ProductAdd item)
     {
         item.IsDeleted = false;
         item.SubmitDate = DateTimeOffset.Now;
         item.ModifyDate = DateTimeOffset.Now;
         var result = await SqlService.GetDataResult(_spNames.Add + " " + item.GetSpParams());
         return result;
     }

     public override async Task<string> Update(ProductUpdate item)
     {
         item.IsDeleted = false;
         item.ModifyDate = DateTimeOffset.Now;
         var result = await SqlService.GetDataResult(_spNames.Update + " " + item.GetSpParams());
         return result;
     }

     public override async Task<string> Delete(long? id)
     {
         var result = await SqlService.GetDataResult(_spNames.Delete + " " + id);
         return result;
     }

     public override async Task<string> Restore(long? id)
     {
         var result = await SqlService.GetDataResult(_spNames.Restore + " " + id);
         return result;
     }
 }

Step 7 (Create repository manager class)

 

Store procedure names and entity name comes from constractor of repository manager class.

  public class SqlDataManager
  {
      protected string _entityName = "";
      protected BaseSpName _spName;
      public SqlDataManager(string entityName, BaseSpName spName)
      {
          _spName = spName;
          _entityName = entityName;
      }


private BaseSqlData<LoginInfoFilter, LoginInfoListFilter, LoginInfoPaginationFilter, LoginInfoAdd, LoginInfoUpdate, BaseSpName> _LoginInfo;

      public BaseSqlData<LoginInfoFilter, LoginInfoListFilter, LoginInfoPaginationFilter, LoginInfoAdd, LoginInfoUpdate, BaseSpName> LoginInfo
      {
          get { if (_LoginInfo == null)  _LoginInfo =  new LoginInfoSqlData(_entityName, _spName); return _LoginInfo; }
      }

private BaseSqlData<UserInfoFilter, UserInfoListFilter, UserInfoPaginationFilter, UserInfoAdd, UserInfoUpdate, BaseSpName> _UserInfo;

      public BaseSqlData<UserInfoFilter, UserInfoListFilter, UserInfoPaginationFilter, UserInfoAdd, UserInfoUpdate, BaseSpName> UserInfo
      {
          get { if (_UserInfo == null)  _UserInfo =  new UserInfoSqlData(_entityName, _spName); return _UserInfo; }
      }

private BaseSqlData<RoleFilter, RoleListFilter, RolePaginationFilter, RoleAdd, RoleUpdate, BaseSpName> _Role;

      public BaseSqlData<RoleFilter, RoleListFilter, RolePaginationFilter, RoleAdd, RoleUpdate, BaseSpName> Role
      {
          get { if (_Role == null)  _Role =  new RoleSqlData(_entityName, _spName); return _Role; }
      }

private BaseSqlData<ProductFilter, ProductListFilter, ProductPaginationFilter, ProductAdd, ProductUpdate, BaseSpName> _Product;

      public BaseSqlData<ProductFilter, ProductListFilter, ProductPaginationFilter, ProductAdd, ProductUpdate, BaseSpName> Product
      {
          get { if (_Product == null)  _Product =  new ProductSqlData(_entityName, _spName); return _Product; }
      }

private BaseSqlData<CategoryFilter, CategoryListFilter, CategoryPaginationFilter, CategoryAdd, CategoryUpdate, BaseSpName> _Category;

      public BaseSqlData<CategoryFilter, CategoryListFilter, CategoryPaginationFilter, CategoryAdd, CategoryUpdate, BaseSpName> Category
      {
          get { if (_Category == null)  _Category =  new CategorySqlData(_entityName, _spName); return _Category; }
      }

private BaseSqlData<TempCardFilter, TempCardListFilter, TempCardPaginationFilter, TempCardAdd, TempCardUpdate, BaseSpName> _TempCard;

      public BaseSqlData<TempCardFilter, TempCardListFilter, TempCardPaginationFilter, TempCardAdd, TempCardUpdate, BaseSpName> TempCard
      {
          get { if (_TempCard == null)  _TempCard =  new TempCardSqlData(_entityName, _spName); return _TempCard; }
      }

private BaseSqlData<TempCardItemFilter, TempCardItemListFilter, TempCardItemPaginationFilter, TempCardItemAdd, TempCardItemUpdate, BaseSpName> _TempCardItem;

      public BaseSqlData<TempCardItemFilter, TempCardItemListFilter, TempCardItemPaginationFilter, TempCardItemAdd, TempCardItemUpdate, BaseSpName> TempCardItem
      {
          get { if (_TempCardItem == null)  _TempCardItem =  new TempCardItemSqlData(_entityName, _spName); return _TempCardItem; }
      }

private BaseSqlData<ShippingTypeFilter, ShippingTypeListFilter, ShippingTypePaginationFilter, ShippingTypeAdd, ShippingTypeUpdate, BaseSpName> _ShippingType;

      public BaseSqlData<ShippingTypeFilter, ShippingTypeListFilter, ShippingTypePaginationFilter, ShippingTypeAdd, ShippingTypeUpdate, BaseSpName> ShippingType
      {
          get { if (_ShippingType == null)  _ShippingType =  new ShippingTypeSqlData(_entityName, _spName); return _ShippingType; }
      }

private BaseSqlData<FactorFilter, FactorListFilter, FactorPaginationFilter, FactorAdd, FactorUpdate, BaseSpName> _Factor;

      public BaseSqlData<FactorFilter, FactorListFilter, FactorPaginationFilter, FactorAdd, FactorUpdate, BaseSpName> Factor
      {
          get { if (_Factor == null)  _Factor =  new FactorSqlData(_entityName, _spName); return _Factor; }
      }

private BaseSqlData<FactorInfoFilter, FactorInfoListFilter, FactorInfoPaginationFilter, FactorInfoAdd, FactorInfoUpdate, BaseSpName> _FactorInfo;

      public BaseSqlData<FactorInfoFilter, FactorInfoListFilter, FactorInfoPaginationFilter, FactorInfoAdd, FactorInfoUpdate, BaseSpName> FactorInfo
      {
          get { if (_FactorInfo == null)  _FactorInfo =  new FactorInfoSqlData(_entityName, _spName); return _FactorInfo; }
      }



  }

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"

[JwtAuth]
public class BaseApiController : ApiController
{
    protected SqlDataManager _sqlData;
    protected string _entityName = "";
    protected BaseSpName _spName;

    public BaseApiController(string entityName)
    {
        BaseSpName spNames = new BaseSpName();
        _entityName = entityName;
        _sqlData = new SqlDataManager(entityName, spNames);
    }
}

Now create your all controllers inherited from BaseApiController.

 

Product Contoller Example,

[Route("Api/Product/")]
public class ProductController : BaseApiController
{
    public ProductController() : base (EntityNames.Product)
    {

    }

    [HttpGet, Route("Api/Product/ToList")]
    public async Task<dynamic> ToList([FromUri] ProductListFilter filter)
    {
        filter = filter ?? new ProductListFilter();
        var result = await _sqlData.Product.ToList(filter);

        return new { Result = JsonConvert.DeserializeObject(result) };
    }

    [HttpGet, Route("Api/Product/PagedList")]
    public async Task<dynamic> PagedList([FromUri] ProductPaginationFilter filter)
    {
        filter = filter ?? new ProductPaginationFilter();
        var result = await _sqlData.Product.PagedList(filter);
        return new { Result = JsonConvert.DeserializeObject(result) };
    }

    [HttpGet, Route("Api/Product/GetDeletedList")]
    public async Task<dynamic> GetDeletedList([FromUri] ProductListFilter filter)
    {
        filter = filter ?? new ProductListFilter();
        var result = await _sqlData.Product.GetDeletedList(filter);
        return new { Result = JsonConvert.DeserializeObject(result) };
    }

    [HttpGet, Route("Api/Product/FirstOrDefault")]
    public async Task<dynamic> FirstOrDefault([FromUri] ProductFilter filter)
    {
        filter = filter ?? new ProductFilter();
        var result = await _sqlData.Product.FirstOrDefault(filter);
        return new { Result = JsonConvert.DeserializeObject(result) };
    }

    [HttpGet, Route("Api/Product/FirstOrDefaultById/{id}")]
    public async Task<dynamic> FirstOrDefaultById(long? id)
    {
        if (id == null)
            return new { JsonString = "Error" };
        var result = await _sqlData.Product.FirstOrDefaultById(id);
        return new { Result = JsonConvert.DeserializeObject(result) };
    }

    [HttpGet, Route("Api/Product/DeletedFirstOrDefault")]
    public async Task<dynamic> DeletedFirstOrDefault([FromUri] ProductFilter filter)
    {
        filter = filter ?? new ProductFilter();
        var result = await _sqlData.Product.DeletedFirstOrDefault(filter);
        return new { Result = JsonConvert.DeserializeObject(result) };
    }

    [HttpGet, Route("Api/Product/AutoComplete")]
    public async Task<dynamic> AutoComplete([FromUri] ProductListFilter filter)
    {
        filter = filter ?? new ProductListFilter();
        var result = await _sqlData.Product.AutoComplete(filter);
        return new { Result = JsonConvert.DeserializeObject(result) };
    }

    [HttpPost, Route("Api/Product/Add")]
    public async Task<dynamic> Add([FromBody]ProductAdd item)
    {
        if(item == null)
            return new { JsonString = "Error" };

        var currentUser = JwtIdentity.UserInfo(Thread.CurrentPrincipal.Identity);
        //item.SubmiterUserId = currentUser.Id;
        var result = await _sqlData.Product.Add(item);
        return new { Result = JsonConvert.DeserializeObject(result) };
    }

    [HttpPut, Route("Api/Product/Update")]
    public async Task<dynamic> Update([FromBody]ProductUpdate item)
    {
        if (item == null)
            return new { JsonString = "Error" };
        var currentUser = JwtIdentity.UserInfo(Thread.CurrentPrincipal.Identity);
        //item.SubmiterUserId = currentUser.Id;
        var result = await _sqlData.Product.Update(item);
        return new { Result = JsonConvert.DeserializeObject(result) };
    }

    [HttpDelete, Route("Api/Product/Delete/{id}")]
    public async Task<dynamic> Delete([FromUri] long? id)
    {
        var result = await _sqlData.Product.Delete(id);
        return new { Result = JsonConvert.DeserializeObject(result) };
    }

    [HttpPut, Route("Api/Product/Restore/{id}")]
    public async Task<dynamic> Restore([FromUri] long? id)
    {
        var result = await _sqlData.Product.Restore(id);
        return new { Result = JsonConvert.DeserializeObject(result) };
    }
}

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

  public class ProductFilter : BaseSqlFilterParam
  {

public long? Id { get; set; }
public string MultiText_Id { get; set; }
public long? MinId { get; set; }
public long? MaxId { get; set; }

public double? Price { get; set; }
public string MultiText_Price { get; set; }
public double? MinPrice { get; set; }
public double? MaxPrice { get; set; }

public int? Count { get; set; }
public string MultiText_Count { get; set; }
public int? MinCount { get; set; }
public int? MaxCount { get; set; }

public string Title { get; set; }
public string MultiText_Title { get; set; }

public int? OffPercent { get; set; }
public string MultiText_OffPercent { get; set; }
public int? MinOffPercent { get; set; }
public int? MaxOffPercent { get; set; }

public long? CategoryId { get; set; }
public string MultiText_CategoryId { get; set; }
public long? MinCategoryId { get; set; }
public long? MaxCategoryId { get; set; }

public bool? IsDeleted { get; set; }
public string MultiText_IsDeleted { get; set; }

public DateTimeOffset? ModifyDate { get; set; }
public string MultiText_ModifyDate { get; set; }
public DateTimeOffset? MinModifyDate { get; set; }
public DateTimeOffset? MaxModifyDate { get; set; }

public DateTimeOffset? SubmitDate { get; set; }
public string MultiText_SubmitDate { get; set; }
public DateTimeOffset? MinSubmitDate { get; set; }
public DateTimeOffset? MaxSubmitDate { get; set; }


      public override string GetSpParams()
      {
          var result = "";


    result += SqlService.Convert(Id) + ", ";
    result += SqlService.Convert(MultiText_Id) + ", ";
    result += SqlService.Convert(MinId) + ", ";
    result += SqlService.Convert(MaxId) + ",  ";

    result += SqlService.Convert(Price) + ", ";
    result += SqlService.Convert(MultiText_Price) + ", ";
    result += SqlService.Convert(MinPrice) + ", ";
    result += SqlService.Convert(MaxPrice) + ",  ";

    result += SqlService.Convert(Count) + ", ";
    result += SqlService.Convert(MultiText_Count) + ", ";
    result += SqlService.Convert(MinCount) + ", ";
    result += SqlService.Convert(MaxCount) + ",  ";

    result += SqlService.Convert(Title) + ", ";

    result += SqlService.Convert(MultiText_Title) + ",  ";


    result += SqlService.Convert(OffPercent) + ", ";
    result += SqlService.Convert(MultiText_OffPercent) + ", ";
    result += SqlService.Convert(MinOffPercent) + ", ";
    result += SqlService.Convert(MaxOffPercent) + ",  ";

    result += SqlService.Convert(CategoryId) + ", ";
    result += SqlService.Convert(MultiText_CategoryId) + ", ";
    result += SqlService.Convert(MinCategoryId) + ", ";
    result += SqlService.Convert(MaxCategoryId) + ",  ";

    result += SqlService.Convert(IsDeleted) + ", ";

    result += SqlService.Convert(MultiText_IsDeleted) + ",  ";


    result += SqlService.Convert(ModifyDate) + ", ";
    result += SqlService.Convert(MultiText_ModifyDate) + ", ";
    result += SqlService.Convert(MinModifyDate) + ", ";
    result += SqlService.Convert(MaxModifyDate) + ",  ";

    result += SqlService.Convert(SubmitDate) + ", ";
    result += SqlService.Convert(MultiText_SubmitDate) + ", ";
    result += SqlService.Convert(MinSubmitDate) + ", ";
    result += SqlService.Convert(MaxSubmitDate) + " ";


          return result;
      }
  }

List Filter Model

public class ProductListFilter : BaseSqlListFilterParam
  {
public override int? TopCount { get; set; } = 100;


public long? Id { get; set; }
public string MultiText_Id { get; set; }
public long? MinId { get; set; }
public long? MaxId { get; set; }

public double? Price { get; set; }
public string MultiText_Price { get; set; }
public double? MinPrice { get; set; }
public double? MaxPrice { get; set; }

public int? Count { get; set; }
public string MultiText_Count { get; set; }
public int? MinCount { get; set; }
public int? MaxCount { get; set; }

public string Title { get; set; }
public string MultiText_Title { get; set; }

public int? OffPercent { get; set; }
public string MultiText_OffPercent { get; set; }
public int? MinOffPercent { get; set; }
public int? MaxOffPercent { get; set; }

public long? CategoryId { get; set; }
public string MultiText_CategoryId { get; set; }
public long? MinCategoryId { get; set; }
public long? MaxCategoryId { get; set; }

public bool? IsDeleted { get; set; }
public string MultiText_IsDeleted { get; set; }

public DateTimeOffset? ModifyDate { get; set; }
public string MultiText_ModifyDate { get; set; }
public DateTimeOffset? MinModifyDate { get; set; }
public DateTimeOffset? MaxModifyDate { get; set; }

public DateTimeOffset? SubmitDate { get; set; }
public string MultiText_SubmitDate { get; set; }
public DateTimeOffset? MinSubmitDate { get; set; }
public DateTimeOffset? MaxSubmitDate { get; set; }


      public override string GetSpParams()
      {
          var result = "";

    result += SqlService.Convert(TopCount) + ", ";

    result += SqlService.Convert(Id) + ", ";
    result += SqlService.Convert(MultiText_Id) + ", ";
    result += SqlService.Convert(MinId) + ", ";
    result += SqlService.Convert(MaxId) + ",  ";

    result += SqlService.Convert(Price) + ", ";
    result += SqlService.Convert(MultiText_Price) + ", ";
    result += SqlService.Convert(MinPrice) + ", ";
    result += SqlService.Convert(MaxPrice) + ",  ";

    result += SqlService.Convert(Count) + ", ";
    result += SqlService.Convert(MultiText_Count) + ", ";
    result += SqlService.Convert(MinCount) + ", ";
    result += SqlService.Convert(MaxCount) + ",  ";

    result += SqlService.Convert(Title) + ", ";

    result += SqlService.Convert(MultiText_Title) + ",  ";


    result += SqlService.Convert(OffPercent) + ", ";
    result += SqlService.Convert(MultiText_OffPercent) + ", ";
    result += SqlService.Convert(MinOffPercent) + ", ";
    result += SqlService.Convert(MaxOffPercent) + ",  ";

    result += SqlService.Convert(CategoryId) + ", ";
    result += SqlService.Convert(MultiText_CategoryId) + ", ";
    result += SqlService.Convert(MinCategoryId) + ", ";
    result += SqlService.Convert(MaxCategoryId) + ",  ";

    result += SqlService.Convert(IsDeleted) + ", ";

    result += SqlService.Convert(MultiText_IsDeleted) + ",  ";


    result += SqlService.Convert(ModifyDate) + ", ";
    result += SqlService.Convert(MultiText_ModifyDate) + ", ";
    result += SqlService.Convert(MinModifyDate) + ", ";
    result += SqlService.Convert(MaxModifyDate) + ",  ";

    result += SqlService.Convert(SubmitDate) + ", ";
    result += SqlService.Convert(MultiText_SubmitDate) + ", ";
    result += SqlService.Convert(MinSubmitDate) + ", ";
    result += SqlService.Convert(MaxSubmitDate) + " ";


          return result;
      }
  }

Pagination Filter Model

  public class ProductPaginationFilter : BaseSqlPaginationFilterParam
  {
public override int? Skip { get; set; } = 0;
public override int? Size { get; set; } = 10;


public long? Id { get; set; }
public string MultiText_Id { get; set; }
public long? MinId { get; set; }
public long? MaxId { get; set; }

public double? Price { get; set; }
public string MultiText_Price { get; set; }
public double? MinPrice { get; set; }
public double? MaxPrice { get; set; }

public int? Count { get; set; }
public string MultiText_Count { get; set; }
public int? MinCount { get; set; }
public int? MaxCount { get; set; }

public string Title { get; set; }
public string MultiText_Title { get; set; }

public int? OffPercent { get; set; }
public string MultiText_OffPercent { get; set; }
public int? MinOffPercent { get; set; }
public int? MaxOffPercent { get; set; }

public long? CategoryId { get; set; }
public string MultiText_CategoryId { get; set; }
public long? MinCategoryId { get; set; }
public long? MaxCategoryId { get; set; }

public bool? IsDeleted { get; set; }
public string MultiText_IsDeleted { get; set; }

public DateTimeOffset? ModifyDate { get; set; }
public string MultiText_ModifyDate { get; set; }
public DateTimeOffset? MinModifyDate { get; set; }
public DateTimeOffset? MaxModifyDate { get; set; }

public DateTimeOffset? SubmitDate { get; set; }
public string MultiText_SubmitDate { get; set; }
public DateTimeOffset? MinSubmitDate { get; set; }
public DateTimeOffset? MaxSubmitDate { get; set; }


      public override string GetSpParams()
      {
          var result = "";

    result += SqlService.Convert(Skip) + ", ";
    result += SqlService.Convert(Size) + ", ";


    result += SqlService.Convert(Id) + ", ";
    result += SqlService.Convert(MultiText_Id) + ", ";
    result += SqlService.Convert(MinId) + ", ";
    result += SqlService.Convert(MaxId) + ",  ";

    result += SqlService.Convert(Price) + ", ";
    result += SqlService.Convert(MultiText_Price) + ", ";
    result += SqlService.Convert(MinPrice) + ", ";
    result += SqlService.Convert(MaxPrice) + ",  ";

    result += SqlService.Convert(Count) + ", ";
    result += SqlService.Convert(MultiText_Count) + ", ";
    result += SqlService.Convert(MinCount) + ", ";
    result += SqlService.Convert(MaxCount) + ",  ";

    result += SqlService.Convert(Title) + ", ";

    result += SqlService.Convert(MultiText_Title) + ",  ";


    result += SqlService.Convert(OffPercent) + ", ";
    result += SqlService.Convert(MultiText_OffPercent) + ", ";
    result += SqlService.Convert(MinOffPercent) + ", ";
    result += SqlService.Convert(MaxOffPercent) + ",  ";

    result += SqlService.Convert(CategoryId) + ", ";
    result += SqlService.Convert(MultiText_CategoryId) + ", ";
    result += SqlService.Convert(MinCategoryId) + ", ";
    result += SqlService.Convert(MaxCategoryId) + ",  ";

    result += SqlService.Convert(IsDeleted) + ", ";

    result += SqlService.Convert(MultiText_IsDeleted) + ",  ";


    result += SqlService.Convert(ModifyDate) + ", ";
    result += SqlService.Convert(MultiText_ModifyDate) + ", ";
    result += SqlService.Convert(MinModifyDate) + ", ";
    result += SqlService.Convert(MaxModifyDate) + ",  ";

    result += SqlService.Convert(SubmitDate) + ", ";
    result += SqlService.Convert(MultiText_SubmitDate) + ", ";
    result += SqlService.Convert(MinSubmitDate) + ", ";
    result += SqlService.Convert(MaxSubmitDate) + " ";


          return result;
      }
  }

Insert Model

  public class ProductAdd : BaseSqlAddParam
  {

//[Column(Order = 2)]
//[Timestamp]
//[Required]
//[MaxLength(0) , MinLength(1)]
//[StringLength (0)]
public double? Price { get; set; }

//[Column(Order = 3)]
//[Timestamp]
//[Required]
//[MaxLength(0) , MinLength(1)]
//[StringLength (0)]
public int? Count { get; set; }

//[Column(Order = 4)]
//[Timestamp]
//[Required]
//[MaxLength(150) , MinLength(1)]
//[StringLength (150)]
public string Title { get; set; }

//[Column(Order = 5)]
//[Timestamp]
//[Required]
//[MaxLength(0) , MinLength(1)]
//[StringLength (0)]
public int? OffPercent { get; set; }

//[Column(Order = 6)]
//[Timestamp]
//[Required]
//[MaxLength(0) , MinLength(1)]
//[StringLength (0)]
public long? CategoryId { get; set; }

//[Column(Order = 999999997)]
//[Timestamp]
//[Required]
//[MaxLength(0) , MinLength(1)]
//[StringLength (0)]
public bool? IsDeleted { get; set; }

//[Column(Order = 999999998)]
//[Timestamp]
//[Required]
//[MaxLength(0) , MinLength(1)]
//[StringLength (0)]
public DateTimeOffset? ModifyDate { get; set; }

//[Column(Order = 999999999)]
//[Timestamp]
//[Required]
//[MaxLength(0) , MinLength(1)]
//[StringLength (0)]
public DateTimeOffset? SubmitDate { get; set; }


      public override string GetSpParams()
      {
          var result = "";


        result += SqlService.Convert(Price) + ",  ";

        result += SqlService.Convert(Count) + ",  ";

        result += SqlService.Convert(Title) + ",  ";

        result += SqlService.Convert(OffPercent) + ",  ";

        result += SqlService.Convert(CategoryId) + ",  ";

        result += SqlService.Convert(IsDeleted) + ",  ";

        result += SqlService.Convert(ModifyDate) + ",  ";

        result += SqlService.Convert(SubmitDate) + " ";


          return result;
      }
  }

Update Model

  public class ProductUpdate : BaseSqlUpdateParam
  {

//[Column(Order = 1)]
//[Timestamp]
//[Required]
//[MaxLength(0) , MinLength(1)]
//[StringLength (0)]
public long? Id { get; set; }

//[Column(Order = 2)]
//[Timestamp]
//[Required]
//[MaxLength(0) , MinLength(1)]
//[StringLength (0)]
public double? Price { get; set; }

//[Column(Order = 3)]
//[Timestamp]
//[Required]
//[MaxLength(0) , MinLength(1)]
//[StringLength (0)]
public int? Count { get; set; }

//[Column(Order = 4)]
//[Timestamp]
//[Required]
//[MaxLength(150) , MinLength(1)]
//[StringLength (150)]
public string Title { get; set; }

//[Column(Order = 5)]
//[Timestamp]
//[Required]
//[MaxLength(0) , MinLength(1)]
//[StringLength (0)]
public int? OffPercent { get; set; }

//[Column(Order = 6)]
//[Timestamp]
//[Required]
//[MaxLength(0) , MinLength(1)]
//[StringLength (0)]
public long? CategoryId { get; set; }

//[Column(Order = 999999997)]
//[Timestamp]
//[Required]
//[MaxLength(0) , MinLength(1)]
//[StringLength (0)]
public bool? IsDeleted { get; set; }

//[Column(Order = 999999998)]
//[Timestamp]
//[Required]
//[MaxLength(0) , MinLength(1)]
//[StringLength (0)]
public DateTimeOffset? ModifyDate { get; set; }


      public override string GetSpParams()
      {
          var result = "";


        result += SqlService.Convert(Id) + ",  ";

        result += SqlService.Convert(Price) + ",  ";

        result += SqlService.Convert(Count) + ",  ";

        result += SqlService.Convert(Title) + ",  ";

        result += SqlService.Convert(OffPercent) + ",  ";

        result += SqlService.Convert(CategoryId) + ",  ";

        result += SqlService.Convert(IsDeleted) + ",  ";

        result += SqlService.Convert(ModifyDate) + " ";


          return result;
      }
  }

Topic 5 - Custom Jwt Authorization

 

First find a safe Encrypt Decrypt class with usage of a password,

public class EncryptCode
{
    private static readonly string key = "apiTokenRequierd!!**KKKKKKKKKKKJJJJJ";
    public static string Encrypt(string data)
    {
        Encoding unicode = Encoding.Unicode;

        return Convert.ToBase64String(Encrypt(unicode.GetBytes(key), unicode.GetBytes(data)));
    }

    public static string Decrypt(string data)
    {
        Encoding unicode = Encoding.Unicode;

        return unicode.GetString(Encrypt(unicode.GetBytes(key), Convert.FromBase64String(data)));
    }

    public static byte[] Encrypt(byte[] key, byte[] data)
    {
        return EncryptOutput(key, data).ToArray();
    }

    public static byte[] Decrypt(byte[] key, byte[] data)
    {
        return EncryptOutput(key, data).ToArray();
    }

    private static byte[] EncryptInitalize(byte[] key)
    {
        byte[] s = Enumerable.Range(0, 256)
          .Select(i => (byte)i)
          .ToArray();

        for (int i = 0, j = 0; i < 256; i++)
        {
            j = (j + key[i % key.Length] + s[i]) & 255;

            Swap(s, i, j);
        }

        return s;
    }

    private static IEnumerable<byte> EncryptOutput(byte[] key, IEnumerable<byte> data)
    {
        byte[] s = EncryptInitalize(key);

        int i = 0;
        int j = 0;

        return data.Select((b) =>
        {
            i = (i + 1) & 255;
            j = (j + s[i]) & 255;

            Swap(s, i, j);

            return (byte)(b ^ s[(s[i] + s[j]) & 255]);
        });
    }

    private static void Swap(byte[] s, int i, int j)
    {
        byte c = s[i];

        s[i] = s[j];
        s[j] = c;
    }
}

Step 1 (Create role permission class)

 

Create a class with the following data structure or save your permissions roles in your database.

public class RoleControllerModel
{
    public IEnumerable<RoleActionModel> Actions { get; set; }

    public string Name { get; set; }

    public string[] Roles { get; set; }
}

public class RoleActionModel
{
    public string Name { get; set; }

    public string[] Roles { get; set; }
}


public static class RolePermission
{
    public const string Admin = "admin";
    public static IEnumerable<RoleControllerModel> RoleList
    {
        get
        {
            var result = new List<RoleControllerModel>();

            result.Add(new RoleControllerModel()
            {
                Name = "Values",
                Roles = new string[] { Admin },
                Actions = new List<RoleActionModel>()
                {
                    new RoleActionModel() { Name = "Token", Roles = new string[] { Admin } },

                },
            });


            result.Add(new RoleControllerModel()
            {
                Name = "LoginInfo",
                Roles = new string[] { Admin },
                Actions = new List<RoleActionModel>()
                {
                    new RoleActionModel() { Name = "DeletedFirstOrDefault", Roles = new string[] { Admin } },
                    new RoleActionModel() { Name = "FirstOrDefaultById", Roles = new string[] { Admin } },
                    new RoleActionModel() { Name = "FirstOrDefault", Roles = new string[] { Admin } },
                    new RoleActionModel() { Name = "GetDeletedList", Roles = new string[] { Admin } },
                    new RoleActionModel() { Name = "AutoComplete", Roles = new string[] { Admin } },
                    new RoleActionModel() { Name = "PagedList", Roles = new string[] { Admin } },
                    new RoleActionModel() { Name = "Restore", Roles = new string[] { Admin } },
                    new RoleActionModel() { Name = "ToList", Roles = new string[] { Admin } },
                    new RoleActionModel() { Name = "Update", Roles = new string[] { Admin } },
                    new RoleActionModel() { Name = "Delete", Roles = new string[] { Admin } },
                    new RoleActionModel() { Name = "Add", Roles = new string[] { Admin } }
                },
            });

            result.Add(new RoleControllerModel()
            {
                Name = "UserInfo",
                Roles = new string[] { Admin },
                Actions = new List<RoleActionModel>()
                {
                    new RoleActionModel() { Name = "DeletedFirstOrDefault", Roles = new string[] { Admin } },
                    new RoleActionModel() { Name = "FirstOrDefaultById", Roles = new string[] { Admin } },
                    new RoleActionModel() { Name = "FirstOrDefault", Roles = new string[] { Admin } },
                    new RoleActionModel() { Name = "GetDeletedList", Roles = new string[] { Admin } },
                    new RoleActionModel() { Name = "AutoComplete", Roles = new string[] { Admin } },
                    new RoleActionModel() { Name = "PagedList", Roles = new string[] { Admin } },
                    new RoleActionModel() { Name = "Restore", Roles = new string[] { Admin } },
                    new RoleActionModel() { Name = "ToList", Roles = new string[] { Admin } },
                    new RoleActionModel() { Name = "Update", Roles = new string[] { Admin } },
                    new RoleActionModel() { Name = "Delete", Roles = new string[] { Admin } },
                    new RoleActionModel() { Name = "Add", Roles = new string[] { Admin } }
                },
            });

            result.Add(new RoleControllerModel()
            {
                Name = "Role",
                Roles = new string[] { Admin },
                Actions = new List<RoleActionModel>()
                {
                    new RoleActionModel() { Name = "DeletedFirstOrDefault", Roles = new string[] { Admin } },
                    new RoleActionModel() { Name = "FirstOrDefaultById", Roles = new string[] { Admin } },
                    new RoleActionModel() { Name = "FirstOrDefault", Roles = new string[] { Admin } },
                    new RoleActionModel() { Name = "GetDeletedList", Roles = new string[] { Admin } },
                    new RoleActionModel() { Name = "AutoComplete", Roles = new string[] { Admin } },
                    new RoleActionModel() { Name = "PagedList", Roles = new string[] { Admin } },
                    new RoleActionModel() { Name = "Restore", Roles = new string[] { Admin } },
                    new RoleActionModel() { Name = "ToList", Roles = new string[] { Admin } },
                    new RoleActionModel() { Name = "Update", Roles = new string[] { Admin } },
                    new RoleActionModel() { Name = "Delete", Roles = new string[] { Admin } },
                    new RoleActionModel() { Name = "Add", Roles = new string[] { Admin } }
                },
            });

            result.Add(new RoleControllerModel()
            {
                Name = "Product",
                Roles = new string[] { Admin },
                Actions = new List<RoleActionModel>()
                {
                    new RoleActionModel() { Name = "DeletedFirstOrDefault", Roles = new string[] { Admin } },
                    new RoleActionModel() { Name = "FirstOrDefaultById", Roles = new string[] { Admin } },
                    new RoleActionModel() { Name = "FirstOrDefault", Roles = new string[] { Admin } },
                    new RoleActionModel() { Name = "GetDeletedList", Roles = new string[] { Admin } },
                    new RoleActionModel() { Name = "AutoComplete", Roles = new string[] { Admin } },
                    new RoleActionModel() { Name = "PagedList", Roles = new string[] { Admin } },
                    new RoleActionModel() { Name = "Restore", Roles = new string[] { Admin } },
                    new RoleActionModel() { Name = "ToList", Roles = new string[] { Admin } },
                    new RoleActionModel() { Name = "Update", Roles = new string[] { Admin } },
                    new RoleActionModel() { Name = "Delete", Roles = new string[] { Admin } },
                    new RoleActionModel() { Name = "Add", Roles = new string[] { Admin } }
                },
            });

            result.Add(new RoleControllerModel()
            {
                Name = "Category",
                Roles = new string[] { Admin },
                Actions = new List<RoleActionModel>()
                {
                    new RoleActionModel() { Name = "DeletedFirstOrDefault", Roles = new string[] { Admin } },
                    new RoleActionModel() { Name = "FirstOrDefaultById", Roles = new string[] { Admin } },
                    new RoleActionModel() { Name = "FirstOrDefault", Roles = new string[] { Admin } },
                    new RoleActionModel() { Name = "GetDeletedList", Roles = new string[] { Admin } },
                    new RoleActionModel() { Name = "AutoComplete", Roles = new string[] { Admin } },
                    new RoleActionModel() { Name = "PagedList", Roles = new string[] { Admin } },
                    new RoleActionModel() { Name = "Restore", Roles = new string[] { Admin } },
                    new RoleActionModel() { Name = "ToList", Roles = new string[] { Admin } },
                    new RoleActionModel() { Name = "Update", Roles = new string[] { Admin } },
                    new RoleActionModel() { Name = "Delete", Roles = new string[] { Admin } },
                    new RoleActionModel() { Name = "Add", Roles = new string[] { Admin } }
                },
            });

            result.Add(new RoleControllerModel()
            {
                Name = "TempCard",
                Roles = new string[] { Admin },
                Actions = new List<RoleActionModel>()
                {
                    new RoleActionModel() { Name = "DeletedFirstOrDefault", Roles = new string[] { Admin } },
                    new RoleActionModel() { Name = "FirstOrDefaultById", Roles = new string[] { Admin } },
                    new RoleActionModel() { Name = "FirstOrDefault", Roles = new string[] { Admin } },
                    new RoleActionModel() { Name = "GetDeletedList", Roles = new string[] { Admin } },
                    new RoleActionModel() { Name = "AutoComplete", Roles = new string[] { Admin } },
                    new RoleActionModel() { Name = "PagedList", Roles = new string[] { Admin } },
                    new RoleActionModel() { Name = "Restore", Roles = new string[] { Admin } },
                    new RoleActionModel() { Name = "ToList", Roles = new string[] { Admin } },
                    new RoleActionModel() { Name = "Update", Roles = new string[] { Admin } },
                    new RoleActionModel() { Name = "Delete", Roles = new string[] { Admin } },
                    new RoleActionModel() { Name = "Add", Roles = new string[] { Admin } }
                },
            });

            result.Add(new RoleControllerModel()
            {
                Name = "TempCardItem",
                Roles = new string[] { Admin },
                Actions = new List<RoleActionModel>()
                {
                    new RoleActionModel() { Name = "DeletedFirstOrDefault", Roles = new string[] { Admin } },
                    new RoleActionModel() { Name = "FirstOrDefaultById", Roles = new string[] { Admin } },
                    new RoleActionModel() { Name = "FirstOrDefault", Roles = new string[] { Admin } },
                    new RoleActionModel() { Name = "GetDeletedList", Roles = new string[] { Admin } },
                    new RoleActionModel() { Name = "AutoComplete", Roles = new string[] { Admin } },
                    new RoleActionModel() { Name = "PagedList", Roles = new string[] { Admin } },
                    new RoleActionModel() { Name = "Restore", Roles = new string[] { Admin } },
                    new RoleActionModel() { Name = "ToList", Roles = new string[] { Admin } },
                    new RoleActionModel() { Name = "Update", Roles = new string[] { Admin } },
                    new RoleActionModel() { Name = "Delete", Roles = new string[] { Admin } },
                    new RoleActionModel() { Name = "Add", Roles = new string[] { Admin } }
                },
            });

            result.Add(new RoleControllerModel()
            {
                Name = "ShippingType",
                Roles = new string[] { Admin },
                Actions = new List<RoleActionModel>()
                {
                    new RoleActionModel() { Name = "DeletedFirstOrDefault", Roles = new string[] { Admin } },
                    new RoleActionModel() { Name = "FirstOrDefaultById", Roles = new string[] { Admin } },
                    new RoleActionModel() { Name = "FirstOrDefault", Roles = new string[] { Admin } },
                    new RoleActionModel() { Name = "GetDeletedList", Roles = new string[] { Admin } },
                    new RoleActionModel() { Name = "AutoComplete", Roles = new string[] { Admin } },
                    new RoleActionModel() { Name = "PagedList", Roles = new string[] { Admin } },
                    new RoleActionModel() { Name = "Restore", Roles = new string[] { Admin } },
                    new RoleActionModel() { Name = "ToList", Roles = new string[] { Admin } },
                    new RoleActionModel() { Name = "Update", Roles = new string[] { Admin } },
                    new RoleActionModel() { Name = "Delete", Roles = new string[] { Admin } },
                    new RoleActionModel() { Name = "Add", Roles = new string[] { Admin } }
                },
            });

            result.Add(new RoleControllerModel()
            {
                Name = "Factor",
                Roles = new string[] { Admin },
                Actions = new List<RoleActionModel>()
                {
                    new RoleActionModel() { Name = "DeletedFirstOrDefault", Roles = new string[] { Admin } },
                    new RoleActionModel() { Name = "FirstOrDefaultById", Roles = new string[] { Admin } },
                    new RoleActionModel() { Name = "FirstOrDefault", Roles = new string[] { Admin } },
                    new RoleActionModel() { Name = "GetDeletedList", Roles = new string[] { Admin } },
                    new RoleActionModel() { Name = "AutoComplete", Roles = new string[] { Admin } },
                    new RoleActionModel() { Name = "PagedList", Roles = new string[] { Admin } },
                    new RoleActionModel() { Name = "Restore", Roles = new string[] { Admin } },
                    new RoleActionModel() { Name = "ToList", Roles = new string[] { Admin } },
                    new RoleActionModel() { Name = "Update", Roles = new string[] { Admin } },
                    new RoleActionModel() { Name = "Delete", Roles = new string[] { Admin } },
                    new RoleActionModel() { Name = "Add", Roles = new string[] { Admin } }
                },
            });

            result.Add(new RoleControllerModel()
            {
                Name = "FactorInfo",
                Roles = new string[] { Admin },
                Actions = new List<RoleActionModel>()
                {
                    new RoleActionModel() { Name = "DeletedFirstOrDefault", Roles = new string[] { Admin } },
                    new RoleActionModel() { Name = "FirstOrDefaultById", Roles = new string[] { Admin } },
                    new RoleActionModel() { Name = "FirstOrDefault", Roles = new string[] { Admin } },
                    new RoleActionModel() { Name = "GetDeletedList", Roles = new string[] { Admin } },
                    new RoleActionModel() { Name = "AutoComplete", Roles = new string[] { Admin } },
                    new RoleActionModel() { Name = "PagedList", Roles = new string[] { Admin } },
                    new RoleActionModel() { Name = "Restore", Roles = new string[] { Admin } },
                    new RoleActionModel() { Name = "ToList", Roles = new string[] { Admin } },
                    new RoleActionModel() { Name = "Update", Roles = new string[] { Admin } },
                    new RoleActionModel() { Name = "Delete", Roles = new string[] { Admin } },
                    new RoleActionModel() { Name = "Add", Roles = new string[] { Admin } }
                },
            });


            return result;
        }
    }
}

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)

public class JwtAuth : AuthorizationFilterAttribute
{
    public override void OnAuthorization(HttpActionContext actionContext)
    {
        try
        {
            if (actionContext.Request.Headers.Authorization != null)
            {
                if(actionContext.Request.Headers.Authorization.Scheme == "Bearer")
                {
                    var authToken = actionContext.Request.Headers.Authorization.Parameter;

                    var decoAuthToken = EncryptCode.Decrypt(authToken);

                    //split by colon : and store in variable
                    //var UserNameAndPassword = decoAuthToken.Split(':');
                    //Passing to a function for authorization

                    //var controller = (string)actionContext.RequestContext.RouteData.Values["controller"];
                    var action = (HttpActionDescriptor[])actionContext.RequestContext.RouteData.Route.DataTokens["actions"];
                    if (action.Length > 0 && action[0].ControllerDescriptor != null)
                    {
                        var rightPlace = action[0];
                        var controllerName = rightPlace.ControllerDescriptor.ControllerName;
                        var actionName = rightPlace.ActionName;
                        var infoes = decoAuthToken.Split('|');
                        if(infoes != null && infoes.Length == 3)
                        {
                            DateTime expDate = DateTime.Now.AddDays(-30);
                            bool haveDate = false;
                            haveDate = DateTime.TryParse(infoes[2], out expDate);
                            if (infoes[0].StartsWith("userId=") && haveDate && (expDate > DateTime.Now))
                            {
                                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]))))
                                {
                                    var genericIdentity = new GenericIdentity(decoAuthToken);
                                    var princ = new GenericPrincipal(genericIdentity, null);
                                    Thread.CurrentPrincipal = princ;
                                }

                                else
                                    actionContext.Response = actionContext.Request.CreateResponse(HttpStatusCode.Unauthorized);
                            }
                            else
                                actionContext.Response = actionContext.Request.CreateResponse(HttpStatusCode.Unauthorized);
                        }

                        else
                            actionContext.Response = actionContext.Request.CreateResponse(HttpStatusCode.Unauthorized);
                    }
                    else
                        actionContext.Response = actionContext.Request.CreateResponse(HttpStatusCode.Unauthorized);
                }
                else
                    actionContext.Response = actionContext.Request.CreateResponse(HttpStatusCode.Unauthorized);

            }
            else
                actionContext.Response = actionContext.Request.CreateResponse(HttpStatusCode.Unauthorized);
        }
        catch (Exception ex)
        {
            actionContext.Response = actionContext.Request.CreateResponse(HttpStatusCode.Unauthorized);
            //ex.Message.ToString();
        }
    }

}

Step 3 (Create jwt Identity class)

 

Use a model and a static class to split token information

public class JwtIdentity
{
    public static JwtUserInfo UserInfo(IIdentity identity)
    {
        if(identity.IsAuthenticated)
        {
            try
            {
                var infoes = identity.Name.Split('|');
                DateTime expDate = DateTime.Now.AddDays(-30);
                bool haveDate = false;
                haveDate = DateTime.TryParse(infoes[2], out expDate);
                return new JwtUserInfo()
                {
                    ExpireDate = expDate,
                    Id = long.Parse(infoes[0].Replace("userId=", "")),
                    Role = infoes[1],
                    TokenString = identity.Name,
                    IsAuthenticated = true,
                    IsInvalidToken = false,
                };
            }
            catch (Exception)
            {
                return new JwtUserInfo() { IsInvalidToken = true, IsAuthenticated = false, TokenString = identity.Name, Id = -999999999999999999, ExpireDate = DateTime.Now.AddSeconds(-1), Role = "" };
            }
        }

        return new JwtUserInfo() { IsInvalidToken = false, IsAuthenticated = false, TokenString = identity.Name, Id = -999999999999999999, ExpireDate = DateTime.Now.AddSeconds(-1), Role = "" };
    }
}

public class JwtUserInfo
{
    public bool IsAuthenticated { get; set; }

    public bool IsInvalidToken { get; set; }

    public long Id { get; set; }

    public string Role { get; set; }

    public DateTime ExpireDate { get; set; }

    public string TokenString { get; set; }
}

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.