static void ConvertReaderToCsv(AdomdDataReader reader, bool openinExcel = true)
{
string csv = string.Empty;
for (int col = 0; col < reader.FieldCount; col++)
{
csv += reader.GetName(col);
csv += (col < (reader.FieldCount - 1)) ? "," : "\n";
}
// Create a loop for every row in the resultset
while (reader.Read())
{
// Create a loop for every column in the current row
for (int i = 0; i < reader.FieldCount; i++)
{
csv += reader.GetValue(i);
csv += (i < (reader.FieldCount - 1)) ? "," : "\n";
}
}
string filePath = System.IO.Directory.GetCurrentDirectory() + @"\QueryResuts.csv";
StreamWriter writer = File.CreateText(filePath);
writer.Write(csv);
writer.Flush();
writer.Dispose();
if (openinExcel)
{
OpenInExcel(filePath);
}
}
/// <summary>
/// Prints query result.
/// </summary>
private static int ProcessQueryResult(AdomdDataReader reader)
{
int count = 0;
// print field names in the query result
for (int j = 0; j < reader.FieldCount; ++j)
{
Console.Write(reader.GetName(j) + (j == reader.FieldCount - 1 ? "" : ", "));
}
Console.WriteLine();
// print field values in the query result;
while (reader.Read())
{
++count;
for (int i = 0; i < reader.FieldCount; i++)
{
Console.Write(reader[i] + (i == reader.FieldCount - 1 ? "" : ", "));
}
Console.WriteLine();
}
return(count);
}
private static Dictionary <string, string> convertToDictionary(AdomdDataReader reader)
{
var dataObject = new Dictionary <string, string>();
var values = new object[reader.FieldCount];
reader.GetValues(values);
for (int i = 0; i < reader.FieldCount; i++)
{
dataObject.Add(reader.GetName(i), stringify(reader, i));
}
return(dataObject);
}
public string Command(string analysisSever, string depenedentValue)
{
StringBuilder stringBuilder = new StringBuilder();
//Analysis Server Connection String
string analysisServer = "Provider=MSOLAP;Data Source=TJ-PC;Integrated Security=SSPI;Initial Catalog=HDPurchasingPredictiveAnalysis;Location=TJ-PC";
using (AdomdConnection serverConnection = new AdomdConnection(analysisServer))
{
//Initialise a new command
AdomdCommand command = new AdomdCommand();
//Connect to the Analysis Server
command.Connection = serverConnection;
//Run this singleton query
command.CommandText = "SELECT [DrugPricingCostLR].[Drug Cost_] From[DrugPricingCostLR] NATURAL PREDICTION JOIN (SELECT 234 AS[ASP 2016]) AS t";
//This is the parameter we are using to predict
AdomdParameter parameter = null;
parameter = command.CreateParameter();
parameter.Direction = ParameterDirection.Input;
parameter.ParameterName = "ASP 2016";
parameter.Value = depenedentValue;
command.Parameters.Add(parameter);
//Open the connection to the Analysis Server
serverConnection.Open();
//Execute the prediction query and return the results
AdomdDataReader reader = null;
reader = command.ExecuteReader();
for (int i = 0; i < reader.FieldCount; i++)
{
stringBuilder.Append(reader.GetName(i) + "\t");
}
stringBuilder.Append("\r\n--------------------------------------------\r\n");
//Important before accessing any data, call the .Read method
while (reader.Read())
{
for (int i = 0; i < reader.FieldCount; i++)
{
stringBuilder.Append(reader.GetValue(i).ToString() + "\t");
}
stringBuilder.Append("\r\n");
}
reader.Close();
}
return(stringBuilder.ToString());
}
//string drugDatabase = "Data Source=TJ-PC;Initial Catalog=HDPurch;Integrated Security=True";
private void buttonPredict_Click(object sender, EventArgs e)
{
if (radioButtonANN.Checked.Equals(true) && !(string.IsNullOrEmpty(dependentVariable.Text)))
{
//SingletonQuery
string dmxQuery = @"SELECT [DrugPricingCostNN].[Drug Cost_] From [DrugPricingCostNN] NATURAL PREDICTION JOIN (SELECT '" + dependentVariable.Text + @"'AS [ASP 2016]) AS t";
//AllResultQuery
string AnnResults = "SELECT[DrugPricingCostNN].[Drug Cost_] From [DrugPricingCostNN] PREDICTION JOIN OPENQUERY([HD Purch], " +
"'SELECT [Drug_Cost_$], [ASP_2012], [ASP_2013], [ASP_2014], [ASP_2015], [ASP_2016] " +
"FROM [dbo].[DrugPricingCost] ') AS t ON " +
"[DrugPricingCostNN].[Drug Cost_] = t.[Drug_Cost_$] AND " +
"[DrugPricingCostNN].[ASP 2012] = t.[ASP_2012] AND " +
"[DrugPricingCostNN].[ASP 2013] = t.[ASP_2013] AND " +
"[DrugPricingCostNN].[ASP 2014] = t.[ASP_2014] AND " +
"[DrugPricingCostNN].[ASP 2015] = t.[ASP_2015] AND " +
"[DrugPricingCostNN].[ASP 2016] = t.[ASP_2016]";
try
{
//Open a connection to the analysis services server
AdomdConnection connection = new AdomdConnection(analysisServer);
connection.Open();
//SingletonQuery
AdomdCommand command = new AdomdCommand(dmxQuery, connection);
AdomdDataReader reader = command.ExecuteReader();
reader.Read();
//Init stringBuilder
StringBuilder stringBuilder = new StringBuilder();
//Init a data table
//DataGrid grid = new DataGrid();
for (int i = 0; i < reader.FieldCount; i++)
{
stringBuilder.Append("Predicted " + reader.GetName(i) + "\t");
stringBuilder.Append(reader.GetValue(i).ToString().Trim() + "\t");
//allResultGridView.DataSource = reader;
}
//Important before accessing any data, call the .Read method
while (reader.Read())
{
for (int i = 0; i < reader.FieldCount; i++)
{
//stringBuilder.Append(reader.GetName(i) + "\t");
stringBuilder.Append(reader.GetValue(i).ToString() + "\t");
}
stringBuilder.Append("\r\n");
}
//Return the predicted value and display it in the text box
predictedValue.Text = stringBuilder.ToString();
Console.Write(stringBuilder.ToString());
reader.Close();
connection.Close();
}
catch (Exception)
{
throw;
}
try
{
AdomdConnection allANNConnect = new AdomdConnection(analysisServer);
allANNConnect.Open();
AdomdCommand allClusterQuery = new AdomdCommand(AnnResults, allANNConnect);
AdomdDataReader annReader = allClusterQuery.ExecuteReader();
annReader.Read();
System.Data.DataTable dataTable = new System.Data.DataTable();
dataTable.Load(annReader);
algorithmGridView.DataSource = dataTable;
allANNConnect.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
throw;
}
}
//Run the Logistic Regression Algorthim
else if (radioButtonLR.Checked.Equals(true) && !(string.IsNullOrEmpty(dependentVariable.Text)))
{
string dmxQuery = @"SELECT [DrugPricingCostLogReg].[Drug Cost_] From [DrugPricingCostLogReg] NATURAL PREDICTION JOIN (SELECT '" + dependentVariable.Text + @"'AS [ASP 2016]) AS t";
//AllResultQuery
string LogRegResults = "SELECT[DrugPricingCostLogReg].[Drug Cost_] From [DrugPricingCostLogReg] PREDICTION JOIN OPENQUERY([HD Purch], " +
"'SELECT [Drug_Cost_$], [ASP_2012], [ASP_2013], [ASP_2014], [ASP_2015], [ASP_2016] " +
"FROM [dbo].[DrugPricingCost] ') AS t ON " +
"[DrugPricingCostLogReg].[Drug Cost_] = t.[Drug_Cost_$] AND " +
"[DrugPricingCostLogReg].[ASP 2012] = t.[ASP_2012] AND " +
"[DrugPricingCostLogReg].[ASP 2013] = t.[ASP_2013] AND " +
"[DrugPricingCostLogReg].[ASP 2014] = t.[ASP_2014] AND " +
"[DrugPricingCostLogReg].[ASP 2015] = t.[ASP_2015] AND " +
"[DrugPricingCostLogReg].[ASP 2016] = t.[ASP_2016]";
try
{
AdomdConnection connection = new AdomdConnection(analysisServer);
connection.Open();
AdomdCommand command = new AdomdCommand(dmxQuery, connection);
AdomdDataReader reader = command.ExecuteReader();
reader.Read();
StringBuilder stringBuilder = new StringBuilder();
for (int i = 0; i < reader.FieldCount; i++)
{
stringBuilder.Append("Predicted " + reader.GetName(i) + "\t");
stringBuilder.Append(reader.GetValue(i).ToString() + "\t");
}
//Important before accessing any data, call the .Read method
while (reader.Read())
{
for (int i = 0; i < reader.FieldCount; i++)
{
//stringBuilder.Append(reader.GetValue(i).ToString() + "\t");
}
stringBuilder.Append("\r\n");
}
predictedValue.Text = stringBuilder.ToString();
Console.Write(stringBuilder.ToString());
reader.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
throw;
}
//Display the results in the grid view tab
try
{
//string drugData = "Select Drug_ID From Drug";
//SqlConnection sqlConnection = new SqlConnection(drugDatabase);
AdomdConnection allLogRegConnect = new AdomdConnection(analysisServer);
allLogRegConnect.Open();
AdomdCommand allLogRegQuery = new AdomdCommand(LogRegResults, allLogRegConnect);
//sqlConnection.Open();
//SqlCommand sqlCommand = new SqlCommand(drugData, sqlConnection);
//SqlDataReader sqlDataReader = sqlCommand.ExecuteReader();
//sqlDataReader.Read();
AdomdDataReader logRegReader = allLogRegQuery.ExecuteReader();
logRegReader.Read();
System.Data.DataTable dataTable = new System.Data.DataTable();
//dataTable.Columns.Add("Drug_ID");
//dataTable.Columns.Add("Brand_Name");
//dataTable.Columns.Add("Generic_Name");
//dataTable.Columns.Add("Drug_Cost_$");
//System.Data.DataRow row = dataTable.NewRow();
//while (sqlDataReader.Read())
//{
// row["Drug_ID"] = sqlDataReader[0];
// row["Brand_Name"] = sqlDataReader[1];
// row["Generic_Name"] = sqlDataReader[2];
// row["Drug_Cost_$"] = sqlDataReader[3];
// dataTable.Rows.Add(row);
//}
//dataTable.Load(sqlDataReader);
dataTable.Load(logRegReader);
algorithmGridView.DataSource = dataTable;
allLogRegConnect.Close();
//sqlConnection.Close();
}
catch (Exception exp)
{
MessageBox.Show(exp.ToString());
throw;
}
}
//Run the clustering algorithm
else if (radioButtonClustering.Checked.Equals(true) && !(string.IsNullOrEmpty(dependentVariable.Text)))
{
string dmxQuery = @"SELECT [DrugPricingClustering].[Drug Cost_] From [DrugPricingClustering] NATURAL PREDICTION JOIN (SELECT '" + dependentVariable.Text + @"'AS [ASP 2016]) AS t";
//AllResultQuery
string ClusterResults = "SELECT [DrugPricingClustering].[Drug Cost_] From [DrugPricingClustering] PREDICTION JOIN OPENQUERY([HD Purch], " +
"'SELECT [Drug_Cost_$], [ASP_2012], [ASP_2013],[ASP_2014],[ASP_2015], [ASP_2016] FROM [dbo].[DrugPricingCost] ') AS t " +
"ON " +
"[DrugPricingClustering].[Drug Cost_] = t.[Drug_Cost_$] AND " +
"[DrugPricingClustering].[ASP 2012] = t.[ASP_2012] AND " +
"[DrugPricingClustering].[ASP 2013] = t.[ASP_2013] AND " +
"[DrugPricingClustering].[ASP 2014] = t.[ASP_2014] AND " +
"[DrugPricingClustering].[ASP 2015] = t.[ASP_2015] AND " +
"[DrugPricingClustering].[ASP 2016] = t.[ASP_2016]";
try
{
AdomdConnection connection = new AdomdConnection(analysisServer);
connection.Open();
AdomdCommand command = new AdomdCommand(dmxQuery, connection);
AdomdDataReader reader = command.ExecuteReader();
reader.Read();
StringBuilder stringBuilder = new StringBuilder();
for (int i = 0; i < reader.FieldCount; i++)
{
stringBuilder.Append("Predicted " + reader.GetName(i) + "\t");
stringBuilder.Append(reader.GetValue(i).ToString() + "\t");
}
//Important before accessing any data, call the .Read method
while (reader.Read())
{
for (int i = 0; i < reader.FieldCount; i++)
{
//stringBuilder.Append(reader.GetValue(i).ToString() + "\t");
}
stringBuilder.Append("\r\n");
}
predictedValue.Text = stringBuilder.ToString();
//Console.Write(stringBuilder.ToString());
reader.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
throw;
}
try
{
AdomdConnection allClusterConnect = new AdomdConnection(analysisServer);
allClusterConnect.Open();
AdomdCommand allClusterQuery = new AdomdCommand(ClusterResults, allClusterConnect);
AdomdDataReader clusterReader = allClusterQuery.ExecuteReader();
clusterReader.Read();
System.Data.DataTable dataTable = new System.Data.DataTable();
dataTable.Load(clusterReader);
algorithmGridView.DataSource = dataTable;
allClusterConnect.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
throw;
}
}
else
{
//Display an error message implying that the user has to select a specific algorithm button
MessageBox.Show("Please select an algorithm to commence the prediction & enter a prediction value into the text box");
}
}
public static string returnJson(string clientid, string secret, string query, string workspace, string dataset)
{
if (String.IsNullOrEmpty(clientid) || String.IsNullOrEmpty(secret) || String.IsNullOrEmpty(query) || String.IsNullOrEmpty(workspace) || String.IsNullOrEmpty(dataset))
{
return("Not all parameters have been specified! clientid, secret, query, workspace, dataset");
}
string tenantid = "your_tenant_id";
string connectionString = "Datasource=" + workspace + ";initial catalog=" + dataset + ";User ID=app:" + clientid + "@" + tenantid + ";Password="******"EVALUATE (Sales)";
AdomdCommand adomdCommand = new AdomdCommand(query, adomdConnection);
/*******************************************************
* Run the Query
* - Open the connection
* - Issue the query
* - Iterate through each row of the reader
* - Iterate through each column of the current row
* - Close the connection
*******************************************************/
adomdConnection.Open();
AdomdDataReader reader = adomdCommand.ExecuteReader();
//Create a loop for every row in the resultset
List <string> curColumn = new List <string> ();
StringBuilder sb = new StringBuilder();
StringWriter sw = new StringWriter(sb);
string columnName = string.Empty;
string fieldVal = string.Empty;
string prevFieldVal = string.Empty;
if (reader != null)
{
using (JsonWriter myJson = new JsonTextWriter(sw))
{
myJson.WriteStartArray();
while (reader.Read())
{
myJson.WriteStartObject();
int fields = reader.FieldCount;
for (int i = 0; i < fields; i++)
{
if (reader[i] != null)
{
fieldVal = reader[i].ToString();
if (i != 0 && reader[i - 1] != null)
{
prevFieldVal = reader[i - 1].ToString();
}
else
{
prevFieldVal = "First";
}
if ((fieldVal == null || fieldVal.ToLower().Trim() == "undefined" ||
fieldVal.ToLower().Trim() == "unknown") &&
(prevFieldVal == null || prevFieldVal.ToLower().Trim() ==
"undefined" || prevFieldVal.ToLower().Trim() == "unknown"))
{
continue;
}
else
{
columnName = reader.GetName(i).Replace(".[MEMBER_CAPTION]",
"").Trim();
curColumn = columnName.Split(new string[] { "." },
StringSplitOptions.None).ToList();
columnName = curColumn[curColumn.Count - 1].Replace("[",
"").Replace("]", "");
if (Convert.ToString(columnName.Trim()).ToLower() == "latitude")
{
columnName = "lat";
}
if (Convert.ToString(columnName.Trim()).ToLower() == "longitude")
{
columnName = "lon";
}
myJson.WritePropertyName(columnName);
myJson.WriteValue(reader[i]);
}
}
}
myJson.WriteEndObject();
}
myJson.WriteEndArray();
}
}
else
{
return("No Records to display");
}
//Console.WriteLine(curColumn);
adomdConnection.Close();
return(sw.ToString());
}
public void LoadData(AdomdDataReader reader)
{
ClearGrid();
int iColCount = reader.FieldCount;
this.ColumnsCount = iColCount;
this.RowsCount = 1;
// set the column headers
for (int i = 0; i < iColCount; i++)
{
string sColumn = reader.GetName(i);
SetHeaderValue(0, i, sColumn);
}
int iRow = 1;
this.RowsCount = 1;
while (reader.Read())
{
this.RowsCount++;
for(int iCol=0; iCol < iColCount; iCol++)
{
if (!reader.IsDBNull(iCol))
{
string sValue = reader.GetValue(iCol).ToString();
SetCellValue(iRow, iCol, sValue);
}
else
{
SetCellValue(iRow, iCol, "");
}
}
iRow++;
}
}
static private void executeDAX(Model model)
{
Console.Clear();
showConsoleHeader(model);
myConsoleWriteLine($"Enter or Pick a Query");
string query = getData("Query.dat");
AdomdConnection adomdConnection = new AdomdConnection($"Data Source={model.Database.Parent.ConnectionString};Initial catalog={model.Database.Name}");
AdomdCommand adomdCommand = new AdomdCommand(query, adomdConnection);
adomdConnection.Open();
String queryResultFileName = $"{appFolder}QueryResult-{Guid.NewGuid()}.tsv";
List <string> list = new List <string>();
bool hasHeader = false;
try {
AdomdDataReader reader = adomdCommand.ExecuteReader();
while (reader.Read())
{
String rowResults = "";
/*****************************************************************************
* Add Header (if needed)
****************************************************************************/
if (!hasHeader)
{
for (int columnNumber = 0; columnNumber < reader.FieldCount; columnNumber++)
{
if (columnNumber > 0)
{
rowResults += $"\t";
}
rowResults += $"{reader.GetName(columnNumber)}";
}
Console.WriteLine(rowResults);
list.Add(rowResults);
hasHeader = true;
}
/*****************************************************************************
* Add normal line
****************************************************************************/
rowResults = "";
// Create a loop for every column in the current row
for (int columnNumber = 0; columnNumber < reader.FieldCount; columnNumber++)
{
if (columnNumber > 0)
{
rowResults += $"\t";
}
rowResults += $"{reader.GetValue(columnNumber)}";
}
Console.WriteLine(rowResults);
list.Add(rowResults);
}
System.IO.File.WriteAllLines(queryResultFileName, list);
ProcessStartInfo startInfo = new ProcessStartInfo();
bool excelFound = false;
if (File.Exists("C:\\Program Files\\Microsoft Office\\root\\Office16\\EXCEL.EXE"))
{
startInfo.FileName = "C:\\Program Files\\Microsoft Office\\root\\Office16\\EXCEL.EXE";
excelFound = true;
}
else
{
if (File.Exists("C:\\Program Files (x86)\\Microsoft Office\\root\\Office16\\EXCEL.EXE"))
{
startInfo.FileName = "C:\\Program Files (x86)\\Microsoft Office\\root\\Office16\\EXCEL.EXE";
excelFound = true;
}
}
if (excelFound)
{
startInfo.Arguments = queryResultFileName;
Process.Start(startInfo);
}
}
catch (Exception ex) {
Console.WriteLine(ex.Message);
Console.ReadKey();
}
adomdConnection.Close();
}
static void Main(string[] args)
{
/*******************************************************
* Define Connection
*******************************************************/
AdomdConnection adomdConnection = new AdomdConnection("Datasource=powerbi://api.powerbi.com/v1.0/myorg/<your work space>;initial catalog=<your data set>;User ID=app:<app_id>@<tenant_id>;Password=<app_secret>");
/*******************************************************
* Define Query (as a Command)
* - the AdomdCommant uses the above connection
* - subsitute this for your own query
*******************************************************/
String query = @"<your_DAX_query>";
AdomdCommand adomdCommand = new AdomdCommand(query, adomdConnection);
/*******************************************************
* Run the Query
* - Open the connection
* - Issue the query
* - Iterate through each row of the reader
* - Iterate through each column of the current row
* - Close the connection
*******************************************************/
adomdConnection.Open();
AdomdDataReader reader = adomdCommand.ExecuteReader();
String jsonstring = @"[";
// Create a loop for every row in the resultset
List <string> curColumn = new List <string> ();
StringBuilder sb = new StringBuilder();
StringWriter sw = new StringWriter(sb);
string columnName = string.Empty;
string fieldVal = string.Empty;
string prevFieldVal = string.Empty;
if (reader != null)
{
using (JsonWriter myJson = new JsonTextWriter(sw))
{
myJson.WriteStartArray();
while (reader.Read())
{
myJson.WriteStartObject();
int fields = reader.FieldCount;
for (int i = 0; i < fields; i++)
{
if (reader[i] != null)
{
fieldVal = reader[i].ToString();
if (i != 0 && reader[i - 1] != null)
{
prevFieldVal = reader[i - 1].ToString();
}
else
{
prevFieldVal = "First";
}
if ((fieldVal == null || fieldVal.ToLower().Trim() == "undefined" ||
fieldVal.ToLower().Trim() == "unknown") &&
(prevFieldVal == null || prevFieldVal.ToLower().Trim() ==
"undefined" || prevFieldVal.ToLower().Trim() == "unknown"))
{
continue;
}
else
{
columnName = reader.GetName(i).Replace(".[MEMBER_CAPTION]",
"").Trim();
curColumn = columnName.Split(new string[] { "." },
StringSplitOptions.None).ToList();
columnName = curColumn[curColumn.Count - 1].Replace("[",
"").Replace("]", "");
if (Convert.ToString(columnName.Trim()).ToLower() == "latitude")
{
columnName = "lat";
}
if (Convert.ToString(columnName.Trim()).ToLower() == "longitude")
{
columnName = "lon";
}
myJson.WritePropertyName(columnName);
myJson.WriteValue(reader[i]);
}
}
}
myJson.WriteEndObject();
}
myJson.WriteEndArray();
}
}
else
{
Console.WriteLine("No Records to display");
}
Console.WriteLine(sw.ToString());
adomdConnection.Close();
}
public HttpResponseMessage GetDataMonthByDimension(string dim, string year, string month, string order, ConsultaMes data)
{
string WITH = @"
WITH
SET [Items] AS
{
STRTOSET(@Dimension)
}
";
string COLUMNS = @"
NON EMPTY
{
STRTOSET(@Year)
}
ON COLUMNS,
";
string ROWS = @"
NON EMPTY
{
ORDER(
[Items],
[Measures].[Hec Ventas Ventas], " + order +
@")
}
*
{
STRTOSET(@Month)
}
ON ROWS
";
string CUBO_NAME = "[DWH Northwind]";
string MDX_QUERY = WITH + @"SELECT " + COLUMNS + ROWS + " FROM " + CUBO_NAME;
Debug.Write(MDX_QUERY);
List <string> anio = new List <string>();
List <dynamic> lstTabla = new List <dynamic>();
string valoresDimension = string.Empty;
string valoresYear = string.Empty;
string valoresMonth = string.Empty;
foreach (var item in data.Dimension)
{
valoresDimension += "{0}.[" + item + "],";
}
valoresDimension = valoresDimension.TrimEnd(',');
valoresDimension = string.Format(valoresDimension, dim);
valoresDimension = @"{" + valoresDimension + "}";
foreach (var item in data.Year)
{
valoresYear += "{0}.[" + item + "],";
}
valoresYear = valoresYear.TrimEnd(',');
valoresYear = string.Format(valoresYear, year);
valoresYear = @"{" + valoresYear + "}";
foreach (var item in data.Month)
{
valoresMonth += "{0}.[" + item + "],";
}
valoresMonth = valoresMonth.TrimEnd(',');
valoresMonth = string.Format(valoresMonth, month);
valoresMonth = @"{" + valoresMonth + "}";
using (AdomdConnection cnn = new AdomdConnection(ConfigurationManager.ConnectionStrings["CuboNorthwind"].ConnectionString))
{
cnn.Open();
using (AdomdCommand cmd = new AdomdCommand(MDX_QUERY, cnn))
{
cmd.Parameters.Add("Dimension", valoresDimension);
cmd.Parameters.Add("Year", valoresYear);
cmd.Parameters.Add("Month", valoresMonth);
using (AdomdDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
while (dr.Read())
{
switch (dr.FieldCount)
{
case 2:
dynamic objTabla = new
{
name = dr.GetString(0),
ventas = new decimal[] { getDecimalHelper(dr.GetValue(2)) },
total = getDecimalHelper(dr.GetValue(2)),
};
lstTabla.Add(objTabla);
break;
case 3:
dynamic objTabla2 = new
{
name = dr.GetString(0),
ventas = new decimal[] { getDecimalHelper(dr.GetValue(2)), getDecimalHelper(dr.GetValue(3)) },
total = getDecimalHelper(dr.GetValue(2)) + getDecimalHelper(dr.GetValue(3)),
};
lstTabla.Add(objTabla2);
break;
case 4:
dynamic objTabla3 = new
{
name = dr.GetString(0),
ventas = new decimal[] { getDecimalHelper(dr.GetValue(2)), getDecimalHelper(dr.GetValue(3)), getDecimalHelper(dr.GetValue(4)) },
total = getDecimalHelper(dr.GetValue(2)) + getDecimalHelper(dr.GetValue(3)) + getDecimalHelper(dr.GetValue(4))
};
lstTabla.Add(objTabla3);
break;
}
}
switch (dr.FieldCount)
{
case 4:
anio.Add(dr.GetName(1).Substring(22, 4));
anio.Add(dr.GetName(2).Substring(22, 4));
anio.Add(dr.GetName(3).Substring(22, 4));
break;
case 3:
anio.Add(dr.GetName(1).Substring(22, 4));
anio.Add(dr.GetName(2).Substring(22, 4));
break;
case 2:
anio.Add(dr.GetName(1).Substring(22, 4));
break;
}
dr.Close();
}
}
}
dynamic result = new
{
anio,
sales = lstTabla
};
return(Request.CreateResponse(HttpStatusCode.OK, (object)result));
}