Custom Code-Generator Using ASP.NET Core

The main thought behind the sample was to generate common types of code easily.

A Single Page Application is built with ASP.NET Core & AngularJS, here’s the execution environment & API service in .NET Core to generate the code. For front-end, I have used AngularJS.

Content Focused

  • Connect to the database server
  • Get all database lists
  • Get all Table Columns by selecting a particular table
  • Choose column then
  • Generate code, that’s it.

Let’s dive into the code, first let’s get started with back-end work. We have connect our database server using connection string.

Let's Work with Back-End, Connect to Database

Create an API Controller to get all our operation done. In our controller class, let’s put a connection string to get connected with our databases.

  1. private string conString = "server=DESKTOP-80DEJMQ; uid=sa; pwd=sa@12345;";  

Get All Databases

Let’s create a method to get all the database list from the database server. Below is the SQL query to get all databases.

SELECT name from sys.databases WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb') ORDER BY create_date

Here’s is the Get method to list all our databases in server.

  1. // api/Codegen/GetDatabaseList  
  2. [HttpGet, Route("GetDatabaseList"), Produces("application/json")]  
  3. public List<vmDatabase> GetDatabaseList()  
  4. {  
  5.     List<vmDatabase> data = new List<vmDatabase>();  
  6.     using (SqlConnection con = new SqlConnection(conString))  
  7.     {  
  8.         int count = 0; con.Open();  
  9.         using (SqlCommand cmd = new SqlCommand("SELECT name from sys.databases WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb') ORDER BY create_date", con))  
  10.         {  
  11.             using (IDataReader dr = cmd.ExecuteReader())  
  12.             {  
  13.                 while (dr.Read())  
  14.                 {  
  15.                     count++;  
  16.                     data.Add(new vmDatabase()  
  17.                     {  
  18.                         DatabaseId = count,  
  19.                         DatabaseName = dr[0].ToString()  
  20.                     });  
  21.                 }  
  22.             }  
  23.         }  
  24.     }  
  25.     return data.ToList();  
  26. }  

Get all Table Column

Here, we are getting all tables from selected database.

  1. // api/Codegen/GetDatabaseTableList  
  2. [HttpPost, Route("GetDatabaseTableList"), Produces("application/json")]  
  3. public List<vmTable> GetDatabaseTableList([FromBody]vmParam model)  
  4. {  
  5.     List<vmTable> data = new List<vmTable>();  
  6.     string conString_ = conString + " Database=" + model.DatabaseName + ";";  
  7.     using (SqlConnection con = new SqlConnection(conString_))  
  8.     {  
  9.         int count = 0; con.Open();  
  10.         DataTable schema = con.GetSchema("Tables");  
  11.         foreach (DataRow row in schema.Rows)  
  12.         {  
  13.             count++;  
  14.             data.Add(new vmTable()  
  15.             {  
  16.                 TableId = count,  
  17.                 TableName = row[2].ToString()  
  18.             });  
  19.         }  
  20.     }  
  21.   
  22.     return data.ToList();  
  23. }  

Choose Column

Below SQL query is used to get all table columns.

  1. SELECT COLUMN_NAME, DATA_TYPE, ISNULL(CHARACTER_MAXIMUM_LENGTH,0), IS_NULLABLE, TABLE_SCHEMA FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '" + model.TableName + "' ORDER BY ORDINAL_POSITION  

Here is the method to list all selected table columns in server. 

  1. // api/Codegen/GetDatabaseTableColumnList  
  2. [HttpPost, Route("GetDatabaseTableColumnList"), Produces("application/json")]  
  3. public List<vmColumn> GetDatabaseTableColumnList([FromBody]vmParam model)  
  4. {  
  5.     List<vmColumn> data = new List<vmColumn>();  
  6.     string conString_ = conString + " Database=" + model.DatabaseName + ";";  
  7.     using (SqlConnection con = new SqlConnection(conString_))  
  8.     {  
  9.         int count = 0; con.Open();  
  10.         using (SqlCommand cmd = new SqlCommand("SELECT COLUMN_NAME, DATA_TYPE, ISNULL(CHARACTER_MAXIMUM_LENGTH,0), IS_NULLABLE, TABLE_SCHEMA FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '" + model.TableName + "' ORDER BY ORDINAL_POSITION", con))  
  11.         {  
  12.             using (IDataReader dr = cmd.ExecuteReader())  
  13.             {  
  14.                 while (dr.Read())  
  15.                 {  
  16.                     count++;  
  17.                     data.Add(new vmColumn()  
  18.                     {  
  19.                         ColumnId = count,  
  20.                         ColumnName = dr[0].ToString(),  
  21.                         DataType = dr[1].ToString(),  
  22.                         MaxLength = dr[2].ToString(),  
  23.                         IsNullable = dr[3].ToString(),  
  24.                         Tablename = model.TableName.ToString(),  
  25.                         TableSchema = dr[4].ToString()  
  26.                     });  
  27.                 }  
  28.             }  
  29.         }  
  30.     }  
  31.     return data.ToList();  
  32. }  

