/// <summary> /// Tạo các class Model, lưu trong thư mục DATA_MODEL_FOLDER_NAME. /// </summary> public static void GenerateModel() { Directory.CreateDirectory(ROOT_PATH + "\\" + DATA_ACCESS_FOLDER_NAME + "\\" + DATA_MODEL_FOLDER_NAME + "\\" + DATABASE_NAME); StringBuilder sb = new StringBuilder(); foreach (Table table in CrudUtilities.LIST_TABLE.Where(m => m.Active == true)) { string modelName = table.TableName; sb.AppendLine("using System;"); sb.AppendLine("using static CrudCoreSystem.CustomAttribute;"); sb.AppendLine(""); sb.AppendLine("namespace " + DATA_ACCESS_FOLDER_NAME + "." + DATA_MODEL_FOLDER_NAME + "." + DATABASE_NAME); sb.AppendLine("{"); sb.AppendLine(" public class " + modelName); sb.AppendLine(" {"); foreach (TableColumn column in table.TableColumn) { if (CrudUtilities.IsIdentityColumn(column)) { sb.AppendLine(" [IdentityField]"); sb.AppendLine(" public int? " + column.ColumnName + " { get; set; }"); } else if (CrudUtilities.IsIntDataTypeColumn(column)) { sb.AppendLine(" public int? " + column.ColumnName + " { get; set; }"); } else if (CrudUtilities.IsCharDataTypeColumn(column)) { sb.AppendLine(" public string " + column.ColumnName + " { get; set; }"); } else if (CrudUtilities.IsDateDataTypeColumn(column)) { sb.AppendLine(" public DateTime? " + column.ColumnName + " { get; set; }"); sb.AppendLine(" [DateSearchField]"); sb.AppendLine(" public DateTime? " + column.ColumnName + "From { get; set; }"); sb.AppendLine(" [DateSearchField]"); sb.AppendLine(" public DateTime? " + column.ColumnName + "To { get; set; }"); } else if (CrudUtilities.IsFloatDataTypeColumn(column)) { sb.AppendLine(" public float? " + column.ColumnName + " { get; set; }"); } } sb.AppendLine(" }"); sb.AppendLine("}"); using (StreamWriter file = new StreamWriter(ROOT_PATH + "\\" + DATA_ACCESS_FOLDER_NAME + "\\" + DATA_MODEL_FOLDER_NAME + "\\" + DATABASE_NAME + "\\" + modelName + ".cs")) { file.WriteLine(sb.ToString()); } sb.Clear(); } }
/// <summary> /// Tạo thủ tục SQL Read. /// Nếu bảng có cột Status thì sẽ lấy những bản ghi có Status != DELETE_STATUS. /// </summary> /// <returns></returns> public static StringBuilder GenerateRead() { StringBuilder sb = new StringBuilder(); foreach (Table table in CrudUtilities.LIST_TABLE.Where(m => m.Active == true)) { int paramCount = table.TableColumn.Count; string statusColumnName = null; Table currentTable = table; List <TableColumn> listParam = new List <TableColumn>(table.TableColumn); foreach (TableColumn tableColumn in currentTable.TableColumn) { if (CrudUtilities.IsStatusColumn(tableColumn)) { statusColumnName = tableColumn.ColumnName; } } string procName = "CRUD_" + table.TableName + "_Read"; sb.AppendLine("if object_id('dbo." + procName + "', 'p') is null"); sb.AppendLine(" exec ('create procedure " + procName + " as select 1')"); sb.AppendLine("go"); sb.AppendLine("alter procedure " + procName); for (int i = 0; i < listParam.Count; i++) { if (CrudUtilities.IsMaxLengthColumn(listParam[i])) { sb.Append("@" + listParam[i].ColumnName + " " + listParam[i].DataType + "(" + listParam[i].MaxLength + ")"); } else { sb.Append("@" + listParam[i].ColumnName + " " + listParam[i].DataType); if (listParam[i].DataType == "datetime") { sb.AppendLine(","); sb.AppendLine("@" + listParam[i].ColumnName + "From " + listParam[i].DataType + ","); sb.Append("@" + listParam[i].ColumnName + "To " + listParam[i].DataType + ""); paramCount += 2; } } if (i < listParam.Count - 1) { sb.AppendLine(","); } else { sb.AppendLine(); } } sb.AppendLine("as"); sb.Append("select "); foreach (TableColumn tableColumn in currentTable.TableColumn) { sb.Append("[" + tableColumn.ColumnName + "], "); } sb.Length--; sb.Length--; sb.AppendLine(" from [" + table.TableName + "] a where "); if (!string.IsNullOrWhiteSpace(statusColumnName)) { sb.Append(" (a.[" + statusColumnName + "] != " + CrudUtilities.DELETE_STATUS + ")"); if (listParam.Count > 0) { sb.AppendLine(" and"); } } for (int i = 0; i < listParam.Count; i++) { if (!CrudUtilities.IsCharDataTypeColumn(listParam[i])) { sb.Append(" (@" + listParam[i].ColumnName + " IS NULL OR a.[" + listParam[i].ColumnName + "] = @" + listParam[i].ColumnName + ")"); } else { sb.Append(" (@" + listParam[i].ColumnName + " IS NULL OR a.[" + listParam[i].ColumnName + "] like N'%' + @" + listParam[i].ColumnName + " + '%')"); } if (listParam[i].DataType == "datetime") { sb.AppendLine(" and"); sb.AppendLine(" (@" + listParam[i].ColumnName + "From IS NULL OR a.[" + listParam[i].ColumnName + "] >= @" + listParam[i].ColumnName + "From) and"); sb.Append(" (@" + listParam[i].ColumnName + "To IS NULL OR a.[" + listParam[i].ColumnName + "] <= @" + listParam[i].ColumnName + "To)"); } if (i < listParam.Count - 1) { sb.AppendLine(" and"); } else { sb.AppendLine(); } } sb.Append("--" + procName + " "); for (int i = 0; i < paramCount; i++) { sb.Append("null, "); } sb.Length--; sb.Length--; sb.AppendLine(); sb.AppendLine("go"); sb.AppendLine(); } return(sb); }
/// <summary> /// Tạo các class DataManipulation. Lưu trong thư mục DATA_MANIPULATION_FOLDER_NAME. /// Những bảng nào không có id tự tăng thì sẽ không có hàm Update và Delete. /// </summary> public static void GenerateManipulation() { Directory.CreateDirectory(ROOT_PATH + "\\" + DATA_ACCESS_FOLDER_NAME + "\\" + DATA_MANIPULATION_FOLDER_NAME + "\\" + DATABASE_NAME); StringBuilder sb = new StringBuilder(); foreach (Table table in CrudUtilities.LIST_TABLE.Where(m => m.Active == true)) { string modelName = table.TableName; string identityColumnName = ""; foreach (TableColumn column in table.TableColumn) { if (CrudUtilities.IsIdentityColumn(column)) { identityColumnName = column.ColumnName; break; } } sb.AppendLine("using CrudCoreSystem;"); sb.AppendLine("using " + DATA_ACCESS_FOLDER_NAME + "." + DATA_MODEL_FOLDER_NAME + "." + DATABASE_NAME + ";"); sb.AppendLine("using " + DATA_ACCESS_FOLDER_NAME + "." + DATABASE_CONFIG_FOLDER_NAME + ";"); sb.AppendLine("using System;"); sb.AppendLine("using System.Collections.Generic;"); sb.AppendLine("using System.Data;"); sb.AppendLine("using System.Data.SqlClient;"); sb.AppendLine(""); sb.AppendLine("namespace " + DATA_ACCESS_FOLDER_NAME + "." + DATA_MANIPULATION_FOLDER_NAME + "." + DATABASE_NAME); sb.AppendLine("{"); sb.AppendLine(" /// <summary>"); sb.AppendLine(" /// Class lưu các hàm CRUD bảng " + modelName + "."); sb.AppendLine(" /// KHÔNG ĐƯỢC SỬA HOẶC KHAI BÁO THÊM HÀM TRONG CLASS NÀY."); sb.AppendLine(" /// </summary>"); sb.AppendLine(" public class " + modelName + DATA_MANIPULATION_FOLDER_NAME + " : " + "CrudDataAccess"); sb.AppendLine(" {"); sb.AppendLine(" protected " + modelName + DATA_MANIPULATION_FOLDER_NAME + "()"); sb.AppendLine(" {"); sb.AppendLine(" SetupConnection(DatabaseConnectionString.CONNECTION_STRING_DB_" + DATABASE_NAME.ToUpper() + ");"); sb.AppendLine(" }"); sb.AppendLine(""); sb.AppendLine(" public void Create(" + modelName + " _objModel)"); sb.AppendLine(" {"); sb.AppendLine(" ObjModel = _objModel;"); sb.AppendLine(" Create();"); sb.AppendLine(" }"); sb.AppendLine(""); sb.AppendLine(" public List<" + modelName + "> Read(" + modelName + " _objModel)"); sb.AppendLine(" {"); sb.AppendLine(" ObjModel = _objModel;"); sb.AppendLine(" Read();"); sb.AppendLine(" List<" + modelName + "> returnList = new List<" + modelName + ">();"); sb.AppendLine(" foreach (DataRow dr in ReturnDataTable.Rows)"); sb.AppendLine(" {"); sb.AppendLine(" " + modelName + " model = new " + modelName + "();"); foreach (TableColumn column in table.TableColumn) { string columnName = column.ColumnName; if (CrudUtilities.IsIntDataTypeColumn(column)) { sb.AppendLine(" if (!string.IsNullOrWhiteSpace(dr[\"" + columnName + "\"].ToString())) model." + columnName + " = Convert.ToInt32(dr[\"" + columnName + "\"]); else model." + columnName + " = null;"); } else if (CrudUtilities.IsCharDataTypeColumn(column)) { sb.AppendLine(" if (!string.IsNullOrWhiteSpace(dr[\"" + columnName + "\"].ToString())) model." + columnName + " = dr[\"" + columnName + "\"].ToString(); else model." + columnName + " = null;"); } else if (CrudUtilities.IsDateDataTypeColumn(column)) { sb.AppendLine(" if (!string.IsNullOrWhiteSpace(dr[\"" + columnName + "\"].ToString())) model." + columnName + " = DateTime.Parse(dr[\"" + columnName + "\"].ToString()); else model." + columnName + " = null;"); } if (CrudUtilities.IsFloatDataTypeColumn(column)) { sb.AppendLine(" if (!string.IsNullOrWhiteSpace(dr[\"" + columnName + "\"].ToString())) model." + columnName + " = float.Parse(dr[\"" + columnName + "\"].ToString()); else model." + columnName + " = null;"); } } sb.AppendLine(" returnList.Add(model);"); sb.AppendLine(" }"); sb.AppendLine(" return returnList;"); sb.AppendLine(" }"); sb.AppendLine(""); if (!string.IsNullOrWhiteSpace(identityColumnName)) { sb.AppendLine(" public void Update(" + modelName + " _objModel)"); sb.AppendLine(" {"); sb.AppendLine(" ObjModel = _objModel;"); sb.AppendLine(" Update();"); sb.AppendLine(" }"); sb.AppendLine(""); sb.AppendLine(" public void Delete(" + modelName + " _objModel)"); sb.AppendLine(" {"); sb.AppendLine(" ObjModel = _objModel;"); sb.AppendLine(" Delete();"); sb.AppendLine(" }"); sb.AppendLine(""); } sb.AppendLine(" public new void ExecuteProcedure(string procedureName, List<SqlParameter> listParam, bool isReturnDataTable)"); sb.AppendLine(" {"); sb.AppendLine(" base.ExecuteProcedure(procedureName, listParam, isReturnDataTable);"); sb.AppendLine(" }"); sb.AppendLine(" }"); sb.AppendLine("}"); using (StreamWriter file = new StreamWriter(ROOT_PATH + "\\" + DATA_ACCESS_FOLDER_NAME + "\\" + DATA_MANIPULATION_FOLDER_NAME + "\\" + DATABASE_NAME + "\\" + modelName + DATA_MANIPULATION_FOLDER_NAME + ".cs")) { file.WriteLine(sb.ToString()); } sb.Clear(); } }