/// <summary> /// Get where clause for viewon main table /// </summary> protected virtual SqlSelectBuilder.Where MainTableWhereClause(TableDef tabledef, ViewonKey key) { var w = new SqlSelectBuilder.Where(); foreach (var cri in key.Criteria) { var coldef = tabledef.FindCol(cri.Name); if (coldef != null) { var crihelper = new ViewonCriterion(coldef, cri.PackedValue); crihelper.ExportWhereClause(w, SqlFlavor); } } return(w); }
/// <summary> /// Get where clause for persiston main table, or null if this is a whole-table persiston /// </summary> protected virtual SqlSelectBuilder.Where MainTableWhereClause(TableDef tabledef, PersistonKey key) { if (key.WholeTable) { return(null); } var pkType = tabledef.FindCol(tabledef.PrimaryKeyColName).CSType; object pk = key.PrimaryKey; //always string here if (pkType != typeof(string)) { pk = Utils.ChangeType(pk, pkType); } var w = new SqlSelectBuilder.Where(); w.AddWhere($"{tabledef.PrimaryKeyColName}={w.NextParameterName()}", pk); return(w); }
/// <summary> /// Called from Load after loading the top table to load all other tables. /// The base implementation is careful to only load a table once regardless of the number of detail levels and the number of parent/grandparent /// rows. For example for a persiston whose table structure is (Sale, LineItem, LineItemNote), then there will only be one select statement /// issued for LineItemNote, using an IN clause for the primary keys of LineItem (which were already loaded). /// (The call syntax would be complex to invoke this for the top table so this is called only once for the children of the top /// table.) /// </summary> /// <param name="parentRows">parent Row objects indexed by primary key value</param> protected async Task LoadChildTablesRecursive(Dictionary <object, Row> parentRows, IDbConnection db, DataDictionary dbdef, TableDef parentdef) { if (!parentRows.Any()) { return; } if (parentdef.Children == null) { return; } //get parent keys in the form "1,2,3..." string parentKeyListFormatted = SqlSelectBuilder.FormatInClauseList(parentRows.Keys); foreach (var childTabledef in parentdef.Children) { //get rows - where clause is "parentkey in (...)" var whereClause = new SqlSelectBuilder.Where(); whereClause.AddWhere($"{childTabledef.ParentKeyColumnName} in({parentKeyListFormatted})"); var loadResult = await LoadTable(db, dbdef, childTabledef, whereClause, childTabledef.DefaulSortColName, 0, 0); var rowdict = loadResult.RowsByParentKey; //deal out the rows into the parent objects' Lists of this type var listField = parentdef.RowType.GetField(childTabledef.Name); foreach (object parentKey in rowdict.Keys) { var rowsForParent = rowdict[parentKey]; var parentRow = parentRows[parentKey]; var list = Utils.CreateOrGetFieldValue <IList>(parentRow, listField); foreach (var row in rowsForParent) { list.Add(row); } } //recur var rowsByPK = RestructureByPrimaryKey(childTabledef, rowdict); await LoadChildTablesRecursive(rowsByPK, db, dbdef, childTabledef); } }
/// <summary> /// Load rows given by where clause for a table and return them as dictionary indexed by the parent key value. /// If there is no parent key for the table, then the dictionary will have one entry with key="". /// The returned list members are objects of the type indicated by tabledef. /// The implementation loads one additional row to determine whether the load was complete, if pageSize is nonzero. /// </summary> /// <param name="pageSize">if zero, does not do paging</param> /// <param name="whereClause">can be null</param> protected virtual Task <SingleLoadResult> LoadTable(IDbConnection db, DataDictionary dbdef, TableDef tabledef, SqlSelectBuilder.Where whereClause, string sortColName, int pageSize, int pageNo) { var colInfos = BuildColumnsToLoadList(dbdef, tabledef); string parentKeyColName = tabledef.ParentKeyColumnName; int parentKeyColIndex = -1; var columnNames = colInfos.Select(c => c.SqlExpression).ToList(); if (parentKeyColName != null) { parentKeyColIndex = columnNames.Count; columnNames.Add(parentKeyColName); } int customColIndex = -1; if (tabledef.HasCustomColumns) { customColIndex = columnNames.Count; columnNames.Add(CUSTOMCOLNAME); } var sql = new SqlSelectBuilder(SqlFlavor, tabledef.SqlTableName, sortColName, columnNames) { PageSize = pageSize, PageNo = pageNo }; if (whereClause != null) { sql.WhereClause = whereClause; } var rowsByParentKey = new Dictionary <object, List <Row> >(); bool isComplete = true; using (var cmd = db.CreateCommand()) { cmd.CommandText = CustomizeSqlStatement(sql.ToString()); if (whereClause != null) { whereClause.ExportParameters(cmd); } using (var reader = cmd.ExecuteReader()) { int rowsLoaded = 0; while (reader.Read()) { //if this is the throw-away row that was one more than the page size, then note as incomplete but don't look at it if (++rowsLoaded > pageSize && pageSize > 0) { isComplete = false; break; } //read parent key value object pk = ""; if (parentKeyColIndex >= 0) { pk = reader.GetValue(parentKeyColIndex); } //read remaining values var row = Utils.Construct(tabledef.RowType) as Row; SetRowFromReader(colInfos, reader, row); //read custom values if (tabledef.HasCustomColumns) { string json = (string)Utils.ChangeType(reader.GetValue(customColIndex), typeof(string)); if (!string.IsNullOrEmpty(json)) { SetRowFromCustomValues(json, tabledef, row); } } //store in return dict if (!rowsByParentKey.ContainsKey(pk)) { rowsByParentKey[pk] = new List <Row>(); } rowsByParentKey[pk].Add(row); } } } var ret = new SingleLoadResult { RowsByParentKey = rowsByParentKey, IsComplete = isComplete }; return(Task.FromResult(ret)); }
public void ExportWhereClause(SqlSelectBuilder.Where w, SqlFlavorizer sqlFlavor) { //numeric ranges if (Utils.IsSupportedNumericType(ColDef.CSType)) { try { (string lo, string hi) = SplitOnTilde(PackedValue); if (lo != null) { decimal dlo = decimal.Parse(lo); w.AddWhere($"{ColDef.Name}>={w.NextParameterName()}", dlo); } if (hi != null) { decimal dhi = decimal.Parse(hi); w.AddWhere($"{ColDef.Name}<={w.NextParameterName()}", dhi); } } catch { throw new Exception($"Misformatted numeric parameter: {PackedValue}"); } } //dates and times else if (ColDef.CSType == typeof(DateTime)) { bool isDateOnly = ColDef.WireType == Constants.TYPE_DATE; (string lo, string hi) = SplitOnTilde(PackedValue); if (lo != null) { var dlo = Retrovert.ParseRetroDateTime(lo, isDateOnly); w.AddWhere($"{ColDef.Name}>={w.NextParameterName()}", dlo); } if (hi != null) { var dhi = Retrovert.ParseRetroDateTime(hi, isDateOnly); w.AddWhere($"{ColDef.Name}<={w.NextParameterName()}", dhi); } } else if (ColDef.CSType == typeof(bool)) { bool b; if (PackedValue == "0") { b = false; } else if (PackedValue == "1") { b = true; } else { throw new Exception($"Boolean parameter must be 0 or 1: {PackedValue}"); } w.AddWhere($"{ColDef.Name}={w.NextParameterName()}", b); } else if (ColDef.CSType == typeof(string)) { w.AddWhere($"{ColDef.Name} {sqlFlavor.LikeOperator()} {w.NextParameterName()}", sqlFlavor.LikeParamValue(PackedValue)); } else { throw new Exception($"Type {ColDef.CSType.Name} not supported as a viewon parameter"); } }