/// <summary>
/// Returns a dynamic PagedResult. Result properties are Items, TotalPages, and TotalRecords.
/// </summary>
public dynamic Paged(object where = null, string orderBy = "", object columns = null, int pageSize = 20,
int currentPage = 1, params object[] args)
{
var whereInfo = GetWhereInfo(where, null, args);
var countSql = string.Format("SELECT COUNT({0}) FROM {1} {2}", this.PrimaryKeyField, this.TableName, whereInfo.Sql);
if (String.IsNullOrWhiteSpace(orderBy))
{
orderBy = this.PrimaryKeyField;
}
var sql = this.Database.Dialect.GetPagingSql(this.TableName, GetColumns(columns), orderBy, whereInfo.Sql, pageSize, currentPage);
var command = new DynamicCommand {
Sql = sql, Arguments = whereInfo.Args, Context = this.GetContext("Paged(Results)")
};
var queryCommand = command;
var command1 = new DynamicCommand {
Sql = countSql, Arguments = whereInfo.Args, Context = this.GetContext("Paged(Count)")
};
var whereCommand = command1;
var totalRecords = (int)this.Database.Scalar(whereCommand);
return(new
{
CurrentPage = currentPage,
TotalRecords = totalRecords,
TotalPages = (totalRecords + (pageSize - 1)) / pageSize,
Items = this.Database.Query(queryCommand)
}.ToExpando());
}
/// <summary>
/// Returns a single row from the database
/// </summary>
public virtual dynamic Single(object key = null, object where = null, object columns = null, params object[] args)
{
var sql = string.Format("SELECT {0} FROM {1}", GetColumns(columns), this.TableName);
var whereInfo = this.GetWhereInfo(where, key, args);
var command = new DynamicCommand {
Sql = sql + whereInfo.Sql, Arguments = whereInfo.Args, Context = this.GetContext("Single")
};
return(this.Database.Fetch(command).FirstOrDefault());
}
private DbCommand CreateDbCommand(DynamicCommand command, DbTransaction tx = null,
DbConnection connection = null)
{
var result = this._factory.CreateCommand();
result.Connection = connection;
result.CommandText = command.Sql;
result.Transaction = tx;
result.AddParams(command.Arguments);
return(result);
}
/// <summary>
/// Removes one or more records from the DB according to the passed-in WHERE
/// </summary>
protected virtual DynamicCommand CreateDeleteCommand(object key = null, object where = null, params object[] args)
{
var sql = String.Format("DELETE FROM {0}", this.TableName);
var whereInfo = this.GetWhereInfo(where, null, args);
var command = new DynamicCommand
{
Sql = sql + whereInfo.Sql,
Arguments = whereInfo.Args,
Context = GetContext("All")
};
return(command);
}
/// <summary>
/// Returns a single result
/// </summary>
public object Scalar(DynamicCommand command)
{
var queryTraceEventArgs = new QueryTraceEventArgs(command.Sql, command.Arguments, command.Context);
QueryTrace.InvokeQueryBegin(queryTraceEventArgs);
using (var conn = this.OpenConnection())
{
var scalar = this.CreateDbCommand(command, connection: conn).ExecuteScalar();
QueryTrace.InvokeQueryEnd(queryTraceEventArgs);
return(scalar);
}
}
/// <summary>
/// Returns all records complying with the passed-in WHERE clause and arguments, ordered as specified, limited (TOP) by limit.
/// </summary>
public virtual IEnumerable<dynamic> All(object where = null, string orderBy = "", int limit = 0, object columns = null,
params object[] args)
{
var sql = String.Format(limit > 0 ? "SELECT TOP " + limit + " {0} FROM {1}" : "SELECT {0} FROM {1}",
GetColumns(columns), this.TableName);
var whereInfo = this.GetWhereInfo(where, null, args);
var command1 = new DynamicCommand {Sql = sql + whereInfo.Sql, Arguments = whereInfo.Args, Context = GetContext("All")};
var command = command1;
if (!String.IsNullOrWhiteSpace(orderBy))
{
command.Sql += (orderBy.Trim().StartsWith("order by", StringComparison.CurrentCultureIgnoreCase)
? " "
: " ORDER BY ") + orderBy;
}
return this.Database.Query(command);
}
/// <summary>
/// Returns all records complying with the passed-in WHERE clause and arguments, ordered as specified, limited (TOP) by limit.
/// </summary>
public virtual IEnumerable <dynamic> All(object where = null, string orderBy = "", int limit = 0, object columns = null,
params object[] args)
{
var sql = String.Format(limit > 0 ? "SELECT TOP " + limit + " {0} FROM {1}" : "SELECT {0} FROM {1}",
GetColumns(columns), this.TableName);
var whereInfo = this.GetWhereInfo(where, null, args);
var command1 = new DynamicCommand {
Sql = sql + whereInfo.Sql, Arguments = whereInfo.Args, Context = GetContext("All")
};
var command = command1;
if (!String.IsNullOrWhiteSpace(orderBy))
{
command.Sql += (orderBy.Trim().StartsWith("order by", StringComparison.CurrentCultureIgnoreCase)
? " "
: " ORDER BY ") + orderBy;
}
return(this.Database.Query(command));
}
/// <summary>
/// Enumerates the reader yielding the results
/// </summary>
public IEnumerable <object> Query(DynamicCommand command)
{
var queryTraceEventArgs = new QueryTraceEventArgs(command.Sql, command.Arguments, command.Context);
QueryTrace.InvokeQueryBegin(queryTraceEventArgs);
using (var conn = this.OpenConnection())
{
var dbCommand = this.CreateDbCommand(command, connection: conn);
using (var rdr = dbCommand.ExecuteReader(CommandBehavior.CloseConnection))
{
QueryTrace.InvokeQueryEnd(queryTraceEventArgs);
while (rdr.Read())
{
var d = (IDictionary <string, object>) new ExpandoObject();
for (var i = 0; i < rdr.FieldCount; i++)
{
var value = rdr[i];
d.Add(rdr.GetName(i), DBNull.Value.Equals(value) ? null : value);
}
yield return(d);
}
}
}
}
private DbCommand CreateDbCommand(DynamicCommand command, DbTransaction tx = null,
DbConnection connection = null)
{
var result = this._factory.CreateCommand();
result.Connection = connection;
result.CommandText = command.Sql;
result.Transaction = tx;
result.AddParams(command.Arguments);
return result;
}
/// <summary>
/// Returns a single result
/// </summary>
public object Scalar(DynamicCommand command)
{
var queryTraceEventArgs = new QueryTraceEventArgs(command.Sql, command.Arguments, command.Context);
QueryTrace.InvokeQueryBegin(queryTraceEventArgs);
using (var conn = this.OpenConnection())
{
var scalar = this.CreateDbCommand(command, connection: conn).ExecuteScalar();
QueryTrace.InvokeQueryEnd(queryTraceEventArgs);
return scalar;
}
}
/// <summary>
/// Enumerates the reader yielding the results
/// </summary>
public IEnumerable<object> Query(DynamicCommand command)
{
var queryTraceEventArgs = new QueryTraceEventArgs(command.Sql, command.Arguments, command.Context);
QueryTrace.InvokeQueryBegin(queryTraceEventArgs);
using (var conn = this.OpenConnection())
{
var dbCommand = this.CreateDbCommand(command, connection: conn);
using (var rdr = dbCommand.ExecuteReader(CommandBehavior.CloseConnection))
{
QueryTrace.InvokeQueryEnd(queryTraceEventArgs);
while (rdr.Read())
{
var d = (IDictionary<string, object>) new ExpandoObject();
for (var i = 0; i < rdr.FieldCount; i++)
{
var value = rdr[i];
d.Add(rdr.GetName(i), DBNull.Value.Equals(value) ? null : value);
}
yield return d;
}
}
}
}
/// <summary>
/// Runs a query against the database.
/// </summary>
public IList<object> Fetch(DynamicCommand command)
{
return Query(command).ToList();
}
/// <summary>
/// Removes one or more records from the DB according to the passed-in WHERE
/// </summary>
protected virtual DynamicCommand CreateDeleteCommand(object key = null, object where = null, params object[] args)
{
var sql = String.Format("DELETE FROM {0}", this.TableName);
var whereInfo = this.GetWhereInfo(where, null, args);
var command = new DynamicCommand
{
Sql = sql + whereInfo.Sql,
Arguments = whereInfo.Args,
Context = GetContext("All")
};
return command;
}
/// <summary>
/// Returns a single row from the database
/// </summary>
public virtual dynamic Single(object key = null, object where = null, object columns = null, params object[] args)
{
var sql = string.Format("SELECT {0} FROM {1}", GetColumns(columns), this.TableName);
var whereInfo = this.GetWhereInfo(where, key, args);
var command = new DynamicCommand {Sql = sql + whereInfo.Sql, Arguments = whereInfo.Args, Context = this.GetContext("Single")};
return this.Database.Fetch(command).FirstOrDefault();
}
/// <summary>
/// Returns a dynamic PagedResult. Result properties are Items, TotalPages, and TotalRecords.
/// </summary>
public dynamic Paged(object where = null, string orderBy = "", object columns = null, int pageSize = 20,
int currentPage = 1, params object[] args)
{
var whereInfo = GetWhereInfo(where, null, args);
var countSql = string.Format("SELECT COUNT({0}) FROM {1} {2}", this.PrimaryKeyField, this.TableName, whereInfo.Sql);
if (String.IsNullOrWhiteSpace(orderBy))
{
orderBy = this.PrimaryKeyField;
}
var sql = this.Database.Dialect.GetPagingSql(this.TableName, GetColumns(columns), orderBy, whereInfo.Sql, pageSize, currentPage);
var command = new DynamicCommand {Sql = sql, Arguments = whereInfo.Args, Context = this.GetContext("Paged(Results)")};
var queryCommand = command;
var command1 = new DynamicCommand {Sql = countSql, Arguments = whereInfo.Args, Context = this.GetContext("Paged(Count)")};
var whereCommand = command1;
var totalRecords = (int) this.Database.Scalar(whereCommand);
return new
{
CurrentPage = currentPage,
TotalRecords = totalRecords,
TotalPages = (totalRecords + (pageSize - 1))/pageSize,
Items = this.Database.Query(queryCommand)
}.ToExpando();
}
/// <summary>
/// Runs a query against the database.
/// </summary>
public IList <object> Fetch(DynamicCommand command)
{
return(Query(command).ToList());
}