Now, let’s get an overview of the server code that is producing the generated code based on our database column.

  1. [EnableCors("AllowCors"), Produces("application/json"), Route("api/Codegen")]  
  2. public class CodegenController : Controller  
  3. {  
  4.     private readonly IHostingEnvironment _hostingEnvironment;  
  5.     private string conString = "server=DESKTOP-80DEJMQ; uid=sa; pwd=sa@12345;";  
  6.   
  7.     public CodegenController(IHostingEnvironment hostingEnvironment)  
  8.     {  
  9.         _hostingEnvironment = hostingEnvironment;  
  10.     }  
  11.  
  12.     #region ++++++ Database +++++++  
  13.     // api/Codegen/GetDatabaseList  
  14.     [HttpGet, Route("GetDatabaseList"), Produces("application/json")]  
  15.     public List<vmDatabase> GetDatabaseList()  
  16.     {  
  17.         List<vmDatabase> data = new List<vmDatabase>();  
  18.         using (SqlConnection con = new SqlConnection(conString))  
  19.         {  
  20.             int count = 0; con.Open();  
  21.             using (SqlCommand cmd = new SqlCommand("SELECT name from sys.databases WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb') ORDER BY create_date", con))  
  22.             {  
  23.                 using (IDataReader dr = cmd.ExecuteReader())  
  24.                 {  
  25.                     while (dr.Read())  
  26.                     {  
  27.                         count++;  
  28.                         data.Add(new vmDatabase()  
  29.                         {  
  30.                             DatabaseId = count,  
  31.                             DatabaseName = dr[0].ToString()  
  32.                         });  
  33.                     }  
  34.                 }  
  35.             }  
  36.         }  
  37.         return data.ToList();  
  38.     }  
  39.   
  40.     // api/Codegen/GetDatabaseTableList  
  41.     [HttpPost, Route("GetDatabaseTableList"), Produces("application/json")]  
  42.     public List<vmTable> GetDatabaseTableList([FromBody]vmParam model)  
  43.     {  
  44.         List<vmTable> data = new List<vmTable>();  
  45.         string conString_ = conString + " Database=" + model.DatabaseName + ";";  
  46.         using (SqlConnection con = new SqlConnection(conString_))  
  47.         {  
  48.             int count = 0; con.Open();  
  49.             DataTable schema = con.GetSchema("Tables");  
  50.             foreach (DataRow row in schema.Rows)  
  51.             {  
  52.                 count++;  
  53.                 data.Add(new vmTable()  
  54.                 {  
  55.                     TableId = count,  
  56.                     TableName = row[2].ToString()  
  57.                 });  
  58.             }  
  59.         }  
  60.   
  61.         return data.ToList();  
  62.     }  
  63.   
  64.     // api/Codegen/GetDatabaseTableColumnList  
  65.     [HttpPost, Route("GetDatabaseTableColumnList"), Produces("application/json")]  
  66.     public List<vmColumn> GetDatabaseTableColumnList([FromBody]vmParam model)  
  67.     {  
  68.         List<vmColumn> data = new List<vmColumn>();  
  69.         string conString_ = conString + " Database=" + model.DatabaseName + ";";  
  70.         using (SqlConnection con = new SqlConnection(conString_))  
  71.         {  
  72.             int count = 0; con.Open();  
  73.             using (SqlCommand cmd = new SqlCommand("SELECT COLUMN_NAME, DATA_TYPE, ISNULL(CHARACTER_MAXIMUM_LENGTH,0), IS_NULLABLE, TABLE_SCHEMA FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '" + model.TableName + "' ORDER BY ORDINAL_POSITION", con))  
  74.             {  
  75.                 using (IDataReader dr = cmd.ExecuteReader())  
  76.                 {  
  77.                     while (dr.Read())  
  78.                     {  
  79.                         count++;  
  80.                         data.Add(new vmColumn()  
  81.                         {  
  82.                             ColumnId = count,  
  83.                             ColumnName = dr[0].ToString(),  
  84.                             DataType = dr[1].ToString(),  
  85.                             MaxLength = dr[2].ToString(),  
  86.                             IsNullable = dr[3].ToString(),  
  87.                             Tablename = model.TableName.ToString(),  
  88.                             TableSchema = dr[4].ToString()  
  89.                         });  
  90.                     }  
  91.                 }  
  92.             }  
  93.         }  
  94.         return data.ToList();  
  95.     }  
  96.     #endregion  
  97.  
  98.     #region +++++ CodeGeneration +++++  
  99.     // api/Codegen/GenerateCode  
  100.     [HttpPost, Route("GenerateCode"), Produces("application/json")]  
  101.     public IActionResult GenerateCode([FromBody]object[] data)  
  102.     {  
  103.         List<string> spCollection = new List<string>();  
  104.         try  
  105.         {  
  106.             string webRootPath = _hostingEnvironment.WebRootPath; //From wwwroot  
  107.             string contentRootPath = _hostingEnvironment.ContentRootPath; //From Others  
  108.   
  109.             var tblColumns = JsonConvert.DeserializeObject<List<vmColumn>>(data[0].ToString());  
  110.   
  111.             string fileContentSet = string.Empty; string fileContentGet = string.Empty;  
  112.             string fileContentPut = string.Empty; string fileContentDelete = string.Empty;  
  113.             string fileContentVm = string.Empty; string fileContentView = string.Empty;  
  114.             string fileContentNg = string.Empty; string fileContentAPIGet = string.Empty;  
  115.             string fileContentAPIGetById = string.Empty;  
  116.   
  117.             //SP  
  118.             fileContentSet = SpGenerator.GenerateSetSP(tblColumns, webRootPath);  
  119.             fileContentGet = SpGenerator.GenerateGetSP(tblColumns, webRootPath);  
  120.             fileContentPut = SpGenerator.GeneratePutSP(tblColumns, webRootPath);  
  121.             fileContentDelete = SpGenerator.GenerateDeleteSP(tblColumns, webRootPath);  
  122.             spCollection.Add(fileContentSet);  
  123.             spCollection.Add(fileContentGet);  
  124.             spCollection.Add(fileContentPut);  
  125.             spCollection.Add(fileContentDelete);  
  126.   
  127.             //VM  
  128.             fileContentVm = VmGenerator.GenerateVm(tblColumns, webRootPath);  
  129.             spCollection.Add(fileContentVm);  
  130.   
  131.             //VU  
  132.             fileContentView = ViewGenerator.GenerateForm(tblColumns, webRootPath);  
  133.             spCollection.Add(fileContentView);  
  134.   
  135.             //NG  
  136.             fileContentNg = NgGenerator.GenerateNgController(tblColumns, webRootPath);  
  137.             spCollection.Add(fileContentNg);  
  138.   
  139.             //API  
  140.             fileContentAPIGet = APIGenerator.GenerateAPIGet(tblColumns, webRootPath);  
  141.             spCollection.Add(fileContentAPIGet);  
  142.         }  
  143.         catch (Exception ex)  
  144.         {  
  145.             ex.ToString();  
  146.         }  
  147.   
  148.         return Json(new  
  149.         {  
  150.             spCollection  
  151.         });  
  152.     }  
  153.  
  154.     #endregion  
  155. }  

