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