public static void FlushSQLResultByTable(string tableName) { if (string.IsNullOrEmpty(tableName)) { return; } string key1 = CsvHelper.CacheListKeyColumnHeader(tableName, true); string key2 = CsvHelper.CacheListKeyColumnHeader(tableName, false); var uncache = new HashSet <string>(StringComparer.OrdinalIgnoreCase); foreach (string sql in CacheList.Keys) { if (sql == key1 || sql == key2) { uncache.Add(sql); continue; } var tables = StringUtilsSQL.GetSQLTableNames(sql); foreach (string tab in tables) { if (tableName.Equals(tab, StringComparison.OrdinalIgnoreCase)) { uncache.Add(sql); break; } } } foreach (string sql in uncache) { CacheList.Remove(sql); } }
/// <summary> /// Initializes a new instance of the <see cref="ColumnFilterLogic" /> class. /// </summary> /// <param name="columnDataType">Type of the column data.</param> /// <param name="dataPropertyName">Name of the data property.</param> public ColumnFilterLogic(Type columnDataType, string dataPropertyName) { Contract.Requires(columnDataType != null); Contract.Requires(dataPropertyName != null); var dataPropertyName1 = dataPropertyName; // Un-escape the name again if (dataPropertyName.StartsWith("[", StringComparison.Ordinal) && dataPropertyName.EndsWith("]", StringComparison.Ordinal)) { dataPropertyName1 = dataPropertyName.Substring(1, dataPropertyName.Length - 2).Replace(@"\]", "]") .Replace(@"\\", @"\"); } m_DataPropertyNameEscape = $"[{StringUtilsSQL.SqlName(dataPropertyName1)}]"; m_ColumnDataType = columnDataType; // m_ValueClusterCollection.CollectionChanged += delegate(object sender, System.Collections.Specialized.NotifyCollectionChangedEventArgs e) { FilterChanged(); }; }
/// <summary> /// Builds the value clusters date. /// </summary> /// <param name="dataTable">The data view.</param> /// <param name="columnIndex">Index of the column.</param> /// <param name="maxNumber">The maximum number.</param> /// <param name="columnType">Type of the column.</param> /// <returns></returns> private BuildValueClustersResult BuildValueClustersNumeric(DataTable dataTable, int columnIndex, int maxNumber, Type columnType) { // Get the distinct values and their counts var clusterFractions = new HashSet <double>(); var clusterOne = new HashSet <long>(); var clusterTen = new HashSet <long>(); var clusterHundered = new HashSet <long>(); var clusterThousand = new HashSet <long>(); var columnName = dataTable.Columns[columnIndex].ColumnName; foreach (DataRow dataRow in dataTable.Rows) { if (dataRow[columnIndex] == DBNull.Value) { clusterFractions.Add(int.MinValue); clusterOne.Add(int.MinValue); clusterTen.Add(int.MinValue); clusterHundered.Add(int.MinValue); } else { if (columnType == typeof(decimal) || columnType == typeof(float) || columnType == typeof(double)) { var rounded = Math.Floor(Convert.ToDouble(dataRow[columnIndex], CultureInfo.CurrentCulture) * 10d) / 10d; clusterFractions.Add(rounded); } var key = Convert.ToInt64(dataRow[columnIndex], CultureInfo.CurrentCulture); clusterOne.Add(key); clusterTen.Add(key / 10); clusterHundered.Add(key / 100); clusterThousand.Add(key / 1000); } // if we have more than the maximum entries stop, no value filter will be used if (clusterThousand.Count <= maxNumber) { continue; } m_ValueClusters.Clear(); return(BuildValueClustersResult.TooManyValues); } if (clusterOne.Count == 0 && clusterFractions.Count == 0) { m_ValueClusters.Clear(); return(BuildValueClustersResult.NoValues); } var colNameEsc = $"[{StringUtilsSQL.SqlName(columnName)}]"; if (clusterFractions.Count < maxNumber && clusterFractions.Count > 0) { m_Type = ValueClustersGroupType.NumericFraction; foreach (var dic in clusterFractions) { if (Math.Abs(dic - int.MinValue) > .1) { m_ValueClusters.Add(new ValueCluster { Display = dic.ToString(CultureInfo.CurrentCulture), // Decimal SQLCondition = string.Format(CultureInfo.InvariantCulture, "({0} >= {1} AND {0} < {2})", colNameEsc, dic, dic + .1), Sort = (dic * 10d).ToString("0000000000000000000", CultureInfo.InvariantCulture) }); } else { m_ValueClusters.Add(new ValueCluster { Display = ColumnFilterLogic.cOPisNull, SQLCondition = string.Format(CultureInfo.InvariantCulture, "({0} IS NULL)", colNameEsc), Sort = string.Empty }); } } } else if (clusterOne.Count < maxNumber) { m_Type = ValueClustersGroupType.NumericOnes; foreach (var dic in clusterOne) { if (dic != int.MinValue) { m_ValueClusters.Add(new ValueCluster { Display = dic.ToString("D", CultureInfo.CurrentCulture), // Decimal SQLCondition = string.Format(CultureInfo.InvariantCulture, "({0} >= {1} AND {0} < {2})", colNameEsc, dic, dic + 1), Sort = dic.ToString("0000000000000000000", CultureInfo.InvariantCulture) }); } else { m_ValueClusters.Add(new ValueCluster { Display = ColumnFilterLogic.cOPisNull, SQLCondition = string.Format(CultureInfo.InvariantCulture, "({0} IS NULL)", colNameEsc), Sort = string.Empty }); } } } else if (clusterTen.Count < maxNumber) { m_Type = ValueClustersGroupType.NumericTens; foreach (var dic in clusterTen) { if (dic != int.MinValue) { m_ValueClusters.Add(new ValueCluster { Display = $"{dic * 10} - {dic * 10 + 9}", // Decimal SQLCondition = string.Format(CultureInfo.InvariantCulture, "({0} >= {1} AND {0} < {2})", colNameEsc, dic * 10, (dic + 1) * 10), Sort = dic.ToString("0000000000000000000", CultureInfo.InvariantCulture) }); } else { m_ValueClusters.Add(new ValueCluster { Display = ColumnFilterLogic.cOPisNull, SQLCondition = string.Format(CultureInfo.InvariantCulture, "({0} IS NULL)", colNameEsc), Sort = string.Empty }); } } } else if (clusterHundered.Count < maxNumber) { m_Type = ValueClustersGroupType.NumericHundreds; foreach (var dic in clusterHundered) { if (dic != int.MinValue) { m_ValueClusters.Add(new ValueCluster { Display = $"{dic * 100} - {dic * 100 + 99}", // Decimal SQLCondition = string.Format(CultureInfo.InvariantCulture, "({0} >= {1} AND {0} < {2})", colNameEsc, dic * 100, (dic + 1) * 100), Sort = dic.ToString("0000000000000000000", CultureInfo.InvariantCulture) }); } else { m_ValueClusters.Add(new ValueCluster { Display = ColumnFilterLogic.cOPisNull, SQLCondition = string.Format(CultureInfo.InvariantCulture, "({0} IS NULL)", colNameEsc), Sort = string.Empty }); } } } else { m_Type = ValueClustersGroupType.NumericThousands; foreach (var dic in clusterThousand) { if (dic != int.MinValue) { m_ValueClusters.Add(new ValueCluster { Display = $"{dic * 1000} - {dic * 1000 + 999}", // Decimal SQLCondition = string.Format(CultureInfo.InvariantCulture, "({0} >= {1} AND {0} < {2})", colNameEsc, dic * 1000, (dic + 1) * 1000), Sort = dic.ToString("0000000000000000000", CultureInfo.InvariantCulture) }); } else { m_ValueClusters.Add(new ValueCluster { Display = ColumnFilterLogic.cOPisNull, SQLCondition = string.Format(CultureInfo.InvariantCulture, "({0} IS NULL)", colNameEsc), Sort = string.Empty }); } } } return(BuildValueClustersResult.ListFilled); }
/// <summary> /// Builds the value clusters date. /// </summary> /// <param name="dataTable">The data view.</param> /// <param name="columnIndex">Index of the column.</param> /// <param name="maxNumber">The maximum number.</param> /// <returns></returns> private BuildValueClustersResult BuildValueClustersDate(DataTable dataTable, int columnIndex, int maxNumber) { // Get the distinct values and their counts var clusterYear = new HashSet <int>(); var clusterMonth = new HashSet <DateTime>(); var clusterDay = new HashSet <DateTime>(); var clusterHour = new HashSet <long>(); var columnName = dataTable.Columns[columnIndex].ColumnName; foreach (DataRow dataRow in dataTable.Rows) { int keyYear; DateTime keyMonth; DateTime keyDate; long keyHour; if (dataRow[columnIndex] == DBNull.Value) { keyYear = 0; keyHour = 0; keyMonth = DateTime.MinValue; keyDate = DateTime.MinValue; } else { var value = (DateTime)dataRow[columnIndex]; keyHour = GetHourKey(value); keyDate = value.Date; keyYear = GetYearKey(value); keyMonth = GetMonthKey(value); } clusterHour.Add(keyHour); clusterDay.Add(keyDate); clusterMonth.Add(keyMonth); clusterYear.Add(keyYear); // if we have more than the maximum entries stop, no value filter will be used if (clusterYear.Count <= maxNumber) { continue; } m_ValueClusters.Clear(); return(BuildValueClustersResult.TooManyValues); } if (clusterYear.Count == 0) { m_ValueClusters.Clear(); return(BuildValueClustersResult.NoValues); } if (clusterDay.Count == 1) { m_Type = ValueClustersGroupType.DateHours; foreach (var dic in clusterHour) { if (dic != 0) { var from = StringConversion.GetTimeFromTicks(dic * c_TicksPerGroup); var to = StringConversion.GetTimeFromTicks((dic + 1) * c_TicksPerGroup); m_ValueClusters.Add(new ValueCluster { Display = $"{from:t} - {to:t}", SQLCondition = string.Format(CultureInfo.InvariantCulture, @"({0} >= #{1:MM\/dd\/yyyy HH:mm}# AND {0} < #{2:MM\/dd\/yyyy HH:mm}#)", StringUtilsSQL.SqlNameSafe(columnName), from, to), Sort = GetHourSort(dic) }); } else { m_ValueClusters.Add(new ValueCluster { Display = ColumnFilterLogic.cOPisNull, SQLCondition = string.Format(CultureInfo.InvariantCulture, "({0} IS NULL)", StringUtilsSQL.SqlNameSafe(columnName)), Sort = string.Empty }); } } } else if (clusterDay.Count < maxNumber) { m_Type = ValueClustersGroupType.DateDay; foreach (var dic in clusterDay) { if (dic != DateTime.MinValue) { m_ValueClusters.Add(new ValueCluster { Display = dic.ToString("d", CultureInfo.CurrentCulture), SQLCondition = string.Format(CultureInfo.InvariantCulture, @"({0} >= #{1:MM\/dd\/yyyy}# AND {0} < #{2:MM\/dd\/yyyy}#)", StringUtilsSQL.SqlNameSafe(columnName), dic, dic.AddDays(1)), Sort = GetDaySort(dic) }); } else { m_ValueClusters.Add(new ValueCluster { Display = ColumnFilterLogic.cOPisNull, SQLCondition = string.Format(CultureInfo.InvariantCulture, c_IsNull, StringUtilsSQL.SqlNameSafe(columnName)), Sort = string.Empty }); } } } else if (clusterMonth.Count < maxNumber) { m_Type = ValueClustersGroupType.DateMonth; foreach (var dic in clusterMonth) { if (dic != DateTime.MinValue) { m_ValueClusters.Add(new ValueCluster { Display = dic.ToString("Y", CultureInfo.CurrentCulture), // Year month pattern SQLCondition = string.Format(CultureInfo.InvariantCulture, @"({0} >= #{1:MM\/dd\/yyyy}# AND {0} < #{2:MM\/dd\/yyyy}#)", StringUtilsSQL.SqlNameSafe(columnName), dic, dic.AddMonths(1)), Sort = GetMonthSort(dic) }); } else { m_ValueClusters.Add(new ValueCluster { Display = ColumnFilterLogic.cOPisNull, SQLCondition = string.Format(CultureInfo.InvariantCulture, c_IsNull, StringUtilsSQL.SqlNameSafe(columnName)), Sort = string.Empty }); } } } else { m_Type = ValueClustersGroupType.DateYear; foreach (var dic in clusterYear) { if (dic != 0) { m_ValueClusters.Add(new ValueCluster { Display = dic.ToString("D", CultureInfo.CurrentCulture), // Decimal SQLCondition = string.Format(CultureInfo.InvariantCulture, "({0} >= #01/01/{1:d4}# AND {0} < #01/01/{2:d4}#)", StringUtilsSQL.SqlNameSafe(columnName), dic, dic + 1), Sort = GetYearSort(dic) }); } else { m_ValueClusters.Add(new ValueCluster { Display = ColumnFilterLogic.cOPisNull, SQLCondition = string.Format(CultureInfo.InvariantCulture, c_IsNull, StringUtilsSQL.SqlNameSafe(columnName)), Sort = string.Empty }); } } } return(BuildValueClustersResult.ListFilled); }