mono/mcs/tools/sqlsharp/SqlSharpCli.cs

//
// SqlSharpCli.cs - main driver for Mono SQL Query Command Line Interface
//                  found in mcs/tools/SqlSharp
//
//                  This program is included in Mono and is licenced under the GPL.
//                  http://www.fsf.org/licenses/gpl.html  
//
//                  For more information about Mono, 
//                  visit http://www.mono-project.com/
//
// To build SqlSharpCli.cs
// $ mcs /out:sqlsharp.exe SqlSharpCli.cs /r:System.Data.dll
//
// To run with mono:
// $ mono sqlsharp.exe
//
// To run with mint:
// $ mint sqlsharp.exe
//
// To run batch commands and get the output, do something like:
// $ cat commands_example.txt | mono sqlsharp.exe -s > results.txt
//
// Author:
//    Daniel Morgan <[email protected]>
//
// (C)Copyright 2002-2004 Daniel Morgan
//

using System;
using System.Collections;
using System.Data;
using System.Data.Common;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.IO;
using System.Reflection;
using System.Runtime.Remoting;
using System.Text;

namespace Mono.Data.SqlSharp {

	public enum FileFormat {
		Html,
		Xml,
		CommaSeparatedValues,
		TabSeparated,
		Normal
	}

	// SQL Sharp - Command Line Interface
	public class SqlSharpCli 
	{
		// provider supports
		private bool UseParameters = true;
		private bool UseSimpleReader = false;
	
		private IDbConnection conn = null;
                		
		private string provider = ""; // name of internal provider
		// {OleDb,SqlClient,MySql,Odbc,Oracle,
		// PostgreSql,SqlLite,Sybase,Tds} however, it
		// can be set to LOADEXTPROVIDER to load an external provider
		private string providerAssembly = "";
		// filename of assembly
		// for example: "Mono.Data.MySql"
		private string providerConnectionClass = "";
		// Connection class
		// in the provider assembly that implements the IDbConnection 
		// interface.  for example: "Mono.Data.MySql.MySqlConnection"
		Type conType;
		private StringBuilder build = null; // SQL string to build
		private string buff = ""; // SQL string buffer

		private string connectionString = "";

		private string inputFilename = "";
		private string outputFilename = "";
		private StreamReader inputFilestream = null;
		private StreamWriter outputFilestream = null;

		private FileFormat outputFileFormat = FileFormat.Html;

		private bool silent = false;
		private bool showHeader = true;

		private Hashtable internalVariables = new Hashtable();
				
		// DisplayResult - used to Read() display a result set
		//                   called by DisplayData()

		public bool DisplayResult (IDataReader reader, DataTable schemaTable)
		{
			StringBuilder column = null;
			StringBuilder line = null;
			StringBuilder hdrUnderline = null;
			string outData = "";
			int hdrLen = 0;
			
			int spacing = 0;
			int columnSize = 0;
			int c;
			
			char spacingChar = ' '; // a space
			char underlineChar = '='; // an equal sign

			string dataType; // .NET Type
			Type theType; 
			DataRow row; // schema row

			line = new StringBuilder ();
			hdrUnderline = new StringBuilder ();
			
			OutputLine ("");
			
			for (c = 0; c < reader.FieldCount; c++) {
				try {			
					DataRow schemaRow = schemaTable.Rows [c];
					string columnHeader = reader.GetName (c);
					if (columnHeader.Equals (""))
						columnHeader = "column";
					if (columnHeader.Length > 32)
						columnHeader = columnHeader.Substring (0,32);
					
					// spacing
					columnSize = (int) schemaRow ["ColumnSize"];
					theType = reader.GetFieldType (c);
					dataType = theType.ToString ();

					switch (dataType) {
					case "System.DateTime":
						columnSize = 25;
						break;
					case "System.Boolean":
						columnSize = 5;
						break;
					case "System.Byte":
						columnSize = 1;
						break;
					case "System.Single":
						columnSize = 12;
						break;
					case "System.Double":
						columnSize = 21;
						break;
					case "System.Int16":
					case "System.Unt16":
						columnSize = 5;
						break;
					case "System.Int32":
					case "System.UInt32":
						columnSize = 10;
						break;
					case "System.Int64":
						columnSize = 19;
						break;
					case "System.UInt64":
						columnSize = 20;
						break;
					case "System.Decimal":
						columnSize = 29;
						break;
					}

					if (columnSize < 0)
						columnSize = 32;
					if (columnSize > 32)
						columnSize = 32;

					hdrLen = columnHeader.Length;
					if (hdrLen < 0)
						hdrLen = 0;
					if (hdrLen > 32)
						hdrLen = 32;

					hdrLen = System.Math.Max (hdrLen, columnSize);

					line.Append (columnHeader);
					if (columnHeader.Length < hdrLen) {
						spacing = hdrLen - columnHeader.Length;
						line.Append (spacingChar, spacing);
					}
					hdrUnderline.Append (underlineChar, hdrLen);

					line.Append (" ");
					hdrUnderline.Append (" ");
				}
				catch (Exception e) {
					OutputLine ("Error: Unable to display header: " + e.Message);
					return false;
				}
			}
			OutputHeader (line.ToString ());
			line = null;
			
			OutputHeader (hdrUnderline.ToString ());
			OutputHeader ("");
			hdrUnderline = null;		
								
			int numRows = 0;

			// column data
			try {
				while (reader.Read ()) {
					numRows++;
				
					line = new StringBuilder ();
					for(c = 0; c < reader.FieldCount; c++) {
						int dataLen = 0;
						string dataValue = "";
						column = new StringBuilder ();
						outData = "";
					
						row = schemaTable.Rows [c];
						string colhdr = (string) reader.GetName (c);
						if (colhdr.Equals (""))
							colhdr = "column";
						if (colhdr.Length > 32)
							colhdr = colhdr.Substring (0, 32);

						columnSize = (int) row ["ColumnSize"];
						theType = reader.GetFieldType (c);
						dataType = theType.ToString ();

						switch (dataType) {
						case "System.DateTime":
							columnSize = 25;
							break;
						case "System.Boolean":
							columnSize = 5;
							break;
						case "System.Byte":
							columnSize = 1;
							break;
						case "System.Single":
							columnSize = 12;
							break;
						case "System.Double":
							columnSize = 21;
							break;
						case "System.Int16":
						case "System.Unt16":
							columnSize = 5;
							break;
						case "System.Int32":
						case "System.UInt32":
							columnSize = 10;
							break;
						case "System.Int64":
							columnSize = 19;
							break;
						case "System.UInt64":
							columnSize = 20;
							break;
						case "System.Decimal":
							columnSize = 29;
							break;
						}

						if (columnSize < 0)
							columnSize = 32;
						if (columnSize > 32)
							columnSize = 32;

						hdrLen = colhdr.Length;
						if (hdrLen < 0)
							hdrLen = 0;
						if (hdrLen > 32)
							hdrLen = 32;

						columnSize = System.Math.Max (colhdr.Length, columnSize);

						dataValue = "";
						dataLen = 0;

						if (!reader.IsDBNull (c)) {
							object o = reader.GetValue (c);
							if (o.GetType ().ToString ().Equals ("System.Byte[]"))
								dataValue = GetHexString ( (byte[]) o);
							else
								dataValue = o.ToString ();

							dataLen = dataValue.Length;
							
							if (dataLen <= 0) {
								dataValue = "";
								dataLen = 0;
							}
							if (dataLen > 32) {
								dataValue = dataValue.Substring (0, 32);
								dataLen = 32;
							}

							if (dataValue.Equals(""))
								dataLen = 0;
						}
						columnSize = System.Math.Max (columnSize, dataLen);
					
						if (dataLen < columnSize) {
							switch (dataType) {
							case "System.Byte":
							case "System.SByte":
							case "System.Int16":
							case "System.UInt16":
							case "System.Int32":
							case "System.UInt32":
							case "System.Int64":
							case "System.UInt64":
							case "System.Single":
							case "System.Double":
							case "System.Decimal":
								outData = dataValue.PadLeft (columnSize);
								break;
							default:
								outData = dataValue.PadRight (columnSize);
								break;
							}
						}
						else
							outData = dataValue;

						line.Append (outData);
						line.Append (" ");
					}
					OutputData (line.ToString ());
				}
			}
			catch (Exception rr) {
				OutputLine ("Error: Unable to read next row: " + rr.Message);
				return false;
			}
		
			OutputLine ("\nRows retrieved: " + numRows.ToString ());

			return true; // return true - success
		}

