private void DoExport(object sender, EventArgs e)
{
var translator = new Translator("ExplainPlan");
using (var dialog = new SaveFileDialog())
{
dialog.DefaultExt = "htm";
dialog.Filter = translator.GetString("SavePlanDialogFilter");
dialog.InitialDirectory = UserOptions.GetString("general/queryPath");
dialog.Title = translator.GetString("SavePlanDialogTitle");
translator = null;
DialogResult result = dialog.ShowDialog(this);
if (result == DialogResult.OK)
{
using (var file = new StreamWriter(dialog.FileName, false))
{
file.WriteLine(browser.DocumentText);
file.Close();
}
}
}
}
/// <summary>
/// Updates the control to reflect any recent changes to the User Options.
/// </summary>
public void SetOptions()
{
queryExt = UserOptions.GetString("general/queryExtension");
reportExt = UserOptions.GetString("general/reportExtension");
templateExt = UserOptions.GetString("general/templateExtension");
templatePath = UserOptions.GetString("general/templatePath");
// may be zero if OnLoad has not yet run (tree hasn't yet been displayed)
if (tree.Nodes.Count > 0)
{
tree.Nodes[0].Tag = templatePath;
RefreshTemplates();
}
}
/// <summary>
/// Prepopulates the template tree when the control is loaded. Doing it here
/// prevents the horizontal scrollbar problem.
/// </summary>
/// <param name="e">Event arguments</param>
protected override void OnLoad(EventArgs e)
{
if (this.DesignMode)
{
return;
}
templateExt = UserOptions.GetString("general/templateExtension");
queryExt = UserOptions.GetString("general/queryExtension");
reportExt = UserOptions.GetString("general/reportExtension");
templatePath = UserOptions.GetString("general/templatePath");
var translator = new Translator("Browser");
string rootName = translator.GetString("TemplateRoot");
var root = new TreeNode(rootName, OpenFolderIcon, OpenFolderIcon);
root.ContextMenuStrip = contextMenu;
root.Tag = templatePath;
root.Expand();
tree.Nodes.Insert(0, root);
RefreshTemplates();
}
/// <summary>
/// Initializes a new QueryWindow for design-time; this default constructor is
/// required by the VS Forms designer.
/// </summary>
public QueryWindow()
{
InitializeComponent();
if (this.DesignMode)
{
return;
}
activePane = editorView;
windowIsValid = true;
this.HandleDestroyed += new EventHandler(DoHandleDestroyed);
splitContainer.SplitterDistance = (int)(splitContainer.ClientSize.Height * 0.6);
// initially hide the results pane
splitContainer.Panel2Collapsed = true;
var updateCommanderHandler = new EventHandler(UpdateCommander);
var resultTargetChangedHandler
= new ResultTargetChangedEventHandler(DoResultTargetChanged);
editorView.Enter += updateCommanderHandler;
editorView.ResultTargetChanged += resultTargetChangedHandler;
resultsView.Enter += updateCommanderHandler;
resultsView.TextChanged += updateCommanderHandler;
//resultsView.ResultTargetChanged += resultTargetChangedHandler;
queryID = ++count;
queries = new QueryCollection();
this.ResultTarget = (ResultTarget)UserOptions.GetInt("results/general/target");
this.basePath = UserOptions.GetString("general/queryPath");
this.filename = null;
}
// used by both delimited-text and grid-export...
private void ReportDelimitedText(Database.Query query, ResultFormat format, Notepad pad)
{
bool outputQuery = UserOptions.GetBoolean("results/general/outputQuery");
bool printHeader = UserOptions.GetBoolean("results/general/printHeader");
StringBuilder sult = new StringBuilder();
StringBuilder line = new StringBuilder();
string delimiter = ",";
switch (format)
{
case ResultFormat.CommaDelimited:
delimiter = ",";
break;
case ResultFormat.TabDelimited:
delimiter = new String((char)0x09, 1);
break;
case ResultFormat.SpaceDelimited:
delimiter = " ";
break;
case ResultFormat.CustomDelimited:
delimiter = UserOptions.GetString("results/general/delimiter");
break;
}
if (outputQuery)
{
PrintQueryBox(query.SQL, pad);
}
OraData data = query.Data;
foreach (OraTable table in data)
{
if (printHeader && (table.Count > 0))
{
for (int i = 0; i < table.FieldCount; i++)
{
if (i > 0)
{
line.Append(delimiter);
}
line.Append(table.Schema[i].ColumnName);
}
sult.Append(line.ToString() + CR);
}
foreach (OraRow row in table)
{
line.Length = 0;
for (int i = 0; i < table.FieldCount; i++)
{
if (i > 0)
{
line.Append(delimiter);
}
if (row[i] != null)
{
line.Append(row[i].ToString());
}
}
sult.Append(line.ToString() + CR);
}
pad.Write(sult.ToString());
pad.WriteNote(CR + "(" + query.AffectedRecords + " row(s) affected)");
}
}
//========================================================================================
// Wrap()
//========================================================================================
/// <summary>
/// Combines the statements of this collection into a temporary stored procedure.
/// The procedure will declare zero or more output parameters returning sys_refcursors
/// for each query statement.
/// </summary>
/// <remarks>
/// After wrapping, the Parameters property will contain zero or more Parameter
/// instances, each describing an output parameter for a SELECT statement in the
/// statement collection.
/// <para>
/// The name of the stored procedure to generate is specified by the
/// <i>connections/utilProcedure</i> option.
/// </para>
/// </remarks>
/// <returns>
/// A string specifying the wrapped content.
/// </returns>
public string Wrap(string schemaName)
{
parameters = new ParameterCollection();
Parameter parameter = null;
var args = new StringBuilder();
var body = new StringBuilder();
string stmt;
// 1. Generate CREATE PROCEDURE statement
body.Append("BEGIN");
for (int i = 0; i < this.Count; i++)
{
stmt = (string)this[i];
if (stmt.ToUpper().StartsWith("SELECT"))
{
parameter = new Parameter(
"recset" + i, ParameterDirection.Output, OracleDbType.RefCursor, null);
parameters.Add(parameter);
if (args.Length > 0)
{
args.Append(", ");
}
args.Append("recset" + i + " OUT sys_refcursor"); //Orqa_Util.Recordset");
body.Append(" OPEN recset" + i + " FOR " + stmt + ";");
}
else
{
body.Append(" " + stmt);
if (stmt[stmt.Length - 1] != ';')
{
body.Append(';');
}
}
}
body.Append(" END;");
string procedureName = schemaName + "." + UserOptions.GetString("connections/utilProcedure");
var text = new StringBuilder("CREATE OR REPLACE PROCEDURE " + procedureName);
if (args.Length > 0)
{
text.Append(" (" + args.ToString() + ")");
}
text.Append(" AS ");
text.Append(body.ToString());
this.Clear();
this.Add(text.ToString());
// 2. Generate EXEC statement
text.Length = 0;
text.Append("EXEC " + procedureName);
for (int i = 0; i < parameters.Count; i++)
{
if (i == 0)
{
text.Append(" @@" + parameters[i].Name);
}
else
{
text.Append(", @@" + parameters[i].Name);
}
}
text.Append(";");
this.Add(text.ToString());
isWrapped = true;
return(this[0]);
}
//========================================================================================
// Explain()
//========================================================================================
private void Explain(Query query)
{
string tableName = UserOptions.GetString("connections/planTable");
using (var transaction = dbase.OraConnection.BeginTransaction())
{
if (EnsurePlanTable(query, tableName))
{
string planID = "ORQA_" + DateTime.Now.Ticks.ToString("X");
string text
= "EXPLAIN PLAN SET statement_id='" + planID
+ "' INTO " + dbase.DefaultSchema + "." + tableName
+ " FOR " + query.SQL;
try
{
using (var cmd = new OracleCommand(text, dbase.OraConnection))
{
// explain the query
cmd.ExecuteNonQuery();
// load the explanation
// we use these colulmns: level, operation, optoins, object_name, cost, cardinality, bytes
cmd.CommandText
= "SELECT level-1 AS \"level\","
+ " level, operation, options, object_name, cost, cardinality, bytes"
//+ " statement_id, timestamp, remarks, operation, options, object_node, object_name,"
//+ " object_instance, object_type, optimizer, search_columns, id, parent_id, position,"
//+ " cost, cardinality, bytes, other_tag, partition_start, partition_stop, partition_id,"
//+ " other, distribution"
+ " FROM " + dbase.DefaultSchema + "." + tableName
+ " CONNECT BY PRIOR id=parent_id"
+ " START WITH id=0"
+ " ORDER BY id";
var adapter = new OracleDataAdapter();
adapter.SelectCommand = cmd;
query.Plan = new DataSet();
adapter.Fill(query.Plan);
}
}
catch (OracleException exc)
{
query.AddMessage(exc);
}
catch (Exception exc)
{
if (exc.Message == String.Empty)
{
query.AddMessage(new Message(Message.MessageType.Error, exc.StackTrace));
}
else
{
query.AddMessage(new Message(Message.MessageType.Error, exc.Message));
}
}
}
transaction.Commit();
}
}
//========================================================================================
// SetOptions()
//========================================================================================
public void SetOptions()
{
string fontName = UserOptions.GetString("editor/editorFonts/font/family");
int fontSize = UserOptions.GetInt("editor/editorFonts/font/size");
editor.Font = new Font(fontName, fontSize);
bool beyondEof = UserOptions.GetBoolean("editor/general/beyondEof");
bool beyondEoln = UserOptions.GetBoolean("editor/general/beyondEoln");
NavigateOptions navigateOptions = NavigateOptions.DownAtLineEnd | NavigateOptions.UpAtLineBegin;
if (beyondEof)
{
navigateOptions |= NavigateOptions.BeyondEof;
}
if (beyondEoln)
{
navigateOptions |= NavigateOptions.BeyondEol;
}
editor.NavigateOptions = navigateOptions;
editor.IndentOptions = IndentOptions.AutoIndent;
bool verticalScroll = UserOptions.GetBoolean("editor/general/verticalScroll");
bool horizontalScroll = UserOptions.GetBoolean("editor/general/horizontalScroll");
if (verticalScroll && horizontalScroll)
{
editor.Scrolling.ScrollBars = RichTextBoxScrollBars.ForcedBoth;
}
else if (verticalScroll)
{
editor.Scrolling.ScrollBars = RichTextBoxScrollBars.ForcedVertical;
}
else if (horizontalScroll)
{
editor.Scrolling.ScrollBars = RichTextBoxScrollBars.ForcedHorizontal;
}
else
{
editor.Scrolling.ScrollBars = RichTextBoxScrollBars.None;
}
bool showGutter = UserOptions.GetBoolean("editor/general/showGutter");
int gutterWidth = UserOptions.GetInt("editor/general/gutterWidth");
bool lineNumbers = UserOptions.GetBoolean("editor/general/lineNumbers");
editor.Gutter.Visible = showGutter;
editor.Gutter.Width = gutterWidth;
editor.Gutter.Options = (lineNumbers ? GutterOptions.PaintLineNumbers | GutterOptions.PaintLinesOnGutter : GutterOptions.None);
bool showMargin = UserOptions.GetBoolean("editor/general/showMargin");
int marginPosition = UserOptions.GetInt("editor/general/marginPosition");
bool wordWrap = UserOptions.GetBoolean("editor/general/wordWrap");
bool wrapAtMargin = UserOptions.GetBoolean("editor/general/wrapAtMargin");
editor.Margin.Visible = showMargin;
editor.Margin.Position = marginPosition;
editor.WordWrap = wordWrap;
editor.WrapAtMargin = wrapAtMargin;
SyntaxSettings settings = new SyntaxSettings();
bool keepTabs = UserOptions.GetBoolean("editor/editorTabs/keepTabs");
int tabSize = UserOptions.GetInt("editor/editorTabs/size");
editor.Source.Lines.TabStops = new int[1] {
tabSize
};
editor.Source.Lines.UseSpaces = !keepTabs;
// set lex styles
XPathNavigator nav = UserOptions.OptionsDoc.CreateNavigator();
nav.MoveToFirstChild();
nav = nav.SelectSingleNode("editor/editorFonts/lexStyles");
nav.MoveToFirstChild();
LexStyleItem item;
string colorName;
var foreground = FontsAndColors.PlainTextForeground;
var background = FontsAndColors.PlainTextBackground;
do
{
if (nav.NodeType == XPathNodeType.Element)
{
item = new LexStyleItem();
item.Name = nav.LocalName;
item.InternalName = nav.LocalName;
colorName = nav.GetAttribute("foreColor", nav.NamespaceURI);
item.ForeColor = Color.FromName(colorName);
if (!item.ForeColor.IsKnownColor)
{
item.ForeColor = Color.FromArgb(unchecked ((int)uint.Parse(
colorName, System.Globalization.NumberStyles.HexNumber)));
}
if (item.Name.Equals("whitespace"))
{
foreground = item.ForeColor;
}
colorName = nav.GetAttribute("backColor", nav.NamespaceURI);
item.BackColor = Color.FromName(colorName);
if (!item.BackColor.IsKnownColor)
{
item.BackColor = Color.FromArgb(unchecked ((int)uint.Parse(
colorName, System.Globalization.NumberStyles.HexNumber)));
}
if (item.Name.Equals("whitespace"))
{
background = item.BackColor;
}
item.FontStyle
= (FontStyle)Enum.Parse(
typeof(FontStyle), nav.GetAttribute("style", nav.NamespaceURI));
ApplyLeXStyle(item);
}
}while (nav.MoveToNext(XPathNodeType.Element));
editor.ForeColor = foreground;
editor.BackColor = background;
editor.Refresh();
}