private static void SearchByComplexQuery()
{
var lq = new LinkQuery
{
LinkTypeId = "ProductItems",
Direction = LinkDirection.OutBound,
SourceEntityTypeId = "Product",
TargetEntityTypeId = "Item"
};
var AllProductsLinkedToItems = remoteManager.DataService.LinkSearch(lq, LoadLevel.DataAndLinks);
var sq = new SystemQuery
{
Created = DateTime.Today.AddDays(-1),
CreatedOperator = Operator.GreaterThan
};
var complexQuery = new ComplexQuery
{
LinkQuery = lq,
SystemQuery = sq
};
var allProductsLinkedToItemsThatWasCreatedToday =
remoteManager.DataService.Search(complexQuery, LoadLevel.DataAndLinks);
}
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);
}
public List <Entity> SystemSearch(SystemQuery systemQuery, LoadLevel level)
{
throw new NotImplementedException();
}
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();
}
}
}
