public async Task <List <T> > SimpleSQLQueryAsync <T>(SimpleSQLQueryModel queryModel) where T : class, new()
{
var result = await client.Sql.QueryAsync(t => t.FetchSize(queryModel.FetchSize).Format(queryModel.Format.ToString()).Query(queryModel.Sql)).ConfigureAwait(false);
var rows = result.Rows.ToList();
var colunms = result.Columns.ToList();
List <T> entityList = new List <T>();
GetDebugInfo(result);
foreach (var row in rows)
{
T entity = new T();
int index = 0;
foreach (var column in colunms)
{
if (!column.Name.Contains("."))
{
var val = row[index];
var emitSetter = EmitHelper.EmitSetter <T>(column.Name);
if (column.Type == "text" || column.Type == "keyword")
{
emitSetter(entity, val.As <string>());
}
else if (column.Type == "datetime")
{
emitSetter(entity, val.As <DateTime>());
}
else if (column.Type == "long")
{
emitSetter(entity, val.As <long>());
}
else if (column.Type == "integer")
{
emitSetter(entity, val.As <int>());
}
else if (column.Type == "float")
{
emitSetter(entity, val.As <float>());
}
else if (column.Type == "double")
{
emitSetter(entity, val.As <double>());
}
else if (column.Type == "boolean")
{
emitSetter(entity, val.As <bool>());
}
}
index++;
}
entityList.Add(entity);
}
return(entityList);
}
public async Task <List <T> > SimpleSqlQueryAsync <T>(SimpleSQLQueryModel queryModel) where T : class, new()
{
var result = await client.Sql.QueryAsync(t => t.FetchSize(queryModel.FetchSize).Format(queryModel.Format.ToString()).Query(queryModel.Sql)).ConfigureAwait(false);
var rows = result.Rows.ToList();
var colunms = result.Columns.ToList();
List <T> entityList = new List <T>();
GetDebugInfo(result);
for (int i = 0; i < rows.Count; i++)
{
var row = rows[i];
T entity = new T();
for (int j = 0; j < colunms.Count; j++)
{
var column = colunms[j];
var val = row[j];
if (!column.Name.Contains("."))
{
var emitSetter = EmitHelper.EmitSetter <T>(column.Name);
emitSetter(entity, GetValue(column, val));
}
else
{
var type = typeof(T);
var pop = type.GetProperties(BindingFlags.Instance | BindingFlags.IgnoreCase | BindingFlags.Public | BindingFlags.NonPublic);
for (int n = 0; n < pop.Length; n++)
{
string childClass = column.Name.Split('.')[0];
string childField = column.Name.Split('.')[1];
object childValue = GetValue(column, val);
if (pop[n].Name.ToLower() == childClass.ToLower())
{
var childType = pop[n].PropertyType;
var childEntityClass = childType.Assembly.CreateInstance(childType.FullName, true);
var popchilds = childType.GetProperties(BindingFlags.Instance | BindingFlags.IgnoreCase | BindingFlags.Public | BindingFlags.NonPublic);
var popchild = popchilds.First(t => t.Name.ToLower() == childField.ToLower());
var emitSetter = EmitHelper.CreatePropertiesFunc(popchilds);
popchild.SetValue(childEntityClass, childValue);
pop[n].SetValue(entity, childEntityClass);
}
}
}
}
entityList.Add(entity);
}
return(entityList);
}
/// <summary>
/// 简单查询
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="queryModel"></param>
/// <returns></returns>
public async Task <List <T> > SimpleSqlQueryAsync <T>(SimpleSQLQueryModel queryModel) where T : class, new()
{
return(await repository.SimpleSqlQueryAsync <T>(queryModel).ConfigureAwait(false));
}
public async Task <ActionResult> SimpleSqlQueryAsync([FromQuery] SimpleSQLQueryModel queryModel)
{
var queryResponse = await queryDemoService.SimpleSqlQueryAsync <DemoEntity>(queryModel).ConfigureAwait(false);
return(Ok(queryResponse));
}
public async Task <string> SqlQueryToJsonAsync(SimpleSQLQueryModel queryModel)
{
var result = await client.Sql.QueryAsync(t => t.FetchSize(queryModel.FetchSize).Format(queryModel.Format.ToString()).Query(queryModel.Sql)).ConfigureAwait(false);
var rows = result.Rows.ToList();
var colunms = result.Columns.ToList();
var jsonString = new StringBuilder();
GetDebugInfo(result);
jsonString.Append("[");
for (int i = 0; i < rows.Count; i++)
{
var row = rows[i];
jsonString.Append("{");
for (int j = 0; j < colunms.Count; j++)
{
var column = colunms[j];
if (!column.Name.Contains("."))
{
var val = row[j];
bool isLastOne = (j == colunms.Count - 1);
var comma = isLastOne ? "" : ",";
if (column.Type == "text" || column.Type == "keyword")
{
jsonString.Append($"\"{column.Name}\":\"{ val.As<string>()}\"{comma}");
}
else if (column.Type == "datetime")
{
jsonString.Append($"\"{column.Name}\":\"{ val.As<DateTime>()}\"{comma}");
}
else if (column.Type == "long")
{
jsonString.Append($"\"{column.Name}\":{ val.As<long>()}{comma}");
}
else if (column.Type == "integer")
{
jsonString.Append($"\"{column.Name}\":{ val.As<int>()}{comma}");
}
else if (column.Type == "float")
{
jsonString.Append($"\"{column.Name}\":{ val.As<float>()}{comma}");
}
else if (column.Type == "double")
{
jsonString.Append($"\"{column.Name}\":{ val.As<double>()}{comma}");
}
else if (column.Type == "boolean")
{
var boolVal = val.As <bool>().ToString().ToLower();
jsonString.Append($"\"{column.Name}\":{ boolVal}{comma}");
}
else
{
jsonString.Append($"\"{column.Name}\":\"{ val.As<string>()}\"{comma}");
}
}
else
{
}
if (i == rows.Count - 1)
{
jsonString.Append("}");
}
else
{
jsonString.Append("},");
}
}
}
jsonString.Append("]");
return(jsonString.ToString());
}
