protected void InsertColumns(params object[] columns)
{
foreach (var column in columns)
{
TableColumns.Add(column);
}
}
public override DataTable GetTableInfo(string tableName)
{
try
{
String[] columnRestrictions = new String[4];
if (tableName == null)
{
columnRestrictions[2] = SelectedTable;
}
else
{
columnRestrictions[2] = tableName;
}
DataTable departmentIDSchemaTable = connection.GetSchema("Columns", columnRestrictions);
TableColumns.Clear();
DataView dv = departmentIDSchemaTable.DefaultView;
departmentIDSchemaTable.DefaultView.Sort = "ORDINAL_POSITION Asc";
var SortedView = dv.ToTable();
foreach (DataRow row in SortedView.Rows)
{
TableColumns.Add(new Column(row, DbType));
}
return(departmentIDSchemaTable);
}
catch (Exception ex)
{
return(null);
}
}
private void OnDataCollected(FdrManagerEventArgs e)
{
Ordinats.Clear();
cb_Abscissa.Items.Clear();
TableColumns.Clear();
foreach (var title in m_DataProcessingHelper.GetCollectedMeasuresNames())
{
CheckedListItem <string> item = new CheckedListItem <string>()
{
Item = title
};
item.PropertyChanged += onOrdinateChecked;
Ordinats.Add(item);
cb_Abscissa.Items.Add(title);
item = new CheckedListItem <string>()
{
Item = title
};
item.PropertyChanged += onTabColumnChecked;
TableColumns.Add(item);
}
StatusLabelMain.Content = Properties.Resources.state_success;
Started = false;
}
private void MakeColumnsAsLocalSubSelect()
{
TableColumns.Clear();
List <ColumnClause> lst = GetAllColumns().ToList();
HashSet <string> str = new HashSet <string>();
foreach (var c in lst)
{
string alias = c.ExtractAlias();
if (alias == null)
{
alias = "";
}
var tp = c.ColumnExpression.GetResultType();
if (tp == SimpleTypes.Geometry)
{
CoordinateSystem = c.ColumnExpression.GetCoordinateSystem();
}
TableColumns.Add(new ColumnSubSelect()
{
ColumnClause = c, Name = alias, SimpleType = tp
});
}
}
public override void Prepare()
{
base.Prepare();
for (int i = 0; i < ReturningColumns.Count; i++)
{
ReturningColumns[i].Prepare();
}
TableColumns.Clear();
List <ColumnClause> lst = ReturningColumns.ToList();
HashSet <string> str = new HashSet <string>();
foreach (var c in lst)
{
string alias = c.ExtractAlias();
if (alias == null)
{
alias = "";
}
var tp = c.ColumnExpression.GetResultType();
if (tp == SimpleTypes.Geometry)
{
CoordinateSystem = c.ColumnExpression.GetCoordinateSystem();
}
TableColumns.Add(new ColumnSubSelect()
{
ColumnClause = c, Name = alias, SimpleType = tp
});
}
}
public void AddColumn(
IEnumerable <T> columnEntries,
T defaultIfMissing
)
{
List <T> column = columnEntries.ToList();
AdjustNumberOfRows(column, defaultIfMissing);
TableColumns.Add(column);
}
public Table(
T defaultElement,
int numberOfRows,
int numberOfColumns
) : this()
{
for (int i = 0; i < numberOfColumns; i++)
{
TableColumns.Add(Enumerable.Repeat(defaultElement, numberOfRows).ToList());
}
}
public override DataTable GetTableInfo(string tableName = null)
{
try
{
String[] columnRestrictions = new String[4];
if (tableName == null)
{
columnRestrictions[2] = SelectedTable;
}
else
{
columnRestrictions[2] = tableName;
}
DataTable departmentIDSchemaTable = connection.GetSchema("Columns", columnRestrictions);
TableColumns.Clear();
DataView dv = departmentIDSchemaTable.DefaultView;
departmentIDSchemaTable.DefaultView.Sort = "ORDINAL_POSITION Asc";
var SortedView = dv.ToTable();
foreach (DataRow row in SortedView.Rows)
{
TableColumns.Add(new Column(row, DbType));
}
/////////
string _table = (tableName == null) ? SelectedTable : tableName;
SqlDataAdapter adapter = new
SqlDataAdapter("SELECT TOP 1 * FROM " + _table,
(connection as SqlConnection));
adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;
DataTable table = new DataTable();
adapter.Fill(table);
using (DataTableReader reader = new DataTableReader(table))
{
int ordinal = 0;
DataTable schemaTable = reader.GetSchemaTable();
for (int i = 0; i < schemaTable.Rows.Count; i++)
{
if ((bool)schemaTable.Rows[i].ItemArray[12])
{
TableColumns[i].ISKey = true;
}
}
}
///////////////
return(departmentIDSchemaTable);
}
catch (Exception ex)
{
return(null);
}
}
/// <summary>
/// Creates a <see cref="RedisHashTableRow"/> from given <paramref name="hashEntries"/> for all <paramref name="columns"/> specified
/// </summary>
/// <param name="tableName">The name of the table</param>
/// <param name="hashEntries">All Entries that should be added as a column (only if <see cref="HashEntry.Name"/> exists in <paramref name="columns"/>)</param>
/// <param name="columns">List of all needed columns. Non existing columns will be filled up with <c>null</c></param>
public RedisHashTableRow(string tableName, HashEntry[] hashEntries, List <string> columns)
{
_tableName = tableName;
foreach (HashEntry entry in hashEntries)
{
if (columns.Contains(entry.Name))
{
TableColumns.Add(new RedisTableColumn(entry.Name, LlFieldType.Text, entry.Value));
}
}
//add missing columns
foreach (string col in columns)
{
if (!TableColumns.Any(s => s.ColumnName == col))
{
TableColumns.Add(new RedisTableColumn(col, LlFieldType.Text, null));
}
}
}
private void btn_Graph_Open_Click(object sender, RoutedEventArgs e)
{
var opened = Load();
if (opened == null)
{
return;
}
cb_Abscissa.Items.Clear();
Ordinats.Clear();
TableColumns.Clear();
m_DataProcessingHelper.loadState(opened.Data);
foreach (var title in m_DataProcessingHelper.GetCollectedMeasuresNames())
{
CheckedListItem <string> item = new CheckedListItem <string>()
{
Item = title
};
Ordinats.Add(item);
cb_Abscissa.Items.Add(title);
item = new CheckedListItem <string>()
{
Item = title
};
item.PropertyChanged += onTabColumnChecked;
TableColumns.Add(item);
}
cb_Abscissa.SelectionChanged -= cb_Abscissa_SelectionChanged;
cb_Abscissa.SelectedItem = m_DataProcessingHelper.Graphs.First().XAxis;
m_DataProcessingHelper.Graphs.Select(g => g.YAxis).ToList().ForEach(y => Ordinats.Where(o => o.Item.Equals(y)).First().IsChecked = true);
opened.CheckedTables.ForEach(c => TableColumns.Where(t => t.Item.Equals(c)).First().IsChecked = true);
cb_Abscissa.SelectionChanged += cb_Abscissa_SelectionChanged;
Ordinats.ToList().ForEach(o => o.PropertyChanged += onOrdinateChecked);
}
public RedisValueTableRow(RedisKey key, RedisValue item)
{
Key = key;
TableColumns.Add(new RedisTableColumn("Value", LlFieldType.Text, item.ToString()));
}
/// <summary>
/// Creates a <see cref="RedisSortedSetTableRow"/> from the given <paramref name="entry"/>
/// </summary>
/// <param name="tableName">The name of the table</param>
/// <param name="entry">Entry that should be used to build the <see cref="RedisSortedSetTableRow"/></param>
public RedisSortedSetTableRow(string tableName, SortedSetEntry entry)
{
_tableName = tableName;
TableColumns.Add(new RedisTableColumn("Value", LlFieldType.Text, entry.Element));
TableColumns.Add(new RedisTableColumn("Score", LlFieldType.Numeric, entry.Score, typeof(double)));
}
/// <summary>
/// Retrieves web services information. The RestfulUri must be passed through the properties. This should be in the format http://sitename/{value1}/{value2} where the names between {} are the names for input parameters. The properties can also contain default values for the parameters.
/// </summary>
/// <param name="importTable"></param>
/// <param name="cancellationToken"></param>
/// <returns></returns>
public override Task <Table> GetSourceTableInfo(Table importTable, CancellationToken cancellationToken)
{
try
{
using (var package = NewConnection())
{
var worksheet = GetWorkSheet(package, importTable.Name);
var columns = new TableColumns();
for (var col = ExcelHeaderCol; col <= worksheet.Dimension.Columns && col <= ExcelHeaderColMax; col++)
{
cancellationToken.ThrowIfCancellationRequested();
var columName = worksheet.Cells[ExcelHeaderRow, col].Value.ToString();
if (string.IsNullOrEmpty(columName))
{
columName = "Column-" + col.ToString();
}
var column = new TableColumn(columName, ETypeCode.String)
{
AllowDbNull = true
};
//search the data to determine datatype.
var dataType = ETypeCode.Unknown;
for (var row = ExcelDataRow; row <= worksheet.Dimension.Rows; row++)
{
var value = worksheet.GetValue(row, col);
if (dataType == ETypeCode.Unknown || dataType == ETypeCode.DateTime)
{
if (worksheet.Cells[row, col].Style.Numberformat.Format.Contains("yy"))
{
dataType = ETypeCode.DateTime;
continue;
}
if (dataType == ETypeCode.DateTime)
{
dataType = ETypeCode.Int64;
}
}
if (dataType == ETypeCode.Unknown || dataType == ETypeCode.Int64)
{
if (value is bool)
{
dataType = ETypeCode.Boolean;
continue;
}
else if (value.IsNumeric())
{
if (Math.Abs((Double)value % 1) <= (Double.Epsilon * 100))
{
dataType = ETypeCode.Int64;
continue;
}
else
{
dataType = ETypeCode.Double;
continue;
}
}
dataType = ETypeCode.String;
break;
}
if (dataType == ETypeCode.Unknown || dataType == ETypeCode.Decimal)
{
if (value.IsNumeric())
{
dataType = ETypeCode.Decimal;
continue;
}
dataType = ETypeCode.String;
break;
}
dataType = ETypeCode.String;
break;
}
column.DataType = dataType == ETypeCode.Unknown ? ETypeCode.String : dataType;
columns.Add(column);
}
var newTable = new Table(importTable.Name, -1, columns);
return(Task.FromResult(newTable));
}
}
catch (Exception ex)
{
throw new ConnectionException($"Failed excel sheet data. {ex.Message}", ex);
}
}
internal void FromTable(Table t)
{
SetAllNull();
if (t.AutoFilter != null)
{
AutoFilter.FromAutoFilter(t.AutoFilter);
HasAutoFilter = true;
}
if (t.SortState != null)
{
SortState.FromSortState(t.SortState);
HasSortState = true;
}
using (var oxr = OpenXmlReader.Create(t.TableColumns))
{
SLTableColumn tc;
while (oxr.Read())
{
if (oxr.ElementType == typeof(TableColumn))
{
tc = new SLTableColumn();
tc.FromTableColumn((TableColumn)oxr.LoadCurrentElement());
TableColumns.Add(tc);
}
}
}
if (t.TableStyleInfo != null)
{
TableStyleInfo.FromTableStyleInfo(t.TableStyleInfo);
HasTableStyleInfo = true;
}
Id = t.Id.Value;
if (t.Name != null)
{
Name = t.Name.Value;
}
sDisplayName = t.DisplayName.Value;
if (t.Comment != null)
{
Comment = t.Comment.Value;
}
var iStartRowIndex = 1;
var iStartColumnIndex = 1;
var iEndRowIndex = 1;
var iEndColumnIndex = 1;
var sRef = t.Reference.Value;
if (sRef.IndexOf(":") > 0)
{
if (SLTool.FormatCellReferenceRangeToRowColumnIndex(sRef, out iStartRowIndex, out iStartColumnIndex,
out iEndRowIndex, out iEndColumnIndex))
{
StartRowIndex = iStartRowIndex;
StartColumnIndex = iStartColumnIndex;
EndRowIndex = iEndRowIndex;
EndColumnIndex = iEndColumnIndex;
}
}
else
{
if (SLTool.FormatCellReferenceToRowColumnIndex(sRef, out iStartRowIndex, out iStartColumnIndex))
{
StartRowIndex = iStartRowIndex;
StartColumnIndex = iStartColumnIndex;
EndRowIndex = iStartRowIndex;
EndColumnIndex = iStartColumnIndex;
}
}
if (t.TableType != null)
{
TableType = t.TableType.Value;
}
if ((t.HeaderRowCount != null) && (t.HeaderRowCount.Value != 1))
{
HeaderRowCount = t.HeaderRowCount.Value;
}
if ((t.InsertRow != null) && t.InsertRow.Value)
{
InsertRow = t.InsertRow.Value;
}
if ((t.InsertRowShift != null) && t.InsertRowShift.Value)
{
InsertRowShift = t.InsertRowShift.Value;
}
if ((t.TotalsRowCount != null) && (t.TotalsRowCount.Value != 0))
{
TotalsRowCount = t.TotalsRowCount.Value;
}
if ((t.TotalsRowShown != null) && !t.TotalsRowShown.Value)
{
TotalsRowShown = t.TotalsRowShown.Value;
}
if ((t.Published != null) && t.Published.Value)
{
Published = t.Published.Value;
}
if (t.HeaderRowFormatId != null)
{
HeaderRowFormatId = t.HeaderRowFormatId.Value;
}
if (t.DataFormatId != null)
{
DataFormatId = t.DataFormatId.Value;
}
if (t.TotalsRowFormatId != null)
{
TotalsRowFormatId = t.TotalsRowFormatId.Value;
}
if (t.HeaderRowBorderFormatId != null)
{
HeaderRowBorderFormatId = t.HeaderRowBorderFormatId.Value;
}
if (t.BorderFormatId != null)
{
BorderFormatId = t.BorderFormatId.Value;
}
if (t.TotalsRowBorderFormatId != null)
{
TotalsRowBorderFormatId = t.TotalsRowBorderFormatId.Value;
}
if (t.HeaderRowCellStyle != null)
{
HeaderRowCellStyle = t.HeaderRowCellStyle.Value;
}
if (t.DataCellStyle != null)
{
DataCellStyle = t.DataCellStyle.Value;
}
if (t.TotalsRowCellStyle != null)
{
TotalsRowCellStyle = t.TotalsRowCellStyle.Value;
}
if (t.ConnectionId != null)
{
ConnectionId = t.ConnectionId.Value;
}
}
public Select Max(object column)
{
TableColumns.Add($"MAX({column})");
return(this);
}
public Select Min(object column)
{
TableColumns.Add($"MIN({column})");
return(this);
}
public Select Average(object column)
{
TableColumns.Add($"AVG({column})");
return(this);
}
public Select Sum(object column)
{
TableColumns.Add($"SUM({column})");
return(this);
}
public Select Count(object column)
{
TableColumns.Add($"COUNT({column})");
return(this);
}
protected TableColumns GroupNode(XmlNode node, TableColumns columns)
{
TableColumns nodeColumns = CreateColumns(node);
TableColumns res = new TableColumns();
if (columns.Length <= nodeColumns.Length)
{
for (int i = 0; i < nodeColumns.Length; i++)
{
for (int k = 0; k < columns.Length; k++)
{
if (!columns[k].marked && columns[k].Equals(nodeColumns[i]))
{
for (int s = 0; s < k - 1; s++)
{
if (!columns[s].marked)
{
res.Add(columns[s]);
columns[s].marked = true;
}
}
columns[k].marked = true;
break;
}
}
res.Add(nodeColumns[i]);
}
for (int i = 0; i < columns.Length; i++)
{
if (!columns[i].marked)
{
res.Add(columns[i]);
}
}
}
else
{
for (int i = 0; i < columns.Length; i++)
{
for (int k = 0; k < nodeColumns.Length; k++)
{
if (!nodeColumns[k].marked && nodeColumns[k].Equals(columns[i]))
{
for (int s = 0; s < k - 1; s++)
{
if (!nodeColumns[s].marked)
{
res.Add(nodeColumns[s]);
nodeColumns[s].marked = true;
}
}
nodeColumns[k].marked = true;
break;
}
}
res.Add(columns[i]);
}
for (int i = 0; i < nodeColumns.Length; i++)
{
if (!nodeColumns[i].marked)
{
res.Add(nodeColumns[i]);
}
}
}
return(res);
}
protected TableColumns CreateColumns(XmlNode node)
{
TableColumns columns = new TableColumns();
if (node is XmlElement)
{
XmlElement elem = (XmlElement)node;
if (elem.HasAttributes)
{
foreach (XmlAttribute attr in elem.Attributes)
{
TableColumn col = columns.Add();
col.type = typeof(XmlAttribute);
col.name = attr.Name;
}
}
}
if (node.HasChildNodes)
{
int i = 0;
List <XmlNode> childs = SelectChilds(node);
while (i < childs.Count)
{
XmlNode cur = childs[i];
TableColumn col = columns.Add();
col.type = cur.GetType();
col.name = GetNodeName(cur);
col.pos = 0;
col.count = 1;
int k;
for (k = 0; k < i; k++)
{
cur = childs[k];
if (!(cur is XmlSignificantWhitespace))
{
if (cur.GetType() == col.type && (!(cur is XmlElement) || GetNodeName(cur) == col.name))
{
col.pos++;
}
}
}
k = i + 1;
while (k < childs.Count)
{
cur = childs[k];
if (!(cur is XmlSignificantWhitespace))
{
if (cur.GetType() == col.type && (!(cur is XmlElement) || GetNodeName(cur) == col.name))
{
col.count++;
}
else
{
break;
}
}
k++;
}
i = k;
}
}
return(columns);
}