2 // SqlSharpCli.cs - main driver for Mono SQL Query Command Line Interface
3 // found in mcs/tools/SqlSharp
5 // This program is included in Mono and is licenced under the GPL.
6 // http://www.fsf.org/licenses/gpl.html
8 // For more information about Mono,
9 // visit http://www.mono-project.com/
11 // To build SqlSharpCli.cs
12 // $ mcs /out:sqlsharp.exe SqlSharpCli.cs /r:System.Data.dll
15 // $ mono sqlsharp.exe
18 // $ mint sqlsharp.exe
20 // To run batch commands and get the output, do something like:
21 // $ cat commands_example.txt | mono sqlsharp.exe -s > results.txt
24 // Daniel Morgan <danielmorgan@verizon.net>
26 // (C)Copyright 2002-2004 Daniel Morgan
30 using System.Collections;
32 using System.Data.Common;
33 using System.Data.OleDb;
34 using System.Data.SqlClient;
36 using System.Reflection;
37 using System.Runtime.Remoting;
40 namespace Mono.Data.SqlSharp {
42 public enum FileFormat {
50 // SQL Sharp - Command Line Interface
51 public class SqlSharpCli
54 private bool UseParameters = true;
55 private bool UseSimpleReader = false;
57 private IDbConnection conn = null;
59 private string provider = ""; // name of internal provider
60 // {OleDb,SqlClient,MySql,Odbc,Oracle,
61 // PostgreSql,SqlLite,Sybase,Tds} however, it
62 // can be set to LOADEXTPROVIDER to load an external provider
63 private string providerAssembly = "";
64 // filename of assembly
65 // for example: "Mono.Data.MySql"
66 private string providerConnectionClass = "";
68 // in the provider assembly that implements the IDbConnection
69 // interface. for example: "Mono.Data.MySql.MySqlConnection"
71 private StringBuilder build = null; // SQL string to build
72 private string buff = ""; // SQL string buffer
74 private string connectionString = "";
76 private string inputFilename = "";
77 private string outputFilename = "";
78 private StreamReader inputFilestream = null;
79 private StreamWriter outputFilestream = null;
81 private FileFormat outputFileFormat = FileFormat.Html;
83 private bool silent = false;
84 private bool showHeader = true;
86 private Hashtable internalVariables = new Hashtable();
88 // DisplayResult - used to Read() display a result set
89 // called by DisplayData()
91 public bool DisplayResult (IDataReader reader, DataTable schemaTable)
93 StringBuilder column = null;
94 StringBuilder line = null;
95 StringBuilder hdrUnderline = null;
103 char spacingChar = ' '; // a space
104 char underlineChar = '='; // an equal sign
106 string dataType; // .NET Type
108 DataRow row; // schema row
110 line = new StringBuilder ();
111 hdrUnderline = new StringBuilder ();
115 for (c = 0; c < reader.FieldCount; c++) {
117 DataRow schemaRow = schemaTable.Rows [c];
118 string columnHeader = reader.GetName (c);
119 if (columnHeader.Equals (""))
120 columnHeader = "column";
121 if (columnHeader.Length > 32)
122 columnHeader = columnHeader.Substring (0,32);
125 columnSize = (int) schemaRow ["ColumnSize"];
126 theType = reader.GetFieldType (c);
127 dataType = theType.ToString ();
130 case "System.DateTime":
133 case "System.Boolean":
139 case "System.Single":
142 case "System.Double":
150 case "System.UInt32":
156 case "System.UInt64":
159 case "System.Decimal":
169 hdrLen = columnHeader.Length;
175 hdrLen = System.Math.Max (hdrLen, columnSize);
177 line.Append (columnHeader);
178 if (columnHeader.Length < hdrLen) {
179 spacing = hdrLen - columnHeader.Length;
180 line.Append (spacingChar, spacing);
182 hdrUnderline.Append (underlineChar, hdrLen);
185 hdrUnderline.Append (" ");
187 catch (Exception e) {
188 OutputLine ("Error: Unable to display header: " + e.Message);
192 OutputHeader (line.ToString ());
195 OutputHeader (hdrUnderline.ToString ());
203 while (reader.Read ()) {
206 line = new StringBuilder ();
207 for(c = 0; c < reader.FieldCount; c++) {
209 string dataValue = "";
210 column = new StringBuilder ();
213 row = schemaTable.Rows [c];
214 string colhdr = (string) reader.GetName (c);
215 if (colhdr.Equals (""))
217 if (colhdr.Length > 32)
218 colhdr = colhdr.Substring (0, 32);
220 columnSize = (int) row ["ColumnSize"];
221 theType = reader.GetFieldType (c);
222 dataType = theType.ToString ();
225 case "System.DateTime":
228 case "System.Boolean":
234 case "System.Single":
237 case "System.Double":
245 case "System.UInt32":
251 case "System.UInt64":
254 case "System.Decimal":
264 hdrLen = colhdr.Length;
270 columnSize = System.Math.Max (colhdr.Length, columnSize);
275 if (!reader.IsDBNull (c)) {
276 object o = reader.GetValue (c);
277 if (o.GetType ().ToString ().Equals ("System.Byte[]"))
278 dataValue = GetHexString ( (byte[]) o);
280 dataValue = o.ToString ();
282 dataLen = dataValue.Length;
289 dataValue = dataValue.Substring (0, 32);
293 if (dataValue.Equals(""))
296 columnSize = System.Math.Max (columnSize, dataLen);
298 if (dataLen < columnSize) {
303 case "System.UInt16":
305 case "System.UInt32":
307 case "System.UInt64":
308 case "System.Single":
309 case "System.Double":
310 case "System.Decimal":
311 outData = dataValue.PadLeft (columnSize);
314 outData = dataValue.PadRight (columnSize);
321 line.Append (outData);
324 OutputData (line.ToString ());
327 catch (Exception rr) {
328 OutputLine ("Error: Unable to read next row: " + rr.Message);
332 OutputLine ("\nRows retrieved: " + numRows.ToString ());
334 return true; // return true - success
337 public static string GetHexString (byte[] bytes)
341 if (bytes.Length > 0) {
342 StringBuilder sb = new StringBuilder ();
344 for (int z = 0; z < bytes.Length; z++)
345 sb.Append (bytes [z].ToString ("x"));
347 bvalue = "0x" + sb.ToString ();
353 public void OutputDataToHtmlFile (IDataReader rdr, DataTable dt)
355 StringBuilder strHtml = new StringBuilder ();
357 strHtml.Append ("<html> \n <head> <title>");
358 strHtml.Append ("Results");
359 strHtml.Append ("</title> </head>");
360 strHtml.Append ("<body>");
361 strHtml.Append ("<h1> Results </h1>");
362 strHtml.Append ("<table border=1>");
364 outputFilestream.WriteLine (strHtml.ToString ());
366 strHtml = new StringBuilder ();
368 strHtml.Append ("<tr>");
369 foreach (DataRow schemaRow in dt.Rows) {
370 strHtml.Append ("<td> <b>");
371 object dataObj = schemaRow ["ColumnName"];
372 string sColumnName = dataObj.ToString ();
373 strHtml.Append (sColumnName);
374 strHtml.Append ("</b> </td>");
376 strHtml.Append ("</tr>");
377 outputFilestream.WriteLine (strHtml.ToString ());
381 string dataValue = "";
383 while (rdr.Read ()) {
384 strHtml = new StringBuilder ();
386 strHtml.Append ("<tr>");
387 for (col = 0; col < rdr.FieldCount; col++) {
390 if (rdr.IsDBNull (col) == true)
393 object obj = rdr.GetValue (col);
394 dataValue = obj.ToString ();
396 strHtml.Append ("<td>");
397 strHtml.Append (dataValue);
398 strHtml.Append ("</td>");
400 strHtml.Append ("\t\t</tr>");
401 outputFilestream.WriteLine (strHtml.ToString ());
404 outputFilestream.WriteLine (" </table> </body> \n </html>");
408 // DisplayData - used to display any Result Sets
409 // from execution of SQL SELECT Query or Queries
410 // called by DisplayData.
411 // ExecuteSql() only calls this function
412 // for a Query, it does not get
414 public void DisplayData (IDataReader reader)
416 DataTable schemaTable = null;
420 // by Default, SqlDataReader has the
421 // first Result set if any
424 OutputLine ("Display the result set " + ResultSet);
426 schemaTable = reader.GetSchemaTable ();
428 if (reader.FieldCount > 0) {
429 // SQL Query (SELECT)
430 // RecordsAffected -1 and DataTable has a reference
431 OutputQueryResult (reader, schemaTable);
433 else if (reader.RecordsAffected >= 0) {
434 // SQL Command (INSERT, UPDATE, or DELETE)
435 // RecordsAffected >= 0
436 Console.WriteLine ("SQL Command Records Affected: " + reader.RecordsAffected);
439 // SQL Command (not INSERT, UPDATE, nor DELETE)
440 // RecordsAffected -1 and DataTable has a null reference
441 Console.WriteLine ("SQL Command Executed.");
444 // get next result set (if anymore is left)
445 } while (reader.NextResult ());
448 // display the result in a simple way
449 // new ADO.NET providers may have not certain
450 // things implemented yet, such as, TableSchema
452 public void DisplayDataSimple (IDataReader reader)
455 Console.WriteLine ("Reading Data using simple reader...");
456 while (reader.Read ()){
458 Console.WriteLine ("Row: " + row);
459 for (int col = 0; col < reader.FieldCount; col++) {
461 Console.WriteLine (" Field: " + co);
463 string dname = (string) reader.GetName (col);
466 if (dname.Equals (String.Empty))
468 Console.WriteLine (" Name: " + dname);
471 if (reader.IsDBNull (col))
474 dvalue = reader.GetValue (col).ToString ();
475 Console.WriteLine (" Value: " + dvalue);
478 Console.WriteLine ("\n" + row + " ROWS RETRIEVED\n");
481 public void OutputQueryResult (IDataReader dreader, DataTable dtable)
483 if (outputFilestream == null) {
484 DisplayResult (dreader, dtable);
487 switch (outputFileFormat) {
488 case FileFormat.Normal:
489 DisplayResult (dreader, dtable);
491 case FileFormat.Html:
492 OutputDataToHtmlFile (dreader, dtable);
495 Console.WriteLine ("Error: Output data file format not supported.");
501 public void BuildParameters (IDbCommand cmd)
503 if (UseParameters == true) {
505 ParametersBuilder parmsBuilder = new ParametersBuilder (cmd, BindVariableCharacter.Colon);
507 Console.WriteLine ("Get Parameters (if any)...");
508 parmsBuilder.ParseParameters ();
509 IList parms = (IList) cmd.Parameters;
511 Console.WriteLine ("Print each parm...");
512 for (int p = 0; p < parms.Count; p++) {
515 IDataParameter prm = (IDataParameter) parms[p];
516 theParmName = prm.ParameterName;
520 if (parmsBuilder.ParameterMarkerCharacter == '?') {
521 Console.Write ("Enter Parameter " +
524 inValue = Console.ReadLine();
528 found = GetInternalVariable (theParmName, out inValue);
533 Console.Write ("Enter Parameter " + (p + 1).ToString () +
534 ": " + theParmName + ": ");
535 inValue = Console.ReadLine ();
544 // ExecuteSql - Execute the SQL Command(s) and/or Query(ies)
545 public void ExecuteSql (string sql)
549 IDbCommand cmd = null;
550 IDataReader reader = null;
552 cmd = conn.CreateCommand();
554 // set command properties
555 cmd.CommandType = CommandType.Text;
556 cmd.CommandText = sql;
557 cmd.Connection = conn;
559 BuildParameters (cmd);
562 reader = cmd.ExecuteReader ();
564 if (UseSimpleReader == false)
565 DisplayData (reader);
567 DisplayDataSimple (reader);
572 catch (Exception e) {
573 msg = "Error: " + e.Message;
574 Console.WriteLine (msg);
582 // ExecuteSql - Execute the SQL Commands (no SELECTs)
583 public void ExecuteSqlNonQuery (string sql)
587 IDbCommand cmd = null;
588 int rowsAffected = -1;
590 cmd = conn.CreateCommand();
592 // set command properties
593 cmd.CommandType = CommandType.Text;
594 cmd.CommandText = sql;
595 cmd.Connection = conn;
597 BuildParameters(cmd);
600 rowsAffected = cmd.ExecuteNonQuery ();
602 Console.WriteLine ("Rows affected: " + rowsAffected);
605 msg = "Error: " + e.Message;
606 Console.WriteLine (msg);
613 public void ExecuteSqlScalar(string sql)
617 IDbCommand cmd = null;
618 string retrievedValue = "";
620 cmd = conn.CreateCommand ();
622 // set command properties
623 cmd.CommandType = CommandType.Text;
624 cmd.CommandText = sql;
625 cmd.Connection = conn;
627 BuildParameters(cmd);
630 retrievedValue = (string) cmd.ExecuteScalar ().ToString ();
631 Console.WriteLine ("Retrieved value: " + retrievedValue);
634 msg = "Error: " + e.Message;
635 Console.WriteLine (msg);
642 public void ExecuteSqlXml(string sql, string[] parms)
644 string filename = "";
646 if (parms.Length != 2) {
647 Console.WriteLine ("Error: wrong number of parameters");
651 filename = parms [1];
653 catch (Exception e) {
654 Console.WriteLine ("Error: Unable to setup output results file. " + e.Message);
659 IDbCommand cmd = null;
661 cmd = conn.CreateCommand ();
663 // set command properties
664 cmd.CommandType = CommandType.Text;
665 cmd.CommandText = sql;
666 cmd.Connection = conn;
668 BuildParameters (cmd);
669 DataSet dataSet = new DataSet ();
670 DbDataAdapter adapter = CreateNewDataAdapter (cmd, conn);
671 adapter.Fill (dataSet);
672 dataSet.WriteXml (filename);
673 OutputLine ("Data written to xml file: " + filename);
675 catch (Exception exexml) {
676 Console.WriteLine ("Error: Execute SQL XML Failure: " + exexml);
680 public DbDataAdapter CreateNewDataAdapter (IDbCommand command, IDbConnection connection)
682 DbDataAdapter adapter = null;
686 adapter = (DbDataAdapter) new OleDbDataAdapter ();
689 adapter = (DbDataAdapter) new SqlDataAdapter ();
691 case "LOADEXTPROVIDER":
692 adapter = CreateExternalDataAdapter (command, connection);
697 Console.WriteLine("Error: Data Adapter not found in provider.");
700 IDbDataAdapter dbAdapter = (IDbDataAdapter) adapter;
701 dbAdapter.SelectCommand = command;
706 public DbDataAdapter CreateExternalDataAdapter (IDbCommand command, IDbConnection connection)
708 DbDataAdapter adapter = null;
710 Assembly ass = Assembly.Load (providerAssembly);
711 Type [] types = ass.GetTypes ();
712 foreach (Type t in types) {
713 if (t.IsSubclassOf (typeof (System.Data.Common.DbDataAdapter))) {
714 if (t.Namespace.Equals (conType.Namespace))
715 adapter = (DbDataAdapter) Activator.CreateInstance (t);
722 // like ShowHelp - but only show at the beginning
723 // only the most important commands are shown
724 // like help and quit
725 public void StartupHelp ()
727 OutputLine (@"Type: \Q to quit");
728 OutputLine (@" \ConnectionString to set the ConnectionString");
729 OutputLine (@" \Provider to set the Provider:");
730 OutputLine (@" {OleDb,SqlClient,MySql,Odbc,DB2,");
731 OutputLine (@" Oracle,PostgreSql,Sqlite,Sybase,Tds)");
732 OutputLine (@" \Open to open the connection");
733 OutputLine (@" \Close to close the connection");
734 OutputLine (@" \e to execute SQL query (SELECT)");
735 OutputLine (@" \h to show help (all commands).");
736 OutputLine (@" \defaults to show default variables.");
740 // ShowHelp - show the help - command a user can enter
741 public void ShowHelp ()
743 Console.WriteLine ("");
744 Console.WriteLine (@"Type: \Q to quit");
745 Console.WriteLine (@" \ConnectionString to set the ConnectionString");
746 Console.WriteLine (@" \Provider to set the Provider:");
747 Console.WriteLine (@" {OleDb,SqlClient,MySql,Odbc,MSODBC,");
748 Console.WriteLine (@" Oracle,PostgreSql,Sqlite,Sybase,Tds}");
749 Console.WriteLine (@" \Open to open the connection");
750 Console.WriteLine (@" \Close to close the connection");
751 Console.WriteLine (@" \e to execute SQL query (SELECT)");
752 Console.WriteLine (@" \exenonquery to execute an SQL non query (not a SELECT).");
753 Console.WriteLine (@" \exescalar to execute SQL to get a single row and single column.");
754 Console.WriteLine (@" \exexml FILENAME to execute SQL and save output to XML file.");
755 Console.WriteLine (@" \f FILENAME to read a batch of SQL# commands from file.");
756 Console.WriteLine (@" \o FILENAME to write result of commands executed to file.");
757 Console.WriteLine (@" \load FILENAME to load from file SQL commands into SQL buffer.");
758 Console.WriteLine (@" \save FILENAME to save SQL commands from SQL buffer to file.");
759 Console.WriteLine (@" \h to show help (all commands).");
760 Console.WriteLine (@" \defaults to show default variables, such as,");
761 Console.WriteLine (@" Provider and ConnectionString.");
762 Console.WriteLine (@" \s {TRUE, FALSE} to silent messages.");
763 Console.WriteLine (@" \r to reset or clear the query buffer.");
765 Console.WriteLine (@" \set NAME VALUE to set an internal variable.");
766 Console.WriteLine (@" \unset NAME to remove an internal variable.");
767 Console.WriteLine (@" \variable NAME to display the value of an internal variable.");
768 Console.WriteLine (@" \loadextprovider ASSEMBLY CLASS to load the provider");
769 Console.WriteLine (@" use the complete name of its assembly and");
770 Console.WriteLine (@" its Connection class.");
771 Console.WriteLine (@" \print - show what's in the SQL buffer now.");
772 Console.WriteLine (@" \UseParameters (TRUE,FALSE) to use parameters when executing SQL.");
773 Console.WriteLine (@" \UseSimpleReader (TRUE,FALSE) to use simple reader when displaying results.");
774 Console.WriteLine ();
777 public bool WaitForEnterKey ()
779 Console.Write("Waiting... Press Enter key to continue. ");
780 string entry = Console.ReadLine();
781 if (entry.ToUpper() == "Q")
786 // ShowDefaults - show defaults for connection variables
787 public void ShowDefaults()
789 Console.WriteLine ();
790 if (provider.Equals (""))
791 Console.WriteLine ("Provider is not set.");
793 Console.WriteLine ("The default Provider is " + provider);
794 if (provider.Equals ("LOADEXTPROVIDER")) {
795 Console.WriteLine (" Assembly: " + providerAssembly);
796 Console.WriteLine (" Connection Class: " + providerConnectionClass);
799 Console.WriteLine ();
800 if (connectionString.Equals (""))
801 Console.WriteLine ("ConnectionString is not set.");
803 Console.WriteLine ("The default ConnectionString is: ");
804 Console.WriteLine (" \"" + connectionString + "\"");
805 Console.WriteLine ();
809 // OpenDataSource - open connection to the data source
810 public void OpenDataSource ()
814 OutputLine ("Opening connection...");
819 conn = new OleDbConnection ();
822 conn = new SqlConnection ();
824 case "LOADEXTPROVIDER":
825 if (LoadExternalProvider () == false)
829 Console.WriteLine ("Error: Bad argument or provider not supported.");
832 } catch (Exception e) {
833 msg = "Error: Unable to create Connection object because: " + e.Message;
834 Console.WriteLine (msg);
838 conn.ConnectionString = connectionString;
842 if (conn.State == ConnectionState.Open)
843 OutputLine ("Open was successfull.");
844 } catch (Exception e) {
845 msg = "Exception Caught Opening. " + e.Message;
846 Console.WriteLine (msg);
851 // CloseDataSource - close the connection to the data source
852 public void CloseDataSource () {
856 OutputLine ("Attempt to Close...");
859 OutputLine ("Close was successfull.");
860 } catch(Exception e) {
861 msg = "Exeception Caught Closing. " + e.Message;
862 Console.WriteLine (msg);
868 // ChangeProvider - change the provider string variable
869 public void ChangeProvider (string[] parms) {
873 if (parms.Length == 2) {
874 string parm = parms [1].ToUpper ();
877 extp = new string[3] {
879 @"System.Data.OracleClient, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089",
880 "System.Data.OracleClient.OracleConnection"};
881 SetupExternalProvider (extp);
882 UseParameters = false;
883 UseSimpleReader = false;
886 extp = new string[3] {
888 @"Mono.Data.TdsClient, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=0738eb9f132ed756",
889 "Mono.Data.TdsClient.TdsConnection"};
890 SetupExternalProvider (extp);
891 UseParameters = false;
892 UseSimpleReader = false;
895 extp = new string[3] {
897 @"Mono.Data.SybaseClient, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=0738eb9f132ed756",
898 "Mono.Data.SybaseClient.SybaseConnection"};
899 SetupExternalProvider (extp);
900 UseParameters = false;
901 UseSimpleReader = false;
904 extp = new string[3] {
906 @"ByteFX.Data, Version=0.7.6.1, Culture=neutral, PublicKeyToken=0738eb9f132ed756",
907 "ByteFX.Data.MySqlClient.MySqlConnection"};
908 SetupExternalProvider (extp);
909 UseParameters = false;
910 UseSimpleReader = false;
914 extp = new string[3] {
916 @"MySql.Data, Version=1.0.7.30073, Culture=neutral, PublicKeyToken=8e323390df8d9ed4",
917 "MySql.Data.MySqlClient.MySqlConnection"};
918 SetupExternalProvider (extp);
919 UseParameters = false;
920 UseSimpleReader = false;
923 extp = new string[3] {
925 @"Mono.Data.SqliteClient, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=0738eb9f132ed756",
926 "Mono.Data.SqliteClient.SqliteConnection"};
927 SetupExternalProvider (extp);
928 UseParameters = false;
929 UseSimpleReader = true;
932 UseParameters = false;
933 UseSimpleReader = false;
936 case "ODBC": // for MS NET 1.1 and above
937 extp = new string[3] {
939 @"System.Data, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089",
940 "System.Data.Odbc.OdbcConnection"};
941 SetupExternalProvider (extp);
942 UseParameters = false;
943 UseSimpleReader = false;
945 case "MSODBC": // for MS NET 1.0
946 extp = new string[3] {
948 @"Microsoft.Data.Odbc, Culture=neutral, PublicKeyToken=b77a5c561934e089, Version=1.0.3300.0",
949 "Microsoft.Data.Odbc.OdbcConnection"};
950 SetupExternalProvider (extp);
951 UseParameters = false;
952 UseSimpleReader = false;
955 UseParameters = false;
956 UseSimpleReader = true;
960 extp = new string[3] {
962 @"FirebirdSql.Data.Firebird, Version=1.7.1.0, Culture=neutral, PublicKeyToken=0706f5520aae4ff4",
963 "FirebirdSql.Data.Firebird.FbConnection"};
964 SetupExternalProvider (extp);
965 UseParameters = false;
966 UseSimpleReader = false;
970 extp = new string[3] {
972 @"Npgsql, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7",
973 "Npgsql.NpgsqlConnection"};
974 SetupExternalProvider (extp);
975 UseParameters = false;
976 UseSimpleReader = false;
979 Console.WriteLine ("Error: " + "Bad argument or Provider not supported.");
982 OutputLine ("The default Provider is " + provider);
983 if (provider.Equals ("LOADEXTPROVIDER")) {
984 OutputLine (" Assembly: " +
986 OutputLine (" Connection Class: " +
987 providerConnectionClass);
991 Console.WriteLine ("Error: provider only has one parameter.");
994 // ChangeConnectionString - change the connection string variable
995 public void ChangeConnectionString (string entry)
997 if (entry.Length > 18)
998 connectionString = entry.Substring (18, entry.Length - 18);
1000 connectionString = "";
1003 public void SetupOutputResultsFile (string[] parms) {
1004 if (parms.Length != 2) {
1005 Console.WriteLine ("Error: wrong number of parameters");
1009 outputFilestream = new StreamWriter (parms[1]);
1011 catch (Exception e) {
1012 Console.WriteLine ("Error: Unable to setup output results file. " + e.Message);
1017 public void SetupInputCommandsFile (string[] parms)
1019 if (parms.Length != 2) {
1020 Console.WriteLine ("Error: wrong number of parameters");
1024 inputFilestream = new StreamReader (parms[1]);
1026 catch (Exception e) {
1027 Console.WriteLine ("Error: Unable to setup input commmands file. " + e.Message);
1032 public void LoadBufferFromFile (string[] parms)
1034 if (parms.Length != 2) {
1035 Console.WriteLine ("Error: wrong number of parameters");
1038 string inFilename = parms[1];
1040 StreamReader sr = new StreamReader (inFilename);
1041 StringBuilder buffer = new StringBuilder ();
1044 while ((NextLine = sr.ReadLine ()) != null) {
1045 buffer.Append (NextLine);
1046 buffer.Append ("\n");
1049 buff = buffer.ToString ();
1051 build = new StringBuilder ();
1054 catch (Exception e) {
1055 Console.WriteLine ("Error: Unable to read file into SQL Buffer. " + e.Message);
1059 public void SaveBufferToFile(string[] parms)
1061 if (parms.Length != 2) {
1062 Console.WriteLine ("Error: wrong number of parameters");
1065 string outFilename = parms[1];
1067 StreamWriter sw = new StreamWriter (outFilename);
1068 sw.WriteLine (buff);
1071 catch (Exception e) {
1072 Console.WriteLine ("Error: Could not save SQL Buffer to file." + e.Message);
1076 public void SetUseParameters (string[] parms)
1078 if (parms.Length != 2) {
1079 Console.WriteLine ("Error: wrong number of parameters");
1082 string parm = parms[1].ToUpper ();
1083 if (parm.Equals ("TRUE"))
1084 UseParameters = true;
1085 else if (parm.Equals ("FALSE"))
1086 UseParameters = false;
1088 Console.WriteLine ("Error: invalid parameter.");
1092 public void SetUseSimpleReader (string[] parms)
1094 if (parms.Length != 2) {
1095 Console.WriteLine ("Error: wrong number of parameters");
1098 string parm = parms[1].ToUpper ();
1099 if (parm.Equals ("TRUE"))
1100 UseSimpleReader = true;
1101 else if (parm.Equals ("FALSE"))
1102 UseSimpleReader = false;
1104 Console.WriteLine ("Error: invalid parameter.");
1107 public void SetupSilentMode (string[] parms)
1109 if (parms.Length != 2) {
1110 Console.WriteLine ("Error: wrong number of parameters");
1113 string parm = parms[1].ToUpper ();
1114 if (parm.Equals ("TRUE"))
1116 else if (parm.Equals ("FALSE"))
1119 Console.WriteLine ("Error: invalid parameter.");
1122 public void SetInternalVariable(string[] parms)
1124 if (parms.Length < 2) {
1125 Console.WriteLine ("Error: wrong number of parameters.");
1128 string parm = parms[1];
1129 StringBuilder ps = new StringBuilder ();
1131 for (int i = 2; i < parms.Length; i++)
1132 ps.Append (parms[i]);
1134 internalVariables[parm] = ps.ToString ();
1137 public void UnSetInternalVariable(string[] parms)
1139 if (parms.Length != 2) {
1140 Console.WriteLine ("Error: wrong number of parameters.");
1143 string parm = parms[1];
1146 internalVariables.Remove (parm);
1147 } catch(Exception e) {
1148 Console.WriteLine ("Error: internal variable does not exist: " + e.Message);
1152 public void ShowInternalVariable(string[] parms)
1154 string internalVariableValue = "";
1156 if (parms.Length != 2) {
1157 Console.WriteLine ("Error: wrong number of parameters.");
1161 string parm = parms[1];
1163 if (GetInternalVariable(parm, out internalVariableValue) == true)
1164 Console.WriteLine ("Internal Variable - Name: " +
1165 parm + " Value: " + internalVariableValue);
1168 public bool GetInternalVariable(string name, out string sValue)
1171 bool valueReturned = false;
1174 if (internalVariables.ContainsKey (name) == true) {
1175 sValue = (string) internalVariables[name];
1176 valueReturned = true;
1179 Console.WriteLine ("Error: internal variable does not exist.");
1182 catch(Exception e) {
1183 Console.WriteLine ("Error: internal variable does not exist: "+ e.Message);
1185 return valueReturned;
1188 public void SetupExternalProvider(string[] parms)
1190 if (parms.Length != 3) {
1191 Console.WriteLine ("Error: Wrong number of parameters.");
1194 provider = "LOADEXTPROVIDER";
1195 providerAssembly = parms[1];
1196 providerConnectionClass = parms[2];
1199 public bool LoadExternalProvider ()
1203 bool success = false;
1205 // For example: for the MySQL provider in Mono.Data.MySql
1206 // \LoadExtProvider Mono.Data.MySql Mono.Data.MySql.MySqlConnection
1207 // \ConnectionString dbname=test
1209 // insert into sometable (tid, tdesc, aint) values ('abc','def',12)
1215 OutputLine ("Loading external provider...");
1217 Assembly ps = Assembly.Load (providerAssembly);
1218 conType = ps.GetType (providerConnectionClass);
1219 conn = (IDbConnection) Activator.CreateInstance (conType);
1222 OutputLine ("External provider loaded.");
1223 UseParameters = false;
1224 } catch(FileNotFoundException f) {
1225 msg = "Error: unable to load the assembly of the provider: " + providerAssembly + " : " + f.Message;
1226 Console.WriteLine(msg);
1228 catch(Exception e) {
1229 msg = "Error: unable to load the assembly of the provider: " + providerAssembly + " : " + e.Message;
1230 Console.WriteLine(msg);
1235 // used for outputting message, but if silent is set,
1237 public void OutputLine (string line)
1239 if (silent == false)
1243 // used for outputting the header columns of a result
1244 public void OutputHeader (string line)
1246 if (showHeader == true)
1250 // OutputData() - used for outputting data
1251 // if an output filename is set, then the data will
1252 // go to a file; otherwise, it will go to the Console.
1253 public void OutputData(string line)
1255 if (outputFilestream == null)
1256 Console.WriteLine (line);
1258 outputFilestream.WriteLine (line);
1261 // HandleCommand - handle SqlSharpCli commands entered
1262 public void HandleCommand (string entry)
1266 parms = entry.Split (new char[1] {' '});
1267 string userCmd = parms[0].ToUpper ();
1271 ChangeProvider (parms);
1273 case "\\CONNECTIONSTRING":
1274 ChangeConnectionString (entry);
1276 case "\\LOADEXTPROVIDER":
1277 SetupExternalProvider (parms);
1286 SetupSilentMode (parms);
1292 // Execute SQL Commands or Queries
1294 Console.WriteLine ("Error: connection is not Open.");
1295 else if (conn.State == ConnectionState.Closed)
1296 Console.WriteLine ("Error: connection is not Open.");
1299 Console.WriteLine ("Error: SQL Buffer is empty.");
1301 buff = build.ToString ();
1307 case "\\EXENONQUERY":
1309 Console.WriteLine ("Error: connection is not Open.");
1310 else if (conn.State == ConnectionState.Closed)
1311 Console.WriteLine ("Error: connection is not Open.");
1314 Console.WriteLine ("Error: SQL Buffer is empty.");
1316 buff = build.ToString ();
1317 ExecuteSqlNonQuery (buff);
1324 Console.WriteLine ("Error: connection is not Open.");
1325 else if (conn.State == ConnectionState.Closed)
1326 Console.WriteLine ("Error: connection is not Open.");
1329 Console.WriteLine ("Error: SQL Buffer is empty.");
1331 buff = build.ToString ();
1332 ExecuteSqlScalar (buff);
1338 // \exexml OUTPUT_FILENAME
1340 Console.WriteLine ("Error: connection is not Open.");
1341 else if (conn.State == ConnectionState.Closed)
1342 Console.WriteLine ("Error: connection is not Open.");
1345 Console.WriteLine ("Error: SQL Buffer is empty.");
1347 buff = build.ToString ();
1348 ExecuteSqlXml (buff, parms);
1354 SetupInputCommandsFile (parms);
1357 SetupOutputResultsFile (parms);
1360 // Load file into SQL buffer: \load FILENAME
1361 LoadBufferFromFile (parms);
1364 // Save SQL buffer to file: \save FILENAME
1365 SaveBufferToFile (parms);
1373 // show the defaults for provider and connection strings
1383 // reset (clear) the query buffer
1387 // sets internal variable
1389 SetInternalVariable (parms);
1392 // deletes internal variable
1394 UnSetInternalVariable (parms);
1397 ShowInternalVariable (parms);
1401 Console.WriteLine ("SQL Buffer is empty.");
1403 Console.WriteLine ("SQL Bufer:\n" + buff);
1405 case "\\USEPARAMETERS":
1406 SetUseParameters (parms);
1408 case "\\USESIMPLEREADER":
1409 SetUseSimpleReader (parms);
1413 Console.WriteLine ("Error: Unknown user command.");
1418 public void DealWithArgs(string[] args)
1420 for (int a = 0; a < args.Length; a++) {
1421 if (args[a].Substring (0,1).Equals ("-")) {
1422 string arg = args [a].ToUpper ().Substring (1, args [a].Length - 1);
1428 if (a + 1 >= args.Length)
1429 Console.WriteLine ("Error: Missing FILENAME for -f switch");
1431 inputFilename = args [a + 1];
1432 inputFilestream = new StreamReader (inputFilename);
1436 if (a + 1 >= args.Length)
1437 Console.WriteLine ("Error: Missing FILENAME for -o switch");
1439 outputFilename = args [a + 1];
1440 outputFilestream = new StreamWriter (outputFilename);
1444 Console.WriteLine ("Error: Unknow switch: " + args [a]);
1451 public string ReadSqlSharpCommand()
1455 if (inputFilestream == null) {
1456 if (silent == false)
1457 Console.Error.Write ("\nSQL# ");
1458 entry = Console.ReadLine ();
1462 entry = inputFilestream.ReadLine ();
1463 if (entry == null) {
1464 OutputLine ("Executing SQL# Commands from file done.");
1467 catch (Exception e) {
1468 Console.WriteLine ("Error: Reading command from file: " + e.Message);
1470 if (silent == false)
1471 Console.Error.Write ("\nSQL# ");
1472 entry = Console.ReadLine ();
1477 public void Run (string[] args)
1479 DealWithArgs (args);
1484 if (silent == false) {
1485 Console.WriteLine ("Welcome to SQL#. The interactive SQL command-line client ");
1486 Console.WriteLine ("for Mono.Data. See http://www.mono-project.com/ for more details.\n");
1492 while (entry.ToUpper ().Equals ("\\Q") == false &&
1493 entry.ToUpper ().Equals ("\\QUIT") == false) {
1495 while ((entry = ReadSqlSharpCommand ()) == "") {}
1498 if (entry.Substring(0,1).Equals ("\\")) {
1499 HandleCommand (entry);
1501 else if (entry.IndexOf(";") >= 0) {
1502 // most likely the end of SQL Command or Query found
1505 Console.WriteLine ("Error: connection is not Open.");
1506 else if (conn.State == ConnectionState.Closed)
1507 Console.WriteLine ("Error: connection is not Open.");
1509 if (build == null) {
1510 build = new StringBuilder ();
1512 build.Append (entry);
1513 //build.Append ("\n");
1514 buff = build.ToString ();
1520 // most likely a part of a SQL Command or Query found
1521 // append this part of the SQL
1522 if (build == null) {
1523 build = new StringBuilder ();
1525 build.Append (entry + "\n");
1526 buff = build.ToString ();
1530 if (outputFilestream != null)
1531 outputFilestream.Close ();
1535 public enum BindVariableCharacter {
1536 Colon, // ':' - named parameter - :name
1537 At, // '@' - named parameter - @name
1538 QuestionMark, // '?' - positioned parameter - ?
1539 SquareBrackets // '[]' - delimited named parameter - [name]
1542 public class ParametersBuilder
1544 private BindVariableCharacter bindCharSetting;
1545 private char bindChar;
1546 private IDataParameterCollection parms;
1548 private IDbCommand cmd;
1550 private void SetBindCharacter ()
1552 switch(bindCharSetting) {
1553 case BindVariableCharacter.Colon:
1556 case BindVariableCharacter.At:
1559 case BindVariableCharacter.SquareBrackets:
1562 case BindVariableCharacter.QuestionMark:
1568 public ParametersBuilder (IDbCommand command, BindVariableCharacter bindVarChar)
1571 sql = cmd.CommandText;
1572 parms = cmd.Parameters;
1573 bindCharSetting = bindVarChar;
1577 public char ParameterMarkerCharacter {
1583 public int ParseParameters ()
1587 IDataParameterCollection parms = cmd.Parameters;
1589 char[] chars = sql.ToCharArray ();
1590 bool bStringConstFound = false;
1592 for (int i = 0; i < chars.Length; i++) {
1593 if (chars[i] == '\'') {
1594 if (bStringConstFound == true)
1595 bStringConstFound = false;
1597 bStringConstFound = true;
1599 else if (chars[i] == bindChar &&
1600 bStringConstFound == false) {
1601 if (bindChar != '?') {
1602 StringBuilder parm = new StringBuilder ();
1604 if (bindChar.Equals ('[')) {
1605 bool endingBracketFound = false;
1606 while (i <= chars.Length) {
1608 if (i == chars.Length)
1609 ch = ' '; // a space
1613 if (Char.IsLetterOrDigit (ch) || ch == ' ') {
1616 else if (ch == ']') {
1617 endingBracketFound = true;
1618 string p = parm.ToString ();
1623 else throw new Exception("SQL Parser Error: Invalid character in parameter name");
1627 if (endingBracketFound == false)
1628 throw new Exception("SQL Parser Error: Ending bracket not found for parameter");
1631 while (i <= chars.Length) {
1633 if (i == chars.Length)
1634 ch = ' '; // a space
1638 if (Char.IsLetterOrDigit(ch)) {
1643 string p = parm.ToString ();
1654 // placeholder paramaeter for ?
1655 string p = numParms.ToString ();
1664 public void AddParameter (string p)
1666 Console.WriteLine ("Add Parameter: " + p);
1667 if (parms.Contains (p) == false) {
1668 IDataParameter prm = cmd.CreateParameter ();
1669 prm.ParameterName = p;
1670 prm.Direction = ParameterDirection.Input;
1671 prm.DbType = DbType.String; // default
1672 prm.Value = ""; // default
1673 cmd.Parameters.Add(prm);
1678 public class SqlSharpDriver
1680 public static void Main (string[] args)
1682 SqlSharpCli sqlCommandLineEngine = new SqlSharpCli ();
1683 sqlCommandLineEngine.Run (args);