Introduction

Dalyan is a single page application project template using AngularJS, ASP.NET & Web API technologies. It is the base of a web application project. It has CRUD operation example with AngularJS. Additionally, WebAPI controllers and CRUD operations are created by T4 templates. You can download it from github source codes.

This post describes step by step how to generate Web API CRUD Operations automatic With T4 Templates on Dalyan Project.

https://github.com/aydnahmet/Dalyan
You can create “Add, Edit, Delete, Retrieve and GetAll” actions after create you table on database.
Lets do it step by step!

Create a “product” table on SQL Server Database
Add “product” table to Dalyan.Db Entity Framework Edmx model
Add “product” table to defination xml file
Generate DbModel with T4 template
Generate ServiceQuery with T4 template
Generate Service with T4 template
Generate WebAPI controller with T4 template
Check WebAPI actions

1. Create a “product” table on SQL Server Database

You have to add these columns to your new table to create CRUD operations automatic.


[Id] [int] IDENTITY(1,1) NOT NULL, ----> For IDENTITY
[CreatedDate] [datetime] NULL, ----> For Logging Created Date automatic
[CreatedIpAddress] [nvarchar](50) NULL, ----> For Logging Created Ip Address automatic
[CreatedUserId] [int] NULL, ----> For Logging Created UserId automatic
[UpdatedDate] [datetime] NULL, ----> For Logging Updated Date automatic
[UpdatedIpAddress] [nvarchar](50) NULL, ----> For Logging Updated Ip Address automatic
[UpdatedUserId] [int] NULL, ----> For Logging Updated UserId automatic
[IsDeleted] [bit] NULL, ----> For Deleting operation automatic

Create a test table for testing t4 templates which name is “Product”

You can add relationship to your columns.
I added CompanyId column relationship with Company table. On WebAPI response you can reach Company table with this relationship.

Table Create Table Script


SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Product](
[Id] [int] IDENTITY(1,1) NOT NULL,
[CompanyId] [int] NULL,
[Name] [nvarchar](50) NULL,
[Description] [nvarchar](1000) NULL,
[Price] [float] NULL,
[CreatedDate] [datetime] NULL,
[CreatedIpAddress] [nvarchar](50) NULL,
[CreatedUserId] [int] NULL,
[UpdatedDate] [datetime] NULL,
[UpdatedIpAddress] [nvarchar](50) NULL,
[UpdatedUserId] [int] NULL,
[IsDeleted] [bit] NULL,
CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Product] WITH CHECK ADD CONSTRAINT [FK_Product_Company] FOREIGN KEY([CompanyId])
REFERENCES [dbo].[Company] ([Id])
GO

ALTER TABLE [dbo].[Product] CHECK CONSTRAINT [FK_Product_Company]
GO

// Create Table Script

2. Add “product” table to Dalyan.Db Entity Framework Edmx model

Open Dalyan.Db project -> DalyanModel.edmx file.

On DalyanModel.edmx file right click and Update Model From Database

Add Product Table and save

Now product table is added to edmx file.

3. Add “product” table to defination xml file

Code generation runs with edmx file but you have to add table name to defination.xml.

Open Dalyan.Entities -> Defination -> Tables.xml file

t4_6

And add this node to xml


&lt;?xml version="1.0" encoding="utf-8" ?&gt;
&lt;Tables&gt;
&lt;Table Name="Product"&gt;&lt;/Table&gt;
&lt;/Tables&gt;

4. Generate DbModel with T4 template

Models are generated by this T4 template.

Open Dalyan.Entities -> Models -> DbModel.tt file

And set you file location to absolutePath


public bool ControlTable(string tableName)
 {
 XmlDocument doc = new XmlDocument();
 string absolutePath = Path.GetFullPath(@"D:\Temp\Dalyan-master\Dalyan-master\Dalyan.Entities\Defination\Tables.xml");
 doc.Load(absolutePath);
 XmlNodeList xnList = doc.SelectNodes("/Tables/Table[@Name='" + tableName +"']");
 foreach (XmlNode xn in xnList)
 {
 return true;
 }
 return false;
 }

Run Custom Tool

t4_7

t4_8

Your model is ready

