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);
}
}
// 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);
}
}
