//Below subroutine will return a data table with results. It takes the DB connection string and query as input protected System.Data.DataTable GetDataTable(string ConnString, string query) { System.Data.DataTable dtOlap = new System.Data.DataTable(); //Connect to Analysis Server AdomdConnection conn = new AdomdConnection(ConnString); System.Diagnostics.Debug.WriteLine(conn.ConnectionString); try { conn.Open(); //Create adomd command using connection and MDX query AdomdCommand cmd = new AdomdCommand(query, conn); AdomdDataAdapter adr = new AdomdDataAdapter(cmd); adr.Fill(dtOlap); } catch (InvalidCastException e) { Response.Write("Access denied on " + ConnString); } finally { //Close DB Connection conn.Close(); } return dtOlap; }
public TableTripCategories GetData() { TableTripCategories model = new TableTripCategories(); // hierarchy properly defined within report List <BikeTable> hierarchy = new List <BikeTable>(); hierarchy.Add(new TableSubscriber()); hierarchy.Add(new TableHour2()); DataSet ds = new DataSet(); using (AdomdConnection conn = new AdomdConnection("Data Source=miranda;Initial Catalog=bikesMD2")) { conn.Open(); using (AdomdCommand cmd = new AdomdCommand(CommandText(), conn)) { AdomdDataAdapter adapter = new AdomdDataAdapter(cmd); adapter.Fill(ds); } conn.Close(); } PopulateModel(hierarchy, model, ds); return(model); }
public void GetData() { DataSet ds = new DataSet(); using (AdomdConnection conn = new AdomdConnection("Data Source=miranda;Initial Catalog=bikesMD2")) { conn.Open(); using (AdomdCommand cmd = new AdomdCommand(CommandText(), conn)) { AdomdDataAdapter adapter = new AdomdDataAdapter(cmd); adapter.Fill(ds); } conn.Close(); } // Single query returns one table DataTable dt = ds.Tables[0]; // read column names to use to select correct fields for data // TBD Inspect here to find column names for pivoted Dayname-bikes Dictionary <string, int> colnbr = new Dictionary <string, int>(); foreach (DataColumn dc in dt.Columns) { colnbr.Add(dc.ColumnName, dc.Ordinal); } foreach (DataRow dr in dt.Rows) { UpdateListForDR(dr, colnbr); } }
//Obtener Nombre de Clientes public DataTable GetClientsQueryMdx() { string dimension = ""; DataTable dataTable = new DataTable(); dimension = "[Dim Cliente].[Dim Cliente Nombre]"; List <ChartDataPie> LstChartDataPie = new List <ChartDataPie>(); var mdxQuery = $@" SELECT NON EMPTY {{ [Measures].[Fact Ventas Netas] }} ON COLUMNS, NON EMPTY {{ ( {dimension}.Children) }} ON ROWS FROM [DWH Northwind]"; using (AdomdConnection cnn = new AdomdConnection($@"Provider=MSOLAP; Data Source=localhost;Catalog=Cubo811; User ID=sa; Password = daniel; Persist Security Info = True; Impersonation Level = Impersonate")) { AdomdDataAdapter adomdDataAdapter; cnn.Open(); using (AdomdCommand cmd = new AdomdCommand(mdxQuery, cnn)) { adomdDataAdapter = new AdomdDataAdapter(cmd); adomdDataAdapter.Fill(dataTable); dataTable.Columns[0].ColumnName = "ClientesNombre"; dataTable.Columns[1].ColumnName = "FactVentas"; } } return(dataTable); }
public DataSet ExecuteDataSet(String query, ref String sessionId) { if (!String.IsNullOrWhiteSpace(_connectionString)) { using (AdomdConnection _connect = new AdomdConnection(_connectionString)) { try { using (AdomdDataAdapter adapter = new AdomdDataAdapter(query, _connect)) { DataSet ds = new DataSet(); adapter.Fill(ds); foreach (DataTable tbl in ds.Tables) { foreach (DataColumn dc in tbl.Columns) { dc.ColumnName = Helpers.GetDimFieldShortName(dc.ColumnName); } } return(ds); } } catch (Exception ex) { if (_connect.State == ConnectionState.Open) { _connect.Close(); } throw ex; } } } return(null); }
private static string GetDataFromAzureAnalysisService(string query) { //Grab the token //Get servername from Azure Analysis Service (Overview) resource //Format: asazure://.asazure.windows.net/ var serverName = "asazure://westus.asazure.windows.net/agghactice"; var token = GetAccessToken("https://westus.asazure.windows.net"); var connectionString = $"Provider=MSOLAP;Data Source={serverName};Initial Catalog=databasename;User ID=;Password={token};Persist Security Info=True;Impersonation Level=Impersonate"; DataTable _dt = new DataTable(); string JSONString_result = string.Empty; try { //read data from AAS AdomdConnection connection = new AdomdConnection(connectionString); connection.Open(); using (AdomdDataAdapter ad = new AdomdDataAdapter(query, connection)) { ad.Fill(_dt); if (_dt.Rows.Count > 0) { JSONString_result = JsonConvert.SerializeObject(_dt); } } } catch (Exception ex) { string ss = ex.Message.ToString(); Console.WriteLine(ex.Message.ToString()); } return(JSONString_result); }
public DataSet ExecuteDataSet(String query, ref String sessionId) { if (!String.IsNullOrWhiteSpace(_connectionString)) { using (AdomdConnection _connect = new AdomdConnection(_connectionString)) { try { using (AdomdDataAdapter adapter = new AdomdDataAdapter(query, _connect)) { DataSet ds = new DataSet(); adapter.Fill(ds); foreach (DataTable tbl in ds.Tables) { foreach (DataColumn dc in tbl.Columns) { dc.ColumnName = Helpers.GetDimFieldShortName(dc.ColumnName); } } return ds; } } catch (Exception ex) { if (_connect.State == ConnectionState.Open) { _connect.Close(); } throw ex; } } } return null; }
public void GetData(TripsByDistDurCatRow distdurparm) { DataSet ds = new DataSet(); using (AdomdConnection conn = new AdomdConnection("Data Source=miranda;Initial Catalog=bikesMD2")) { conn.Open(); using (AdomdCommand cmd = new AdomdCommand(CommandText(), conn)) { cmd.Parameters.Add(new AdomdParameter("StationPairDistanceMileCategories", distdurparm.DistanceCategory.MemberUniqueName)); cmd.Parameters.Add(new AdomdParameter("TripCatTripCategory", distdurparm.DurationCategory.MemberUniqueName)); cmd.Parameters.Add(new AdomdParameter("StationPairDistanceQtrMiles", distdurparm.DistanceQtrMi.MemberUniqueName)); AdomdDataAdapter adapter = new AdomdDataAdapter(cmd); adapter.Fill(ds); } conn.Close(); } // Single query returns one table DataTable dt = ds.Tables[0]; // read column names to use to select correct fields for data // TBD Inspect here to find column names for pivoted Dayname-bikes Dictionary <string, int> colnbr = new Dictionary <string, int>(); foreach (DataColumn dc in dt.Columns) { colnbr.Add(dc.ColumnName, dc.Ordinal); } foreach (DataRow dr in dt.Rows) { TripsByWeekdayRow oneresult = new TripsByWeekdayRow(distdurparm, dr, colnbr); Add(oneresult); } }
public virtual DataSet Execute(out float elapsedSec) { // Open the connection using (var connection = new AdomdConnection()) { var connectionString = command.Connection.ConnectionString; try { connection.ConnectionString = connectionString; } catch (ArgumentException ex) { throw new ConnectionException(ex, connectionString); } //TODO //try // {connection.Open();} //catch (AdomdException ex) // {throw new ConnectionException(ex);} Trace.WriteLineIf(NBiTraceSwitch.TraceVerbose, command.CommandText); foreach (AdomdParameter param in command.Parameters) { Trace.WriteLineIf(NBiTraceSwitch.TraceVerbose, string.Format("{0} => {1}", param.ParameterName, param.Value)); } // capture time before execution DateTime timeBefore = DateTime.Now; command.Connection = connection; var adapter = new AdomdDataAdapter(command); var ds = new DataSet(); adapter.SelectCommand.CommandTimeout = 0; try { adapter.Fill(ds); } catch (AdomdConnectionException ex) { throw new ConnectionException(ex, connectionString); } catch (AdomdErrorResponseException ex) { if (!ex.Message.StartsWith("Timeout expired.")) { throw new ConnectionException(ex, connectionString); } else { throw new CommandTimeoutException(ex, adapter.SelectCommand); } } // capture time after execution DateTime timeAfter = DateTime.Now; // setting query runtime elapsedSec = (float)timeAfter.Subtract(timeBefore).TotalSeconds; Trace.WriteLineIf(NBiTraceSwitch.TraceInfo, string.Format("Time needed to execute query [ADOMD]: {0}", timeAfter.Subtract(timeBefore).ToString(@"d\d\.hh\h\:mm\m\:ss\s\ \+fff\m\s"))); return(ds); } }
/// <summary> /// Initializes a new instance of the <see cref="AdomdDataAdapter"/> class. /// </summary> /// <param name="innerAdapter">The AdomdDataAdapter to encapsulate.</param> public AdomdDataAdapterWrapper(AdomdDataAdapter innerAdapter) { if (innerAdapter == null) { throw new ArgumentNullException("innerAdapter"); } _innerAdapter = innerAdapter; }
private void ExecuteOlapQuery() { OlapManager objOlapManager = new OlapManager(); CellSet objCellSet = objOlapManager.GetQueryResult(TextBoxQuery.Text); AdomdDataAdapter objDataAdaptor = new AdomdDataAdapter(objOlapManager.ObjCommand); AdomdDataReader objDataReader = objOlapManager.ObjCommand.ExecuteReader(CommandBehavior.CloseConnection); DataTable objTable = new DataTable(); DataColumn myColumn = new DataColumn(); DataRow myRow = null; DataTable objSchemaTable = objDataReader.GetSchemaTable(); List <string> lMeta = new List <string>(); // init meta values for (int i = 0; i < objSchemaTable.Rows.Count; i++) { lMeta.Add(objSchemaTable.Rows[i][0].ToString()); } // add columns and column captions for (int i = 0; i < objDataReader.FieldCount; i++) { myColumn = new DataColumn(lMeta[i]); objTable.Columns.Add(myColumn); } // output the rows in the DataReader while (objDataReader.Read()) { // new row myRow = objTable.NewRow(); // set the row values for (int i = 0; i < objDataReader.FieldCount; i++) { myRow[i] = objDataReader[i]; } // add row to the table objTable.Rows.Add(myRow); } // close reader objDataReader.Close(); GridViewData.DataSource = objTable; GridViewData.DataBind(); // export TEST //ExportDataTableToExcel(objTable); objOlapManager.CloseConnection(); // load the main table data this.ObjMainTable = objTable; Session.Add("queryData", objMainTable); }
/// <summary> /// Retrieves a dataset from an OLAP databse using the specified MDX query. /// </summary> /// <param name="conn">Opened connection to the database</param> /// <param name="mdxQuery">MDX query</param> /// <returns></returns> public static DataSet GetData(AdomdConnection conn, string mdxQuery) { using (var ds = new DataSet()) using (AdomdCommand cmd = new AdomdCommand(mdxQuery, conn)) using (AdomdDataAdapter da = new AdomdDataAdapter(cmd)) { da.Fill(ds); return(ModifyDataType(ds, typeof(System.Decimal))); } }
public static DataTable ExecuteForDataTable(string connectionString, string commandText, CancellationToken cancellationToken = default(CancellationToken)) { Func <IDbCommand, DataTable> function = (command) => { var table = new DataTable(); using (var adapter = new AdomdDataAdapter(command as AdomdCommand)) adapter.Fill(table); return(table); }; return(Execute <DataTable>(connectionString, commandText, cancellationToken, function)); }
public DataTable GetDataTable(string query) { using (AdomdConnection con = new AdomdConnection(asConnectionString)) { con.Open(); using (AdomdDataAdapter adapter = new AdomdDataAdapter(query, con)) { DataTable dataTable = new DataTable(); adapter.Fill(dataTable); return(dataTable); } } }
public DataTable GetDataTable(string mdx) { using (var command = this.prepareCommand(mdx)) { var table = new DataTable("CubeResults"); using (var daptor = new AdomdDataAdapter(command)) { daptor.Fill(table); } return(table); } }
public ViewWeekofyearGivenDistanceDurationCategories GetData(string DistanceCategory, string DurationCategory) { ViewWeekofyearGivenDistanceDurationCategories model = new ViewWeekofyearGivenDistanceDurationCategories(DistanceCategory, DurationCategory); List <BikeTable> hierarchy = new List <BikeTable>(); hierarchy.Add(new TableWeekday()); hierarchy.Add(new TableSubscriber()); hierarchy.Add(new TableHour2()); string CommandText = @"SELECT NON EMPTY { [Measures].[Bikes] } ON COLUMNS, NON EMPTY { ([Time Table].[Nameofday].[Nameofday].ALLMEMBERS * [Time Table].[Weekofyear].[Weekofyear].ALLMEMBERS * [Subscribers].[Subscriber Info].[Subscriber Info].ALLMEMBERS * [Time Table].[Hour2ofday].[Hour2ofday].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( { [Direction].[Direction].&[A-B] } ) ON COLUMNS FROM ( SELECT ( STRTOSET(@StationPairDistanceMileCategories, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@TripCatTripCategory, CONSTRAINED) ) ON COLUMNS FROM [Bikeshare]))) WHERE ( IIF( STRTOSET(@TripCatTripCategory, CONSTRAINED).Count = 1, STRTOSET(@TripCatTripCategory, CONSTRAINED), [TripCat].[Trip Category].currentmember ), IIF( STRTOSET(@StationPairDistanceMileCategories, CONSTRAINED).Count = 1, STRTOSET(@StationPairDistanceMileCategories, CONSTRAINED), [Station Pair Distance].[Mile Categories].currentmember ), [Direction].[Direction].&[A-B] )"; DataSet ds = new DataSet(); using (AdomdConnection conn = new AdomdConnection("Data Source=miranda;Initial Catalog=bikesMD2")) { conn.Open(); using (AdomdCommand cmd = new AdomdCommand(CommandText, conn)) { cmd.Parameters.Add(new AdomdParameter("StationPairDistanceMileCategories", DistanceCategory)); cmd.Parameters.Add(new AdomdParameter("TripCatTripCategory", DurationCategory)); AdomdDataAdapter adapter = new AdomdDataAdapter(cmd); adapter.Fill(ds); } conn.Close(); } PopulateModel(hierarchy, model, ds); return(model); }
/// <summary> /// Executes the query string and returns the result in a DataTable object. /// </summary> /// <param name="mdxQuery">The string mdx query.</param> /// <returns></returns> public DataTable Execute(string mdxQuery) { using (var connection = new AdomdConnection(this.ConnectionString)) { using (var command = new AdomdCommand(mdxQuery, connection)) { connection.Open(); using (var dapter = new AdomdDataAdapter(command)) { var table = new DataTable(connection.Database); dapter.Fill(table); return(table); } } } }
public DataTable GetDataTable(string queryMdx) { DataTable dataTable = new DataTable(); using (AdomdConnection cnn = new AdomdConnection($@"Provider=MSOLAP; Data Source=localhost;Catalog=CuboNorthwindISSC811; User ID=sa; Password = roverto; Persist Security Info = True; Impersonation Level = Impersonate")) { AdomdDataAdapter adomdDataAdapter; cnn.Open(); using (AdomdCommand cmd = new AdomdCommand(queryMdx, cnn)) { adomdDataAdapter = new AdomdDataAdapter(cmd); adomdDataAdapter.Fill(dataTable); } } return(dataTable); }
public DataTable ExecuteDaxQueryDataTable(string query) { _runningCommand = _adomdConn.CreateCommand(); _runningCommand.CommandType = CommandType.Text; _runningCommand.CommandText = query; var da = new AdomdDataAdapter(_runningCommand); var dt = new DataTable("DAXResult"); if (_adomdConn.State != ConnectionState.Open) { _adomdConn.Open(); } da.Fill(dt); _runningCommand = null; return(dt); }
public static List<object> GetData(string command) { DataSet ds = new DataSet(); using (AdomdConnection conn = new AdomdConnection("Data Source=miranda;Initial Catalog=bikesMD2")) { conn.Open(); using (AdomdCommand cmd = new AdomdCommand(command, conn)) { AdomdDataAdapter adapter = new AdomdDataAdapter(cmd); adapter.Fill(ds); } conn.Close(); } return ConvertDataTableToObjectList(ds.Tables[0]); }
public ViewWeekofyearGivenDistanceDurationCategories GetData(string DistanceCategory, string DurationCategory) { ViewWeekofyearGivenDistanceDurationCategories model = new ViewWeekofyearGivenDistanceDurationCategories(DistanceCategory, DurationCategory); List<BikeTable> hierarchy = new List<BikeTable>(); hierarchy.Add(new TableWeekday()); hierarchy.Add(new TableSubscriber()); hierarchy.Add(new TableHour2()); string CommandText = @"SELECT NON EMPTY { [Measures].[Bikes] } ON COLUMNS, NON EMPTY { ([Time Table].[Nameofday].[Nameofday].ALLMEMBERS * [Time Table].[Weekofyear].[Weekofyear].ALLMEMBERS * [Subscribers].[Subscriber Info].[Subscriber Info].ALLMEMBERS * [Time Table].[Hour2ofday].[Hour2ofday].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( { [Direction].[Direction].&[A-B] } ) ON COLUMNS FROM ( SELECT ( STRTOSET(@StationPairDistanceMileCategories, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@TripCatTripCategory, CONSTRAINED) ) ON COLUMNS FROM [Bikeshare]))) WHERE ( IIF( STRTOSET(@TripCatTripCategory, CONSTRAINED).Count = 1, STRTOSET(@TripCatTripCategory, CONSTRAINED), [TripCat].[Trip Category].currentmember ), IIF( STRTOSET(@StationPairDistanceMileCategories, CONSTRAINED).Count = 1, STRTOSET(@StationPairDistanceMileCategories, CONSTRAINED), [Station Pair Distance].[Mile Categories].currentmember ), [Direction].[Direction].&[A-B] )"; DataSet ds = new DataSet(); using (AdomdConnection conn = new AdomdConnection("Data Source=miranda;Initial Catalog=bikesMD2")) { conn.Open(); using (AdomdCommand cmd = new AdomdCommand(CommandText, conn)) { cmd.Parameters.Add(new AdomdParameter("StationPairDistanceMileCategories", DistanceCategory)); cmd.Parameters.Add(new AdomdParameter("TripCatTripCategory", DurationCategory)); AdomdDataAdapter adapter = new AdomdDataAdapter(cmd); adapter.Fill(ds); } conn.Close(); } PopulateModel(hierarchy, model, ds); return model; }
public static List <object> GetData(string command) { DataSet ds = new DataSet(); using (AdomdConnection conn = new AdomdConnection("Data Source=miranda;Initial Catalog=bikesMD2")) { conn.Open(); using (AdomdCommand cmd = new AdomdCommand(command, conn)) { AdomdDataAdapter adapter = new AdomdDataAdapter(cmd); adapter.Fill(ds); } conn.Close(); } return(ConvertDataTableToObjectList(ds.Tables[0])); }
public DataTable ExecuteDaxQueryAsync(string query) { AdomdCommand cmd = _adomdConn.CreateCommand(); cmd.CommandType = CommandType.Text; cmd.CommandText = query; var da = new AdomdDataAdapter(cmd); var dt = new DataTable("DAXResult"); if (_adomdConn.State != ConnectionState.Open) { _adomdConn.Open(); } da.Fill(dt); return(dt); }
public static DataTable ExecuteDataTable(AdomdConnection conn, string CommandText) { DataTable dt = null; try { AdomdCommand command = new AdomdCommand(); SetCommand(conn, command, CommandText); AdomdDataAdapter da = new AdomdDataAdapter(command); dt = new DataTable(); da.Fill(dt); } catch (Exception ex) { throw; } return dt; }
public static DataTable ExecuteDataTable(AdomdConnection conn, string CommandText) { DataTable dt = null; try { AdomdCommand command = new AdomdCommand(); SetCommand(conn, command, CommandText); AdomdDataAdapter da = new AdomdDataAdapter(command); dt = new DataTable(); da.Fill(dt); } catch (Exception ex) { throw; } return(dt); }
private static DataTable ExecuteMdx(string query, List <AdomdParameter> parameters = null) { DataTable dataTable = new DataTable(); using (AdomdConnection sqlCon = new AdomdConnection(mdxConnectionString)) { sqlCon.Open(); string processedQuery = MdxQueryFormatter.GetQueryWithParameters(parameters, query); AdomdCommand mdxCmd = new AdomdCommand(processedQuery, sqlCon); using (AdomdDataAdapter dataAdapter = new AdomdDataAdapter(mdxCmd)) { dataAdapter.Fill(dataTable); } } return(dataTable); }
/// <summary> /// Return MDX query results as a DataSet /// </summary> /// <param name="mdx"></param> /// <returns></returns> internal DataSet GetData(string mdx) { var builder = new SqlConnectionStringBuilder(); builder.DataSource = Server; builder.InitialCatalog = Catalog; DataSet ds = null; using (var connection = new AdomdConnection(builder.ToString())) { connection.Open(); ds = new DataSet(); using (var adaptor = new AdomdDataAdapter(mdx, connection)) { adaptor.Fill(ds); } } return(ds); }
public ViewWeekofyearGivenDistanceDurationCategories(string DistanceCategoryParm, string DurationCategoryParm) { string CommandText = @"SELECT NON EMPTY { [Measures].[Bikes] } ON COLUMNS, NON EMPTY { ([Station Pair Distance].[Mile Categories].[Mile Categories].ALLMEMBERS * [TripCat].[Trip Category].[Trip Category].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOSET(@TripCatTripCategory, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@StationPairDistanceMileCategories, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( { [Direction].[Direction].&[A-B] } ) ON COLUMNS FROM [Bikeshare]) WHERE ( [Direction].[Direction].&[A-B] )))"; DataSet ds = new DataSet(); using (AdomdConnection conn = new AdomdConnection("Data Source=miranda;Initial Catalog=bikesMD2")) { conn.Open(); using (AdomdCommand cmd = new AdomdCommand(CommandText, conn)) { cmd.Parameters.Add(new AdomdParameter("StationPairDistanceMileCategories", DistanceCategoryParm)); cmd.Parameters.Add(new AdomdParameter("TripCatTripCategory", DurationCategoryParm)); AdomdDataAdapter adapter = new AdomdDataAdapter(cmd); adapter.Fill(ds); } conn.Close(); } DataTable dt = ds.Tables[0]; Dictionary <string, int> colnbr = new Dictionary <string, int>(); foreach (DataColumn dc in dt.Columns) { colnbr.Add(dc.ColumnName, dc.Ordinal); } foreach (DataRow dr in dt.Rows) { DistanceCategory = dr[colnbr["[Station Pair Distance].[Mile Categories].[Mile Categories].[MEMBER_CAPTION]"]].ToString(); DurationCategory = dr[colnbr["[TripCat].[Trip Category].[Trip Category].[MEMBER_CAPTION]"]].ToString(); Bikes = Convert.ToSingle(dr[colnbr["[Measures].[Bikes]"]]); } }
public TableTripCategories GetData() { TableTripCategories model = new TableTripCategories(); // hierarchy properly defined within report List<BikeTable> hierarchy = new List<BikeTable>(); hierarchy.Add(new TableSubscriber()); hierarchy.Add(new TableHour2()); DataSet ds = new DataSet(); using (AdomdConnection conn = new AdomdConnection("Data Source=miranda;Initial Catalog=bikesMD2")) { conn.Open(); using (AdomdCommand cmd = new AdomdCommand(CommandText(), conn)) { AdomdDataAdapter adapter = new AdomdDataAdapter(cmd); adapter.Fill(ds); } conn.Close(); } PopulateModel(hierarchy, model, ds); return (model); }
private void ExecuteCommandDataSet() { string sMDX = txtMDX.Text; try { AdomdCommand oCmd = new AdomdCommand(sMDX, moConn); AdomdDataAdapter da = new AdomdDataAdapter(oCmd); //DataSet ds = new DataSet(); da.Fill(dataSet1, "olap"); grdResults.LoadData(dataSet1.Tables["olap"]); //grdResults.DataSource = dataSet1.Tables["olap"]; } catch (AdomdException ex) { MessageBox.Show(ex.Message); } }
// execute SSAS/OLAP command and return a R language compliant dataframe script representing the result data. static string SSASConnectAndExec(string mdx) { AdomdConnection conn = new AdomdConnection(_connStr); // string mdx = "SELECT {[Measures].[Page Views],[Measures].[Daily Unique Visitors]} ON 0,{[Dim Date].[Month].[Month]} ON ROWS FROM [Hearst POC];"; AdomdCommand cmd = new AdomdCommand(mdx, conn); AdomdDataAdapter da = new AdomdDataAdapter(cmd); DataTable dt = new DataTable(); try { conn.Open(); } catch (AdomdException e) { Console.WriteLine(">> Failed to open SSAS Connection"); log.Error("Failed to open SSAS connection for " + _connStr, e); return(null); } try { da.Fill(dt); } catch (AdomdException e) { Console.WriteLine(">> Failed to fill the datatable"); log.Error("Failed to fill datatable for query " + mdx, e); return(null); } // Clean up ado.net objects da.Dispose(); conn.Close(); // process datatable and create R language compliant dataframe object as script return(DataTableToRDataFrame(dt)); }
/// <summary> /// Execute an MDX command via a AdomdDataAdapter /// </summary> private void ExecuteCommandDataSet() { string sMDX = GetCommand(); try { AdomdCommand oCmd = new AdomdCommand(sMDX, moConn); AdomdDataAdapter da = new AdomdDataAdapter(oCmd); DataTable dt = new DataTable(); da.Fill(dt); grdResults.LoadData(dt); grdResults.AutoSize(); } catch (AdomdException ex) { MessageBox.Show(ex.Message); } catch (InvalidOperationException exo) { MessageBox.Show(exo.Message); } }
public ReportUsingLinq(string title) { string CommandText = @"SELECT NON EMPTY { [Measures].[Bikes] } ON COLUMNS, NON EMPTY { ([Station Pair Distance].[Mile Categories].[Mile Categories].ALLMEMBERS * [TripCat].[Trip Category].[Trip Category].ALLMEMBERS * [Subscribers].[Subscriber Info].[Subscriber Info].ALLMEMBERS * [Time Table].[Hour2ofday].[Hour2ofday].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( { [Direction].[Direction].&[A-B] } ) ON COLUMNS FROM [Bikeshare]) WHERE ( [Direction].[Direction].&[A-B] )"; DataSet ds = new DataSet(); using (AdomdConnection conn = new AdomdConnection("Data Source=miranda;Initial Catalog=bikesMD2")) { conn.Open(); using (AdomdCommand cmd = new AdomdCommand(CommandText, conn)) { AdomdDataAdapter adapter = new AdomdDataAdapter(cmd); adapter.Fill(ds); } conn.Close(); } // Linq using nested groups to build hierarchy, does this work? // if it works, can intellisense in Razor figure it out? var HourBikes = (from row in ds.Tables[0].AsEnumerable() select new //LinqResultTable() { DistanceCategory = row.Field<string>("[Station Pair Distance].[Mile Categories].[Mile Categories].[MEMBER_UNIQUE_NAME]"), DurationCategory = row.Field<string>("[TripCat].[Trip Category].[Trip Category].[MEMBER_UNIQUE_NAME]"), Subscriber = row.Field<string>("[Subscribers].[Subscriber Info].[Subscriber Info].[MEMBER_UNIQUE_NAME]"), Hour2 = row.Field<string>("[Time Table].[Hour2ofday].[Hour2ofday].[MEMBER_UNIQUE_NAME]"), DistanceCategoryCaption = row.Field<string>("[Station Pair Distance].[Mile Categories].[Mile Categories].[MEMBER_CAPTION]"), DurationCategoryCaption = row.Field<string>("[TripCat].[Trip Category].[Trip Category].[MEMBER_CAPTION]"), SubscriberCaption = row.Field<string>("[Subscribers].[Subscriber Info].[Subscriber Info].[MEMBER_CAPTION]"), Hour2Caption = row.Field<string>("[Time Table].[Hour2ofday].[Hour2ofday].[MEMBER_UNIQUE_NAME]"), Bikes = (row.Field<double>("[Measures].[Bikes]")).ToString() }); // .ToArray(); //forces immediate execution, not needed. model = (from row in HourBikes group row by new { DistanceCategory = row.DistanceCategory, DurationCategory = row.DurationCategory, DistanceCategoryCaption = row.DistanceCategoryCaption, DurationCategoryCaption = row.DurationCategoryCaption } into Cat select new { DistanceCategory = Cat.Key.DistanceCategory, DurationCategory = Cat.Key.DurationCategory, DistanceCategoryCaption = Cat.Key.DistanceCategoryCaption, DurationCategoryCaption = Cat.Key.DurationCategoryCaption, // BikeValueList = (Cat.Select(row => row.Bikes)), // generates list of string values that can be summed later TotalBikes = (Cat.Sum(row => Convert.ToDouble(row.Bikes))).ToString(), subscribers = from srow in HourBikes where Cat.Key.DistanceCategory == srow.DistanceCategory && Cat.Key.DurationCategory == srow.DurationCategory group srow by new { Subsciber = srow.Subscriber, SubscriberCaption = srow.SubscriberCaption } into Scat select new { Subscriber = Scat.Key.Subsciber, SubscriberCaption = Scat.Key.SubscriberCaption, // BikeValueList = (Scat.Select(row => row.Bikes)), TotalBikes = (Scat.Sum(row => Convert.ToDouble(row.Bikes))).ToString("#,#.##"), Hour2 = from hrow in HourBikes where Cat.Key.DistanceCategory == hrow.DistanceCategory && Cat.Key.DurationCategory == hrow.DurationCategory && Scat.Key.Subsciber == hrow.Subscriber group hrow by new { Hour2 = hrow.Hour2, Hour2Caption = hrow.Hour2Caption } into Hcat select new { Hour2 = Hcat.Key.Hour2, Hour2Caption = Hcat.Key.Hour2Caption, // BikeValueList = (Hcat.Select(row => row.Bikes)), TotalBikes = (Hcat.Sum(row => Convert.ToDouble(row.Bikes))).ToString() } } }).AsEnumerable().Select(c=>c.ToExpando()); Title = title; // return model; }
/// <summary> /// Executes the command. /// </summary> /// <typeparam name="T">Supported types: XmlReader, CellSet, AdomdDataReader or Int</typeparam> /// <param name="conn">The conn.</param> /// <param name="command">The command.</param> /// <param name="parameters">The parameters.</param> /// <returns>An XmlReader, CellSet, AdomdDataReader or Int</returns> public static T ExecuteCommand <T>(AdomdConnection conn, string command, params AdomdParameter[] parameters) { if (conn == null) { throw new ArgumentNullException("conn"); } if (string.IsNullOrEmpty(command)) { throw new ArgumentNullException("command"); } LogQuery(command, parameters); using (AdomdCommand cmd = GetCommand(conn, command, parameters)) { if (conn.State != System.Data.ConnectionState.Open) { conn.Open(); } object result = null; var type = typeof(T); if (type == typeof(XmlReader)) { result = cmd.ExecuteXmlReader(); } else if (type == typeof(CellSet)) { result = cmd.ExecuteCellSet(); } else if (type == typeof(AdomdDataReader)) { result = cmd.ExecuteReader(); } else if (type == typeof(int)) { result = cmd.ExecuteNonQuery(); } else if (type == typeof(DataTable)) { // Outputs all members of a parent-child hierarchy in a single table column in the flattened result (http://msdn.microsoft.com/en-us/library/ms186627.aspx) cmd.Properties.Add("DbpropMsmdFlattened2", true); using (var adapter = new AdomdDataAdapter(cmd)) { var ds = new DataSet(); adapter.Fill(ds); if (ds.Tables.Count != 0) { result = ds.Tables[0]; } } } else { throw new ApplicationException(string.Format("Invalid ExecuteCommand result type: '{0}'", type.Name)); } return((T)result); } }
// criar principal // dat permissão no aas ao principal com o formato app:<aapID>@<tenantID> static void Main(string[] args) { string tenantID = ""; string clientId = ""; string clientSecret = ""; string resourceID = ""; string aasModel = ""; string query = @""; if (args.Length == 6) { tenantID = args[0]; clientId = args[1]; clientSecret = args[2]; resourceID = args[3]; aasModel = args[4]; query = args[5].Replace("\"", ""); } string tk = ""; bool getToken = true; if (File.Exists(@".\token")) { string[] lines = System.IO.File.ReadAllLines(@".\token"); if (tenantID == lines[0] && clientId == lines[1]) { DateTime dttk = (new DateTime(long.Parse(lines[2]))) - (new TimeSpan(0, 0, 10)); if (dttk > DateTime.Now) { tk = lines[3]; getToken = false; } } } if (getToken) { AuthenticationContext authContext = new AuthenticationContext("https://login.windows.net/" + tenantID); ClientCredential cc = new ClientCredential(clientId, clientSecret); AuthenticationResult token = authContext.AcquireTokenAsync("https://" + resourceID, cc).Result; tk = token.AccessToken; string[] nf = new string[4]; nf[0] = tenantID; nf[1] = clientId; nf[2] = token.ExpiresOn.Ticks.ToString(); nf[3] = tk; System.IO.File.WriteAllLines(@".\token", nf); } var connectionString = $"Provider=MSOLAP;Data Source=asazure://" + resourceID + "/" + aasModel + ";Password="******";Persist Security Info=True; Impersonation Level=Impersonate;"; var ssasConnection = new AdomdConnection(connectionString); AdomdCommand ccc = new AdomdCommand(query, ssasConnection); AdomdDataAdapter objDataAdapter = new AdomdDataAdapter(ccc); System.Data.DataTable dt = new System.Data.DataTable(); DateTime dtN = DateTime.Now; ssasConnection.Open(); DateTime dtNq = DateTime.Now; objDataAdapter.Fill(dt); TimeSpan ts = DateTime.Now - dtN; string r = (DateTime.Now - dtN).Milliseconds.ToString() + " " + (DateTime.Now - dtNq).Milliseconds.ToString() + " " + dt.Rows.Count.ToString(); ssasConnection.Close(); Console.WriteLine(r); }
// whereOk = only one dimension in rows public static DataTable GetDataHelper(string dim, string dim2, string rows, List <KeyFigure> keyFigures, Dictionary <Dimension, string[]> restrictions, bool zeroSkip, bool zeroskipstock, bool whereOk, bool skipRows, bool nonEmpty, string connectionString, int userID, SqlConnection SQLServer) { AdomdConnection con = new AdomdConnection(); con.ConnectionString = connectionString; Helpers.ETLHelpers.Debug("Using ADOMD String : " + con.ConnectionString); con.Open(); List <string> kfs = keyFigures.ConvertAll(x => x.dbName); string columns = "{" + string.Join(", ", kfs.ToArray()) + "}"; string where = ""; string withMember = ""; List <string> whereClauses = new List <string>(); List <string> idsAdded = new List <string>(); if (whereOk) { Debug.Assert(dim2 == ""); Dictionary <string, List <string> > values = new Dictionary <string, List <string> >(); bool restrictionOnDimension = false; foreach (KeyValuePair <Dimension, string[]> r in restrictions) { if (r.Key != null) { if (dim == r.Key.dbName && r.Value.Length > 0 && !skipRows) { if (!restrictionOnDimension) { restrictionOnDimension = true; rows = "{"; } } List <string> whereClauseValues = new List <string>(); foreach (string v in r.Value) { if (v != null && v != I18n.GetString("latest")) { if (dim == r.Key.dbName && !skipRows && nonEmpty) { rows += string.Format("NonEmpty({0}.&[{1}]), ", r.Key.dbName, v); } else if (dim == r.Key.dbName) { rows += string.Format("{0}.&[{1}], ", r.Key.dbName, v); } else { whereClauseValues.Add(string.Format("{0}.&[{1}]", r.Key.dbName, v)); } } } if (whereClauseValues.Count > 0) { whereClauses.Add("{" + String.Join(", ", whereClauseValues.ToArray()) + "}"); } } } if (restrictionOnDimension) { rows = rows.Substring(0, rows.Length - 2) + "}"; } } else { // if there is no restriction on the rows, then we can just use WITH MEMBER... // if there is restrictions on the rows, then we need to construct all the values we want manually bool restrictionOnRows = false; List <string> dimRestrictions = new List <string>(); List <string> dim2Restrictions = new List <string>(); foreach (KeyValuePair <Dimension, string[]> r in restrictions) { if (r.Key.dbName == dim || r.Key.dbName == dim2) { restrictionOnRows = true; } } // with can be used to restrict on several dimensions, but only ones that are not in rows foreach (KeyValuePair <Dimension, string[]> r in restrictions) { if (restrictionOnRows) { if (r.Key.dbName == dim) { foreach (string v in r.Value) { if (v != I18n.GetString("latest")) { dimRestrictions.Add(v); } } } else if (r.Key.dbName == dim2) { foreach (string v in r.Value) { if (v != I18n.GetString("latest")) { dim2Restrictions.Add(v); } } } else { if (r.Value.Length > 0) { if (withMember == "") { withMember += "WITH "; } withMember += "MEMBER " + r.Key.dbName.Substring(0, r.Key.dbName.LastIndexOf("]", r.Key.dbName.LastIndexOf("]") - 1) + 1) + "." + r.Key.id + " AS Aggregate ({"; bool inforeach = false; //MGA 24092010; week missing in r. foreach (string v in r.Value) { if (v != I18n.GetString("latest")) { inforeach = true; withMember += r.Key.dbName + ".&[" + v + "], "; } } // Helpers.debug("infoeach1 is : " + inforeach); if (inforeach) { withMember = withMember.Substring(0, withMember.Length - 2) + "}) "; } else { withMember = withMember.Substring(0, withMember.Length - 2) + "}) "; } whereClauses.Add(r.Key.id); } } } else { if (r.Value.Length > 0) { if (withMember == "") { withMember += "WITH "; } withMember += "MEMBER " + r.Key.dbName.Substring(0, r.Key.dbName.LastIndexOf("]", r.Key.dbName.LastIndexOf("]") - 1) + 1) + "." + r.Key.id + " AS Aggregate ({"; bool inforeach = false; //MGA 24092010; week missing in r. foreach (string v in r.Value) { //What happens is the only one is latest? if (v != I18n.GetString("latest")) { inforeach = true; withMember += r.Key.dbName + ".&[" + v + "], "; } } // Helpers.debug("infoeach2 is : " + inforeach); if (inforeach) { withMember = withMember.Substring(0, withMember.Length - 2) + "}) "; } else { withMember = withMember + "}) "; } whereClauses.Add(r.Key.id); } } Helpers.ETLHelpers.Debug("Rows1: " + rows); if (dimRestrictions.Count > 0 && dim2Restrictions.Count > 0) { rows = "{"; foreach (string dimRestriction in dimRestrictions) { foreach (string dim2Restriction in dim2Restrictions) { if (nonEmpty) { rows += string.Format("(NonEmpty({0}.&[{1}]), NonEmpty({2}.&[{3}])), ", dim, dimRestriction, dim2, dim2Restriction); } else { rows += string.Format("({0}.&[{1}], {2}.&[{3}]), ", dim, dimRestriction, dim2, dim2Restriction); } } } rows = rows.Substring(0, rows.Length - 2) + "}"; Helpers.ETLHelpers.Debug("Rows2: " + rows); } else if (dimRestrictions.Count > 0) { rows = "{"; foreach (string dimRestriction in dimRestrictions) { if (nonEmpty) { rows += string.Format("(NonEmpty({0}.&[{1}])), ", dim, dimRestriction); } else { rows += string.Format("({0}.&[{1}]), ", dim, dimRestriction); } } rows = rows.Substring(0, rows.Length - 2) + "}"; Helpers.ETLHelpers.Debug("Rows3: " + rows); } else if (dim2Restrictions.Count > 0) { rows = "{"; foreach (string dimRestriction in dim2Restrictions) { if (nonEmpty) { rows += string.Format("(NonEmpty({0}), NonEmpty({1}.&[{2}])), ", dim, dim2, dimRestriction); } else { rows += string.Format("({0}, {1}.&[{2}]), ", dim, dim2, dimRestriction); } } rows = rows.Substring(0, rows.Length - 2) + "}"; Helpers.ETLHelpers.Debug("Rows4: " + rows); } } } if (whereClauses.Count > 0) { where = " WHERE (" + string.Join(", ", whereClauses.ToArray()) + ")"; } if (dim.ToLower().StartsWith("[product]") && String.IsNullOrEmpty(dim2)) { //problem if we also want to see eg. sold sold qty and this is > 0 but ultimolager = 0 then this does not appear. //This does not work when filtrering on multiple dimensions (set on axis). Uncommented gives larges values. //rows = String.Format("Non Empty(FILTER({0}, [Measures].[UltimoLager_Stk] <> 0 or [Measures].[Afsætning] <> 0))", dim); } string query; if (skipRows) { query = withMember + "SELECT " + columns + " on columns FROM [" + ConnectionHandler.adomdCubeName(userID) + "]" + where; } else { query = withMember + "SELECT " + columns + " on columns, " + rows + " ON ROWS FROM [" + ConnectionHandler.adomdCubeName(userID) + "]" + where; } Helpers.ETLHelpers.Debug("ADOMD query for execution: " + query); AdomdDataAdapter adapter = new AdomdDataAdapter(query, con); DataTable table = new DataTable(); Type type = null; if (Helpers.ETLHelpers.IsTimeDimension(dim)) { type = typeof(System.DateTime); } else { type = typeof(string); } table.Columns.Add(new DataColumn(dim + ".[MEMBER_CAPTION]", type)); foreach (string x in kfs) { table.Columns.Add(new DataColumn(x, typeof(Double))); } if (!String.IsNullOrEmpty(dim2)) { if (Helpers.ETLHelpers.IsTimeDimension(dim2)) { type = typeof(System.DateTime); } else { type = typeof(string); } table.Columns.Add(new DataColumn(dim2 + ".[MEMBER_CAPTION]", type)); } adapter.Fill(table); con.Close(); // FIXME: exception handling? connection pooling? DateTime timeNow = DateTime.Now; // currency if (User.IsChainUser(userID) && User.UsesCurrency(userID)) { var conversionRate = Helpers.ETLHelpers.ConversionRate(User.Currency(userID), SQLServer); foreach (DataRow row in table.Rows) { int index = 1; foreach (KeyFigure k in keyFigures) { if (k.type == KeyFigureType.Money) { try { double val = System.Convert.ToDouble(row[index]); row[index] = val * conversionRate; } catch (InvalidCastException) { row[index] = 0; } } ++index; } } } //Helpers.debug("count {0}", table.Rows.Count); if (zeroSkip) { List <int> row_indexes = new List <int>(); int row_index = 0; var dims = 1; var dimsAtEnd = 0; if (!String.IsNullOrEmpty(dim2)) { dimsAtEnd = 1; } Helpers.ETLHelpers.Debug("ADOMD restrictions count:" + dims); Helpers.ETLHelpers.Debug("Columns count:" + table.Columns.Count); Helpers.ETLHelpers.Debug("Itemarray[0] count:" + table.Rows[0].ItemArray.Length); Type stringType = "".GetType(); foreach (DataRow row in table.Rows) { bool skip = true; for (int i = dims; i < row.ItemArray.Length - dimsAtEnd; ++i) { // this is much faster than catching exceptions Helpers.ETLHelpers.Debug("Data type : " + table.Columns[i].DataType + ", value: " + row.ItemArray[i]); if (row.ItemArray[i] == DBNull.Value || table.Columns[i].DataType == stringType) { continue; } Helpers.ETLHelpers.Debug("item array value: " + (Double)row.ItemArray[i]); try { if ((Double)row.ItemArray[i] != 0) { skip = false; Helpers.ETLHelpers.Debug(" Zeroskip, skipped = false: " + row.ItemArray[i]); break; } } catch (System.InvalidCastException) { Helpers.ETLHelpers.Debug("Invalid cast with zeroskip: " + row.ItemArray[i]); skip = false; //MGA NEW 27052013 } } if (skip) { row_indexes.Add(row_index); } ++row_index; } row_indexes.Reverse(); // otherwise the indexes don't match ;-) Helpers.ETLHelpers.Debug("tablerows count before : " + table.Rows.Count); foreach (int index in row_indexes) { Helpers.ETLHelpers.Debug("Zeroskip, should remove index : " + index); table.Rows.RemoveAt(index); } Helpers.ETLHelpers.Debug("tablerows count after : " + table.Rows.Count); } // Helpers.debug("count {0}", table.Rows.Count); return(table); }
public List <string> GetChartLabelsDataBarQueryMdx(string[] clients, string[] months, string[] years) // { List <dynamic> dlist = new List <dynamic>(); List <string> lstColumNames = new List <string>(); string SelectedClients = "", SelectedMonths = "", SelectedYears = ""; if (clients.Length >= 1) { foreach (var item in clients) { SelectedClients += $@"[Dim Cliente].[Dim Cliente Nombre].&[{item}],"; } SelectedClients = SelectedClients.Remove(SelectedClients.Length - 1); } if (months.Length >= 1) { foreach (var item in months) { SelectedMonths += $@"[Dim Tiempo].[Dim Tiempo Mes].&[{item}],"; } SelectedMonths = SelectedMonths.Remove(SelectedMonths.Length - 1); } if (years.Length >= 1) { foreach (var item in years) { SelectedYears += $@"[Dim Tiempo].[Dim Tiempo Año].&[{item}],"; } SelectedYears = SelectedYears.Remove(SelectedYears.Length - 1); } DataTable dataTable = new DataTable(); List <ChartDataPie> LstChartDataPie = new List <ChartDataPie>(); var mdxQuery = $@" SELECT NON EMPTY {{ ( [Dim Tiempo].[Dim Tiempo Año].[Dim Tiempo Año].ALLMEMBERS * [Dim Tiempo].[Dim Tiempo Mes].[Dim Tiempo Mes].ALLMEMBERS ) }} ON COLUMNS, NON EMPTY {{ ( ( [Measures].[Fact Ventas Netas], [Dim Cliente].[Dim Cliente Nombre].[Dim Cliente Nombre].ALLMEMBERS ) ) }} ON ROWS FROM ( SELECT ( {{ {SelectedYears} }}) ON COLUMNS FROM ( SELECT ( {{ {SelectedMonths} }} ) ON COLUMNS FROM ( SELECT ( {{ {SelectedClients} }} ) ON COLUMNS FROM [DWH Northwind]))) "; using (AdomdConnection cnn = new AdomdConnection($@"Provider=MSOLAP; Data Source=localhost;Catalog=Cubo811; User ID=sa; Password = roverto; Persist Security Info = True; Impersonation Level = Impersonate")) { AdomdDataAdapter adomdDataAdapter; cnn.Open(); using (AdomdCommand cmd = new AdomdCommand(mdxQuery, cnn)) { adomdDataAdapter = new AdomdDataAdapter(cmd); adomdDataAdapter.Fill(dataTable); } string tmpNombre = "Cliente"; dataTable.Columns.RemoveAt(0); dataTable.Columns[0].ColumnName = "Cliente"; foreach (DataColumn item in dataTable.Columns) { tmpNombre = item.ColumnName; tmpNombre = tmpNombre.Replace("[Dim Tiempo].[Dim Tiempo Año].&[", " "); tmpNombre = tmpNombre.Replace("].[Dim Tiempo].[Dim Tiempo Mes].&[", " "); tmpNombre = tmpNombre.Replace("[", " "); tmpNombre = tmpNombre.Replace("]", " "); dataTable.Columns[item.Ordinal].ColumnName = tmpNombre; lstColumNames.Add(tmpNombre); tmpNombre = ""; } List <double> lstDouble = null; for (int i = 0; i < dataTable.Rows.Count; i++) { lstDouble = new List <double>(); string ssss = ""; foreach (var item2 in lstColumNames) { double number1 = 0; var dataToConvert = ""; if (dataTable.Rows[i][item2].ToString().Equals("")) { dataToConvert = "0"; } else { dataToConvert = dataTable.Rows[i][item2].ToString(); } bool canConvert = double.TryParse(dataToConvert, out number1); if (canConvert == true) { lstDouble.Add(number1); } else { ssss = dataTable.Rows[i][item2].ToString(); } } dynamic objTabla = new { label = ssss, data = lstDouble }; dlist.Add(objTabla); //lstDouble.Clear(); } var dhyudsyuyuds = dlist; } dynamic Labels = new { labelColums = lstColumNames }; lstColumNames.RemoveAt(0); var asdasd = lstColumNames; var gagag = dataTable; //dlist.Add(Labels); return(lstColumNames); }
// was (L3ABwHrWDParams param) public void GetData() { // TBD, make view-model consisting of replicating L3 results and details for L2 results // 1) create view-model model // 2) populate L3-model sub-model // 3) populate L2-model sub-model // DataSet ds = new DataSet(); // work-around for non-functioning constructor: results = new List<L2ABwHrWD>(); string command = @" SELECT NON EMPTY { [Measures].[Bikes] } ON COLUMNS, NON EMPTY { ([CentroidA].[Level2 Locality].[Level2 Locality].ALLMEMBERS * [CentroidB].[Level2 Locality].[Level2 Locality].ALLMEMBERS * [Time Table].[Hourofday].[Hourofday].ALLMEMBERS * [Time Table].[Nameofday].[Nameofday].ALLMEMBERS * [Time Table].[Dayofweek].[Dayofweek].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION ON ROWS FROM ( SELECT ( STRTOSET(@DirectionDirection, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@CentroidBLevelLocality, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@CentroidALevelLocality, CONSTRAINED) ) ON COLUMNS FROM [Bikeshare] ) ) ) WHERE ( IIF( STRTOSET(@CentroidALevelLocality, CONSTRAINED).Count = 1, STRTOSET(@CentroidALevelLocality, CONSTRAINED), [CentroidA].[Level3 Locality].currentmember ), IIF( STRTOSET(@CentroidBLevelLocality, CONSTRAINED).Count = 1, STRTOSET(@CentroidBLevelLocality, CONSTRAINED), [CentroidB].[Level3 Locality].currentmember ), IIF( STRTOSET(@DirectionDirection, CONSTRAINED).Count = 1, STRTOSET(@DirectionDirection, CONSTRAINED), [Direction].[Direction].currentmember ) )"; using (AdomdConnection conn = new AdomdConnection("Data Source=miranda;Initial Catalog=bikesMD2")) { conn.Open(); using (AdomdCommand cmd = new AdomdCommand(command, conn)) { cmd.Parameters.Add(new AdomdParameter("DirectionDirection", queryparameters.Direction.UniqueName)); cmd.Parameters.Add(new AdomdParameter("CentroidALevelLocality", queryparameters.Level3A.UniqueName)); cmd.Parameters.Add(new AdomdParameter("CentroidBLevelLocality", queryparameters.Level3B.UniqueName)); AdomdDataAdapter adapter = new AdomdDataAdapter(cmd); adapter.Fill(ds); } conn.Close(); } // results in ds.Tables[0] // need to transfer to new list DataTable dt = ds.Tables[0]; Dictionary<string,int> colnbr = new Dictionary<string,int >(); //Dictionary(<string>,<int>) colnbr = new Dictionary(<string>,<int>); foreach (DataColumn dc in dt.Columns) { colnbr.Add(dc.ColumnName, dc.Ordinal); } foreach (DataRow dr in dt.Rows) { L2ABwHrWD oneresult = new L2ABwHrWD(dr, colnbr); results.Add(oneresult); } }
public void GetData() // was (L3ABwHrWDParams param) { // TBD, make view-model consisting of replicating L3 results and details for L2 results // 1) create view-model model // 2) populate L3-model sub-model // 3) populate L2-model sub-model // DataSet ds = new DataSet(); // work-around for non-functioning constructor: results = new List<L2ABwHrWD>(); string command = @" SELECT NON EMPTY { [Measures].[Bikes] } ON COLUMNS, NON EMPTY { ([CentroidA].[Level2 Locality].[Level2 Locality].ALLMEMBERS * [CentroidB].[Level2 Locality].[Level2 Locality].ALLMEMBERS * [Time Table].[Hourofday].[Hourofday].ALLMEMBERS * [Time Table].[Nameofday].[Nameofday].ALLMEMBERS * [Time Table].[Dayofweek].[Dayofweek].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION ON ROWS FROM ( SELECT ( STRTOSET(@DirectionDirection, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@CentroidBLevelLocality, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@CentroidALevelLocality, CONSTRAINED) ) ON COLUMNS FROM [Bikeshare] ) ) ) WHERE ( IIF( STRTOSET(@CentroidALevelLocality, CONSTRAINED).Count = 1, STRTOSET(@CentroidALevelLocality, CONSTRAINED), [CentroidA].[Level3 Locality].currentmember ), IIF( STRTOSET(@CentroidBLevelLocality, CONSTRAINED).Count = 1, STRTOSET(@CentroidBLevelLocality, CONSTRAINED), [CentroidB].[Level3 Locality].currentmember ), IIF( STRTOSET(@DirectionDirection, CONSTRAINED).Count = 1, STRTOSET(@DirectionDirection, CONSTRAINED), [Direction].[Direction].currentmember ) )"; using (AdomdConnection conn = new AdomdConnection("Data Source=miranda;Initial Catalog=bikesMD2")) { conn.Open(); using (AdomdCommand cmd = new AdomdCommand(command, conn)) { cmd.Parameters.Add(new AdomdParameter("DirectionDirection", queryparameters.Direction.UniqueName)); cmd.Parameters.Add(new AdomdParameter("CentroidALevelLocality", queryparameters.Level3A.UniqueName)); cmd.Parameters.Add(new AdomdParameter("CentroidBLevelLocality", queryparameters.Level3B.UniqueName)); AdomdDataAdapter adapter = new AdomdDataAdapter(cmd); adapter.Fill(ds); } conn.Close(); } // results in ds.Tables[0] // need to transfer to new list DataTable dt = ds.Tables[0]; Dictionary <string, int> colnbr = new Dictionary <string, int>(); //Dictionary(<string>,<int>) colnbr = new Dictionary(<string>,<int>); foreach (DataColumn dc in dt.Columns) { colnbr.Add(dc.ColumnName, dc.Ordinal); } foreach (DataRow dr in dt.Rows) { L2ABwHrWD oneresult = new L2ABwHrWD(dr, colnbr); results.Add(oneresult); } }
public virtual DataSet Execute(out float elapsedSec) { // Open the connection using (var connection = new AdomdConnection()) { var connectionString = command.Connection.ConnectionString; try { connection.ConnectionString = connectionString; } catch (ArgumentException ex) { throw new ConnectionException(ex, connectionString); } //TODO //try // {connection.Open();} //catch (AdomdException ex) // {throw new ConnectionException(ex);} Trace.WriteLineIf(NBiTraceSwitch.TraceVerbose, command.CommandText); foreach (AdomdParameter param in command.Parameters) Trace.WriteLineIf(NBiTraceSwitch.TraceVerbose, string.Format("{0} => {1}", param.ParameterName, param.Value)); // capture time before execution DateTime timeBefore = DateTime.Now; command.Connection = connection; var adapter = new AdomdDataAdapter(command); var ds = new DataSet(); adapter.SelectCommand.CommandTimeout = 0; try { adapter.Fill(ds); } catch (AdomdConnectionException ex) { throw new ConnectionException(ex, connectionString); } catch (AdomdErrorResponseException ex) { throw new ConnectionException(ex, connectionString); } // capture time after execution DateTime timeAfter = DateTime.Now; // setting query runtime elapsedSec = (float) timeAfter.Subtract(timeBefore).TotalSeconds; Trace.WriteLineIf(NBiTraceSwitch.TraceInfo, string.Format("Time needed to execute query: {0}", timeAfter.Subtract(timeBefore).ToString(@"d\d\.hh\h\:mm\m\:ss\s\ \+fff\m\s"))); return ds; } }
private void ExecuteOlapQuery() { OlapManager objOlapManager = new OlapManager(); CellSet objCellSet = objOlapManager.GetQueryResult(TextBoxQuery.Text); AdomdDataAdapter objDataAdaptor = new AdomdDataAdapter(objOlapManager.ObjCommand); AdomdDataReader objDataReader = objOlapManager.ObjCommand.ExecuteReader(CommandBehavior.CloseConnection); DataTable objTable = new DataTable(); DataColumn myColumn = new DataColumn(); DataRow myRow = null; DataTable objSchemaTable = objDataReader.GetSchemaTable(); List<string> lMeta = new List<string>(); // init meta values for (int i = 0; i < objSchemaTable.Rows.Count; i++) lMeta.Add(objSchemaTable.Rows[i][0].ToString()); // add columns and column captions for (int i = 0; i < objDataReader.FieldCount; i++) { myColumn = new DataColumn(lMeta[i]); objTable.Columns.Add(myColumn); } // output the rows in the DataReader while (objDataReader.Read()) { // new row myRow = objTable.NewRow(); // set the row values for (int i = 0; i < objDataReader.FieldCount; i++) myRow[i] = objDataReader[i]; // add row to the table objTable.Rows.Add(myRow); } // close reader objDataReader.Close(); GridViewData.DataSource = objTable; GridViewData.DataBind(); // export TEST //ExportDataTableToExcel(objTable); objOlapManager.CloseConnection(); // load the main table data this.ObjMainTable = objTable; Session.Add("queryData", objMainTable); }
public DataTable GetChartDataPieQueryMdx(string[] clients, string[] months, string[] years) { string SelectedClients = "", SelectedMonths = "", SelectedYears = ""; if (clients.Length >= 1) { foreach (var item in clients) { SelectedClients += $@"[Dim Cliente].[Dim Cliente Nombre].&[{item}],"; } SelectedClients = SelectedClients.Remove(SelectedClients.Length - 1); } if (months.Length >= 1) { foreach (var item in months) { SelectedMonths += $@"[Dim Tiempo].[Dim Tiempo Mes].&[{item}],"; } SelectedMonths = SelectedMonths.Remove(SelectedMonths.Length - 1); } if (years.Length >= 1) { foreach (var item in years) { SelectedYears += $@"[Dim Tiempo].[Dim Tiempo Año].&[{item}],"; } SelectedYears = SelectedYears.Remove(SelectedYears.Length - 1); } DataTable dataTable = new DataTable(); List <ChartDataPie> LstChartDataPie = new List <ChartDataPie>(); var mdxQuery = $@" SELECT NON EMPTY {{ ( [Dim Tiempo].[Dim Tiempo Año].[Dim Tiempo Año].ALLMEMBERS * [Dim Tiempo].[Dim Tiempo Mes].[Dim Tiempo Mes].ALLMEMBERS ) }} ON COLUMNS, NON EMPTY {{ ( ( [Measures].[Fact Ventas Netas], [Dim Cliente].[Dim Cliente Nombre].[Dim Cliente Nombre].ALLMEMBERS ) ) }} ON ROWS FROM ( SELECT ( {{ {SelectedYears} }}) ON COLUMNS FROM ( SELECT ( {{ {SelectedMonths} }} ) ON COLUMNS FROM ( SELECT ( {{ {SelectedClients} }} ) ON COLUMNS FROM [DWH Northwind]))) "; using (AdomdConnection cnn = new AdomdConnection($@"Provider=MSOLAP; Data Source=localhost;Catalog=Cubo811; User ID=sa; Password = roverto; Persist Security Info = True; Impersonation Level = Impersonate")) { AdomdDataAdapter adomdDataAdapter; cnn.Open(); using (AdomdCommand cmd = new AdomdCommand(mdxQuery, cnn)) { adomdDataAdapter = new AdomdDataAdapter(cmd); adomdDataAdapter.Fill(dataTable); } dataTable.Columns.RemoveAt(0); dataTable.Columns[0].ColumnName = "ClientesNombre"; dataTable.Columns.Add("Total", typeof(double)); double r = 0; double sum = 0; for (int renglones = 0; renglones < dataTable.Rows.Count; renglones++) { for (int columnas = 0; columnas < dataTable.Columns.Count; columnas++) { double.TryParse(dataTable.Rows[renglones][columnas].ToString(), out r); sum = sum + r; } dataTable.Rows[renglones][dataTable.Columns.Count - 1] = sum; sum = 0; } List <int> numeros = new List <int>(); for (int i = 1; i < dataTable.Columns.Count - 1; i++) { numeros.Add(i); } foreach (var item in numeros) { if (dataTable.Columns.Count == 2) { break; } dataTable.Columns.RemoveAt(dataTable.Columns.Count - 2); } } return(dataTable); }
private void comboBox1_SelectedIndexChanged(object sender, EventArgs e) { /* Obtendo campos do modelo*/ if (comboBox1.SelectedIndex != 0) { SqlConnection conSql = new SqlConnection(ConfigurationManager.ConnectionStrings["Comercial.Properties.Settings.COMERCIALConnectionString"].ConnectionString); SqlCommand comSql = new SqlCommand("select * from modelocampo where idModelo = (select id from modelo where estrutura = '" + comboBox1.Text.Substring(0, comboBox1.Text.Length - 13) + "')", conSql); conSql.Open(); SqlDataReader read = comSql.ExecuteReader(); if (read.HasRows) { int y = 0; string camp = ""; foreach (var item in read) { camp += read["campo"].ToString() + ";"; y++; } campos = camp.Split(';'); read.Close(); read.Dispose(); comSql.CommandText = "select algoritmo from modelo where estrutura = '" + comboBox1.Text.Substring(0, comboBox1.Text.Length - 13) + "'"; read = comSql.ExecuteReader(); read.Read(); if (read["algoritmo"].ToString() == "MTS (Microsoft Time Serial)") { int x = 0; AdomdConnection conn = new AdomdConnection(ConfigurationManager.ConnectionStrings["Comercial.Properties.Settings.COMERCIALConnectionString_Analysis"].ConnectionString); AdomdCommand comand = new AdomdCommand(); conn.Open(); comand.Connection = conn; comand.CommandText = "SELECT StructureColumn('" + campos[0].Trim() + "'),StructureColumn('" + campos[1].Trim() + "') " + "FROM " + comboBox1.Text.Substring(0, comboBox1.Text.Length - 13) + ".CASES"; AdomdDataAdapter ad = new AdomdDataAdapter(comand); table.Clear(); ad.Fill(table); DataTableReader reader = table.CreateDataReader(); // read.Read(); string valores = ""; foreach (var item in reader) { valores += reader[1].ToString() + ","; } valores = valores.Substring(0, valores.Length - 1); try { //chartHist.Clear(); chartHist.Charts[0].SeriesCollection.Delete(0); chartHist.Charts.Delete(0); chartHist.Refresh(); } catch { } chartHist.ResetText(); chartHist.Charts.Add(0); chartHist.Charts[0].SeriesCollection.Add(0); chartHist.Charts[0].Type = OWC11.ChartChartTypeEnum.chChartTypeLine; chartHist.Charts[0].SeriesCollection[0].SetData(OWC11.ChartDimensionsEnum.chDimSeriesNames, Convert.ToInt32(OWC11.ChartSpecialDataSourcesEnum.chDataLiteral), "Quantidade"); chartHist.Charts[0].SeriesCollection[0].SetData(OWC11.ChartDimensionsEnum.chDimValues, Convert.ToInt32(OWC11.ChartSpecialDataSourcesEnum.chDataLiteral), valores); chartHist.Refresh(); } } carregaGrid(); } }
// execute SSAS/OLAP command and return a R language compliant dataframe script representing the result data. static string SSASConnectAndExec(string mdx) { AdomdConnection conn = new AdomdConnection(_connStr); // string mdx = "SELECT {[Measures].[Page Views],[Measures].[Daily Unique Visitors]} ON 0,{[Dim Date].[Month].[Month]} ON ROWS FROM [Hearst POC];"; AdomdCommand cmd = new AdomdCommand(mdx, conn); AdomdDataAdapter da = new AdomdDataAdapter(cmd); DataTable dt = new DataTable(); try { conn.Open(); } catch (AdomdException e) { Console.WriteLine(">> Failed to open SSAS Connection"); log.Error("Failed to open SSAS connection for " + _connStr, e); return null; } try { da.Fill(dt); } catch (AdomdException e) { Console.WriteLine(">> Failed to fill the datatable"); log.Error("Failed to fill datatable for query " + mdx, e); return null; } // Clean up ado.net objects da.Dispose(); conn.Close(); // process datatable and create R language compliant dataframe object as script return DataTableToRDataFrame(dt); }
public ViewWeekofyearGivenDistanceDurationCategories(string DistanceCategoryParm, string DurationCategoryParm) { string CommandText = @"SELECT NON EMPTY { [Measures].[Bikes] } ON COLUMNS, NON EMPTY { ([Station Pair Distance].[Mile Categories].[Mile Categories].ALLMEMBERS * [TripCat].[Trip Category].[Trip Category].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( STRTOSET(@TripCatTripCategory, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( STRTOSET(@StationPairDistanceMileCategories, CONSTRAINED) ) ON COLUMNS FROM ( SELECT ( { [Direction].[Direction].&[A-B] } ) ON COLUMNS FROM [Bikeshare]) WHERE ( [Direction].[Direction].&[A-B] )))"; DataSet ds = new DataSet(); using (AdomdConnection conn = new AdomdConnection("Data Source=miranda;Initial Catalog=bikesMD2")) { conn.Open(); using (AdomdCommand cmd = new AdomdCommand(CommandText, conn)) { cmd.Parameters.Add(new AdomdParameter("StationPairDistanceMileCategories", DistanceCategoryParm)); cmd.Parameters.Add(new AdomdParameter("TripCatTripCategory", DurationCategoryParm)); AdomdDataAdapter adapter = new AdomdDataAdapter(cmd); adapter.Fill(ds); } conn.Close(); } DataTable dt = ds.Tables[0]; Dictionary<string, int> colnbr = new Dictionary<string, int>(); foreach (DataColumn dc in dt.Columns) { colnbr.Add(dc.ColumnName, dc.Ordinal); } foreach (DataRow dr in dt.Rows) { DistanceCategory = dr[colnbr["[Station Pair Distance].[Mile Categories].[Mile Categories].[MEMBER_CAPTION]"]].ToString(); DurationCategory = dr[colnbr["[TripCat].[Trip Category].[Trip Category].[MEMBER_CAPTION]"]].ToString(); Bikes = Convert.ToSingle(dr[colnbr["[Measures].[Bikes]"]]); } }
void BackgroundWorker_ExecuteQuery(object sender, DoWorkEventArgs e) { using (DataTable resultDataTable = new DataTable()) { bool isXmla = false; for (int iTry = 0; iTry < 2; iTry++) { try { var bw = sender as BackgroundWorker; bw.DoWork -= new DoWorkEventHandler(BackgroundWorker_ExecuteQuery); LogLine("Begin executing query."); string commandText = e.Argument as string; // Check first not whitespace char. If it is ''< assume that the text is XMLA, otherwise it is DAX char firstNonWhitespaceChar = commandText.ToCharArray().AsEnumerable <char>().FirstOrDefault(i => !char.IsWhiteSpace(i)); if (firstNonWhitespaceChar == '<') { isXmla = true; } if (isXmla) { AdomdConnection adomdClient = null; //XmlaClient xmlaClient = null; try { adomdClient = new AdomdConnection(this._daxDocumentProperties.ConnectionStringWithoutDatabaseName); adomdClient.Open(); var cmd = new AdomdCommand("@CommandText", adomdClient); cmd.Parameters.Add(new AdomdParameter("CommandText", commandText)); System.Xml.XmlReader reader = cmd.ExecuteXmlReader(); var formattedXmlaResult = reader.ReadOuterXml().ToString(); reader.Close(); //xmlaClient = new Microsoft.AnalysisServices.XmlaClient(); //xmlaClient.Connect(this._daxDocumentProperties.ConnectionStringWithoutDatabaseName); //var result = xmlaClient.Send(commandText, null); //var formattedXmlaResult = XDocument.Parse(result.ToString()).ToString(); resultDataTable.Columns.Add("Result"); resultDataTable.Rows.Add("See XMLA Result tab"); _updateEditorMargin.UpdateResult(resultDataTable); _updateEditorMargin.UpdateXmlaResult(formattedXmlaResult); } finally { if (adomdClient != null) { adomdClient.Close(); } } } else { using (var cmd = new AdomdCommand("@CommandText", _adomdConn)) { cmd.Parameters.Add(new AdomdParameter("CommandText", commandText)); using (AdomdDataAdapter dataAdapter = new AdomdDataAdapter(cmd)) { dataAdapter.Fill(resultDataTable); } } NormalizeHeaders(resultDataTable); _updateEditorMargin.UpdateResult(resultDataTable); _updateEditorMargin.UpdateXmlaResult("See Result tab"); } LogLine("End reading response."); LogLine("End executing query."); } catch (AdomdConnectionException) { if (_adomdConn != null) { LogLine("Connection error while executing the query. Reconnecting..."); var connectionString = _adomdConn.ConnectionString; DoDisconnect(); DoConnect(connectionString); continue; } } catch (AdomdErrorResponseException ex) { LogLine("Error while reading response."); resultDataTable.Columns.Add("Error"); resultDataTable.Rows.Add(ex.Message); resultDataTable.Rows[0].RowError = ex.Message; _updateEditorMargin.UpdateResult(resultDataTable); _updateEditorMargin.UpdateXmlaResult(ex.Message); } catch (Exception ex) { LogError("Error while executing query:" + ex.ToString()); } // As soon as reached this point do not re-try break; } } }