void ExecuteAndSaveResultsToFile(ExecuteOutputType oType)
{
outType = oType;
new FileSelectionDialog ("Results File Save As", new FileSelectionEventHandler (OnSaveExeOutFile));
}
// Execute SQL Commands in editor
private bool ExecuteSQL (ExecuteOutputType outputType, string filename, BatchExecuteMode exeMode)
{
int page = sourceFileNotebook.CurrentPage;
EditorTab tab = FindEditorTab(page);
TextIter start_iter, end_iter;
TextBuffer buf = tab.editor.Buffer;
string sql = "";
// get text from SQL editor
try {
if(buf.GetSelectionBounds (out start_iter, out end_iter) == true) {
sql = buf.GetText(start_iter, end_iter, false);
exeMode = BatchExecuteMode.AsIs;
if (Execute (outputType, filename, sql) == false) {
AppendText("");
return false;
}
AppendText("");
}
else {
if (exeMode == BatchExecuteMode.AsIs) {
// get entire text from buffer
start_iter = tab.editor.Buffer.StartIter;
end_iter = tab.editor.Buffer.EndIter;
sql = buf.GetText(start_iter, end_iter, false);
if (Execute (outputType, filename, sql) == false) {
AppendText("");
return false;
}
AppendText("");
}
else if (exeMode == BatchExecuteMode.Command) {
// get command at cursor
end_iter = tab.editor.Buffer.EndIter;
sql = tab.editor.GetSqlStatementAtCursor(out end_iter);
if (Execute (outputType, filename, sql) == false) {
AppendText("");
return false;
}
AppendText("");
}
else if (exeMode == BatchExecuteMode.Script) {
start_iter = buf.GetIterAtOffset (0);
sql = tab.editor.GetSqlStatementAtCursor(out start_iter);
if (sql.Length > 0) {
// move insert mark to end of SQL statement to be executed
buf.MoveMark (buf.InsertMark, start_iter);
buf.MoveMark (buf.SelectionBound, start_iter);
while (Application.EventsPending ())
Application.RunIteration ();
if (Execute (outputType, filename, sql) == false) {
tab.editor.View.ScrollToMark (buf.InsertMark, 0.4, true, 0.0, 1.0);
return false;
}
}
while(sql.Length > 0 && start_iter.IsEnd == false) {
while (Application.EventsPending ())
Application.RunIteration ();
sql = tab.editor.GetNextSqlStatement(ref start_iter);
if (sql.Length > 0) {
// move insert mark to end of SQL statement to be executed
buf.MoveMark (buf.InsertMark, start_iter);
buf.MoveMark (buf.SelectionBound, start_iter);
while (Application.EventsPending ())
Application.RunIteration ();
if (Execute (outputType, filename, sql) == false) {
tab.editor.View.ScrollToMark (buf.InsertMark, 0.4, true, 0.0, 1.0);
return false;
}
}
}
tab.editor.View.ScrollToMark (buf.InsertMark, 0.4, true, 0.0, 1.0);
AppendText("");
}
}
}
catch (Exception et) {
if (exeMode == BatchExecuteMode.Script)
tab.editor.View.ScrollToMark (buf.InsertMark, 0.4, true, 0.0, 1.0);
AppendText ("Error: Unable to execute SQL statement: " + et.Message);
return false;
}
return true; // return true - success
}
private bool Execute (ExecuteOutputType outputType, string filename, string sql)
{
int page = sourceFileNotebook.CurrentPage;
EditorTab tab = FindEditorTab(page);
// do not execute empty queries
if (sql == null)
return true;
if (sql.Trim ().Equals(""))
return true;
string[] parms = sql.Split (new char[1] {' '});
string userCmd = parms[0].ToUpper ();
switch (userCmd) {
case "CONNECT":
if(parms.Length == 2)
CreateConnection (parms[1]);
else {
Error ("CONNECT only has only one operand");
return false;
}
return true;
case "DISCONNECT":
Error ("Disconnecting a data source not supported yet.");
return false;
default:
break;
}
if (conn == null) {
AppendText ("Error: Not Connected.");
SetStatusBarText ("Error: Not Connected.");
return false;
}
IDbCommand cmd = null;
DataTable schemaTable = null;
string msg = "";
long rowsRetrieved = 0;
int rowsAffected = 0;
try {
cmd = conn.CreateCommand ();
}
catch (Exception ec) {
AppendText ("Error: Unable to create command to execute: " + ec.Message);
return false;
}
try {
cmd.CommandText = sql;
}
catch (Exception e) {
AppendText ("Error: Unable to set SQL text to command. Reason: " + e.Message);
return false;
}
IDataReader reader = null;
SetStatusBarText ("Executing...");
DoEvents ();
if (outputType == ExecuteOutputType.Normal ||
outputType == ExecuteOutputType.CsvFile) {
try {
if (queryMode == QueryMode.Query)
reader = cmd.ExecuteReader ();
else if (queryMode == QueryMode.NonQuery) {
rowsAffected = cmd.ExecuteNonQuery ();
if (rowsAffected == -1) {
msg = "SQL Command Executed.";
AppendText (msg);
SetStatusBarText (msg);
}
else {
msg = "Rows Affected: " + rowsAffected.ToString ();
AppendText (msg);
SetStatusBarText (msg);
}
}
}
catch (Exception e) {
msg = "SQL Error: " + e.Message;
Error (msg);
return false;
}
if (queryMode == QueryMode.Query && reader == null) {
Error("Error: reader is null");
return false;
}
}
if (queryMode == QueryMode.Query) {
try {
if (outputResults == OutputResults.TextView &&
outputType == ExecuteOutputType.Normal) {
DisplayData (reader);
// clean up
reader.Close ();
reader.Dispose ();
reader = null;
}
else if(outputType == ExecuteOutputType.CsvFile) {
schemaTable = reader.GetSchemaTable();
if(schemaTable != null && reader.FieldCount > 0) {
OutputDataToCsvFile(reader, schemaTable, filename);
}
else {
AppendTextWithoutScroll("Command executed.");
SetStatusBarText ("Command executed.");
}
// clean up
reader.Close ();
reader.Dispose ();
reader = null;
}
else {
switch(outputType) {
case ExecuteOutputType.Normal:
ArrayList grds = new ArrayList ();
bool bContinue = true;
while (bContinue) {
DataGrid grd = CreateOutputResultsDataGrid ();
grd.View.Selection.Mode = SelectionMode.Multiple;
grd.Show ();
rowsRetrieved = grd.DataLoad (reader);
if (reader.FieldCount == 0) {
SetStatusBarText ("SQL executed.");
AppendText ("SQL executed.");
}
else {
SetStatusBarText ("Records Retrieved: " + rowsRetrieved.ToString ());
AppendText ("Records Retrieved: " + rowsRetrieved.ToString ());
}
grds.Add (grd);
bContinue = reader.NextResult ();
}
if (tab.grid != null)
{
tab.frame.Remove (tab.grid);
tab.grid = null;
}
else if (tab.gridResults != null)
{
tab.frame.Remove (tab.gridResults);
tab.gridResults = null;
}
if (grds.Count == 1) {
tab.grid = (DataGrid) grds [0];
tab.frame.Add (tab.grid);
}
else {
tab.gridResults = new MultiResultsGrid (grds);
tab.frame.Add (tab.gridResults);
}
sourceFileNotebook.QueueDraw ();
tab.resultsNotebook.ShowAll ();
tab.resultsNotebook.ResizeChildren ();
DoEvents ();
tab.resultsNotebook.CurrentPage = tab.GridPage;
reader.Close ();
reader.Dispose ();
reader = null;
break;
case ExecuteOutputType.XmlFile:
AppendText ("Execute and output to XML file: " + filename + "...");
SetStatusBarText ("Execute and output to XML file: " + filename + "...");
DataSet dataSet = new DataSet();
DataTable dataTable = LoadDataTable (cmd);
dataSet.Tables.Add(dataTable);
dataSet.WriteXml(filename);
dataSet = null;
dataTable.Clear();
dataTable.Dispose();
dataTable = null;
AppendText ("XML file written: " + filename);
SetStatusBarText ("XML file written: " + filename);
break;
}
cmd.Dispose();
cmd = null;
}
}
catch (Exception e) {
msg = "Error Displaying Data: " + e.Message;
Error (msg);
return false;
}
}
return true; // return true - success
}
// Execute SQL Commands
void ExecuteSQL (ExecuteOutputType outputType, string filename)
{
if (conn == null) {
AppendText (buf, "Error: Not Connected.");
return;
}
DataTable schemaTable = null;
int page = sourceFileNotebook.CurrentPage;
EditorTab tab = FindEditorTab(page);
string msg = "";
string sql = "";
IDbCommand cmd;
try {
cmd = conn.CreateCommand ();
}
catch (Exception ec) {
AppendText (buf,
"Error: Unable to create command to execute: " +
ec.Message);
return;
}
SqlSharpGtk.DebugWriteLine ("get text from SQL editor...");
// get text from SQL editor
try {
TextIter start_iter, end_iter;
TextBuffer exeBuff;
exeBuff = tab.editor.Buffer;
start_iter = exeBuff.StartIter;
end_iter = exeBuff.EndIter;
sql = exeBuff.GetText(start_iter, end_iter, false);
}
catch (Exception et) {
AppendText (buf,
"Error: Unable to get text from SQL editor: " +
et.Message);
return;
}
try {
cmd.CommandText = sql;
}
catch (Exception e) {
AppendText (buf,
"Error: Unable to set SQL text to command.");
}
IDataReader reader = null;
SqlSharpGtk.DebugWriteLine ("Executing SQL: " + sql);
if ((outputResults == OutputResults.TextView &&
outputType == ExecuteOutputType.Normal) ||
outputType == ExecuteOutputType.HtmlFile ||
outputType == ExecuteOutputType.CsvFile) {
try {
reader = cmd.ExecuteReader ();
}
catch (Exception e) {
//msg = "SQL Execution Error: " + e.Message;
msg = "SQL Execution Error: " + e;
Error (msg);
return;
}
if (reader == null) {
Error("Error: reader is null");
return;
}
}
try {
if (outputResults == OutputResults.TextView &&
outputType == ExecuteOutputType.Normal) {
DisplayData (reader);
// clean up
reader.Close ();
reader.Dispose ();
reader = null;
}
else if(outputType == ExecuteOutputType.HtmlFile) {
schemaTable = reader.GetSchemaTable();
if(schemaTable != null && reader.FieldCount > 0) {
OutputDataToHtmlFile(reader, schemaTable, filename);
}
else {
AppendText("Command executed.");
}
// clean up
reader.Close ();
reader.Dispose ();
reader = null;
}
else if(outputType == ExecuteOutputType.CsvFile) {
schemaTable = reader.GetSchemaTable();
if(schemaTable != null && reader.FieldCount > 0) {
OutputDataToCsvFile(reader, schemaTable, filename);
}
else {
AppendText("Command executed.");
}
// clean up
reader.Close ();
reader.Dispose ();
reader = null;
}
else {
DataTable dataTable = LoadDataTable (cmd);
switch(outputType) {
case ExecuteOutputType.Normal:
AppendText("set DataGrid.DataSource to DataTable...");
grid.DataSource = dataTable;
AppendText("DataBind...");
grid.DataBind ();
AppendText("Clean up...");
// clean up
grid.DataSource = null;
break;
case ExecuteOutputType.XmlFile:
AppendText("Create DataSet...");
DataSet dataSet = new DataSet();
AppendText("Add DataTable to DataSet's DataTableCollection...");
dataSet.Tables.Add(dataTable);
AppendText("Write DataSet to XML file: " +
filename);
dataSet.WriteXml(filename);
AppendText("Clean up...");
dataSet = null;
break;
}
// clean up
dataTable.Clear();
dataTable.Dispose();
dataTable = null;
AppendText("Done.");
cmd.Dispose();
cmd = null;
}
}
catch (Exception e) {
//msg = "Error Displaying Data: " + e.Message;
msg = "Error Displaying Data: " + e;
Error (msg);
}
}