protected void DoFetch(SchemaRelation[] relations, ICriteriaValue[] criteria, params string[] columnNames)
{
fetchCriteria = criteria;
foreach (var criterion in criteria)
{
if (criterion.Column == null)
{
IDataColumn col = null;
var schemaCol = criterion.SchemaColumn;
var colName = String.Concat(schemaCol.SchemaTable.TableName, ".", criterion.SchemaColumn.ColumnName);
if (!columnsDictionary.TryGetValue(colName, out col))
{
col = InitColumn(colName);
}
criterion.Column = col;
}
}
rowsList.Clear();
using (var cm = DataContext.GetConnectionManager())
{
var cmd = cm.Connection.CreateCommand();
cmd.CommandType = System.Data.CommandType.Text;
if (relations == null)
{
cmd.CommandText = DataContext.GetSqlFactory().GetSelectSql(TableName, readOnlyColumnsList, criteria);
}
else
{
cmd.CommandText = DataContext.GetSqlFactory().GetSelectSql(relations, readOnlyColumnsList, criteria);
}
using (var reader = cmd.ExecuteReader())
{
//var columnOrdinalDictionary = GetQueryColumnOrdinals(reader);
while (reader.Read())
{
rowsList.Add(GetNewDataRow(reader));
}
}
}
}
protected string GetSqlJoinExpression(SchemaRelation relation)
{
StringBuilder builder = new StringBuilder();
for (int i = 0; i < relation.ParentColumns.Count; i++)
{
builder.AppendFormat("{0} = {1} AND ",
GetSqlFieldName(relation.ParentTable.TableName, relation.ParentColumns[i].SchemaColumnName),
GetSqlFieldName(relation.ChildTable.TableName, relation.ChildColumns[i].SchemaColumnName));
}
//
// Remove trailing " AND "
//
builder.Remove(builder.Length - 5, 5);
return builder.ToString();
}
public string GetSelectSql(SchemaRelation[] relations, IEnumerable<IDataColumn> columms, params ICriteriaValue[] criteria)
{
var builder = new StringBuilder();
builder.Append("SELECT ");
if (columms != null && columms.Count() > 0)
{
foreach (var column in columms)
{
builder.Append(GetSqlFieldName(column.SchemaColumn.SchemaTable.TableName, column.SchemaColumn.SchemaColumnName));
builder.Append(", ");
}
//
// Remove trailing comma and space
//
builder.Remove(builder.Length - 2, 2);
}
else
{
builder.Append(" *");
}
//builder.AppendFormat(" FROM {0}{1}{3}.{0}{2}{3}", IdentifierDelimiterOpen, DefaultOwner, tableName, IdentifierDelimiterClose);
//
// FROM "PUB"."POHeader" INNER JOIN "PUB"."PODetail" ON "PUB"."POHeader"."Company" = "PUB"."PODetail"."Company" AND "PUB"."POHeader"."PONum" = "PUB"."PODetail"."PONum"
// INNER JOIN "PUB"."PORel" ON PUB"."PODetail"."Company" = "PUB"."PORel"."Company" AND "PUB"."PODetail"."PONum" = "PUB"."PORel"."PONum" AND "PUB"."PODetail"."POLine" = "PUB"."PORel"."POLine"
//
builder.AppendFormat(" FROM ");
bool isFirstRelation = true;
foreach (var relation in relations)
{
SchemaTable parent = null;
SchemaTable child = null;
parent = relation.IsReversed ? relation.ChildTable : relation.ParentTable;
child = relation.IsReversed ? relation.ParentTable : relation.ChildTable;
if (isFirstRelation)
{
builder.AppendFormat("{0} INNER JOIN {1} ON {2}", GetSqlTableName(parent.TableName), GetSqlTableName(child.TableName), GetSqlJoinExpression(relation));
isFirstRelation = false;
}
else
{
builder.AppendFormat(" INNER JOIN {0} ON {1}", GetSqlTableName(child.TableName), GetSqlJoinExpression(relation));
}
}
if (criteria != null && criteria.Length > 0)
{
builder.Append(" WHERE ");
foreach (var criterion in criteria)
{
var column = criterion.Column;
builder.AppendFormat("{4}.{0}{1}{2} = {3} AND ", IdentifierDelimiterOpen, column.SchemaColumn.SchemaColumnName, IdentifierDelimiterClose, column.FormatLiteral(this, criterion.Value), GetSqlTableName(column.SchemaColumn.SchemaTable.TableName));
}
//
// Remove trailing " AND "
//
builder.Remove(builder.Length - 5, 5);
}
return builder.ToString();
}
