public async Task <object> ExecuteQueryAsync(Query query, IDictionary <string, object> parameters) { var sqlQuery = query as SqlQuery; var templateContext = new TemplateContext(); foreach (var parameter in parameters) { templateContext.SetValue(parameter.Key, parameter.Value); } var tokenizedQuery = await _liquidTemplateManager.RenderAsync(sqlQuery.Template, templateContext); var connection = _store.Configuration.ConnectionFactory.CreateConnection(); var dialect = SqlDialectFactory.For(connection); var results = new List <JObject>(); if (!SqlParser.TryParse(tokenizedQuery, dialect, _store.Configuration.TablePrefix, out var rawQuery, out var rawParameters, out var messages)) { return(results); } if (sqlQuery.ReturnDocuments) { IEnumerable <int> documentIds; using (connection) { connection.Open(); documentIds = await connection.QueryAsync <int>(rawQuery, rawParameters); } var documents = await _session.GetAsync <object>(documentIds.ToArray()); foreach (var document in documents) { results.Add(JObject.FromObject(document)); } } else { IEnumerable <dynamic> queryResults; using (connection) { connection.Open(); queryResults = await connection.QueryAsync(rawQuery, rawParameters); } foreach (var document in queryResults) { results.Add(JObject.FromObject(document)); } } return(results.ToArray()); }
public static bool TryParse(string sql, ISqlDialect dialect, string tablePrefix, IDictionary <string, object> parameters, out string query, out IEnumerable <string> messages) { try { var tree = new Parser(language).Parse(sql); if (tree.HasErrors()) { query = null; messages = tree .ParserMessages .Select(x => $"{x.Message} at line:{x.Location.Line}, col:{x.Location.Column}") .ToArray(); return(false); } var sqlParser = new SqlParser(tree, dialect, tablePrefix, parameters); query = sqlParser.Evaluate(); messages = Array.Empty <string>(); return(true); } catch (SqlParserException se) { query = null; messages = new string[] { se.Message }; } catch (Exception e) { query = null; messages = new string[] { "Unexpected error: " + e.Message }; } return(false); }
public async Task <IQueryResults> ExecuteQueryAsync(Query query, IDictionary <string, object> parameters) { var sqlQuery = query as SqlQuery; var sqlQueryResults = new SQLQueryResults(); var tokenizedQuery = await _liquidTemplateManager.RenderStringAsync(sqlQuery.Template, NullEncoder.Default, parameters.Select(x => new KeyValuePair <string, FluidValue>(x.Key, FluidValue.Create(x.Value, _templateOptions)))); var connection = _dbConnectionAccessor.CreateConnection(); var dialect = _session.Store.Configuration.SqlDialect; if (!SqlParser.TryParse(tokenizedQuery, dialect, _session.Store.Configuration.TablePrefix, parameters, out var rawQuery, out var messages)) { sqlQueryResults.Items = new object[0]; connection.Dispose(); return(sqlQueryResults); } if (sqlQuery.ReturnDocuments) { IEnumerable <int> documentIds; using (connection) { await connection.OpenAsync(); using (var transaction = connection.BeginTransaction(_session.Store.Configuration.IsolationLevel)) { documentIds = await connection.QueryAsync <int>(rawQuery, parameters, transaction); } } sqlQueryResults.Items = await _session.GetAsync <ContentItem>(documentIds.ToArray()); return(sqlQueryResults); } else { IEnumerable <dynamic> queryResults; using (connection) { await connection.OpenAsync(); using (var transaction = connection.BeginTransaction(_session.Store.Configuration.IsolationLevel)) { queryResults = await connection.QueryAsync(rawQuery, parameters, transaction); } } var results = new List <JObject>(); foreach (var document in queryResults) { results.Add(JObject.FromObject(document)); } sqlQueryResults.Items = results; return(sqlQueryResults); } }