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}"); }