public virtual string ToSelectStatement(ModelDefinition modelDef, SqlClauseContext sqlClause)
{
StringBuilder sb = new StringBuilder(sqlClause.Select)
.AppendLine(sqlClause.From)
.AppendLine($"WHERE {sqlClause.Where}")
.AppendLine(sqlClause.Group)
.AppendLine(sqlClause.Having);
if (!string.IsNullOrWhiteSpace(sqlClause.OrderBy))
{
sb.AppendLine(sqlClause.OrderBy);
}
if (sqlClause.Take > 0 || sqlClause.Skip > 0)
{
sb.AppendLine("LIMIT ");
if (sqlClause.Skip == 0)
{
sb.Append(sqlClause.Take);
}
else
{
sb.Append($"{sqlClause.Take} OFFSET {sqlClause.Skip}");
}
}
return(sb.ToString());
}
public string ToCountStatement(ModelDefinition modelDef, SqlClauseContext sqlClause)
{
return(new StringBuilder("SELECT COUNT(1)")
.AppendLine(sqlClause.From)
.AppendLine($"WHERE {sqlClause.Where}")
.AppendLine(sqlClause.Group)
.AppendLine(sqlClause.Having).ToString());
}
public override string ToSelectStatement(ModelDefinition modelDef, SqlClauseContext sqlClause)
{
StringBuilder sb = new StringBuilder(sqlClause.Select)
.AppendLine(sqlClause.From)
.AppendLine($"WHERE {sqlClause.Where}")
.AppendLine(sqlClause.Group)
.AppendLine(sqlClause.Having);
if (sqlClause.Take == 0 && sqlClause.Skip == 0)
{
return(sb.AppendLine(sqlClause.OrderBy).ToString());
}
if (sqlClause.Skip == 0)
{
if (sqlClause.Take == int.MaxValue)
{
return(sb.AppendLine(sqlClause.OrderBy).ToString());
}
return(new StringBuilder("SELECT")
.AppendLine(sqlClause.Distinct)
.AppendLine(" TOP " + sqlClause.Take)
.AppendLine(string.Join(",", sqlClause.SelectFields))
.AppendLine(sqlClause.From)
.AppendLine($"WHERE {sqlClause.Where}")
.AppendLine(sqlClause.Group)
.AppendLine(sqlClause.Having)
.AppendLine(sqlClause.OrderBy).ToString());
}
if (string.IsNullOrWhiteSpace(sqlClause.OrderBy))
{
sqlClause.OrderBy = $"ORDER BY {modelDef.PrimaryKey}";
}
return(new StringBuilder("SELECT * FROM (")
.AppendLine($"SELECT {sqlClause.Distinct} {string.Join(",", sqlClause.SelectFields)} , ROW_NUMBER() OVER ({sqlClause.OrderBy}) As RowNum")
.AppendLine(sqlClause.From)
.AppendLine($"WHERE {sqlClause.Where}")
.AppendLine(sqlClause.Group)
.AppendLine(sqlClause.Having)
.AppendLine(") AS RowConstrainedResult")
.AppendLine($"WHERE RowNum > {sqlClause.Skip} AND RowNum <= {(sqlClause.Take == int.MaxValue ? sqlClause.Take : sqlClause.Skip + sqlClause.Take)}")
.ToString());
}
public virtual string ToUpdateStatement(object fields, ModelDefinition modelDef, SqlClauseContext sqlClause)
{
StringBuilder sbSql = new StringBuilder($"UPDATE {modelDef.Name}");
bool allUpdates = sqlClause.UpdateFields == null || sqlClause.UpdateFields.Count() <= 0;
if (allUpdates)
{
modelDef = fields.GetType().GetModelDefinition();
List <string> updateFields = new List <string>();
foreach (FieldDefinition field in modelDef.FieldDefinitions)
{
sqlClause.Params.Add(Prefix + field.Name, field.GetValue(fields));
if (field.IsPrimaryKey)
{
sqlClause.Where = string.Concat(field, "=", Prefix, field);
}
else
{
updateFields.Add(field.Name);
}
}
sqlClause.UpdateFields = updateFields;
}
return(sbSql.AppendLine($"SET {string.Join(",", sqlClause.UpdateFields.Select(field => string.Concat(field, "=", Prefix, field)))}")
.AppendLine($"WHERE {sqlClause.Where}").ToString());
}
public virtual string ToInsertStatement(object fields, ModelDefinition modelDef, SqlClauseContext sqlClause)
{
StringBuilder sbSql = new StringBuilder($"INSERT INTO {modelDef.Name}");
bool allFields = sqlClause.InsertFields == null || sqlClause.InsertFields.Count() <= 0;
if (allFields)
{
modelDef = fields.GetType().GetModelDefinition();
List <string> insertFields = new List <string>();
foreach (FieldDefinition field in modelDef.FieldDefinitions)
{
if (field.IsPrimaryKey)
{
continue;
}
else
{
sqlClause.Params.Add(Prefix + field.Name, field.GetValue(fields));
insertFields.Add(field.Name);
}
}
sqlClause.InsertFields = insertFields;
}
return(sbSql.AppendLine($"({string.Join(",", sqlClause.InsertFields)})")
.AppendLine($"VALUES ({string.Join(",", sqlClause.InsertFields.Select(field => Prefix + field))})")
.AppendLine(SelectIdentitySql).ToString());
}
public virtual string ToDeleteStatement(ModelDefinition modelDef, SqlClauseContext sqlClause)
{
return($"DELETE FROM {modelDef.Name} WHERE {sqlClause.Where}");
}
