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