public static string GetXml(string input, PipelineCommand command, ExecutionLog log)
{
var sql = command.GetArgument("sql,proc", input);
var connectionInfo = command.GetArgument("connection");
log.AddMessage($"Allowed connection strings are: {Pipeline.GetGlobalVariable(ALLOWED_CONNECTION_STRINGS_VARIABLE_NAME)}");
IsValidConnectionStringName(connectionInfo);
var sqlCommand = ConfigureCommand(connectionInfo, command, log);
// If this isn't a stored proc, then we need to append the XML stuff to it. If it is a stored proc, when we assume that's already there.
if (!command.HasArgument("proc"))
{
sqlCommand.CommandText = String.Concat(sql, " FOR XML PATH, ROOT('rows'), ELEMENTS XSINIL");
}
else
{
sqlCommand.CommandText = sql;
}
var xml = "<rows/>";
try
{
var reader = sqlCommand.ExecuteXmlReader();
log.AddMessage($"Successful SQL execution in {sqlCommand.Connection.RetrieveStatistics()["ExecutionTime"]}ms");
reader.Read();
var rawResult = reader.ReadOuterXml();
if (!String.IsNullOrWhiteSpace(rawResult))
{
xml = rawResult;
}
var xmlDoc = new XmlDocument();
try
{
xmlDoc.LoadXml(xml);
}
catch (Exception e)
{
throw new DeninaException("Unable to parse XML response.", e);
}
}
catch (Exception e)
{
throw new DeninaException("Error executing SQL", e);
}
return(String.IsNullOrWhiteSpace(xml) ? ">" : xml);
}
public static string WrapInTag(string input, PipelineCommand command)
{
var stringWriter = new StringWriter();
var tagBuilder = new HtmlTextWriter(stringWriter);
// The second argument should be the class
if (command.HasArgument("class"))
{
tagBuilder.AddAttribute("class", command.GetArgument("class"));
}
// The third argument should be the id
if (command.HasArgument("id"))
{
tagBuilder.AddAttribute("id", command.GetArgument("id"));
}
tagBuilder.RenderBeginTag(command.GetArgument("tag"));
tagBuilder.Write(input);
tagBuilder.RenderEndTag();
return(stringWriter.ToString());
}
public static string GetXml(string input, PipelineCommand command)
{
var sql = command.GetArgument("sql,proc", input);
var connectionStringName = command.GetArgument("connection");
IsValidConnectionStringName(connectionStringName);
var sqlCommand = ConfigureCommand(connectionStringName, command);
// If this isn't a stored proc, then we need to append the XML stuff to it. If it is a stored proc, when we assume that's already there.
if (!command.HasArgument("proc"))
{
sqlCommand.CommandText = String.Concat(sql, " FOR XML PATH, ROOT('rows'), ELEMENTS XSINIL");
}
else
{
sqlCommand.CommandText = sql;
}
var xml = "<rows/>";
try
{
var reader = sqlCommand.ExecuteXmlReader();
reader.Read();
var rawResult = reader.ReadOuterXml();
if (!String.IsNullOrWhiteSpace(rawResult))
{
xml = rawResult;
}
var xmlDoc = new XmlDocument();
try
{
xmlDoc.LoadXml(xml);
}
catch (Exception e)
{
throw new DeninaException("Unable to parse XML response.", e);
}
}
catch (Exception e)
{
throw new DeninaException("Error executing SQL", e);
}
return(String.IsNullOrWhiteSpace(xml) ? ">" : xml);
}
public static string GetTable(string input, PipelineCommand command)
{
var sql = input;
var connectionStringName = command.GetArgument("connection");
// If they passed in a SQL argument, use it
if (command.HasArgument("sql"))
{
sql = command.GetArgument("sql");
}
IsValidConnectionStringName(connectionStringName);
var sqlCommand = ConfigureCommand(connectionStringName, command);
sqlCommand.CommandText = sql;
SqlDataReader reader;
try
{
reader = sqlCommand.ExecuteReader();
}
catch (Exception e)
{
throw new DeninaException("Error executing SQL", e);
}
var html = new StringWriter();
var writer = new HtmlTextWriter(html);
if (command.HasArgument("class"))
{
writer.AddAttribute("class", command.GetArgument("class"));
}
writer.RenderBeginTag(HtmlTextWriterTag.Table);
writer.RenderBeginTag(HtmlTextWriterTag.Thead);
writer.RenderBeginTag(HtmlTextWriterTag.Tr);
var columns = Enumerable.Range(0, reader.FieldCount).Select(reader.GetName).ToList();
foreach (var column in columns)
{
var columnClass = column.ToLower().Replace(" ", "-");
writer.AddAttribute("class", String.Concat("column-", columnClass));
writer.RenderBeginTag(HtmlTextWriterTag.Th);
writer.Write(column);
writer.RenderEndTag();
}
writer.RenderEndTag(); // Ends tr
writer.RenderEndTag(); // Ends thead
writer.RenderBeginTag(HtmlTextWriterTag.Tbody);
if (reader.HasRows)
{
while (reader.Read())
{
writer.RenderBeginTag(HtmlTextWriterTag.Tr);
for (int i = 0; i < reader.FieldCount; i++)
{
var value = reader.IsDBNull(i) ? String.Empty : Convert.ToString(reader[i]);
var columnClass = reader.GetName(i).ToLower().Replace(" ", "-");
writer.AddAttribute("class", String.Concat("column-", columnClass));
writer.RenderBeginTag(HtmlTextWriterTag.Td);
writer.Write(StringUtilities.Link(value));
writer.RenderEndTag(); // Ends td
}
writer.RenderEndTag(); // Ends tr
}
}
writer.RenderEndTag(); // Ends tbody
writer.RenderEndTag(); // End table
return(html.ToString());
}
public static string Transform(string input, PipelineCommand command)
{
var xml = string.Empty;
var xsl = command.GetArgument("xslt");
// This adds an extension object for XSL transforms
var arguments = new XsltArgumentList();
arguments.AddExtensionObject("http://denina", new XsltExtensions());
// Do we want to pass in a custom extension object?
if (Pipeline.IsSetGlobally(XSLT_ARGUMENT_VARIABLE_NAME))
{
var namespaceName = "ext";
var className = Pipeline.GetGlobalVariable(XSLT_ARGUMENT_VARIABLE_NAME).ToString();
// If they want to specify a custom namespace via "=", split and reassign the namespace and class names.
if (className.Contains('='))
{
namespaceName = className.Split('=').First();
className = className.Split('=').Last();
}
// Try to get the object
ObjectHandle extensionObject;
try
{
extensionObject = Activator.CreateInstance(className.Split(',').Last(), className.Split(',').First());
}
catch (Exception e)
{
throw new DeninaException(String.Format("Unable to load XsltExtension object \"{0}\"", className), e);
}
arguments.AddExtensionObject(String.Concat("http://", namespaceName), extensionObject.Unwrap());
}
// If there are two arguments, assume the second is XML
if (command.HasArgument("xml"))
{
xml = command.GetArgument("xml");
}
else
{
// Otherwise, the XML is the input
xml = input;
}
// Set some basic parameters for our XML reading...
var settings = new XmlReaderSettings()
{
IgnoreComments = true,
IgnoreProcessingInstructions = true,
IgnoreWhitespace = true
};
// Form the XML doc from the input
XmlReader xmlReader;
try
{
xmlReader = XmlReader.Create(new StringReader(xml), settings);
}
catch (Exception e)
{
throw new DeninaException("Error parsing XML.", e);
}
// Form the XSL from the first argument
XslCompiledTransform transform;
try
{
transform = new XslCompiledTransform();
transform.Load(XmlReader.Create(new StringReader(xsl), settings));
}
catch (Exception e)
{
throw new DeninaException("Error parsing XSL.", e);
}
// Do the transform (we're passing in an empty XsltArgumentList as a placeholder, in case we want to do something with it later...)
var writer = new StringWriter();
try
{
transform.Transform(xmlReader, arguments, writer);
}
catch (Exception e)
{
throw new DeninaException("Error performing XSLT transform.", e);
}
return(writer.ToString().Replace("\u00A0", " ")); // This is a bit of a hack. We're replacing NO BREAK SPACE with a regular space. There has to be a way to fix this in the XSLT output.
}
public static string GetTable(string input, PipelineCommand command, ExecutionLog log)
{
var sql = input;
var connectionInfo = command.GetArgument("connection");
// If they passed in a SQL argument, use it
if (command.HasArgument("sql"))
{
sql = command.GetArgument("sql");
}
log.AddMessage($"Allowed connection strings are: {Pipeline.GetGlobalVariable(ALLOWED_CONNECTION_STRINGS_VARIABLE_NAME)}");
IsValidConnectionStringName(connectionInfo);
var sqlCommand = ConfigureCommand(connectionInfo, command, log);
sqlCommand.CommandText = sql;
SqlDataReader reader;
try
{
reader = sqlCommand.ExecuteReader();
log.AddMessage($"Successful SQL execution in {sqlCommand.Connection.RetrieveStatistics()["ExecutionTime"]}ms");
}
catch (Exception e)
{
throw new DeninaException("Error executing SQL", e);
}
var html = new StringWriter();
var writer = new HtmlTextWriter(html);
if (command.HasArgument("class"))
{
writer.AddAttribute("class", command.GetArgument("class"));
}
writer.RenderBeginTag(HtmlTextWriterTag.Table);
writer.RenderBeginTag(HtmlTextWriterTag.Thead);
writer.RenderBeginTag(HtmlTextWriterTag.Tr);
var columns = Enumerable.Range(0, reader.FieldCount).Select(reader.GetName).ToList();
foreach (var column in columns)
{
var columnClass = column.ToLower().Replace(" ", "-");
writer.AddAttribute("class", String.Concat("column-", columnClass));
writer.RenderBeginTag(HtmlTextWriterTag.Th);
writer.Write(column);
writer.RenderEndTag();
}
writer.RenderEndTag(); // Ends tr
writer.RenderEndTag(); // Ends thead
writer.RenderBeginTag(HtmlTextWriterTag.Tbody);
if (reader.HasRows)
{
while (reader.Read())
{
writer.RenderBeginTag(HtmlTextWriterTag.Tr);
for (int i = 0; i < reader.FieldCount; i++)
{
var value = reader.IsDBNull(i) ? String.Empty : Convert.ToString(reader[i]);
var columnClass = reader.GetName(i).ToLower().Replace(" ", "-");
writer.AddAttribute("class", String.Concat("column-", columnClass));
writer.RenderBeginTag(HtmlTextWriterTag.Td);
writer.Write(StringUtilities.Link(value));
writer.RenderEndTag(); // Ends td
}
writer.RenderEndTag(); // Ends tr
}
}
writer.RenderEndTag(); // Ends tbody
writer.RenderEndTag(); // End table
return(html.ToString());
}