In our generation mechanism, the .txt template is used for representing the real structure. In the below folder, we have all the formatted template to present the real structure in generated code.

ASP.NET Core

Let’s expand the generator. Here’s the list that we have generated by this app

  • API Controller
  • ViewModel
  • AngularJS Controller
  • Stored Procedure
  • HTML Form

API-Generator

The below code method will generate the API Controller using a formatted text file.

  1. public static dynamic GenerateAPIGet(List<vmColumn> tblColumns, string contentRootPath)  
  2. {  
  3.     TextInfo textInfo = new CultureInfo("en-US"false).TextInfo;  
  4.     StringBuilder builderPrm = new StringBuilder();  
  5.     StringBuilder builderSub = new StringBuilder();  
  6.     builderPrm.Clear(); builderSub.Clear();  
  7.     string fileContent = string.Empty; string queryPrm = string.Empty; string submitPrm = string.Empty;  
  8.   
  9.     string tableName = tblColumns[0].Tablename; string tableSchema = tblColumns[0].TableSchema;  
  10.     string path = @"" + contentRootPath + "\\template\\WebAPI\\APIController.txt";  
  11.   
  12.     //Api Controller  
  13.     string routePrefix = "api/" + textInfo.ToTitleCase(Conversion.RemoveSpecialCharacters(tableName.ToString()));  
  14.     string apiController = textInfo.ToTitleCase(Conversion.RemoveSpecialCharacters(tableName.ToString())) + "Controller";  
  15.     string collectionName = "List<" + tableName.ToString() + ">";  
  16.     string listObj = tableName.ToString() + "s";  
  17.     string getDbMethod = "_ctx." + tableName.ToString() + ".ToListAsync()";  
  18.     string entity = tableName.ToString();  
  19.     string urlApiGet = "api/" + textInfo.ToTitleCase(Conversion.RemoveSpecialCharacters(tableName.ToString())) + "/GetAll";  
  20.     string urlApiGetByID = "api/" + textInfo.ToTitleCase(Conversion.RemoveSpecialCharacters(tableName.ToString())) + "/GetByID/5";  
  21.     string urlApiPost = "api/" + textInfo.ToTitleCase(Conversion.RemoveSpecialCharacters(tableName.ToString())) + "/Save";  
  22.     string urlApiPut = "api/" + textInfo.ToTitleCase(Conversion.RemoveSpecialCharacters(tableName.ToString())) + "/UpdateByID/5";  
  23.     string urlApiDeleteByID = "api/" + textInfo.ToTitleCase(Conversion.RemoveSpecialCharacters(tableName.ToString())) + "/DeleteByID/5";  
  24.   
  25.     //Enity Fields  
  26.     foreach (var item in tblColumns)  
  27.     {  
  28.         //parameter  
  29.         builderPrm.AppendLine();  
  30.         builderPrm.Append("                        entityUpdate." + item.ColumnName + " = model." + item.ColumnName + ";");  
  31.     }  
  32.     submitPrm = builderPrm.AppendLine().ToString();  
  33.   
  34.     using (StreamReader sr = new StreamReader(path, Encoding.UTF8))  
  35.     {  
  36.         fileContent = sr.ReadToEnd()  
  37.             .Replace("#RoutePrefix", routePrefix.ToString())  
  38.             .Replace("#APiController", apiController.ToString())  
  39.             .Replace("#Collection", collectionName.ToString())  
  40.             .Replace("#ListObj", listObj.ToString())  
  41.             .Replace("#DbMethod", getDbMethod.ToString())  
  42.             .Replace("#Entity", entity.ToString())  
  43.             .Replace("#UrlApiGet", urlApiGet.ToString())  
  44.             .Replace("#UrlGetByID", urlApiGetByID.ToString())  
  45.             .Replace("#UrlPostByID", urlApiPost.ToString())  
  46.             .Replace("#UrlApiPut", urlApiPut.ToString())  
  47.             .Replace("#ColUpdate", submitPrm.ToString())  
  48.             .Replace("#UrlDeleteByID", urlApiDeleteByID.ToString());  
  49.     }  
  50.   
  51.     return fileContent.ToString();  
  52. }  

API Controller Format

