/// <summary>
/// Selects first row matching criteria and maps it to poco
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="db"></param>
/// <param name="condition"></param>
/// <returns></returns>
public static async Task<T> GetAsync<T>(this DbConnection db,Expression<Func<T, bool>> condition,CancellationToken token)
{
var builder = new ExpressionSqlBuilder<T>(db.GetProvider().BuilderHelper);
builder
.WriteSelect()
.WriteSelectAllColumns()
.WriteFrom()
.Where(condition);
return await db.QuerySingleAsync<T>(token,builder.ToString(), builder.Parameters.ToArray());
}
public static int DeleteFrom <T>(this IAccessDb db, Expression <Func <T, bool> > criteria = null)
{
var builder = new ExpressionSqlBuilder <T>(db.Provider.BuilderHelper);
builder.WriteDelete();
if (criteria != null)
{
builder.Where(criteria);
}
return(db.ExecuteCommand(builder.ToString(), builder.Parameters.ToArray()));
}
/// <summary>
///
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="db"></param>
/// <param name="condition"></param>
/// <returns></returns>
public static IEnumerable <T> Query <T>(this DbConnection db, Expression <Func <T, bool> > condition)
{
var builder = new ExpressionSqlBuilder <T>(db.GetProvider().BuilderHelper);
builder
.WriteSelect()
.WriteSelectAllColumns()
.WriteFrom()
.Where(condition);
return(db.Query <T>(builder.ToString(), builder.Parameters.ToArray()));
}
public static long Count <T>(this DbConnection db, Expression <Func <T, bool> > criteria = null)
{
var builder = new ExpressionSqlBuilder <T>(db.GetProvider().BuilderHelper);
builder.Append("select count(*) from ").WriteTableName();
if (criteria != null)
{
builder.Where(criteria);
}
return(db.GetValue <long>(builder.ToString(), builder.Parameters.ToArray()));
}
public static int DeleteFrom <T>(this DbConnection db, Expression <Func <T, bool> > criteria = null, string schemaName = null)
{
var builder = new ExpressionSqlBuilder <T>(db.GetProvider().BuilderHelper, schemaName);
builder.WriteDelete();
if (criteria != null)
{
builder.Where(criteria);
}
return(db.Execute(builder.ToString(), builder.Parameters.ToArray()));
}
/// <summary>
/// Selects first row matching criteria and maps it to poco
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="db"></param>
/// <param name="condition"></param>
/// <returns></returns>
public static T Get <T>(this DbConnection db, Expression <Func <T, bool> > condition, string schemaName = null)
{
var builder = new ExpressionSqlBuilder <T>(db.GetProvider().BuilderHelper, schemaName);
builder
.WriteSelect()
.WriteSelectAllColumns()
.WriteFrom()
.Where(condition);
return(db.QuerySingle <T>(builder.ToString(), builder.Parameters.ToArray()));
}
/// <summary>
/// Selects first row matching criteria and maps it to poco
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="db"></param>
/// <param name="condition"></param>
/// <returns></returns>
public static T Get <T>(this IAccessDb db, Expression <Func <T, bool> > condition)
{
var builder = new ExpressionSqlBuilder <T>(db.Provider.BuilderHelper);
builder
.WriteSelect()
.WriteSelectAllColumns()
.WriteFrom()
.Where(condition);
return(db.FirstOrDefault <T>(builder.ToString(), builder.Parameters.ToArray()));
}
/// <summary>
///
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="db"></param>
/// <param name="condition"></param>
/// <returns></returns>
public static async Task <IEnumerable <T> > QueryAsync <T>(this DbConnection db, Expression <Func <T, bool> > condition, CancellationToken token)
{
var builder = new ExpressionSqlBuilder <T>(db.GetProvider().BuilderHelper);
builder
.WriteSelect()
.WriteSelectAllColumns()
.WriteFrom()
.Where(condition);
return(await db.FetchAsync <T>(token, builder.ToString(), builder.Parameters.ToArray()).ConfigureAwait(false));
}
/// <summary>
/// Returns only the specified column value
/// </summary>
/// <typeparam name="T"></typeparam>
/// <typeparam name="R"></typeparam>
/// <param name="db"></param>
/// <param name="selector">Column selector</param>
/// <param name="criteria">Selection criteria</param>
/// <returns></returns>
public static async Task<R> GetColumnValueAsync<T, R>(this DbConnection db, Expression<Func<T, R>> selector,
Expression<Func<T, bool>> criteria,CancellationToken token)
{
selector.MustNotBeNull();
var builder = new ExpressionSqlBuilder<T>(db.GetProvider().BuilderHelper);
builder
.WriteSelect()
.WriteSelectColumn(selector)
.WriteFrom()
.Where(criteria);
return await db.GetValueAsync<R>(token,builder.ToString(), builder.Parameters.ToArray());
}
public static IEnumerable <R> QueryColumn <T, R>(this DbConnection db, Expression <Func <T, R> > selector,
Expression <Func <T, bool> > criteria)
{
selector.MustNotBeNull();
var builder = new ExpressionSqlBuilder <T>(db.GetProvider().BuilderHelper);
builder
.WriteSelect()
.WriteSelectColumn(selector)
.WriteFrom()
.Where(criteria);
return(db.Query <R>(builder.ToString(), builder.Parameters.ToArray()));
}
/// <summary>
/// Returns only the specified column value
/// </summary>
/// <typeparam name="T"></typeparam>
/// <typeparam name="R"></typeparam>
/// <param name="db"></param>
/// <param name="selector">Column selector</param>
/// <param name="criteria">Selection criteria</param>
/// <returns></returns>
public static R GetColumnValue <T, R>(this DbConnection db, Expression <Func <T, R> > selector,
Expression <Func <T, bool> > criteria, string schemaName = null)
{
selector.MustNotBeNull();
var builder = new ExpressionSqlBuilder <T>(db.GetProvider().BuilderHelper, schemaName);
builder
.WriteSelect()
.WriteSelectColumn(selector)
.WriteFrom()
.Where(criteria);
return(db.GetValue <R>(builder.ToString(), builder.Parameters.ToArray()));
}
/// <summary>
/// Checks if the table has any rows with optional criteria
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="db"></param>
/// <param name="criteria"></param>
/// <returns></returns>
public static bool HasAnyRows <T>(this DbConnection db, Expression <Func <T, bool> > criteria = null)
{
var builder = new ExpressionSqlBuilder <T>(db.GetProvider().BuilderHelper);
builder
.Append("select 1 from ")
.WriteTableName()
.Append(" where exists(select * from ")
.WriteTableName();
if (criteria != null)
{
builder.Where(criteria);
}
builder.Append(")");
var r = db.GetValue <int?>(builder.ToString(), builder.Parameters.ToArray());
if (r.HasValue)
{
return(true);
}
return(false);
}
public string GetSql()
{
_builder.EndEnumeration();
return(_builder.ToString());
}