/// <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 Create. /// Nếu có cột id tự tăng thủ tục sẽ có param ReturnData trả về id đó, nếu không thì param ReturnData trả về -1. /// </summary> /// <returns></returns> public static StringBuilder GenerateCreate() { StringBuilder sb = new StringBuilder(); foreach (Table table in CrudUtilities.LIST_TABLE.Where(m => m.Active == true)) { bool hasIdentityColumn = false; string procName = "CRUD_" + table.TableName + "_Create"; 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); foreach (TableColumn tableColumn in table.TableColumn) { if (CrudUtilities.IsIdentityColumn(tableColumn)) { hasIdentityColumn = true; } if (CrudUtilities.IsMaxLengthColumn(tableColumn)) { sb.AppendLine("@" + tableColumn.ColumnName + " " + tableColumn.DataType + "(" + tableColumn.MaxLength + "),"); } else { sb.AppendLine("@" + tableColumn.ColumnName + " " + tableColumn.DataType + ","); } } sb.AppendLine("@ReturnData int output,"); sb.AppendLine("@ReturnCode int output,"); sb.AppendLine("@ReturnMess nvarchar(500) output"); sb.AppendLine("as"); sb.AppendLine("begin transaction;"); sb.AppendLine("begin try"); sb.Append(" insert into [" + table.TableName + "]("); for (int i = 0; i < table.TableColumn.Count; i++) { if (!CrudUtilities.IsIdentityColumn(table.TableColumn[i])) { sb.Append("[" + table.TableColumn[i].ColumnName + "], "); } } sb.Length--; sb.Length--; sb.AppendLine(")"); sb.Append(" values ("); for (int i = 0; i < table.TableColumn.Count; i++) { if (!CrudUtilities.IsIdentityColumn(table.TableColumn[i])) { sb.Append("@" + table.TableColumn[i].ColumnName + ", "); } } sb.Length--; sb.Length--; sb.AppendLine(")"); sb.AppendLine("end try"); sb.AppendLine("begin catch"); sb.AppendLine(" set @ReturnData = -1;"); sb.AppendLine(" set @ReturnCode = ERROR_NUMBER();"); sb.AppendLine(" set @ReturnMess = ERROR_MESSAGE();"); sb.AppendLine(" if @@TRANCOUNT > 0"); sb.AppendLine(" rollback transaction;"); sb.AppendLine(" return;"); sb.AppendLine("end catch;"); sb.AppendLine("if @@TRANCOUNT > 0"); sb.AppendLine("commit transaction;"); if (hasIdentityColumn) { sb.AppendLine("set @ReturnData = SCOPE_IDENTITY();"); } else { sb.AppendLine("set @ReturnData = -1;"); } sb.AppendLine("set @ReturnCode = 0"); sb.AppendLine("set @ReturnMess = 'success'"); sb.AppendLine("go"); sb.AppendLine(); } return(sb); }
/// <summary> /// Tạo thủ tục SQL Delete. /// Nếu bảng có trường Status thì sẽ update Status = DELETE_STATUS, còn không sẽ xóa bản ghi của bảng đó. /// Những bảng nào không có id tự tăng thì sẽ bỏ qua bảng đó. /// </summary> /// <returns></returns> public static StringBuilder GenerateDelete() { StringBuilder returnData = new StringBuilder(); foreach (Table table in CrudUtilities.LIST_TABLE.Where(m => m.Active == true)) { StringBuilder sb = new StringBuilder(); string identityColumnName = null; string statusColumnName = null; string procName = "CRUD_" + table.TableName + "_Delete"; 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); foreach (TableColumn tableColumn in table.TableColumn) { if (CrudUtilities.IsIdentityColumn(tableColumn)) { if (CrudUtilities.IsMaxLengthColumn(tableColumn)) { sb.AppendLine("@" + tableColumn.ColumnName + " " + tableColumn.DataType + "(" + tableColumn.MaxLength + "),"); } else { sb.AppendLine("@" + tableColumn.ColumnName + " " + tableColumn.DataType + ","); } identityColumnName = tableColumn.ColumnName; } if (CrudUtilities.IsStatusColumn(tableColumn)) { statusColumnName = tableColumn.ColumnName; } } //Nếu bảng không có cột nào id tự tăng thì sẽ bỏ qua bảng đó if (string.IsNullOrWhiteSpace(identityColumnName)) { continue; } sb.AppendLine("@ReturnData int output,"); sb.AppendLine("@ReturnCode int output,"); sb.AppendLine("@ReturnMess nvarchar(500) output"); sb.AppendLine("as"); sb.AppendLine("begin transaction;"); sb.AppendLine("begin try"); if (string.IsNullOrWhiteSpace(statusColumnName)) { //Delete bản ghi sb.AppendLine(" delete from [" + table.TableName + "] where " + identityColumnName + " = @" + identityColumnName + ";"); } else { //Update status = DELETE_STATUS sb.AppendLine(" update [" + table.TableName + "] set [" + statusColumnName + "] = " + CrudUtilities.DELETE_STATUS + " where " + identityColumnName + " = @" + identityColumnName + ";"); } sb.AppendLine("end try"); sb.AppendLine("begin catch"); sb.AppendLine(" set @ReturnData = -1;"); sb.AppendLine(" set @ReturnCode = ERROR_NUMBER();"); sb.AppendLine(" set @ReturnMess = ERROR_MESSAGE();"); sb.AppendLine(" if @@TRANCOUNT > 0"); sb.AppendLine(" rollback transaction;"); sb.AppendLine(" return;"); sb.AppendLine("end catch;"); sb.AppendLine("if @@TRANCOUNT > 0"); sb.AppendLine("commit transaction;"); sb.AppendLine("set @ReturnData = @" + identityColumnName + ";"); sb.AppendLine("set @ReturnCode = 0"); sb.AppendLine("set @ReturnMess = 'success'"); sb.AppendLine("go"); sb.AppendLine(); returnData.Append(sb); } return(returnData); }
/// <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 Repository và Interface. Lưu trong thư mục DATA_REPOSITORY_FOLDER_NAME. /// </summary> public static void GenerateRepository() { Directory.CreateDirectory(ROOT_PATH + "\\" + DATA_ACCESS_FOLDER_NAME + "\\" + DATA_REPOSITORY_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 " + DATA_ACCESS_FOLDER_NAME + "." + DATA_MANIPULATION_FOLDER_NAME + "." + DATABASE_NAME + ";"); sb.AppendLine(""); sb.AppendLine("namespace " + DATA_ACCESS_FOLDER_NAME + "." + DATA_REPOSITORY_FOLDER_NAME + "." + DATABASE_NAME); sb.AppendLine("{"); sb.AppendLine(" /// <summary>"); sb.AppendLine(" /// Class lưu các phương thức thao tác dữ liệu khác của bảng " + modelName + "."); sb.AppendLine(" /// </summary>"); sb.AppendLine(" public class " + modelName + DATA_REPOSITORY_FOLDER_NAME + " : " + modelName + DATA_MANIPULATION_FOLDER_NAME + ", I" + modelName + DATA_REPOSITORY_FOLDER_NAME); sb.AppendLine(" {"); sb.AppendLine(""); sb.AppendLine(" }"); sb.AppendLine("}"); using (StreamWriter file = new StreamWriter(ROOT_PATH + "\\" + DATA_ACCESS_FOLDER_NAME + "\\" + DATA_REPOSITORY_FOLDER_NAME + "\\" + DATABASE_NAME + "\\" + modelName + DATA_REPOSITORY_FOLDER_NAME + ".cs")) { file.WriteLine(sb.ToString()); } sb.Clear(); string identityColumnName = ""; foreach (TableColumn column in table.TableColumn) { if (CrudUtilities.IsIdentityColumn(column)) { identityColumnName = column.ColumnName; break; } } sb.AppendLine("using " + DATA_ACCESS_FOLDER_NAME + "." + DATA_MODEL_FOLDER_NAME + "." + DATABASE_NAME + ";"); sb.AppendLine("using System.Collections.Generic;"); sb.AppendLine("using CrudCoreSystem;"); sb.AppendLine(""); sb.AppendLine("namespace " + DATA_ACCESS_FOLDER_NAME + "." + DATA_REPOSITORY_FOLDER_NAME + "." + DATABASE_NAME); sb.AppendLine("{"); sb.AppendLine(" public interface I" + modelName + DATA_REPOSITORY_FOLDER_NAME + " : ICrudReturnData"); sb.AppendLine(" {"); sb.AppendLine(" #region Crud core function"); sb.AppendLine(" void Create(" + modelName + " _objModel);"); sb.AppendLine(" List<" + modelName + "> Read(" + modelName + " _objModel);"); if (!string.IsNullOrWhiteSpace(identityColumnName)) { sb.AppendLine(" void Update(" + modelName + " _objModel);"); sb.AppendLine(" void Delete(" + modelName + " _objModel);"); } sb.AppendLine(" #endregion"); sb.AppendLine(" }"); sb.AppendLine("}"); using (StreamWriter file = new StreamWriter(ROOT_PATH + "\\" + DATA_ACCESS_FOLDER_NAME + "\\" + DATA_REPOSITORY_FOLDER_NAME + "\\" + DATABASE_NAME + "\\I" + modelName + DATA_REPOSITORY_FOLDER_NAME + ".cs")) { file.WriteLine(sb.ToString()); } sb.Clear(); } }
/// <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(); } }