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