public void ExtractValues(System.Collections.Specialized.IOrderedDictionary dictionary)
{
var conditionsJsonStr = conditions.Value;
var expressionJsonStr = expression.Value;
dictionary[ConditionsFieldName] = conditionsJsonStr;
dictionary[ExpressionFieldName] = expressionJsonStr;
if (RelexFieldName != null && !String.IsNullOrEmpty(conditionsJsonStr) && !String.IsNullOrEmpty(expressionJsonStr))
{
var expressionData = JsHelper.FromJsonString <IDictionary <string, object> >(expressionJsonStr);
var exprStr = Convert.ToString(expressionData["expression"]);
if (String.IsNullOrEmpty(exprStr.Trim()))
{
dictionary[RelexFieldName] = "1=1";
}
else
{
var fieldData = JsHelper.FromJsonString <IList <Dictionary <string, object> > >(fieldDescriptors.Value);
var fieldTypeMapping = new Dictionary <string, string>();
var relexConditionMapping = new Dictionary <string, string>();
foreach (var fldData in fieldData)
{
fieldTypeMapping[Convert.ToString(fldData["name"])] = Convert.ToString(fldData["dataType"]);
if (fldData.ContainsKey("relexcondition"))
{
relexConditionMapping[Convert.ToString(fldData["name"])] = Convert.ToString(fldData["relexcondition"]);
}
}
dictionary[RelexFieldName] = QueryBuilderHelper.GenerateRelexFromQueryBuilder(JsHelper.FromJsonString <IList <IDictionary <string, object> > >(conditionsJsonStr), exprStr, fieldTypeMapping, relexConditionMapping);
}
}
}
public static T Get <T>(Dictionary <string, string> query = null)
{
try
{
var queryString = string.Empty;
var type = QueryBuilderHelper.GetType <T>(ref query);
using (var client = QueryBuilderHelper.SetupClient())
{
var stringTask = QueryBuilderHelper.BuildTaskString(query, ref queryString, client, type);
var result = QueryBuilderHelper.CreateObject <T>(stringTask);
if (result is IBaseQueryModel model)
{
model.PagingInfo = HttpResponseToPagingInfo.MapFrom(stringTask.Headers);
return((T)model);
}
return(result);
}
}
catch (Exception ex)
{
return((T)Convert.ChangeType(ex.Message, typeof(T)));
}
}
/// <summary>
/// Gets the default list of cards or filter with a query.
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
public static Pokemon Get(Dictionary <string, string> query = null)
{
try
{
// Make sure this actually gets the default cards
// from the API as described.
query = QueryBuilderHelper.GetDefaultQuery(query);
Pokemon pokemon = QueryBuilder.GetPokemonCards(query);
if (pokemon == null)
{
pokemon.Errors = new List <string>()
{
"Not Found"
};
return(pokemon);
}
else
{
return(pokemon);
}
}
catch (Exception ex)
{
Pokemon pokemon = new Pokemon();
pokemon.Errors = new List <string>()
{
ex.Message
};
return(pokemon);
}
}
public static List <string> GetSuperTypes(Dictionary <string, string> query = null)
{
try
{
string queryString = string.Empty;
HttpResponseMessage stringTask;
List <string> superTypes = new List <string>();
query = QueryBuilderHelper.GetDefaultQuery(query);
using (HttpClient client = QueryBuilderHelper.SetupClient())
{
stringTask = QueryBuilderHelper.BuildTaskString(query, ref queryString, client, ResourceTypes.SuperTypes);
SuperType type = QueryBuilderHelper.CreateObject <SuperType>(stringTask);
superTypes.AddRange(type.Types);
return(superTypes);
}
}
catch (Exception ex)
{
List <string> errors = new List <string>()
{
ex.Message
};
return(errors);
}
}
/// <summary>
/// Get all of the cards. This call will take a while to finish.
/// </summary>
/// <param name="query"></param>
/// <returns></returns>
// TODO: Make this call more generic
public static List <PokemonCard> All(Dictionary <string, string> query = null)
{
using (HttpClient client = QueryBuilderHelper.SetupClient())
{
HttpResponseMessage stringTask;
List <Pokemon> items = new List <Pokemon>();
List <PokemonCard> mergedList = new List <PokemonCard>();
bool fetchAll = QueryBuilderHelper.FetchAll(ref query);
if (query != null)
{
if (!query.ContainsKey(CardQueryTypes.Page))
{
query.Add(CardQueryTypes.Page, "1");
}
query.Add(CardQueryTypes.PageSize, "500");
}
else
{
query = new Dictionary <string, string>()
{
{ CardQueryTypes.Page, "1" },
{ CardQueryTypes.PageSize, "500" }
};
}
for (int i = 0; i < int.Parse(query[CardQueryTypes.PageSize]); i++)
{
string queryString = string.Empty;
stringTask = QueryBuilderHelper.BuildTaskString(query, ref queryString, client, ResourceTypes.Cards);
if (stringTask.IsSuccessStatusCode)
{
Pokemon item = QueryBuilderHelper.CreateObject <Pokemon>(stringTask);
query[CardQueryTypes.Page] = (int.Parse(query[CardQueryTypes.Page]) + 1).ToString();
items.Add(item);
if (!fetchAll)
{
break;
}
}
else
{
break;
}
}
// Create the list returned as a single list instead of
// a list of lists
foreach (Pokemon pokemon in items)
{
mergedList.AddRange(pokemon.Cards);
}
return(mergedList);
}
}
/// <inherit/>
public async Task <T> FindAsync(Guid id)
{
var query = QueryBuilderHelper.GetFindQuery <T>();
var paramObject = new ExpandoObject();
paramObject.TryAdd(QueryBuilderHelper.GetPropertiesWithAttribute <T>(typeof(KeyAttribute)).FirstOrDefault().Name, id);
var record = await Connection.QueryAsync <T>(query, param : paramObject, transaction : Transaction);
return(record.FirstOrDefault());
}
public static List <SetData> Find(Dictionary <string, string> query)
{
string queryString = string.Empty;
using (HttpClient client = QueryBuilderHelper.SetupClient())
{
HttpResponseMessage stringTask = QueryBuilderHelper.BuildTaskString(query, ref queryString, client, ResourceTypes.Sets);
Set set = QueryBuilderHelper.CreateObject <Set>(stringTask);
return(set.Cards);
}
}
public static async Task <List <SetData> > Find(Dictionary <string, string> query)
{
var queryString = string.Empty;
using (var client = QueryBuilderHelper.SetupClient())
{
var stringTask = await QueryBuilderHelper.BuildTaskString(query, queryString, client, ResourceTypes.Sets);
var set = QueryBuilderHelper.CreateObject <Set>(stringTask);
return(set.Cards);
}
}
private string QueryBuilderAmount(int userId, string className, int manacost, bool isEditable)
{
StringBuilder mainQuery = new StringBuilder("Select Count(img) from cards c ");
var queryBuilderHelper = new QueryBuilderHelper();
queryBuilderHelper.UserFilter(userId, isEditable);
queryBuilderHelper.ClassFilter(className);
queryBuilderHelper.ManacostFilter(manacost);
AppendJoinsListToQueryString(mainQuery, queryBuilderHelper.JoinsList);
AppendWhereListToQueryString(mainQuery, queryBuilderHelper.WhereList);
return(mainQuery.ToString());
}
/// <inherit/>
public async Task <Guid> AddAsync(T entity)
{
if (entity == null)
{
throw new ArgumentNullException(nameof(T));
}
var paramObject = new ExpandoObject();
foreach (var property in QueryBuilderHelper.GetPropertiesWithAttribute <T>(typeof(DapperInsertAttribute)))
{
paramObject.TryAdd(property.Name, property.GetValue(entity));
}
return(await Connection.ExecuteScalarAsync <Guid>(QueryBuilderHelper.GetInsertQuery <T>(), paramObject, Transaction));
}
public static T Find <T>(string id)
{
try
{
var type = QueryBuilderHelper.GetType <T>();
using (var client = QueryBuilderHelper.SetupClient())
{
var stringTask = client.GetAsync($"{type}/{id}").Result;
return(QueryBuilderHelper.CreateObject <T>(stringTask));
}
}
catch (Exception ex)
{
return((T)Convert.ChangeType(ex.Message, typeof(T)));
}
}
public static T Get <T>(Dictionary <string, string> query = null)
{
try
{
var queryString = string.Empty;
var type = QueryBuilderHelper.GetType <T>(ref query);
using (var client = QueryBuilderHelper.SetupClient())
{
var stringTask = QueryBuilderHelper.BuildTaskString(query, ref queryString, client, type);
return(QueryBuilderHelper.CreateObject <T>(stringTask));
}
}
catch (Exception ex)
{
return((T)Convert.ChangeType(ex.Message, typeof(T)));
}
}
private string QueryBuilderCards(int userId, string className, int manacost, bool isEditable, int page, int pageSize)
{
StringBuilder mainQuery =
new StringBuilder("Select c.id, c.name, c.rarity_id, c.img, c.manacost from cards c ");
var queryBuilderHelper = new QueryBuilderHelper();
queryBuilderHelper.UserFilter(userId, isEditable);
queryBuilderHelper.ClassFilter(className);
queryBuilderHelper.ManacostFilter(manacost);
AppendJoinsListToQueryString(mainQuery, queryBuilderHelper.JoinsList);
AppendWhereListToQueryString(mainQuery, queryBuilderHelper.WhereList);
mainQuery.Append("Order By manacost, c.name " +
$"Offset {(page - 1) * pageSize} Rows Fetch next {pageSize} Rows only ");
return(mainQuery.ToString());
}
public static Pokemon GetPokemonCards(Dictionary <string, string> query = null)
{
try
{
var queryString = string.Empty;
using (var client = QueryBuilderHelper.SetupClient())
{
var stringTask = QueryBuilderHelper.BuildTaskString(query, ref queryString, client);
return(QueryBuilderHelper.CreateObject <Pokemon>(stringTask));
}
}
catch (Exception ex)
{
return(new Pokemon {
Errors = new List <string> {
ex.Message
}
});
}
}
public static async Task <SetData> GetSetsAsync(Dictionary <string, string> query = null)
{
try
{
var queryString = string.Empty;
using (var client = QueryBuilderHelper.SetupClient())
{
var stringTask = await QueryBuilderHelper.BuildTaskStringAsync(query, queryString, client);
return(QueryBuilderHelper.CreateObject <SetData>(stringTask));
}
}
catch (Exception ex)
{
return(new SetData {
Errors = new List <string> {
ex.Message
}
});
}
}
public static List <string> GetTypes(Dictionary <string, string> query = null)
{
try
{
var queryString = string.Empty;
var superTypes = new List <string>();
using (var client = QueryBuilderHelper.SetupClient())
{
var stringTask =
QueryBuilderHelper.BuildTaskString(null, ref queryString, client, ResourceTypes.Types);
var type = QueryBuilderHelper.CreateObject <TypeData>(stringTask);
superTypes.AddRange(type.Types);
return(superTypes);
}
}
catch (Exception ex)
{
return(new List <string> {
ex.Message
});
}
}
public static async Task <List <string> > GetSubTypesAsync(Dictionary <string, string> query = null)
{
try
{
var queryString = string.Empty;
var superTypes = new List <string>();
using (var client = QueryBuilderHelper.SetupClient())
{
var stringTask = await QueryBuilderHelper.BuildTaskStringAsync(null, queryString, client, ResourceTypes.SubTypes);
var type = QueryBuilderHelper.CreateObject <SubType>(stringTask);
superTypes.AddRange(type.Types);
return(superTypes);
}
}
catch (Exception ex)
{
return(new List <string> {
ex.Message
});
}
}
public static SetData GetSets(Dictionary <string, string> query = null)
{
try
{
string queryString = string.Empty;
HttpResponseMessage stringTask;
using (HttpClient client = QueryBuilderHelper.SetupClient())
{
stringTask = QueryBuilderHelper.BuildTaskString(query, ref queryString, client);
return(QueryBuilderHelper.CreateObject <SetData>(stringTask));
}
}
catch (Exception ex)
{
SetData set = new SetData();
set.Errors = new List <string>()
{
ex.Message
};
return(set);
}
}
internal DataSet getEmployeeList(DataTableFilter filter)
{
QueryBuilderHelper QBManager = new QueryBuilderHelper();
string searchQuery = string.Empty;
if (!string.IsNullOrEmpty(filter.sSearch))
{
searchQuery = QBManager.generateSearchQuery(filter);
}
string SortAndPaginationquery = QBManager.generateSortAndPaginationQuery(filter);
DataSet ds = null;
DatabaseManager DBManager = DatabaseManager.sharedDatabaseManagerinstance;
DSQuery dbquery = new DSQuery();
dbquery.query = " SELECT Id,Name,Age,Salary,MaritalStatus,Location FROM Employee EMPLOYEE";
dbquery.query = dbquery.query + " " + searchQuery + " " + SortAndPaginationquery + "; ";
dbquery.query = dbquery.query + (getCountDBQuery() + searchQuery);
dbquery.parameters.Add("SearchString", filter.sSearch);
ds = DBManager.internalExecuteSelectQueryAndReturnDataSet(dbquery);
return(ds);
}
public static Pokemon GetPokemonCards(Dictionary <string, string> query = null)
{
try
{
string queryString = string.Empty;
HttpResponseMessage stringTask;
using (HttpClient client = QueryBuilderHelper.SetupClient())
{
stringTask = QueryBuilderHelper.BuildTaskString(query, ref queryString, client);
return(QueryBuilderHelper.CreateObject <Pokemon>(stringTask));
}
}
catch (Exception ex)
{
Pokemon pokemon = new Pokemon();
pokemon.Errors = new List <string>()
{
ex.Message
};
return(pokemon);
}
}
public static Pokemon GetPokemonCards(Dictionary <string, string> query = null)
{
try
{
var queryString = string.Empty;
using (var client = QueryBuilderHelper.SetupClient())
{
var stringTask = QueryBuilderHelper.BuildTaskString(query, ref queryString, client);
var result = QueryBuilderHelper.CreateObject <Pokemon>(stringTask);
result.PagingInfo = HttpResponseToPagingInfo.MapFrom(stringTask.Headers);
return(result);
}
}
catch (Exception ex)
{
return(new Pokemon {
Errors = new List <string> {
ex.Message
}
});
}
}
public void Run()
{
var _distinctdata = _alldata.SelectMany(e => e.BaseObject.Select(w => w.Key)).Distinct();
var ValidKeys = _tableInfo.ColumnInfos.Where(q => q.IsKey).Select(e => e.ColumnName).Intersect(_distinctdata).ToList();
var AllValidfields = _tableInfo.ColumnInfos.Select(e => e.ColumnName).Intersect(_distinctdata);
if (ValidKeys.Count != _tableInfo.ColumnInfos.Where(e => e.IsKey).Select(e => e.ColumnName).Distinct().Count())
{
Console.WriteLine($"Key missed -Type {_tableName} ");
EmailSender sender = new EmailSender();
sender.SendEmailAsync("*****@*****.**", "Error--key", $"Key missed -Type {_tableName} ");
}
querybulder = new InsertQueryBuilder(AllValidfields, _tableName);
List <SqlParameter> _paramsSql = new List <SqlParameter>();
int _index = 0;
foreach (var item in _alldata)
{
List <string> stringparamForQuery = new List <string>();
foreach (var field in AllValidfields)
{
string param = $"@p{_index++}";
stringparamForQuery.Add(param);
var dbpar = new SqlParameter(param, shcema[_tableName][field]);
if (item.BaseObject.ContainsKey(field))
{
dbpar.Value = QueryBuilderHelper.FormatSQLForDb(_tableName, field, item.BaseObject[field]);
}
else
{
dbpar.Value = DBNull.Value;
}
_paramsSql.Add(dbpar);
}
querybulder.AddQueryParametrs(stringparamForQuery.ToList());
if (_paramsSql.Count > InsertCommandParametersCount)
{
Execute(_paramsSql);
_paramsSql = new List <SqlParameter>();
_index = 0;
querybulder.Reset();
}
}
if (_paramsSql.Count > 0)
{
Execute(_paramsSql);
_paramsSql = new List <SqlParameter>();
_index = 0;
}
}
public ucSelectOptions(QueryBuilderHelper queryBuilderHelper)
{
InitializeComponent();
_queryBuilderHelper = queryBuilderHelper;
addFromJoin(true);
}
public ucFromJoin(QueryBuilderHelper queryBuilderHelper)
{
InitializeComponent();
this.IsFrom = true;
_queryBuilderHelper = queryBuilderHelper;
}
public frmQueryBuilder(QueryBuilderHelper queryBuilderHelper)
{
InitializeComponent();
_queryBuilderHelper = queryBuilderHelper;
}
public static async Task <List <SetData> > All(Dictionary <string, string> query = null)
{
using (var client = QueryBuilderHelper.SetupClient())
{
var items = new List <Set>();
var mergedList = new List <SetData>();
var fetchAll = QueryBuilderHelper.FetchAll(ref query);
if (query != null)
{
if (!query.ContainsKey(CardQueryTypes.Page))
{
query.Add(CardQueryTypes.Page, "1");
}
query.Add(CardQueryTypes.PageSize, "500");
}
else
{
query = new Dictionary <string, string>
{
{ CardQueryTypes.Page, "1" },
{ CardQueryTypes.PageSize, "100" }
};
}
var totalCount = int.Parse(query[CardQueryTypes.PageSize]);
int amount;
for (var i = 0; i < totalCount; i += amount)
{
var queryString = string.Empty;
var stringTask = await QueryBuilderHelper.BuildTaskString(query, queryString, client, ResourceTypes.Sets);
if (stringTask.IsSuccessStatusCode)
{
var info = HttpResponseToPagingInfo.MapFrom(stringTask.Headers);
totalCount = info.TotalCount;
amount = info.Count;
var item = QueryBuilderHelper.CreateObject <Set>(stringTask);
query[CardQueryTypes.Page] = (int.Parse(query[CardQueryTypes.Page]) + 1).ToString();
items.Add(item);
if (!fetchAll)
{
break;
}
}
else
{
break;
}
}
// Create the list returned as a single list instead of a list of lists
foreach (var set in items)
{
mergedList.AddRange(set.Cards);
}
return(mergedList);
}
}
/// <summary>
/// Handles the Click event of the viewButton control.
/// </summary>
/// <param name="sender">The source of the event.</param>
/// <param name="e">The <see cref="System.Web.UI.ImageClickEventArgs"/> instance containing the event data.</param>
protected void ViewButton_Click(object sender, ImageClickEventArgs e)
{
try
{
string strSQLQuery = string.Empty;
string strColumnName = string.Empty;
DropDownList cboOperator = null;
TextBox txtCriteria = null;
CheckBox chbColumn = null;
CheckBox chbSelectAll = chbHeaderColumn;
QueryBuilderHelper objQueryBuilder = new QueryBuilderHelper();
#region sql query builder
objQueryBuilder.TableName = querySearchTree.SelectedNode.Value;
if(chbSelectAll.Checked)
{
objQueryBuilder.ColumnNames.Add(ALLCOLUMNS);
}
else
{
for(int intIndex = 0; intIndex < tblColumnNames.Rows.Count; intIndex++)
{
chbColumn = (CheckBox)(tblColumnNames.Rows[intIndex].Cells[0].FindControl("chbColumns"));
if(chbColumn != null)
{
if(chbColumn.Checked)
{
objQueryBuilder.ColumnNames.Add(tblColumnNames.Rows[intIndex].Cells[1].Text.ToString());
}
}
}
}
for(int intIndex = 0; intIndex < tblColumnNames.Rows.Count; intIndex++)
{
strColumnName = tblColumnNames.Rows[intIndex].Cells[1].Text;
cboOperator = (DropDownList)tblColumnNames.Rows[intIndex].Cells[2].FindControl("cboOperator");
txtCriteria = (TextBox)tblColumnNames.Rows[intIndex].Cells[3].FindControl("txtCriteria");
if(txtCriteria.Text.ToString().Length > 0 && cboOperator.SelectedIndex != 0)
{
string strCriteria = txtCriteria.Text;
strCriteria = strCriteria.Replace("*", "%");
strCriteria = strCriteria.Replace("?", "_");
strCriteria = strCriteria.Replace("'", "''");
objQueryBuilder.AddSubClause(strColumnName, cboOperator.SelectedItem.Text, strCriteria);
}
}
strSQLQuery = objQueryBuilder.BuildQuery();
#endregion
txtSQLQuery.Text = strSQLQuery;
tblColumnNames.Enabled = true;
txtSQLQuery.ReadOnly = true;
if(!string.Equals(btnSaveSearch.Text.ToString(), MODIFYSRCH) && (
!string.Equals(btnSaveSearch.Text.ToString(), MODIFYSQL)))
{
btnSaveSearch.Text = SAVESRCH;
}
EnableCriteria();
}
catch(WebException webEx)
{
CommonUtility.HandleException(strCurrSiteUrl, webEx, 1);
ShowLableMessage(webEx.Message);
}
catch(Exception ex)
{
CommonUtility.HandleException(strCurrSiteUrl, ex);
}
}
/// <inherit/>
public async Task <IList <T> > AllAsync(int page, int pageCount)
{
var records = await Connection.QueryAsync <T>(QueryBuilderHelper.GetSelectQuery <T>(), transaction : Transaction);
return(records.ToList());
}
public void Run()
{
var _distinctdata = _alldata.SelectMany(e => e.BaseObject.Select(w => w.Key)).Distinct().ToList();
var AllValidfields = _tableInfo.ColumnInfos.Select(e => e.ColumnName).Intersect(_distinctdata);
var ValidKeys = _tableInfo.ColumnInfos.Where(q => q.IsKey).Select(e => e.ColumnName).Intersect(_distinctdata).ToList();
if (ValidKeys.Count != _tableInfo.ColumnInfos.Where(e => e.IsKey).Select(e => e.ColumnName).Distinct().Count())
{
Console.WriteLine($"Key missed -Type {_tableName} ");
throw new Exception();
}
querybulder = new UpdateQueryBuilder(_tableName);
List <SqlParameter> _paramsSql = new List <SqlParameter>();
int _index = 0;
foreach (var item in _alldata)
{
List <string> stringParamForQuery = new List <string>();
List <string> stringKeyParam = new List <string>();
var data = item.ChangedProperties.Intersect(AllValidfields);
if (data.Count() > 0)
{
foreach (var obj in data)
{
string param = $"@p{_index++}";
stringParamForQuery.Add(param);
var dbpar = new SqlParameter(param, shcema[_tableName][obj]);
if (item.BaseObject.ContainsKey(obj))
{
dbpar.Value = QueryBuilderHelper.FormatSQLForDb(_tableName, obj, item.BaseObject[obj]);
}
else
{
dbpar.Value = DBNull.Value;
}
_paramsSql.Add(dbpar);
}
foreach (var singleKey in ValidKeys)
{
string param = $"@p{_index++}";
var dbpar = new SqlParameter(param, shcema[_tableName][singleKey]);
stringKeyParam.Add(param);
dbpar.Value = QueryBuilderHelper.FormatSQLForDb(_tableName, singleKey, item.BaseObject[singleKey]);
_paramsSql.Add(dbpar);
}
querybulder.AppendCommand(data.ToList(), stringParamForQuery, ValidKeys, stringKeyParam);
if (_paramsSql.Count > UpdateCommandParametersCount)
{
Execute(_paramsSql);
_paramsSql = new List <SqlParameter>();
_index = 0;
querybulder.Reset();
stringParamForQuery = new List <string>();
stringKeyParam = new List <string>();
}
}
}
if (_paramsSql.Count > 0)
{
Execute(_paramsSql);
_paramsSql = new List <SqlParameter>();
_index = 0;
}
}