		public static string GetHexString (byte[] bytes) 
		{ 			
			string bvalue = "";
			
			if (bytes.Length > 0) {
				StringBuilder sb = new StringBuilder ();

				for (int z = 0; z < bytes.Length; z++)
					sb.Append (bytes [z].ToString ("x"));

				bvalue = "0x" + sb.ToString ();
			}
	
			return bvalue;
		}
		
		public void OutputDataToHtmlFile (IDataReader rdr, DataTable dt) 
		{        		
			StringBuilder strHtml = new StringBuilder ();

			strHtml.Append ("<html> \n <head> <title>");
			strHtml.Append ("Results");
			strHtml.Append ("</title> </head>");
			strHtml.Append ("<body>");
			strHtml.Append ("<h1> Results </h1>");
			strHtml.Append ("<table border=1>");
		
			outputFilestream.WriteLine (strHtml.ToString ());

			strHtml = new StringBuilder ();

			strHtml.Append ("<tr>");
			foreach (DataRow schemaRow in dt.Rows) {
				strHtml.Append ("<td> <b>");
				object dataObj = schemaRow ["ColumnName"];
				string sColumnName = dataObj.ToString ();
				strHtml.Append (sColumnName);
				strHtml.Append ("</b> </td>");
			}
			strHtml.Append ("</tr>");
			outputFilestream.WriteLine (strHtml.ToString ());
			strHtml = null;

			int col = 0;
			string dataValue = "";
			
			while (rdr.Read ()) {
				strHtml = new StringBuilder ();

				strHtml.Append ("<tr>");
				for (col = 0; col < rdr.FieldCount; col++) {
						
					// column data
					if (rdr.IsDBNull (col) == true)
						dataValue = "NULL";
					else {
						object obj = rdr.GetValue (col);
						dataValue = obj.ToString ();
					}
					strHtml.Append ("<td>");
					strHtml.Append (dataValue);
					strHtml.Append ("</td>");
				}
				strHtml.Append ("\t\t</tr>");
				outputFilestream.WriteLine (strHtml.ToString ());
				strHtml = null;
			}
			outputFilestream.WriteLine (" </table> </body> \n </html>");
			strHtml = null;
		}
		
		// DisplayData - used to display any Result Sets
		//                 from execution of SQL SELECT Query or Queries
		//                 called by DisplayData. 
		//                 ExecuteSql() only calls this function
		//                 for a Query, it does not get
		//                 for a Command.
		public void DisplayData (IDataReader reader) 
		{
			DataTable schemaTable = null;
			int ResultSet = 0;

			do {
				// by Default, SqlDataReader has the 
				// first Result set if any

				ResultSet++;
				OutputLine ("Display the result set " + ResultSet);
				
				schemaTable = reader.GetSchemaTable ();
				
				if (reader.FieldCount > 0) {
					// SQL Query (SELECT)
					// RecordsAffected -1 and DataTable has a reference
					OutputQueryResult (reader, schemaTable);
				}
				else if (reader.RecordsAffected >= 0) {
					// SQL Command (INSERT, UPDATE, or DELETE)
					// RecordsAffected >= 0
					Console.WriteLine ("SQL Command Records Affected: " + reader.RecordsAffected);
				}
				else {
					// SQL Command (not INSERT, UPDATE, nor DELETE)
					// RecordsAffected -1 and DataTable has a null reference
					Console.WriteLine ("SQL Command Executed.");
				}
				
				// get next result set (if anymore is left)
			} while (reader.NextResult ());
		}

