I need to create fluent sql builder. I decided to create many interfaces for each part of SQL query (FROM, WHERE, JOIN etc.) and interfaces for each type of query (SELECT, INSERT, UPDATE etc.). My code:
// BUILDERS
public interface IBuilder
{
}
public interface IConditionBuilder<out TThis> : IBuilder
where TThis : IQuery
{
TThis Or();
TThis Not();
}
public interface IFromBuilder<out TThis> : IBuilder
where TThis : IQuery
{
TThis From(string table);
TThis FromRaw(string expression);
}
public interface IGroupBuilder<out TThis> : IBuilder
where TThis: IQuery
{
TThis GroupBy(params string[] fields);
TThis GroupByRaw(string expression);
}
public interface IHavingBuilder<out TThis> : IConditionBuilder<TThis>
where TThis : IQuery
{
TThis Having<T>(string field, string operation, T value);
TThis HavingRaw(string expression);
}
public interface IJoinBuilder<TThis> : IBuilder
where TThis : IQuery
{
TThis Join(string table, string first, string second);
TThis Join(string table, string first, string second, string op);
TThis Join(string table, string first, string second, string op, string type);
TThis Join(string table, Func<IOnBuilder<TThis>, IOnBuilder<TThis>> on); // PROBLEM HERE
TThis Join(string table, Func<IOnBuilder<TThis>, IOnBuilder<TThis>> on, string type); // PROBLEM HERE
}
public interface IJoinBuilder<TThis, in TQuery> : IJoinBuilder<TThis>
where TThis : IQuery
where TQuery : ISelectQuery
{
TThis Join(TQuery query, string first, string second);
TThis Join(TQuery query, string first, string second, string op);
TThis Join(TQuery query, string first, string second, string op, string type);
TThis Join(TQuery query, Func<IOnBuilder<TThis>, IOnBuilder<TThis>> on); // PROBLEM HERE
TThis Join(TQuery query, Func<IOnBuilder<TThis>, IOnBuilder<TThis>> on, string type); // PROBLEM HERE
}
public interface IOnBuilder<out TThis> : IConditionBuilder<TThis>
where TThis : IQuery
{
TThis On(string first, string second);
TThis On(string first, string second, string op);
TThis OnRaw(string expression);
}
public interface IOrderBuilder<out TThis> : IBuilder
where TThis : IQuery
{
TThis OrderBy(params string[] fields);
TThis OrderByDesc(params string[] fields);
TThis OrderByRaw(string expression);
}
public interface IReturningBuilder<out TThis> : IBuilder
where TThis : IQuery
{
TThis Returning(params string[] fields);
}
public interface ISelectBuilder<out TThis> : IBuilder
{
TThis Select(params string[] columns);
TThis Select(params object[] columns);
TThis SelectRaw(string expression);
}
public interface ISelectBuilder<out TThis, TQuery> : ISelectBuilder<TThis>
where TThis: IQuery
where TQuery: ISelectQuery
{
TThis Select(TQuery query, string alias);
TThis Select(Func<TQuery, TQuery> query, string alias);
}
public interface IWhereBuilder<out TThis> : IConditionBuilder<TThis>
where TThis : IQuery
{
TThis Where(string field, object value);
TThis Where(string field, string op, object value);
TThis WhereBetween<T>(string field, T lower, T higher);
TThis WhereNull(string field);
TThis WhereIn<T>(string field, IEnumerable<T> values);
TThis WhereColumns(string fieldOne, string op, string fieldTwo);
TThis WhereContains(string field, string value);
TThis WhereContains(string field, string value, bool isCaseSensetive);
TThis WhereStarts(string field, string value);
TThis WhereStarts(string field, string value, bool isCaseSensetive);
TThis WhereEnds(string field, string value);
TThis WhereEnds(string field, string value, bool isCaseSensetive);
TThis WhereRaw(string expression);
}
public interface IWhereBuilder<out TThis, TQuery> : IWhereBuilder<TThis>
where TThis : IQuery
where TQuery : ISelectQuery
{
TThis Where(IWhereBuilder<out TThis, TQuery> where);
TThis Where(Func<TQuery, TQuery> where); // PROBLEM HERE
TThis WhereIn(string field, TQuery query);
TThis WhereIn(string field, Func<TQuery, TQuery> query);
TThis WhereExists(TQuery query);
TThis WhereExists(Func<TQuery, TQuery> query);
}
// QUERIES
public interface IQuery
{
string ToSql();
}
public interface ISelectQuery : IQuery,
ISelectBuilder<ISelectQuery>,
IFromBuilder<ISelectQuery, ISelectQuery>,
IWhereBuilder<ISelectQuery, ISelectQuery>,
IJoinBuilder<ISelectQuery>
{
ISelectQuery Distinct();
ISelectQuery Take(long limit);
ISelectQuery Skip(long offset);
ISelectQuery Union(ISelectQuery query);
ISelectQuery Union(Func<ISelectQuery, ISelectQuery> query);
}
I have problems with conditions. Now I can write something like this:
query.Where("Id", 10).Or().WhereBetween("Id", 20, 25);
I want to have an ability to write something like this:
query.Where(w=>w.Where("id", 11).Or().Where("id", 15))
// OR
query.Join("table", on => on.On("Id","=","IdTwo").Or().On("Id", "=", "IdThree");
I have no idea how to do it.
Aucun commentaire:
Enregistrer un commentaire