//------------------------------------------------------------------------------
// <auto-generated>
// This code was generated from the Dalyan T4 template.
// Do not change
//
// </auto-generated>
//------------------------------------------------------------------------------
namespace Dalyan.Entities.Models
{
using System;
using System.Collections.Generic;

public partial class Product
{

public int Id { get; set; }

public Nullable<int> CompanyId { get; set; }

public string Name { get; set; }

public string Description { get; set; }

public Nullable<double> Price { get; set; }

public Nullable<System.DateTime> CreatedDate { get; set; }

public string CreatedIpAddress { get; set; }

public Nullable<int> CreatedUserId { get; set; }

public Nullable<System.DateTime> UpdatedDate { get; set; }

public string UpdatedIpAddress { get; set; }

public Nullable<int> UpdatedUserId { get; set; }

public Nullable<bool> IsDeleted { get; set; }

public virtual Company Company { get; set; }

}

}

5. Generate ServiceQuery with T4 template

ServiceQuery is base layer to access database. It has CRUD operations and mapping operations in this layer.

Open Dalyan.Domain -> Query -> ServiceQuery.tt file

Right click and “Run Custom Tools”

ProductAddQuery

//------------------------------------------------------------------------------
// <auto-generated>
// This code was generated from a template.
//
// Manual changes to this file may cause unexpected behavior in your application.
// Manual changes to this file will be overwritten if the code is regenerated.
// </auto-generated>
//------------------------------------------------------------------------------
namespace Dalyan.Domain.Query
{

using System;
using Dalyan.Domain;
using System.Linq;
using System.Text;
using Dalyan.Domain.Query;
using System.Data;
using System.Xml;
using Dalyan.Db;
using Entities.Models;
using Dalyan.Entities.Enumerations;
using AutoMapper;
using System.Threading.Tasks;
using System.Collections.Generic;

public class ProductAddQuery : IQuery<Dalyan.Entities.Models.Product>
{
public Dalyan.Entities.Models.Product Product{ get; set; }
}

public class ProductAddQueryHandler : IQueryHandler<ProductAddQuery,Dalyan.Entities.Models.Product>
{
private readonly DbEntities _db;
public ProductAddQueryHandler()
{
_db = new DbEntities();
}

public Dalyan.Entities.Models.Product Handler(ProductAddQuery query)
{
try
{
var obj = new Dalyan.Db.Product();
obj.Id = query.Product.Id;
obj.CompanyId = query.Product.CompanyId;
obj.Name = query.Product.Name;
obj.Description = query.Product.Description;
obj.Price = query.Product.Price;
obj.CreatedDate = query.Product.CreatedDate;
obj.CreatedIpAddress = query.Product.CreatedIpAddress;
obj.CreatedUserId = query.Product.CreatedUserId;
obj.UpdatedDate = query.Product.UpdatedDate;
obj.UpdatedIpAddress = query.Product.UpdatedIpAddress;
obj.UpdatedUserId = query.Product.UpdatedUserId;
obj.IsDeleted = query.Product.IsDeleted;
_db.Product.Add(obj);
_db.SaveChanges();
query.Product.Id = obj.Id;
return query.Product;

}
catch (Exception ex)
{
throw new ExceptionLog(LogType.DATABASE_INSERT, LogLevel.ERROR, ex, "AddQueryHandler");
}
}
}

}

//------------------------------------------------------------------------------
// <auto-generated>
// This code was generated from a template.
//
// Manual changes to this file may cause unexpected behavior in your application.
// Manual changes to this file will be overwritten if the code is regenerated.
// </auto-generated>
//------------------------------------------------------------------------------
namespace Dalyan.Domain.Query
{

using System;
using Dalyan.Domain;
using System.Linq;
using System.Text;
using Dalyan.Domain.Query;
using System.Data;
using System.Xml;
using Dalyan.Db;
using Entities.Models;
using Dalyan.Entities.Enumerations;
using AutoMapper;
using System.Threading.Tasks;
using System.Collections.Generic;

public class ProductDeleteQuery : IQuery<bool>
{
public int Id { get; set; }
}

public class ProductDeleteQueryHandler : IQueryHandler<Dalyan.Domain.Query.ProductDeleteQuery, bool>
{
private readonly DbEntities _db;
public ProductDeleteQueryHandler()
{
_db = new DbEntities();
}

public bool Handler(Dalyan.Domain.Query.ProductDeleteQuery query)
{
try
{
var obj = new Dalyan.Db.Product();
obj = _db.Product.FirstOrDefault(x => x.Id == query.Id);

obj.IsDeleted = true;
_db.SaveChanges();
return true;
}
catch (Exception ex)
{
throw new ExceptionLog(LogType.DATABASE_DELETE, LogLevel.ERROR, ex, "DeleteQueryHandler");
}
}
}
}