		// display the result in a simple way
		// new ADO.NET providers may have not certain
		// things implemented yet, such as, TableSchema
		// support
		public void DisplayDataSimple (IDataReader reader) 
		{				
			int row = 0;
			Console.WriteLine ("Reading Data using simple reader...");
			while (reader.Read ()){
				row++;
				Console.WriteLine ("Row: " + row);
				for (int col = 0; col < reader.FieldCount; col++) {
					int co = col + 1;
					Console.WriteLine ("  Field: " + co);
					
					string dname = (string) reader.GetName (col);
					if (dname == null)
						dname = "?column?";
					if (dname.Equals (String.Empty))
						dname = "?column?";
					Console.WriteLine ("      Name: " + dname);

					string dvalue = "";
					if (reader.IsDBNull (col))
						dvalue = "(null)";
					else
						dvalue = reader.GetValue (col).ToString ();
					Console.WriteLine ("      Value: " + dvalue);
				}
			}
			Console.WriteLine ("\n" + row + " ROWS RETRIEVED\n");
		}

		public void OutputQueryResult (IDataReader dreader, DataTable dtable) 
		{
			if (outputFilestream == null) {
				DisplayResult (dreader, dtable);
			}
			else {
				switch (outputFileFormat) {
				case FileFormat.Normal:
					DisplayResult (dreader, dtable);
					break;
				case FileFormat.Html:
					OutputDataToHtmlFile (dreader, dtable);
					break;
				default:
					Console.WriteLine ("Error: Output data file format not supported.");
					break;
				}
			}
		}

		public void BuildParameters (IDbCommand cmd) 
		{
			if (UseParameters == true) {

				ParametersBuilder parmsBuilder = new ParametersBuilder (cmd, BindVariableCharacter.Colon);
			
				Console.WriteLine ("Get Parameters (if any)...");
				parmsBuilder.ParseParameters ();
				IList parms = (IList) cmd.Parameters;
		
				Console.WriteLine ("Print each parm...");
				for (int p = 0; p < parms.Count; p++) {
					string theParmName;

					IDataParameter prm = (IDataParameter) parms[p];
					theParmName = prm.ParameterName;
				
					string inValue = "";
					bool found;
					if (parmsBuilder.ParameterMarkerCharacter == '?') {
						Console.Write ("Enter Parameter " + 
							(p + 1).ToString() +
							": ");
						inValue = Console.ReadLine();
						prm.Value = inValue;
					}
					else {
						found = GetInternalVariable (theParmName, out inValue);
						if (found == true) {
							prm.Value = inValue;
						}
						else {
							Console.Write ("Enter Parameter " + (p + 1).ToString () +
								": " + theParmName + ": ");
							inValue = Console.ReadLine ();
							prm.Value = inValue;
						}
					}
				}
				parmsBuilder = null;
			}
		}

		// ExecuteSql - Execute the SQL Command(s) and/or Query(ies)
		public void ExecuteSql (string sql) 
		{
			string msg = "";

			IDbCommand cmd = null;
			IDataReader reader = null;

			cmd = conn.CreateCommand();

			// set command properties
			cmd.CommandType = CommandType.Text;
			cmd.CommandText = sql;
			cmd.Connection = conn;

			BuildParameters (cmd);

			try {
				reader = cmd.ExecuteReader ();

				if (UseSimpleReader == false)
					DisplayData (reader);
				else
					DisplayDataSimple (reader);

				reader.Close ();
				reader = null;
			}
			catch (Exception e) {
				msg = "Error: " + e.Message;
				Console.WriteLine (msg);
				reader = null;
			}
			finally {
				cmd = null;
			}
		}

		// ExecuteSql - Execute the SQL Commands (no SELECTs)
		public void ExecuteSqlNonQuery (string sql) 
		{
			string msg = "";

			IDbCommand cmd = null;
			int rowsAffected = -1;
			
			cmd = conn.CreateCommand();

			// set command properties
			cmd.CommandType = CommandType.Text;
			cmd.CommandText = sql;
			cmd.Connection = conn;

			BuildParameters(cmd);

			try {
				rowsAffected = cmd.ExecuteNonQuery ();
				cmd = null;
				Console.WriteLine ("Rows affected: " + rowsAffected);
			}
			catch(Exception e) {
				msg = "Error: " + e.Message;
				Console.WriteLine (msg);
			}
			finally {
				cmd = null;
			}
		}

		public void ExecuteSqlScalar(string sql) 
		{
			string msg = "";

			IDbCommand cmd = null;
			string retrievedValue = "";
			
			cmd = conn.CreateCommand ();

			// set command properties
			cmd.CommandType = CommandType.Text;
			cmd.CommandText = sql;
			cmd.Connection = conn;

			BuildParameters(cmd);

			try {
				retrievedValue = (string) cmd.ExecuteScalar ().ToString ();
				Console.WriteLine ("Retrieved value: " + retrievedValue);
			}
			catch(Exception e) {
				msg = "Error: " + e.Message;
				Console.WriteLine (msg);
			}
			finally {
				cmd = null;
			}
		}

		public void ExecuteSqlXml(string sql, string[] parms) 
		{
			string filename = "";

			if (parms.Length != 2) {
				Console.WriteLine ("Error: wrong number of parameters");
				return;
			}
			try {
				filename = parms [1];
			}
			catch (Exception e) {
				Console.WriteLine ("Error: Unable to setup output results file. " + e.Message);
				return;
			}

			try {	
				IDbCommand cmd = null;
				
				cmd = conn.CreateCommand ();

				// set command properties
				cmd.CommandType = CommandType.Text;
				cmd.CommandText = sql;
				cmd.Connection = conn;

				BuildParameters (cmd);
				DataSet dataSet = new DataSet ();
				DbDataAdapter adapter = CreateNewDataAdapter (cmd, conn);
				adapter.Fill (dataSet);	
				dataSet.WriteXml (filename);
				OutputLine ("Data written to xml file: " + filename);
			}
			catch (Exception exexml) {
				Console.WriteLine ("Error: Execute SQL XML Failure: " + exexml);
			}
		}

