public Object Search(string id, [FromBody] string json)
{
var hasSeq = db.CoreTableMetaDatas.Where(x => x.TableName == id &&
x.FieldName.ToUpper() == "SEQ").Count() > 0;
var sWhere = " where 1=1";
if (json != null)
{
JavaScriptSerializer serializer = new JavaScriptSerializer();
dynamic item = serializer.Deserialize <object>(json);
foreach (var x in item)
{
if (x.Value != null && x.Value != "")
{
sWhere += " and [" + x.Key + "] like " + "N'%" + x.Value + "%'";
}
}
}
var query = "select * from " + id + sWhere;
if (hasSeq)
{
query += " order by SEQ";
}
;
var list = DynamicSqlHelper.DynamicSqlQuery(db.Database, query);
return(list);
}
public Object GetSearchResult(string id)
{
var query = "select * from " + id;
var list = DynamicSqlHelper.DynamicSqlQuery(db.Database, query);
return(list);
}
public async Task <IHttpActionResult> AutoComplete(SearchCondition searchCondition)
{
string selectField = "select ";
selectField += string.Join(",", searchCondition.List_Select);
string from = " from " + searchCondition.DataSourceId;
string whereCause = " where 1=1";
if (searchCondition.List_Where != null)
{
foreach (var where in searchCondition.List_Where)
{
if (where.Condition == "IN")
{
whereCause += " and [" + where.Key + "] "
+ where.Condition + " " + where.Value + " ";
}
else
{
whereCause += " and [" + where.Key + "] "
+ (where.Condition ?? "=") + " '" + where.Value + "'";
}
}
}
string groupBy = " ";
if (searchCondition.List_GroupBy != null)
{
groupBy += "group by " + string.Join(",", searchCondition.List_GroupBy);
}
string orderBy = " ";
if ((searchCondition.OrderBy ?? "") != "")
{
orderBy += " order by " + searchCondition.OrderBy ?? "";
}
string sqlCommand = selectField + from + whereCause + groupBy + orderBy;
var list = DynamicSqlHelper.DynamicSqlQuery(db.Database, sqlCommand);
return(Json(list));
}
public async Task <IHttpActionResult> GetCoreTableMetaDatas(string id, [FromBody] string json)
{
var listPk = db.CoreTableMetaDatas.Where(x => x.TableName == id &&
x.IsPrimaryKey == true).OrderBy(x => x.Sequence);
var listPkValue = new List <string>();
JavaScriptSerializer serializer = new JavaScriptSerializer();
dynamic item = serializer.Deserialize <object>(json);
var query = "select * from " + id;
query += " where 1=1";
var paramList = new List <SqlParameter>();
foreach (var pk in listPk)
{
query += " and [" + pk.FieldName + "]=" + "@" + pk.FieldName;
paramList.Add(new SqlParameter(pk.FieldName, item[pk.FieldName]));
listPkValue.Add(item[pk.FieldName].ToString());
}
var list = DynamicSqlHelper.DynamicSqlQuery(db.Database, query, paramList.ToArray());
return(Ok(list));
}
protected System.Collections.IEnumerable GetListSql(string sqlCommand)
{
var list = DynamicSqlHelper.DynamicSqlQuery(db.Database, sqlCommand);
return(list);
}
