/// <summary> /// Add values into a dictionary /// </summary> /// <param name="result"></param> /// <param name="list"></param> /// <param name="values"></param> /// <param name="valuesToStuff">If there are hardcoded values to stuff into dictionary, add a column name/value pair here</param> private static void AddValuesToDictionary(NycResultsWithMetadata result, ICollection <CheckedDictionary <string, object> > list, IEnumerable <IList <object> > values, Dictionary <String, String> valuesToStuff) { CheckedDictionary <string, object> dic; foreach (object[] val in values) { // Loop through the attributes we have, and get the values dic = new CheckedDictionary <string, object>(); for (int i = 0; i < result.Attrs.Count; i++) { // Get name/val pairs string name = result.Attrs[i].Name; dic.Add(name, val[i]); } // If there are values to stuff into this dictionary, do it here foreach (KeyValuePair <String, String> row in valuesToStuff) { dic.Add(row.Key, row.Value); } // Add the whole dictionary to a list of dictionaries list.Add(dic); } }
/// <summary> /// Uses the list of metadata to construct a lookup dictionary allowing /// you to get the index of any column by name. /// </summary> /// <returns>A dictionary of int indexes of the attributes keyed by attribute names.</returns> public IDictionary <string, int> GetIndexesByAttrID() { IDictionary <string, int> retVal = new CheckedDictionary <string, int>(); for (int x = 0; x < Attrs.Count; x++) { retVal[Attrs[x].UID] = x; } return(retVal); }
/// <summary> /// Gets all the attributes, without values, as a dictionary keyed by attribute display name. /// </summary> /// <param name="entityType">Only Properties is supported.</param> /// <param name="userRoles">The roles the current user has. /// Only attributes they can see will be returned.</param> /// <returns>All the attributes visible to someone with these roles.</returns> public static IDictionary <string, PdbAttribute> GetAttributesDictionary(PdbEntityType entityType, IEnumerable <SecurityRole> userRoles) { DaoCriteria crit = new DaoCriteria(); crit.Expressions.Add(new EqualExpression("EntityType", entityType)); IList <PdbAttribute> attrList = GetAttribRecords(crit, userRoles, true); IDictionary <string, PdbAttribute> retVal = new CheckedDictionary <string, PdbAttribute>(); foreach (PdbAttribute attr in attrList) { retVal[attr.UID] = attr; } return(retVal); }
/// <summary> /// Runs a query for the data for a particular indicator/resolution/time, /// and generates an SLD to color the geographies correctly based on the values. /// </summary> /// <param name="indicatorId"></param> /// <param name="resolutionType"></param> /// <param name="timeId"></param> /// <param name="scopeBorough"></param> /// <param name="scopeSubborough"></param> /// <returns></returns> public static string GenerateSld(object indicatorId, NycResolutionType resolutionType, object timeId, object scopeBorough, object scopeSubborough) { // First load the indicator metadata, both because we need the info, and because // if the query is for data that doesn't exist, we can skip a lot of work. NycIndicator indicator = GetIndicator(indicatorId); // Don't validate time, since there's no easy way to do it other than querying and seeing // if we get anything. // Now load the configs that tell us what color to render everything as. Config cfg = Config.GetConfig("NYC.SLD"); // Acceptable values are 0 to 100. So we need a 101 length array. string[] colors = new string[101]; string color = null; IDictionary <string, IList <object> > geogIdsByColor = new CheckedDictionary <string, IList <object> >(); for (int x = 0; x < colors.Length; x++) { // If the value isn't in the config, use the same as the last value. color = cfg.GetParameter(indicator.UseAlternateColors ? "AlternateBreakpoints" : "BreakPoints", x.ToString(), color); // Make sure we have a list created for the geogs that will be this color. if ((color != null) && (!geogIdsByColor.ContainsKey(color))) { geogIdsByColor[color] = new List <object>(); } colors[x] = color; } string mapLayerName = cfg.GetParameter("LayerNames", resolutionType.ToString(), null); string layerGeogIdField = cfg.GetParameter("LayerGeographyIdFields", resolutionType.ToString(), null); string layerDisplayNameField = cfg.GetParameter("LayerDisplayNameFields", resolutionType.ToString(), null); // This includes any labeling and outlines, default background color, etc. StringBuilder sldRules = new StringBuilder(cfg.GetConfigInnerXml("DefaultSLD")); // Replace any tokens with the appropriate values for this layer. sldRules.Replace("{LayerName}", mapLayerName); sldRules.Replace("{LayerGeographyIdField}", layerGeogIdField); sldRules.Replace("{LayerDisplayNameField}", layerDisplayNameField); // If no map layer is defined (I.E. "City"), or the request is invalid in some way, // use just the default SLD. if (mapLayerName != null) { // Now query for all the data. DaoCriteria crit = new DaoCriteria(); crit.Expressions.Add(new EqualExpression("IndicatorId", indicatorId)); crit.Expressions.Add(new EqualExpression("Resolution", resolutionType)); crit.Expressions.Add(new EqualExpression("TimeId", timeId)); DaoCriteria geogCrit = null; if (scopeBorough != null) { geogCrit = new DaoCriteria(); geogCrit.Expressions.Add(new EqualExpression("Borough", scopeBorough)); if (scopeSubborough != null) { geogCrit.Expressions.Add(new EqualExpression("SubBorough", scopeSubborough)); } } if (geogCrit != null) { // Scope and/or subscope was defined. geogCrit.Expressions.Add(new EqualExpression("Resolution", resolutionType)); IList <object> geogIdsInScope = new List <object>(); foreach (NycGeography geog in _geogDao.Get(geogCrit)) { geogIdsInScope.Add(geog.UID); } crit.Expressions.Add(new PropertyInListExpression("GeographyId", geogIdsInScope)); } IList <NycDatum> data = _dataDao.Get(crit); // If there's no data, don't make any SLD rules. if ((data != null) && (data.Count > 0)) { foreach (NycDatum datum in data) { int val; switch (indicator.Breakpoint) { case NycBreakpointType.HistoricalBreakpoint: val = datum.HistoricalBreakpoint; break; case NycBreakpointType.ContemporaryBreakpoint: val = datum.ContemporaryBreakpoint; break; default: throw new ArgumentOutOfRangeException("Indicator " + indicator + " has invalid breakpoint: " + indicator.Breakpoint); } if ((val < 0) || (val > 100)) { _log.Warn("'Normalized' value " + datum + " was outside 0-100 range."); } // Figure out, based on the value, which color group it belongs to. string thisColor = colors[val]; if (thisColor != null) { // Put it in that group. geogIdsByColor[thisColor].Add(datum.GeographyId); } } // Now append the rules for those color groups. foreach (KeyValuePair <string, IList <object> > kvp in geogIdsByColor) { // Only add rules for colors that actually have values. if (kvp.Value.Count > 0) { sldRules.Append("<Rule>"); // The part that says what geography IDs get this color. sldRules.Append("<ogc:Filter>"); sldRules.Append("<ogc:Or>"); foreach (object geogId in kvp.Value) { sldRules.Append("<ogc:PropertyIsEqualTo>"); sldRules.Append("<ogc:PropertyName>").Append(layerGeogIdField).Append( "</ogc:PropertyName>"); sldRules.Append("<ogc:Literal>").Append(geogId).Append("</ogc:Literal>"); sldRules.Append("</ogc:PropertyIsEqualTo>"); } sldRules.Append("</ogc:Or>"); sldRules.Append("</ogc:Filter>"); // The part that says what color to fill with. sldRules.Append("<PolygonSymbolizer>"); sldRules.Append("<Fill>"); sldRules.Append("<CssParameter name=\"fill\">").Append(kvp.Key).Append("</CssParameter>"); sldRules.Append("<CssParameter name=\"fill-opacity\">").Append( cfg.GetParameter("Polygons", "Opacity")).Append("</CssParameter>"); sldRules.Append("</Fill>"); sldRules.Append("</PolygonSymbolizer>"); sldRules.Append("</Rule>"); } } } } // Now wrap all the rendering rules in the SLD outer tags. StringBuilder sld = new StringBuilder(); sld.Append("<?xml version=\"1.0\" encoding=\"ISO-8859-1\"?>\n") .Append("<StyledLayerDescriptor version=\"1.0.0\"") .Append(" xsi:schemaLocation=\"http://www.opengis.net/sld StyledLayerDescriptor.xsd\"") .Append(" xmlns=\"http://www.opengis.net/sld\"") .Append(" xmlns:ogc=\"http://www.opengis.net/ogc\"") .Append(" xmlns:xlink=\"http://www.w3.org/1999/xlink\"") .Append(" xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\">") .Append("<NamedLayer>") .Append("<Name>").Append(mapLayerName).Append("</Name>") .Append("<UserStyle>") .Append("<FeatureTypeStyle>") .Append(sldRules.ToString()) .Append("</FeatureTypeStyle>") .Append("</UserStyle>") .Append("</NamedLayer>") .Append("</StyledLayerDescriptor>"); return(sld.ToString()); }
/// <summary> /// Queries for Nychanis data for the specified year range, resolution, and indicator. /// </summary> /// <param name="indicatorId">ID of the indicator being queried.</param> /// <param name="resolutionType">What resolution are we querying for.</param> /// <param name="timeUnitType">What type of time units should the data be in?</param> /// <param name="startYear">First year to include in the results.</param> /// <param name="endYear">Last year to include in the results.</param> /// <param name="scopeSubborough"></param> /// <param name="scopeBorough"></param> /// <param name="orderCol">The column to sort by.</param> /// <param name="orderDir">The direction to sort, ignored if order is less than zero. /// <param name="numPerPage">Number of records to be returned in the page sequence, if not less than zero</param> /// <param name="page">Which page in this page sequence is this request for. /// If null, assumed to be ascending.</param> /// <returns>The results!</returns> public static NycResultsWithMetadata Query(object indicatorId, NycResolutionType resolutionType, NycTimeframeType timeUnitType, int startYear, int endYear, object scopeBorough, object scopeSubborough, int orderCol, SortType?orderDir, int numPerPage, int page) { NycResultsWithMetadata retVal = new NycResultsWithMetadata(); // First load the indicator metadata, both because we need the display name, and because // if the query is for data that doesn't exist, we can skip a lot of work. NycIndicator indicator = GetIndicator(indicatorId); retVal.Indicator = indicator.Name; retVal.Resolution = resolutionType.ToString(); // Now verify the query against the metadata. retVal.MinYear = (startYear < indicator.MinYear) ? indicator.MinYear : startYear; retVal.MaxYear = (endYear > indicator.MaxYear) ? indicator.MaxYear : endYear; bool validRequest = (retVal.MaxYear >= retVal.MinYear); if (!validRequest) { // Return a completely blank result object. return(retVal); } // We only want to load time metadata for times this indicator actually has data for. DaoJoinCriteria joinCrit = new DaoJoinCriteria(); joinCrit.RightCriteria = new DaoCriteria(); joinCrit.RightCriteria.Expressions.Add(new EqualExpression("IndicatorId", indicatorId)); joinCrit.RightCriteria.Expressions.Add(new EqualExpression("Resolution", resolutionType)); joinCrit.Expressions.Add(new EqualJoinExpression("UID", "TimeId")); // Load the time metadata. joinCrit.LeftCriteria = new DaoCriteria(); // These are not-ed so they are <= and >=; joinCrit.LeftCriteria.Expressions.Add(new GreaterExpression("Year", retVal.MaxYear, false)); joinCrit.LeftCriteria.Expressions.Add(new LesserExpression("Year", retVal.MinYear, false)); joinCrit.LeftCriteria.Expressions.Add(new EqualExpression("Type", timeUnitType)); // Sort by value descending, so the most recent year comes first. joinCrit.Orders.Add(new JoinSortOrder("Value", SortType.Asc, true)); List <JoinResult <NycTimeframe, NycResolutionForIndicator> > timeframes = _timeDao.Get(joinCrit, _resolutionDao); // We also need to know what all possible times are though, so we can render the fancy slider with appropriate gaps. joinCrit.LeftCriteria.Orders.Add(new SortOrder("Value", SortType.Asc)); IList <NycTimeframe> allTimeframes = _timeDao.Get(joinCrit.LeftCriteria); // Use them to assemble the metadata, since one year is one column. retVal.Attrs = new List <AbstractNamedSortable>(); retVal.Attrs.Add(new NycGeogColumnMetadata("Area")); IDictionary <object, int> colsByTimeId = new CheckedDictionary <object, int>(); foreach (JoinResult <NycTimeframe, NycResolutionForIndicator> timeframe in timeframes) { colsByTimeId[timeframe.Left.UID] = retVal.Attrs.Count; retVal.Attrs.Add(new NycYearColumnMetadata(timeframe.Left, indicator.ValueType)); } NycTableResults results = QueryNychanisData(indicatorId, resolutionType, scopeBorough, scopeSubborough, colsByTimeId); retVal.TotalResults = results.Values.Count; // Don't do any further processing if there are no results if (results.Values.Count == 0) { return(retVal); } // If the user specified a sort order, use that, otherwise sort by the first column (area). List <KeyValuePair <int, SortType> > colSorts = new List <KeyValuePair <int, SortType> >(); colSorts.Add(new KeyValuePair <int, SortType>(orderCol < 0 ? 0 : orderCol, orderDir ?? SortType.Asc)); results.Values.Sort(new MultipleColumnListComparer(colSorts)); // Truncate the results by the requested paging information retVal.Values = ResultsWithMetadata <AbstractNamedSortable> .GetPagedSubset(results.Values, numPerPage, page); // Now get context rows. Always get City, unless we're querying for City. if (resolutionType != NycResolutionType.City) { List <IList <object> > contextRows = QueryNychanisData(indicatorId, NycResolutionType.City, null, null, colsByTimeId).Values; // Now, if they provided a borough scope and didn't ask for a borough resolution, // include borough. if ((resolutionType != NycResolutionType.Borough) && (scopeBorough != null)) { contextRows.AddRange(QueryNychanisData(indicatorId, NycResolutionType.Borough, scopeBorough, null, colsByTimeId).Values); // Then if not subborough but a subborough is provided, include that. if ((resolutionType != NycResolutionType.SubBorough) && (scopeSubborough != null)) { contextRows.AddRange(QueryNychanisData(indicatorId, NycResolutionType.SubBorough, scopeBorough, scopeSubborough, colsByTimeId).Values); } } retVal.ContextRows = contextRows; } // Now generate the map info for showing the results as a cloropleth layer. Config cfg = Config.GetConfig("PDP.Data"); string sldHandlerUrl = cfg.GetParameter("Mapping", "SldHandlerURL"); retVal.MapInfo = new NycMapInfo(); retVal.MapInfo.Server = cfg.GetParameter("Mapping", "MapServerURL"); retVal.MapInfo.Layers = new List <NycLayerInfo>(); string layerName = Config.GetConfig("NYC.SLD").GetParameter("LayerNames", resolutionType.ToString(), null); // City doesn't have a map layer, so don't create any OL layers for it. if (layerName != null) { int possibleTimeIndex = 0; int actualTimeIndex = 0; while (possibleTimeIndex < allTimeframes.Count) { // We need to pad out the list of layers with blanks for timeframes that lack data. NycLayerInfo layer = new NycLayerInfo(); layer.Name = allTimeframes[possibleTimeIndex].Name; // Years that actually have data go up faster than all years, so check if the current // possible year is lower than the next actual year. if (allTimeframes[possibleTimeIndex].Value < timeframes[actualTimeIndex].Left.Value) { // Need to pad with a blank, so just let the blank layer object be added. // Increment the possible time index to the next timeframe. possibleTimeIndex++; } else { NycTimeframe time = timeframes[actualTimeIndex].Left; // I think this check is no longer necessary, since we're probably // always excluding unavailable data. But if it ain't broke... if (results.DataAvailableByTime[actualTimeIndex]) { layer.Config = new Dictionary <string, object>(); layer.Config["layers"] = layerName; layer.Config["styles"] = ""; layer.Config["format"] = "image/png"; layer.Config["tiled"] = true; layer.Config["srs"] = "EPSG:4326"; layer.Config["transparent"] = true; StringBuilder sb = new StringBuilder(sldHandlerUrl); sb.Append("?indicator=").Append(indicatorId); sb.Append("&resolution=").Append(resolutionType.ToString()); sb.Append("&time=").Append(time.UID); if (scopeBorough != null) { sb.Append("&borough=").Append(scopeBorough); if (scopeSubborough != null) { sb.Append("&subborough=").Append(scopeSubborough); } } layer.Config["SLD"] = sb.ToString(); } // Increment both indexes. possibleTimeIndex++; actualTimeIndex++; } retVal.MapInfo.Layers.Add(layer); } // If we are creating layers, we must create a legend to describe them retVal.LegendInfo = GenerateLegendList(indicator, resolutionType); } return(retVal); }
/// <summary> /// Returns metadata about all the indicators available. /// </summary> /// <returns></returns> public static IList <NycIndicatorCategory> GetIndicatorMetadata() { IDictionary <string, IDictionary <string, IList <NycIndicator> > > groups = new CheckedDictionary <string, IDictionary <string, IList <NycIndicator> > >(); // Get all the indicators and split 'em up. foreach (NycIndicator ind in _indicatorDao.Get()) { IDictionary <string, IList <NycIndicator> > catGroup; if (groups.ContainsKey(ind.Category)) { catGroup = groups[ind.Category]; } else { catGroup = new CheckedDictionary <string, IList <NycIndicator> >(); groups[ind.Category] = catGroup; } IList <NycIndicator> subcatList; if (catGroup.ContainsKey(ind.SubCategory)) { subcatList = catGroup[ind.SubCategory]; } else { subcatList = new List <NycIndicator>(); catGroup[ind.SubCategory] = subcatList; } subcatList.Add(ind); } // Now they're all split up, create the returnable object types. // Remember it is impossible for any of the collections to be empty, since we created // them all based on records we had. List <NycIndicatorCategory> retVal = new List <NycIndicatorCategory>(); foreach (IDictionary <string, IList <NycIndicator> > catGroup in groups.Values) { List <NycIndicatorSubCategory> subCats = new List <NycIndicatorSubCategory>(); NycIndicator anIndicator = null; foreach (IList <NycIndicator> subcatList in catGroup.Values) { subCats.Add(new NycIndicatorSubCategory(subcatList)); // save one indicator for the category info. if (anIndicator == null) { anIndicator = subcatList[0]; } } retVal.Add(new NycIndicatorCategory(anIndicator, subCats)); } retVal.Sort(); // Now populate the available times based on resolutions for each indicator. IDictionary <object, IDictionary <int, IDictionary <int, IList <int> > > > resByIndic = GetResolutionsByIndicator(); foreach (NycIndicatorCategory cat in retVal) { foreach (NycIndicatorSubCategory subCat in cat.SubCats) { foreach (ThinNycIndicator indic in subCat.Indicators) { // Protect against bad data. if (resByIndic.ContainsKey(indic.UID)) { indic.AvailableYearsByResolution = resByIndic[indic.UID]; } } } } return(retVal); }
/// <summary> /// Reads the results from the data reader produced by the group by /// query, creates the GroupCountResults, and returns them in the /// parameters collection. /// </summary> /// <param name="parameters">Input and output parameters for the method.</param> /// <param name="reader">Data reader to read from.</param> protected virtual void ReadGroupByCount(Hashtable parameters, IDataReader reader) { ICollection<AbstractGroupExpression> groupExpressions = (ICollection<AbstractGroupExpression>) parameters["groupBys"]; ClassMapping mapping = (ClassMapping) parameters["mapping"]; List<GroupCountResult> results = new List<GroupCountResult>(); parameters["results"] = results; // Read each row and store it as a GroupCountResult. while (reader.Read()) { // We aliased the count as COUNT_COL_ALIAS. // Some databases return other numeric types, like "decimal", so you can't // just call reader.GetInt. string colName = COUNT_COL_ALIAS; int count = (int)CoerceType(typeof(int), reader.GetValue(reader.GetOrdinal(colName))); IDictionary<string, object> values = new CheckedDictionary<string, object>(); int groupByNum = 0; foreach (AbstractGroupExpression expr in groupExpressions) { values[expr.Name] = GetGroupByValue(mapping, reader, groupByNum, expr); groupByNum++; } results.Add(new GroupCountResult(count, values)); } }
/// <summary> /// Gets all columns (visible to the user anyway) and includes the allowed /// values for columns that have entries in the Attribute_Values table. /// </summary> /// <param name="entityType">Only Properties is supported.</param> /// <param name="userRoles">The roles the current user has. /// Only attributes they can see will be returned.</param> /// <returns>All the attribute metadata for the specified columns.</returns> public static IList <PdbCategory> GetAttributesForClient( PdbEntityType entityType, IEnumerable <SecurityRole> userRoles) { IDictionary <string, IDictionary <string, IList <PdbAttribute> > > attrsByCatAndSub = new CheckedDictionary <string, IDictionary <string, IList <PdbAttribute> > >(); IDictionary <string, IList <PdbAttribute> > attrsByCat = new CheckedDictionary <string, IList <PdbAttribute> >(); DaoCriteria crit = new DaoCriteria(); crit.Expressions.Add(new EqualExpression("EntityType", entityType)); IList <PdbAttribute> attrs = GetAttribRecords(crit, userRoles); // Get all the attributes and split 'em up. Put them either into the single // or double-nested dictionary depending on if they have subcategories. foreach (PdbAttribute attr in attrs) { IDictionary <string, IList <PdbAttribute> > catSubCats; IList <PdbAttribute> catAttrs; if (attrsByCatAndSub.ContainsKey(attr.Category)) { catSubCats = attrsByCatAndSub[attr.Category]; catAttrs = attrsByCat[attr.Category]; } else { catSubCats = new CheckedDictionary <string, IList <PdbAttribute> >(); catAttrs = new List <PdbAttribute>(); attrsByCatAndSub[attr.Category] = catSubCats; attrsByCat[attr.Category] = catAttrs; } // Now either it has a subcategory, in which case it's filed there, or // it doesn't, and it's filed under the category directly. if (StringHelper.IsNonBlank(attr.SubCategory)) { IList <PdbAttribute> subcatList; if (catSubCats.ContainsKey(attr.SubCategory)) { subcatList = catSubCats[attr.SubCategory]; } else { subcatList = new List <PdbAttribute>(); catSubCats[attr.SubCategory] = subcatList; } subcatList.Add(attr); } else { catAttrs.Add(attr); } } // Now they're all split up, create the returnable object types. // Remember it is impossible for any of the collections to be empty, since we created // them all based on records we had. List <PdbCategory> retVal = new List <PdbCategory>(); foreach (KeyValuePair <string, IDictionary <string, IList <PdbAttribute> > > kvp in attrsByCatAndSub) { IDictionary <string, IList <PdbAttribute> > subCatLists = kvp.Value; List <PdbSubCategory> subCats = new List <PdbSubCategory>(); PdbAttribute anAttr = null; foreach (IList <PdbAttribute> subCatList in subCatLists.Values) { subCats.Add(new PdbSubCategory(subCatList[0].SubCategory, subCatList[0].FilterAttrOrder, SimplifyAndGetValues(subCatList))); // save one indicator for the category info. if (anAttr == null) { anAttr = subCatList[0]; } } if (anAttr == null) { // subCatList and attrsByCat can't BOTH be empty or we wouldn't have this category. anAttr = attrsByCat[kvp.Key][0]; } retVal.Add(new PdbCategory(anAttr.Category, anAttr.FilterCatOrder, SimplifyAndGetValues(attrsByCat[kvp.Key]), subCats)); } retVal.Sort(); return(retVal); }
/// <summary> /// Updates a data object record using the "table" and a list of column/value pairs. /// </summary> /// <param name="transaction">Should be null, transactions are not supported.</param> /// <param name="mapping">The mapping of the table or other data container we're dealing with.</param> /// <param name="crit">All records matching this criteria will be updated per the dictionary of /// values.</param> /// <param name="propValues">A dictionary of column/value pairs for all non-ID columns to be updated.</param> /// <returns>The number of records affected.</returns> public override int Update(ITransaction transaction, ClassMapping mapping, DaoCriteria crit, IDictionary<string, object> propValues) { switch (_connDesc.Type) { case CsvConnectionType.Directory: case CsvConnectionType.FileName: // These are OK. break; default: throw new LoggingException("Connection does not support updating: " + _connDesc); } // No way to selectively update text from a text file, so instead we first copy all // the rows that don't match the criteria into a new file. string existingFile = GetFileName(mapping); string newFile = existingFile + ".new"; DaoCriteria inverseCrit = new DaoCriteria(); foreach (IExpression expr in crit.Expressions) { inverseCrit.Expressions.Add(expr.Invert()); } TextWriter newWriter = new StreamWriter(newFile, false); int rowsUpdated = 0; try { newWriter.WriteLine(MakeHeaderRow(mapping)); // Copy the rows that don't match... CsvDataReader reader = new CsvDataReader(this, mapping, inverseCrit); try { int numCols = reader.FieldCount; while (reader.Read()) { for (int x = 0; x < numCols; x++) { if (x > 0) { newWriter.Write(","); } newWriter.Write(QuoteValue(reader.GetValue(x))); } newWriter.WriteLine(); } } finally { reader.Close(); } // Copy (modified) the rows that do match... reader = new CsvDataReader(this, mapping, crit); try { IDictionary<int, object> replacements = new CheckedDictionary<int, object>(); foreach (KeyValuePair<string, object> kvp in propValues) { replacements[reader.GetColumnIndex(kvp.Key)] = kvp.Value; } int numCols = reader.FieldCount; while (reader.Read()) { rowsUpdated++; for (int x = 0; x < numCols; x++) { if (x > 0) { newWriter.Write(","); } // Use the updated value if one was provided. object val = replacements.ContainsKey(x) ? replacements[x] : reader.GetValue(x); newWriter.Write(QuoteValue(val)); } newWriter.WriteLine(); } } finally { reader.Close(); } } finally { newWriter.Close(); } // Now move the old file out of the way and replace it with the new one. File.Replace(newFile, existingFile, existingFile + ".old", true); return rowsUpdated; }
public void TestWriteCsvWithQuotes() { StringWriter writer = new StringWriter(); // This is testing to verify the CsvDescriptor constructor that takes a // Writer and a CVSQuoteLevel. CsvTestObj testObj1 = new CsvTestObj(); CheckedDictionary<string, object> testDict1 = new CheckedDictionary<string, object>(); testObj1.One = 50; testObj1.Two = -1.0; testObj1.Three = "Yo"; testObj1.Four = new DateTime(2001, 1, 1, 1, 1, 1); testObj1.Five = null; testDict1["One"] = testObj1.One; testDict1["Two"] = testObj1.Two; testDict1["Three"] = testObj1.Three; testDict1["Four"] = testObj1.Four; testDict1["Five"] = testObj1.Five; CsvTestObj testObj2 = new CsvTestObj(); CheckedDictionary<string, object> testDict2 = new CheckedDictionary<string, object>(); testObj2.One = int.MaxValue; testObj2.Two = double.MinValue; testObj2.Three = null; testObj2.Four = DateTime.MinValue; testObj2.Five = ""; testDict2["One"] = testObj2.One; testDict2["Two"] = testObj2.Two; testDict2["Three"] = testObj2.Three; testDict2["Four"] = testObj2.Four; testDict2["Five"] = testObj2.Five; ClassMapping mapping1 = MakeMapping("n/a", "WriteOne", true); CsvDescriptor desc1 = new CsvDescriptor(writer, CsvQuoteLevel.QuoteStrings); DictionaryDao dao1 = new DictionaryDao(desc1, mapping1); dao1.Insert(testDict1); dao1.Insert(testDict2); string csv = writer.ToString(); Assert.IsNotEmpty(csv, "The writer should not return an empty string. "); }
public void TestWriteCsv() { CsvTestObj testObj1 = new CsvTestObj(); CheckedDictionary<string, object> testDict1 = new CheckedDictionary<string, object>(); testObj1.One = 50; testObj1.Two = -1.0; testObj1.Three = "Yo"; testObj1.Four = new DateTime(2001, 1, 1, 1, 1, 1); testObj1.Five = null; testDict1["One"] = testObj1.One; testDict1["Two"] = testObj1.Two; testDict1["Three"] = testObj1.Three; testDict1["Four"] = testObj1.Four; testDict1["Five"] = testObj1.Five; CsvTestObj testObj2 = new CsvTestObj(); CheckedDictionary<string, object> testDict2 = new CheckedDictionary<string, object>(); testObj2.One = int.MaxValue; testObj2.Two = double.MinValue; testObj2.Three = null; testObj2.Four = DateTime.MinValue; testObj2.Five = ""; testDict2["One"] = testObj2.One; testDict2["Two"] = testObj2.Two; testDict2["Three"] = testObj2.Three; testDict2["Four"] = testObj2.Four; testDict2["Five"] = testObj2.Five; ClassMapping mapping1 = MakeMapping("n/a", "WriteOne", true); CsvDescriptor desc1 = new CsvDescriptor("..\\..\\Tests"); DictionaryDao dao1 = new DictionaryDao(desc1, mapping1); dao1.Truncate(); dao1.Insert(testDict1); dao1.Insert(testDict2); ClassMapping mapping2 = MakeMapping("Azavea.Open.DAO.CSV.Tests.CsvTestObj,Azavea.Open.DAO.CSV", "Doesn'tMatter", true); CsvDescriptor desc2 = new CsvDescriptor(CsvConnectionType.FileName, "..\\..\\Tests\\WriteTwo.csv"); FastDAO<CsvTestObj> dao2 = new FastDAO<CsvTestObj>(desc2, mapping2); dao2.Truncate(); dao2.Insert(testObj1); dao2.Insert(testObj2); ClassMapping mapping3 = MakeMapping("n/a", "AlsoDoesn'tMatter", true); using (StreamWriter sw = new StreamWriter("..\\..\\Tests\\WriteThree.csv", false)) { CsvDescriptor desc3 = new CsvDescriptor(sw); DictionaryDao dao3 = new DictionaryDao(desc3, mapping3); // Can't truncate this one. dao3.Insert(testDict1); dao3.Insert(testDict2); } ClassMapping mapping4 = MakeMapping("n/a", "WriteFour", false); CsvDescriptor desc4 = new CsvDescriptor("..\\..\\Tests"); DictionaryDao dao4 = new DictionaryDao(desc4, mapping4); dao4.Truncate(); dao4.Insert(testDict1); dao4.Insert(testDict2); ClassMapping mapping5 = MakeMapping("Azavea.Open.DAO.CSV.Tests.CsvTestObj,Azavea.Open.DAO.CSV", "Doesn'tMatter", false); CsvDescriptor desc5 = new CsvDescriptor(CsvConnectionType.FileName, "..\\..\\Tests\\WriteFive.csv"); FastDAO<CsvTestObj> dao5 = new FastDAO<CsvTestObj>(desc5, mapping5); dao5.Truncate(); dao5.Insert(testObj1); dao5.Insert(testObj2); ClassMapping mapping6 = MakeMapping("n/a", "AlsoDoesn'tMatter", false); using (StreamWriter sw = new StreamWriter("..\\..\\Tests\\WriteSix.csv", false)) { CsvDescriptor desc6 = new CsvDescriptor(sw); DictionaryDao dao6 = new DictionaryDao(desc6, mapping6); // Can't truncate this one. dao6.Insert(testDict1); dao6.Insert(testDict2); } // Now, assert they are all correct. 1, 2, and 3 should be the same (they have headers) // and 4, 5, and 6 should be the same (without headers). AssertFileContentsSame("..\\..\\Tests\\WriteOne.csv", "..\\..\\Tests\\WriteTwo.csv"); AssertFileContentsSame("..\\..\\Tests\\WriteOne.csv", "..\\..\\Tests\\WriteThree.csv"); AssertFileGreater("..\\..\\Tests\\WriteOne.csv", "..\\..\\Tests\\WriteFour.csv"); AssertFileContentsSame("..\\..\\Tests\\WriteFour.csv", "..\\..\\Tests\\WriteFive.csv"); AssertFileContentsSame("..\\..\\Tests\\WriteFour.csv", "..\\..\\Tests\\WriteFive.csv"); }
private IDictionary<string, MemoryObject> GetTable(ClassMapping mapping) { IDictionary<string, MemoryObject> retVal; lock (_datastore) { if (_datastore.ContainsKey(mapping.Table)) { retVal = _datastore[mapping.Table]; } else { retVal = new CheckedDictionary<string, MemoryObject>(); _datastore[mapping.Table] = retVal; } } return retVal; }
/// <summary> /// Executes a query and invokes a method with a DataReader of results. /// </summary> /// <param name="transaction">The transaction to do this as part of.</param> /// <param name="mapping">Class mapping for the table we're querying against. Optional, /// but not all columns may be properly typed if it is null.</param> /// <param name="query">The query to execute, should have come from CreateQuery.</param> /// <param name="invokeMe">The method to invoke with the IDataReader results.</param> /// <param name="parameters">A hashtable containing any values that need to be persisted through invoked method. /// The list of objects from the query will be placed here.</param> public override void ExecuteQuery(ITransaction transaction, ClassMapping mapping, IDaQuery query, DataReaderDelegate invokeMe, Hashtable parameters) { // Make a copy of the table and iterate over that, that way reading doesn't block writing (or // more reading). IDictionary<string, MemoryObject> tempTable; IDictionary<string, MemoryObject> table = GetTable(mapping); lock (table) { tempTable = new CheckedDictionary<string, MemoryObject>(table); } MemoryDataReader reader = new MemoryDataReader(this, mapping, ((UnqueryableQuery)query).Criteria, tempTable.Values.GetEnumerator()); try { invokeMe.Invoke(parameters, reader); } finally { reader.Close(); } }