public void TestFromSubQuery()
{
var q = Query.From <Students>();
var q1 = Query.From(q).Select(t => new { t.Id, t.No });
var r = queryBuilder.Build(q1);
Assert.AreEqual("select TEMP0.`Id` as Id,TEMP0.`No` as No from (select * from `Students` ) TEMP0", r.SQL);
}
public async Task ExecuteAsync(CancellationToken token, IPackage pckg)
{
_log.Debug("Starting processing of package...");
if (!_fileService.DirectoryExists(_args.OutputFolder))
{
_fileService.CreateDirectory(_args.OutputFolder);
}
var tasks = new List <Task>();
foreach (var scriptSource in pckg.Scripts)
{
tasks.Add(Task.Run(async() =>
{
var pTables = await DI.GetInstance <IDependencyBuilder>()
.PrepareAsync(token, scriptSource);
_fileService.Save(_sqlBuilder.Build(pTables, _schema), string.Format(".\\{0}\\{1}_p_{2}.sql", _args.OutputFolder, scriptSource.Order, scriptSource.ScriptName));
}));
}
await Task.WhenAll(tasks);
_log.Debug("Finished processing of package.");
}
public async Task <IActionResult> ExecuteAsync(CancellationToken token, [FromBody] Package pckg)
{
try
{
await _schema.Init(token);
_log.Debug("Starting processing of package...");
var tasks = new List <Task <Tuple <string, string> > >();
foreach (var scriptSource in pckg.Scripts)
{
tasks.Add(Task.Run(async() =>
{
var pTables = await _dependencyBuilder.PrepareAsync(token, scriptSource);
return(new Tuple <string, string>(_sqlBuilder.Build(pTables, _schema), "{0}_p_{1}.sql".FormIt(scriptSource.Order, scriptSource.ScriptName)));
}));
}
await Task.WhenAll(tasks);
_log.Debug("Finished processing of package.");
return(new FileContentResult(await PrepareStream(tasks.Select(_ => _.Result)), new MediaTypeHeaderValue("application/zip"))
{
FileDownloadName = "scripts.zip"
});
}
catch (Exception e)
{
throw e;
}
}
public string Execute(string xmlstring)
{
XmlToSqlResult xmlToSqlResult = _sqlBuilder.Build(xmlstring);
if (xmlToSqlResult.Success)
{
switch (xmlToSqlResult.Type)
{
case SqlStatementTypeEnum.SELECT:
List <Dictionary <string, object> > selectResult = _dataRepository.Select(xmlToSqlResult.Statement);
return(ConvertToXMLResult(StatusEnum.SUCCESS, JsonConvert.SerializeObject(selectResult, Formatting.Indented)));
case SqlStatementTypeEnum.DELETE:
case SqlStatementTypeEnum.UPDATE:
case SqlStatementTypeEnum.INSERT:
return(_dataRepository.ExecuteNoneQuery(xmlToSqlResult.Statement)
? ConvertToXMLResult(StatusEnum.SUCCESS, $"Operation '{xmlToSqlResult.Type}' success!")
: ConvertToXMLResult(StatusEnum.REJECTED, $"Operation '{xmlToSqlResult.Type}' rejected!"));
default:
return(ConvertToXMLResult(StatusEnum.REJECTED, $"Operation '{xmlToSqlResult.Type}' rejected!"));
}
}
else
{
return(ConvertToXMLResult(StatusEnum.BAD_FORMAT, $"Error: ${xmlToSqlResult.ErrorMessage}"));
}
}
public void TestContextSetRepository()
{
Expression <Predicate <TestTable> > expression = table => table.Id == 1;
ISqlBuilder builder = _builder.Visit(expression);
string sql = builder.Build(_builder, out DbParameter[] parameters);
Assert.AreEqual("Id = @parameter1", sql);
Assert.AreEqual(1, parameters.Length);
}
public void TestSelectSql()
{
var query = Query.From <Peoples>().Select(t => new
{
t.Age
});
var r = builder.Build(query);
Assert.AreEqual("select `Peoples`.`Age` as Age from `Peoples`", r.SQL);
}
public void BuildSelectById()
{
string SELECT_BY_ID = @"
<Resource>
<Verb>GET</Verb>
<Noun>/RESOURCE/1</Noun>
<Query></Query>
<Fields></Fields>
<ConnectedTo></ConnectedTo>
<ConnectedType></ConnectedType>
</Resource>
" ;
XmlToSqlResult result = sqlBuilder.Build(SELECT_BY_ID);
Assert.True(result.Success);
Assert.AreEqual("SELECT x.* FROM resources x WHERE x.Id=1", result.Statement);
}
/// <summary>
/// Excutes the query.
/// </summary>
/// <param name="database"></param>
/// <param name="sqls">The sqls.</param>
/// <returns>A list of TS.</returns>
public virtual async Task <IEnumerable <T> > ExcuteQueryAsync <T>(string database, IEnumerable <ISql> sqls) where T : class
{
var sql = _sqlBuilder.Build(sqls);
var command = await CreateDbCommandAsync(database);
command.CommandText = sql.SQL;
command.Parameters.AddRange(sql.Parameters);
using var reader = await command.ExecuteReaderAsync().ConfigureAwait(false);
var list = new List <T>();
Func <IGetValue, T> fun = t => _objectFactory.Create <T>(t);
if (typeof(T).IsAssignableFrom(typeof(IRecord)))
{
fun = t => new Record(t) as T;
}
while (await reader.ReadAsync().ConfigureAwait(false))
{
var t = fun(new DataReaderGetValue(reader));
list.Add(t);
}
return(list);
}
public async Task <IEnumerable <TEntity> > ExecuteAsync()
{
IDbCommand cmd = _repository.Context.Connection.CreateCommand();
if (_expression != null)
{
ExpressionToSql parser = new ExpressionToSql(_repository.Context.Options);
ISqlBuilder builder = parser.Visit(_expression);
string whereClause = builder.Build(parser, out DbParameter[] parameters);
cmd.CommandText = $"SELECT * FROM {_repository.TableName} WHERE {whereClause}";
foreach (DbParameter parameter in parameters)
{
cmd.Parameters.Add(parameter);
}
}
else
{
cmd.CommandText = $"SELECT * FROM {_repository.TableName}";
}
return(await cmd.ExecuteReaderAsync(_repository.Mapper));
}
public void TestDbData()
{
var obj = new Students()
{
ClassCount = 1,
ClassId = "11",
School = "dd",
};
var sql = Sql.InsertSql(obj);
var sqlResult = _sqlBuilder.Build(sql);
Assert.AreEqual("insert into `Students`(`School`,`ClassId`,`ClassCount`,`No`) values('dd','11',1,0)", sqlResult.SQL);
sql = Sql.DeleteSql <Students>(t => t.ClassId == "1" && t.ClassCount == 2);
sqlResult = _sqlBuilder.Build(sql);
Assert.AreEqual("delete from `Students` where `Students`.`ClassId` = '1' and `Students`.`ClassCount` = 2", sqlResult.SQL);
sql = Sql.UpdateSql <Students>(new
{
ClassId = "2"
}, t => t.Id == "aa");
sqlResult = _sqlBuilder.Build(sql);
Trace.TraceInformation(sqlResult.SQL);
}
public void Build_Defaults_BuildsCorrectSql()
{
var actual = _builder.Build(ExampleTokenisedSqlA);
Assert.That(actual, Is.EqualTo(DefaultSqlA));
}
/// <summary>
/// Gets the generic sql.
/// </summary>
/// <param name="id">The id.</param>
/// <returns>A string.</returns>
public string GetGenericSql(string id)
{
return(_queryBuilder.Build(Query.Where <T>((T t) => t.Id == id)).SQL);
}
private ICommand createParameterCommand <TDynamic>(ISqlQuery <TEntity> query) => new AnonymousParameterCommand <TDynamic>(_connection.SqlConnection, _sqlBuilder.Build(query));