public ImageTextCell (int imageColumn, int textColumn) : this() { ImageBinding = new ColumnBinding (imageColumn); TextBinding = new ColumnBinding (textColumn); }
private static string DBServerName = ""; //will say Oracle or Microsoft SQL Server private DimensionError[] Check(Dimension d) { if (d.MiningModelID != null) { return new DimensionError[] { } } ; List <DimensionError> problems = new List <DimensionError>(); //TODO: need to add in code to allow you to cancel such that it will stop an executing query DataSource dataSource = d.DataSource; try { //if the key attribute points to a table with a different data source than the default data source for the DSV, use it ColumnBinding col = GetColumnBindingForDataItem(d.KeyAttribute.KeyColumns[0]); DataTable table = d.DataSourceView.Schema.Tables[col.TableID]; if (table.ExtendedProperties.ContainsKey("DataSourceID")) { dataSource = d.Parent.DataSources[table.ExtendedProperties["DataSourceID"].ToString()]; } } catch { } Microsoft.DataWarehouse.Design.DataSourceConnection openedDataSourceConnection = Microsoft.DataWarehouse.DataWarehouseUtilities.GetOpenedDataSourceConnection((object)null, dataSource.ID, dataSource.Name, dataSource.ManagedProvider, dataSource.ConnectionString, dataSource.Site, false); try { if (openedDataSourceConnection != null) { openedDataSourceConnection.QueryTimeOut = (int)dataSource.Timeout.TotalSeconds; } } catch { } if (openedDataSourceConnection == null) { DimensionError err = new DimensionError(); err.ErrorDescription = "Unable to connect to data source [" + dataSource.Name + "] to test attribute relationships and key uniqueness."; problems.Add(err); } else { sq = openedDataSourceConnection.Cartridge.IdentStartQuote; fq = openedDataSourceConnection.Cartridge.IdentEndQuote; DBServerName = openedDataSourceConnection.DBServerName; cartridge = openedDataSourceConnection.Cartridge; int iProgressCount = 0; String sql = ""; bool bGotSQL = false; foreach (DimensionAttribute da in d.Attributes) { try { bGotSQL = false; if (da.Usage != AttributeUsage.Parent) { sql = GetQueryToValidateKeyUniqueness(da); } else { sql = null; } if (sql != null) { bGotSQL = true; DataSet ds = new DataSet(); openedDataSourceConnection.Fill(ds, sql); if (ds.Tables[0].Rows.Count > 0) { string problem = "Attribute [" + da.Name + "] has key values with multiple names."; DimensionDataError err = new DimensionDataError(); err.ErrorDescription = problem; err.ErrorTable = ds.Tables[0]; problems.Add(err); } } ApplicationObject.StatusBar.Progress(true, "Checking Attribute Key Uniqueness...", ++iProgressCount, d.Attributes.Count * 2); } catch (Exception ex) { string problem = "Attempt to validate key and name relationship for attribute [" + da.Name + "] failed:" + ex.Message + ex.StackTrace + (bGotSQL ? "\r\nSQL query was: " + sql : ""); DimensionError err = new DimensionError(); err.ErrorDescription = problem; problems.Add(err); } } foreach (DimensionAttribute da in d.Attributes) { foreach (AttributeRelationship r in da.AttributeRelationships) { try { bGotSQL = false; if (da.Usage != AttributeUsage.Parent) { sql = GetQueryToValidateRelationship(r); } else { sql = null; } if (sql != null) { bGotSQL = true; DataSet ds = new DataSet(); openedDataSourceConnection.Fill(ds, sql); if (ds.Tables[0].Rows.Count > 0) { string problem = "Attribute relationship [" + da.Name + "] -> [" + r.Attribute.Name + "] is not valid because it results in a many-to-many relationship."; DimensionDataError err = new DimensionDataError(); err.ErrorDescription = problem; err.ErrorTable = ds.Tables[0]; problems.Add(err); } } } catch (Exception ex) { string problem = "Attempt to validate attribute relationship [" + da.Name + "] -> [" + r.Attribute.Name + "] failed:" + ex.Message + ex.StackTrace + (bGotSQL ? "\r\nSQL query was: " + sql : ""); DimensionError err = new DimensionError(); err.ErrorDescription = problem; problems.Add(err); } } ApplicationObject.StatusBar.Progress(true, "Checking Attribute Relationships...", ++iProgressCount, d.Attributes.Count * 2); } cartridge = null; openedDataSourceConnection.Close(); } //check obvious attribute relationship mistakes foreach (DimensionAttribute da in d.Attributes) { foreach (DimensionAttribute child in d.Attributes) { try { if (child.ID != da.ID && da.AttributeHierarchyEnabled && ContainsSubsetOfKeys(da, child) && !IsParentOf(child, da)) { if (ContainsSubsetOfKeys(child, da) && (IsParentOf(da, child) || (child.Name.CompareTo(da.Name) < 0 && child.AttributeHierarchyEnabled))) { //if the keys for both are the same, then skip this one if the opposite attribute relationship is defined... otherwise, only return one direction based on alphabetic order continue; } DimensionRelationshipWarning warn = new DimensionRelationshipWarning(); if (d.KeyAttribute.AttributeRelationships.Contains(child.ID)) { warn.ErrorDescription = "Attribute [" + child.Name + "] has a subset of the keys of attribute [" + da.Name + "]. Therefore, those attributes can be related which is preferable to leaving [" + child.Name + "] related directly to the key."; } else { warn.ErrorDescription = "Attribute [" + child.Name + "] has a subset of the keys of attribute [" + da.Name + "]. Therefore, those attributes can be related. However, this may not be necessary since [" + child.Name + "] is already part of a set of attribute relationships."; } warn.Attribute = da; warn.RelatedAttribute = child; problems.Add(warn); } } catch (Exception ex) { string problem = "Attempt to check for obvious attribute relationship oversights on [" + da.Name + "] and [" + child.Name + "] failed:" + ex.Message + ex.StackTrace; DimensionError err = new DimensionError(); err.ErrorDescription = problem; problems.Add(err); } } } return(problems.ToArray()); }
private static string GetQueryToValidateUniqueness(DataSourceView dsv, DataItem[] child, DataItem[] parent) { //need to do GetColumnBindingForDataItem StringBuilder select = new StringBuilder(); StringBuilder outerSelect = new StringBuilder(); StringBuilder groupBy = new StringBuilder(); StringBuilder orderBy = new StringBuilder(); StringBuilder join = new StringBuilder(); StringBuilder topLevelColumns = new StringBuilder(); Dictionary <DataTable, JoinedTable> tables = new Dictionary <DataTable, JoinedTable>(); List <string> previousColumns = new List <string>(); foreach (DataItem di in child) { ColumnBinding col = GetColumnBindingForDataItem(di); if (!previousColumns.Contains("[" + col.TableID + "].[" + col.ColumnID + "]")) { string colAlias = GetNextUniqueColumnIdentifier(); DataColumn dc = dsv.Schema.Tables[col.TableID].Columns[col.ColumnID]; groupBy.Append((select.Length == 0 ? "group by " : ",")); outerSelect.Append((select.Length == 0 ? "select " : ",")); select.Append((select.Length == 0 ? "select distinct " : ",")); string sIsNull = ""; string sForceDataTypeOnJoin = ""; if (dc.DataType == typeof(string)) { if (DBServerName == "Oracle") { if (di.NullProcessing == NullProcessing.Preserve) { sIsNull = "'__BIDS_HELPER_DIMENSION_HEALTH_CHECK_UNIQUE_STRING__'"; //a unique value that shouldn't ever occur in the real data } else { sIsNull = "' '"; //oracle treats empty string as null } } else { if (di.NullProcessing == NullProcessing.Preserve) { sIsNull = "'__BIDS_HELPER_DIMENSION_HEALTH_CHECK_UNIQUE_STRING__'"; //a unique value that shouldn't ever occur in the real data } else { sIsNull = "''"; } } } else if (dc.DataType == typeof(DateTime)) { if (DBServerName == "Oracle") { if (di.NullProcessing == NullProcessing.Preserve) { sIsNull = "to_date('1/1/1899 01:02:03','MM/DD/YYYY HH:MI:SS')"; //a unique value that shouldn't ever occur in the real data } else { sIsNull = "to_date('12/30/1899','MM/DD/YYYY')"; //think this is what SSAS converts null dates to } } else if (DBServerName == "Teradata") { sForceDataTypeOnJoin = "timestamp"; if (di.NullProcessing == NullProcessing.Preserve) { sIsNull = "cast('1899/12/30 01:02:03.456789' as timestamp)"; //a unique value that shouldn't ever occur in the real data } else { sIsNull = "cast('1899/12/30 00:00:00' as timestamp)"; //think this is what SSAS converts null dates to } } else { if (di.NullProcessing == NullProcessing.Preserve) { sIsNull = "'1/1/1899 01:02:03 AM'"; //a unique value that shouldn't ever occur in the real data } else { sIsNull = "'12/30/1899'"; //think this is what SSAS converts null dates to } } } else if (dc.DataType == typeof(Guid)) // Guid { if (di.NullProcessing == NullProcessing.Preserve) { sIsNull = "'" + (new Guid()).ToString() + "'"; //a unique value that shouldn't ever occur in the real data } else { sIsNull = "'" + Guid.Empty.ToString() + "'"; } } else //numeric { if (di.NullProcessing == NullProcessing.Preserve) { sIsNull = "-987654321.123456789"; //a unique value that shouldn't ever occur in the real data } else { sIsNull = "0"; } } if (!string.IsNullOrEmpty(sForceDataTypeOnJoin)) { join.Append((join.Length == 0 ? "on " : "and ")).Append("coalesce(cast(y.").Append(sq).Append(colAlias).Append(fq).Append(" as ").Append(sForceDataTypeOnJoin).Append("),").Append(sIsNull).Append(") = coalesce(cast(z.").Append(sq).Append(colAlias).Append(fq).Append(" as ").Append(sForceDataTypeOnJoin).Append("),").Append(sIsNull).AppendLine(")"); } else { join.Append((join.Length == 0 ? "on " : "and ")).Append("coalesce(y.").Append(sq).Append(colAlias).Append(fq).Append(",").Append(sIsNull).Append(") = coalesce(z.").Append(sq).Append(colAlias).Append(fq).Append(",").Append(sIsNull).AppendLine(")"); } groupBy.Append(sq).Append(colAlias).AppendLine(fq); outerSelect.Append(sq).Append(colAlias).AppendLine(fq); if (topLevelColumns.Length > 0) { topLevelColumns.Append(","); } topLevelColumns.Append("y.").Append(sq).Append(colAlias).Append(fq).Append(" as ").Append(sq).Append(dc.ColumnName).AppendLine(fq); orderBy.Append((orderBy.Length == 0 ? "order by " : ",")); orderBy.Append("y.").Append(sq).Append(colAlias).AppendLine(fq); if (!dc.ExtendedProperties.ContainsKey("ComputedColumnExpression")) { select.Append(sq).Append(dsv.Schema.Tables[col.TableID].ExtendedProperties["FriendlyName"].ToString()).Append(fq).Append(".").Append(sq).Append((dc.ExtendedProperties["DbColumnName"] ?? dc.ColumnName).ToString()).Append(fq).Append(" as ").Append(sq).Append(colAlias).AppendLine(fq); } else { select.AppendLine(dc.ExtendedProperties["ComputedColumnExpression"].ToString()); select.Append(" as ").Append(sq).Append(colAlias).AppendLine(fq); } if (!tables.ContainsKey(dsv.Schema.Tables[col.TableID])) { tables.Add(dsv.Schema.Tables[col.TableID], new JoinedTable(dsv.Schema.Tables[col.TableID])); } previousColumns.Add("[" + col.TableID + "].[" + col.ColumnID + "]"); } } foreach (DataItem di in parent) { ColumnBinding col = GetColumnBindingForDataItem(di); if (!previousColumns.Contains("[" + col.TableID + "].[" + col.ColumnID + "]")) { string colAlias = GetNextUniqueColumnIdentifier(); DataColumn dc = dsv.Schema.Tables[col.TableID].Columns[col.ColumnID]; select.Append(","); //use the __PARENT__ prefix in case there's a column with the same name but different table if (!dc.ExtendedProperties.ContainsKey("ComputedColumnExpression")) { select.Append(sq).Append(dsv.Schema.Tables[col.TableID].ExtendedProperties["FriendlyName"].ToString()).Append(fq).Append(".").Append(sq).Append((dc.ExtendedProperties["DbColumnName"] ?? dc.ColumnName).ToString()).Append(fq).Append(" as ").Append(sq).Append(colAlias).AppendLine(fq); } else { select.AppendLine(dc.ExtendedProperties["ComputedColumnExpression"].ToString()); select.Append(" as ").Append(sq).Append(colAlias).AppendLine(fq); } if (topLevelColumns.Length > 0) { topLevelColumns.Append(","); } topLevelColumns.Append("y.").Append(sq).Append(colAlias).Append(fq).Append(" as ").Append(sq).Append(dc.ColumnName).AppendLine(fq); orderBy.Append((orderBy.Length == 0 ? "order by " : ",")); orderBy.Append("y.").Append(sq).Append(colAlias).AppendLine(fq); if (!tables.ContainsKey(dsv.Schema.Tables[col.TableID])) { tables.Add(dsv.Schema.Tables[col.TableID], new JoinedTable(dsv.Schema.Tables[col.TableID])); } previousColumns.Add("[" + col.TableID + "].[" + col.ColumnID + "]"); } } int iLastTableCount = 0; while (iLastTableCount != tables.Values.Count) { iLastTableCount = tables.Values.Count; JoinedTable[] arrJt = new JoinedTable[iLastTableCount]; tables.Values.CopyTo(arrJt, 0); //because you can't iterate the dictionary keys while they are changing foreach (JoinedTable jt in arrJt) { TraverseParentRelationshipsAndAddNewTables(tables, jt.table); } } //check that all but one table have a valid join path to them DataTable baseTable = null; foreach (JoinedTable t in tables.Values) { if (!t.Joined) { if (baseTable == null) { baseTable = t.table; } else { throw new Exception("Cannot find join path for table " + t.table.TableName + " or " + baseTable.TableName + ". Only one table can be the starting table for the joins."); } } } if (baseTable == null) { //there are joins to and from the base table, so guess at the base table based on the child parameter to this function ColumnBinding col = GetColumnBindingForDataItem(child[0]); baseTable = dsv.Schema.Tables[col.TableID]; } //by now, all tables needed for joins will be in the dictionary select.Append("\r\nfrom ").AppendLine(GetFromClauseForTable(baseTable)); select.Append(TraverseParentRelationshipsAndGetFromClause(tables, baseTable)); outerSelect.AppendLine("\r\nfrom (").Append(select).AppendLine(") x").Append(groupBy).AppendLine("\r\nhaving count(*)>1"); string invalidValuesInner = outerSelect.ToString(); outerSelect = new StringBuilder(); outerSelect.Append("select ").AppendLine(topLevelColumns.ToString()).AppendLine(" from (").Append(select).AppendLine(") y"); outerSelect.AppendLine("join (").AppendLine(invalidValuesInner).AppendLine(") z").AppendLine(join.ToString()).AppendLine(orderBy.ToString()); return(outerSelect.ToString()); }
/// <summary> /// Initializes a new instance of the <see cref="Eto.Forms.ImageTextCell"/> class when binding to an indexed-based data item. /// </summary> /// <param name="imageColumn">Index of the image column in the data item.</param> /// <param name="textColumn">Index of the text column in the data item.</param> public ImageTextCell(int imageColumn, int textColumn) { ImageBinding = new ColumnBinding<Image>(imageColumn); TextBinding = new ColumnBinding<string>(textColumn); }
/// <summary> /// Initializes a new instance of the <see cref="DsBindComboBoxCell"/> class with the column index to bind. /// </summary> /// <param name="column">Column index to bind to.</param> public DsBindComboBoxCell(int column) : this() { Binding = new ColumnBinding <object>(column); }
public UsedColumn(DataItem di, ColumnBinding column, DataSourceView dsv, string usageType, string usageObjectName) { m_dataItem = di; m_column = column; m_dsv = dsv; m_usageType = usageType; m_usageObjectName = usageObjectName; }
public ComboBoxCell (int column) : this() { Binding = new ColumnBinding (column); }
/// <summary> /// Add measure into a measure group /// </summary> /// <param name="measureGroup"></param> /// <param name="tableID"></param> /// <param name="columnID"></param> /// <param name="measureName"></param> /// <param name="measureID"></param> /// <param name="displayFolder"></param> /// <param name="formatStr"></param> /// <param name="aggregationFunction"></param> /// <param name="visible"></param> /// <param name="sourceColDataType"></param> /// <param name="measureDataType"></param> internal static void ADD_MEASURE_TO_MEASURE_GROUP( DB_SQLHELPER_BASE sqlHelper , MeasureGroup measureGroup , String tableID , String columnID , String measureName , String measureID , String displayFolder , String formatStr , String aggregationFunction , bool visible = true , String sourceColDataType = "double" , String measureDataType = "double") { Microsoft.AnalysisServices.DataItem source = new Microsoft.AnalysisServices.DataItem(); source.NullProcessing = NullProcessing.Preserve; Measure measure = new Measure(measureName, measureID); String aggType = aggregationFunction.ToLower(); measure.DataType = AS_API_HELPER.GET_SSAS_MEASURE_DATA_TYPE_BY_NAME(measureDataType); if (aggType == "count*") { RowBinding rowBind = new RowBinding(); rowBind.TableID = tableID; measure.AggregateFunction = AggregationFunction.Count; source.Source = rowBind; measure.Source = source; measure.DataType = MeasureDataType.Integer; //source.DataType = AS_API_HELPER.GET_SSAS_OLEDB_TYPE_BY_NAME(sourceColDataType); } else { ColumnBinding colBind = new ColumnBinding(); colBind.TableID = tableID; colBind.ColumnID = columnID; source.DataType = AS_API_HELPER.GET_SSAS_OLEDB_TYPE_BY_NAME(sourceColDataType); source.Source = colBind; measure.AggregateFunction = AS_API_HELPER.GET_SSAS_AGGREGATION_FUNCTION_BY_NAME(aggType.ToLower()); if (aggType.ToLower() == "distinctcount") { source.NullProcessing = NullProcessing.Automatic; source.DataType = AS_API_HELPER.GET_SSAS_OLEDB_TYPE_BY_NAME("integer"); measure.DataType = MeasureDataType.Integer; } measure.Source = source; } String dataType = sourceColDataType.ToLower(); measure.DisplayFolder = displayFolder; //measure.FormatString = formatStr measure.Visible = visible; Measure measureEx = measureGroup.Measures.Find(measureID); if (measureEx != null) { sqlHelper.ADD_MESSAGE_LOG( String.Format("measure {0} exists", measureName), MESSAGE_TYPE.MEASURES, MESSAGE_RESULT_TYPE.Warning); measureEx.Name = measure.Name; measureEx.AggregateFunction = measure.AggregateFunction; measureEx.DataType = AS_API_HELPER.GET_SSAS_MEASURE_DATA_TYPE_BY_NAME(measureDataType); measureEx.DisplayFolder = measure.DisplayFolder; measureEx.Visible = measure.Visible; measureEx.FormatString = measure.FormatString; measureEx.Source = source.Clone(); } else { sqlHelper.ADD_MESSAGE_LOG( String.Format("Added measure {0} into measure group {1}", measureName, measureGroup.Name), MESSAGE_TYPE.MEASURES, MESSAGE_RESULT_TYPE.Succeed); measureGroup.Measures.Add(measure); } }
public void Build(IDbCommand adoCommand) { adoCommand.CommandText = this.Data.CommandText; Dictionary <string, IDbDataParameter> adoMap = new Dictionary <string, IDbDataParameter>(); foreach (IParameter parameter in this.Data.Parameters.Where(p => !adoMap.ContainsKey(p.Name))) { IDbDataParameter adoParameter = ParameterHelper.CreateAdoParameter(adoCommand, parameter); if (parameter.Field != null) { FieldData fieldData = this.Map.Get(parameter.Field); if (fieldData != null) { adoParameter.Value = fieldData.GetValue(); } } adoMap.Add(parameter.Name, adoParameter); adoCommand.Parameters.Add(adoParameter); } foreach (var g in this.Data.Bindings.GroupBy(b => b.Field).Select(g => g.ToArray())) { ParameterBinding parameterBinding = g.OfType <ParameterBinding>().FirstOrDefault(); ColumnBinding columnBinding = g.OfType <ColumnBinding>().FirstOrDefault(); if (parameterBinding == null && columnBinding == null) { throw new CommandException("ICommandBinding must be a ColumnBinding or ParameterBinding instance."); } IField field = columnBinding?.Field ?? parameterBinding.Field; FieldData fieldData = this.Map.Get(field); if (fieldData == null) { fieldData = this.Map.Add(field); } if (columnBinding != null) { this.headingMap[columnBinding.ColumnName] = fieldData; } else { IDbDataParameter adoParameter = adoMap.TryGetValue(parameterBinding.ParameterName); if (adoParameter == null) { adoParameter = ParameterHelper.CreateAdoParameter(adoCommand, new Parameter(parameterBinding.ParameterName, field: parameterBinding.Field)); this.SetParameterDirection(adoParameter, ParameterDirection.Output); adoMap.Add(parameterBinding.ParameterName, adoParameter); adoCommand.Parameters.Add(adoParameter); } fieldData.SetValue(adoParameter); if (adoParameter.Direction == ParameterDirection.Input) { this.SetParameterDirection(adoParameter, ParameterDirection.InputOutput); } } } }
internal static string GetQueryDefinition(Database d, NamedComponent nc, Microsoft.AnalysisServices.Binding b, List <DataItem> columnsNeeded) { StringBuilder sQuery = new StringBuilder(); if (b is DsvTableBinding) { DsvTableBinding oDsvTableBinding = (DsvTableBinding)b; DataSourceView oDSV = d.DataSourceViews[oDsvTableBinding.DataSourceViewID]; DataTable oTable = oDSV.Schema.Tables[oDsvTableBinding.TableID]; if (oTable == null) { throw new Exception("DSV table " + oDsvTableBinding.TableID + " not found"); } else if (!oTable.ExtendedProperties.ContainsKey("QueryDefinition") && oTable.ExtendedProperties.ContainsKey("DbTableName")) { foreach (DataColumn oColumn in oTable.Columns) { bool bFoundColumn = false; if (columnsNeeded == null) { bFoundColumn = true; } else { foreach (DataItem di in columnsNeeded) { if (GetColumnBindingForDataItem(di).TableID == oTable.TableName && GetColumnBindingForDataItem(di).ColumnID == oColumn.ColumnName) { bFoundColumn = true; } } } if (bFoundColumn) { if (sQuery.Length == 0) { sQuery.Append("select "); } else { sQuery.Append(","); } if (!oColumn.ExtendedProperties.ContainsKey("ComputedColumnExpression")) { sQuery.Append(sq).Append((oColumn.ExtendedProperties["DbColumnName"] ?? oColumn.ColumnName).ToString()).AppendLine(fq); } else { sQuery.Append(oColumn.ExtendedProperties["ComputedColumnExpression"].ToString()).Append(" as ").Append(sq).Append((oColumn.ExtendedProperties["DbColumnName"] ?? oColumn.ColumnName).ToString()).AppendLine(fq); } } } if (sQuery.Length == 0) { throw new Exception("There was a problem constructing the query."); } sQuery.Append("from "); if (oTable.ExtendedProperties.ContainsKey("DbSchemaName")) { sQuery.Append(sq).Append(oTable.ExtendedProperties["DbSchemaName"].ToString()).Append(fq).Append("."); } sQuery.Append(sq).Append(oTable.ExtendedProperties["DbTableName"].ToString()); sQuery.Append(fq).Append(" ").Append(sq).Append(oTable.ExtendedProperties["FriendlyName"].ToString()).AppendLine(fq); } else if (oTable.ExtendedProperties.ContainsKey("QueryDefinition")) { sQuery.AppendLine("select *"); sQuery.AppendLine("from ("); sQuery.AppendLine(oTable.ExtendedProperties["QueryDefinition"].ToString()); sQuery.AppendLine(") x"); } else { throw new Exception("Current the code does not support this type of query."); } } else if (b is QueryBinding) { QueryBinding oQueryBinding = (QueryBinding)b; sQuery.Append(oQueryBinding.QueryDefinition); } else if (b is ColumnBinding) { ColumnBinding cb = (ColumnBinding)b; object parent = cb.Parent; DataTable dt = d.DataSourceViews[0].Schema.Tables[cb.TableID]; if (nc is DimensionAttribute) { DimensionAttribute da = (DimensionAttribute)nc; if (da.Parent.KeyAttribute.KeyColumns.Count != 1) { throw new Exception("Attribute " + da.Parent.KeyAttribute.Name + " has a composite key. This is not supported for a key attribute of a dimension."); } string sDsvID = ((DimensionAttribute)nc).Parent.DataSourceView.ID; columnsNeeded.Add(new DataItem(cb.Clone())); columnsNeeded.Add(da.Parent.KeyAttribute.KeyColumns[0]); return(GetQueryDefinition(d, nc, new DsvTableBinding(sDsvID, cb.TableID), columnsNeeded)); } else { throw new Exception("GetQueryDefinition does not currently support a ColumnBinding on a object of type " + nc.GetType().Name); } } else { throw new Exception("Not a supported query binding type: " + b.GetType().FullName); } return(sQuery.ToString()); }
public ImageTextCell(int imageColumn, int textColumn) : this() { ImageBinding = new ColumnBinding(imageColumn); TextBinding = new ColumnBinding(textColumn); }
public static void Check(MeasureGroup mg, IDesignerHost designer) { if (mg.Measures.Count == 0) { throw new Exception(mg.Name + " has no measures."); } if (mg.IsLinked) { throw new Exception(mg.Name + " is a linked measure group. Run this Measure Group Health Check on the source measure group."); } DataSource oDataSource = mg.Parent.DataSource; DsvTableBinding oTblBinding = new DsvTableBinding(mg.Parent.DataSourceView.ID, GetTableIdForDataItem(mg.Measures[0].Source)); DataTable dtTable = mg.ParentDatabase.DataSourceViews[oTblBinding.DataSourceViewID].Schema.Tables[oTblBinding.TableID]; //check whether this fact table uses an alternate datasource if (dtTable.ExtendedProperties.ContainsKey("DataSourceID")) { oDataSource = mg.ParentDatabase.DataSources[dtTable.ExtendedProperties["DataSourceID"].ToString()]; } Microsoft.DataWarehouse.Design.DataSourceConnection openedDataSourceConnection = Microsoft.DataWarehouse.DataWarehouseUtilities.GetOpenedDataSourceConnection((object)null, oDataSource.ID, oDataSource.Name, oDataSource.ManagedProvider, oDataSource.ConnectionString, oDataSource.Site, false); try { if (openedDataSourceConnection != null) { openedDataSourceConnection.QueryTimeOut = (int)oDataSource.Timeout.TotalSeconds; } } catch { } sq = openedDataSourceConnection.Cartridge.IdentStartQuote; fq = openedDataSourceConnection.Cartridge.IdentEndQuote; string sBitSqlDatatype = "bit"; string sCountBig = "count_big("; string sCountBigEnd = ")"; string sFloorFunctionBegin = "floor("; string sFloorFunctionEnd = ")"; if (openedDataSourceConnection.DBServerName == "Oracle") { sBitSqlDatatype = "number(1,0)"; sCountBig = "count("; } else if (openedDataSourceConnection.DBServerName == "Teradata") { sBitSqlDatatype = "numeric(1,0)"; sCountBig = "cast(count("; sCountBigEnd = ") as bigint)"; sFloorFunctionBegin = "cast("; sFloorFunctionEnd = " as bigint)"; } string sFactQuery = GetQueryDefinition(mg.ParentDatabase, mg, oTblBinding, null); StringBuilder sOuterQuery = new StringBuilder(); foreach (Measure m in mg.Measures) { //TODO: measure expressions if ((m.AggregateFunction == AggregationFunction.Sum && !(m.Source.Source is RowBinding)) || m.AggregateFunction == AggregationFunction.AverageOfChildren || m.AggregateFunction == AggregationFunction.ByAccount || m.AggregateFunction == AggregationFunction.FirstChild || m.AggregateFunction == AggregationFunction.FirstNonEmpty || m.AggregateFunction == AggregationFunction.LastChild || m.AggregateFunction == AggregationFunction.LastNonEmpty || m.AggregateFunction == AggregationFunction.Min || m.AggregateFunction == AggregationFunction.Max || m.AggregateFunction == AggregationFunction.None) { ColumnBinding cb = GetColumnBindingForDataItem(m.Source); DataColumn col = mg.Parent.DataSourceView.Schema.Tables[cb.TableID].Columns[cb.ColumnID]; if (col.DataType == typeof(DateTime)) { continue; //DateTime not supported by BIDS Helper except for count and distinct count aggregates } if (sOuterQuery.Length > 0) { sOuterQuery.Append(","); } else { sOuterQuery.Append("select "); } string sNegativeSign = string.Empty; if (col.DataType == typeof(bool)) { sNegativeSign = "-"; //true in sql is 1, but SSAS treats true as -1 } if (m.AggregateFunction == AggregationFunction.Min || m.AggregateFunction == AggregationFunction.Max || m.AggregateFunction == AggregationFunction.None) { sOuterQuery.Append("max(").Append(sNegativeSign).Append("cast(").Append(sq).Append(cb.ColumnID).Append(fq).Append(" as float))").AppendLine(); } else { sOuterQuery.Append("sum(").Append(sNegativeSign).Append("cast(").Append(sq).Append(cb.ColumnID).Append(fq).Append(" as float))").AppendLine(); } sOuterQuery.Append(",min(").Append(sNegativeSign).Append("cast(").Append(sq).Append(cb.ColumnID).Append(fq).Append(" as float))").AppendLine(); sOuterQuery.Append(",max(").Append(sNegativeSign).Append("cast(").Append(sq).Append(cb.ColumnID).Append(fq).Append(" as float))").AppendLine(); sOuterQuery.Append(",cast(max(case when ").Append(sFloorFunctionBegin).Append(sq).Append(cb.ColumnID).Append(fq).Append(sFloorFunctionEnd).Append(" <> ").Append(sq).Append(cb.ColumnID).Append(fq).Append(" then 1 else 0 end) as ").Append(sBitSqlDatatype).Append(")").AppendLine(); sOuterQuery.Append(",cast(max(case when ").Append(sFloorFunctionBegin).Append(sq).Append(cb.ColumnID).Append(fq).Append("*10000.0").Append(sFloorFunctionEnd).Append(" <> cast(").Append(sq).Append(cb.ColumnID).Append(fq).Append("*10000.0 as float) then 1 else 0 end) as ").Append(sBitSqlDatatype).Append(")").AppendLine(); } else if (m.AggregateFunction == AggregationFunction.Count || m.AggregateFunction == AggregationFunction.DistinctCount || (m.AggregateFunction == AggregationFunction.Sum && m.Source.Source is RowBinding)) { if (sOuterQuery.Length > 0) { sOuterQuery.Append(","); } else { sOuterQuery.Append("select "); } if (m.Source.Source is RowBinding) { if (m.AggregateFunction == AggregationFunction.DistinctCount) { throw new Exception("RowBinding on a distinct count not allowed by Analysis Services"); } else { sOuterQuery.Append(sCountBig).Append("*").Append(sCountBigEnd).AppendLine(); } sOuterQuery.Append(",0").AppendLine(); sOuterQuery.Append(",1").AppendLine(); sOuterQuery.Append(",cast(0 as ").Append(sBitSqlDatatype).Append(")").AppendLine(); sOuterQuery.Append(",cast(0 as ").Append(sBitSqlDatatype).Append(")").AppendLine(); } else { ColumnBinding cb = GetColumnBindingForDataItem(m.Source); DataColumn col = mg.Parent.DataSourceView.Schema.Tables[cb.TableID].Columns[cb.ColumnID]; if (m.AggregateFunction == AggregationFunction.DistinctCount) { sOuterQuery.Append(sCountBig).Append("distinct ").Append(sq).Append(cb.ColumnID).Append(fq).Append(sCountBigEnd).AppendLine(); } else if (col.DataType == typeof(Byte[])) { sOuterQuery.Append("sum(cast(case when ").Append(sq).Append(cb.ColumnID).Append(fq).Append(" is not null then 1 else 0 end as float))").AppendLine(); } else { sOuterQuery.Append(sCountBig).Append(sq).Append(cb.ColumnID).Append(fq).Append(sCountBigEnd).AppendLine(); } sOuterQuery.Append(",0").AppendLine(); sOuterQuery.Append(",1").AppendLine(); sOuterQuery.Append(",cast(0 as ").Append(sBitSqlDatatype).Append(")").AppendLine(); sOuterQuery.Append(",cast(0 as ").Append(sBitSqlDatatype).Append(")").AppendLine(); } } else { throw new Exception("Aggregation function " + m.AggregateFunction.ToString() + " not supported!"); } } if (sOuterQuery.Length == 0) { return; } sOuterQuery.AppendLine("from (").Append(sFactQuery).AppendLine(") fact"); DataSet ds = new DataSet(); //openedDataSourceConnection.QueryTimeOut = 0; //just inherit from the datasource openedDataSourceConnection.Fill(ds, sOuterQuery.ToString()); DataRow row = ds.Tables[0].Rows[0]; openedDataSourceConnection.Close(); List <MeasureHealthCheckResult> measureResults = new List <MeasureHealthCheckResult>(); int i = 0; foreach (Measure m in mg.Measures) { if (m.AggregateFunction == AggregationFunction.Sum || m.AggregateFunction == AggregationFunction.AverageOfChildren || m.AggregateFunction == AggregationFunction.ByAccount || m.AggregateFunction == AggregationFunction.FirstChild || m.AggregateFunction == AggregationFunction.FirstNonEmpty || m.AggregateFunction == AggregationFunction.LastChild || m.AggregateFunction == AggregationFunction.LastNonEmpty || m.AggregateFunction == AggregationFunction.Count || m.AggregateFunction == AggregationFunction.DistinctCount || m.AggregateFunction == AggregationFunction.Min || m.AggregateFunction == AggregationFunction.Max || m.AggregateFunction == AggregationFunction.None) { double dsvColMaxValue = 0; bool dsvColAllowsDecimals = false; if (m.Source.Source is ColumnBinding && m.AggregateFunction != AggregationFunction.Count && m.AggregateFunction != AggregationFunction.DistinctCount) { ColumnBinding cb = GetColumnBindingForDataItem(m.Source); DataColumn col = mg.Parent.DataSourceView.Schema.Tables[cb.TableID].Columns[cb.ColumnID]; if (col.DataType == typeof(DateTime)) { continue; //DateTime not supported by BIDS Helper except for count and distinct count aggregates } MeasureDataTypeOption dsvOption = GetMeasureDataTypeOptionForType(col.DataType); if (dsvOption != null) { dsvColMaxValue = dsvOption.max; dsvColAllowsDecimals = dsvOption.allowsDecimals; } } double?total = (!Convert.IsDBNull(row[i * 5]) ? Convert.ToDouble(row[i * 5]) : (double?)null); double?min = (!Convert.IsDBNull(row[i * 5 + 1]) ? Convert.ToDouble(row[i * 5 + 1]) : (double?)null); double?max = (!Convert.IsDBNull(row[i * 5 + 2]) ? Convert.ToDouble(row[i * 5 + 2]) : (double?)null); bool hasDecimals = (!Convert.IsDBNull(row[i * 5 + 3]) ? Convert.ToBoolean(row[i * 5 + 3]) : false); bool hasMoreThan4Decimals = (!Convert.IsDBNull(row[i * 5 + 4]) ? Convert.ToBoolean(row[i * 5 + 4]) : false); MeasureDataTypeOption oldDataTypeOption = GetMeasureDataTypeOptionForMeasure(m); double recommendedMaxValue = double.MaxValue; List <MeasureDataTypeOption> possible = new List <MeasureDataTypeOption>(); foreach (MeasureDataTypeOption option in dataTypeOptions) { if ( (total == null || (option.max >= total && option.min <= total)) && (max == null || option.max >= max) && (min == null || option.min <= min) && (!hasDecimals || option.allowsDecimals) && (!hasMoreThan4Decimals || option.allowsMoreThan4Decimals) ) { possible.Add(option); if ( (total == null || (total * FreeSpaceFactor < option.max && total * FreeSpaceFactor > option.min)) && option.max < recommendedMaxValue && option.max >= dsvColMaxValue && (dsvColAllowsDecimals == option.allowsDecimals) && (option.oleDbType != OleDbType.Single) //never recommend Single ) { recommendedMaxValue = option.max; } } } foreach (MeasureDataTypeOption option in dataTypeOptions) { if (option.max == recommendedMaxValue) { Type dsvDataType = null; //don't bother getting the DSV datatype for count or DistinctCount measures if (m.Source.Source is ColumnBinding && m.AggregateFunction != AggregationFunction.Count && m.AggregateFunction != AggregationFunction.DistinctCount) { ColumnBinding cb = GetColumnBindingForDataItem(m.Source); DataColumn col = mg.Parent.DataSourceView.Schema.Tables[cb.TableID].Columns[cb.ColumnID]; dsvDataType = col.DataType; } MeasureHealthCheckResult result = new MeasureHealthCheckResult(m, FormatDouble(total), FormatDouble(min), FormatDouble(max), hasDecimals, hasMoreThan4Decimals, possible.ToArray(), option, oldDataTypeOption, dsvDataType); measureResults.Add(result); break; } } i++; } else { throw new Exception("Aggregation function " + m.AggregateFunction.ToString() + " not supported"); } } BIDSHelper.SSAS.MeasureGroupHealthCheckForm form = new BIDSHelper.SSAS.MeasureGroupHealthCheckForm(); form.measureDataTypeOptionBindingSource.DataSource = dataTypeOptions; form.measureHealthCheckResultBindingSource.DataSource = measureResults; form.Text = "Measure Group Health Check: " + mg.Name; DialogResult dialogResult = form.ShowDialog(); if (dialogResult == DialogResult.OK) { foreach (MeasureHealthCheckResult r in measureResults) { if (r.CurrentDataType != r.DataType) { //save change if (r.Measure.AggregateFunction == AggregationFunction.Count || r.Measure.AggregateFunction == AggregationFunction.DistinctCount) { r.Measure.DataType = r.DataType.dataType; } else { r.Measure.Source.DataType = r.DataType.oleDbType; r.Measure.DataType = MeasureDataType.Inherited; } //mark cube object as dirty IComponentChangeService changesvc = (IComponentChangeService)designer.GetService(typeof(IComponentChangeService)); changesvc.OnComponentChanging(mg.Parent, null); changesvc.OnComponentChanged(mg.Parent, null, null, null); //marks the cube designer as dirty } } } }
public TextBoxCell(int column) : this() { Binding = new ColumnBinding(column); }
/// <summary> /// returns information about the binding for table/dsvTable or ColumnBinding, we return the table ID /// for query binding, the returned value is -1, but the query out parameter is set to the query definition /// </summary> /// <param name="connectionID"></param> /// <param name="dsvTableNameToIdMap"></param> /// <param name="binding"></param> /// <param name="?"></param> /// <returns></returns> private int GetSourceIDForBinding(int connectionID, Dictionary <string, int> dsvTableNameToIdMap, Binding binding, out string query) { int sourceID = -1; query = null; if (binding != null) { if (binding is ColumnBinding) { ColumnBinding colBinding = (ColumnBinding)binding; // get the id of the dsv table colBinding.TableID if (dsvTableNameToIdMap.ContainsKey(colBinding.TableID)) { sourceID = dsvTableNameToIdMap[colBinding.TableID]; } } else { if (binding is DsvTableBinding) { DsvTableBinding dsvTableBinding = (DsvTableBinding)binding; if (dsvTableNameToIdMap.ContainsKey(dsvTableBinding.TableID)) { sourceID = dsvTableNameToIdMap[dsvTableBinding.TableID]; } } else { if (binding is TableBinding) { TableBinding tableBinding = (TableBinding)binding; string tableName = GetFullyQualifiedTableName(tableBinding.DbSchemaName, tableBinding.DbTableName); if (threePartNames) { tableName = String.Format("[{0}].{1}", repository.RetrieveDatabaseNameFromConnectionID(connectionID), tableName); } // get the table name from the repository itself sourceID = repository.GetTable(connectionID, tableName); if (sourceID == -1) { sourceID = repository.AddObject(tableName, string.Empty, RelationalEnumerator.ObjectTypes.Table, connectionID); } } else { if (binding is MeasureGroupDimensionBinding) { // the caller will handle this since we need the list of cb dimensions } else { if (binding is QueryBinding) { QueryBinding queryBinding = (QueryBinding)binding; query = queryBinding.QueryDefinition; } } } } } } return(sourceID); }
/* * cavaets: * doesn't handle varchar(max) DSV columns as the DSV doesn't report the length. The length needs to be set manually on the KeyColumn or NameColumn */ private void CheckDataTypeDiscrepancies(DataItem di, ColumnType ColumnType) { if (di == null) { return; } ColumnBinding cb = di.Source as ColumnBinding; if (cb == null) { return; } IModelComponent parent = di.Parent; while (parent != null && !(parent is DimensionAttribute)) { parent = parent.Parent; } DimensionAttribute da = (DimensionAttribute)parent; if (!da.Parent.DataSourceView.Schema.Tables.Contains(cb.TableID)) { return; } if (!da.Parent.DataSourceView.Schema.Tables[cb.TableID].Columns.Contains(cb.ColumnID)) { return; } DataColumn col = da.Parent.DataSourceView.Schema.Tables[cb.TableID].Columns[cb.ColumnID]; if (ColumnType == ColumnType.NameColumn) { if (col.MaxLength <= 0) { return; } if (col.DataType != typeof(string)) { return; } if (di.DataType != OleDbType.WChar) { return; } if (col.MaxLength != di.DataSize) { DataTypeDiscrepancy discrepancy = new DataTypeDiscrepancy(); discrepancy.AnalysisServicesColumnType = ColumnType; discrepancy.AnalysisServicesColumn = di; discrepancy.DimensionAttribute = da; discrepancy.DSVColumn = col; listDiscrepancies.Add(discrepancy); } } else //KeyColumn { bool bDiscrepancy = false; if (Microsoft.AnalysisServices.OleDbTypeConverter.Convert(di.DataType) != col.DataType && Microsoft.AnalysisServices.OleDbTypeConverter.GetRestrictedOleDbType(col.DataType) != di.DataType) { bDiscrepancy = true; } if (di.DataSize >= 0 && col.MaxLength >= 0 && di.DataSize != col.MaxLength) { bDiscrepancy = true; } if (bDiscrepancy) { DataTypeDiscrepancy discrepancy = new DataTypeDiscrepancy(); discrepancy.AnalysisServicesColumnType = ColumnType; discrepancy.AnalysisServicesColumn = di; discrepancy.DimensionAttribute = da; discrepancy.DSVColumn = col; listDiscrepancies.Add(discrepancy); } } }
private static string GetSnowflakeQuery(DataSourceView dsv, ColumnBinding[] child, ColumnBinding[] parent, ColumnBinding prefer) { StringBuilder select = new StringBuilder(); Dictionary<DataTable, JoinedTable> tables = new Dictionary<DataTable, JoinedTable>(); List<string> previousColumns = new List<string>(); foreach (ColumnBinding col in child) { if (!previousColumns.Contains("[" + col.TableID + "].[" + col.ColumnID + "]")) { DataColumn dc = dsv.Schema.Tables[col.TableID].Columns[col.ColumnID]; select.Append((select.Length == 0 ? "select " : ",")); if (!dc.ExtendedProperties.ContainsKey("ComputedColumnExpression")) { select.Append("[").Append(dsv.Schema.Tables[col.TableID].ExtendedProperties["FriendlyName"].ToString()).Append("].[").Append(GetColumnName(dc)).AppendLine("]"); } else { select.Append("[").Append(GetColumnName(dc)).Append("] = "); select.AppendLine(dc.ExtendedProperties["ComputedColumnExpression"].ToString()); } if (!tables.ContainsKey(dsv.Schema.Tables[col.TableID])) { tables.Add(dsv.Schema.Tables[col.TableID], new JoinedTable(dsv.Schema.Tables[col.TableID])); } previousColumns.Add("[" + col.TableID + "].[" + col.ColumnID + "]"); } } foreach (ColumnBinding col in parent) { if (!previousColumns.Contains("[" + col.TableID + "].[" + col.ColumnID + "]")) { DataColumn dc = dsv.Schema.Tables[col.TableID].Columns[col.ColumnID]; select.Append(","); if (!dc.ExtendedProperties.ContainsKey("ComputedColumnExpression")) { select.Append("[").Append(dsv.Schema.Tables[col.TableID].ExtendedProperties["FriendlyName"].ToString()).Append("].[").Append(GetColumnName(dc)).AppendLine("]"); } else { select.Append("[").Append(GetColumnName(dc)).Append("] = "); select.AppendLine(dc.ExtendedProperties["ComputedColumnExpression"].ToString()); } if (!tables.ContainsKey(dsv.Schema.Tables[col.TableID])) { tables.Add(dsv.Schema.Tables[col.TableID], new JoinedTable(dsv.Schema.Tables[col.TableID])); } previousColumns.Add("[" + col.TableID + "].[" + col.ColumnID + "]"); } } int iLastTableCount = 0; while (iLastTableCount != tables.Values.Count) { iLastTableCount = tables.Values.Count; JoinedTable[] arrJt = new JoinedTable[iLastTableCount]; tables.Values.CopyTo(arrJt, 0); //because you can't iterate the dictionary keys while they are changing foreach (JoinedTable jt in arrJt) { TraverseParentRelationshipsAndAddNewTables(tables, jt.table); } } //check that all but one table have a valid join path to them DataTable baseTable = null; foreach (JoinedTable t in tables.Values) { if (!t.Joined) { if (baseTable == null) { baseTable = t.table; } else { throw new Exception("Cannot find join path for table " + t.table.TableName + " or " + baseTable.TableName + ". Only one table can be the starting table for the joins."); } } } //by now, all tables needed for joins will be in the dictionary select.Append("\r\nfrom ").AppendLine(GetFromClauseForTable(baseTable)); select.Append(TraverseParentRelationshipsAndGetFromClause(tables, baseTable, prefer)); return select.ToString(); }
internal static int SyncDescriptions(Dimension d, bool bPromptForProperties, IServiceProvider provider, bool bIsTabular) { int iUpdatedDescriptions = 0; DataSource dataSource = d.DataSource; ColumnBinding colDimensionKey = null; #if !(YUKON || KATMAI) if (d.KeyAttribute.KeyColumns[0].Source is RowNumberBinding) { foreach (DimensionAttribute a in d.Attributes) { if (a.KeyColumns != null && a.KeyColumns.Count > 0 && a.KeyColumns[0].Source is ColumnBinding) { colDimensionKey = GetColumnBindingForDataItem(a.KeyColumns[0]); break; } } if (colDimensionKey == null) { throw new Exception("Couldn't find an attribute with a ColumnBinding, so couldn't find DSV table."); } } else { colDimensionKey = GetColumnBindingForDataItem(d.KeyAttribute.KeyColumns[0]); } #else colDimensionKey = GetColumnBindingForDataItem(d.KeyAttribute.KeyColumns[0]); #endif DataTable oDimensionKeyTable = d.DataSourceView.Schema.Tables[colDimensionKey.TableID]; //if this is a Tabular model, the Dimension.DataSource may point at the default data source for the data source view if (oDimensionKeyTable.ExtendedProperties.ContainsKey("DataSourceID")) { dataSource = d.Parent.DataSources[oDimensionKeyTable.ExtendedProperties["DataSourceID"].ToString()]; } IServiceProvider settingService = dataSource.Site; if (settingService == null) { settingService = provider; } Microsoft.DataWarehouse.Design.DataSourceConnection openedDataSourceConnection = Microsoft.DataWarehouse.DataWarehouseUtilities.GetOpenedDataSourceConnection((object)null, dataSource.ID, dataSource.Name, dataSource.ManagedProvider, dataSource.ConnectionString, settingService, false); try { if (d.MiningModelID != null) { return(iUpdatedDescriptions); } try { if (openedDataSourceConnection != null) { openedDataSourceConnection.QueryTimeOut = (int)dataSource.Timeout.TotalSeconds; } } catch { } if (openedDataSourceConnection == null) { throw new Exception("Unable to connect to data source [" + d.DataSource.Name + "]."); } else { sq = openedDataSourceConnection.Cartridge.IdentStartQuote; fq = openedDataSourceConnection.Cartridge.IdentEndQuote; DBServerName = openedDataSourceConnection.DBServerName; cartridge = openedDataSourceConnection.Cartridge; if (DBServerName != "Microsoft SQL Server") { MessageBox.Show("Data source [" + d.DataSource.Name + "] connects to " + DBServerName + " which may not be supported."); } String sql = "select distinct Name from sys.extended_properties order by Name"; if (bPromptForProperties) { DataSet dsExtendedProperties = new DataSet(); openedDataSourceConnection.Fill(dsExtendedProperties, sql); SSAS.SyncDescriptionsForm form = new SSAS.SyncDescriptionsForm(); form.cmbDescriptionProperty.DataSource = dsExtendedProperties.Tables[0]; form.cmbDescriptionProperty.DisplayMember = "Name"; form.cmbDescriptionProperty.ValueMember = "Name"; foreach (DataRow row in dsExtendedProperties.Tables[0].Rows) { form.listOtherProperties.Items.Add(row["Name"].ToString()); } DialogResult result = form.ShowDialog(); if (result != DialogResult.OK) { return(iUpdatedDescriptions); } DescriptionPropertyName = form.cmbDescriptionProperty.GetItemText(form.cmbDescriptionProperty.SelectedItem); List <string> listOtherProperties = new List <string>(); for (int i = 0; i < form.listOtherProperties.CheckedItems.Count; i++) { listOtherProperties.Add(form.listOtherProperties.GetItemText(form.listOtherProperties.CheckedItems[i])); } OtherPropertyNamesToInclude = listOtherProperties.ToArray(); OverwriteExistingDescriptions = form.chkOverwriteExistingDescriptions.Checked; } if ((string.IsNullOrEmpty(d.Description) || OverwriteExistingDescriptions) && (!oDimensionKeyTable.ExtendedProperties.ContainsKey("QueryDefinition") || bIsTabular) && //Tabular always has a QueryDefinition, even when it's just a table binding oDimensionKeyTable.ExtendedProperties.ContainsKey("DbTableName") && oDimensionKeyTable.ExtendedProperties.ContainsKey("DbSchemaName")) { sql = "SELECT PropertyName = p.name" + "\r\n" + ",PropertyValue = CAST(p.value AS sql_variant)" + "\r\n" + "FROM sys.all_objects AS tbl" + "\r\n" + "INNER JOIN sys.schemas sch ON sch.schema_id = tbl.schema_id" + "\r\n" + "INNER JOIN sys.extended_properties AS p ON p.major_id=tbl.object_id AND p.minor_id=0 AND p.class=1" + "\r\n" + "where sch.name = '" + oDimensionKeyTable.ExtendedProperties["DbSchemaName"].ToString().Replace("'", "''") + "'\r\n" + "and tbl.name = '" + oDimensionKeyTable.ExtendedProperties["DbTableName"].ToString().Replace("'", "''") + "'\r\n" + "order by p.name"; string sNewDimensionDescription = ""; DataSet dsTableProperties = new DataSet(); openedDataSourceConnection.Fill(dsTableProperties, sql); foreach (DataRow row in dsTableProperties.Tables[0].Rows) { if (string.Compare((string)row["PropertyName"], DescriptionPropertyName, true) == 0) { sNewDimensionDescription = (string)row["PropertyValue"]; } } foreach (DataRow row in dsTableProperties.Tables[0].Rows) { foreach (string sProp in OtherPropertyNamesToInclude) { if (string.Compare((string)row["PropertyName"], sProp, true) == 0 && !string.IsNullOrEmpty((string)row["PropertyValue"])) { if (sNewDimensionDescription.Length > 0) { sNewDimensionDescription += "\r\n"; } sNewDimensionDescription += (string)row["PropertyName"] + ": " + (string)row["PropertyValue"]; } } } if (!string.IsNullOrEmpty(sNewDimensionDescription)) { d.Description = sNewDimensionDescription; iUpdatedDescriptions++; } } foreach (DimensionAttribute a in d.Attributes) { ColumnBinding col = null; #if !(YUKON || KATMAI) if (a.Type == AttributeType.RowNumber) { continue; } #endif if (a.NameColumn != null) { if (!(a.NameColumn.Source is ColumnBinding)) { continue; } col = GetColumnBindingForDataItem(a.NameColumn); } else if (a.KeyColumns.Count == 1) { if (!(a.KeyColumns[0].Source is ColumnBinding)) { continue; } col = GetColumnBindingForDataItem(a.KeyColumns[0]); } else { continue; //skip this attribute since we don't know which column to use } DataTable oDsvTable = d.DataSourceView.Schema.Tables[col.TableID]; if ((string.IsNullOrEmpty(a.Description) || OverwriteExistingDescriptions) && (!oDsvTable.ExtendedProperties.ContainsKey("QueryDefinition") || bIsTabular) && oDsvTable.ExtendedProperties.ContainsKey("DbTableName") && oDsvTable.ExtendedProperties.ContainsKey("DbSchemaName")) { sql = "SELECT PropertyName = p.name" + "\r\n" + ",PropertyValue = CAST(p.value AS sql_variant)" + "\r\n" + "FROM sys.all_objects AS tbl" + "\r\n" + "INNER JOIN sys.schemas sch ON sch.schema_id = tbl.schema_id" + "\r\n" + "INNER JOIN sys.all_columns AS clmns ON clmns.object_id=tbl.object_id" + "\r\n" + "INNER JOIN sys.extended_properties AS p ON p.major_id=clmns.object_id AND p.minor_id=clmns.column_id AND p.class=1" + "\r\n" + "where sch.name = '" + oDsvTable.ExtendedProperties["DbSchemaName"].ToString().Replace("'", "''") + "'\r\n" + "and tbl.name = '" + oDsvTable.ExtendedProperties["DbTableName"].ToString().Replace("'", "''") + "'\r\n" + "and clmns.name = '" + oDsvTable.Columns[col.ColumnID].ColumnName.Replace("'", "''") + "'\r\n" + "order by p.name"; string sNewDescription = ""; DataSet dsProperties = new DataSet(); openedDataSourceConnection.Fill(dsProperties, sql); foreach (DataRow row in dsProperties.Tables[0].Rows) { if (string.Compare((string)row["PropertyName"], DescriptionPropertyName, true) == 0) { sNewDescription = (string)row["PropertyValue"]; } } foreach (DataRow row in dsProperties.Tables[0].Rows) { foreach (string sProp in OtherPropertyNamesToInclude) { if (string.Compare((string)row["PropertyName"], sProp, true) == 0 && !string.IsNullOrEmpty((string)row["PropertyValue"])) { if (sNewDescription.Length > 0) { sNewDescription += "\r\n"; } sNewDescription += (string)row["PropertyName"] + ": " + (string)row["PropertyValue"]; } } } if (!string.IsNullOrEmpty(sNewDescription)) { a.Description = sNewDescription; iUpdatedDescriptions++; } } } if (d.Site != null) //if not Tabular { //mark dimension as dirty IComponentChangeService changesvc = (IComponentChangeService)d.Site.GetService(typeof(IComponentChangeService)); changesvc.OnComponentChanging(d, null); changesvc.OnComponentChanged(d, null, null, null); } } } finally { try { cartridge = null; openedDataSourceConnection.Close(); } catch { } } return(iUpdatedDescriptions); }
private static string TraverseParentRelationshipsAndGetFromClause(Dictionary<DataTable, JoinedTable> tables, DataTable t, ColumnBinding prefer) { StringBuilder joins = new StringBuilder(); foreach (DataRelation r in t.ParentRelations) { if (prefer != null && r.ChildColumns[0].Table.TableName == prefer.TableID && r.ChildColumns[0].ColumnName != prefer.ColumnID) { //this is the right table but is not the preferred column, so skip it continue; } if (r.ParentTable != r.ChildTable && tables.ContainsKey(r.ParentTable) && !tables[r.ParentTable].AddedToQuery) { joins.Append("join ").AppendLine(GetFromClauseForTable(r.ParentTable)); for (int i = 0; i < r.ParentColumns.Length; i++) { joins.Append((i == 0 ? " on " : " and ")); joins.Append("[").Append(r.ParentTable.ExtendedProperties["FriendlyName"].ToString()).Append("].[").Append(r.ParentColumns[i].ColumnName).Append("]"); joins.Append(" = [").Append(r.ChildTable.ExtendedProperties["FriendlyName"].ToString()).Append("].[").Append(r.ChildColumns[i].ColumnName).AppendLine("]"); } joins.Append(TraverseParentRelationshipsAndGetFromClause(tables, r.ParentTable, prefer)); } } tables[t].AddedToQuery = true; return joins.ToString(); }
public ImageViewCell (int column) : this() { Binding = new ColumnBinding (column); }
public CheckBoxCell(int column) : this() { Binding = new ColumnBinding(column); }
public TextBoxCell (int column) : this() { Binding = new ColumnBinding (column); }
public void PersistAndFind() { var bindings = Emf.Configuration.Binding; Assert.IsTrue(bindings.RegisterKeyBinding(typeof(EntityA), new KeyBindingEntityA())); Assert.IsFalse(bindings.RegisterKeyBinding(typeof(EntityA), new KeyBindingEntityA())); Assert.IsTrue(bindings.RegisterKeyBinding(typeof(EntityB), new KeyBindingEntityB())); { var cb = new ColumnBinding("e"); Assert.IsTrue(bindings.RegisterColumnBinding(typeof(EntityC), cb)); Assert.IsTrue(bindings.RegisterKeyBinding(typeof(EntityC), new KeyBinding <EntityC>(e => e.Name))); } var eb1 = new EntityB(); TestBase.TestSerialization(eb1); var eb2 = new EntityB { A = new EntityA() }; TestBase.TestSerialization(eb2); using (var em = Emf.CreateEntityManager()) { em.Persist(eb1); Assert.IsNotNull(eb1.Key); Assert.IsFalse(string.IsNullOrEmpty(eb1.Key.Row)); Assert.IsFalse(string.IsNullOrEmpty(eb1.Key.ColumnFamily)); Assert.IsTrue(string.IsNullOrEmpty(eb1.Key.ColumnQualifier)); Assert.IsTrue(eb1.Key.Row.StartsWith(typeof(EntityB).Name)); em.Persist(eb2); Assert.IsNotNull(eb2.Key); Assert.IsFalse(string.IsNullOrEmpty(eb2.Key.Row)); Assert.IsFalse(string.IsNullOrEmpty(eb2.Key.ColumnFamily)); Assert.IsTrue(string.IsNullOrEmpty(eb2.Key.ColumnQualifier)); Assert.IsTrue(eb2.Key.Row.StartsWith(typeof(EntityB).Name)); Assert.IsTrue(eb2.A.Name.StartsWith(typeof(EntityA).Name)); } using (var em = Emf.CreateEntityManager()) { var _eb1 = em.Find <EntityB>(eb1.Key); Assert.AreEqual(eb1, _eb1); Assert.IsTrue(_eb1.Key.Row.StartsWith(_eb1.GetType().Name)); var _ea2 = em.Find <EntityA>(eb2.A.Name); // EntityA key binding uses the Name property as row key Assert.AreEqual(eb2.A, _ea2); var _eb2 = em.Find <EntityB>(eb2.Key); Assert.AreEqual(eb2, _eb2); Assert.IsTrue(_eb2.Key.Row.StartsWith(typeof(EntityB).Name)); Assert.AreEqual(eb2.A.Name, _eb2.A.Name); Assert.IsTrue(eb2.A.Name.StartsWith(typeof(EntityA).Name)); } var ec1 = new EntityC { Name = "11", A = new EntityA() }; TestBase.TestSerialization(ec1); var ec2 = new EntityC { Name = "222", A = new EntityA(), B = new EntityB() }; TestBase.TestSerialization(ec2); using (var em = Emf.CreateEntityManager()) { em.Persist(ec1); Assert.AreEqual(ec1.Id, ec1.Name); em.Persist(ec2); Assert.AreEqual(ec2.Id, ec2.Name); } using (var em = Emf.CreateEntityManager()) { var _ec1 = em.Find <EntityC>("11"); Assert.AreEqual(ec1, _ec1); var _ec2 = em.Find <EntityC>(ec2); Assert.AreEqual(ec2, _ec2); } bindings.UnregisterBinding(typeof(EntityA)); bindings.UnregisterBinding(typeof(EntityB)); bindings.UnregisterBinding(typeof(EntityC)); }