In this portion we have read the text as it is till the end line then replaced those #tag properties using C# dynamically. This is the main trick that used in code generation. 

  1. [Route("#RoutePrefix"), Produces("application/json")]  
  2. public class #APiController : Controller  
  3. {  
  4.     private dbContext _ctx = null;  
  5.     public #APiController(dbContext context)  
  6.     {  
  7.         _ctx = context;  
  8.     }  
  9.   
  10.     // GET: #UrlApiGet  
  11.     [HttpGet, Route("GetAll")]  
  12.     public async Task<object> GetAll()  
  13.     {  
  14.         #Collection #ListObj = null;  
  15.         try  
  16.         {  
  17.             using (_ctx)  
  18.             {  
  19.                 #ListObj = await #DbMethod;  
  20.             }  
  21.         }  
  22.         catch (Exception ex)  
  23.         {  
  24.             ex.ToString();  
  25.         }  
  26.         return #ListObj;  
  27.     }  
  28.   
  29.     // GET #UrlGetByID  
  30.     [HttpGet, Route("GetByID/{id}")]  
  31.     public async Task<#Entity> GetByID(int id)  
  32.     {  
  33.         #Entity obj = null;  
  34.         try  
  35.         {  
  36.             using (_ctx)  
  37.             {  
  38.                 obj = await _ctx.#Entity.FirstOrDefaultAsync(x => x.Id == id);  
  39.             }  
  40.         }  
  41.         catch (Exception ex)  
  42.         {  
  43.             ex.ToString();  
  44.         }  
  45.         return obj;  
  46.     }  
  47.   
  48.   
  49.     // POST #UrlPostByID   
  50.     [HttpPost, Route("Save")]  
  51.     public async Task<object> Save([FromBody]#Entity model)  
  52.     {  
  53.         object result = null; string message = "";  
  54.         if (model == null)  
  55.         {  
  56.             return BadRequest();  
  57.         }  
  58.         using (_ctx)  
  59.         {  
  60.             using (var _ctxTransaction = _ctx.Database.BeginTransaction())  
  61.             {  
  62.                 try  
  63.                 {  
  64.                     _ctx.#Entity.Add(model);  
  65.                     await _ctx.SaveChangesAsync();  
  66.                     _ctxTransaction.Commit();  
  67.                     message = "Ok";  
  68.                 }  
  69.                 catch (Exception e)  
  70.                 {  
  71.                     _ctxTransaction.Rollback();  
  72.                     e.ToString();  
  73.                     message = "Error";  
  74.                 }  
  75.   
  76.                 result = new  
  77.                 {  
  78.                     message  
  79.                 };  
  80.             }  
  81.         }  
  82.         return result;  
  83.     }  
  84.   
  85.     // PUT #UrlApiPut   
  86.     [HttpPut, Route("UpdateByID/{id}")]  
  87.     public async Task<object> UpdateByID(int id, [FromBody]#Entity model)  
  88.     {  
  89.         object result = null; string message = string.Empty;  
  90.         if (model == null)  
  91.         {  
  92.             return BadRequest();  
  93.         }  
  94.         using (_ctx)  
  95.         {  
  96.             using (var _ctxTransaction = _ctx.Database.BeginTransaction())  
  97.             {  
  98.                 try  
  99.                 {  
  100.                     var entityUpdate = _ctx.#Entity.FirstOrDefault(x => x.Id == id);  
  101.                     if (entityUpdate != null)  
  102.                     {  
  103.                         #ColUpdate  
  104.   
  105.                         await _ctx.SaveChangesAsync();  
  106.                     }  
  107.                     _ctxTransaction.Commit();  
  108.                     message = "Ok";  
  109.                 }  
  110.                 catch (Exception e)  
  111.                 {  
  112.                     _ctxTransaction.Rollback(); e.ToString();  
  113.                     message = "Error";  
  114.                 }  
  115.   
  116.                 result = new  
  117.                 {  
  118.                     message  
  119.                 };  
  120.             }  
  121.         }  
  122.         return result;  
  123.     }  
  124.   
  125.     // DELETE #UrlDeleteByID  
  126.     [HttpDelete, Route("DeleteByID/{id}")]  
  127.     public async Task<object> DeleteByID(int id)  
  128.     {  
  129.         object result = null; string message = "";  
  130.         using (_ctx)  
  131.         {  
  132.             using (var _ctxTransaction = _ctx.Database.BeginTransaction())  
  133.             {  
  134.                 try  
  135.                 {  
  136.                     var idToRemove = _ctx.#Entity.SingleOrDefault(x => x.Id == id);  
  137.                     if (idToRemove != null)  
  138.                     {  
  139.                         _ctx.#Entity.Remove(idToRemove);  
  140.                         await _ctx.SaveChangesAsync();  
  141.                     }  
  142.                     _ctxTransaction.Commit();  
  143.                     message = "Ok";  
  144.                 }  
  145.                 catch (Exception e)  
  146.                 {  
  147.                     _ctxTransaction.Rollback();   
  148.                     e.ToString();  
  149.                     message = "Error";  
  150.                 }  
  151.   
  152.                 result = new  
  153.                 {  
  154.                     message  
  155.                 };  
  156.             }  
  157.         }  
  158.         return result;  
  159.     }  
  160. }  

ViewModel-Generator

For a specific data model we need to generate some view models. The below method will generate those data models.

  1. public static dynamic GenerateVm(List<vmColumn> tblColumns, string contentRootPath)  
  2. {  
  3.     StringBuilder builderPrm = new StringBuilder();  
  4.     StringBuilder builderBody = new StringBuilder();  
  5.     builderPrm.Clear(); builderBody.Clear();  
  6.     string fileContent = string.Empty; string queryPrm = string.Empty;  
  7.   
  8.     string tableName = tblColumns[0].Tablename; string tableSchema = tblColumns[0].TableSchema;  
  9.     string path = @"" + contentRootPath + "\\template\\ViewModel\\vmModel.txt";  
  10.     string className = "vm" + tableName.ToString();  
  11.     foreach (var item in tblColumns)  
  12.     {  
  13.         //parameter  
  14.         builderPrm.AppendLine();  
  15.         builderPrm.Append("  public " + TypeMap.GetClrType(item.DataType) + " " + item.ColumnName + " { get; set; }");  
  16.     }  
  17.   
  18.     queryPrm = builderPrm.AppendLine().ToString();  
  19.   
  20.     using (StreamReader sr = new StreamReader(path, Encoding.UTF8))  
  21.     {  
  22.         fileContent = sr.ReadToEnd().Replace("#ClassName", className.ToString()).Replace("#Properties", queryPrm.ToString());  
  23.     }  
  24.   
  25.     return fileContent.ToString();  
  26. }  

Stored Procedure-Generator

Insert-SP

This is a minimum label sp generated query template for an insert operation. In the application, we need to modify those with our own additional logic.

  1. public static dynamic GenerateSetSP(List<vmColumn> tblColumns, string contentRootPath)  
  2. {  
  3.     StringBuilder builderPrm = new StringBuilder();  
  4.     StringBuilder builderBody = new StringBuilder();  
  5.     builderPrm.Clear(); builderBody.Clear();  
  6.   
  7.     string path = @"" + contentRootPath + "\\template\\StoredProcedure\\InsertSP.txt";  
  8.     string fileContent = string.Empty; string fileld = string.Empty; string fileldPrm = string.Empty; string queryPrm = string.Empty;  
  9.     string tableName = tblColumns[0].Tablename; string tableSchema = tblColumns[0].TableSchema;  
  10.   
  11.     string spName = ("[" + tableSchema + "].[Set_" + tableName + "]").ToString();  
  12.     foreach (var item in tblColumns)  
  13.     {  
  14.         fileld = fileld + item.ColumnName + ",";  
  15.         fileldPrm = fileldPrm + "@" + item.ColumnName + ",";  
  16.   
  17.         //parameter  
  18.         builderPrm.AppendLine();  
  19.         if ((item.DataType.ToString() == "nvarchar") || (item.DataType.ToString() == "varchar"))  
  20.             builderPrm.Append("  @" + item.ColumnName + " " + item.DataType + "(" + item.MaxLength + "),");  
  21.         else  
  22.             builderPrm.Append("  @" + item.ColumnName + " " + item.DataType + ",");  
  23.     }  
  24.   
  25.     queryPrm = builderPrm.Remove((builderPrm.Length - 1), 1).AppendLine().ToString();  
  26.     //queryPrm = builderPrm.ToString().TrimEnd(',');  
  27.   
  28.     //Body  
  29.     builderBody.Append("INSERT INTO [" + tableSchema + "].[" + tableName + "](");  
  30.     builderBody.Append(fileld.TrimEnd(',') + ") ");  
  31.     //builderBody.AppendLine();  
  32.     builderBody.Append("VALUES (" + fileldPrm.TrimEnd(',') + ")");  
  33.   
  34.     using (StreamReader sr = new StreamReader(path, Encoding.UTF8))  
  35.     {  
  36.         fileContent = sr.ReadToEnd().Replace("#Name", spName.ToString()).Replace("#Param", queryPrm.ToString()).Replace("#Body", builderBody.ToString());  
  37.     }  
  38.   
  39.     return fileContent.ToString();  
  40. }  

Get-SP

This is a minimum label sp generated query template for get operations, in the application we need to modify those with our own additional logic.

  1. public static dynamic GenerateGetSP(List<vmColumn> tblColumns, string contentRootPath)  
  2. {  
  3.     StringBuilder builderPrm = new StringBuilder();  
  4.     StringBuilder builderBody = new StringBuilder();  
  5.     builderPrm.Clear(); builderBody.Clear();  
  6.   
  7.     string path = @"" + contentRootPath + "\\template\\StoredProcedure\\ReadSP.txt";  
  8.     string fileContent = string.Empty; string fileld = string.Empty; string fileldPrm = string.Empty; string queryPrm = string.Empty;  
  9.     string tableName = tblColumns[0].Tablename; string tableSchema = tblColumns[0].TableSchema;  
  10.   
  11.     string spName = ("[" + tableSchema + "].[Get_" + tableName + "]").ToString();  
  12.     foreach (var item in tblColumns)  
  13.     {  
  14.         fileld = fileld + item.ColumnName + ",";  
  15.         fileldPrm = fileldPrm + "@" + item.ColumnName + ",";  
  16.     }  
  17.   
  18.   
  19.     //Body  
  20.     builderBody.Append("SELECT " + fileldPrm.TrimEnd(',') + " FROM [" + tableSchema + "].[" + tableName + "]");  
  21.   
  22.     using (StreamReader sr = new StreamReader(path, Encoding.UTF8))  
  23.     {  
  24.         fileContent = sr.ReadToEnd().Replace("#Name", spName.ToString()).Replace("#Body", builderBody.ToString()).Replace("#OrdPrm", fileldPrm.TrimEnd(',').ToString());  
  25.     }  
  26.   
  27.     return fileContent.ToString();  
  28. }  

Update-SP

This is a minimum label sp generated query template for update operations, in the application we need to modify those with our own additional logic.

  1. public static dynamic GeneratePutSP(List<vmColumn> tblColumns, string contentRootPath)  
  2. {  
  3.     StringBuilder builderPrm = new StringBuilder();  
  4.     StringBuilder builderBody = new StringBuilder();  
  5.     builderPrm.Clear(); builderBody.Clear();  
  6.   
  7.     string path = @"" + contentRootPath + "\\template\\StoredProcedure\\UpdateSP.txt";  
  8.     string fileContent = string.Empty; string fileld = string.Empty; string fileldPrm = string.Empty; string queryPrm = string.Empty;  
  9.     string tableName = tblColumns[0].Tablename; string tableSchema = tblColumns[0].TableSchema;  
  10.   
  11.     string spName = ("[" + tableSchema + "].[Get_" + tableName + "]").ToString();  
  12.     foreach (var item in tblColumns)  
  13.     {  
  14.         fileld = fileld + item.ColumnName + ",";  
  15.         fileldPrm = fileldPrm + item.ColumnName + " = @" + item.ColumnName + ",";  
  16.   
  17.         //parameter  
  18.         builderPrm.AppendLine();  
  19.         if ((item.DataType.ToString() == "nvarchar") || (item.DataType.ToString() == "varchar"))  
  20.             builderPrm.Append("  @" + item.ColumnName + " " + item.DataType + "(" + item.MaxLength + "),");  
  21.         else  
  22.             builderPrm.Append("  @" + item.ColumnName + " " + item.DataType + ",");  
  23.     }  
  24.   
  25.     queryPrm = builderPrm.Remove((builderPrm.Length - 1), 1).AppendLine().ToString();  
  26.   
  27.     //Body  
  28.     builderBody.Append("UPDATE [" + tableSchema + "].[" + tableName + "] SET " + fileldPrm.TrimEnd(',') + " WHERE [CONDITIONS]");  
  29.   
  30.     using (StreamReader sr = new StreamReader(path, Encoding.UTF8))  
  31.     {  
  32.         fileContent = sr.ReadToEnd().Replace("#Name", spName.ToString()).Replace("#Param", queryPrm.ToString()).Replace("#Body", builderBody.ToString()).Replace("#OrdPrm", fileldPrm.ToString());  
  33.     }  
  34.   
  35.     return fileContent.ToString();  
  36. }  

Delete-SP

This is a minimum label sp generated query template for delete operations, in the application we need to modify those with our own additional logic.

  1. public static dynamic GenerateDeleteSP(List<vmColumn> tblColumns, string contentRootPath)  
  2. {  
  3.     StringBuilder builderPrm = new StringBuilder();  
  4.     StringBuilder builderBody = new StringBuilder();  
  5.     builderPrm.Clear(); builderBody.Clear();  
  6.   
  7.     string path = @"" + contentRootPath + "\\template\\StoredProcedure\\DeleteSP.txt";  
  8.     string fileContent = string.Empty; string fileld = string.Empty; string fileldPrm = string.Empty; string queryPrm = string.Empty;  
  9.     string tableName = tblColumns[0].Tablename; string tableSchema = tblColumns[0].TableSchema;  
  10.   
  11.     string spName = ("[" + tableSchema + "].[Delete_" + tableName + "]").ToString();  
  12.     foreach (var item in tblColumns)  
  13.     {  
  14.         fileld = fileld + item.ColumnName + ",";  
  15.         fileldPrm = fileldPrm + "@" + item.ColumnName + ",";  
  16.   
  17.         //parameter  
  18.         builderPrm.AppendLine();  
  19.         if ((item.DataType.ToString() == "nvarchar") || (item.DataType.ToString() == "varchar"))  
  20.             builderPrm.Append("  @" + item.ColumnName + " " + item.DataType + "(" + item.MaxLength + "),");  
  21.         else  
  22.             builderPrm.Append("  @" + item.ColumnName + " " + item.DataType + ",");  
  23.     }  
  24.   
  25.     queryPrm = builderPrm.Remove((builderPrm.Length - 1), 1).AppendLine().ToString();  
  26.   
  27.     //Body  
  28.     builderBody.Append("DELETE FROM [" + tableSchema + "].[" + tableName + "] WHERE [CONDITIONS]");  
  29.   
  30.     using (StreamReader sr = new StreamReader(path, Encoding.UTF8))  
  31.     {  
  32.         fileContent = sr.ReadToEnd().Replace("#Name", spName.ToString()).Replace("#Param", queryPrm.ToString()).Replace("#Body", builderBody.ToString()).Replace("#OrdPrm", fileldPrm.ToString());  
  33.     }  
  34.   
  35.     return fileContent.ToString();  
  36. }  

Let’s Work with Front-End, AngularJS Controller-Generator

In our frontend we also used the same mechanism that is used to generate API Controller. The below method is generating AngularJS Controller code.

  1. public static dynamic GenerateNgController(List<vmColumn> tblColumns, string contentRootPath)  
  2. {  
  3.     TextInfo textInfo = new CultureInfo("en-US"false).TextInfo;  
  4.     StringBuilder builderPrm = new StringBuilder();  
  5.     StringBuilder builderSub = new StringBuilder();  
  6.     builderPrm.Clear(); builderSub.Clear();  
  7.     string fileContent = string.Empty; string queryPrm = string.Empty; string submitPrm = string.Empty;  
  8.   
  9.     string tableName = tblColumns[0].Tablename; string tableSchema = tblColumns[0].TableSchema;  
  10.     string path = @"" + contentRootPath + "\\template\\AngularJS\\Controller.txt";  
  11.   
  12.     //Controller Name  
  13.     string ctrlName = textInfo.ToTitleCase(Conversion.RemoveSpecialCharacters(tableName.ToString())) + "Controller";  
  14.     string serviceInjected = "'$scope', '$http'"; string srvParam = "$scope, $http";  
  15.     string urlApiGet = "'/api/" + textInfo.ToTitleCase(Conversion.RemoveSpecialCharacters(tableName.ToString())) + "/GetAll'";  
  16.     string url_GetByID = "'/api/" + textInfo.ToTitleCase(Conversion.RemoveSpecialCharacters(tableName.ToString())) + "/GetByID/'+ parseInt(model.id)";  
  17.     string url_Post = "'/api/" + textInfo.ToTitleCase(Conversion.RemoveSpecialCharacters(tableName.ToString())) + "/Save'";  
  18.     string url_Put = "'/api/" + textInfo.ToTitleCase(Conversion.RemoveSpecialCharacters(tableName.ToString())) + "/UpdateByID/'+ parseInt(model.id)";  
  19.     string url_Delete = "'/api/" + textInfo.ToTitleCase(Conversion.RemoveSpecialCharacters(tableName.ToString())) + "/DeleteByID/'+ parseInt(model.id)";  
  20.   
  21.     using (StreamReader sr = new StreamReader(path, Encoding.UTF8))  
  22.     {  
  23.         fileContent = sr.ReadToEnd()  
  24.             .Replace("#ControllerName", ctrlName.ToString())  
  25.             .Replace("#ServiceInjected", serviceInjected.ToString())  
  26.             .Replace("#SrvParam", srvParam.ToString())  
  27.             .Replace("#UrlGet", urlApiGet.ToString())  
  28.             .Replace("#Url_GetByID", url_GetByID.ToString())  
  29.             .Replace("#Url_Post", url_Post.ToString())  
  30.             .Replace("#Url_Put", url_Put.ToString())  
  31.             .Replace("#Url_Delete", url_Delete.ToString());  
  32.     }  
  33.   
  34.     return fileContent.ToString();  
  35. }  

HTML-Generator (Form)

Using the below method we have generated an Html form that's embedded with an Angular tag.

  1. public static dynamic GenerateForm(List<vmColumn> tblColumns, string contentRootPath)  
  2. {  
  3.     TextInfo textInfo = new CultureInfo("en-US"false).TextInfo;  
  4.     StringBuilder builderPrm = new StringBuilder();  
  5.     StringBuilder builderSub = new StringBuilder();  
  6.     builderPrm.Clear(); builderSub.Clear();  
  7.     string fileContent = string.Empty; string queryPrm = string.Empty; string submitPrm = string.Empty;  
  8.   
  9.     string tableName = tblColumns[0].Tablename; string tableSchema = tblColumns[0].TableSchema;  
  10.     string path = @"" + contentRootPath + "\\template\\HtmlForm\\Form.txt";  
  11.   
  12.     //Form Name  
  13.     string frmName = "name='frm" + tableName.ToString() + "' novalidate";  
  14.   
  15.     //Form Fields  
  16.     foreach (var item in tblColumns)  
  17.     {  
  18.         //parameter  
  19.         builderPrm.AppendLine();  
  20.         builderPrm.Append(" <div class='form-group'>");  
  21.         builderPrm.AppendLine();  
  22.         if (item.ColumnName.Contains("email") || item.ColumnName.Contains("Email"))  
  23.         {  
  24.             builderPrm.Append("  <label for='" + item.ColumnName + "' class='control-label'>" + textInfo.ToTitleCase(Conversion.RemoveSpecialCharacters(item.ColumnName)) + "</label>");  
  25.             builderPrm.AppendLine();  
  26.             builderPrm.Append("  <input type='email' class='form-control' ng-model='vmfrm." + item.ColumnName + "' name='" + item.ColumnName + "' required />");  
  27.         }  
  28.         else if (item.ColumnName.Contains("password") || item.ColumnName.Contains("Password"))  
  29.         {  
  30.             builderPrm.Append("  <label for='" + item.ColumnName + "' class='control-label'>" + textInfo.ToTitleCase(Conversion.RemoveSpecialCharacters(item.ColumnName)) + "</label>");  
  31.             builderPrm.AppendLine();  
  32.             builderPrm.Append("  <input type='password' class='form-control' ng-model='vmfrm." + item.ColumnName + "' name='" + item.ColumnName + "' required />");  
  33.         }  
  34.         else  
  35.         {  
  36.             builderPrm.Append("  <label for='" + item.ColumnName + "' class='control-label'>" + textInfo.ToTitleCase(Conversion.RemoveSpecialCharacters(item.ColumnName)) + "</label>");  
  37.             builderPrm.AppendLine();  
  38.             builderPrm.Append("  <input type='text' class='form-control' ng-model='vmfrm." + item.ColumnName + "' name='" + item.ColumnName + "' required />");  
  39.         }  
  40.         builderPrm.AppendLine();  
  41.         builderPrm.Append(" </div>");  
  42.     }  
  43.     queryPrm = builderPrm.AppendLine().ToString();  
  44.   
  45.     //Form Submit  
  46.     builderSub.Append(" <div class='form-group'>");  
  47.     builderSub.AppendLine();  
  48.     builderSub.Append("  <input type='submit' name='reset' value='Reset' ng-click='Reset()' />");  
  49.     builderSub.AppendLine();  
  50.     builderSub.Append("  <input type='submit' name='update' value='Update' ng-click='Update()' />");  
  51.     builderSub.AppendLine();  
  52.     builderSub.Append("  <input type='submit' name='submit' value='Save' ng-click='Save()' />");  
  53.     builderSub.AppendLine();  
  54.     builderSub.Append(" </div>");  
  55.   
  56.     submitPrm = builderSub.AppendLine().ToString();  
  57.   
  58.     using (StreamReader sr = new StreamReader(path, Encoding.UTF8))  
  59.     {  
  60.         fileContent = sr.ReadToEnd().Replace("#frmName", frmName.ToString()).Replace("#frmGroup", queryPrm.ToString()).Replace("#frmSubmit", submitPrm.ToString());  
  61.     }  
  62.   
  63.     return fileContent.ToString();  
  64. }  

Generated Code

We have used multiple tab to represent all the generated code. Below is our HTML section.

  1. <div class="tab-struct custom-tab-2 mt-0">  
  2.     <ul role="tablist" class="nav nav-tabs" id="myTabs_15">  
  3.         <li class="active" role="presentation">  
  4.             <a data-toggle="tab" id="views_tab_1" role="tab" href="#views" aria-expanded="false">HTML Views</a>  
  5.         </li>  
  6.         <li role="presentation" class="">  
  7.             <a data-toggle="tab" id="angular_tab_1" role="tab" href="#angular" aria-expanded="false">AngularJS</a>  
  8.         </li>  
  9.         <li role="presentation" class="">  
  10.             <a data-toggle="tab" id="csharp_tab_1" role="tab" href="#csharp" aria-expanded="false">WebAPI</a>  
  11.         </li>  
  12.         <li role="presentation" class="">  
  13.             <a data-toggle="tab" id="model_tab_1" role="tab" href="#model" aria-expanded="false">Model</a>  
  14.         </li>  
  15.         <li class="" role="presentation">  
  16.             <a aria-expanded="true" data-toggle="tab" role="tab" id="sql_tab_1" href="#sql">SQL</a>  
  17.         </li>  
  18.     </ul>  
  19.     <div class="tab-content" id="myTabContent_15">  
  20.         <div id="views" class="tab-pane fade  active in" role="tabpanel" style="max-height:450px; overflow-y: auto;">  
  21.             <pre class="pa-10" lang="html"><xmp id="genCodeVu"></xmp></pre>  
  22.         </div>  
  23.         <div id="angular" class="tab-pane fade" role="tabpanel" style="max-height:450px; overflow-y: auto;">  
  24.             <pre class="pa-10" lang="sql"><xmp id="genCodeAngular"></xmp></pre>  
  25.         </div>  
  26.         <div id="csharp" class="tab-pane fade" role="tabpanel" style="max-height:450px; overflow-y: auto;">  
  27.             <pre class="pa-10" lang="sql"><xmp id="genCodeAPI"></xmp></pre>  
  28.         </div>  
  29.         <div id="model" class="tab-pane fade" role="tabpanel" style="max-height:450px; overflow-y: auto;">  
  30.             <pre class="pa-10" lang="cs"><xmp id="genCodeVm"></xmp></pre>  
  31.         </div>  
  32.         <div id="sql" class="tab-pane fade" role="tabpanel" style="max-height:450px; overflow-y: auto;">  
  33.             <pre class="pa-10" lang="sql"><xmp id="genCodeSql"></xmp></pre>  
  34.         </div>  
  35.     </div>  
  36. </div>  

We have use pre-formated & HTML Example Element tag in html to present the generated code.

  1. <pre class="pa-10" lang="html"><xmp id="genCodeVu"></xmp></pre>  

Here's the code generation frontend function that is interacting with API's, then getting the generated content which is represented by writing the DOM in Html page.

  1. $scope.generate = function () {  
  2.     $('.nav-tabs a[href="#views"]').tab('show');  
  3.   
  4.     var rowGen = [];  
  5.     var elementIDSql = 'genCodeSql';  
  6.     var elementIDVm = 'genCodeVm';  
  7.     var elementIDVu = 'genCodeVu';  
  8.     var elementIDNg = 'genCodeAngular';  
  9.     var elementIDApi = 'genCodeAPI';  
  10.   
  11.     if ($scope.collist.length > 0) {  
  12.         var models = "[" + JSON.stringify($scope.collist) + "]";  
  13.   
  14.         $http({  
  15.             method: 'POST',  
  16.             url: '/api/Codegen/GenerateCode',  
  17.             data: models,  
  18.             dataType: "json",  
  19.             contentType: 'application/json; charset=utf-8'  
  20.         }).then(function (response) {  
  21.   
  22.             $('#genCodeSql').text(''); $('#genCodeVm').text(''); $('#genCodeVu').text(''); $('#genCodeAngular').text(''); $('#genCodeAPI').text('');  
  23.             rowGen = response.data.spCollection;  
  24.   
  25.             if (rowGen.length > 0) {  
  26.                 for (var i = 0; i < rowGen.length; i++) {  
  27.                     //SP  
  28.                     if (i == 0)  
  29.                         document.getElementById(elementIDSql).innerHTML += "--+++++++++ SET SP +++++++ \r\n" + rowGen[i] + "\r\n";  
  30.                     else if (i == 1)  
  31.                         document.getElementById(elementIDSql).innerHTML += "--+++++++++ GET SP +++++++++ \r\n" + rowGen[i] + "\r\n";  
  32.                     else if (i == 2)  
  33.                         document.getElementById(elementIDSql).innerHTML += "--+++++++++ PUT SP +++++++++ \r\n" + rowGen[i] + "\r\n";  
  34.                     else if (i == 3)  
  35.                         document.getElementById(elementIDSql).innerHTML += "--+++++++++ DELETE SP +++++++++ \r\n" + rowGen[i] + "\r\n";  
  36.                     //VM  
  37.                     else if (i == 4)  
  38.                         document.getElementById(elementIDVm).innerHTML += "// +++++++++ MODEL PROPERTIES +++++++++ \r\n" + rowGen[i] + "\r\n";  
  39.                     //VIEW  
  40.                     else if (i == 5)  
  41.                         document.getElementById(elementIDVu).innerHTML += "<!-- +++++++++ HTML FORM +++++++++ --> \r\n" + rowGen[i] + "\r\n";  
  42.                     //ANGULAR  
  43.                     else if (i == 6)  
  44.                         document.getElementById(elementIDNg).innerHTML += "// +++++++++ AngularJS Controller +++++++++ \r\n" + rowGen[i] + "\r\n";  
  45.                     //API  
  46.                     else if (i == 7)  
  47.                         document.getElementById(elementIDApi).innerHTML += "// +++++++++ API Controller +++++++++ \r\n" + rowGen[i] + "\r\n";  
  48.                     else  
  49.                         document.getElementById(elementIDSql).innerHTML += " Error !!";  
  50.                 };  
  51.             };  
  52.         }, function (error) {  
  53.             console.log(error);  
  54.         });  
  55.     }  
  56.     else {  
  57.         rowGen = []; $('#genCodeSql').text(''); $('#genCodeVm').text('');  
  58.         console.log("Please Choose a Column!!");  
  59.     };  
  60. };  

OutPut

As you can see the final output in the below screen. 

output

Source Code


I’ve uploaded the full source code to download/clone @github. Hope this will help. 

X

Build smarter apps with Machine Learning, Bots, Cognitive Services - Start free.

Start Learning Now