public static SettingsModel SaveExportMapping(AuthenticatedUserManager user, string reportPath, string procName, string displayOption) { SettingsModel.ReportPath = string.Empty; ExportMapManager manager = new ExportMapManager(user); BOExportMap exportMap = new BOExportMap(); exportMap.ProcName = procName; exportMap.ReportPath = reportPath; exportMap.DisplayOptions = displayOption; exportMap.DataSourcePath = manager.LookupDataSourceFromRdl(reportPath); exportMap.Save(user.AuthenticatedUserSignature); return SettingsModel.GetExportMappings(user); }
public static SettingsModel DeleteExportMapping(AuthenticatedUserManager user, string reportPath) { SettingsModel.ReportPath = string.Empty; BOExportMap exportMap = new BOExportMap(); ExportMapManager manager = new ExportMapManager(user); exportMap = manager.FindAssociatedExportMap(reportPath, false); exportMap.Delete(); return SettingsModel.GetExportMappings(user); }
private Hashtable lookupStoredProcParametersFromRdl(BOExportMap exportMap) { Hashtable storedProcParameters = new Hashtable(); // Get Report RDL byte[] rdl = _owner.ReportingServiceNonHttpClient.GetReportDefinition(exportMap.ReportPath); // Parse RDL to find proc definition -- need to find out which DS is the primary one. XmlDocument rdlDocument = new XmlDocument(); rdlDocument.Load(new MemoryStream(rdl)); // RDL is a well-formed XML doc, add a namespace manager so we can use XPath to search it XmlNamespaceManager nsmgr = new XmlNamespaceManager(rdlDocument.NameTable); nsmgr.AddNamespace("default", "http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition"); XmlNodeList dataSetNodeList = rdlDocument.DocumentElement.SelectNodes("//default:Report/default:DataSets/default:DataSet", nsmgr); foreach (XmlNode dataSetNode in dataSetNodeList) { bool foundMainDataSet = false; bool isStoredProc = false; bool isDWDataSource = false; bool isCorrectStoredProc = false; XmlNode queryCommandTypeNode = dataSetNode.SelectSingleNode("./default:Query/default:CommandType", nsmgr); if (queryCommandTypeNode != null) { if (queryCommandTypeNode.InnerText.Equals("StoredProcedure", StringComparison.OrdinalIgnoreCase)) { isStoredProc = true; } } XmlNode queryDataSourceNameNode = dataSetNode.SelectSingleNode("./default:Query/default:DataSourceName", nsmgr); if (queryDataSourceNameNode != null) { if (queryDataSourceNameNode.InnerText.Equals("DataWarehouse", StringComparison.OrdinalIgnoreCase)) { isDWDataSource = true; } } if (isStoredProc) { XmlNode queryCommandTextNode = dataSetNode.SelectSingleNode("./default:Query/default:CommandText", nsmgr); if (queryCommandTextNode != null) { string storedProcName = queryCommandTextNode.InnerText.Trim().Replace("[", null).Replace("]", null); if (exportMap.ProcName.Equals(storedProcName, StringComparison.OrdinalIgnoreCase)) { isCorrectStoredProc = true; } } } if ((isStoredProc) && (isDWDataSource) && (isCorrectStoredProc)) { foundMainDataSet = true; } if (foundMainDataSet) { XmlNodeList queryCommandParameterNodeList = dataSetNode.SelectNodes("./default:Query/default:QueryParameters/default:QueryParameter", nsmgr); if (queryCommandParameterNodeList != null) { foreach (XmlNode queryCommandParameterNode in queryCommandParameterNodeList) { string paramName = ((XmlElement)queryCommandParameterNode).Attributes["Name"].Value; string paramValue = string.Empty; foreach (XmlNode parameterChildNode in queryCommandParameterNode.ChildNodes) { if (parameterChildNode.Name == "Value") { paramValue = parameterChildNode.InnerText; } } storedProcParameters.Add(paramName, paramValue); } } break; } } return storedProcParameters; }
/// <summary> /// This method executes the stored procedure defined in this class via a data reader. /// The parameters from this stored procedure are a combination of the values selected by /// the user through the shell, and the information found in the RDL defined by _reportPath /// </summary> /// <param name="user">The currently authenticated NewSkies user</param> /// <param name="paramArgTable">Table of name value pairs of parameters to be used for the current execution. User input from the shell, rolled-dates + user input for subscriptions</param> /// <param name="useExternalHandler">Flag that indicates whether the export will be handled by an event outside of this method, or if the method needs to generate the results</param> /// <returns></returns> public byte[] ExportToCSV(BOExportMap exportMap, NParameterValueCollection parameterValueCollection, IFormatProvider cultureInfo, bool forceError, string strUser, string strCSVDelimiter) { byte[] results = null; CultureInfo subscriptionCultureInfo = (CultureInfo)cultureInfo; // Find the connection string for this exportMap string connString = lookupConfiguredConnectionString(exportMap); // Get the Stored Proc Parameters from the RDL Hashtable storedProcParameters = lookupStoredProcParametersFromRdl(exportMap); // Task 176511 - Applied the combinedMethod for multi-select values NParameterValueCollection combinedParameterValue; combinedParameterValue = combinedMethod(parameterValueCollection); Hashtable newProcArgs; try { // Merge the args from the parameters control with the ones from the stored proc in // the RDL newProcArgs = mergeParameters(storedProcParameters, combinedParameterValue, subscriptionCultureInfo, strUser); } catch { // Merge the args from the parameters control with the ones from the stored proc in // the RDL newProcArgs = mergeParameters(storedProcParameters, parameterValueCollection, subscriptionCultureInfo, strUser); } // Build the argument list used when calling the proc string argList = buildCommandArguments(newProcArgs); using (SqlConnection sqlConnection = new SqlConnection(connString)) { try { sqlConnection.Open(); using (SqlCommand sqlCommand = new SqlCommand()) { sqlCommand.Connection = sqlConnection; sqlCommand.CommandType = CommandType.Text; sqlCommand.CommandTimeout = 18000; // Wait for up to 5 hours sqlCommand.CommandText = string.Format(CultureInfo.InvariantCulture, "EXEC {0} {1}", exportMap.ProcName, argList); if (forceError) { // Force error is used for debugging. It returns the text of the query // to the caller as the message of the exception. throw new DiagnosisException(sqlCommand.CommandText); } using (SqlDataReader sqlDataReader = sqlCommand.ExecuteReader(CommandBehavior.CloseConnection)) { try { StringBuilder csvBuilder = new StringBuilder(); // Header DataTable schemaTable = sqlDataReader.GetSchemaTable(); for (int rowNdx = 0; rowNdx < schemaTable.Rows.Count; rowNdx++) { DataRow schemaRow = schemaTable.Rows[rowNdx]; csvBuilder.Append(schemaRow["ColumnName"]); // Don't insert a comma at the end of the line if (rowNdx < schemaTable.Rows.Count - 1) { //Task200712 - Applied the CSV delimiter based on the app setting csvBuilder.Append(strCSVDelimiter); } else { csvBuilder.Append(Environment.NewLine); } } // Data while (sqlDataReader.Read()) { for (int colNdx = 0; colNdx < sqlDataReader.FieldCount; colNdx++) { string colVal = string.Empty; Type colType = sqlDataReader[colNdx].GetType(); if (colType == typeof(DateTime)) { colVal = ((DateTime)sqlDataReader[colNdx]).ToString(cultureInfo); } else { colVal = sqlDataReader[colNdx].ToString(); } // Escape any fields that contains commas if (colVal.Contains(strCSVDelimiter)) { colVal = string.Format(CultureInfo.InvariantCulture, "\"{0}\"", colVal); } csvBuilder.Append(colVal); // Don't insert a comma at the end of the line if (colNdx < sqlDataReader.FieldCount - 1) { //Task200712 - Applied the CSV delimiter based on the app setting csvBuilder.Append(strCSVDelimiter); } else { csvBuilder.Append(Environment.NewLine); } } } results = Encoding.Default.GetBytes(csvBuilder.ToString()); } finally { sqlDataReader.Close(); } } } } finally { sqlConnection.Close(); } } return results; }
private string lookupConfiguredConnectionString(BOExportMap exportMap) { // Find the datasource to make the connection to the DB BOConfiguredDataSource configuredDataSource = _owner.CatalogInteractionManager.ConfiguredDataSourceCollection[exportMap.DataSourcePath]; if (configuredDataSource == null) { throw new InvalidConfigurationException("The datasource for this report has not been configured for the application."); } // Remove any trailing semi-colons so we can append the credentials to the connection string string trimmedConnectionString = configuredDataSource.ConnectionStringValue.TrimEnd(new char[] { ';' }); // Decrypt the credentials stored in the DB string plainCrednetials = Utility.DecryptValue(configuredDataSource.Credentials); // Put the pieces together return string.Format(CultureInfo.InvariantCulture, "{0};{1}", trimmedConnectionString, plainCrednetials); }