private DataItem CreateDataItem(DataSourceView parDsv, string parTableName, string parColumnName) { DataTable dataTable = ((DataSourceView)parDsv).Schema.Tables[parTableName]; DataColumn dataColumn = dataTable.Columns[parColumnName]; return(new DataItem(parTableName, parColumnName, OleDbTypeConverter.GetRestrictedOleDbType(dataColumn.DataType))); }
public override void Exec() { try { UIHierarchy solExplorer = this.ApplicationObject.ToolWindows.SolutionExplorer; UIHierarchyItem hierItem = (UIHierarchyItem)((System.Array)solExplorer.SelectedItems).GetValue(0); ProjectItem projItem = (ProjectItem)hierItem.Object; DataSourceView dsv = (DataSourceView)projItem.Object; IterateDsvColumns(dsv); ReportViewerForm frm = new ReportViewerForm(); frm.ReportBindingSource.DataSource = usedColumns; frm.Report = "SSAS.UsedColumns.rdlc"; Microsoft.Reporting.WinForms.ReportDataSource reportDataSource1 = new Microsoft.Reporting.WinForms.ReportDataSource(); reportDataSource1.Name = "BIDSHelper_UsedColumn"; reportDataSource1.Value = frm.ReportBindingSource; frm.ReportViewerControl.LocalReport.DataSources.Add(reportDataSource1); frm.ReportViewerControl.LocalReport.ReportEmbeddedResource = "SSAS.UsedColumns.rdlc"; frm.Caption = "Used Columns Report"; frm.WindowState = FormWindowState.Maximized; frm.Show(); } catch (System.Exception ex) { MessageBox.Show(ex.Message); } }
private void SaveAction(object obj) { var results = DataSourceView.Cast <Mahasiswa>(); Model = results.ToList(); WindowClose(); }
protected override void OnDataBound(EventArgs e) { base.OnDataBound(e); if (!this.DesignMode) { ViewState["TotalRowCount"] = 0; if (this.PageCount > 1) { if (this.SelectArguments.TotalRowCount > -1) { ViewState["TotalRowCount"] = this.SelectArguments.TotalRowCount; } } else { ViewState["TotalRowCount"] = this.Rows.Count; } if (this.TotalRowCount < 1) { IDataSource IDS = this.GetDataSource(); DataSourceView dsv = IDS.GetView(this.DataMember); dsv.Select(DataSourceSelectArguments.Empty, this.SetRowCount); } } }
/// <summary> /// 创建关系 /// </summary> /// <param name="parDsv">数据源视图</param> /// <param name="parFkTableName">外键表名</param> /// <param name="parFkColumnName">外键列名</param> /// <param name="parPkTableName">主键表名</param> /// <param name="parPkColumnName">主键列名</param> public void AddRelation(DataSourceView parDsv, string parFkTableName, string parFkColumnName, string parPkTableName, string parPkColumnName) { DataColumn fkColumn = parDsv.Schema.Tables[parFkTableName].Columns[parFkColumnName]; DataColumn pkColumn = parDsv.Schema.Tables[parPkTableName].Columns[parPkColumnName]; parDsv.Schema.Relations.Add("FK_" + parFkTableName + "_" + parPkColumnName, pkColumn, fkColumn); }
/// <summary> /// 创建数据源视图 /// </summary> /// <param name="parDatabase">数据库</param> /// <param name="parDataSourceName">数据源名</param> /// <param name="parDataSourceViewName">数据源视图名</param> public void CreateDataSourceView(Database parDatabase, string parDataSourceName, string parDataSourceViewName) { DataSourceView dsv = parDatabase.DataSourceViews.Add(parDataSourceViewName); dsv.DataSourceID = parDataSourceName; dsv.Schema = new DataSet(); //Open a connection to the data source OleDbConnection connection = new OleDbConnection(dsv.DataSource.ConnectionString); connection.Open(); //增加 DimGeography表 AddTable(dsv, connection, "DimGeography"); //AddComputedColumn(dsv, connection, "DimCustomer", "FullName", "CASE WHEN MiddleName IS NULL THEN FirstName + ' ' + LastName ELSE FirstName + ' ' + MiddleName + ' ' + LastName END"); //AddComputedColumn(dsv, connection, "DimCustomer", "GenderDesc", "CASE WHEN Gender = 'M' THEN 'Male' ELSE 'Female' END"); //AddComputedColumn(dsv, connection, "DimCustomer", "MaritalStatusDesc", "CASE WHEN MaritalStatus = 'S' THEN 'Single' ELSE 'Married' END"); //增加 DimCustomer表 AddTable(dsv, connection, "DimCustomer"); AddRelation(dsv, "DimCustomer", "GeographyKey", "DimGeography", "GeographyKey"); //增加 FactInternetSales表 AddTable(dsv, connection, "FactInternetSales"); AddRelation(dsv, "FactInternetSales", "CustomerKey", "DimCustomer", "CustomerKey"); dsv.Update(); }
protected override void OnDataBinding(EventArgs e) { base.OnDataBinding(e); DataSourceView view = GetData(); // view could be null when a user implements his own GetData(). if (null == view) { throw new InvalidOperationException(SR.GetString(SR.DataControl_ViewNotFound, ID)); } // DevDiv 1036362: enable async model binding for ListControl bool useAsyncSelect = false; if (AppSettings.EnableAsyncModelBinding) { var modelDataView = view as ModelDataSourceView; useAsyncSelect = modelDataView != null && modelDataView.IsSelectMethodAsync; } if (useAsyncSelect) { _asyncSelectPending = true; // disable post data binding action until the callback is invoked view.Select(SelectArguments, OnDataSourceViewSelectCallback); } else { IEnumerable data = view.ExecuteSelect(DataSourceSelectArguments.Empty); PerformDataBinding(data); } }
/* * Fact Col1 - Granularity Attribute * Col2 - Source Table name */ private static void getFactMeasureGroupAttributeUsage(List <DimensionUsage> dimUsage, MeasureGroup mg, DegenerateMeasureGroupDimension factMGdim) { DimensionUsage usage = null; usage = new DimensionUsage("Fact", mg, factMGdim.CubeDimension, factMGdim.Dimension); usage.Column1Name = "Granularity Attribute"; usage.Column2Name = "Source Table"; foreach (MeasureGroupAttribute mga in factMGdim.Attributes) { //mga. if (mga.Type == MeasureGroupAttributeType.Granularity) { usage.Column1Value = mga.Attribute.Name; foreach (DataItem di in mga.KeyColumns) { if (di.Source is ColumnBinding) { usage.Column2Value = ((ColumnBinding)di.Source).TableID; DataSourceView dsv = mga.ParentCube.DataSourceView; if (dsv.Schema.Tables.Contains(usage.Column2Value)) { DataTable oTable = dsv.Schema.Tables[dsv.Schema.Tables.IndexOf(usage.Column2Value)]; if (oTable.ExtendedProperties.ContainsKey("FriendlyName")) { usage.Column2Value = oTable.ExtendedProperties["FriendlyName"].ToString(); } } } } } } dimUsage.Add(usage); }
protected virtual IEnumerable GetData() { if (DataSource != null && IsBoundUsingDataSourceID) { throw new HttpException("Control bound using both DataSourceID and DataSource properties."); } if (DataSource != null) { return(DataSourceResolver.ResolveDataSource(DataSource, DataMember)); } if (!IsBoundUsingDataSourceID) { return(null); } IEnumerable result = null; DataSourceView boundDataSourceView = ConnectToDataSource(); boundDataSourceView.Select(DataSourceSelectArguments.Empty, delegate(IEnumerable data) { result = data; }); return(result); }
static void AddRelation(DataSourceView dsv, String fkTableName, String fkColumnName, String pkTableName, String pkColumnName) { DataColumn fkColumn = dsv.Schema.Tables[fkTableName].Columns[fkColumnName]; DataColumn pkColumn = dsv.Schema.Tables[pkTableName].Columns[pkColumnName]; dsv.Schema.Relations.Add("FK_" + fkTableName + "_" + fkColumnName, pkColumn, fkColumn, true); }
/// <summary> /// Creating the Dimension, Attribute, Hierarchy, and MemberProperty Objects. /// </summary> /// <param name="objDatabase">DB instance.</param> /// <param name="objDataSourceView">DataSource instance.</param> /// <param name="strTableNamesAndKeys">Array of Table names and keys.</param> /// <param name="intDimensionTableCount">Dimension table count.</param> /// <returns>Dimension Array.</returns> private static object[] CreateDimension(Database objDatabase, DataSourceView objDataSourceView, string[,] strTableNamesAndKeys, int intDimensionTableCount) { try { Console.WriteLine("Creating the Dimension, Attribute, Hierarchy, and MemberProperty Objects ..."); Dimension[] objDimensions = new Dimension[intDimensionTableCount]; for (int i = 0; i < intDimensionTableCount; i++) { objDimensions[i] = (Dimension)GenerateDimension(objDatabase, objDataSourceView, strTableNamesAndKeys[i, 0], strTableNamesAndKeys[i, 1]); } ////Add Hierarchy and Level //Hierarchy objHierarchy = objDimension.Hierarchies.Add("ProductByCategory"); //objHierarchy.Levels.Add("Category").SourceAttributeID = objCatKeyAttribute.ID; //objHierarchy.Levels.Add("Product").SourceAttributeID = objProdKeyAttribute.ID; ////Add Member Property ////objProdKeyAttribute.AttributeRelationships.Add(objProdDescAttribute.ID); //objDimension.Update(); return(objDimensions); } catch (Exception ex) { Console.WriteLine("Error in Creating the Dimension, Attribute, Hierarchy, and MemberProperty Objects. Error Message -> " + ex.Message); return(null); } }
/// <devdoc> /// Returns an IEnumerable that is the DataSource, which either came /// from the DataSource property or from the control bound via the /// DataSourceID property. /// </devdoc> protected virtual IEnumerable GetData() { DataSourceView view = ConnectToDataSourceView(); Debug.Assert(_currentViewValid); if (view != null) { // DevDiv 1070535: enable async model binding for Repeater bool useAsyncSelect = false; if (AppSettings.EnableAsyncModelBinding) { var modelDataView = view as ModelDataSourceView; useAsyncSelect = modelDataView != null && modelDataView.IsSelectMethodAsync; } if (useAsyncSelect) { _asyncSelectPending = true; // disable post data binding action until the callback is invoked view.Select(SelectArguments, OnDataSourceViewSelectCallback); } else { return(view.ExecuteSelect(SelectArguments)); } } return(null); }
public static DataSourceView GetTypedDatSourceView(YDataSourceType dataSourceType, DataSourceView dataSourceView = null) { DataSourceView ds = dataSourceType switch { YDataSourceType.AzureBlobStorage => new DataSourceViewAzureBlobStorage(), YDataSourceType.AzureBlobFS => new DataSourceViewAzureBlobFS(), YDataSourceType.AzureSqlDatabase => new DataSourceViewAzureSqlDatabase(), YDataSourceType.AzureSqlDW => new DataSourceViewAzureSqlDatabase(), YDataSourceType.AzureDatabricks => new DataSourceViewAzureDatabricks(), YDataSourceType.CosmosDb => new DataSourceViewCosmosDb(), _ => new DataSourceViewUnknown() }; // clone if (dataSourceView != null) { ds.DataSource.AdditionalData = dataSourceView.DataSource.AdditionalData; ds.DataSource.Description = dataSourceView.DataSource.Description; ds.DataSource.Name = dataSourceView.DataSource.Name; ds.DataSource.Type = dataSourceView.DataSource.Type; ds.EngineId = dataSourceView.EngineId; ds.IsNew = dataSourceView.IsNew; if (ds.DataSource.AdditionalData?["properties"] is JObject props) { ds.DataSource.OnDeserialized(props); } } return(ds); } }
/// <summary> /// Generate single dimension. /// </summary> /// <param name="objDatabase">DB instance.</param> /// <param name="objDataSourceView">DataSourceView instance.</param> /// <param name="strTableName">Table name.</param> /// <param name="strTableKeyName">Table key.</param> /// <returns>Dimension instance.</returns> private static object GenerateDimension(Database objDatabase, DataSourceView objDataSourceView, string strTableName, string strTableKeyName) { try { Dimension objDimension = new Dimension(); //Add Dimension to the Database objDimension = objDatabase.Dimensions.Add(strTableName); objDimension.Source = new DataSourceViewBinding(objDataSourceView.ID); DimensionAttributeCollection objDimensionAttributesColl = objDimension.Attributes; //Add Dimension Attributes DimensionAttribute objAttribute = objDimensionAttributesColl.Add(strTableKeyName); //Set Attribute usage and source objAttribute.Usage = AttributeUsage.Key; objAttribute.KeyColumns.Add(strTableName, strTableKeyName, OleDbType.Integer); objDimension.Update(); return(objDimension); } catch (Exception ex) { Console.WriteLine("Error in Creating the Dimension, Attribute, Hierarchy, and MemberProperty Objects - GenerateDimension. Error Message -> " + ex.Message); return(null); } }
/// <summary> /// Create data item /// </summary> /// <param name="dsv"></param> /// <param name="factTableName"></param> /// <param name="factFKDimColumnName"></param> /// <param name="dataType"></param> /// <returns></returns> internal static DataItem CREATE_DATA_ITEM( DB_SQLHELPER_BASE sqlHelper, DataSourceView dsv, String factTableName, String factFKDimColumnName, System.Data.OleDb.OleDbType dataType) { DataTable data_table = dsv.Schema.Tables[factTableName]; DataColumn dataColumn = data_table.Columns[factFKDimColumnName]; if (dataColumn == null) { sqlHelper.ADD_MESSAGE_LOG( String.Format("Table [{0}] doesn't have column [{0}]", factTableName, factFKDimColumnName), MESSAGE_TYPE.COLUMN_BINDING_DATA_ITEM, MESSAGE_RESULT_TYPE.Error); } DataItem dataItem = new DataItem(factTableName, dataColumn.ColumnName); if (dataType != null) { dataItem.DataType = dataType; } else { dataItem.DataType = OleDbTypeConverter.GetRestrictedOleDbType(dataColumn.DataType); } return(dataItem); }
/// <summary> /// Populate ssas cube data source view defination /// </summary> /// <param name="oledb">oledb connection obj</param> /// <param name="dsv">ssas cube data source view obj</param> /// <param name="dsvSchemaName">dsv schema name: eg.. OLAP_FACT_SALES</param> /// <param name="dsvQueryText">query text of dsv</param> /// <param name="dbTableName">depended table name</param> /// <param name="tableType"></param> /// <param name="db_SchemaName"></param> /// <param name="isNamedQuery"></param> internal static void ADD_TABLE_TO_CUBE_DSV(IDbConnection oledb, DataSourceView dsv, String dsvSchemaName, String dsvQueryText, String dbTableName, String tableType, String db_SchemaName, bool isNamedQuery = false) { System.Data.DataSet schema = dsv.Schema; System.Data.OleDb.OleDbCommand select_cmd = new System.Data.OleDb.OleDbCommand(); select_cmd.CommandText = dsvQueryText; select_cmd.Connection = oledb as System.Data.OleDb.OleDbConnection; System.Data.OleDb.OleDbDataAdapter adapter = new System.Data.OleDb.OleDbDataAdapter(); adapter.SelectCommand = select_cmd; DataTable[] data_tables = adapter.FillSchema(schema, SchemaType.Mapped, dsvSchemaName); DataTable data_table = data_tables[0]; data_table.ExtendedProperties.Remove("TableType"); data_table.ExtendedProperties.Add("TableType", tableType); if (isNamedQuery) { data_table.ExtendedProperties.Remove("QueryDefinition"); data_table.ExtendedProperties.Add("QueryDefinition", dsvQueryText); } else { data_table.ExtendedProperties.Remove("DbSchemaName"); data_table.ExtendedProperties.Add("DbSchemaName", db_SchemaName); } data_table.ExtendedProperties.Remove("DbTableName"); data_table.ExtendedProperties.Add("DbTableName", dbTableName); data_table.ExtendedProperties.Remove("FriendlyName"); data_table.ExtendedProperties.Add("FriendlyName", dbTableName); }
private DataSourceView ConnectToDataSourceView() { if (!this._currentViewValid || base.DesignMode) { if ((this._currentView != null) && this._currentViewIsFromDataSourceID) { this._currentView.DataSourceViewChanged -= new EventHandler(this.OnDataSourceViewChanged); } this._currentDataSource = this.GetDataSource(); string dataMember = this.DataMember; if (this._currentDataSource == null) { this._currentDataSource = new ReadOnlyDataSource(this.DataSource, dataMember); } else if (this.DataSource != null) { throw new InvalidOperationException(System.Web.SR.GetString("DataControl_MultipleDataSources", new object[] { this.ID })); } this._currentDataSourceValid = true; DataSourceView view = this._currentDataSource.GetView(dataMember); if (view == null) { throw new InvalidOperationException(System.Web.SR.GetString("DataControl_ViewNotFound", new object[] { this.ID })); } this._currentViewIsFromDataSourceID = base.IsBoundUsingDataSourceID; this._currentView = view; if ((this._currentView != null) && this._currentViewIsFromDataSourceID) { this._currentView.DataSourceViewChanged += new EventHandler(this.OnDataSourceViewChanged); } this._currentViewValid = true; } return(this._currentView); }
/// <summary> /// Create Column Binding Data Item /// </summary> /// <param name="dsv"></param> /// <param name="tableName"></param> /// <param name="columnName"></param> /// <param name="dataType"></param> /// <returns></returns> internal static DataItem CREATE_COLUMN_BINDING_DATA_ITEM( DB_SQLHELPER_BASE sqlHelper, DataSourceView dsv, String tableName, String columnName, System.Data.OleDb.OleDbType dataType) { DataItem dataItem = null; DataTable data_table = dsv.Schema.Tables[tableName]; if (data_table == null) { sqlHelper.ADD_MESSAGE_LOG( String.Format("Table {0} is not existed in current DSV", tableName), MESSAGE_TYPE.COLUMN_BINDING_DATA_ITEM, MESSAGE_RESULT_TYPE.Warning); } DataColumn dataColumn = data_table.Columns[columnName]; if (dataColumn == null) { sqlHelper.ADD_MESSAGE_LOG( String.Format("Column {0} is not existed in table {1}", columnName, tableName), MESSAGE_TYPE.COLUMN_BINDING_DATA_ITEM, MESSAGE_RESULT_TYPE.Warning); } dataItem = new DataItem(tableName, dataColumn.ColumnName); if (dataType != null) { dataItem.DataType = dataType; } else { dataItem.DataType = OleDbTypeConverter.GetRestrictedOleDbType(dataColumn.DataType); } return(dataItem); }
IEnumerable GetData() { IEnumerable result; #if NET_2_0 if (IsBoundUsingDataSourceID) { if (DataSourceID.Length == 0) { return(null); } if (boundDataSource == null) { return(null); } DataSourceView dsv = boundDataSource.GetView(String.Empty); dsv.Select(SelectArguments, new DataSourceViewSelectCallback(SelectCallback)); result = data; data = null; } else #endif result = DataSourceResolver.ResolveDataSource(DataSource, DataMember); return(result); }
public void InsertRow(int rowIndex) { IDataSource ds = GetDataSource(); DataSourceView view = ds.GetView(this.DataMember); GridViewRow row = this.Rows[rowIndex]; OrderedDictionary values = new OrderedDictionary(); for (int i = 0; i < this.Columns.Count; ++i) { this.Columns[i].ExtractValuesFromCell(values, (DataControlFieldCell)row.Cells[i], DataControlRowState.Insert, false); } GridViewInsertingEventArgs insertingArgs = new GridViewInsertingEventArgs(values, rowIndex); OnRowInserting(insertingArgs); if (insertingArgs.Cancel) { return; } view.Insert(values, delegate(int affectedRecords, Exception ex) { GridViewInsertedEventArgs insertedArgs = new GridViewInsertedEventArgs(affectedRecords, ex, values); OnRowInserted(insertedArgs); if (!insertedArgs.KeepInInsertMode) { this.InsertRowsCount = 0; } return(insertedArgs.ExceptionHandled); }); }
public ActionDataSourceView(ActionDataSource source, string viewName, DataSourceView underlyingDataSourceView) : base(source, viewName) { UnderlyingView = underlyingDataSourceView; ActionDS = source; }
static void AddComputedColumn(DataSourceView dsv, OleDbConnection connection, String tableName, String computedColumnName, String expression) { DataSet tmpDataSet = new DataSet(); tmpDataSet.Locale = CultureInfo.CurrentCulture; OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT (" + expression + ") AS [" + computedColumnName + "] FROM [dbo].[" + tableName + "] WHERE 1=0", connection); DataTable[] dataTables = adapter.FillSchema(tmpDataSet, SchemaType.Mapped, tableName); DataTable dataTable = dataTables[0]; DataColumn dataColumn = dataTable.Columns[computedColumnName]; dataTable.Constraints.Clear(); dataTable.Columns.Remove(dataColumn); dataColumn.ExtendedProperties.Add("DbColumnName", computedColumnName); dataColumn.ExtendedProperties.Add("ComputedColumnExpression", expression); dataColumn.ExtendedProperties.Add("IsLogical", "True"); dsv.Schema.Tables[tableName].Columns.Add(dataColumn); dataColumn = null; dataTable = null; dataTables = null; adapter = null; tmpDataSet = null; }
public async Task <IActionResult> OnGetAsync(Guid engineId, string dataSourceName) { if (string.IsNullOrEmpty(dataSourceName)) { return(new NotFoundResult()); } try { var response = await this.client.ProcessRequestApiAsync <YDataSourceUnknown>( $"api/Datafactories/{engineId}/links/{dataSourceName}").ConfigureAwait(false); if (response.StatusCode == System.Net.HttpStatusCode.NotFound) { return(new NotFoundResult()); } var dataSource = response.Value; this.DataSourceView = dataSource.ToTypedDataSourceView(); this.DataSourceView.IsNew = false; this.DataSourceView.EngineId = engineId; } catch (Exception) { return(NotFound()); } return(Page()); }
private void GetSelectedText() { if (this.DesignMode) { return; } if (this.SelectedID == "") { this.SelectedText = ""; return; } if (this.SelectedText != "" && !this.Page.IsCallback) { return; } try { try { // call the event GetSeletedItemTextEventArgs e = new GetSeletedItemTextEventArgs(); e.SelectedID = this.SelectedID; GetSeletedItemText(this, e); this.SelectedText = e.TextValue; } catch { if (this.DataMember != "") { IDataSource IDS = this.GetDataSource(); DataSourceView dsv = IDS.GetView(this.DataMember); dsv.Select(DataSourceSelectArguments.Empty, this.GetTextFromView); } else { string typeName = this.DataSource.GetType().Name; switch (typeName) { case "DataSet": SetSelectedTextValue(((DataSet)this.DataSource).Tables[0].DefaultView); break; case "DataTable": SetSelectedTextValue(((DataTable)this.DataSource).DefaultView); break; default: SetSelectedTextValue((DataView)this.DataSource); break; } } } } catch {} }
private static void BuildCube() { try { Console.WriteLine("Cube creation process started."); Console.WriteLine(""); string strDBServerName = "LocalHost"; //имя сервер string strProviderName = "msolap"; //имя провайдера string strFactTableName = "OrderDetails"; //имя таблицы фактов string strDBName = "northwind"; //имя бд string strCubeDBName = "OLAPDB"; //имя куба string strCubeDataSourceName = "OLAPDS"; //имя источника данных string strCubeDataSourceViewName = "OLAPDSView"; //имя представления int intDimensionTableCount = 2; //число измерений string[,] strTableNamesAndKeys = { { "Products", "ProductID", "OrderDetails", "ProductID" }, { "Orders", "OrderID", "OrderDetails", "OrderID" }, //{ "DimProduct", "ProductKey", "FactResellerSales", "ProductKey" }, // { "DimPromotion", "PromotionKey", "FactResellerSales", "PromotionKey" }, // { "DimReseller", "ResellerKey", "FactResellerSales", "ResellerKey" }, //{ "DimSalesTerritory", "SalesTerritoryKey", "FactResellerSales", "SalesTerritoryKey" }, }; Server objServer = new Server(); // сервер Database objDatabase = new Database(); // бд RelationalDataSource objDataSource = new RelationalDataSource(); //источник DataSourceView objDataSourceView = new DataSourceView(); // представление DataSet objDataSet = new DataSet(); // набор данных Dimension[] objDimensions = new Dimension[intDimensionTableCount]; //массив измерений //Connecting to the Analysis Services. Подключаемся к АС objServer = (Server)ConnectAnalysisServices(strDBServerName, strProviderName); //Creating a Database. Создаем БД objDatabase = (Database)CreateDatabase(objServer, strCubeDBName); //Creating a DataSource. Создаем источник objDataSource = (RelationalDataSource)CreateDataSource(objServer, objDatabase, strCubeDataSourceName, strDBServerName, strDBName); //Creating a DataSourceView. Создаем представление objDataSet = (DataSet)GenerateDWSchema(strDBServerName, strDBName, strFactTableName, strTableNamesAndKeys, intDimensionTableCount); objDataSourceView = (DataSourceView)CreateDataSourceView(objDatabase, objDataSource, objDataSet, strCubeDataSourceViewName); //Creating the Dimension, Attribute, Hierarchy, and MemberProperty Objects. Создаем измерения и прочее objDimensions = (Dimension[])CreateDimension(objDatabase, objDataSourceView, strTableNamesAndKeys, intDimensionTableCount); //Creating the Cube, MeasureGroup, Measure, and Partition Objects. Создаем куб CreateCube(objDatabase, objDataSourceView, objDataSource, objDimensions, strFactTableName, strTableNamesAndKeys, intDimensionTableCount); objDatabase.Process(ProcessType.ProcessFull); Console.WriteLine("Cube created successfully."); } catch (Exception ex) { Console.WriteLine("Error -> " + ex.Message); } Console.WriteLine(""); Console.WriteLine("Press any key to exit."); Console.ReadLine(); }
public SessionDataSourceView(SqlDataSource owner, string name, HttpContext context, DataSourceView originalView, HttpSessionState session) : base(owner, name, context) { this.owner = owner as SessionDataSource; this.originalView = originalView; this.session = session; this.context = context; }
private DataItem CreateDataItem(DataSourceView dsv, string tableName, string columnName) { DataTable dataTable = ((DataSourceView)dsv).Schema.Tables[tableName]; DataColumn dataColumn = dataTable.Columns[columnName]; return(new DataItem(tableName, columnName, OleDbTypeConverter.GetRestrictedOleDbType(dataColumn.DataType))); }
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; }
protected override DataSourceView GetData() { dataBindTrace.Append("[Start GetData]"); DataSourceView d = base.GetData(); dataBindTrace.Append("[End GetData]"); return(d); }
/// <devdoc> /// Gets the DataSourceView of the IDataSource that this control is /// bound to, if any. /// </devdoc> protected virtual DataSourceView GetData() { DataSourceView view = ConnectToDataSourceView(); Debug.Assert(_currentViewValid); return(view); }
//-------------------- public bool CreateDimensionAttributeForLevel(Dimension dimension, DataSourceView dataSourceView, String levelId, String levelName, String tableName, String keyColumn, String nameColumn, AttributeUsage attributeUsage, OrderBy orderBy, AttributeType attribType, bool visible) { if (dimension.Attributes == null) { return false; } DimensionAttribute dimAttrib; if (!dimension.Attributes.Contains(levelId)) { dimAttrib = dimension.Attributes.Add(levelName, levelId); dimAttrib.Usage = attributeUsage; dimAttrib.OrderBy = orderBy; dimAttrib.Type = attribType; dimAttrib.AttributeHierarchyVisible = true;// visible; dimAttrib.AttributeHierarchyOptimizedState = OptimizationType.NotOptimized; dimAttrib.AttributeHierarchyOrdered = true; dimAttrib.IsAggregatable = true; //设置MembersWithDataCaption会导致当前粒度在子粒度中存在,形如"XXXX(All)",因此注释掉 //if (attributeUsage == AttributeUsage.Parent) //{ // dimAttrib.MembersWithDataCaption = "*(All)"; //} dimAttrib.MembersWithData = MembersWithData.NonLeafDataHidden; DataItem diKey = CreateDataItem(dataSourceView, tableName, keyColumn); dimAttrib.KeyColumns.Add(diKey); if (!String.IsNullOrEmpty(nameColumn)) { DataItem diName = CreateDataItem(dataSourceView, tableName, nameColumn); dimAttrib.NameColumn = diName; DataItem diValue = CreateDataItem(dataSourceView, tableName, nameColumn); dimAttrib.ValueColumn = diValue; } return true; } return true; }
static void AddCompositeRelation(DataSourceView dsv, String fkTableName, String pkTableName, String columnName1, String columnName2) { DataColumn[] fkColumns = new DataColumn[2]; fkColumns[0] = dsv.Schema.Tables[fkTableName].Columns[columnName1]; fkColumns[1] = dsv.Schema.Tables[fkTableName].Columns[columnName2]; DataColumn[] pkColumns = new DataColumn[2]; pkColumns[0] = dsv.Schema.Tables[pkTableName].Columns[columnName1]; pkColumns[1] = dsv.Schema.Tables[pkTableName].Columns[columnName2]; dsv.Schema.Relations.Add("FK_" + fkTableName + "_" + columnName1 + "_" + columnName2, pkColumns, fkColumns, true); }
/* * Demo code! */ private void AddTable(DataSourceView dsv, OleDbConnection connection, string tableName, string dataSourceID) { OleDbDataAdapter adapter = new OleDbDataAdapter( "SELECT * FROM [dbo].[" + tableName + "] WHERE 1=0", connection); DataTable[] dataTables = adapter.FillSchema(dsv.Schema, SchemaType.Mapped, tableName); DataTable dataTable = dataTables[0]; dataTable.ExtendedProperties.Add("TableType", "Table"); dataTable.ExtendedProperties.Add("DbSchemaName", "dbo"); dataTable.ExtendedProperties.Add("DbTableName", tableName); dataTable.ExtendedProperties.Add("FriendlyName", tableName); dataTable.ExtendedProperties.Add("DataSourceID", dataSourceID); }
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 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 = System.Guid.NewGuid().ToString(); 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 = ""; //select.Append(" /*" + dc.DataType.FullName + "*/ "); //for troubleshooting data types if (dc.DataType == typeof(string)) { 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 (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 //numeric { if (di.NullProcessing == NullProcessing.Preserve) sIsNull = "-987654321.123456789"; //a unique value that shouldn't ever occur in the real data else sIsNull = "0"; } join.Append((join.Length == 0 ? "on " : "and ")).Append("coalesce(y.[").Append(colAlias).Append("],").Append(sIsNull).Append(") = coalesce(z.[").Append(colAlias).Append("],").Append(sIsNull).AppendLine(")"); groupBy.Append("[").Append(colAlias).AppendLine("]"); outerSelect.Append("[").Append(colAlias).AppendLine("]"); if (topLevelColumns.Length > 0) topLevelColumns.Append(","); topLevelColumns.Append("y.[").Append(colAlias).Append("] as [").Append(dc.ColumnName).AppendLine("]"); if (!dc.ExtendedProperties.ContainsKey("ComputedColumnExpression")) { select.Append("[").Append(colAlias).Append("] = [").Append(dsv.Schema.Tables[col.TableID].ExtendedProperties["FriendlyName"].ToString()).Append("].[").Append((dc.ExtendedProperties["DbColumnName"] ?? dc.ColumnName).ToString()).AppendLine("]"); } else { select.Append("[").Append(colAlias).Append("]"); select.Append(" = ").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 (DataItem di in parent) { ColumnBinding col = GetColumnBindingForDataItem(di); if (!previousColumns.Contains("[" + col.TableID + "].[" + col.ColumnID + "]")) { string colAlias = System.Guid.NewGuid().ToString(); 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("[").Append(colAlias).Append("] = [").Append(dsv.Schema.Tables[col.TableID].ExtendedProperties["FriendlyName"].ToString()).Append("].[").Append((dc.ExtendedProperties["DbColumnName"] ?? dc.ColumnName).ToString()).AppendLine("]"); } else { select.Append("[").Append(colAlias).Append("]"); select.Append(" = ").AppendLine(dc.ExtendedProperties["ComputedColumnExpression"].ToString()); } if (topLevelColumns.Length > 0) topLevelColumns.Append(","); topLevelColumns.Append("y.[").Append(colAlias).Append("] as [").Append(dc.ColumnName).AppendLine("]"); 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(") as y"); outerSelect.AppendLine("join (").AppendLine(invalidValuesInner).AppendLine(") z").AppendLine(join.ToString()); return outerSelect.ToString(); }
/// <devdoc> /// Connects this data bound control to the appropriate DataSourceView /// and hooks up the appropriate event listener for the /// DataSourceViewChanged event. The return value is the new view (if /// any) that was connected to. An exception is thrown if there is /// a problem finding the requested view or data source. /// </devdoc> private DataSourceView ConnectToDataSourceView() { if (_currentViewValid && !DesignMode) { // If the current view is correct, there is no need to reconnect return _currentView; } // Disconnect from old view, if necessary if ((_currentView != null) && (_currentViewIsFromDataSourceID)) { // We only care about this event if we are bound through the DataSourceID property _currentView.DataSourceViewChanged -= new EventHandler(OnDataSourceViewChanged); } EnsureSingleDataSource(); // Connect to new view _currentDataSource = GetDataSource(); string dataMember = DataMember; if (_currentDataSource == null) { // DataSource control was not found, construct a temporary data source to wrap the data _currentDataSource = new ReadOnlyDataSource(DataSource, dataMember); } _currentDataSourceValid = true; // IDataSource was found, extract the appropriate view and return it DataSourceView newView = _currentDataSource.GetView(dataMember); if (newView == null) { throw new InvalidOperationException(SR.GetString(SR.DataControl_ViewNotFound, ID)); } _currentViewIsFromDataSourceID = IsDataBindingAutomatic; _currentView = newView; if ((_currentView != null) && (_currentViewIsFromDataSourceID)) { // We only care about this event if we are bound through the DataSourceID property _currentView.DataSourceViewChanged += new EventHandler(OnDataSourceViewChanged); } _currentViewValid = true; return _currentView; }
public UnusedColumn(DataColumn column, DataSourceView dsv) { m_column = column; m_dsv = dsv; }
/// <summary> /// 创建表 /// </summary> /// <param name="parDsv">数据源视图</param> /// <param name="parConn">连接到数据仓库</param> /// <param name="parTable">表名</param> public void AddTable(DataSourceView dsv, OleDbConnection connection, String tableName) { OleDbDataAdapter adapter = new OleDbDataAdapter( "SELECT * FROM [dbo].[" + tableName + "] WHERE 1=0", connection); DataTable[] dataTables = adapter.FillSchema(dsv.Schema, //将表的结构映射到数据源视图 SchemaType.Mapped, tableName); DataTable dataTable = dataTables[0]; dataTable.ExtendedProperties.Add("TableType", "Table"); //为表增加扩展属性 dataTable.ExtendedProperties.Add("DbSchemaName", "dbo"); dataTable.ExtendedProperties.Add("DbTableName", tableName); dataTable.ExtendedProperties.Add("FriendlyName", tableName); }
/* * Create new database objects; for future use */ void AddNewDataAccessObjects(Database db) { // Create connection to datasource cto extract schema to a dataset DataSet dset = new DataSet(); SqlConnection cn = new SqlConnection("Data Source=localhost; Initial Catalog=Chapter 16; Integrated Security=true"); // Create the Customers data adapter with the calculated appended SqlDataAdapter daCustomers = new SqlDataAdapter( "SELECT *, " + "(CASE WHEN (Age < 30) THEN ’GenY’ " + " WHEN (Age >= 30 AND Age < 40) THEN ’GenX’ " + "ELSE ’Baby Boomer’ END) AS Generation " + "FROM Customers", cn); daCustomers.FillSchema(dset, SchemaType.Mapped, "Customers"); // Add Extended properties to the Generation column indicating to // Analysis Services that it is a calculated column DataColumn genColumn = dset.Tables["Customers"].Columns ["Generation"]; genColumn.ExtendedProperties.Add("DbColumnName", "Generation"); genColumn.ExtendedProperties.Add("Description", "Customer generation"); genColumn.ExtendedProperties.Add("IsLogical", "true"); genColumn.ExtendedProperties.Add("ComputedColumnExpression", "CASE WHEN (Age < 30) THEN ’GenY’ " + "WHEN (Age >= 30 AND Age < 40) THEN ’GenX’ " + "ELSE ’Baby Boomer’ END"); // Create a ’Pay Channels’ data adapter with a customer query // for our named query SqlDataAdapter daPayChannels = new SqlDataAdapter( "SELECT * FROM Channels " + "WHERE Channel IN (’Cinemax’, ’Encore’, ’HBO’, ’Showtime’, " + "’STARZ!’, ’The Movie Channel’)", cn); daPayChannels.FillSchema(dset, SchemaType.Mapped, "PayChannels"); // Add Extended properties to the PayChannels table indicating to // Analysis Services that it is a named query DataTable pcTable = dset.Tables["PayChannels"]; pcTable.ExtendedProperties.Add("IsLogical", "true"); pcTable.ExtendedProperties.Add("Description", "Channels requiring an additional fee"); pcTable.ExtendedProperties.Add("TableType", "View"); pcTable.ExtendedProperties.Add("QueryDefinition", "SELECT * FROM Channels " + "WHERE Channel IN (’Cinemax’, ’Encore’, ’HBO’, ’Showtime’, " + "’STARZ!’, ’The Movie Channel’)"); // Add relationship between Customers and PayChannels DataRelation drCustomerPayChannels = new DataRelation( "CustomerPayChannels", dset.Tables["Customers"].Columns["SurveyTakenID"], dset.Tables["PayChannels"].Columns["SurveyTakenID"]); dset.Relations.Add(drCustomerPayChannels); // Access the data source and the DSV created previously // by specifying the ID DataSourceView dsv = new DataSourceView("MovieClick", "MovieClick"); dsv.DataSourceID = "MovieClick"; dsv.Schema = dset.Clone(); db.DataSourceViews.Add(dsv); // Update the database to create the objects on the server db.Update(UpdateOptions.ExpandFull); }
private void CreateCube(Database database, DataSource dataSource, DataSourceView dataSourceView, CubeEntity cube) { Cube ssasCube = database.Cubes.Find(cube.ID); if (ssasCube != null) ssasCube.Drop(); ssasCube = database.Cubes.Add(cube.Name, cube.ID); ssasCube.Source = new DataSourceViewBinding(dataSourceView.ID); ssasCube.StorageMode = StorageMode.Molap; ssasCube.Visible = true; foreach (var item in cube.Dimensions) { ssasCube.Dimensions.Add(item.ID); } var defaultGroup = ssasCube.MeasureGroups.Add("Default"); defaultGroup.StorageMode = StorageMode.Molap; defaultGroup.ProcessingMode = ProcessingMode.LazyAggregations; foreach (var outerMeasure in cube.Measures) { var ssasMeasure = defaultGroup.Measures.Add(outerMeasure.Name, outerMeasure.ID); ssasMeasure.Source = this.CreateDataItem(dataSourceView, cube.TableName, outerMeasure.ColumnName); ssasMeasure.AggregateFunction = this.GetAggregator(outerMeasure.Aggregator); //measure.FormatString = format;// "000,000.##"; //measure.Description = description; //measure.DisplayFolder = folderName; ssasMeasure.Visible = outerMeasure.Visable; } foreach (var dim in cube.Dimensions) { var cubeDim = ssasCube.Dimensions.Find(dim.ID); var groupDim = defaultGroup.Dimensions.Add(dim.ID); string levelId = ""; for (int i = 0; i < cubeDim.Attributes.Count; i++) { if (cubeDim.Dimension.Attributes[i].Usage == AttributeUsage.Key) { levelId = cubeDim.Dimension.Attributes[i].ID; break; } } var measureGroupAttribute = groupDim.Attributes.Add(levelId); measureGroupAttribute.Type = MeasureGroupAttributeType.Granularity; measureGroupAttribute.KeyColumns.Add(CreateDataItem(dataSourceView, cube.TableName, dim.FKColumn)); } CreatePartition(defaultGroup, dataSource, cube.TableName, DateTime.Now); ssasCube.Update(UpdateOptions.ExpandFull); ssasCube.Process(); }
private void CreateDim(Database database, DataSourceView dataSourceView, DimensionEntity dim) { Dimension ssasDimension; if (!database.Dimensions.Contains(dim.ID)) { ssasDimension = database.Dimensions.Add(dim.Name, dim.ID); ssasDimension.UnknownMember = UnknownMemberBehavior.Hidden; ssasDimension.AttributeAllMemberName = "all"; ssasDimension.StorageMode = DimensionStorageMode.Molap; ssasDimension.Source = new DataSourceViewBinding(dataSourceView.ID); ssasDimension.Type = DimensionType.Regular; } else { ssasDimension = database.Dimensions[dim.ID]; } if ((dim.Hierarchies == null || dim.Hierarchies.Count == 0) && (dim.Levels != null && dim.Levels.Count != 0)) { if (!ssasDimension.Hierarchies.Contains(DefaultHierarchy)) { Hierarchy ssasHierarchy = ssasDimension.Hierarchies.Add(DefaultHierarchy, DefaultHierarchy); ssasHierarchy.AllMemberName = "all"; CreateLevels(ssasDimension, ssasHierarchy, dataSourceView, dim.Levels); } } else { foreach (var outerHierarchy in dim.Hierarchies) { Hierarchy ssasHierarchy; if (!ssasDimension.Hierarchies.Contains(outerHierarchy.ID)) { ssasHierarchy = ssasDimension.Hierarchies.Add(outerHierarchy.Name, outerHierarchy.ID); ssasHierarchy.AllMemberName = "all"; } else { ssasHierarchy = ssasDimension.Hierarchies[outerHierarchy.ID]; } if (outerHierarchy.Levels != null && outerHierarchy.Levels.Count > 0) { CreateLevels(ssasDimension, ssasHierarchy, dataSourceView, outerHierarchy.Levels); } } } ssasDimension.Update(); }
public void CreateViewRelation(DataSourceView dataSourceView, String parFkTableName, String parFkColumnName, String parPkTableName, String parPkColumnName) { DataColumn fkColumn = dataSourceView.Schema.Tables[parFkTableName].Columns[parFkColumnName]; DataColumn pkColumn = dataSourceView.Schema.Tables[parPkTableName].Columns[parPkColumnName]; string fkName = "FK_" + parFkTableName + "_" + parPkTableName; fkName = GetFKName(dataSourceView, fkName, 0); dataSourceView.Schema.Relations.Add(fkName, pkColumn, fkColumn); }
protected void IterateDsvColumns(DataSourceView dsv) { m_dsv = dsv; //add all DSV columns to a list unusedColumns.Clear(); usedColumns.Clear(); foreach (DataTable t in dsv.Schema.Tables) { foreach (DataColumn c in t.Columns) { unusedColumns.Add("[" + t.TableName + "].[" + c.ColumnName + "]", new UnusedColumn(c, dsv)); } } //remove columns that are used in dimensions foreach (Dimension dim in dsv.Parent.Dimensions) { if (dim.DataSourceView != null && dim.DataSourceView.ID == dsv.ID) { foreach (DimensionAttribute attr in dim.Attributes) { foreach (DataItem di in attr.KeyColumns) { ProcessDataItemInLists(di, "Dimension Attribute Key"); } ProcessDataItemInLists(attr.NameColumn, "Dimension Attribute Name"); ProcessDataItemInLists(attr.ValueColumn, "Dimension Attribute Value"); ProcessDataItemInLists(attr.UnaryOperatorColumn, "Dimension Attribute Unary Operator"); ProcessDataItemInLists(attr.SkippedLevelsColumn, "Dimension Attribute Skipped Levels"); ProcessDataItemInLists(attr.CustomRollupColumn, "Dimension Attribute Custom Rollup"); ProcessDataItemInLists(attr.CustomRollupPropertiesColumn, "Dimension Attribute Custom Rollup Properties"); foreach (AttributeTranslation tran in attr.Translations) { ProcessDataItemInLists(tran.CaptionColumn, "Dimension Attribute Translation"); } } } } foreach (Cube cube in dsv.Parent.Cubes) { if (cube.DataSourceView != null && cube.DataSourceView.ID == dsv.ID) { foreach (MeasureGroup mg in cube.MeasureGroups) { //remove columns that are used in measures foreach (Measure m in mg.Measures) { ProcessDataItemInLists(m.Source, "Measure"); } //remove columns that are used in dimension relationships foreach (MeasureGroupDimension mgdim in mg.Dimensions) { if (mgdim is ManyToManyMeasureGroupDimension) { //no columns to remove } else if (mgdim is DataMiningMeasureGroupDimension) { //no columns to remove } else if (mgdim is RegularMeasureGroupDimension) { //Degenerate dimensions and Reference dimensions RegularMeasureGroupDimension regMDdim = (RegularMeasureGroupDimension)mgdim; foreach (MeasureGroupAttribute mga in regMDdim.Attributes) { if (mga.Type == MeasureGroupAttributeType.Granularity) { foreach (DataItem di3 in mga.KeyColumns) { ProcessDataItemInLists(di3, "Fact Table Dimension Key"); } } } } } } } } //remove mining structure columns foreach (MiningStructure structure in dsv.Parent.MiningStructures) { if (structure.DataSourceView != null && structure.DataSourceView.ID == dsv.ID) RecurseMiningStructureColumnsAndProcessDataItemInLists(structure.Columns); } }
/// <summary> /// 创建项 /// </summary> /// <param name="parDsv">数据源视图</param> /// <param name="parTableName">表名</param> /// <param name="parColumnName">列名</param> /// <returns></returns> public DataItem CreateDataItem(DataSourceView parDsv, string parTableName, string parColumnName) { DataTable dataTable = ((DataSourceView)parDsv).Schema.Tables[parTableName]; DataColumn dataColumn = dataTable.Columns[parColumnName]; return new DataItem(parTableName, parColumnName, OleDbTypeConverter.GetRestrictedOleDbType(dataColumn.DataType)); }
private DataItem CreateDataItem(DataSourceView dsv, string tableName, string columnName) { DataTable dataTable = ((DataSourceView)dsv).Schema.Tables[tableName]; DataColumn dataColumn = dataTable.Columns[columnName]; return new DataItem(tableName, columnName, OleDbTypeConverter.GetRestrictedOleDbType(dataColumn.DataType)); }
public void RaiseUnsupportedCapabilitiesError(DataSourceView view) {}
static void AddTable(DataSourceView dsv, OleDbConnection connection, String tableName) { string strSelectText = "SELECT * FROM [dbo].[" + tableName + "] WHERE 1=0"; OleDbDataAdapter adapter = new OleDbDataAdapter(strSelectText, connection); DataTable[] dataTables = adapter.FillSchema(dsv.Schema, SchemaType.Mapped, tableName); DataTable dataTable = dataTables[0]; dataTable.ExtendedProperties.Add("TableType", "Table"); dataTable.ExtendedProperties.Add("DbSchemaName", "dbo"); dataTable.ExtendedProperties.Add("DbTableName", tableName); dataTable.ExtendedProperties.Add("FriendlyName", tableName); dataTable = null; dataTables = null; adapter = null; }
/* * Create mining structure for selected database */ private MiningStructure CreateMiningStructure(Database objDatabase) { // drop the existing structures with the same name DropExistingStructures(objDatabase, sStructureName); // Initialize a new mining structure MiningStructure currentMiningStruct = new MiningStructure(sStructureName, sStructureName); currentMiningStruct.Source = new DataSourceViewBinding("Adventure Works DW"); DataSourceView currentDataSource = new DataSourceView("Adventure Works DW"); // Create the columns of the mining structure // setting the type, content and data binding // User Id column ScalarMiningStructureColumn StructKey = new ScalarMiningStructureColumn("StructKey", "StructKey"); StructKey.Type = MiningStructureColumnTypes.Long; StructKey.Content = MiningStructureColumnContents.Key; StructKey.IsKey = true; // Add data binding to the column StructKey.KeyColumns.Add("dbo_DimCustomer", "CustomerKey", System.Data.OleDb.OleDbType.Integer); // Add the column to the mining structure currentMiningStruct.Columns.Add(StructKey); // Generation column ScalarMiningStructureColumn Gender = new ScalarMiningStructureColumn("Gender", "Gender"); Gender.Type = MiningStructureColumnTypes.Text; Gender.Content = MiningStructureColumnContents.Discrete; // Add data binding to the column Gender.KeyColumns.Add("dbo_DimCustomer", "Gender", System.Data.OleDb.OleDbType.WChar); // Add the column to the mining structure currentMiningStruct.Columns.Add(Gender); // Add Nested table by creating a table column and adding // a key column to the nested table /* TableMiningStructureColumn PayChannels = new TableMiningStructureColumn("PayChannels", "PayChannels"); PayChannels.ForeignKeyColumns.Add("dbo_Customer", "TotalChildren", System.Data.OleDb.OleDbType.Integer); ScalarMiningStructureColumn Channel = new ScalarMiningStructureColumn("Channel", "Channel"); Channel.Type = MiningStructureColumnTypes.Text; Channel.Content = MiningStructureColumnContents.Key; Channel.IsKey = true; // Add data binding to the column Channel.KeyColumns.Add("dbo_Customer", "FirstName", System.Data.OleDb.OleDbType.WChar); PayChannels.Columns.Add(Channel); currentMiningStruct.Columns.Add(PayChannels); * */ // Add the mining structure to the database objDatabase.MiningStructures.Add(currentMiningStruct); currentMiningStruct.Update(); return currentMiningStruct; }
void UpdateViewData () { if (currentView != null) currentView.DataSourceViewChanged -= new EventHandler (OnDataSourceViewChanged); DataSourceView view = InternalGetData (); if (view != currentView) currentView = view; if (currentView != null) currentView.DataSourceViewChanged += new EventHandler (OnDataSourceViewChanged); }
/* * Create new database objects; for future use */ private void CreateDataAccessObjects(Database objDB) { // Create a relational data source // by specifying the name and the id RelationalDataSource ds = new RelationalDataSource("MovieClick", Utils.GetSyntacticallyValidID("MovieClick", typeof(Database))); ds.ConnectionString = "Provider=SQLNCLI10.1;Data Source=localhost;Integrated Security=SSPI;Initial Catalog=Chapter 16"; objDB.DataSources.Add(ds); // Create connection to datasource to extract schema to a dataset DataSet dset = new DataSet(); SqlConnection cn = new SqlConnection("Data Source=localhost; Initial Catalog=Chapter 16; Integrated Security=true"); // Create data adapters from database tables and load schemas SqlDataAdapter daCustomers = new SqlDataAdapter("SELECT * FROM Customers", cn); daCustomers.FillSchema(dset, SchemaType.Mapped, "Customers"); SqlDataAdapter daChannels = new SqlDataAdapter("SELECT * FROM Channels", cn); daChannels.FillSchema(dset, SchemaType.Mapped, "Channels"); // Add relationship between Customers and Channels DataRelation drCustomerChannels = new DataRelation("Customer_Channels", dset.Tables["Customers"].Columns["SurveyTakenID"], dset.Tables["Channels"].Columns["SurveyTakenID"]); dset.Relations.Add(drCustomerChannels); // Create the DSV, ad the dataset and add to the database DataSourceView dsv = new DataSourceView("SimpleMovieClick", "SimpleMovieClick"); dsv.DataSourceID = "MovieClick"; dsv.Schema = dset.Clone(); objDB.DataSourceViews.Add(dsv); // Update the database to create the objects on the server objDB.Update(UpdateOptions.ExpandFull); }
private string GetFKName(DataSourceView dataSourceView, string name, int idx) { string temp = name; foreach (DataRelation dr in dataSourceView.Schema.Relations) { if (dr.RelationName == name) { idx++; temp += idx.ToString(); return GetFKName(dataSourceView, temp, idx); } } return name; }
protected override void OnDataPropertyChanged () { base.OnDataPropertyChanged (); currentView = null; }
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(); }
private void CreateLevels(Dimension ssasDimension, Hierarchy ssasHierarchy, DataSourceView dataSourceView, List<LevelEntity> outerLevels) { var miniOuterLevel = outerLevels[outerLevels.Count - 1]; this.CreateDimensionAttributeForLevel( ssasDimension , dataSourceView , miniOuterLevel.ID , miniOuterLevel.Name , miniOuterLevel.SourceTable , miniOuterLevel.KeyColumn , miniOuterLevel.NameColumn , AttributeUsage.Key , OrderBy.Key , AttributeType.Regular , true); foreach (var outerLevel in outerLevels) { Level ssasLevel; if (!ssasHierarchy.Levels.Contains(outerLevel.ID)) { this.CreateDimensionAttributeForLevel( ssasDimension , dataSourceView , outerLevel.ID , outerLevel.Name , outerLevel.SourceTable , outerLevel.KeyColumn , outerLevel.NameColumn , AttributeUsage.Regular , OrderBy.Key , AttributeType.Regular , true); ssasLevel = ssasHierarchy.Levels.Add(outerLevel.Name); ssasLevel.SourceAttributeID = ssasDimension.Attributes.GetByName(outerLevel.Name).ID; } else { ssasLevel = ssasHierarchy.Levels[outerLevel.ID]; } } }
DataSourceView ConnectToDataSource () { if (_boundDataSourceView != null) return _boundDataSourceView; /* verify that the data source exists and is an IDataSource */ object ctrl = null; Page page = Page; if (page != null) ctrl = page.FindControl (DataSourceID); if (ctrl == null || !(ctrl is IDataSource)) { string format; if (ctrl == null) format = "DataSourceID of '{0}' must be the ID of a control of type IDataSource. A control with ID '{1}' could not be found."; else format = "DataSourceID of '{0}' must be the ID of a control of type IDataSource. '{1}' is not an IDataSource."; throw new HttpException (String.Format (format, ID, DataSourceID)); } _boundDataSourceView = ((IDataSource)ctrl).GetView (String.Empty); _boundDataSourceView.DataSourceViewChanged += OnDataSourceViewChanged; return _boundDataSourceView; }
/// <summary> /// Build mining database /// </summary> /// <param name="MainServerConnectionString">Main server connection string</param> /// <param name="AnalysisServerConnectionString">Analysis services connection string</param> /// <param name="TempServerConnectionString">Temp server connection string (contain views)</param> /// <param name="strPrefix">Prefix</param> /// <param name="dt_parametters">Decision Tree Parametters</param> public static void BuildMiningDatabase(string MainServerConnectionString, string AnalysisServerConnectionString, string TempServerConnectionString, string strPrefix = "PF", DecisionTreeAlgorithmParameters dt_parametter = null) { if (dt_parametter == null) dt_parametter = new DecisionTreeAlgorithmParameters(); string strMiningDBName = "Job Zoom Mining"; //Mining database name (Analysis Service) string strMiningDataSourceName = "Data Source"; //Mining datasource name (Analysis Service) string strMiningDataSourceViewName = "Data Source View"; //Mining datasource view name (Analysis Service) string[] strFactTableNames = getAllMiningTableNames(TempServerConnectionString, strPrefix); //tables in datasource view to mining string[,] strTableNamesAndKeys = { { "PivotProfile", "ProfileBasicId", "PivotProfile", "ProfileBasicId" }, }; int intDimensionTableCount = 0; Server objServer = new Server(); Database objDatabase = new Database(); RelationalDataSource objDataSource = new RelationalDataSource(); DataSourceView objDataSourceView = new DataSourceView(); DataSet objDataSet = new DataSet(); Dimension[] objDimensions = new Dimension[intDimensionTableCount]; MiningStructure[] objMiningStructures = new MiningStructure[strFactTableNames.Length]; //Console.WriteLine("Mining creation process started."); //Console.WriteLine(""); //Console.WriteLine("Step 1. Connecting to the Analysis Services."); //Console.WriteLine("Step 1. Started!"); objServer = (Server)ConnectAnalysisServices(AnalysisServerConnectionString); //Console.WriteLine("Step 1. Finished!"); //Console.WriteLine(""); //Console.WriteLine("Step 2. Creating a Database."); //Console.WriteLine("Step 2. Started!"); objDatabase = (Database)CreateDatabase(objServer, strMiningDBName); strMiningDBName = objDatabase.Name; //Console.WriteLine("Step 2. Finished!"); //Console.WriteLine(""); //Console.WriteLine("Step 3. Creating a DataSource."); //Console.WriteLine("Step 3. Started!"); objDataSource = (RelationalDataSource)CreateDataSource(objServer, objDatabase, strMiningDataSourceName, TempServerConnectionString); //Console.WriteLine("Step 3. Finished!"); //Console.WriteLine(""); //Console.WriteLine("Step 4. Creating a DataSourceView."); //Console.WriteLine("Step 4. Started!"); //objDataSet = (DataSet)GenerateDWSchema(strDBServerName, strDBName, strFactTableName, strTableNamesAndKeys, intDimensionTableCount); objDataSet = (DataSet)GenerateDWSchema(TempServerConnectionString, strPrefix); //Get all mining views objDataSourceView = (DataSourceView)CreateDataSourceView(objDatabase, objDataSource, objDataSet, strMiningDataSourceViewName); //Console.WriteLine("Step 4. Finished!"); //Console.WriteLine(""); //Console.WriteLine("Step 5. Createing Mining Structures [with Decision Tree Algorithms]"); //Console.WriteLine("Step 5. Started!"); objMiningStructures = (MiningStructure[])CreateMiningStructures(objDatabase, objDataSourceView, strFactTableNames, new DecisionTreeAlgorithmParameters()); //objDatabase.Process(ProcessType.ProcessFull); //Console.WriteLine("Step 5. Finished!"); //Console.WriteLine(""); //Console.WriteLine("Step 6. Export mining data to JobZoom Database (Database Engine)"); //Console.WriteLine("Step 6. Started!"); //Console.WriteLine("Preparing... Put website to maintenance mode"); //EXEC WEB SITE MAINTENANCE SERVICE METHOD //Console.WriteLine("Preparing... Cleaning DecisionTreeNode and DecisionTreeNodeDistribution"); //Console.WriteLine("\nStep 6. Finished!"); //Console.WriteLine(""); exportMiningDataToDB(MainServerConnectionString, AnalysisServerConnectionString, strFactTableNames, strPrefix); //Console.WriteLine("Export completed! Release website to continuing for using"); //WEBSITE CAN CONTINUE FOR USING //Console.WriteLine("Process Full..."); //objDatabase.Process(ProcessType.ProcessFull); //Console.WriteLine("Analysis Service Database created successfully."); //Console.WriteLine("Step 7. Removing Analysis Database"); //Console.WriteLine("Step 7. Started!"); //Console.WriteLine(deleteDatabase(objServer, objDatabase.Name)); deleteDatabase(objServer, objDatabase.Name); //Console.WriteLine("Removing Analysis Database completely ..."); //Console.WriteLine("\nStep 7. Finished!"); //Console.WriteLine("Press any key to exit."); //Console.ReadLine(); }
public void AddComputedColumn(DataSourceView dsv, OleDbConnection connection, String tableName, String computedColumnName, String expression) { DataSet tmpDataSet = new DataSet(); tmpDataSet.Locale = CultureInfo.CurrentCulture; OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT (" + expression + ") AS [" + computedColumnName + "] FROM [dbo].[" + tableName + "] WHERE 1=0", connection); DataTable[] dataTables = adapter.FillSchema(tmpDataSet, SchemaType.Mapped, tableName); DataTable dataTable = dataTables[0]; DataColumn dataColumn = dataTable.Columns[computedColumnName]; dataTable.Constraints.Clear(); dataTable.Columns.Remove(dataColumn); dataColumn.ExtendedProperties.Add("DbColumnName", computedColumnName); dataColumn.ExtendedProperties.Add("ComputedColumnExpression", expression); dataColumn.ExtendedProperties.Add("IsLogical", "True"); dsv.Schema.Tables[tableName].Columns.Add(dataColumn); }
/// <summary> /// Generate a mining structure and full process it /// </summary> /// <param name="objDatabase">Analysis Service Database instance</param> /// <param name="objDataSourceView">Analysis Service DataSourceView instance</param> /// <param name="strCaseTableName">Mining table name</param> /// <returns>Mining structure</returns> public static object GenerateMiningStructure(Database objDatabase, DataSourceView objDataSourceView, string strCaseTableName, DecisionTreeAlgorithmParameters dtParams) { try { MiningStructure objMiningStructure = new MiningStructure(); objMiningStructure = objDatabase.MiningStructures.Add(objDatabase.MiningStructures.GetNewName(StringEncode(strCaseTableName))); objMiningStructure.HoldoutMaxPercent = dtParams.HoldoutMaxPercent; // Percent for testing objMiningStructure.Source = new DataSourceViewBinding(objDataSourceView.ID); objMiningStructure.CaseTableName = strCaseTableName; foreach (string name in getAllColumnName(objDataSourceView, strCaseTableName)) { string colName = StringEncode(name); ScalarMiningStructureColumn column = new ScalarMiningStructureColumn(colName, colName); switch (colName) { case "ID": // ProfileBasicId column column.Type = MiningStructureColumnTypes.Long; column.Content = MiningStructureColumnContents.Key; column.IsKey = true; // Add data binding to the column column.KeyColumns.Add(strCaseTableName, name); // Add the column to the mining structure objMiningStructure.Columns.Add(column); break; case "ProfileBasicId": case "JobPostingId": case "UserId": case "JobTitle": case "JobName": case "CompanyId": case "CompanyName": //column.Type = MiningStructureColumnTypes.Text; //column.Content = MiningStructureColumnContents.Discrete; break; case "IsApproved": default: column.Type = MiningStructureColumnTypes.Boolean; column.Content = MiningStructureColumnContents.Discrete; // Add data binding to the column column.KeyColumns.Add(strCaseTableName, name); // Add the column to the mining structure objMiningStructure.Columns.Add(column); break; } } MiningModel objMiningModel = objMiningStructure.CreateMiningModel(true, StringEncode(strCaseTableName)); //MiningModel objMiningModel = objMiningStructure.MiningModels.Add(objMiningStructure.MiningModels.GetNewName(strMiningStructureName)); objMiningModel.Algorithm = MiningModelAlgorithms.MicrosoftDecisionTrees; objMiningModel.AllowDrillThrough = true; objMiningModel.AlgorithmParameters.Add("SCORE_METHOD", dtParams.SCORE_METHOD); objMiningModel.AlgorithmParameters.Add("COMPLEXITY_PENALTY", dtParams.COMPLEXITY_PENALTY); objMiningModel.AlgorithmParameters.Add("SPLIT_METHOD", dtParams.SPLIT_METHOD); objMiningModel.AlgorithmParameters.Add("MAXIMUM_INPUT_ATTRIBUTES", dtParams.MAXIMUM_INPUT_ATTRIBUTES); objMiningModel.AlgorithmParameters.Add("MAXIMUM_OUTPUT_ATTRIBUTES", dtParams.MAXIMUM_OUTPUT_ATTRIBUTES); objMiningModel.AlgorithmParameters.Add("MINIMUM_SUPPORT", dtParams.MINIMUM_SUPPORT); int i = 0; foreach(MiningModelColumn col in objMiningModel.Columns) { switch (col.Name) { case "IsApproved": objMiningModel.Columns[i].Usage = "Predict"; break; case "ID": objMiningModel.Columns[i].Usage = "Key"; break; default: objMiningModel.Columns[i].Usage = "Input"; break; } ++i; } //objMiningModel.Update(UpdateOptions.ExpandFull); objMiningStructure.Update(UpdateOptions.ExpandFull); Console.WriteLine("Processing mining model " + objMiningStructure.Name + "..."); objMiningModel.Process(ProcessType.ProcessFull); Console.WriteLine("Process " + objMiningStructure.Name + " finished!"); return objMiningStructure; } catch (Exception ex) { throw new Exception("Error in Creating a Mining structure - GenerateMiningStructure. Error Message -> " + ex.Message); } }