public async Task <HttpResponseMessage> PostRawBufferManual() { string connStr = ""; string loc = ""; try { string request = await Request.Content.ReadAsStringAsync(); var addin = Globals.ThisAddIn; var app = addin.Application; var wb = app.ActiveWorkbook; loc = wb.FullName; //@"D:\Data\Presentations\Drop Your DAX\demos\02 DAX filter similar.xlsx"; // parse request looking for workbook name in Workstation ID // we are using the Workstation ID property to tunnel the location property through // from the UI to the PowerPivot engine. The Location property does not appear to get // serialized through into the XMLA request so we "hijack" the Workstation ID var wsid = ParseRequestForWorkstationID(request); if (!string.IsNullOrEmpty(wsid)) { Log.Debug("{class} {method} {message}", "XmlaController", "PostRawBufferManual", "Resetting Location based on WorkstationID to: " + loc); loc = wsid; } connStr = string.Format("Provider=MSOLAP;Persist Security Info=True;Initial Catalog=Microsoft_SQLServer_AnalysisServices;Data Source=$Embedded$;MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error;Subqueries=0;Optimize Response=7;Location=\"{0}\"", loc); //connStr = string.Format("Provider=MSOLAP;Persist Security Info=True;Data Source=$Embedded$;MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error;Subqueries=0;Optimize Response=7;Location={0}", loc); // 2010 conn str //connStr = string.Format("Provider=MSOLAP.5;Persist Security Info=True;Initial Catalog=Microsoft_SQLServer_AnalysisServices;Data Source=$Embedded$;MDX Compatibility=1;Safety Options=2;ConnectTo=11.0;MDX Missing Member Mode=Error;Optimize Response=3;Cell Error Mode=TextValue;Location={0}", loc); //connStr = string.Format("Provider=MSOLAP;Data Source=$Embedded$;MDX Compatibility=1;Safety Options=2;ConnectTo=11.0;MDX Missing Member Mode=Error;Optimize Response=3;Location={0};", loc); Log.Debug("{class} {method} {message}", "XmlaController", "PostRawBufferManual", "About to Load AmoWrapper"); AmoWrapper.AmoType amoType = AmoWrapper.AmoType.AnalysisServices; if (float.Parse(app.Version, CultureInfo.InvariantCulture) >= EXCEL_2013) { amoType = AmoWrapper.AmoType.Excel; Log.Debug("{class} {method} {message}", "XmlaController", "PostRawBufferManual", "Loading Microsoft.Excel.Amo"); } else { Log.Debug("{class} {method} {message}", "XmlaController", "PostRawBufferManual", "defaulting to Microsoft.AnalysisServices"); } var svr = new AmoWrapper.AmoServer(amoType); svr.Connect(connStr); // STEP 1: send the request to server. Log.Verbose("{class} {method} request: {request}", "XmlaController", "PostRawBufferManual", request); System.IO.TextReader streamWithXmlaRequest = new StringReader(request); System.Xml.XmlReader xmlaResponseFromServer = null; // will be used to parse the XML/A response from server string fullEnvelopeResponseFromServer = ""; try { //xmlaResponseFromServer = svr.SendXmlaRequest( XmlaRequestType.Undefined, streamWithXmlaRequest); xmlaResponseFromServer = svr.SendXmlaRequest(streamWithXmlaRequest); // STEP 2: read/parse the XML/A response from server. xmlaResponseFromServer.MoveToContent(); fullEnvelopeResponseFromServer = xmlaResponseFromServer.ReadOuterXml(); } catch (Exception ex) { Log.Error("ERROR sending response: {class} {method} {exception}", "XmlaController", "PostRawBufferManual", ex); //result = new HttpResponseMessage(HttpStatusCode.InternalServerError); //result.Content = new StringContent(String.Format("An unexpected error occurred (sending XMLA request): \n{0}", ex.Message)); } finally { streamWithXmlaRequest.Close(); } HttpResponseMessage result; try { result = new HttpResponseMessage(HttpStatusCode.OK); result.Content = new StringContent(fullEnvelopeResponseFromServer); result.Content.Headers.ContentType = new MediaTypeHeaderValue("text/xml"); result.Headers.TransferEncodingChunked = true; } catch (Exception ex) { Log.Error("ERROR sending response: {class} {method} {exception}", "XmlaController", "PostRawBufferManual", ex); result = new HttpResponseMessage(HttpStatusCode.InternalServerError); result.Content = new StringContent(String.Format("An unexpected error occurred (reading XMLA response): \n{0}", ex.Message)); } finally { // STEP 3: close the System.Xml.XmlReader, to release the connection for future use. if (xmlaResponseFromServer != null) { xmlaResponseFromServer.Close(); } } return(result); } catch (Exception ex) { Log.Error("ERROR Connecting: {class} {method} loc: '{loc}' conn:'{connStr}' ex: {exception}", "XmlaController", "PostRawBufferManual", loc, connStr, ex); var expResult = new HttpResponseMessage(HttpStatusCode.InternalServerError); expResult.Content = new StringContent(String.Format("An unexpected error occurred: \n{0}", ex.Message)); return(expResult); } }
public async Task<HttpResponseMessage> PostRawBufferManual() { string connStr = ""; string loc = ""; try { string request = await Request.Content.ReadAsStringAsync(); var addin = Globals.ThisAddIn; var app = addin.Application; var wb = app.ActiveWorkbook; loc = wb.FullName; //@"D:\Data\Presentations\Drop Your DAX\demos\02 DAX filter similar.xlsx"; connStr = string.Format("Provider=MSOLAP;Persist Security Info=True;Initial Catalog=Microsoft_SQLServer_AnalysisServices;Data Source=$Embedded$;MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error;Subqueries=0;Optimize Response=7;Location=\"{0}\"", loc); //connStr = string.Format("Provider=MSOLAP;Persist Security Info=True;Data Source=$Embedded$;MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error;Subqueries=0;Optimize Response=7;Location={0}", loc); // 2010 conn str //connStr = string.Format("Provider=MSOLAP.5;Persist Security Info=True;Initial Catalog=Microsoft_SQLServer_AnalysisServices;Data Source=$Embedded$;MDX Compatibility=1;Safety Options=2;ConnectTo=11.0;MDX Missing Member Mode=Error;Optimize Response=3;Cell Error Mode=TextValue;Location={0}", loc); //connStr = string.Format("Provider=MSOLAP;Data Source=$Embedded$;MDX Compatibility=1;Safety Options=2;ConnectTo=11.0;MDX Missing Member Mode=Error;Optimize Response=3;Location={0};", loc); Log.Debug("{class} {method} {message}", "XmlaController", "PostRawBufferManual", "defaulting to Microsoft.AnalysisServices"); AmoWrapper.AmoType amoType = AmoWrapper.AmoType.AnalysisServices; if (float.Parse(app.Version,CultureInfo.InvariantCulture) >= 15) { amoType = AmoWrapper.AmoType.Excel; Log.Debug("{class} {method} {message}", "XmlaController", "PostRawBufferManual", "Loading Microsoft.Excel.Amo"); } var svr = new AmoWrapper.AmoServer(amoType); svr.Connect(connStr); // STEP 1: send the request to server. Log.Verbose("{class} {method} request: {request}", "XmlaController", "PostRawBufferManual", request); System.IO.TextReader streamWithXmlaRequest = new StringReader(request); System.Xml.XmlReader xmlaResponseFromServer=null; // will be used to parse the XML/A response from server string fullEnvelopeResponseFromServer = ""; try { xmlaResponseFromServer = svr.SendXmlaRequest( XmlaRequestType.Undefined, streamWithXmlaRequest); // STEP 2: read/parse the XML/A response from server. xmlaResponseFromServer.MoveToContent(); fullEnvelopeResponseFromServer = xmlaResponseFromServer.ReadOuterXml(); } catch (Exception ex) { Log.Error("ERROR sending response: {class} {method} {exception}", "XmlaController", "PostRawBufferManual", ex); //result = new HttpResponseMessage(HttpStatusCode.InternalServerError); //result.Content = new StringContent(String.Format("An unexpected error occurred (sending XMLA request): \n{0}", ex.Message)); } finally { streamWithXmlaRequest.Close(); } HttpResponseMessage result; try { result = new HttpResponseMessage(HttpStatusCode.OK); result.Content = new StringContent(fullEnvelopeResponseFromServer); result.Content.Headers.ContentType = new MediaTypeHeaderValue("text/xml"); result.Headers.TransferEncodingChunked = true; } catch (Exception ex) { Log.Error("ERROR sending response: {class} {method} {exception}", "XmlaController", "PostRawBufferManual", ex); result = new HttpResponseMessage(HttpStatusCode.InternalServerError); result.Content = new StringContent(String.Format("An unexpected error occurred (reading XMLA response): \n{0}", ex.Message)); } finally { // STEP 3: close the System.Xml.XmlReader, to release the connection for future use. if (xmlaResponseFromServer != null) { xmlaResponseFromServer.Close(); } } return result; } catch (Exception ex) { Log.Error("ERROR Connecting: {class} {method} loc: '{loc}' conn:'{connStr}' ex: {exception}", "XmlaController", "PostRawBufferManual", loc, connStr, ex); var expResult = new HttpResponseMessage(HttpStatusCode.InternalServerError); expResult.Content = new StringContent(String.Format("An unexpected error occurred: \n{0}", ex.Message)); return expResult; } }