//------------------------------------------------------------------------------
// <auto-generated>
// This code was generated from a template.
//
// Manual changes to this file may cause unexpected behavior in your application.
// Manual changes to this file will be overwritten if the code is regenerated.
// </auto-generated>
//------------------------------------------------------------------------------
namespace Dalyan.Domain.Query
{

using System;
using Dalyan.Domain;
using System.Linq;
using System.Text;
using Dalyan.Domain.Query;
using System.Data;
using System.Xml;
using Dalyan.Db;
using Entities.Models;
using Dalyan.Entities.Enumerations;
using AutoMapper;
using System.Threading.Tasks;
using System.Collections.Generic;

public class ProductEditQuery : IQuery<Dalyan.Entities.Models.Product>
{
public Dalyan.Entities.Models.Product Product{ get; set; }
}

public class ProductEditQueryHandler : IQueryHandler<ProductEditQuery, Dalyan.Entities.Models.Product>
{
private readonly DbEntities _db;
public ProductEditQueryHandler()
{
_db = new DbEntities();
}

public Dalyan.Entities.Models.Product Handler(ProductEditQuery query)
{
try
{
var obj = new Dalyan.Db.Product();
obj = _db.Product.FirstOrDefault(x => x.Id == query.Product.Id);
obj.Id = query.Product.Id;
obj.CompanyId = query.Product.CompanyId;
obj.Name = query.Product.Name;
obj.Description = query.Product.Description;
obj.Price = query.Product.Price;
obj.CreatedDate = query.Product.CreatedDate;
obj.CreatedIpAddress = query.Product.CreatedIpAddress;
obj.CreatedUserId = query.Product.CreatedUserId;
obj.UpdatedDate = query.Product.UpdatedDate;
obj.UpdatedIpAddress = query.Product.UpdatedIpAddress;
obj.UpdatedUserId = query.Product.UpdatedUserId;
obj.IsDeleted = query.Product.IsDeleted;
_db.SaveChanges();
return query.Product;

}
catch (Exception ex)
{
throw new ExceptionLog(LogType.DATABASE_UPDATE, LogLevel.ERROR, ex, "EditQueryHandler");
}
}
}
}

//------------------------------------------------------------------------------
// <auto-generated>
// This code was generated from a template.
//
// Manual changes to this file may cause unexpected behavior in your application.
// Manual changes to this file will be overwritten if the code is regenerated.
// </auto-generated>
//------------------------------------------------------------------------------
namespace Dalyan.Domain.Query
{

using System;
using Dalyan.Domain;
using System.Linq;
using System.Text;
using Dalyan.Domain.Query;
using System.Data;
using System.Xml;
using Dalyan.Db;
using Entities.Models;
using Dalyan.Entities.Enumerations;
using AutoMapper;
using System.Threading.Tasks;
using System.Collections.Generic;

public class ProductGetAllQuery : IQuery<IList<Dalyan.Entities.Models.Product>>
{

}

public class ProductGetAllQueryHandler : IQueryHandler<ProductGetAllQuery, IList<Dalyan.Entities.Models.Product>>
{
private readonly DbEntities _db;
public ProductGetAllQueryHandler()
{
_db = new DbEntities();
}

public IList<Dalyan.Entities.Models.Product> Handler(ProductGetAllQuery query)
{
try
{
var result = _db.Product.Include("Company").Where(x => x.IsDeleted == false).AsEnumerable().ToList();
Mapper.CreateMap<Dalyan.Db.Company, Dalyan.Entities.Models.Company>();
Mapper.CreateMap<Dalyan.Db.Product, Dalyan.Entities.Models.Product>().ForMember(c => c.Company, d => d.MapFrom(s => s.Company));
return Mapper.Map<IEnumerable<Dalyan.Db.Product>, IEnumerable<Dalyan.Entities.Models.Product>>(result).ToList();

}
catch (Exception ex)
{
throw new ExceptionLog(LogType.DATABASE_SELECT, LogLevel.ERROR, ex, "GetAllQueryHandler");
}
}
}
}


