/// <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();
}
}
