2 // SqlSharpGtk - Mono SQL# For GTK# - SQL Query and Configuration tool for
6 // Daniel Morgan <danielmorgan@verizon.net>
8 // (C)Copyright 2002, 2003 by Daniel Morgan
10 // To be included with Mono as a SQL query tool licensed under the GPL license.
13 namespace Mono.Data.SqlSharp.Gui.GtkSharp
16 using System.Collections;
18 using System.Data.Common;
19 using System.Data.Odbc;
20 using System.Data.OleDb;
21 using System.Data.SqlClient;
25 using System.Reflection;
26 using System.Runtime.Remoting;
27 using System.Runtime.InteropServices;
28 using System.Diagnostics;
34 using Mono.GtkSharp.Goodies;
40 public enum OutputResults
46 public enum ExecuteOutputType
54 public class EditorTab
56 public SqlEditorSharp editor;
58 public string filename;
59 public string basefilename;
63 public class SqlSharpGtk
65 static int SqlWindowCount = 0;
67 private IDbConnection conn = null;
68 public DbProvider dbProvider = null;
69 private Type connectionType = null;
70 private Type adapterType = null;
71 public Assembly providerAssembly = null;
72 public string connectionString = "";
74 private Statusbar statusBar;
75 private Toolbar toolbar;
77 int lastUnknownFile = 0;
81 // OutputResults.TextView
82 private ScrolledWindow swin;
83 public TextBuffer buf;
84 private TextView textView;
85 private TextTag textTag;
86 // OutputResults.DataGrid
87 private DataGrid grid;
89 private Gtk.Window win;
91 public static readonly string ApplicationName = "Mono SQL# For GTK#";
93 private OutputResults outputResults;
95 public DbProviderCollection providerList;
96 Notebook sourceFileNotebook;
97 Notebook resultsNotebook;
98 ArrayList editorTabs = new ArrayList();
100 public SqlSharpGtk ()
112 public void CreateGui()
114 win = new Gtk.Window (ApplicationName);
115 win.DeleteEvent += new GtkSharp.DeleteEventHandler(OnWindow_Delete);
117 win.DefaultSize = new Size (450, 300);
119 VBox vbox = new VBox (false, 4);
123 MenuBar mb = CreateMenuBar ();
124 vbox.PackStart(mb, false, false, 0);
127 toolbar = CreateToolbar ();
128 vbox.PackStart (toolbar, false, false, 0);
131 VPaned paned = new VPaned ();
132 vbox.PackStart (paned, true, true, 0);
134 // SQL Editor (top TextView panel)
135 sourceFileNotebook = new Notebook();
136 sourceFileNotebook.Scrollable = true;
138 paned.Add1 (sourceFileNotebook);
139 sourceFileNotebook.SwitchPage += new
140 GtkSharp.SwitchPageHandler(OnEditorTabSwitched);
143 resultsNotebook = CreateOutputResultsGui ();
144 paned.Add2 (resultsNotebook);
146 statusBar = new Statusbar ();
147 vbox.PackEnd (statusBar, false, false, 0);
149 outputResults = OutputResults.TextView;
150 ToggleResultsOutput ();
153 EditorTab NewEditorTab ()
155 SqlEditorSharp editor;
156 editor = new SqlEditorSharp ();
157 editor.UseSyntaxHiLighting = true;
159 editor.View.KeyPressEvent +=
\r
160 new GtkSharp.KeyPressEventHandler(OnKeyPressEventKey);
\r
163 string unknownFile = "Unknown" +
164 lastUnknownFile.ToString() + ".sql";
165 Label label = new Label(unknownFile);
167 sourceFileNotebook.AppendPage(editor, label);
168 sourceFileNotebook.ShowAll ();
169 sourceFileNotebook.ResizeChildren ();
171 sourceFileNotebook.CurrentPage = -1;
173 EditorTab tab = new EditorTab();
177 tab.basefilename = unknownFile;
178 tab.page = sourceFileNotebook.CurrentPage;
187 Notebook CreateOutputResultsGui ()
190 Notebook results = new Notebook();
191 results.TabPos = PositionType.Bottom;
193 grid = CreateOutputResultsDataGrid ();
195 label = new Label("Grid");
196 results.AppendPage(grid, label);
198 swin = CreateOutputResultsTextView ();
200 label = new Label("Log");
201 results.AppendPage(swin, label);
203 sourceFileNotebook.ShowAll ();
204 sourceFileNotebook.ResizeChildren ();
210 DataGrid CreateOutputResultsDataGrid ()
212 return new DataGrid ();
215 ScrolledWindow CreateOutputResultsTextView ()
218 sw = new ScrolledWindow (
219 new Adjustment (0.0, 0.0, 0.0, 0.0, 0.0, 0.0),
220 new Adjustment (0.0, 0.0, 0.0, 0.0, 0.0, 0.0));
221 sw.HscrollbarPolicy = Gtk.PolicyType.Automatic;
222 sw.VscrollbarPolicy = Gtk.PolicyType.Automatic;
223 sw.ShadowType = Gtk.ShadowType.In;
225 textView = new TextView ();
226 buf = textView.Buffer;
227 textView.Editable = false;
228 textView.ModifyFont (Pango.FontDescription.FromString ("courier new"));
234 void OnKeyPressEventKey(object o, GtkSharp.KeyPressEventArgs args)
\r
236 if (o is TextView) {
\r
237 TextView tv = (TextView) o;
\r
238 //Gdk.EventKey k = args.Event;
\r
240 // if the F5 key was pressed
\r
241 if (args.Event.keyval == 0xFFC2) {
\r
242 if (tv.Editable == true) {
\r
244 ExecuteSQL (ExecuteOutputType.Normal, "");
\r
250 Toolbar CreateToolbar ()
252 Toolbar toolbar = new Toolbar ();
254 toolbar.ToolbarStyle = ToolbarStyle.Icons;
256 toolbar.AppendItem ("Execute",
257 "Execute SQL Commands.", String.Empty,
258 new Gtk.Image (Stock.Execute, IconSize.SmallToolbar),
259 new Gtk.SignalFunc (OnToolbar_Execute));
261 toolbar.AppendItem ("DataGrid",
262 "Toggle Results to DataGrid or TextView", String.Empty,
263 new Gtk.Image (Stock.GoDown, IconSize.SmallToolbar),
264 new Gtk.SignalFunc (OnToolbar_ToggleResultsOutput));
269 // TODO: use the ProviderFactory in Mono.Data
271 // instead of what's below
272 public void LoadProviders ()
274 providerList = new DbProviderCollection ();
276 providerList.Add (new DbProvider (
280 "Mono.Data.MySql.MySqlConnection",
281 "Mono.Data.MySql.MySqlDataAdapter",
283 providerList.Add (new DbProvider (
287 "ByteFX.Data.MySQLClient.MySQLConnection",
288 "ByteFX.Data.MySQLClient.MySQLDataAdapter",
290 providerList.Add (new DbProvider (
293 "Mono.Data.PostgreSqlClient",
294 "Mono.Data.PostgreSqlClient.PgSqlConnection",
295 "Mono.Data.PostgreSqlClient.PgSqlDataAdapter",
297 providerList.Add (new DbProvider (
299 "PostgreSQL (Npgsql)",
301 "Npgsql.NpgsqlConnection",
302 "Npgsql.NpgsqlDataAdapter",
304 providerList.Add (new DbProvider (
306 "Microsoft SQL Server",
311 providerList.Add (new DbProvider (
314 "Mono.Data.TdsClient",
315 "Mono.Data.TdsClient.TdsConnection",
316 "Mono.Data.TdsClient.TdsDataAdapter",
318 providerList.Add (new DbProvider (
325 providerList.Add (new DbProvider (
332 providerList.Add (new DbProvider (
335 "Mono.Data.SqliteClient",
336 "Mono.Data.SqliteClient.SqliteConnection",
337 "Mono.Data.SqliteClient.SqliteDataAdapter",
339 providerList.Add (new DbProvider (
342 "Mono.Data.SybaseClient",
343 "Mono.Data.SybaseClient.SybaseConnection",
344 "Mono.Data.SybaseClient.SybaseDataAdapter",
346 providerList.Add (new DbProvider (
349 "Mono.Data.DB2Client",
350 "Mono.Data.DB2Client.DB2ClientConnection",
351 "Mono.Data.DB2Client.DB2ClientDataAdapter",
353 providerList.Add (new DbProvider (
356 "System.Data.OracleClient",
357 "System.Data.OracleClient.OracleConnection",
358 "System.Data.OracleClient.OracleDataAdapter",
362 public MenuBar CreateMenuBar ()
364 MenuBar menuBar = new MenuBar ();
374 item = new MenuItem ("New SQL# _Window");
375 item.Activated += new EventHandler (OnMenu_FileNewSqlWindow);
378 menu.Append (new SeparatorMenuItem ());
380 item = new MenuItem ("_New");
381 item.Activated += new EventHandler (OnMenu_FileNew);
384 item = new MenuItem ("_Open...");
385 item.Activated += new EventHandler (OnMenu_FileOpen);
388 item = new MenuItem ("_Save");
389 item.Activated += new EventHandler (OnMenu_FileSave);
392 item = new MenuItem ("Save _As...");
393 item.Activated += new EventHandler (OnMenu_FileSaveAs);
396 item = new MenuItem ("Close");
397 item.Activated += new EventHandler (OnMenu_FileClose);
400 menu.Append (new SeparatorMenuItem ());
402 // TODO: submenu Save Output
403 submenu = new Menu ();
404 subitem = new MenuItem ("CSV - Comma Separated Values");
405 //subitem.Activated += new EventHandler (OnMenu_FileSaveOutput_CSV);
406 submenu.Append(subitem);
407 subitem = new MenuItem ("TAB - Tab Separated Values");
408 //subitem.Activated += new EventHandler (OnMenu_FileSaveOutput_TAB);
409 submenu.Append(subitem);
410 subitem = new MenuItem ("XML");
411 //subitem.Activated += new EventHandler (OnMenu_FileSaveOutput_XML);
412 submenu.Append(subitem);
414 item = new MenuItem ("Save _Output...");
415 item.Submenu = submenu;
418 menu.Append (new SeparatorMenuItem ());
420 item = new MenuItem ("E_xit");
421 item.Activated += new EventHandler (OnMenu_FileExit);
424 barItem = new MenuItem ("_File");
425 barItem.Submenu = menu;
426 menuBar.Append (barItem);
432 item = new MenuItem ("_Undo");
433 //item.Activated += new EventHandler (OnMenu_EditUndo);
436 item = new MenuItem ("_Redo");
437 //item.Activated += new EventHandler (OnMenu_EditRedo);
440 menu.Append (new SeparatorMenuItem ());
442 item = new MenuItem ("Cu_t");
443 //item.Activated += new EventHandler (OnMenu_EditCut);
446 item = new MenuItem ("_Copy");
447 //item.Activated += new EventHandler (OnMenu_EditCopy);
450 item = new MenuItem ("_Paste");
451 //item.Activated += new EventHandler (OnMenu_EditPaste);
454 item = new MenuItem ("_Delete");
455 //item.Activated += new EventHandler (OnMenu_EditDelete);
458 menu.Append (new SeparatorMenuItem ());
460 item = new MenuItem ("_Find and Replace...");
461 //item.Activated += new EventHandler (OnMenu_EditFindReplace);
464 menu.Append (new SeparatorMenuItem ());
466 item = new MenuItem ("_Options");
467 //item.Activated += new EventHandler (OnMenu_EditOptions);
470 barItem = new MenuItem ("_Edit");
471 barItem.Submenu = menu;
472 menuBar.Append (barItem);
477 item = new MenuItem ("_Connect");
478 item.Activated += new EventHandler (OnMenu_SessionConnect);
481 item = new MenuItem ("_Disconnect");
482 item.Activated += new EventHandler (OnMenu_SessionDisconnect);
485 barItem = new MenuItem ("_Session");
486 barItem.Submenu = menu;
487 menuBar.Append (barItem);
492 item = new MenuItem ("_Execute");
493 item.Activated += new EventHandler (OnMenu_CommandExecute);
496 item = new MenuItem ("_Execute With Output to XML");
497 item.Activated += new EventHandler (OnMenu_CommandExecuteXML);
500 item = new MenuItem ("_Execute With Output to CSV");
501 item.Activated += new EventHandler (OnMenu_CommandExecuteCSV);
504 item = new MenuItem ("_Execute With Output to HTML");
505 item.Activated += new EventHandler (OnMenu_CommandExecuteHTML);
508 barItem = new MenuItem ("_Command");
509 barItem.Submenu = menu;
510 menuBar.Append (barItem);
515 void AppendText (string text)
517 AppendText (buf, text);
520 public void AppendTextWithoutScroll (TextBuffer buffer, string text)
523 text = text.Replace("\0","");
524 buffer.MoveMark(buf.InsertMark, buffer.EndIter);
525 if (text.Equals ("") == false) {
526 iter = buffer.EndIter;
527 buffer.Insert (iter, text);
529 iter = buffer.EndIter;
530 buffer.Insert (iter, "\n");
533 // WriteLine() to output text to bottom TextView
534 // for displaying result sets and logging messages
535 public void AppendText (TextBuffer buffer, string text)
537 AppendTextWithoutScroll(buffer,text);
538 while (Application.EventsPending ())
539 Application.RunIteration ();
540 textView.ScrollToMark (buf.InsertMark, 0.4, true, 0.0, 1.0);
543 public bool LoadExternalProvider (string strProviderAssembly,
544 string providerConnectionClass)
547 SqlSharpGtk.DebugWriteLine ("Loading external provider...");
548 providerAssembly = null;
549 providerAssembly = Assembly.Load (strProviderAssembly);
550 Type typ = providerAssembly.GetType (providerConnectionClass);
551 conn = (IDbConnection) Activator.CreateInstance (typ);
553 SqlSharpGtk.DebugWriteLine ("External provider loaded.");
555 catch (Exception f) {
556 string errorMessage = String.Format (
557 "Error: unable to load the assembly of the provider: {1} because: {2}",
560 Error (errorMessage);
566 void QuitApplication()
569 if(conn.State == ConnectionState.Open) {
570 Console.WriteLine("Closing connection...");
573 Console.WriteLine("Connection closed.");
576 if(grid.DataSource != null) {
578 grid.DataSource = null;
579 grid.DataMember = "";
584 if(SqlWindowCount == 0)
590 void UpdateTitleBar(EditorTab tab)
594 if(tab.filename.Equals(""))
595 title = tab.label.Text + " - " + ApplicationName;
597 title = tab.filename + " - " + ApplicationName;
600 title = ApplicationName;
605 void OnEditorTabSwitched (object o, GtkSharp.SwitchPageArgs args)
607 int page = (int) args.PageNum;
608 EditorTab tab = FindEditorTab(page);
609 UpdateTitleBar (tab);
612 void OnWindow_Delete (object o, GtkSharp.DeleteEventArgs args)
617 void OnExit (Gtk.Object o)
622 void OnMenu_FileNewSqlWindow (object o, EventArgs args)
624 SqlSharpGtk sqlSharp = new SqlSharpGtk ();
628 void OnMenu_FileNew (object o, EventArgs args)
631 sourceFileNotebook.CurrentPage = -1;
634 void OnMenu_FileOpen (object o, EventArgs args)
636 FileSelectionDialog openFileDialog =
637 new FileSelectionDialog ("Open File",
638 new FileSelectionEventHandler (OnOpenFile));
641 void OnOpenFile (object o, FileSelectionEventArgs args)
643 EditorTab etab = NewEditorTab();
645 etab.editor.LoadFromFile (args.Filename);
647 catch(Exception openFileException) {
648 Error("Error: Could not open file: \n" +
651 openFileException.Message);
654 TextBuffer buf = etab.editor.Buffer;
655 buf.Modified = false;
656 string basefile = Path.GetFileName (args.Filename);
657 etab.label.Text = basefile;
658 etab.basefilename = basefile;
659 etab.filename = args.Filename;
660 sourceFileNotebook.CurrentPage = -1;
661 UpdateTitleBar(etab);
664 EditorTab FindEditorTab (int searchPage)
666 EditorTab tab = null;
667 for (int t = 0; t < editorTabs.Count; t++) {
668 tab = (EditorTab) editorTabs[t];
669 if (tab.page == searchPage)
675 void OnMenu_FileSave (object o, EventArgs args)
677 int page = sourceFileNotebook.CurrentPage;
678 EditorTab tab = FindEditorTab(page);
680 if(tab.filename.Equals(""))
683 SaveFile(tab.filename);
684 tab.label.Text = tab.basefilename;
688 void SaveFile (string filename)
690 int page = sourceFileNotebook.CurrentPage;
691 EditorTab etab = FindEditorTab(page);
694 // FIXME: if file exists, ask if you want to
695 // overwrite. currently, it overwrites
697 etab.editor.SaveToFile (filename);
698 } catch(Exception saveFileException) {
699 Error("Error: Could not open file: \n" +
702 saveFileException.Message);
705 TextBuffer buf = etab.editor.Buffer;
706 buf.Modified = false;
709 void OnMenu_FileSaveAs (object o, EventArgs args)
716 FileSelectionDialog openFileDialog =
717 new FileSelectionDialog ("File Save As",
718 new FileSelectionEventHandler (OnSaveAsFile));
721 void OnSaveAsFile (object o, FileSelectionEventArgs args)
723 int page = sourceFileNotebook.CurrentPage;
724 EditorTab etab = FindEditorTab(page);
726 SaveFile(args.Filename);
728 string basefile = Path.GetFileName (args.Filename);
729 etab.label.Text = basefile;
730 etab.basefilename = basefile;
731 etab.filename = args.Filename;
732 UpdateTitleBar(etab);
735 void OnMenu_FileClose (object o, EventArgs args)
740 void OnCloseEditor (object obj, EventArgs args)
747 int page = sourceFileNotebook.CurrentPage;
748 SqlEditorSharp sqlEditor;
749 sqlEditor = (SqlEditorSharp) sourceFileNotebook.GetNthPage(page);
750 TextBuffer buffer = sqlEditor.Buffer;
751 if(buffer.Modified) {
752 // TODO: if text modified,
753 // ask if user wants to save
755 // use MessageDialog to prompt
756 RemoveEditorTab (sqlEditor.Tab, page);
759 RemoveEditorTab (sqlEditor.Tab, page);
765 void RemoveEditorTab (EditorTab tab, int page)
768 tab.editor.Tab = null;
771 editorTabs.Remove(tab);
772 sourceFileNotebook.RemovePage (page);
773 sourceFileNotebook.QueueDraw();
777 void OnMenu_FileExit (object o, EventArgs args)
782 void OnMenu_SessionConnect (object o, EventArgs args)
784 LoginDialog login = new LoginDialog (this);
788 void OnMenu_SessionDisconnect (object o, EventArgs args)
790 AppendText(buf, "Disconnecting...");
795 catch (Exception e) {
796 Error ("Error: Unable to disconnect." +
801 AppendText (buf, "Disconnected.");
804 void OnToolbar_ToggleResultsOutput ()
806 ToggleResultsOutput ();
809 void ToggleResultsOutput ()
811 if (outputResults == OutputResults.TextView) {
812 outputResults = OutputResults.DataGrid;
814 else if (outputResults == OutputResults.DataGrid) {
815 outputResults = OutputResults.TextView;
819 public void OnToolbar_Execute ()
821 ExecuteSQL (ExecuteOutputType.Normal, "");
824 // Execute SQL Commands
825 void ExecuteSQL (ExecuteOutputType outputType, string filename)
828 AppendText (buf, "Error: Not Connected.");
832 DataTable schemaTable = null;
834 int page = sourceFileNotebook.CurrentPage;
835 EditorTab tab = FindEditorTab(page);
843 cmd = conn.CreateCommand ();
845 catch (Exception ec) {
847 "Error: Unable to create command to execute: " +
852 SqlSharpGtk.DebugWriteLine ("get text from SQL editor...");
854 // get text from SQL editor
856 TextIter start_iter, end_iter;
858 exeBuff = tab.editor.Buffer;
859 start_iter = exeBuff.StartIter;
860 end_iter = exeBuff.EndIter;
861 sql = exeBuff.GetText(start_iter, end_iter, false);
863 catch (Exception et) {
865 "Error: Unable to get text from SQL editor: " +
871 cmd.CommandText = sql;
873 catch (Exception e) {
875 "Error: Unable to set SQL text to command.");
878 IDataReader reader = null;
879 SqlSharpGtk.DebugWriteLine ("Executing SQL: " + sql);
881 if ((outputResults == OutputResults.TextView &&
882 outputType == ExecuteOutputType.Normal) ||
883 outputType == ExecuteOutputType.HtmlFile ||
884 outputType == ExecuteOutputType.CsvFile) {
887 reader = cmd.ExecuteReader ();
889 catch (Exception e) {
890 //msg = "SQL Execution Error: " + e.Message;
891 msg = "SQL Execution Error: " + e;
896 if (reader == null) {
897 Error("Error: reader is null");
903 if (outputResults == OutputResults.TextView &&
904 outputType == ExecuteOutputType.Normal) {
906 DisplayData (reader);
912 else if(outputType == ExecuteOutputType.HtmlFile) {
913 schemaTable = reader.GetSchemaTable();
914 if(schemaTable != null && reader.FieldCount > 0) {
915 OutputDataToHtmlFile(reader, schemaTable, filename);
918 AppendText("Command executed.");
925 else if(outputType == ExecuteOutputType.CsvFile) {
926 schemaTable = reader.GetSchemaTable();
927 if(schemaTable != null && reader.FieldCount > 0) {
928 OutputDataToCsvFile(reader, schemaTable, filename);
931 AppendText("Command executed.");
939 DataTable dataTable = LoadDataTable (cmd);
941 case ExecuteOutputType.Normal:
942 AppendText("set DataGrid.DataSource to DataTable...");
943 grid.DataSource = dataTable;
944 AppendText("DataBind...");
946 AppendText("Clean up...");
948 grid.DataSource = null;
950 case ExecuteOutputType.XmlFile:
951 AppendText("Create DataSet...");
952 DataSet dataSet = new DataSet();
953 AppendText("Add DataTable to DataSet's DataTableCollection...");
954 dataSet.Tables.Add(dataTable);
955 AppendText("Write DataSet to XML file: " +
957 dataSet.WriteXml(filename);
958 AppendText("Clean up...");
971 catch (Exception e) {
972 //msg = "Error Displaying Data: " + e.Message;
973 msg = "Error Displaying Data: " + e;
978 public void OutputDataToHtmlFile(IDataReader rdr, DataTable dt, string file)
980 AppendText("Outputting results to HTML file " + file + "...");
981 StreamWriter outputFilestream = null;
983 outputFilestream = new StreamWriter(file);
986 Error("Error: Unable to setup output results file. " +
991 StringBuilder strHtml = new StringBuilder();
993 strHtml.Append("<html>\n<head><title>");
994 strHtml.Append("Results");
995 strHtml.Append("</title></head>\n");
996 strHtml.Append("<body>\n");
997 strHtml.Append("<h1>Results</h1>\n");
998 strHtml.Append("\t<table border=1>\n");
1000 outputFilestream.WriteLine(strHtml.ToString());
1003 strHtml = new StringBuilder();
1005 strHtml.Append("\t\t<tr>\n");
1006 for (int c = 0; c < rdr.FieldCount; c++) {
1007 strHtml.Append("\t\t\t<td><b>");
1008 string sColumnName = rdr.GetName(c);
1009 strHtml.Append(sColumnName);
1010 strHtml.Append("</b></td>\n");
1012 strHtml.Append("\t\t</tr>\n");
1013 outputFilestream.WriteLine(strHtml.ToString());
1017 string dataValue = "";
1020 strHtml = new StringBuilder();
1022 strHtml.Append("\t\t<tr>\n");
1023 for(col = 0; col < rdr.FieldCount; col++) {
1026 if(rdr.IsDBNull(col) == true)
1029 object obj = rdr.GetValue(col);
1030 dataValue = obj.ToString();
1032 strHtml.Append("\t\t\t<td>");
1033 strHtml.Append(dataValue);
1034 strHtml.Append("</td>\n");
1036 strHtml.Append("\t\t</tr>\n");
1037 outputFilestream.WriteLine(strHtml.ToString());
1040 outputFilestream.WriteLine("\t</table>\n</body>\n</html>\n");
1042 outputFilestream.Close();
1043 outputFilestream = null;
1044 AppendText("Outputting file done.");
1047 public void OutputDataToCsvFile(IDataReader rdr, DataTable dt, string file)
1049 AppendText("Outputting results to CSV file " + file + "...");
1050 StreamWriter outputFilestream = null;
1052 outputFilestream = new StreamWriter(file);
1054 catch(Exception e) {
1055 Error("Error: Unable to setup output results file. " +
1060 StringBuilder strCsv = null;
1063 string dataValue = "";
1066 strCsv = new StringBuilder();
1068 for(col = 0; col < rdr.FieldCount; col++) {
1073 if(rdr.IsDBNull(col) == true)
1076 object obj = rdr.GetValue(col);
1077 dataValue = "\"" + obj.ToString() + "\"";
1079 strCsv.Append(dataValue);
1081 outputFilestream.WriteLine(strCsv.ToString());
1085 outputFilestream.Close();
1086 outputFilestream = null;
1087 AppendText("Outputting file done.");
1090 void OnMenu_CommandExecute (object o, EventArgs args)
1092 ExecuteSQL (ExecuteOutputType.Normal, "");
1095 void OnMenu_CommandExecuteXML (object o, EventArgs args)
1097 ExecuteAndSaveResultsToFile (ExecuteOutputType.XmlFile);
1100 void OnMenu_CommandExecuteCSV (object o, EventArgs args)
1102 ExecuteAndSaveResultsToFile (ExecuteOutputType.CsvFile);
1105 void OnMenu_CommandExecuteHTML (object o, EventArgs args)
1107 ExecuteAndSaveResultsToFile (ExecuteOutputType.HtmlFile);
1110 ExecuteOutputType outType;
1111 void ExecuteAndSaveResultsToFile(ExecuteOutputType oType)
1114 FileSelectionDialog openFileDialog =
1115 new FileSelectionDialog ("Results File Save As",
1116 new FileSelectionEventHandler (OnSaveExeOutFile));
1119 void OnSaveExeOutFile (object o, FileSelectionEventArgs args)
1121 ExecuteSQL (outType, args.Filename);
1124 public void DisplayResult (IDataReader reader, DataTable schemaTable)
1126 const string zero = "0";
1127 StringBuilder column = null;
1128 StringBuilder line = null;
1129 StringBuilder hdrUnderline = null;
1130 string outData = "";
1137 char spacingChar = ' '; // a space
1138 char underlineChar = '='; // an equal sign
1140 string dataType; // .NET Type
1142 string dataTypeName; // native Database type
1143 DataRow row; // schema row
1145 line = new StringBuilder ();
1146 hdrUnderline = new StringBuilder ();
1149 OutputLine ("Fields in Query Result: " +
1153 Error ("Error: Unable to get FieldCount: " +
1160 for(c = 0; c < reader.FieldCount; c++) {
1162 DataRow schemaRow = schemaTable.Rows[c];
1163 string columnHeader = reader.GetName (c);
1164 if (columnHeader.Equals (""))
1165 columnHeader = "column";
1166 if (columnHeader.Length > 32)
1167 columnHeader = columnHeader.Substring (0,32);
1170 columnSize = (int) schemaRow["ColumnSize"];
1171 theType = reader.GetFieldType(c);
1172 dataType = theType.ToString();
1173 //dataTypeName = reader.GetDataTypeName(c);
1176 case "System.DateTime":
1179 case "System.Boolean":
1184 hdrLen = Math.Max (columnHeader.Length, columnSize);
1191 line.Append(columnHeader);
1192 if(columnHeader.Length < hdrLen) {
1193 spacing = hdrLen - columnHeader.Length;
1194 line.Append(spacingChar, spacing);
1196 hdrUnderline.Append(underlineChar, hdrLen);
1199 hdrUnderline.Append(" ");
1201 catch(Exception e) {
1202 Error ("Error: Unable to display header: " +
1207 OutputHeader(line.ToString());
1210 OutputHeader(hdrUnderline.ToString());
1212 hdrUnderline = null;
1218 while(reader.Read()) {
1221 line = new StringBuilder();
1222 for(c = 0; c < reader.FieldCount; c++) {
1224 string dataValue = "";
1225 column = new StringBuilder();
1228 row = schemaTable.Rows[c];
1229 string colhdr = (string) reader.GetName(c);
1230 if(colhdr.Equals(""))
1232 if(colhdr.Length > 32)
1233 colhdr = colhdr.Substring(0, 32);
1235 columnSize = (int) row["ColumnSize"];
1236 theType = reader.GetFieldType(c);
1237 dataType = theType.ToString();
1239 //dataTypeName = reader.GetDataTypeName(c);
1242 case "System.DateTime":
1245 case "System.Boolean":
1250 columnSize = Math.Max(colhdr.Length, columnSize);
1259 if(reader.IsDBNull(c)) {
1266 if(dataType.Equals("System.DateTime")) {
1268 // display date in ISO format
1269 // "YYYY-MM-DD HH:MM:SS"
1270 dt = reader.GetDateTime(c);
1271 sb = new StringBuilder();
1274 sb.Append("000" + dt.Year);
1275 else if(dt.Year < 100)
1276 sb.Append("00" + dt.Year);
1277 else if(dt.Year < 1000)
1278 sb.Append("0" + dt.Year);
1284 sb.Append(zero + dt.Month);
1286 sb.Append(dt.Month);
1290 sb.Append(zero + dt.Day);
1296 sb.Append(zero + dt.Hour);
1302 sb.Append(zero + dt.Minute);
1304 sb.Append(dt.Minute);
1308 sb.Append(zero + dt.Second);
1310 sb.Append(dt.Second);
1312 dataValue = sb.ToString();
1315 object o = reader.GetValue(c);
1316 dataValue = o.ToString();
1319 dataLen = dataValue.Length;
1325 dataValue = dataValue.Substring(0,32);
1329 columnSize = Math.Max (columnSize, dataLen);
1331 if(dataLen < columnSize) {
1334 case "System.SByte":
1335 case "System.Int16":
1336 case "System.UInt16":
1337 case "System.Int32":
1338 case "System.UInt32":
1339 case "System.Int64":
1340 case "System.UInt64":
1341 case "System.Single":
1342 case "System.Double":
1343 case "System.Decimal":
1344 outData = dataValue.PadLeft(columnSize);
1347 outData = dataValue.PadRight(columnSize);
1350 outData = outData + " ";
1353 outData = dataValue;
1355 line.Append (outData);
1358 OutputData (line.ToString ());
1362 catch (Exception rr) {
1363 Error ("Error: Unable to read next row: " +
1368 OutputLine ("\nRows retrieved: " + numRows.ToString());
1372 public void DisplayData(IDataReader reader)
1374 bool another = false;
1375 DataTable schemaTable = null;
1378 OutputLine ("Display any result sets...");
1381 // by Default, data reader has the
1382 // first Result set if any
1385 OutputLine ("Display the result set " + ResultSet);
1387 if (reader.FieldCount > 0) {
1388 // SQL Query (SELECT)
1389 // RecordsAffected -1 and DataTable has a reference
1391 schemaTable = reader.GetSchemaTable ();
1393 catch (Exception es) {
1394 Error ("Error: Unable to get schema table: " +
1399 AppendText (buf, "Display Result...");
1400 DisplayResult (reader, schemaTable);
1402 else if (reader.RecordsAffected >= 0) {
1403 // SQL Command (INSERT, UPDATE, or DELETE)
1404 // RecordsAffected >= 0
1407 records = reader.RecordsAffected;
1408 AppendText (buf, "SQL Command Records Affected: " +
1411 catch (Exception er) {
1412 Error ("Error: Unable to get records affected: " +
1418 // SQL Command (not INSERT, UPDATE, nor DELETE)
1419 // RecordsAffected -1 and DataTable has a null reference
1420 AppendText (buf, "SQL Command Executed.");
1423 // get next result set (if anymore is left)
1425 another = reader.NextResult ();
1427 catch(Exception e) {
1428 Error ("Error: Unable to read next result: " +
1432 } while(another == true);
1435 // used for outputting message, but if silent is set,
1437 public void OutputLine(string line)
1439 //if(silent == false)
1443 // used for outputting the header columns of a result
1444 public void OutputHeader(string line)
1446 //if(showHeader == true)
1450 // OutputData() - used for outputting data
1451 // if an output filename is set, then the data will
1452 // go to a file; otherwise, it will go to the Console.
1453 public void OutputData(string line)
1455 //if(outputFilestream == null)
1456 // Console.WriteLine(line);
1458 // outputFilestream.WriteLine(line);
1459 AppendTextWithoutScroll(buf,line);
1462 public void Error(string message)
1464 Console.WriteLine(message);
1465 Console.Out.Flush();
1466 AppendText(buf, message);
1469 bool OpenInternalProvider ()
1473 string providerKey = dbProvider.Key;
1474 switch (providerKey.ToUpper ()) {
1477 conn = new SqlConnection ();
1479 catch (Exception e) {
1480 msg = "Error: unable to create connection: " +
1488 conn = new OdbcConnection ();
1490 catch (Exception e) {
1491 msg = "Error: unable to create connection: " +
1499 conn = new OleDbConnection ();
1501 catch (Exception e) {
1502 msg = "Error: unable to create connection: " +
1509 msg = "Error: provider not supported.";
1516 bool OpenExternalProvider()
1518 bool success = false;
1520 success = LoadExternalProvider (
1521 dbProvider.Assembly,
1522 dbProvider.ConnectionClass);
1527 public DbDataAdapter CreateDbDataAdapter (IDbCommand cmd)
1530 DbDataAdapter dbAdapter = null;
1531 if (dbProvider.InternalProvider == true) {
1532 dbAdapter = CreateInternalDataAdapter (cmd);
1535 dbAdapter = CreateExternalDataAdapter (dbProvider.AdapterClass, cmd);
1540 public DbDataAdapter CreateInternalDataAdapter (IDbCommand cmd)
1543 DbDataAdapter dbAdapter = null;
1544 string providerKey = dbProvider.Key;
1545 switch (providerKey.ToUpper ()) {
1548 dbAdapter = new SqlDataAdapter (cmd as SqlCommand);
1550 catch (Exception e) {
1551 msg = "Error: unable to create adapter: " +
1560 dbAdapter = new OleDbDataAdapter (cmd as OleDbCommand);
1562 catch (Exception e) {
1563 msg = "Error: unable to create adapter: " +
1571 dbAdapter = new OdbcDataAdapter (cmd as OdbcCommand);
1573 catch (Exception e) {
1574 msg = "Error: unable to create adapter: " +
1584 public DbDataAdapter CreateExternalDataAdapter (string adapterClass, IDbCommand cmd)
1586 adapterType = providerAssembly.GetType (adapterClass);
1587 System.Object ad = Activator.CreateInstance (adapterType);
1589 // set property SelectCommand on DbDataAdapter
1590 PropertyInfo prop = adapterType.GetProperty("SelectCommand");
1591 prop.SetValue (ad, cmd, null);
1593 return (DbDataAdapter) ad;
1596 public DataTable LoadDataTable (IDbCommand dbcmd)
1598 string status = String.Empty;
1600 AppendText("Create DbDataAdapter...");
1601 SqlSharpDataAdapter adapter = new SqlSharpDataAdapter (dbcmd);
1603 AppendText("Create DataTable...");
1604 DataTable dataTable = new DataTable ();
1606 AppendText("Fill data into DataTable via DbDataAdapter...");
1608 int rowsAddedOrRefreshed = 0;
1609 IDataReader reader = null;
1612 reader = dbcmd.ExecuteReader ();
1613 if (reader.FieldCount > 0)
1614 rowsAddedOrRefreshed = adapter.FillTable (dataTable, reader);
1616 catch(Exception sqle) {
1617 status = "Error: " + sqle.Message;
1620 if (status.Equals(String.Empty)) {
1621 AppendText("Rows successfully Added or Refreshed in the DataTable: " +
1622 rowsAddedOrRefreshed);
1623 int rowsAffected = reader.RecordsAffected;
1624 AppendText("Rows Affected: " + rowsAffected);
1626 int fields = ((IDataRecord) reader).FieldCount;
1627 AppendText("Field Count: " + fields);
1630 status = "Rows Selected: " + rowsAddedOrRefreshed +
1631 " Fields: " + fields;
1634 status = "Rows Modified: " + rowsAffected;
1637 AppendText("Status: " + status);
1642 AppendText("Return DataTable...");
1646 public bool OpenDataSource ()
1649 bool gotClass = false;
1651 msg = "Attempt to open connection...";
1652 AppendText (buf, msg);
1657 if (dbProvider.InternalProvider == true) {
1658 gotClass = OpenInternalProvider ();
1661 gotClass = OpenExternalProvider ();
1664 catch (Exception e) {
1665 msg = "Error: Unable to create Connection object. " +
1671 if (gotClass == false)
1674 conn.ConnectionString = connectionString;
1678 if( conn.State == ConnectionState.Open)
1679 AppendText (buf, "Open was successfull.");
1681 AppendText (buf, "Error: Open failed.");
1685 catch (Exception e) {
1686 msg = "Error: Could not open data source: " + e.Message;
1693 public static void DebugWriteLine (string text)
1696 Console.WriteLine (text);
1697 Console.Out.Flush ();
1701 public static int Main (string[] args)
1703 Application.Init ();
1704 SqlSharpGtk sqlSharp = new SqlSharpGtk ();