ISqlSelect ISqlSelect.Join <TJoin>(JoinType joinType, ISqlFilter condition, SqlAlias <TJoin> joinAlias)
{
ISqlSelect result;
switch (joinType)
{
case JoinType.Inner:
result = InnerJoin(condition, joinAlias);
break;
case JoinType.Left:
result = LeftJoin(condition, joinAlias);
break;
case JoinType.Right:
result = RightJoin(condition, joinAlias);
break;
case JoinType.Full:
result = FullJoin(condition, joinAlias);
break;
default:
throw new NotSupportedException($"{joinType.ToString()} is not supported");
}
return(result);
}
public SqlSelectInfo Where(ISqlFilter value)
{
var clone = Clone();
clone._where = value;
return(clone);
}
public SqlSelectInfo Having(ISqlFilter value)
{
var clone = Clone();
clone._having = value;
return(clone);
}
public SqlSelect Having(ISqlFilter filter)
{
if (filter == null)
{
throw new ArgumentNullException(nameof(filter));
}
return(CreateSqlSelect(Info.Having(filter.WithParameterPrefix($"{Info.Alias.Value}_h"))));
}
public SqlSelect FullJoin <TJoin>(ISqlFilter condition, SqlAlias <TJoin> joinAlias = null)
{
if (condition == null)
{
throw new ArgumentNullException(nameof(condition));
}
return(CreateSqlSelect(Join(JoinType.Full, condition, joinAlias)));
}
/// <summary>
/// 添加Sql过滤器
/// </summary>
/// <param name="filter">Sql查询过滤器</param>
public static void Add(ISqlFilter filter)
{
if (filter == null)
{
return;
}
Filters.Add(filter);
}
private void AppendFilter(StringBuilder querySb, string clause, ISqlFilter filter, bool parametric,
IReadOnlyList <ModifyQueryPartCallback> modificators)
{
if (filter != null)
{
var sql = parametric ? filter.ParametricSql : filter.RawSql;
querySb.Append(SEPARATOR).Append(clause)
.Append(SEPARATOR_WITH_OFFSET)
.Append(modificators.Aggregate(sql, (result, callback) => callback(result)));
}
else if (modificators.Count > 0)
{
querySb.Append(modificators.Aggregate(string.Empty, (result, callback) => callback(result)));
}
}
protected SqlSelectInfo Join <TJoin>(JoinType joinType, ISqlFilter condition, SqlAlias <TJoin> joinAlias = null)
{
if (condition == null)
{
throw new ArgumentNullException(nameof(condition));
}
if (joinAlias == null)
{
joinAlias = MetadataProvider.AliasFor <TJoin>();
}
if (Info.Joins().Any(j => j.JoinAlias.Value == joinAlias.Value))
{
throw new JoinException($"Alias '{joinAlias.Value}' is already registered");
}
var join = new SqlJoin <TJoin>(joinType, condition, joinAlias);
return(Info.Joins(join));
}
public List <User> GetUsers(ISqlFilter filter)
{
//connestion string --> database è sul mio pc
string connectionString = GetConnectionString();
using (var connection = new MySqlConnection(connectionString))
{
connection.Open();
//Chiamo la dispose (using) per tutti i comandi disposable (chiude connection col database e il reader)
using (var command = connection.CreateCommand())
{
var userFilter = filter.GetMySqlString();
command.CommandText = "SELECT * FROM users WHERE " + userFilter;
using (var reader = command.ExecuteReader())
{
var users = new List <User>();
while (reader.Read())//è improntato sulla riga 0 quindi il while scorre le righe finchè ci sono righe
{
var user = new User();
user.Email = reader.GetString("email");
user.Phone = reader.GetString("phone");
user.Name = reader.GetString("name");
user.Counter = reader.GetInt32("counter");
//Solo il campo notes della tabella è nullable
var ordinal = reader.GetOrdinal("notes");
if (!reader.IsDBNull(ordinal))
{
user.Notes = reader.GetString(ordinal);
}
user.TimeStamp = reader.GetDateTime("timestamp");
users.Add(user);
}
return(users);
}
}
}
}
ISqlSelect ISqlSelect.Having(ISqlFilter filter) => Having(filter);
//-------------------------------------------------------------------------
ISqlSelect ISqlSelect.Where(ISqlFilter filter) => Where(filter);
public SqlSelect<T> Having(ISqlFilter filter)
{
return CreateSqlSelect(Info.Having(filter.WithParameterPrefix("h")));
}
/// <summary>
/// Adds a WHERE clause to the query.
/// </summary>
/// <param name="filter"></param>
public void AddFilter(ISqlFilter filter)
{
this.Filters.Add(filter);
}
public SqlSelect<T> RightJoin<TJoin>(ISqlFilter condition, SqlAlias<TJoin> joinAlias = null)
{
if (condition == null) throw new ArgumentNullException(nameof(condition));
return CreateSqlSelect(Join(JoinType.Right, condition, joinAlias));
}
public SqlJoin(JoinType joinType, ISqlFilter joinCondition, SqlAlias <TJoin> joinAlias)
{
JoinType = joinType;
JoinCondition = joinCondition ?? throw new ArgumentNullException(nameof(joinCondition));
JoinAlias = joinAlias ?? throw new ArgumentNullException(nameof(joinAlias));
}
public SqlSelect<T> Where(ISqlFilter filter)
{
return CreateSqlSelect(Info.Where(filter.WithParameterPrefix("w")));
}
/// <summary>
/// Adds a filter to the where clause
/// </summary>
/// <param name="filter"></param>
/// <returns></returns>
public IDbQueryWhereClause AddFilter(ISqlFilter filter)
{
this.Filters.Add(filter);
return(this);
}