---------------------------------------------------------------
// <auto-generated>
// This code was generated from a template.
//
// Manual changes to this file may cause unexpected behavior in your application.
// Manual changes to this file will be overwritten if the code is regenerated.
// </auto-generated>
//------------------------------------------------------------------------------
namespace Dalyan.Domain.Query
{

using System;
using Dalyan.Domain;
using System.Linq;
using System.Text;
using Dalyan.Domain.Query;
using System.Data;
using System.Xml;
using Dalyan.Db;
using Entities.Models;
using Dalyan.Entities.Enumerations;
using AutoMapper;
using System.Threading.Tasks;
using System.Collections.Generic;

public class ProductRetrieveQuery : IQuery<Dalyan.Entities.Models.Product>
{
public int Id { get; set; }
}

public class ProductRetrieveQueryHandler : IQueryHandler<ProductRetrieveQuery, Dalyan.Entities.Models.Product>
{
private readonly DbEntities _db;
public ProductRetrieveQueryHandler()
{
_db = new DbEntities();
}

public Dalyan.Entities.Models.Product Handler(ProductRetrieveQuery query)
{
try
{
var result = _db.Product.Include("Company").Where(x => x.IsDeleted == false && x.Id == query.Id).FirstOrDefault();
Mapper.CreateMap<Dalyan.Db.Company, Dalyan.Entities.Models.Company>();
Mapper.CreateMap<Dalyan.Db.Product, Dalyan.Entities.Models.Product>().ForMember(c => c.Company, d => d.MapFrom(s => s.Company));
return Mapper.Map<Dalyan.Db.Product, Dalyan.Entities.Models.Product>(result);

}
catch (Exception ex)
{
throw new ExceptionLog(LogType.DATABASE_SELECT, LogLevel.ERROR, ex, "RetrieveQueryHandler");
}
}
}

}

6. Generate Service with T4 template

Service Layer is your business side. It calls the Domain layer to access database.

Open Dalyan.Service -> Services -> ServiceQueryHandler.tt file and “run custom tools”

Your service code is ready.

