/// <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()); }