		public DbDataAdapter CreateNewDataAdapter (IDbCommand command, IDbConnection connection) 
		{
			DbDataAdapter adapter = null;

			switch(provider) {
			case "OLEDB":
				adapter = (DbDataAdapter) new OleDbDataAdapter ();
				break;
			case "SQLCLIENT":
				adapter = (DbDataAdapter) new SqlDataAdapter ();
				break;
			case "LOADEXTPROVIDER":
				adapter = CreateExternalDataAdapter (command, connection);
				if (adapter == null)
					return null;
				break;
			default:
				Console.WriteLine("Error: Data Adapter not found in provider.");
				return null;
			}
			IDbDataAdapter dbAdapter = (IDbDataAdapter) adapter;
			dbAdapter.SelectCommand = command;

			return adapter;
		}

		public DbDataAdapter CreateExternalDataAdapter (IDbCommand command, IDbConnection connection) 
		{
			DbDataAdapter adapter = null;

			Assembly ass = Assembly.Load (providerAssembly); 
			Type [] types = ass.GetTypes (); 
			foreach (Type t in types) { 
				if (t.IsSubclassOf (typeof (System.Data.Common.DbDataAdapter))) {
					if (t.Namespace.Equals (conType.Namespace))
						adapter = (DbDataAdapter) Activator.CreateInstance (t);
				}
			}
                        
			return adapter;
		}

		// like ShowHelp - but only show at the beginning
		// only the most important commands are shown
		// like help and quit
		public void StartupHelp () 
		{
			OutputLine (@"Type:  \Q to quit");
			OutputLine (@"       \ConnectionString to set the ConnectionString");
			OutputLine (@"       \Provider to set the Provider:");
			OutputLine (@"                 {OleDb,SqlClient,MySql,Odbc,DB2,");
			OutputLine (@"                  Oracle,PostgreSql,Sqlite,Sybase,Tds)");
			OutputLine (@"       \Open to open the connection");
			OutputLine (@"       \Close to close the connection");
			OutputLine (@"       \e to execute SQL query (SELECT)");
			OutputLine (@"       \h to show help (all commands).");
			OutputLine (@"       \defaults to show default variables.");
			OutputLine ("");
		}
		
		// ShowHelp - show the help - command a user can enter
		public void ShowHelp () 
		{
			Console.WriteLine ("");
			Console.WriteLine (@"Type:  \Q to quit");
			Console.WriteLine (@"       \ConnectionString to set the ConnectionString");
			Console.WriteLine (@"       \Provider to set the Provider:");
			Console.WriteLine (@"                 {OleDb,SqlClient,MySql,Odbc,MSODBC,");
			Console.WriteLine (@"                  Oracle,PostgreSql,Sqlite,Sybase,Tds}");
			Console.WriteLine (@"       \Open to open the connection");
			Console.WriteLine (@"       \Close to close the connection");
			Console.WriteLine (@"       \e to execute SQL query (SELECT)");
			Console.WriteLine (@"       \exenonquery to execute an SQL non query (not a SELECT).");
			Console.WriteLine (@"       \exescalar to execute SQL to get a single row and single column.");
			Console.WriteLine (@"       \exexml FILENAME to execute SQL and save output to XML file.");
			Console.WriteLine (@"       \f FILENAME to read a batch of SQL# commands from file.");
			Console.WriteLine (@"       \o FILENAME to write result of commands executed to file.");
			Console.WriteLine (@"       \load FILENAME to load from file SQL commands into SQL buffer.");
			Console.WriteLine (@"       \save FILENAME to save SQL commands from SQL buffer to file.");
			Console.WriteLine (@"       \h to show help (all commands).");
			Console.WriteLine (@"       \defaults to show default variables, such as,");
			Console.WriteLine (@"            Provider and ConnectionString.");
			Console.WriteLine (@"       \s {TRUE, FALSE} to silent messages.");
			Console.WriteLine (@"       \r to reset or clear the query buffer.");
			WaitForEnterKey ();
			Console.WriteLine (@"       \set NAME VALUE to set an internal variable.");
			Console.WriteLine (@"       \unset NAME to remove an internal variable.");
			Console.WriteLine (@"       \variable NAME to display the value of an internal variable.");
			Console.WriteLine (@"       \loadextprovider ASSEMBLY CLASS to load the provider"); 
			Console.WriteLine (@"            use the complete name of its assembly and");
			Console.WriteLine (@"            its Connection class.");
			Console.WriteLine (@"       \print - show what's in the SQL buffer now.");
			Console.WriteLine (@"       \UseParameters (TRUE,FALSE) to use parameters when executing SQL.");
			Console.WriteLine (@"       \UseSimpleReader (TRUE,FALSE) to use simple reader when displaying results.");
			Console.WriteLine ();
		}

		public bool WaitForEnterKey () 
		{
                        Console.Write("Waiting... Press Enter key to continue. ");
			string entry = Console.ReadLine();
			if (entry.ToUpper() == "Q")
				return false;
			return true;
		}

		// ShowDefaults - show defaults for connection variables
		public void ShowDefaults() 
		{
			Console.WriteLine ();
			if (provider.Equals (""))
				Console.WriteLine ("Provider is not set.");
			else {
				Console.WriteLine ("The default Provider is " + provider);
				if (provider.Equals ("LOADEXTPROVIDER")) {
					Console.WriteLine ("  Assembly: " + providerAssembly);
					Console.WriteLine ("  Connection Class: " + providerConnectionClass);
				}
			}
			Console.WriteLine ();
			if (connectionString.Equals (""))
				Console.WriteLine ("ConnectionString is not set.");
			else {
				Console.WriteLine ("The default ConnectionString is: ");
				Console.WriteLine ("    \"" + connectionString + "\"");
				Console.WriteLine ();
			}
		}