//------------------------------------------------------------------------------
// <auto-generated>
// This code was generated from a template.
//
// Manual changes to this file may cause unexpected behavior in your application.
// Manual changes to this file will be overwritten if the code is regenerated.
// </auto-generated>
//------------------------------------------------------------------------------
namespace Dalyan.Service.Services
{

using System;
using SimpleInjector;
using Dalyan.Domain;
using Entities.Contracts;
using System.Linq;
using System.Text;
using Dalyan.Entities.Interfaces;
using Dalyan.Domain.Query;
using System.Data;
using System.Xml;
using Dalyan.Entities.Models;
using Dalyan.Entities.Enumerations;
using System.Threading.Tasks;
using System.Collections.Generic;

public class ProductService
{
private readonly Container _container;
public ProductService(Container container)
{
_container = container;
}

public ServiceResult<Product> Add(Product obj)
{
try
{
IMediator service = _container.GetInstance<IMediator>();
IUserContext currentUser = _container.GetInstance<IUserContext>();
obj.CreatedDate = DateTime.Now;
obj.CreatedUserId = currentUser.CurrentUserIdentity.UserID;
obj.CreatedIpAddress = currentUser.CurrentUserIdentity.IpAddress;
obj.UpdatedDate = DateTime.Now;
obj.UpdatedUserId = currentUser.CurrentUserIdentity.UserID;
obj.UpdatedIpAddress = currentUser.CurrentUserIdentity.IpAddress;
obj.IsDeleted = false;
var query = new ProductAddQuery();
query.Product = obj;
return new ServiceResult<Product>(service.Proccess(query), message: ClientErrorMessage.Success(), state: ServiceResultStates.SUCCESS);
}
catch(ExceptionLog ex)
{
LoggerService.Logger.Log(_container, ex);
return new ServiceResult<Product>(result: null, message: ClientErrorMessage.Error(), state: ServiceResultStates.ERROR);
}
}
public ServiceResult<Product> Edit(Product obj)
{
try
{
IMediator service = _container.GetInstance<IMediator>();
IUserContext currentUser = _container.GetInstance<IUserContext>();
var query = new ProductEditQuery();
obj.UpdatedDate = DateTime.Now;
obj.UpdatedUserId = currentUser.CurrentUserIdentity.UserID;
obj.UpdatedIpAddress = currentUser.CurrentUserIdentity.IpAddress;
query.Product = obj;
return new ServiceResult<Product>(service.Proccess(query), message: ClientErrorMessage.Success(), state: ServiceResultStates.SUCCESS);
}
catch(ExceptionLog ex)
{
LoggerService.Logger.Log(_container, ex);
return new ServiceResult<Product>(result: null, message: ClientErrorMessage.Error(), state: ServiceResultStates.ERROR);
}
}
public ServiceResult<Product> Retrieve(int Id)
{
try
{
IMediator service = _container.GetInstance<IMediator>();
var query = new ProductRetrieveQuery{ Id = Id };
return new ServiceResult<Product>(service.Proccess(query), message: ClientErrorMessage.Success(), state: ServiceResultStates.SUCCESS);
}
catch(ExceptionLog ex)
{
LoggerService.Logger.Log(_container, ex);
return new ServiceResult<Product>(result: null, message: ClientErrorMessage.Error(), state: ServiceResultStates.ERROR);
}
}
public ServiceResult<IList<Product>> GetAll()
{
try
{
IMediator service = _container.GetInstance<IMediator>();
var query = new ProductGetAllQuery();
return new ServiceResult<IList<Product>>(service.Proccess(query), message: ClientErrorMessage.Success(), state: ServiceResultStates.SUCCESS);
}
catch(ExceptionLog ex)
{
LoggerService.Logger.Log(_container, ex);
return new ServiceResult<IList<Product>>(result: null, message: ClientErrorMessage.Error(), state: ServiceResultStates.ERROR);
}
}
public ServiceResult<string> Delete(int Id)
{
try
{
IMediator service = _container.GetInstance<IMediator>();
var query = new ProductDeleteQuery{ Id = Id };
return new ServiceResult<string>(service.Proccess(query).ToString(), message: ClientErrorMessage.Success(), state: ServiceResultStates.SUCCESS);
}
catch(ExceptionLog ex)
{
LoggerService.Logger.Log(_container, ex);
return new ServiceResult<string>(result: null, message: ClientErrorMessage.Error(), state: ServiceResultStates.ERROR);
}
}
}

}

7. Generate WebAPI controller with T4 template

The last one is WebAPI controller layer. Your SPA access this layer to CRUD operations.
For authentication add [UserAuthorize] Attributes to your action. [UserAuthorize] authentication is default.

Open Dalyan.WebApi project -> Controllers -> ControllerService.tt file


//------------------------------------------------------------------------------
// <auto-generated>
// This code was generated from a template.
//
// Manual changes to this file may cause unexpected behavior in your application.
// Manual changes to this file will be overwritten if the code is regenerated.
// </auto-generated>
//------------------------------------------------------------------------------
namespace Dalyan.WebApi.Controllers
{

using System;
using SimpleInjector;
using Dalyan.Entities;
using System.Linq;
using System.Net;
using System.Web.Http;
using System.Net.Http;
using Dalyan.Entities.Models;
using Dalyan.Service;
using Entities.Contracts;
using Dalyan.WebApi.Attributes;
using Dalyan.Service.Services;
using System.Collections.Generic;

public class ProductController : ApiController
{
private readonly Container _container;
public ProductController(Container container)
: base()
{
_container = container;
}

[UserAuthorize]
[HttpPost]
public ServiceResult<Product> Add(Product obj)
{
ProductService service = new ProductService(_container);
return service.Add(obj);
}
[UserAuthorize]
[HttpPost]
public ServiceResult<Product> Edit(Product obj)
{
ProductService service = new ProductService(_container);
return service.Edit(obj);
}
[UserAuthorize]
[HttpPost]
public ServiceResult<Product> Retrieve(int Id)
{
ProductService service = new ProductService(_container);
return service.Retrieve(Id);
}
[UserAuthorize]
[HttpPost]
public ServiceResult<IList<Product>> GetAll()
{
ProductService service = new ProductService(_container);
return service.GetAll();
}
[UserAuthorize]
[HttpPost]
public ServiceResult<string> Delete(int Id)
{
ProductService service = new ProductService(_container);
return service.Delete(Id);
}
}

}

