public HttpResponseMessage List(string name = "cipla") { using (AdomdConnection conn = new AdomdConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString())) { // AdomdConnection conn = new AdomdConnection( // "Data Source=localhost;Catalog=YourDatabase"); //conn.Open(); string commandText = @"SELECT FLATTENED PredictAssociation() From [Mining Structure Name] NATURAL PREDICTION JOIN (SELECT (SELECT 1 AS [UserId]) AS [Vm]) AS t "; AdomdCommand cmd = new AdomdCommand(commandText, conn); AdomdDataReader dr = cmd.ExecuteReader(); while (dr.Read()) { Console.WriteLine(Convert.ToString(dr[0])); } dr.Close(); conn.Close(); return(null); } }
protected void Button2_Click(object sender, EventArgs e) { AdomdConnection con = new AdomdConnection("Data Source=PC;Initial Catalog=ADM_HW"); con.Open(); AdomdCommand com = new AdomdCommand(); com.Connection = con; string s = @"SELECT disease From [HeartID3MM] NATURAL PREDICTION JOIN (SELECT '" + TextBox1.Text + @"' AS [Age], '" + DropDownList1.SelectedItem.Text + @"' AS [Chest Pain Type], '" + TextBox2.Text + @"' AS [Rest Blood Pressure], '" + DropDownList2.SelectedItem.Text + @"' AS [Blood Sugar], '" + DropDownList4.SelectedItem.Text + @"' AS [Exercice Angina], '" + TextBox3.Text + @"' AS [Max Heart Rate], '" + DropDownList3.SelectedItem.Text + @"' AS [Rest Electro]) AS t"; com.CommandText = s; AdomdDataReader dr = com.ExecuteReader(); if (dr.Read()) { if (dr[0] != null) { Label2.Text = dr[0].ToString(); } } dr.Close(); con.Close(); }
public HttpResponseMessage GetItemsByDimension(string dim, string order) { string WITH = @" WITH SET [OrderDimension] AS NONEMPTY( ORDER( {0}.CHILDREN, {0}.CURRENTMEMBER.MEMBER_NAME, " + order + @") ) "; string COLUMNS = @" NON EMPTY { [Measures].[Ventas] } ON COLUMNS, "; string ROWS = @" NON EMPTY [OrderDimension] ON ROWS "; string CUBO_NAME = "[DHW Northwind]"; WITH = string.Format(WITH, dim); string MDX_QUERY = WITH + @"SELECT " + COLUMNS + ROWS + " FROM " + CUBO_NAME; Debug.Write(MDX_QUERY); List <string> dimension = new List <string>(); dynamic result = new { datosDimension = dimension }; using (AdomdConnection cnn = new AdomdConnection(ConfigurationManager.ConnectionStrings["CuboNorthwind"].ConnectionString)) { cnn.Open(); using (AdomdCommand cmd = new AdomdCommand(MDX_QUERY, cnn)) { cmd.Parameters.Add("Dimension", dim); using (AdomdDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)) { while (dr.Read()) { dimension.Add(dr.GetString(0)); } dr.Close(); } } } return(Request.CreateResponse(HttpStatusCode.OK, (object)result)); }
private void getBoughtToghtherMovies(String input, List <string> output) { String newInput = input.Trim(); AdomdConnection CON = new AdomdConnection( "Data Source=.;Catalog=ADMF18"); CON.Open(); AdomdCommand COM = CON.CreateCommand(); string s = "SELECT Flattened PREDICT([Movies],5" + ") FROM [CustomersMM] NATURAL PREDICTION JOIN (SELECT (" + "Select '" + newInput + "' as [movie]" + ") AS [Movies]) As T"; COM.CommandText = s; AdomdDataReader DR = COM.ExecuteReader(); while (DR.Read()) { if (DR[0] != null) { output.Add(DR[0].ToString()); } } DR.Close(); CON.Close(); }
/// <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 Dictionary <string, int> RequestSales(string MDXQuery) { Dictionary <string, int> responseMessage = new Dictionary <string, int>(); try { OpenConnection(); AdomdCommand command = new AdomdCommand(MDXQuery, connection); AdomdDataReader dataReader = command.ExecuteReader(); while (dataReader.Read()) { responseMessage.Add(Convert.ToString(dataReader[0]), Convert.ToInt32(dataReader[1])); } dataReader.Close(); connection.Close(); return(responseMessage); } catch (Exception e) { Console.WriteLine("Exception has occured while retriving data:\n" + "Error message: " + e.Message); return(null); } }
/// <summary> /// Returns a DataTable from an AdomdCommand reader (MDX Query in an OLAP Cube) /// </summary> static public DataTable FromAdomdCommand(AdomdCommand command) { AdomdDataReader dr = command.ExecuteReader(); DataTable result = new DataTable("Data"); // output the rows in the DataReader DataTable dtSchema = dr.GetSchemaTable(); foreach (DataRow schemarow in dtSchema.Rows) { var columnName = schemarow.ItemArray[0].ToString().Replace("[", "").Replace("]", "").Replace(" ", ""); result.Columns.Add(columnName, Type.GetType(schemarow.ItemArray[5].ToString())); } while (dr.Read()) { object[] ColArray = new object[dr.FieldCount]; for (int i = 0; i < dr.FieldCount; i++) { if (dr[i] != null) { ColArray[i] = dr[i]; } } result.LoadDataRow(ColArray, true); } dr.Close(); return(result); }
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); } }
public SemesterSales[] GetSalesPrSemester() { using (AdomdConnection conn = new AdomdConnection("DataSource = localhost; Initial Catalog = FClubCube")) { conn.Open(); AdomdCommand cmd = conn.CreateCommand(); cmd.CommandText = @" SELECT [Measures].[Fact Sale Count] ON COLUMNS, NONEMPTY([Dim Member].[Semester].[Semester]*[Dim Product].[Sub Sub Category].[Sub Sub Category]) ON ROWS FROM [F Club DW]"; AdomdDataReader dr = cmd.ExecuteReader(); List <SemesterSales> semesters = new List <SemesterSales>(); while (dr.Read()) { SemesterSales semester = new SemesterSales { semester = dr[0].ToString(), SubSubCategory = dr[1].ToString(), saleCount = int.Parse(dr[2].ToString()) }; semesters.Add(semester); } dr.Close(); SemesterSales[] semesterSales = semesters.FindAll(FindBeer).ToArray(); conn.Close(); return(semesterSales); } }
public ProductCat[] GetProductsByCategory() { using (AdomdConnection conn = new AdomdConnection("DataSource = localhost; Initial Catalog = FClubCube")) { conn.Open(); AdomdCommand cmd = conn.CreateCommand(); cmd.CommandText = @"SELECT [Measures].[Fact Sale Count] ON COLUMNS, {[Dim Product].[Product Id].[Product Id]*[Dim Product].[Name].[Name]*[Dim Product].[Category].[Category]*[Dim Product].[Sub Category].[Sub Category]*[Dim Product].[Sub Sub Category].[Sub Sub Category]} ON ROWS From [F Club DW]"; AdomdDataReader dr = cmd.ExecuteReader(); List <ProductCat> productCats = new List <ProductCat>(); while (dr.Read()) { ProductCat productCat = new ProductCat { productID = int.Parse(dr[0].ToString()), name = dr[1].ToString(), cat = dr[2].ToString(), subCat = dr[3].ToString(), subSubCat = dr[4].ToString(), }; productCats.Add(productCat); } dr.Close(); ProductCat[] productSubCats = productCats.ToArray(); conn.Close(); return(productSubCats); } }
public SCCatD[] GetProductSalesPrDay() { using (AdomdConnection conn = new AdomdConnection("DataSource = localhost; Initial Catalog = FClubCube")) { conn.Open(); AdomdCommand cmd = conn.CreateCommand(); cmd.CommandText = @" SELECT [Measures].[Fact Sale Count] ON COLUMNS, NONEMPTY( [Dim Product].[Category].[Category]*[Dim Date].[Day].[Day]) ON ROWS FROM [F Club DW]"; AdomdDataReader dr = cmd.ExecuteReader(); List <SCCatD> days = new List <SCCatD>(); while (dr.Read()) { SCCatD day = new SCCatD { cat = dr[0].ToString(), day = int.Parse(dr[1].ToString()), saleCount = int.Parse(dr[2].ToString()) }; days.Add(day); } dr.Close(); SCCatD[] daySales = days.ToArray(); conn.Close(); return(daySales); } }
protected void Button1_Click(object sender, EventArgs e) { //الاتصال مع قاعدة البيانات AdomdConnection conn = new AdomdConnection(); conn.ConnectionString = "Data Source=.;Catalog=AnalysisDB"; conn.Open(); // Command AdomdCommand cmd = new AdomdCommand(); cmd.Connection = conn; //DMX استعلام cmd.CommandText = String.Format(@"select Predict ([Product Name]) from [Customer_ProductsMM] natural prediction join (select '{0}' as [Age], '{1}' as [Cars], '{2}' as [Children], '{3}' as [Commute Distance], '{4}' as [Education], '{5}' as [Gender], '{6}' as [Home Owner], '{7}' as [Income], '{8}' as [Marital Status], '{9}' as [Occupation], '{10}' as [Region]) as t", txtAge.Text, txtcar.Text, txtChildren.Text, DRLCommuteD.SelectedItem, DRLeducation.SelectedItem, RBLGender.SelectedItem, RBLHomeOwner.SelectedItem, txtIncome.Text, DRLMaritalst.SelectedItem, DRLOccupation.SelectedItem, DRLRegion.SelectedItem); //إظهار النتيجة AdomdDataReader adr = cmd.ExecuteReader(); while (adr.Read()) { txtresult.Text = adr.GetString(0); } }
protected void Button1_Click(object sender, EventArgs e) { AdomdConnection con = new AdomdConnection("Data Source = PC; Initial Catalog = ADM_HW"); con.Open(); AdomdCommand com = new AdomdCommand(); com.Connection = con; string s = @"select flattened predicthistogram(disease) from [HeartBMM] natural prediction join (select '" + TextBox1.Text + @"' AS [Age], '" + DropDownList1.SelectedItem.Text + @"' AS [Chest Pain Type], '" + TextBox2.Text + @"' AS [Rest Blood Pressure], '" + DropDownList2.SelectedItem.Text + @"' AS [Blood Sugar], '" + DropDownList4.SelectedItem.Text + @"' AS [Exercice Angina], '" + TextBox3.Text + @"' AS [Max Heart Rate], '" + DropDownList3.SelectedItem.Text + @"' AS [Rest Electro]) AS t"; com.CommandText = s; AdomdDataReader dr = com.ExecuteReader(); while (dr.Read()) { if (dr[0] != null) { Label1.Text += dr[0].ToString() + " " + dr[2].ToString(); } } dr.Close(); con.Close(); }
public static IEnumerable <T> ReturnFromReader <T>(this AdomdDataReader source) where T : new() { var type = typeof(T); var schemaTable = source.GetSchemaTable(); var columnNames = schemaTable.Rows .Cast <DataRow>() .Select(x => x[0].ToString()) .ToArray(); var props = type.GetProperties() .Where(x => System.Attribute.IsDefined(x, typeof(MapToAttribute))) .Select(x => new { Attribute = x.GetCustomAttribute <MapToAttribute>(), PropertyInfo = x }) .Where(x => columnNames.Contains(x.Attribute.MdxColumn)) .OrderBy(x => x.Attribute.MdxColumn); var bindingList = new Dictionary <ParameterExpression, MemberAssignment>(); var converterList = new TypeConverter[props.Count()]; var stringArrayParam = Expression.Parameter(typeof(string[]), "values"); var converterArrayParam = Expression.Parameter(typeof(TypeConverter[]), "converters"); var converter = typeof(TypeConverter).GetMethod("ConvertFromString", new [] { typeof(string) }); props.For((v, i) => { var prop = v.PropertyInfo; var paramExp = Expression.Parameter(prop.PropertyType, prop.Name); var arrayAssignment = Expression.ArrayIndex(stringArrayParam, Expression.Constant(i)); var typeConverter = TypeDescriptor.GetConverter(prop.PropertyType); var converterArrayAssignment = Expression.ArrayIndex(converterArrayParam, Expression.Constant(i)); var defaultValue = prop.PropertyType.GetDefault(); var defaultConstant = Expression.Constant(defaultValue == null ? null : defaultValue.ToString(), typeof(string)); var methodExp = Expression.Call(converterArrayAssignment, converter, Expression.Coalesce(arrayAssignment, defaultConstant)); Expression.Bind(prop, Expression.Convert(methodExp, prop.PropertyType)) .Finally(bind => bindingList.Add(paramExp, bind)); converterList[i] = typeConverter; }); var newExp = Expression.New(typeof(T)); var memberInit = Expression.MemberInit(newExp, bindingList.Values.ToArray()); var lambda = Expression.Lambda <Func <TypeConverter[], string[], T> >(memberInit, new[] { converterArrayParam, stringArrayParam }); var creatorMethod = lambda.Compile(); while (source.Read()) { var rawResults = new string[columnNames.Length]; columnNames .OrderBy(x => x) .For((v, i) => rawResults[i] = source[v].ToString()); yield return(creatorMethod(converterList, rawResults)); } }
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 HttpResponseMessage comparativa(DatosComparar comp) { string ROWS = @"NON EMPTY { [Dim Tiempo].[Dim Tiempo Año].CHILDREN } * { [Dim Tiempo].[Dim Tiempo Mes Siglas].CHILDREN } ON ROWS "; string CUBO_NAME = @"[DWH Northwind] "; string WHERE = ""; string dimension = @""; string coma = ""; foreach (var dato in comp.Nombres) { switch (comp.dimension) { case 1: dimension += coma + @"[Dim Cliente].[Dim Cliente Nombre].&[" + dato + "]"; break; case 2: dimension += coma + @"[Dim Producto].[Dim Producto Nombre].&[" + dato + "]"; break; case 3: dimension += coma + @"[Dim Producto].[Dim Producto Categoria].&[" + dato + "]"; break; case 4: dimension += coma + @"[Dim Empleado].[Dim Empleado Nombre].&[" + dato + "]"; break; } coma = ", "; } string COLUMNS = @" NON EMPTY { [Measures].[Fact Ventas Netas] } * { " + dimension + " } ON COLUMNS,"; string MDXQuery = "SELECT " + COLUMNS + ROWS + "FROM " + CUBO_NAME + WHERE; List <Comparativa> ventasUsuario = new List <Comparativa>(); using (AdomdConnection cnn = new AdomdConnection(ConfigurationManager.ConnectionStrings["CuboNorthwind"].ConnectionString)) { cnn.Open(); using (AdomdCommand cmd = new AdomdCommand(MDXQuery, cnn)) { using (AdomdDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)) { int fi = dr.FieldCount; while (dr.Read()) { Comparativa v = new Comparativa(); List <decimal> listaUsu = new List <decimal>(); v.fecha = dr.GetString(1) + " " + dr.GetString(0); for (int i = 2; i < fi; i++) { decimal valor = dr.IsDBNull(i) ? 0 : dr.GetDecimal(i); listaUsu.Add(valor); } v.Datos = listaUsu; ventasUsuario.Add(v); } dr.Close(); } } } return(Request.CreateResponse(HttpStatusCode.OK, ventasUsuario)); }
public HttpResponseMessage GetDataPieByDimension(string dimension, string order, string[] values) { string elementsToSearch = ""; foreach (var item in values) { elementsToSearch += ($@"{dimension}.&[{item}],"); } elementsToSearch = elementsToSearch.Remove(elementsToSearch.Length - 1); string MDX_QUERY = $@" SELECT ({{ {elementsToSearch} }}) ON ROWS,{{ [Measures].[VENTAS] }}ON COLUMNS FROM [PUBS DW] "; List <string> dim = new List <string>(); List <decimal> ventas = new List <decimal>(); List <dynamic> lstTabla = new List <dynamic>(); dynamic result = new { datosDimension = dim, datosVenta = ventas, datosTabla = lstTabla }; using (AdomdConnection cnn = new AdomdConnection(ConfigurationManager.ConnectionStrings["cubopubs"].ConnectionString)) { cnn.Open(); using (AdomdCommand cmd = new AdomdCommand(MDX_QUERY, cnn)) { //cmd.Parameters.Add("Dimension", valoresDimension); using (AdomdDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)) { while (dr.Read()) { dim.Add(dr.GetString(0)); ventas.Add(dr.GetDecimal(1)); dynamic objTabla = new { descripcion = dr.GetString(0), valor = dr.GetDecimal(1) }; lstTabla.Add(objTabla); } dr.Close(); } } } return(Request.CreateResponse(HttpStatusCode.OK, (object)result)); }
protected void Button1_Click(object sender, EventArgs e) { //الاتصال مع قاعدة البيانات AdomdConnection conn = new AdomdConnection(); conn.ConnectionString = "Data Source=.;Catalog=AnalysisDB"; conn.Open(); //تعريف الاستعلام AdomdCommand cmd = new AdomdCommand(); cmd.Connection = conn; // DMX استعلام cmd.CommandText = String.Format(@"select flattened predicthistogram([Buy]) from [CustomersBayesMM] natural prediction join (select '{0}' as [Age], '{1}' as [Cars], '{2}' as [Children], '{3}' as [Commute Distance], '{4}' as [Education], '{5}' as [Gender], '{6}' as [Home Owner], '{7}' as [Income], '{8}' as [Marital Status], '{9}' as [Occupation], '{10}' as [Region]) as t", txtAge.Text, txtcar.Text, txtChildren.Text, DRLCommuteD.SelectedItem, DRLeducation.SelectedItem, RBLGender.SelectedItem, RBLHomeOwner.SelectedItem, txtIncome.Text, DRLMaritalst.SelectedItem, DRLOccupation.SelectedItem, DRLRegion.SelectedItem); AdomdDataReader adr = cmd.ExecuteReader(); //إظهار النتيجة if (adr.Read()) { txtresult.Text = adr.GetString(0) + " With " + Convert.ToString(Math.Round(Convert.ToDecimal(adr.GetString(2)) * 100, 2)) + " %"; } if (adr.Read()) { txtresult0.Text = adr.GetString(0) + " With " + Convert.ToString(Math.Round(Convert.ToDecimal(adr.GetString(2)) * 100, 2)) + " %"; } }
public HttpResponseMessage Top(Reporte info) { string nombreDimension = @""; switch (info.dimension) { case 1: //Clientes default: nombreDimension = @"{ [Dim Cliente].[Dim Cliente Nombre].CHILDREN } "; break; case 2: //Productos nombreDimension = @"{ [Dim Producto].[Dim Producto Nombre].CHILDREN } "; break; case 3: //Categorias nombreDimension = @"{ [Dim Producto].[Dim Producto Categoria].CHILDREN } "; break; case 4: //Empleadoss nombreDimension = @"{ [Dim Empleado].[Dim Empleado Nombre].CHILDREN } "; break; } string WITH = @"WITH SET [TopVentas] AS NONEMPTY( ORDER (STRTOSET(@Dimension), [Measures].[Fact Ventas Netas], BDESC ) ) "; string COLUMNS = @"NON EMPTY { [Measures].[Fact Ventas Netas] } ON COLUMNS, "; string ROWS = @"NON EMPTY { HEAD([TopVentas], " + info.top + ") } ON ROWS "; string CUBO_NAME = @"[DWH Northwind] "; string MDXQuery = WITH + "SELECT " + COLUMNS + ROWS + "FROM " + CUBO_NAME; List <Venta> ventasUsuario = new List <Venta>(); using (AdomdConnection cnn = new AdomdConnection(ConfigurationManager.ConnectionStrings["CuboNorthwind"].ConnectionString)) { cnn.Open(); using (AdomdCommand cmd = new AdomdCommand(MDXQuery, cnn)) { cmd.Parameters.Add(new AdomdParameter("Dimension", nombreDimension)); using (AdomdDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)) { while (dr.Read()) { Venta v = new Venta(); v.mes = dr.GetString(0); v.ventas = dr.GetDecimal(1); ventasUsuario.Add(v); } dr.Close(); } } }; return(Request.CreateResponse(HttpStatusCode.OK, ventasUsuario)); }
public HttpResponseMessage GetMesDimension() { string WITH = @" WITH SET [OrderDimension] AS NONEMPTY( ORDER( [Dim Tiempo].[Mes Espaniol].CHILDREN, [Dim Tiempo].[Mes Espaniol].CURRENTMEMBER.MEMBER_NAME ) ) "; string COLUMNS = @" NON EMPTY { [Measures].[Hec Ventas Ventas] } ON COLUMNS, "; string ROWS = @" NON EMPTY { [OrderDimension] } ON ROWS "; string CUBO_NAME = "[DWH Northwind]"; string MDX_QUERY = WITH + @"SELECT " + COLUMNS + ROWS + " FROM " + CUBO_NAME; List <string> dimension = new List <string>(); dynamic result = new { datosMeses = dimension, }; using (AdomdConnection cnn = new AdomdConnection(ConfigurationManager.ConnectionStrings["CuboNorthwind"].ConnectionString)) { cnn.Open(); using (AdomdCommand cmd = new AdomdCommand(MDX_QUERY, cnn)) { using (AdomdDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)) { while (dr.Read()) { dimension.Add(dr.GetString(0)); } dr.Close(); } } } return(Request.CreateResponse(HttpStatusCode.OK, (object)result)); }
public static List <AATest> GetDataByCon2(string strMdx) { List <AATest> list = new List <AATest>(); try { if (_connection != null) { if (_connection.State == ConnectionState.Closed) { _connection.Open(); } Stopwatch sw = new Stopwatch(); //开始计时 sw.Start(); AdomdCommand cmd = _connection.CreateCommand(); cmd.CommandText = strMdx; AdomdDataReader reader = cmd.ExecuteReader(); //Execute query while (reader.Read()) // read { AATest dt = new AATest(); // custom class dt.A = reader[0] == null ? "" : reader[0].ToString(); dt.B = reader[1] == null ? "" : reader[1].ToString(); dt.C = reader[2] == null ? "" : reader[2].ToString(); dt.D = reader[3] == null?"":reader[3].ToString(); dt.E = reader[4] == null ? "":reader[4].ToString(); dt.F = reader[5] == null ? "" : reader[5].ToString(); dt.G = reader[6] == null ? "" : reader[6].ToString(); //dt.H = reader[7].ToString(); //dt.I = reader[8].ToString(); //dt.J = reader[9].ToString(); //dt.K = reader[10].ToString(); list.Add(dt); } sw.Stop(); //获取运行时间[毫秒] long times = sw.ElapsedMilliseconds; Console.WriteLine($"耗时2——{times}"); _connection.Close(); } return(list); } catch (Exception ex) { throw ex; } }
static void AddSalesRegionMeasures() { // DAX query to be submitted totabuar database engine String query = "EVALUATE( VALUES(Customers[Sales Region]) )"; AdomdConnection adomdConnection = new AdomdConnection(connectString); adomdConnection.Open(); AdomdCommand adomdCommand = new AdomdCommand(query, adomdConnection); AdomdDataReader reader = adomdCommand.ExecuteReader(); // open connection use TOM to create new measures TOM.Server server = new TOM.Server(); server.Connect(connectString); TOM.Model model = server.Databases[0].Model; TOM.Table salesTable = model.Tables["Sales"]; String measureDescription = "Auto Measures"; // delete any previously created "Auto" measures foreach (TOM.Measure m in salesTable.Measures) { if (m.Description == measureDescription) { salesTable.Measures.Remove(m); model.SaveChanges(); } } // Create the new measures while (reader.Read()) { String SalesRegion = reader.GetValue(0).ToString(); String measureName = $"{SalesRegion} Sales"; TOM.Measure measure = new TOM.Measure() { Name = measureName, Description = measureDescription, DisplayFolder = "Auto Measures", FormatString = "$#,##0", Expression = $@"CALCULATE( SUM(Sales[SalesAmount]), Customers[Sales Region] = ""{SalesRegion}"" )" }; salesTable.Measures.Add(measure); } model.SaveChanges(); reader.Dispose(); adomdConnection.Close(); }
public HttpResponseMessage ventas(Filtro info) { //string WITH = @"WITH SET [TopVentas] AS NONEMPTY( ORDER (STRTOSET(@Dimension), [Measures].[Fact Ventas Netas], BDESC ) ) "; string COLUMNS = @" NON EMPTY { [Measures].[Fact Ventas Netas] } ON COLUMNS,"; string ROWS = @"NON EMPTY { [Dim Tiempo].[Dim Tiempo Año].CHILDREN } * { [Dim Tiempo].[Dim Tiempo Mes Siglas].CHILDREN } ON ROWS "; string CUBO_NAME = @"[DWH Northwind] "; string WHERE = ""; if (info.Item != 0 && info.Nombre != "") { string dimension = @""; switch (info.Item) { case 1: dimension = @"[Dim Cliente].[Dim Cliente Nombre].&[" + info.Nombre + "]"; break; case 2: dimension = @"[Dim Producto].[Dim Producto Nombre].&[" + info.Nombre + "]"; break; case 3: dimension = @"[Dim Producto].[Dim Producto Categoria].&[" + info.Nombre + "]"; break; case 4: dimension = @"[Dim Empleado].[Dim Empleado Nombre].&[" + info.Nombre + "]"; break; } WHERE = " WHERE " + dimension; } string MDXQuery = "SELECT " + COLUMNS + ROWS + "FROM " + CUBO_NAME + WHERE; List <Venta> ventasUsuario = new List <Venta>(); using (AdomdConnection cnn = new AdomdConnection(ConfigurationManager.ConnectionStrings["CuboNorthwind"].ConnectionString)) { cnn.Open(); using (AdomdCommand cmd = new AdomdCommand(MDXQuery, cnn)) { using (AdomdDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)) { while (dr.Read()) { Venta v = new Venta(); //v.ano = ; v.mes = dr.GetString(1) + " " + dr.GetInt16(0); v.ventas = dr.GetDecimal(2); ventasUsuario.Add(v); } dr.Close(); } } } return(Request.CreateResponse(HttpStatusCode.OK, ventasUsuario)); }
public void Visit(MetadataInfo.DaxMetadata daxMetadata) { if (daxMetadata == null) { throw new ArgumentNullException(nameof(daxMetadata)); } string ssasVersion = GetSsasVersion(); Product productInfo = GetProduct(ssasVersion); daxMetadata.Version = new MetadataInfo.SsasVersion { SSAS_VERSION = ssasVersion, CAPTURE_DATE = DateTime.Now, PRODUCT_TYPE = productInfo.Type, PRODUCT_NAME = productInfo.Name }; AdomdDataReader result = _conn.ExecuteReader("SELECT * FROM $SYSTEM.MDSCHEMA_FUNCTIONS"); while (result.Read()) { // Filters only DAX functions int?origin = GetInt(result, result.GetOrdinal("ORIGIN")); if (origin == null) { continue; } if (origin != 3 && origin != 4) { continue; } var function = new MetadataInfo.DaxFunction { SSAS_VERSION = ssasVersion, FUNCTION_NAME = GetString(result, result.GetOrdinal("FUNCTION_NAME")), DESCRIPTION = GetString(result, result.GetOrdinal("DESCRIPTION")), PARAMETER_LIST = GetString(result, result.GetOrdinal("PARAMETER_LIST")), RETURN_TYPE = GetInt(result, result.GetOrdinal("RETURN_TYPE")), ORIGIN = origin, INTERFACE_NAME = GetString(result, result.GetOrdinal("INTERFACE_NAME")), LIBRARY_NAME = GetString(result, result.GetOrdinal("LIBRARY_NAME")), DLL_NAME = GetString(result, result.GetOrdinal("DLL_NAME")), HELP_FILE = GetString(result, result.GetOrdinal("HELP_FILE")), HELP_CONTEXT = GetInt(result, result.GetOrdinal("HELP_CONTEXT")), OBJECT = GetString(result, result.GetOrdinal("OBJECT")), CAPTION = GetString(result, result.GetOrdinal("CAPTION")), PARAMETERINFO = GetXmlString(result, result.GetOrdinal("PARAMETERINFO")), DIRECTQUERY_PUSHABLE = (result.FieldCount >= 14 ? GetInt(result, result.GetOrdinal("DIRECTQUERY_PUSHABLE")) : null) }; daxMetadata.DaxFunctions.Add(function); } }
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()); }
public List <string> predict(List <string> input) { List <string> output = new List <string>(); //Connection AdomdConnection CON = new AdomdConnection( "Data Source=.;Catalog=AnalysisDB"); CON.Open(); // command AdomdCommand COM = CON.CreateCommand(); // Query Command to Retrive Result string s = @"SELECT Flattened PREDICT([Orders Details],3) FROM [OrdersMM] NATURAL PREDICTION JOIN (SELECT ( "; foreach (string x in input) { if (input.IndexOf(x) > 0) { s += " Union "; } s += "Select '" + x + "' as [Product Name]"; } s += " ) AS [Orders Details]) As T"; COM.CommandText = s; // Read result AdomdDataReader DR = COM.ExecuteReader(); while (DR.Read()) { if (DR[0] != null) { output.Add(DR[0].ToString()); } } DR.Close(); CON.Close(); return(output); }
public HttpResponseMessage comparativausu(Reporte num) { string COLUMNS = @" NON EMPTY [Measures].[Fact Ventas Netas] ON COLUMNS,"; string CUBO_NAME = @"[DWH Northwind] "; string WHERE = ""; string dimension = @""; switch (num.dimension) { case 1: dimension = @"[Dim Cliente].[Dim Cliente Nombre].CHILDREN"; break; case 2: dimension = @"[Dim Producto].[Dim Producto Nombre].CHILDREN"; break; case 3: dimension = @"[Dim Producto].[Dim Producto Categoria].CHILDREN"; break; case 4: dimension = @"[Dim Empleado].[Dim Empleado Nombre].CHILDREN"; break; } string ROWS = @" NON EMPTY " + dimension + " ON ROWS"; //WHERE = " WHERE " + dimension; string MDXQuery = "SELECT " + COLUMNS + ROWS + " FROM " + CUBO_NAME; List <usuario> ventasUsuario = new List <usuario>(); using (AdomdConnection cnn = new AdomdConnection(ConfigurationManager.ConnectionStrings["CuboNorthwind"].ConnectionString)) { cnn.Open(); using (AdomdCommand cmd = new AdomdCommand(MDXQuery, cnn)) { using (AdomdDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)) { while (dr.Read()) { usuario u = new usuario(); //v.ano = ; u.nombre = dr.GetString(0); ventasUsuario.Add(u); } dr.Close(); } } } return(Request.CreateResponse(HttpStatusCode.OK, ventasUsuario)); }
/// <summary> /// Returns a dictionary of row label to value retrieved from a query (command) applied to a cube (CubeInfo). /// </summary> /// <param name="cubeInfo"></param> /// <param name="command"></param> /// <returns></returns> public Dictionary <string, double> GetSlicedMeasureData(CubeInfo cubeInfo, string command) { Dictionary <string, double> datalist = new Dictionary <string, double>(); string connection = cubeInfo.getStandardConnectionString(); try { using (AdomdConnection conn = new AdomdConnection(connection)) { conn.Open(); using (AdomdCommand cmd = conn.CreateCommand()) { cmd.CommandText = command; using (AdomdDataReader result = cmd.ExecuteReader()) { while (result.Read()) { string label = ""; if (!result.IsDBNull(0)) { label = result[0].ToString(); } double value = 0; if (!result.IsDBNull(2)) { value = result.GetDouble(2); } datalist.Add(label, value); } } } conn.Close(); } } catch (AdomdErrorResponseException aere) { Utility.WriteToConsole(string.Format(Constants.invalid, aere.Message), 100); } return(datalist); }
//</snippetReturnCommandUsingCellSet> //<snippetOutputCommandWithDataReader> void OutputCommandWithDataReader() { //Connect to the local server AdomdConnection conn = new AdomdConnection("Data Source=localhost"); conn.Open(); //Create a command to send to the server. AdomdCommand cmd = new AdomdCommand(@"WITH MEMBER [Measures].[FreightCostPerOrder] AS [Measures].[Reseller Freight Cost]/[Measures].[Reseller Order Quantity], FORMAT_STRING = 'Currency' SELECT [Geography].[Geography].[Country].&[United States].Children ON ROWS, [Date].[Calendar].[Calendar Year] ON COLUMNS FROM [Adventure Works] WHERE [Measures].[FreightCostPerOrder]", conn); //Execute the command, returning an AdomdDataReader AdomdDataReader reader = cmd.ExecuteReader(); //Retrieve the schema information about the AdomdDataReader System.Data.DataTable schema = reader.GetSchemaTable(); //Cycle through the schema, printing out the //ColumnName information as column headers. for (int n = 0; n < schema.Rows.Count; n++) { Console.Write(schema.Rows[n]["ColumnName"] + "\t"); } Console.WriteLine(); //Loop through the reader, outputting each cell's value. while (reader.Read()) { for (int n = 0; n < reader.FieldCount; n++) { Console.Write(reader.GetValue(n) + "\t"); } Console.WriteLine(); } //Close the connection and await user input. conn.Close(); Console.ReadLine(); }
public void Run <TKey, TValue>(string mdxScript, IDictionary <TKey, TValue> result, Func <AdomdDataReader, String[], TKey> mapperKey, Func <AdomdDataReader, TValue> mapperValue, String[] args) { try { AdomdCommand cmd = new AdomdCommand(mdxScript, conn); using (AdomdDataReader dr = cmd.ExecuteReader()) { while (dr.Read()) { result.Add(mapperKey(dr, args), mapperValue(dr)); } } } catch (AdomdErrorResponseException e) { Console.WriteLine(e.Message); } }
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++; } }