		// OpenDataSource - open connection to the data source
		public void OpenDataSource () 
		{
			string msg = "";
			
			OutputLine ("Opening connection...");

			try {
				switch (provider) {
				case "OLEDB":
					conn = new OleDbConnection ();
					break;
				case "SQLCLIENT":
					conn = new SqlConnection ();
					break;
				case "LOADEXTPROVIDER":
					if (LoadExternalProvider () == false)
						return;
					break;
				default:
					Console.WriteLine ("Error: Bad argument or provider not supported.");
					return;
				}
			} catch (Exception e) {
				msg = "Error: Unable to create Connection object because: " + e.Message;
				Console.WriteLine (msg);
				return;
			}

			conn.ConnectionString = connectionString;
			
			try {
				conn.Open ();
				if (conn.State == ConnectionState.Open)
					OutputLine ("Open was successfull.");
			} catch (Exception e) {
				msg = "Exception Caught Opening. " + e.Message;
				Console.WriteLine (msg);
				conn = null;
			}
		}

		// CloseDataSource - close the connection to the data source
		public void CloseDataSource () {
			string msg = "";
			
			if (conn != null) {
				OutputLine ("Attempt to Close...");
				try {
					conn.Close ();
					OutputLine ("Close was successfull.");
				} catch(Exception e) {
					msg = "Exeception Caught Closing. " + e.Message;
					Console.WriteLine (msg);
				}
				conn = null;
			}
		}

		// ChangeProvider - change the provider string variable
		public void ChangeProvider (string[] parms) {

			string[] extp;

			if (parms.Length == 2) {
				string parm = parms [1].ToUpper ();
				switch (parm) {
				case "ORACLE":
					extp = new string[3] {
								     "\\loadextprovider",
								     @"System.Data.OracleClient, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089",
								     "System.Data.OracleClient.OracleConnection"};
					SetupExternalProvider (extp);
					UseParameters = false;
					UseSimpleReader = false;
					break;
				case "TDS":
					extp = new string[3] {
								     "\\loadextprovider",
								     @"Mono.Data.TdsClient, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=0738eb9f132ed756",
								     "Mono.Data.TdsClient.TdsConnection"};
					SetupExternalProvider (extp);
					UseParameters = false;
					UseSimpleReader = false;
					break;
				case "SYBASE":
					extp = new string[3] {
								     "\\loadextprovider",
								     @"Mono.Data.SybaseClient, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=0738eb9f132ed756",
								     "Mono.Data.SybaseClient.SybaseConnection"};
					SetupExternalProvider (extp);
					UseParameters = false;
					UseSimpleReader = false;
					break;
				case "MYSQL":
				case "MYSQLNET":
					extp = new string[3] {
								     "\\loadextprovider",
								     @"ByteFX.Data, Version=0.7.6.1, Culture=neutral, PublicKeyToken=0738eb9f132ed756",
								     "ByteFX.Data.MySqlClient.MySqlConnection"};
					SetupExternalProvider (extp);
					UseParameters = false;
					UseSimpleReader = false;
					break;
				case "SQLITE":
					extp = new string[3] {
								     "\\loadextprovider",
								     @"Mono.Data.SqliteClient, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=0738eb9f132ed756",
								     "Mono.Data.SqliteClient.SqliteConnection"};
					SetupExternalProvider (extp);
					UseParameters = false;
					UseSimpleReader = true;
					break;
				case "SQLCLIENT":
					UseParameters = false;
					UseSimpleReader = false;
					provider = parm;
					break;
				case "ODBC": // for MS NET 1.1 and above
					extp = new string[3] {
								     "\\loadextprovider",
								     @"System.Data, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089",
								     "System.Data.Odbc.OdbcConnection"};
					SetupExternalProvider (extp);
					UseParameters = false;
					UseSimpleReader = false;
					break;
				case "MSODBC": // for MS NET 1.0
					extp = new string[3] {
								     "\\loadextprovider",
								     @"Microsoft.Data.Odbc, Culture=neutral, PublicKeyToken=b77a5c561934e089, Version=1.0.3300.0",
								     "Microsoft.Data.Odbc.OdbcConnection"};
					SetupExternalProvider (extp);
					UseParameters = false;
					UseSimpleReader = false;
					break;
				case "OLEDB":
					UseParameters = false;
					UseSimpleReader = true;
					provider = parm;
					break;
				case "FIREBIRD":
					extp = new string[3] {
								     "\\loadextprovider",
								     @"FirebirdSql.Data.Firebird, Version=1.6.3.0, Culture=neutral, PublicKeyToken=e1b4f92304d7b12f",
								     "FirebirdSql.Data.Firebird.FbConnection"};
					SetupExternalProvider (extp);
					UseParameters = false;
					UseSimpleReader = false;
					break;
				case "POSTGRESQL":
				case "NPGSQL":
					extp = new string[3] {
								     "\\loadextprovider",
								     @"Npgsql, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7",
								     "Npgsql.NpgsqlConnection"};
					SetupExternalProvider (extp);
					UseParameters = false;
					UseSimpleReader = false;
					break;
				default:
					Console.WriteLine ("Error: " + "Bad argument or Provider not supported.");
					break;
				}
				OutputLine ("The default Provider is " + provider);
				if (provider.Equals ("LOADEXTPROVIDER")) {
					OutputLine ("          Assembly: " + 
						providerAssembly);
					OutputLine ("  Connection Class: " + 
						providerConnectionClass);
				}
			}
			else
				Console.WriteLine ("Error: provider only has one parameter.");
		}

		// ChangeConnectionString - change the connection string variable
		public void ChangeConnectionString (string entry) 
		{		
			if (entry.Length > 18)
				connectionString = entry.Substring (18, entry.Length - 18);
			else
				connectionString = "";
		}

		public void SetupOutputResultsFile (string[] parms) {
			if (parms.Length != 2) {
				Console.WriteLine ("Error: wrong number of parameters");
				return;
			}
			try {
				outputFilestream = new StreamWriter (parms[1]);
			}
			catch (Exception e) {
				Console.WriteLine ("Error: Unable to setup output results file. " + e.Message);
				return;
			}
		}

