private void DoParametersDialogClosed(object sender, FormClosedEventArgs e)
{
Dialogs.ParametersDialog dialog = (Dialogs.ParametersDialog)sender;
if (dialog.DialogResult == DialogResult.OK)
{
queries.Clear();
driver.Reset();
resultsView.Clear();
if (splitContainer.Panel2Collapsed)
{
DoToggleResults(null, null);
}
// catch up on Windows messages just to look better
Application.DoEvents();
SetStatusMessage(RxExecutingStatus);
Database.Query query = new Database.Query(dialog.ProcedureName, dialog.Parameters);
queries.Add(query);
string sql = (new StatementParser()).BuildProcedureStatement(query);
editorView.InsertText("\n" + sql + "\n\n");
resultsView.Lock();
driver.Execute(queries, basePath, 1);
}
}
//========================================================================================
// ToggleTracing()
//========================================================================================
public void ToggleTracing(bool enable)
{
IsSaved = true;
SetTitle();
commander.ExecuteControls.IsEnabled = true;
SetStatusMessage(RxExecutingStatus);
if (splitContainer.Panel2Collapsed)
{
DoToggleResults(null, null);
}
splitContainer.SplitterDistance = (int)(splitContainer.Height * 0.2);
// catch up on Windows messages just to look better
Application.DoEvents();
string sql =
"alter session set sql_trace = " + (enable ? "true" : "false");
Logger.WriteLine("QueryWindow.ToggleTracing");
Logger.WriteLine(sql);
if (enable)
{
resultsView.WriteLine("\nEnabling SQL tracing...\n" + sql);
}
else
{
resultsView.WriteLine("\nDisabling SQL tracing...\n" + sql);
}
queries.Clear();
driver.Reset();
var query = new Database.Query(sql);
(new StatementParser()).ParseStatement(dbase, query, browser);
queries.Add(query);
if (enable)
{
sql = "select P.tracefile from v$process P, v$session S"
+ " where S.audsid = userenv('sessionid') and S.paddr = P.addr";
Logger.WriteLine(sql);
query = new Database.Query(sql);
(new StatementParser()).ParseStatement(dbase, query, browser);
queries.Add(query);
}
// execute query collection
resultsView.Lock();
driver.Execute(queries, basePath, 1);
}
//========================================================================================
// ShowOpenTransactions()
//========================================================================================
/// <summary>
/// Opens a new query window and executes the SELECT FROM v$transaction statement.
/// </summary>
public void ShowOpenTransactions()
{
string formattedSQL =
"SELECT vp.spid as pid, S.blocking_session blocker, S.sid, S.serial#,\n"
+ " s.osuser, S.username, S.machine, S.program,\n"
+ " Q.sql_fulltext as cur_sql, PQ.sql_fulltext as prev_sql,\n"
+ " vt.used_urec, vt.start_date\n"
+ " FROM v$session S\n"
+ " LEFT JOIN v$sqlarea Q on S.sql_id = Q.sql_id\n"
+ " LEFT JOIN v$sqlarea PQ on S.prev_sql_id = PQ.sql_id\n"
+ " LEFT JOIN v$process vp on s.paddr = vp.addr\n"
+ " LEFT JOIN v$transaction vt on s.saddr = vt.ses_addr\n"
+ " ORDER BY S.username, S.machine\n";
InsertText(formattedSQL);
IsSaved = true;
SetTitle();
Logger.WriteLine("QueryWindow.ShowOpenTransactions");
commander.ExecuteControls.IsEnabled = true;
SetStatusMessage(RxExecutingStatus);
resultsView.Clear();
if (splitContainer.Panel2Collapsed)
{
DoToggleResults(null, null);
}
splitContainer.SplitterDistance = (int)(splitContainer.Height * 0.25);
// catch up on Windows messages just to look better
Application.DoEvents();
queries.Clear();
driver.Reset();
var query = new Database.Query(formattedSQL.Replace("\n", String.Empty));
var parser = new StatementParser();
parser.ParseStatement(dbase, query, browser);
queries.Add(query);
// execute query collection
resultsView.Lock();
driver.Execute(queries, basePath, 1);
}
//========================================================================================
// ReportXml()
//========================================================================================
private void ReportXml(Database.Query query)
{
if (query.HasOutputs)
{
PrintOutputParameters(query, textpad);
}
var xml = new StringBuilder(query.Data.GetXml().ToString());
xml.Replace("<", "<");
xml.Replace(">", ">");
textpad.WriteLine(xml.ToString());
textpad.WriteLine();
textpad.WriteNote("(" + query.AffectedRecords + " row(s) affected)");
}
private void Execute(ParseMode parseMode, string text, int repeat)
{
Logger.WriteLine("QueryWindow.Execute");
commander.ExecuteControls.IsEnabled = true;
SetStatusMessage(RxExecutingStatus);
resultsView.Clear();
if (splitContainer.Panel2Collapsed)
{
DoToggleResults(null, null);
}
// catch up on Windows messages just to look better
Application.DoEvents();
queries.Clear();
driver.Reset();
var parser = new StatementParser();
parser.ParseNotification += new NotificationEventHandler(DoParseNotification);
StatementCollection statements = GetStatementCollection(parseMode, text);
// build collection of parsed queries
Database.Query query;
System.Collections.Specialized.StringEnumerator e = statements.GetEnumerator();
while (e.MoveNext())
{
query = new Database.Query(e.Current);
parser.ParseStatement(dbase, query, browser);
if (parseMode == ParseMode.SqlPlus)
{
query.QueryType = QueryType.SqlPlus;
}
queries.Add(query);
}
// execute query collection
resultsView.Lock();
driver.Execute(queries, basePath, repeat);
}
//========================================================================================
// ShowOpenCursors()
//========================================================================================
/// <summary>
/// Opens a new query window and executes the SELECT FROM v$open_cursors statement.
/// </summary>
public void ShowOpenCursors()
{
string formattedSQL =
"SELECT count(*), sql_text\n"
+ " FROM v$open_cursor\n"
+ " GROUP BY sql_text\n"
+ " ORDER BY 1 DESC\n";
InsertText(formattedSQL);
IsSaved = true;
SetTitle();
//Execute(ParseMode.Sequential, editorView.SelectedText, 1);
Logger.WriteLine("QueryWindow.ShowOpenCursors");
commander.ExecuteControls.IsEnabled = true;
SetStatusMessage(RxExecutingStatus);
resultsView.Clear();
if (splitContainer.Panel2Collapsed)
{
DoToggleResults(null, null);
}
splitContainer.SplitterDistance = (int)(splitContainer.Height * 0.2);
// catch up on Windows messages just to look better
Application.DoEvents();
queries.Clear();
driver.Reset();
var query = new Database.Query(formattedSQL.Replace("\n", String.Empty));
var parser = new StatementParser();
parser.ParseStatement(dbase, query, browser);
queries.Add(query);
// execute query collection
resultsView.Lock();
driver.Execute(queries, basePath, 1);
}
//========================================================================================
// PrintOutputParameters()
//========================================================================================
private void PrintOutputParameters(Database.Query query, Notepad output)
{
var savecolor = output.ForeColor;
foreach (Database.Parameter parameter in query.Parameters)
{
if (((parameter.Direction &
(ParameterDirection.Output | ParameterDirection.ReturnValue)) > 0) &&
(parameter.DataType != Oracle.ManagedDataAccess.Client.OracleDbType.RefCursor))
{
output.ForeColor = Color.FromArgb(0x6E96BE); // "Number-style" blue;
output.WriteLine(
"Output parameter: '" + parameter.Name
+ "' = [" + parameter.Value.ToString().Trim() + "]");
}
}
output.ForeColor = savecolor;
}
//========================================================================================
// ReportGrid()
//========================================================================================
#region ReportGrid
private void ReportGrid(Database.Query query)
{
DataGridView g = (resultCount == 0 ? grid : AddGrid());
if (query.Data.Count > 0)
{
OraTable table = query.Data[0];
for (int c = 0; c < table.Schema.FieldCount; c++)
{
g.Columns.Add(table.Schema[c].ColumnName, table.Schema[c].ColumnName);
}
foreach (var row in table)
{
var values = new List <String>();
foreach (object value in row)
{
if (value == null)
{
values.Add("...");
}
else
{
values.Add(value.ToString());
}
}
g.Rows.Add(values.ToArray());
}
}
g.Tag = query;
bool outputQuery = UserOptions.GetBoolean("results/general/outputQuery");
if (outputQuery)
{
PrintQueryBox(query.SQL, msgpad);
}
msgpad.WriteNote(CR + "(" + query.AffectedRecords + " row(s) affected)");
}
// 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)");
}
}
//========================================================================================
// ReportDelimitedText()
//========================================================================================
private void ReportDelimitedText(Database.Query query, ResultFormat format)
{
ReportDelimitedText(query, format, textpad);
}
private void ReportText(Database.Query query, Notepad pad)
{
bool outputQuery = UserOptions.GetBoolean("results/general/outputQuery");
bool printHeader = UserOptions.GetBoolean("results/general/printHeader");
bool rightAlign = UserOptions.GetBoolean("results/general/rightAlign");
bool cleanNewlines = UserOptions.GetBoolean("results/general/cleanNewlines");
bool ratype = rightAlign;
int totalRows = 0;
OraData data = query.Data;
var line = new StringBuilder();
string text;
if (resultCount > 0)
{
var color = pad.ForeColor;
pad.ForeColor = Color.FromArgb(0x6E96BE); // "Number-style" blue
pad.WriteLine(CR + new String((char)0x2550, 100) + CR);
pad.ForeColor = color;
}
if (outputQuery)
{
PrintQueryBox(query.SQL, pad);
}
if (query.HasOutputs)
{
PrintOutputParameters(query, pad);
}
for (int t = 0; t < data.Count; t++)
{
OraTable table = data[t];
if (t > 0)
{
var color = pad.ForeColor;
pad.ForeColor = Color.FromArgb(0x6E96BE); // "Number-style" blue
pad.WriteLine(CR + new String((char)0x2550, 100));
pad.ForeColor = color;
}
int[] widths = null;
if (table.Count > 0)
{
widths = MeasureColumns(table);
if (printHeader)
{
BuildTextHeader(table, widths, pad);
}
foreach (OraRow row in table)
{
line = new StringBuilder();
for (int i = 0; i < table.FieldCount; i++)
{
if (i > 0)
{
line.Append(" ");
}
if (row[i] == null)
{
text = "...";
}
else if (table.Schema[i].DataType == typeof(Byte[]))
{
if (row[i].GetType() == typeof(DBNull))
{
text = String.Empty;
}
else
{
Byte[] bytes = (Byte[])row[i];
text = String.Join(
String.Empty,
bytes.Select(b => b.ToString("X2")).ToArray());
}
}
else
{
text = row[i].ToString();
}
if (cleanNewlines)
{
text = text.Replace("\n", String.Empty).Replace("\r", String.Empty);
}
if (text.Length > widths[i])
{
line.Append(text.Substring(0, widths[i]));
}
else
{
if (rightAlign)
{
TypeCode code = Type.GetTypeCode(row[i].GetType());
ratype = ((code != TypeCode.Boolean) &&
(code != TypeCode.Char) &&
(code != TypeCode.String));
}
if (ratype)
{
line.Append(new String(' ', widths[i] - text.Length) + text);
}
else
{
line.Append(text + new String(' ', widths[i] - text.Length));
}
}
}
pad.WriteLine(line.ToString());
}
}
pad.WriteLine();
pad.WriteNote(String.Format(RxRowsAffected, table.Count));
totalRows += table.Count;
}
if (data.Count == 0)
{
totalRows = query.AffectedRecords;
}
if (data.Count != 1)
{
string msg;
if (totalRows < 0)
{
msg = RxCompleted;
}
else
{
msg = String.Format(RxTotalRowsAffected, totalRows);
}
pad.WriteNote(CR + msg);
}
}
//========================================================================================
// ReportText()
//========================================================================================
#region ReportText
private void ReportText(Database.Query query)
{
ReportText(query, textpad);
}
//========================================================================================
// Add()
//========================================================================================
/// <summary>
/// Displays the results of the given query.
/// </summary>
/// <param name="query">The query to report.</param>
public void Add(Database.Query query)
{
Logger.WriteLine("Adding result with target [" + resultTarget + "]");
Notepad msgTarget = textpad;
if ((int)resultTarget < 0) // TODO: why!?
{
resultTarget = ResultTarget.Text;
}
if (!query.HideResults)
{
if (query.QueryType == QueryType.SqlPlus)
{
LoadFile(query.OutputFilename);
}
else
{
switch (resultTarget)
{
case ResultTarget.Text:
ResultFormat format = (ResultFormat)UserOptions.GetEnumeration(
"results/general/format", typeof(ResultFormat));
if (format == ResultFormat.ColumnAligned)
{
ReportText(query);
}
else
{
ReportDelimitedText(query, format);
}
break;
case ResultTarget.Grid:
ReportGrid(query);
msgTarget = msgpad;
break;
case ResultTarget.Xml:
ReportXml(query);
break;
}
}
}
if (query.Messages.Count > 0)
{
ReportMessages(query.Messages, msgTarget, query.HideResults);
}
else if (query.HideResults)
{
textpad.WriteNote(CR + RxNoMessages);
}
if (UserOptions.GetBoolean("results/general/dbmsOutput"))
{
if (query.OutputLines.Count > 0)
{
ReportOutputLines(query.OutputLines);
}
}
resultCount++;
}
//========================================================================================
// ShowUserErrors()
//========================================================================================
/// <summary>
/// Opens a new query window and executes the SELECT FROM User_Errors statement.
/// </summary>
public void ShowUserErrors()
{
InsertText("SELECT name, line, position, text"
+ " FROM User_Errors"
+ " ORDER BY name, line, position;\n");
IsSaved = true;
SetTitle();
//Execute(ParseMode.Sequential, editorView.SelectedText, 1);
Logger.WriteLine("QueryWindow.ShowUserErrors");
commander.ExecuteControls.IsEnabled = true;
SetStatusMessage(RxExecutingStatus);
resultsView.Clear();
if (splitContainer.Panel2Collapsed)
{
DoToggleResults(null, null);
}
splitContainer.SplitterDistance = (int)(splitContainer.Height * 0.2);
// catch up on Windows messages just to look better
Application.DoEvents();
queries.Clear();
driver.Reset();
var query = new Database.Query("SELECT name, line, position, text"
+ " FROM User_Errors"
+ " ORDER BY name, line, position");
(new StatementParser()).ParseStatement(dbase, query, browser);
queries.Add(query);
query.Messages.AddRange(QueryDriver.GetUserErrors(dbase));
query.HideResults = true;
//StatementParser parser = new StatementParser();
//parser.ParseNotification += new NotificationEventHandler(DoParseNotification);
//StatementCollection statements = GetStatementCollection(parseMode, text);
//// build collection of parsed queries
//Database.Query query;
//System.Collections.Specialized.StringEnumerator e = statements.GetEnumerator();
//while (e.MoveNext())
//{
// query = new Database.Query(e.Current);
// parser.ParseStatement(dbase, query, browser);
// queries.Add(query);
//}
// execute query collection
resultsView.Lock();
driver.Execute(queries, basePath, 1);
}
private void DoQueryCompleted(Database.Query query)
{
resultsView.Add(query);
SetStatusTime(query.ElapsedTime);
SetStatusRows(query.AffectedRecords);
}