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