		public void SetupInputCommandsFile (string[] parms) 
		{
			if (parms.Length != 2) {
				Console.WriteLine ("Error: wrong number of parameters");
				return;
			}
			try {
				inputFilestream = new StreamReader (parms[1]);
			}
			catch (Exception e) {
				Console.WriteLine ("Error: Unable to setup input commmands file. " + e.Message);
				return;
			}	
		}

		public void LoadBufferFromFile (string[] parms) 
		{
			if (parms.Length != 2) {
				Console.WriteLine ("Error: wrong number of parameters");
				return;
			}
			string inFilename = parms[1];
			try {
				StreamReader sr = new StreamReader (inFilename);
				StringBuilder buffer = new StringBuilder ();
				string NextLine;
			
				while ((NextLine = sr.ReadLine ()) != null) {
					buffer.Append (NextLine);
					buffer.Append ("\n");
				}
				sr.Close ();
				buff = buffer.ToString ();
				build = null;
				build = new StringBuilder ();
				build.Append(buff);
			}
			catch (Exception e) {
				Console.WriteLine ("Error: Unable to read file into SQL Buffer. " + e.Message);
			}
		}

		public void SaveBufferToFile(string[] parms) 
		{
			if (parms.Length != 2) {
				Console.WriteLine ("Error: wrong number of parameters");
				return;
			}
			string outFilename = parms[1];
			try {
				StreamWriter sw = new StreamWriter (outFilename);
				sw.WriteLine (buff);
				sw.Close ();
			}
			catch (Exception e) {
				Console.WriteLine ("Error: Could not save SQL Buffer to file." + e.Message);
			}
		}

		public void SetUseParameters (string[] parms) 
		{
			if (parms.Length != 2) {
				Console.WriteLine ("Error: wrong number of parameters");
				return;
			}
			string parm = parms[1].ToUpper ();
			if (parm.Equals ("TRUE"))
				UseParameters = true;
			else if (parm.Equals ("FALSE"))
				UseParameters = false;
			else
				Console.WriteLine ("Error: invalid parameter.");

		}

		public void SetUseSimpleReader (string[] parms) 
		{
			if (parms.Length != 2) {
				Console.WriteLine ("Error: wrong number of parameters");
				return;
			}
			string parm = parms[1].ToUpper ();
			if (parm.Equals ("TRUE"))
				UseSimpleReader = true;
			else if (parm.Equals ("FALSE"))
				UseSimpleReader = false;
			else
				Console.WriteLine ("Error: invalid parameter.");
		}

		public void SetupSilentMode (string[] parms) 
		{
			if (parms.Length != 2) {
				Console.WriteLine ("Error: wrong number of parameters");
				return;
			}
			string parm = parms[1].ToUpper ();
			if (parm.Equals ("TRUE"))
				silent = true;
			else if (parm.Equals ("FALSE"))
				silent = false;
			else
				Console.WriteLine ("Error: invalid parameter.");
		}

		public void SetInternalVariable(string[] parms) 
		{
			if (parms.Length < 2) {
				Console.WriteLine ("Error: wrong number of parameters.");
				return;
			}
			string parm = parms[1];
			StringBuilder ps = new StringBuilder ();
			
			for (int i = 2; i < parms.Length; i++)
				ps.Append (parms[i]);

			internalVariables[parm] = ps.ToString ();
		}

		public void UnSetInternalVariable(string[] parms) 
		{
			if (parms.Length != 2) {
				Console.WriteLine ("Error: wrong number of parameters.");
				return;
			}
			string parm = parms[1];

			try {
				internalVariables.Remove (parm);
			} catch(Exception e) {
				Console.WriteLine ("Error: internal variable does not exist: " + e.Message);
			}
		}

		public void ShowInternalVariable(string[] parms) 
		{
			string internalVariableValue = "";

			if (parms.Length != 2) {
				Console.WriteLine ("Error: wrong number of parameters.");
				return;
			}
						
			string parm = parms[1];

			if (GetInternalVariable(parm, out internalVariableValue) == true)
				Console.WriteLine ("Internal Variable - Name: " + 
					parm + "  Value: " + internalVariableValue);
		}

		public bool GetInternalVariable(string name, out string sValue) 
		{
			sValue = "";
			bool valueReturned = false;

			try {
				if (internalVariables.ContainsKey (name) == true) {
					sValue = (string) internalVariables[name];
					valueReturned = true;
				}
				else
					Console.WriteLine ("Error: internal variable does not exist.");

			}
			catch(Exception e) {
				Console.WriteLine ("Error: internal variable does not exist: "+	e.Message);
			}
			return valueReturned;
		}

		public void SetupExternalProvider(string[] parms) 
		{
			if (parms.Length != 3) {
				Console.WriteLine ("Error: Wrong number of parameters.");
				return;
			}
			provider = "LOADEXTPROVIDER";
			providerAssembly = parms[1];
			providerConnectionClass = parms[2];
		}

		public bool LoadExternalProvider () 
		{
			string msg = "";
			
			bool success = false;

			// For example: for the MySQL provider in Mono.Data.MySql
			//   \LoadExtProvider Mono.Data.MySql Mono.Data.MySql.MySqlConnection
			//   \ConnectionString dbname=test
			//   \open
			//   insert into sometable (tid, tdesc, aint) values ('abc','def',12)
			//   \exenonquery
			//   \close
			//   \quit

			try {
				OutputLine ("Loading external provider...");

				Assembly ps = Assembly.Load (providerAssembly);
				conType = ps.GetType (providerConnectionClass);
				conn = (IDbConnection) Activator.CreateInstance (conType);
				success = true;
				
				OutputLine ("External provider loaded.");
				UseParameters = false;
			} catch(FileNotFoundException f) {
				msg = "Error: unable to load the assembly of the provider: " + providerAssembly + " : " + f.Message;
				Console.WriteLine(msg);
			}
			catch(Exception e) {
				msg = "Error: unable to load the assembly of the provider: " + providerAssembly + " : " + e.Message;
				Console.WriteLine(msg);
			}
			return success;
		}

