protected string CreateText(string EntityName, string SetNameSpace, bool IsFrame) { string result = string.Empty; if (!string.IsNullOrWhiteSpace(EntityName)) { var tableInfo = new List <DbTableInfo>(); using (var cmd = new SqlCommand(SystemQuery.TableInfo(EntityName), this.main.SqlConn)) { cmd.CommandType = CommandType.Text; tableInfo = cmd.ExecuteList <DbTableInfo>(); } if (tableInfo != null && tableInfo.Count > 0) { result = GetEntityBodyText(tableInfo, EntityName, SetNameSpace, IsFrame); } } return(result); }
private void SetResult() { if (LB_Table.SelectedItem != null && CB_TYPE.SelectedItem != null) { string entityName = LB_Table.SelectedItem.ToString(); string rtnType = CB_TYPE.SelectedItem.ToString(); //Void,ReturnValue,List<T> string roleType = CB_Role.SelectedItem.ToString(); //Select,Update,Insert,Delete,Save bool IsTran = IsTransaction.Checked; StringBuilder builder = new StringBuilder(200); if (!String.IsNullOrWhiteSpace(entityName)) { List <DbTableInfo> tableinfos = new List <DbTableInfo>(); try { using (var cmd = new SqlCommand(SystemQuery.TableInfo(entityName), this.main.SqlConn)) { cmd.CommandType = CommandType.Text; tableinfos = cmd.ExecuteList <DbTableInfo>(); } } catch (Exception ex) { MsgWrite(ex.Message); } if (tableinfos != null && tableinfos.Count > 0) { var identityColumn = tableinfos.Where(x => x.is_identity).FirstOrDefault(); if (identityColumn == null) { identityColumn = tableinfos.FirstOrDefault(); } if (identityColumn != null) { builder.AppendLine($"CREATE PROCEDURE [dbo].[ESP_{entityName}_{roleType}]"); builder.AppendLine("("); int num = 0; foreach (DbTableInfo info in tableinfos.Where(x => x.is_identity == false).OrderBy(x => x.is_nullable)) { switch (info.ColumnType.ToLower()) { case "date": case "datetime": case "datetime2": case "smalldatetime": break; default: if (num > 0) { builder.Append(","); } builder.AppendLine($" @{info.ColumnName} {this.ColumnTypeString(info)}"); num++; break; } } if (tableinfos.Where(x => x.is_identity == false).Count() > 0) { builder.Append(","); } builder.AppendLine($" @{identityColumn.ColumnName} {identityColumn.ColumnType} = -1"); if (rtnType.Equals("ReturnValue", StringComparison.OrdinalIgnoreCase)) { builder.AppendLine(", @Code bigint output"); builder.AppendLine(", @Value varchar(100) output"); builder.AppendLine(", @Msg nvarchar(100) output"); } builder.AppendLine(")"); builder.AppendLine("AS"); builder.AppendLine(""); builder.AppendLine("SET NOCOUNT ON"); builder.AppendLine("SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED"); builder.AppendLine(""); builder.AppendLine("Declare @Err int"); builder.AppendLine("SET @Err = 0"); builder.AppendLine(""); if (IsTran) { builder.AppendLine("BEGIN TRAN"); } if (rtnType.Equals("ReturnValue", StringComparison.OrdinalIgnoreCase)) { builder.AppendLine("SET @Code = 0"); builder.AppendLine("SET @Value = ''"); builder.AppendLine("SET @Msg = ''"); builder.AppendLine(""); builder.AppendLine("BEGIN TRY"); } //BODY switch (roleType.ToUpper()) { case "SELECT": builder.AppendLine($"select * from [{entityName}]"); break; case "UPDATE": builder.AppendLine($"update [{entityName}] set "); num = 0; foreach (DbTableInfo info in tableinfos.Where(x => x.is_identity == false)) { if (num > 0) { builder.Append(","); } switch (info.ColumnType.ToLower()) { case "date": case "datetime": case "datetime2": case "smalldatetime": builder.AppendLine($"[{info.ColumnName}] = getdate()"); break; default: builder.AppendLine($"[{info.ColumnName}] = @{info.ColumnName}"); break; } num++; } builder.Append("where "); num = 0; foreach (DbTableInfo info in tableinfos.Where(x => x.is_identity)) { if (num > 0) { builder.Append(" and "); } builder.AppendLine($"{info.ColumnName} = @{info.ColumnName}"); num++; } builder.AppendLine(""); builder.AppendLine("SET @Err = @Err + @@Error"); if (rtnType.Equals("ReturnValue", StringComparison.OrdinalIgnoreCase)) { builder.AppendLine(""); builder.AppendLine("IF IsNull(@Err,0) = 0"); builder.AppendLine(" BEGIN"); builder.AppendLine($" SET @Code = @{identityColumn.ColumnName}"); builder.AppendLine(" END"); builder.AppendLine("ELSE"); builder.AppendLine(" BEGIN"); builder.AppendLine($" SET @Code = -1"); builder.AppendLine($" SET @Msg = '수정하지 못했습니다.'"); builder.AppendLine(" END"); } break; case "INSERT": builder.AppendLine($"insert into [{entityName}] ("); num = 0; foreach (DbTableInfo info in tableinfos.Where(x => x.is_identity == false)) { if (num > 0) { builder.Append(","); } builder.AppendLine($"[{info.ColumnName}]"); num++; } builder.AppendLine(") values ("); num = 0; foreach (DbTableInfo info in tableinfos.Where(x => x.is_identity == false)) { if (num > 0) { builder.Append(","); } switch (info.ColumnType.ToLower()) { case "date": case "datetime": case "datetime2": case "smalldatetime": builder.AppendLine("getdate()"); break; default: builder.AppendLine($"@{info.ColumnName}"); break; } num++; } builder.AppendLine(")"); builder.AppendLine(""); builder.AppendLine("SET @Err = @Err + @@Error"); if (rtnType.Equals("ReturnValue", StringComparison.OrdinalIgnoreCase)) { builder.AppendLine(""); builder.AppendLine("IF IsNull(@Err,0) = 0"); builder.AppendLine(" BEGIN"); builder.AppendLine($" SET @Code = @@IDENTITY"); builder.AppendLine(" END"); builder.AppendLine("ELSE"); builder.AppendLine(" BEGIN"); builder.AppendLine($" SET @Code = -1"); builder.AppendLine($" SET @Msg = '저장하지 못했습니다.'"); builder.AppendLine(" END"); } break; case "DELETE": builder.AppendLine($"delete from [{entityName}]"); if (tableinfos.Where(x => x.is_identity).Count() > 0) { builder.Append("where "); num = 0; foreach (DbTableInfo info in tableinfos.Where(x => x.is_identity)) { if (num > 0) { builder.Append(" and "); } builder.AppendLine($"[{info.ColumnName}] = @{info.ColumnName}"); num++; } } builder.AppendLine(""); builder.AppendLine("SET @Err = @Err + @@Error"); if (rtnType.Equals("ReturnValue", StringComparison.OrdinalIgnoreCase)) { builder.AppendLine(""); builder.AppendLine("IF IsNull(@Err,0) = 0"); builder.AppendLine(" BEGIN"); builder.AppendLine($" SET @Code = @{identityColumn.ColumnName}"); builder.AppendLine(" END"); builder.AppendLine("ELSE"); builder.AppendLine(" BEGIN"); builder.AppendLine($" SET @Code = -1"); builder.AppendLine($" SET @Msg = '삭제하지 못했습니다.'"); builder.AppendLine(" END"); } break; case "SAVE": builder.AppendLine($"IF Exists(select [{identityColumn.ColumnName}] from [{entityName}] where [{identityColumn.ColumnName}] = @{identityColumn.ColumnName})"); builder.AppendLine(" BEGIN"); builder.AppendLine($"update [{entityName}] set "); num = 0; foreach (DbTableInfo info in tableinfos.Where(x => x.is_identity == false)) { if (num > 0) { builder.Append(","); } switch (info.ColumnType.ToLower()) { case "date": case "datetime": case "datetime2": case "smalldatetime": builder.AppendLine($"[{info.ColumnName}] = getdate()"); break; default: builder.AppendLine($"[{info.ColumnName}] = @{info.ColumnName}"); break; } num++; } builder.Append("where "); num = 0; foreach (DbTableInfo info in tableinfos.Where(x => x.is_identity)) { if (num > 0) { builder.Append(" and "); } builder.AppendLine($"[{info.ColumnName}] = @{info.ColumnName}"); num++; } builder.AppendLine(""); builder.AppendLine("SET @Err = @Err + @@Error"); if (rtnType.Equals("ReturnValue", StringComparison.OrdinalIgnoreCase)) { builder.AppendLine(""); builder.AppendLine("IF IsNull(@Err,0) = 0"); builder.AppendLine(" BEGIN"); builder.AppendLine($" SET @Code = @{identityColumn.ColumnName}"); builder.AppendLine(" END"); builder.AppendLine("ELSE"); builder.AppendLine(" BEGIN"); builder.AppendLine($" SET @Code = -1"); builder.AppendLine($" SET @Msg = '수정하지 못했습니다.'"); builder.AppendLine(" END"); } builder.AppendLine(" END"); builder.AppendLine("ELSE"); builder.AppendLine(" BEGIN"); builder.AppendLine($"insert into [{entityName}] ("); num = 0; foreach (DbTableInfo info in tableinfos.Where(x => x.is_identity == false)) { if (num > 0) { builder.Append(","); } builder.AppendLine($"[{info.ColumnName}]"); num++; } builder.AppendLine(") values ("); num = 0; foreach (DbTableInfo info in tableinfos.Where(x => x.is_identity == false)) { if (num > 0) { builder.Append(","); } switch (info.ColumnType.ToLower()) { case "date": case "datetime": case "datetime2": case "smalldatetime": builder.AppendLine("getdate()"); break; default: builder.AppendLine($"@{info.ColumnName}"); break; } num++; } builder.AppendLine(")"); builder.AppendLine(""); builder.AppendLine("SET @Err = @Err + @@Error"); if (rtnType.Equals("ReturnValue", StringComparison.OrdinalIgnoreCase)) { builder.AppendLine(""); builder.AppendLine("IF IsNull(@Err,0) = 0"); builder.AppendLine(" BEGIN"); builder.AppendLine($" SET @Code = @@IDENTITY"); builder.AppendLine(" END"); builder.AppendLine("ELSE"); builder.AppendLine(" BEGIN"); builder.AppendLine($" SET @Code = -1"); builder.AppendLine($" SET @Msg = '저장하지 못했습니다.'"); builder.AppendLine(" END"); } builder.AppendLine(" END"); builder.AppendLine(""); break; } //END BODY if (rtnType.Equals("ReturnValue", StringComparison.OrdinalIgnoreCase)) { builder.AppendLine("END TRY"); builder.AppendLine("BEGIN CATCH"); builder.AppendLine(" SET @Code = -1"); builder.AppendLine(" SET @Err = @Err + 1"); builder.AppendLine(" SET @Msg = ERROR_MESSAGE()"); builder.AppendLine("END CATCH"); } if (IsTran) { builder.AppendLine(""); builder.AppendLine("IF IsNull(@Err,0) = 0"); builder.AppendLine(" BEGIN"); builder.AppendLine(" COMMIT TRAN"); builder.AppendLine(" END"); builder.AppendLine("ELSE"); builder.AppendLine(" BEGIN"); builder.AppendLine(" ROLLBACK TRAN"); if (rtnType.Equals("ReturnValue", StringComparison.OrdinalIgnoreCase)) { builder.AppendLine(" SET @Code = -1"); builder.AppendLine(" SET @Msg = '처리하지 못했습니다.'"); } builder.AppendLine(" END"); } } else { builder.AppendLine("<< Identity Column is not Found >>"); } } this.Query = builder.ToString(); } } }