8. Check WebAPI actions

For testing add some records to database


GO
SET IDENTITY_INSERT [dbo].[Company] ON

GO
INSERT [dbo].[Company] ([Id], [Name], [IsDeleted]) VALUES (1, N'Apple', 0)
GO
INSERT [dbo].[Company] ([Id], [Name], [IsDeleted]) VALUES (2, N'Samsung', 0)
GO
SET IDENTITY_INSERT [dbo].[Company] OFF
GO
SET IDENTITY_INSERT [dbo].[Product] ON
GO
INSERT [dbo].[Product] ([Id], [CompanyId], [Name], [Description], [Price], [CreatedDate], [CreatedIpAddress], [CreatedUserId], [UpdatedDate], [UpdatedIpAddress], [UpdatedUserId], [IsDeleted]) VALUES (1, 1, N'IPhone 6s', N'The only thing that’s changed is everything.', 350, CAST(N'2016-01-22 00:00:00.000' AS DateTime), N'192.1681.1', 1, CAST(N'2016-01-22 00:00:00.000' AS DateTime), N'192.1681.1', 1, 0)
GO
INSERT [dbo].[Product] ([Id], [CompanyId], [Name], [Description], [Price], [CreatedDate], [CreatedIpAddress], [CreatedUserId], [UpdatedDate], [UpdatedIpAddress], [UpdatedUserId], [IsDeleted]) VALUES (2, 2, N'Samsung Galaxy S6 edge', N'Big screen. Big entertainment.', 300, CAST(N'2016-01-22 00:00:00.000' AS DateTime), N'192.1681.1', 1, CAST(N'2016-01-22 00:00:00.000' AS DateTime), N'192.1681.1', 1, 0)
GO
SET IDENTITY_INSERT [dbo].[Product] OFF
GO

You can test you api from SPA code or postman chrome extension

https://chrome.google.com/webstore/detail/postman/fhbjgbiflinjbdggehcddcbncdddomop

The result of out call

http://localhost/Dalyan.WebApi/Product/GetAll

{
"Result": [
{
"Id": 1,
"CompanyId": 1,
"Name": "IPhone 6s",
"Description": "The only thing that’s changed is everything.",
"Price": 350,
"CreatedDate": "2016-01-22T00:00:00",
"CreatedIpAddress": "192.1681.1",
"CreatedUserId": 1,
"UpdatedDate": "2016-01-22T00:00:00",
"UpdatedIpAddress": "192.1681.1",
"UpdatedUserId": 1,
"IsDeleted": false,
"Company": {
"Id": 1,
"Name": "Apple",
"IsDeleted": false
}
},
{
"Id": 2,
"CompanyId": 2,
"Name": "Samsung Galaxy S6 edge",
"Description": "Big screen. Big entertainment.",
"Price": 300,
"CreatedDate": "2016-01-22T00:00:00",
"CreatedIpAddress": "192.1681.1",
"CreatedUserId": 1,
"UpdatedDate": "2016-01-22T00:00:00",
"UpdatedIpAddress": "192.1681.1",
"UpdatedUserId": 1,
"IsDeleted": false,
"Company": {
"Id": 2,
"Name": "Samsung",
"IsDeleted": false
}
}
],
"ResultList": null,
"State": 0,
"Message": "Your transaction has been completed successfully.",
"PortalTask": null
}

-- Thank you
-- Ahmet AYDIN

Generate Web API CRUD Operations Automatic With T4 Template on Dalyan Project