		// used for outputting message, but if silent is set,
		// don't display
		public void OutputLine (string line) 
		{
			if (silent == false)
				OutputData (line);
		}

		// used for outputting the header columns of a result
		public void OutputHeader (string line) 
		{
			if (showHeader == true)
				OutputData (line);
		}

		// OutputData() - used for outputting data
		//  if an output filename is set, then the data will
		//  go to a file; otherwise, it will go to the Console.
		public void OutputData(string line) 
		{
			if (outputFilestream == null)
				Console.WriteLine (line);
			else
				outputFilestream.WriteLine (line);
		}

		// HandleCommand - handle SqlSharpCli commands entered
		public void HandleCommand (string entry) 
		{		
			string[] parms;
			
			parms = entry.Split (new char[1] {' '});
			string userCmd = parms[0].ToUpper ();

			switch (userCmd) {
			case "\\PROVIDER":
				ChangeProvider (parms);
				break;
			case "\\CONNECTIONSTRING":
				ChangeConnectionString (entry);
				break;
			case "\\LOADEXTPROVIDER":
				SetupExternalProvider (parms);
				break;
			case "\\OPEN":
				OpenDataSource ();
				break;
			case "\\CLOSE":
				CloseDataSource ();
				break;
			case "\\S":
				SetupSilentMode (parms);
				break;
			case "\\E":
			case "\\EXEQUERY":
			case "\\EXEREADER":
			case "\\EXECUTE":
				// Execute SQL Commands or Queries
				if (conn == null)
					Console.WriteLine ("Error: connection is not Open.");
				else if (conn.State == ConnectionState.Closed)
					Console.WriteLine ("Error: connection is not Open.");
				else {
					if (build == null)
						Console.WriteLine ("Error: SQL Buffer is empty.");
					else {
						buff = build.ToString ();
						ExecuteSql (buff);
					}
					build = null;
				}
				break;
			case "\\EXENONQUERY":
				if (conn == null)
					Console.WriteLine ("Error: connection is not Open.");
				else if (conn.State == ConnectionState.Closed)
					Console.WriteLine ("Error: connection is not Open.");
				else {
					if (build == null)
						Console.WriteLine ("Error: SQL Buffer is empty.");
					else {
						buff = build.ToString ();
						ExecuteSqlNonQuery (buff);
					}
					build = null;
				}
				break;
			case "\\EXESCALAR":
				if (conn == null)
					Console.WriteLine ("Error: connection is not Open.");
				else if (conn.State == ConnectionState.Closed)
					Console.WriteLine ("Error: connection is not Open.");
				else {
					if (build == null)
						Console.WriteLine ("Error: SQL Buffer is empty.");
					else {
						buff = build.ToString ();
						ExecuteSqlScalar (buff);
					}
					build = null;
				}
				break;
			case "\\EXEXML":
				// \exexml OUTPUT_FILENAME
				if (conn == null)
					Console.WriteLine ("Error: connection is not Open.");
				else if (conn.State == ConnectionState.Closed)
					Console.WriteLine ("Error: connection is not Open.");
				else {
					if (build == null)
						Console.WriteLine ("Error: SQL Buffer is empty.");
					else {
						buff = build.ToString ();
						ExecuteSqlXml (buff, parms);
					}
					build = null;
				}
				break;
			case "\\F":
				SetupInputCommandsFile (parms);
				break;
			case "\\O":
				SetupOutputResultsFile (parms);
				break;
			case "\\LOAD":
				// Load file into SQL buffer: \load FILENAME
				LoadBufferFromFile (parms);
				break;
			case "\\SAVE":
				// Save SQL buffer to file: \save FILENAME
				SaveBufferToFile (parms);
				break;
			case "\\H":
			case "\\HELP":
				// Help
				ShowHelp ();
				break;
			case "\\DEFAULTS":
				// show the defaults for provider and connection strings
				ShowDefaults ();
				break;
			case "\\Q": 
			case "\\QUIT":
				// Quit
				break;
			case "\\CLEAR":
			case "\\RESET":
			case "\\R": 
				// reset (clear) the query buffer
				build = null;
				break;
			case "\\SET":
				// sets internal variable
				// \set name value
				SetInternalVariable (parms);
				break;
			case "\\UNSET":
				// deletes internal variable
				// \unset name
				UnSetInternalVariable (parms);
				break;
			case "\\VARIABLE":
				ShowInternalVariable (parms);
				break;
			case "\\PRINT":
				if (build == null)
					Console.WriteLine ("SQL Buffer is empty.");
				else
					Console.WriteLine ("SQL Bufer:\n" + buff);
				break;
			case "\\USEPARAMETERS":
				SetUseParameters (parms);
				break;
			case "\\USESIMPLEREADER":
				SetUseSimpleReader (parms);
				break;
			default:
				// Error
				Console.WriteLine ("Error: Unknown user command.");
				break;
			}
		}

		public void DealWithArgs(string[] args) 
		{
			for (int a = 0; a < args.Length; a++) {
				if (args[a].Substring (0,1).Equals ("-")) {
					string arg = args [a].ToUpper ().Substring (1, args [a].Length - 1);
					switch (arg) {
					case "S":
						silent = true;
						break;
					case "F":		
						if (a + 1 >= args.Length)
							Console.WriteLine ("Error: Missing FILENAME for -f switch");
						else {
							inputFilename = args [a + 1];
							inputFilestream = new StreamReader (inputFilename);
						}
						break;
					case "O":
						if (a + 1 >= args.Length)
							Console.WriteLine ("Error: Missing FILENAME for -o switch");
						else {
							outputFilename = args [a + 1];
							outputFilestream = new StreamWriter (outputFilename);
						}
						break;
					default:
						Console.WriteLine ("Error: Unknow switch: " + args [a]);
						break;
					}
				}
			}
		}
		
