private static SqlCommand ConfigureCommand(string connectionInfo, PipelineCommand command, ExecutionLog log)
{
// Determine if we have an actual connection string, or a connection string name
var connectionString = IsConnectionStringName(connectionInfo) ? ConfigurationManager.ConnectionStrings[connectionInfo].ToString() : connectionInfo;
log.AddMessage(connectionString != connectionInfo ? "Connection argument is a valid, authorized connection string key" : "Connection argument is not a connection string name; assuming a raw connection string");
var sqlCommand = new SqlCommand()
{
Connection = new SqlConnection(connectionString)
};
sqlCommand.Connection.StatisticsEnabled = true;
// Add all variables as params
foreach (var variable in command.Pipeline.Variables.Where(v => v.Key != Pipeline.GLOBAL_VARIABLE_NAME))
{
sqlCommand.Parameters.AddWithValue(variable.Key, variable.Value.Value ?? String.Empty);
log.AddMessage($"Added SQL param: \"{variable.Key}\"");
}
try
{
sqlCommand.Connection.Open();
}
catch (Exception e)
{
throw new DeninaException("Error connecting to SQL Server with connection string \"" + connectionInfo + "\"", e);
}
return(sqlCommand);
}
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 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());
}
