public DataTable RetrieveDataByQuery(Query query, int offset)
{
string queryString = null;
DataTable data = new DataTable();
try
{
queryString = new QueryConverter().ConvertToSQL(query, offset);
using (SqlConnection conn = new SqlConnection(SQLAccess.Properties.Settings.Default.masterConnectionString))
{
SqlDataAdapter adapter = new SqlDataAdapter(queryString, conn);
try
{
adapter.Fill(data);
}
catch (SqlException e)
{
MessageBox.Show(e.Message, "Error", MessageBoxButton.OK, MessageBoxImage.Warning);
}
}
}
catch (ArgumentException e)
{
MessageBox.Show(e.Message, "Confirmation", MessageBoxButton.OK, MessageBoxImage.Warning);
}
return(data);
}
public SpellCheckHandler(Analyzer analyzer, string fieldName)
{
_queryConverter = new SpellingQueryConverter();
_queryConverter.setAnalyzer(analyzer);
_spellChecker = new SpellCheckerWrapper(fieldName, null);
}
internal SqlNode TranslateLink(SqlLink link, List <SqlExpression> keyExpressions, bool asExpression)
{
var member = link.Member;
if (member.IsAssociation)
{
var otherType = member.Association.OtherType;
var type = otherType.InheritanceRoot.Type;
var table = services.Context.GetTable(type);
Expression otherSource = new LinkedTableExpression(link, table, typeof(IQueryable <>).MakeGenericType(otherType.Type));
var array = new Expression[keyExpressions.Count];
for (var i = 0; i < keyExpressions.Count; i++)
{
var metaDataMember = member.Association.OtherKey[i];
var memberType = TypeSystem.GetMemberType(metaDataMember.Member);
array[i] = InternalExpression.Known(keyExpressions[i], memberType);
}
var thisInstance = (link.Expression != null) ? ((Expression)InternalExpression.Known(link.Expression)) : ((Expression)Expression.Constant(null, link.Member.Member.DeclaringType));
var node = TranslateAssociation(services.Context, member.Association, otherSource, array, thisInstance);
var queryConverter = new QueryConverter(services, typeProvider, this, sql);
var sqlSelect = (SqlSelect)queryConverter.ConvertInner(node, link.SourceExpression);
SqlNode result = sqlSelect;
if (asExpression)
{
result = ((!member.Association.IsMany) ? new SqlSubSelect(SqlNodeType.Element, link.ClrType, link.SqlType, sqlSelect) : new SqlSubSelect(SqlNodeType.Multiset, link.ClrType, link.SqlType, sqlSelect));
}
return(result);
}
return(link.Expansion);
}
public static string ConvertToSql(QueryExpression queryExpression, out ParameterBag valueParameters)
{
var queryWriter = new QueryWriter();
var converter = new QueryConverter();
converter.WriteQuery(queryWriter, queryExpression);
valueParameters = queryWriter.Parameters;
return(queryWriter.ToString().ReduceWhitespace().Trim());
}
internal SqlNode TranslateLink(SqlLink link, List <SqlExpression> keyExpressions, bool asExpression)
{
MetaDataMember mm = link.Member;
if (mm.IsAssociation)
{
// Create the row source.
MetaType otherType = mm.Association.OtherType;
Type tableType = otherType.InheritanceRoot.Type;
ITable table = this.services.Context.GetTable(tableType);
Expression source = new LinkedTableExpression(link, table, typeof(IQueryable <>).MakeGenericType(otherType.Type));
// Build key expression nodes.
Expression[] keyExprs = new Expression[keyExpressions.Count];
for (int i = 0; i < keyExpressions.Count; ++i)
{
MetaDataMember metaMember = mm.Association.OtherKey[i];
Type memberType = TypeSystem.GetMemberType(metaMember.Member);
keyExprs[i] = InternalExpression.Known(keyExpressions[i], memberType);
}
Expression lex = link.Expression != null
? (Expression)InternalExpression.Known(link.Expression)
: (Expression)Expression.Constant(null, link.Member.Member.DeclaringType);
Expression expr = TranslateAssociation(this.services.Context, mm.Association, source, keyExprs, lex);
// Convert
QueryConverter qc = new QueryConverter(this.services, this.typeProvider, this, this.sql);
SqlSelect sel = (SqlSelect)qc.ConvertInner(expr, link.SourceExpression);
// Turn it into an expression is necessary
SqlNode result = sel;
if (asExpression)
{
if (mm.Association.IsMany)
{
result = new SqlSubSelect(SqlNodeType.Multiset, link.ClrType, link.SqlType, sel);
}
else
{
result = new SqlSubSelect(SqlNodeType.Element, link.ClrType, link.SqlType, sel);
}
}
return(result);
}
else
{
System.Diagnostics.Debug.Assert(link.Expansion != null);
System.Diagnostics.Debug.Assert(link.KeyExpressions == keyExpressions);
// deferred expression already defined...
return(link.Expansion);
}
}
public void Test()
{
var dynamicQuery = BuildDynamicQuery();
var whereClause = BuilderWhereClause(dynamicQuery);
var orderClause = BuildOrderClause(dynamicQuery);
// Get data ready
var context = ServiceProvider.GetRequiredService <Context>();
context.TestingDto.Add(new Model.TestingDto
{
Id = Guid.NewGuid().ToString("N"),
TestAddress = "chengdu lnk",
Disabled = false,
Amout = 11,
Price = 110,
Drink = "coffee",
Count = 1,
Total = 100,
Url = "Http://www.feiniubus.com",
Extras = new HashSet <Model.TestingDto.Extra>()
{
new Model.TestingDto.Extra
{
Guest = "Andy"
}
}
});
context.SaveChanges();
//testing
var entityType = typeof(Model.TestingDto);
var converter = new QueryConverter(entityType, dynamicQuery, true);
var model = converter.Converter();
var entities = AutoQueryAsync(context.TestingDto.AsQueryable(), model, true);
//sql testing
var sb = new StringBuilder();
sb.AppendLine("SELECT * FROM TestingDto WHERE");
sb.AppendLine(whereClause.Item1);
sb.AppendLine("ORDER BY");
sb.AppendLine(orderClause);
sb.AppendLine(";");
string sql = sb.ToString();
Assert.True(entities?.Rows?.Count() > 0);
}
public void TestChargeHistory()
{
var query = new Model.ChargeHostory[]
{
new Model.ChargeHostory(),
new Model.ChargeHostory(),
}.AsQueryable();
var builder = new DynamicQueryBuilder(true);
builder.Take(20).Skip(10).ParamGroupBuilder.ParamBuilder.Equal("charge_id", 1);
var converter = new QueryConverter(typeof(Model.ChargeHostory), builder.Build(), true);
var model = converter.Converter();
}
public bool BindModel(HttpActionContext actionContext, ModelBindingContext bindingContext)
{
if (bindingContext.ModelType == typeof(Query))
{
var queryString = actionContext.Request.GetQueryNameValuePairs()?.ToList();
if (queryString != null)
{
var result = QueryConverter.ConvertToQuery(queryString);
bindingContext.Model = result;
return(true);
}
}
return(false);
}
/// <summary>
/// Gets converted queries. Modification of getSelectments() method
/// </summary>
/// <param name="queries">List of queries, where each item is query to separate table.</param>
/// <returns>List of selectments model.</returns>
private List <SelectModel> GetConvertedQueries(List <QueryTableModel> queries)
{
var tables = queries.Select(query => GetTable(new TableViewModel {
Id = query.Id, Name = query.TableName, Schema = query.TableSchema
})).ToList();
var queryConverter = new QueryConverter(tables);
var convertedQueries = queries.Select(query => queryConverter.ToSelectModel(query)).ToList();
//var lastContainingWhere = convertedQueries.LastOrDefault(x => x.Wheres.Any());
//if (lastContainingWhere != null)
//{
// queryConverter.CorrectLastWhere(ref lastContainingWhere);
//}
return(convertedQueries.ToList());
}