		public string ReadSqlSharpCommand() 
		{
			string entry = "";

			if (inputFilestream == null) {
				if (silent == false)
					Console.Error.Write ("\nSQL# ");
				entry = Console.ReadLine ();
			}
			else {
				try {
					entry = inputFilestream.ReadLine ();
					if (entry == null) {
						OutputLine ("Executing SQL# Commands from file done.");
					}
				}
				catch (Exception e) {
					Console.WriteLine ("Error: Reading command from file: " + e.Message);
				}
				if (silent == false)
					Console.Error.Write ("\nSQL# ");
				entry = Console.ReadLine ();
			}
			return entry;
		}
		
		public void Run (string[] args) 
		{
			DealWithArgs (args);

			string entry = "";
			build = null;

			if (silent == false) {
				Console.WriteLine ("Welcome to SQL#. The interactive SQL command-line client ");
				Console.WriteLine ("for Mono.Data.  See http://www.mono-project.com/ for more details.\n");
						
				StartupHelp ();
				ShowDefaults ();
			}
			
			while (entry.ToUpper ().Equals ("\\Q") == false &&
				entry.ToUpper ().Equals ("\\QUIT") == false) {
				
				while ((entry = ReadSqlSharpCommand ()) == "") {}
			
				
				if (entry.Substring(0,1).Equals ("\\")) {
					HandleCommand (entry);
				}
				else if (entry.IndexOf(";") >= 0) {
					// most likely the end of SQL Command or Query found
					// execute the SQL
					if (conn == null)
						Console.WriteLine ("Error: connection is not Open.");
					else if (conn.State == ConnectionState.Closed)
						Console.WriteLine ("Error: connection is not Open.");
					else {
						if (build == null) {
							build = new StringBuilder ();
						}
						build.Append (entry);
						//build.Append ("\n");
						buff = build.ToString ();
						ExecuteSql (buff);
						build = null;
					}
				}
				else {
					// most likely a part of a SQL Command or Query found
					// append this part of the SQL
					if (build == null) {
						build = new StringBuilder ();
					}
					build.Append (entry + "\n");
					buff = build.ToString ();
				}
			}			
			CloseDataSource ();
			if (outputFilestream != null)
				outputFilestream.Close ();
		}
	}

	public enum BindVariableCharacter {
		Colon,         // ':'  - named parameter - :name
		At,            // '@'  - named parameter - @name
		QuestionMark,  // '?'  - positioned parameter - ?
		SquareBrackets // '[]' - delimited named parameter - [name]
	}

	public class ParametersBuilder 
	{
		private BindVariableCharacter bindCharSetting;
		private char bindChar;
		private IDataParameterCollection parms;
		private string sql;
		private IDbCommand cmd;
			
		private void SetBindCharacter () 
		{
			switch(bindCharSetting) {
			case BindVariableCharacter.Colon:
				bindChar = ':';
				break;
			case BindVariableCharacter.At:
				bindChar = '@';
				break;
			case BindVariableCharacter.SquareBrackets:
				bindChar = '[';
				break;
			case BindVariableCharacter.QuestionMark:
				bindChar = '?';
				break;
			}
		}

		public ParametersBuilder (IDbCommand command, BindVariableCharacter bindVarChar) 
		{
			cmd = command;
			sql = cmd.CommandText;
			parms = cmd.Parameters;
			bindCharSetting = bindVarChar;
			SetBindCharacter();
		}	

		public char ParameterMarkerCharacter {
			get {
				return bindChar;
			}
		}

		public int ParseParameters () 
		{	
			int numParms = 0;

			IDataParameterCollection parms = cmd.Parameters;

			char[] chars = sql.ToCharArray ();
			bool bStringConstFound = false;

			for (int i = 0; i < chars.Length; i++) {
				if (chars[i] == '\'') {
					if (bStringConstFound == true)
						bStringConstFound = false;
					else
						bStringConstFound = true;
				}
				else if (chars[i] == bindChar && 
					bStringConstFound == false) {
					if (bindChar != '?') {
						StringBuilder parm = new StringBuilder ();
						i++;
						if (bindChar.Equals ('[')) {
							bool endingBracketFound = false;
							while (i <= chars.Length) {
								char ch;
								if (i == chars.Length)
									ch = ' '; // a space
								else
									ch = chars[i];

								if (Char.IsLetterOrDigit (ch) || ch == ' ') {
									parm.Append (ch);
								}
								else if (ch == ']') {
									endingBracketFound = true;
									string p = parm.ToString ();
									AddParameter (p);
									numParms ++;
									break;
								}
								else throw new Exception("SQL Parser Error: Invalid character in parameter name");
								i++;
							}
							i--;
							if (endingBracketFound == false)
								throw new Exception("SQL Parser Error: Ending bracket not found for parameter");
						}
						else {
							while (i <= chars.Length) {
								char ch;
								if (i == chars.Length)
									ch = ' '; // a space
								else
									ch = chars[i];

								if (Char.IsLetterOrDigit(ch)) {
									parm.Append (ch);
								}
								else {

									string p = parm.ToString ();
									AddParameter (p);
									numParms ++;
									break;
								}
								i++;
							}
							i--;
						}
					}
					else {
						// placeholder paramaeter for ?
						string p = numParms.ToString ();
						AddParameter (p);
						numParms ++;
					}
				}			
			}
			return numParms;
		}

		public void AddParameter (string p) 
		{
			Console.WriteLine ("Add Parameter: " + p);
			if (parms.Contains (p) == false) {
				IDataParameter prm = cmd.CreateParameter ();
				prm.ParameterName = p;
				prm.Direction = ParameterDirection.Input;
				prm.DbType = DbType.String; // default
				prm.Value = ""; // default
				cmd.Parameters.Add(prm);
			}
		}
	}

	public class SqlSharpDriver 
	{
		public static void Main (string[] args) 
		{
			SqlSharpCli sqlCommandLineEngine = new SqlSharpCli ();
			sqlCommandLineEngine.Run (args);
		}
	}
}