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