public SelectStatementCombine OrderBy(IOrderByExpression order_by_expression) { if (_OrderBy == null) { _OrderBy = new List <IOrderByExpression>(); } _OrderBy.Add(order_by_expression); return(this); }
public virtual IList <LM> SelectListModel <LM, QM>(Expression <Func <E, LM> > selector, QM model = null) where LM : BaseListModel, new() where QM : BaseQueryModel, new() { Expression <Func <E, bool> > express = GetExpress(model); IOrderByExpression <E> orderby = GetOrderBy(); var list = Gets(express, selector, model.PageIndex, model.PageSize, orderby); return(list); }
protected virtual SelectStatement GetSelectStatement(ICondition criteria, IOrderByExpression order_by_expression) { var colNames = new List <string>(); var select = new SelectStatement { ConnectionStringName = _connectionStringName }; select.From(GetBaseSelectStatement(), _entityName); for (var x = 0; x < _metadata.Columns.Count; x++) { string colName = _metadata.Columns[x].Name; colNames.Add(colName); select.Column(new SqlColumnName(_metadata.Columns[x].Name), colName); } if (_associations != null) { foreach (var belong in _associations) { var omapping = MappingFactory.GetMapping(belong.OtherMappingType); for (var x = 0; x < omapping.Columns.Count; x++) { string colName = belong.Name + "." + omapping.Columns[x].Name; if (colNames.Contains(colName)) { continue; } else { colNames.Add(colName); } select.Column(new SqlColumnName(colName), colName); } } } if (criteria != null) { select.Where(criteria); } if (order_by_expression != null) { select.OrderBy(order_by_expression); } return(select); }
public IEnumerable <TEntity> GetAll(ICondition filter, IOrderByExpression order_by_expression) { var select = GetSelectStatement(filter, order_by_expression); var reader = select.ExecuteReader(); var list = new List <TEntity>(); while (reader.Read()) { list.Add(ReadEntity(reader)); } reader.Close(); return(list); }
public IEnumerable <TEntity> GetAll(ICondition criteria, IOrderByExpression order_by_expression, int start_record, int max_records) { var select = GetSelectStatement(criteria, order_by_expression); var reader = select.ExecuteReader(start_record); var list = new List <TEntity>(); for (var i = 0; i < max_records; i++) { if (reader.Read()) { list.Add(ReadEntity(reader)); } else { break; } } reader.Close(); return(list); }
public virtual IList <E> GetList <QM>(QM model = null) where QM : BaseQueryModel, new() { if (model == null) { model = new QM(); } Expression <Func <E, bool> > express = GetExpress(model); IOrderByExpression <E> orderby = GetOrderBy(); IList <E> list = null; if (!model.IsNotTracking) { list = Gets(express, model.PageIndex, model.PageSize, orderby); } else { list = GetsNoTracking(express, model.PageIndex, model.PageSize, orderby); } return(list); }
public virtual void LoadData(string table_name, ICondition criteria, IOrderByExpression order_by, int start_record, int max_records) { var myTable = this.Tables[table_name]; var select = GetSelectStatement(table_name, criteria, order_by); myTable.Clear(); IDataReader reader = select.ExecuteReader(start_record); for (int i = 0; i < max_records; i++) { if (reader.Read()) { var row = myTable.NewRow(); FillRowFromReader(reader, row); myTable.Rows.Add(row); } else { break; } } reader.Close(); myTable.AcceptChanges(); }
public virtual TEntity GetOne(ICondition filter, IOrderByExpression order_by) { var select = GetSelectStatement(filter, order_by); var reader = select.ExecuteReader(); TEntity entity = default(TEntity); if (reader.Read()) { entity = ReadEntity(reader); } reader.Close(); if (entity == null) { return(default(TEntity)); } if (_associations.Count() > 0) { foreach (IAssociationMapping _association in _associations) { var amapping = MappingFactory.GetMapping(_association.OtherMappingType); object val = entity[_association.ThisKeys[0]]; if (val == null) { continue; } int colCount = amapping.Columns.Count; var select2 = SqlUtils.CreateSelectStatement(amapping); select2.ConnectionStringName = select.ConnectionStringName; select2.Where(Condition.IsEqual(_association.OtherKeys[0], new SqlNumber((int)val))); var values = new Dictionary <string, object>(); var reader2 = select2.ExecuteReader(); if (reader2.Read()) { if (entity[_association.Name] == null) { entity[_association.Name] = Activator.CreateInstance(entity.GetProperty(_association.Name).PropertyType, (int)val); } Entity _prop = entity[_association.Name] as Entity; for (int f = 0; f < reader2.FieldCount; f++) { _prop[reader2.GetName(f)] = reader2.GetValue(f); } } reader2.Close(); } } if (_hasManyAssociations.Count() == 0) { return(entity); } foreach (IAssociationMapping hma in _hasManyAssociations) { var omapping = MappingFactory.GetMapping(hma.OtherMappingType); int colCount = omapping.Columns.Count; var select2 = SqlUtils.CreateSelectStatement(omapping); select2.ConnectionStringName = select.ConnectionStringName; ICondition idCriteria = IdentityInsert ? Conditions.IsEqual(hma.OtherKeys[0], new SqlNumber(entity.Id.ToString())) : Conditions.IsEqual(hma.OtherKeys[0], new SqlString(entity.Id.ToString())); select2.Where(idCriteria); var reader2 = select2.ExecuteReader(); while (reader2.Read()) { var values = new Dictionary <string, object>(); for (int f = 0; f < reader2.FieldCount; f++) { values.Add(reader2.GetName(f), reader2.GetValue(f)); } entity.AddValueObject(hma.Name, values); } reader2.Close(); } return(entity); }
public virtual void LoadData(ICondition criteria, IOrderByExpression order_by, int start_record, int max_records) { LoadData(this.Tables[0].TableName, criteria, order_by, start_record, max_records); }
public virtual void LoadData(string table_name, ICondition criteria, IOrderByExpression order_by) { LoadData(table_name, GetSelectStatement(table_name, criteria, order_by).ExecuteReader()); }
public void LoadData(ICondition criteria, IOrderByExpression order_by) { LoadData(this.Tables[0].TableName, criteria, order_by); }
protected virtual SelectStatement GetSelectStatement(string table_name, ICondition criteria, IOrderByExpression order_by) { var select = new SelectStatement() { ConnectionStringName = _connectionStringName }; var myTable = this.Tables[table_name]; foreach (DataColumn col in myTable.Columns) { if (IgnoreMemoFields && col.ExtendedProperties["Memo"] != null) { continue; } if (col.ExtendedProperties.ContainsKey("SelectExpression")) { select.Column((ISqlExpression)col.ExtendedProperties["SelectExpression"], col.Caption); } else { select.Column(new SqlColumnName(col.ColumnName), col.ColumnName); } } select.From((SelectStatement)myTable.ExtendedProperties["SelectStatement"], table_name); if (criteria != null) { select.Where(criteria); } if (order_by != null) { select.OrderBy(order_by); } return(select); }
public OrderBy(IOrderByExpression sort) { var orders = sort.Fields.Select(field => $" `{field.Property}` {field.Order} ").ToList(); Sql = $" ORDER BY {string.Join(",", orders)}"; }
public override void GetSql(DbTarget db_target, ref StringBuilder sql) { if (_Selects == null) { return; } int count = _Selects.Count; string combine = ""; switch (_CombineType) { case CombineType.Union: combine = " UNION "; break; case CombineType.UnionAll: combine = " UNION ALL "; break; case CombineType.Intersect: combine = " INTERSECT "; break; case CombineType.Except: combine = " EXCEPT "; break; case CombineType.Minus: combine = " MINUS "; break; } for (int x = 0; x < count; x++) { _Selects[x].GetSql(db_target, ref sql); if (x + 1 < count) { sql.Append(combine); } } if (_OrderBy != null) { count = _OrderBy.Count; if (count > 0) { sql.Append(" ORDER BY "); for (int x = 0; x < count; x++) { IOrderByExpression item = _OrderBy[x]; item.GetSql(db_target, ref sql); if (x + 1 < count) { sql.Append(","); } } } } }
public override void GetSql(DbTarget db_target, ref StringBuilder sql) { sql.Append("SELECT "); if (IsDistinct) { sql.Append("DISTINCT "); } if (db_target == DbTarget.SqlServer && Top > 0) { sql.Append(String.Format("TOP {0} ", _Top)); } int count = Columns.Count; if (count == 0) { sql.Append(" * "); } else { for (int x = 0; x < count; x++) { if (x > 0) { sql.Append(","); } SelectColumn col = _Columns[x]; col.GetSql(db_target, ref sql); } } if (_From != null) { _From.GetSql(db_target, ref sql); } if (_Where != null && _Where.Count > 0) { _Where.GetSql(db_target, ref sql); } if (_GroupBy != null) { count = _GroupBy.Count; if (count > 0) { sql.Append(" GROUP BY "); for (int x = 0; x < count; x++) { ISqlExpression item = _GroupBy[x]; if (!item.IsLiteral) { sql.Append("("); } item.GetSql(db_target, ref sql); if (!item.IsLiteral) { sql.Append(")"); } if (x + 1 < count) { sql.Append(", "); } else { sql.Append(" "); } } } } if (_Having != null && _Having.Count > 0) { _Having.GetSql(db_target, ref sql); } if (_OrderBy != null) { count = _OrderBy.Count; if (count > 0) { sql.Append(" ORDER BY "); for (int x = 0; x < count; x++) { IOrderByExpression item = _OrderBy[x]; item.GetSql(db_target, ref sql); if (x + 1 < count) { sql.Append(","); } } } } if ((db_target == DbTarget.SqlLite || db_target == DbTarget.MySql) && Top > 0) { sql.Append(String.Format(" LIMIT {0} ", _Top)); } }