mcs/tools/sqlsharp/SqlSharpCli.cs

   1 //
   2 // SqlSharpCli.cs - main driver for Mono SQL Query Command Line Interface
   3 //                  found in mcs/tools/SqlSharp
   4 //
   5 //                  This program is included in Mono and is licenced under the GPL.
   6 //                  http://www.fsf.org/licenses/gpl.html
   7 //
   8 //                  For more information about Mono,
   9 //                  visit http://www.mono-project.com/
  10 //
  11 // To build SqlSharpCli.cs
  12 // $ mcs /out:sqlsharp.exe SqlSharpCli.cs /r:System.Data.dll
  13 //
  14 // To run with mono:
  15 // $ mono sqlsharp.exe
  16 //
  17 // To run with mint:
  18 // $ mint sqlsharp.exe
  19 //
  20 // To run batch commands and get the output, do something like:
  21 // $ cat commands_example.txt | mono sqlsharp.exe -s > results.txt
  22 //
  23 // Author:
  24 //    Daniel Morgan <danielmorgan@verizon.net>
  25 //
  26 // (C)Copyright 2002-2004 Daniel Morgan
  27 //
  28
  29 using System;
  30 using System.Collections;
  31 using System.Data;
  32 using System.Data.Common;
  33 using System.Data.OleDb;
  34 using System.Data.SqlClient;
  35 using System.IO;
  36 using System.Reflection;
  37 using System.Runtime.Remoting;
  38 using System.Text;
  39
  40 namespace Mono.Data.SqlSharp {
  41
  42         public enum FileFormat {
  43                 Html,
  44                 Xml,
  45                 CommaSeparatedValues,
  46                 TabSeparated,
  47                 Normal
  48         }
  49
  50         // SQL Sharp - Command Line Interface
  51         public class SqlSharpCli
  52         {
  53                 // provider supports
  54                 private bool UseParameters = true;
  55                 private bool UseSimpleReader = false;
  56
  57                 private IDbConnection conn = null;
  58
  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 = "";
  67                 // Connection class
  68                 // in the provider assembly that implements the IDbConnection
  69                 // interface.  for example: "Mono.Data.MySql.MySqlConnection"
  70                 Type conType;
  71                 private StringBuilder build = null; // SQL string to build
  72                 private string buff = ""; // SQL string buffer
  73
  74                 private string connectionString = "";
  75
  76                 private string inputFilename = "";
  77                 private string outputFilename = "";
  78                 private StreamReader inputFilestream = null;
  79                 private StreamWriter outputFilestream = null;
  80
  81                 private FileFormat outputFileFormat = FileFormat.Html;
  82
  83                 private bool silent = false;
  84                 private bool showHeader = true;
  85
  86                 private Hashtable internalVariables = new Hashtable();
  87
  88                 // DisplayResult - used to Read() display a result set
  89                 //                   called by DisplayData()
  90
  91                 public bool DisplayResult (IDataReader reader, DataTable schemaTable)
  92                 {
  93                         StringBuilder column = null;
  94                         StringBuilder line = null;
  95                         StringBuilder hdrUnderline = null;
  96                         string outData = "";
  97                         int hdrLen = 0;
  98
  99                         int spacing = 0;
 100                         int columnSize = 0;
 101                         int c;
 102
 103                         char spacingChar = ' '; // a space
 104                         char underlineChar = '='; // an equal sign
 105
 106                         string dataType; // .NET Type
 107                         Type theType;
 108                         DataRow row; // schema row
 109
 110                         line = new StringBuilder ();
 111                         hdrUnderline = new StringBuilder ();
 112
 113                         OutputLine ("");
 114
 115                         for (c = 0; c < reader.FieldCount; c++) {
 116                                 try {
 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);
 123
 124                                         // spacing
 125                                         columnSize = (int) schemaRow ["ColumnSize"];
 126                                         theType = reader.GetFieldType (c);
 127                                         dataType = theType.ToString ();
 128
 129                                         switch (dataType) {
 130                                         case "System.DateTime":
 131                                                 columnSize = 25;
 132                                                 break;
 133                                         case "System.Boolean":
 134                                                 columnSize = 5;
 135                                                 break;
 136                                         case "System.Byte":
 137                                                 columnSize = 1;
 138                                                 break;
 139                                         case "System.Single":
 140                                                 columnSize = 12;
 141                                                 break;
 142                                         case "System.Double":
 143                                                 columnSize = 21;
 144                                                 break;
 145                                         case "System.Int16":
 146                                         case "System.Unt16":
 147                                                 columnSize = 5;
 148                                                 break;
 149                                         case "System.Int32":
 150                                         case "System.UInt32":
 151                                                 columnSize = 10;
 152                                                 break;
 153                                         case "System.Int64":
 154                                                 columnSize = 19;
 155                                                 break;
 156                                         case "System.UInt64":
 157                                                 columnSize = 20;
 158                                                 break;
 159                                         case "System.Decimal":
 160                                                 columnSize = 29;
 161                                                 break;
 162                                         }
 163
 164                                         if (columnSize < 0)
 165                                                 columnSize = 32;
 166                                         if (columnSize > 32)
 167                                                 columnSize = 32;
 168
 169                                         hdrLen = columnHeader.Length;
 170                                         if (hdrLen < 0)
 171                                                 hdrLen = 0;
 172                                         if (hdrLen > 32)
 173                                                 hdrLen = 32;
 174
 175                                         hdrLen = System.Math.Max (hdrLen, columnSize);
 176
 177                                         line.Append (columnHeader);
 178                                         if (columnHeader.Length < hdrLen) {
 179                                                 spacing = hdrLen - columnHeader.Length;
 180                                                 line.Append (spacingChar, spacing);
 181                                         }
 182                                         hdrUnderline.Append (underlineChar, hdrLen);
 183
 184                                         line.Append (" ");
 185                                         hdrUnderline.Append (" ");
 186                                 }
 187                                 catch (Exception e) {
 188                                         OutputLine ("Error: Unable to display header: " + e.Message);
 189                                         return false;
 190                                 }
 191                         }
 192                         OutputHeader (line.ToString ());
 193                         line = null;
 194
 195                         OutputHeader (hdrUnderline.ToString ());
 196                         OutputHeader ("");
 197                         hdrUnderline = null;
 198
 199                         int numRows = 0;
 200
 201                         // column data
 202                         try {
 203                                 while (reader.Read ()) {
 204                                         numRows++;
 205
 206                                         line = new StringBuilder ();
 207                                         for(c = 0; c < reader.FieldCount; c++) {
 208                                                 int dataLen = 0;
 209                                                 string dataValue = "";
 210                                                 column = new StringBuilder ();
 211                                                 outData = "";
 212
 213                                                 row = schemaTable.Rows [c];
 214                                                 string colhdr = (string) reader.GetName (c);
 215                                                 if (colhdr.Equals (""))
 216                                                         colhdr = "column";
 217                                                 if (colhdr.Length > 32)
 218                                                         colhdr = colhdr.Substring (0, 32);
 219
 220                                                 columnSize = (int) row ["ColumnSize"];
 221                                                 theType = reader.GetFieldType (c);
 222                                                 dataType = theType.ToString ();
 223
 224                                                 switch (dataType) {
 225                                                 case "System.DateTime":
 226                                                         columnSize = 25;
 227                                                         break;
 228                                                 case "System.Boolean":
 229                                                         columnSize = 5;
 230                                                         break;
 231                                                 case "System.Byte":
 232                                                         columnSize = 1;
 233                                                         break;
 234                                                 case "System.Single":
 235                                                         columnSize = 12;
 236                                                         break;
 237                                                 case "System.Double":
 238                                                         columnSize = 21;
 239                                                         break;
 240                                                 case "System.Int16":
 241                                                 case "System.Unt16":
 242                                                         columnSize = 5;
 243                                                         break;
 244                                                 case "System.Int32":
 245                                                 case "System.UInt32":
 246                                                         columnSize = 10;
 247                                                         break;
 248                                                 case "System.Int64":
 249                                                         columnSize = 19;
 250                                                         break;
 251                                                 case "System.UInt64":
 252                                                         columnSize = 20;
 253                                                         break;
 254                                                 case "System.Decimal":
 255                                                         columnSize = 29;
 256                                                         break;
 257                                                 }
 258
 259                                                 if (columnSize < 0)
 260                                                         columnSize = 32;
 261                                                 if (columnSize > 32)
 262                                                         columnSize = 32;
 263
 264                                                 hdrLen = colhdr.Length;
 265                                                 if (hdrLen < 0)
 266                                                         hdrLen = 0;
 267                                                 if (hdrLen > 32)
 268                                                         hdrLen = 32;
 269
 270                                                 columnSize = System.Math.Max (colhdr.Length, columnSize);
 271
 272                                                 dataValue = "";
 273                                                 dataLen = 0;
 274
 275                                                 if (!reader.IsDBNull (c)) {
 276                                                         object o = reader.GetValue (c);
 277                                                         if (o.GetType ().ToString ().Equals ("System.Byte[]"))
 278                                                                 dataValue = GetHexString ( (byte[]) o);
 279                                                         else
 280                                                                 dataValue = o.ToString ();
 281
 282                                                         dataLen = dataValue.Length;
 283
 284                                                         if (dataLen <= 0) {
 285                                                                 dataValue = "";
 286                                                                 dataLen = 0;
 287                                                         }
 288                                                         if (dataLen > 32) {
 289                                                                 dataValue = dataValue.Substring (0, 32);
 290                                                                 dataLen = 32;
 291                                                         }
 292
 293                                                         if (dataValue.Equals(""))
 294                                                                 dataLen = 0;
 295                                                 }
 296                                                 columnSize = System.Math.Max (columnSize, dataLen);
 297
 298                                                 if (dataLen < columnSize) {
 299                                                         switch (dataType) {
 300                                                         case "System.Byte":
 301                                                         case "System.SByte":
 302                                                         case "System.Int16":
 303                                                         case "System.UInt16":
 304                                                         case "System.Int32":
 305                                                         case "System.UInt32":
 306                                                         case "System.Int64":
 307                                                         case "System.UInt64":
 308                                                         case "System.Single":
 309                                                         case "System.Double":
 310                                                         case "System.Decimal":
 311                                                                 outData = dataValue.PadLeft (columnSize);
 312                                                                 break;
 313                                                         default:
 314                                                                 outData = dataValue.PadRight (columnSize);
 315                                                                 break;
 316                                                         }
 317                                                 }
 318                                                 else
 319                                                         outData = dataValue;
 320
 321                                                 line.Append (outData);
 322                                                 line.Append (" ");
 323                                         }
 324                                         OutputData (line.ToString ());
 325                                 }
 326                         }
 327                         catch (Exception rr) {
 328                                 OutputLine ("Error: Unable to read next row: " + rr.Message);
 329                                 return false;
 330                         }
 331
 332                         OutputLine ("\nRows retrieved: " + numRows.ToString ());
 333
 334                         return true; // return true - success
 335                 }
 336
 337                 public static string GetHexString (byte[] bytes)
 338                 {
 339                         string bvalue = "";
 340
 341                         if (bytes.Length > 0) {
 342                                 StringBuilder sb = new StringBuilder ();
 343
 344                                 for (int z = 0; z < bytes.Length; z++)
 345                                         sb.Append (bytes [z].ToString ("x"));
 346
 347                                 bvalue = "0x" + sb.ToString ();
 348                         }
 349
 350                         return bvalue;
 351                 }
 352
 353                 public void OutputDataToHtmlFile (IDataReader rdr, DataTable dt)
 354                 {
 355                         StringBuilder strHtml = new StringBuilder ();
 356
 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>");
 363
 364                         outputFilestream.WriteLine (strHtml.ToString ());
 365
 366                         strHtml = new StringBuilder ();
 367
 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>");
 375                         }
 376                         strHtml.Append ("</tr>");
 377                         outputFilestream.WriteLine (strHtml.ToString ());
 378                         strHtml = null;
 379
 380                         int col = 0;
 381                         string dataValue = "";
 382
 383                         while (rdr.Read ()) {
 384                                 strHtml = new StringBuilder ();
 385
 386                                 strHtml.Append ("<tr>");
 387                                 for (col = 0; col < rdr.FieldCount; col++) {
 388
 389                                         // column data
 390                                         if (rdr.IsDBNull (col) == true)
 391                                                 dataValue = "NULL";
 392                                         else {
 393                                                 object obj = rdr.GetValue (col);
 394                                                 dataValue = obj.ToString ();
 395                                         }
 396                                         strHtml.Append ("<td>");
 397                                         strHtml.Append (dataValue);
 398                                         strHtml.Append ("</td>");
 399                                 }
 400                                 strHtml.Append ("\t\t</tr>");
 401                                 outputFilestream.WriteLine (strHtml.ToString ());
 402                                 strHtml = null;
 403                         }
 404                         outputFilestream.WriteLine (" </table> </body> \n </html>");
 405                         strHtml = null;
 406                 }
 407
 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
 413                 //                 for a Command.
 414                 public void DisplayData (IDataReader reader)
 415                 {
 416                         DataTable schemaTable = null;
 417                         int ResultSet = 0;
 418
 419                         do {
 420                                 // by Default, SqlDataReader has the
 421                                 // first Result set if any
 422
 423                                 ResultSet++;
 424                                 OutputLine ("Display the result set " + ResultSet);
 425
 426                                 schemaTable = reader.GetSchemaTable ();
 427
 428                                 if (reader.FieldCount > 0) {
 429                                         // SQL Query (SELECT)
 430                                         // RecordsAffected -1 and DataTable has a reference
 431                                         OutputQueryResult (reader, schemaTable);
 432                                 }
 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);
 437                                 }
 438                                 else {
 439                                         // SQL Command (not INSERT, UPDATE, nor DELETE)
 440                                         // RecordsAffected -1 and DataTable has a null reference
 441                                         Console.WriteLine ("SQL Command Executed.");
 442                                 }
 443
 444                                 // get next result set (if anymore is left)
 445                         } while (reader.NextResult ());
 446                 }
 447
 448                 // display the result in a simple way
 449                 // new ADO.NET providers may have not certain
 450                 // things implemented yet, such as, TableSchema
 451                 // support
 452                 public void DisplayDataSimple (IDataReader reader)
 453                 {
 454                         int row = 0;
 455                         Console.WriteLine ("Reading Data using simple reader...");
 456                         while (reader.Read ()){
 457                                 row++;
 458                                 Console.WriteLine ("Row: " + row);
 459                                 for (int col = 0; col < reader.FieldCount; col++) {
 460                                         int co = col + 1;
 461                                         Console.WriteLine ("  Field: " + co);
 462
 463                                         string dname = (string) reader.GetName (col);
 464                                         if (dname == null)
 465                                                 dname = "?column?";
 466                                         if (dname.Equals (String.Empty))
 467                                                 dname = "?column?";
 468                                         Console.WriteLine ("      Name: " + dname);
 469
 470                                         string dvalue = "";
 471                                         if (reader.IsDBNull (col))
 472                                                 dvalue = "(null)";
 473                                         else
 474                                                 dvalue = reader.GetValue (col).ToString ();
 475                                         Console.WriteLine ("      Value: " + dvalue);
 476                                 }
 477                         }
 478                         Console.WriteLine ("\n" + row + " ROWS RETRIEVED\n");
 479                 }
 480
 481                 public void OutputQueryResult (IDataReader dreader, DataTable dtable)
 482                 {
 483                         if (outputFilestream == null) {
 484                                 DisplayResult (dreader, dtable);
 485                         }
 486                         else {
 487                                 switch (outputFileFormat) {
 488                                 case FileFormat.Normal:
 489                                         DisplayResult (dreader, dtable);
 490                                         break;
 491                                 case FileFormat.Html:
 492                                         OutputDataToHtmlFile (dreader, dtable);
 493                                         break;
 494                                 default:
 495                                         Console.WriteLine ("Error: Output data file format not supported.");
 496                                         break;
 497                                 }
 498                         }
 499                 }
 500
 501                 public void BuildParameters (IDbCommand cmd)
 502                 {
 503                         if (UseParameters == true) {
 504
 505                                 ParametersBuilder parmsBuilder = new ParametersBuilder (cmd, BindVariableCharacter.Colon);
 506
 507                                 Console.WriteLine ("Get Parameters (if any)...");
 508                                 parmsBuilder.ParseParameters ();
 509                                 IList parms = (IList) cmd.Parameters;
 510
 511                                 Console.WriteLine ("Print each parm...");
 512                                 for (int p = 0; p < parms.Count; p++) {
 513                                         string theParmName;
 514
 515                                         IDataParameter prm = (IDataParameter) parms[p];
 516                                         theParmName = prm.ParameterName;
 517
 518                                         string inValue = "";
 519                                         bool found;
 520                                         if (parmsBuilder.ParameterMarkerCharacter == '?') {
 521                                                 Console.Write ("Enter Parameter " +
 522                                                         (p + 1).ToString() +
 523                                                         ": ");
 524                                                 inValue = Console.ReadLine();
 525                                                 prm.Value = inValue;
 526                                         }
 527                                         else {
 528                                                 found = GetInternalVariable (theParmName, out inValue);
 529                                                 if (found == true) {
 530                                                         prm.Value = inValue;
 531                                                 }
 532                                                 else {
 533                                                         Console.Write ("Enter Parameter " + (p + 1).ToString () +
 534                                                                 ": " + theParmName + ": ");
 535                                                         inValue = Console.ReadLine ();
 536                                                         prm.Value = inValue;
 537                                                 }
 538                                         }
 539                                 }
 540                                 parmsBuilder = null;
 541                         }
 542                 }
 543
 544                 // ExecuteSql - Execute the SQL Command(s) and/or Query(ies)
 545                 public void ExecuteSql (string sql)
 546                 {
 547                         string msg = "";
 548
 549                         IDbCommand cmd = null;
 550                         IDataReader reader = null;
 551
 552                         cmd = conn.CreateCommand();
 553
 554                         // set command properties
 555                         cmd.CommandType = CommandType.Text;
 556                         cmd.CommandText = sql;
 557                         cmd.Connection = conn;
 558
 559                         BuildParameters (cmd);
 560
 561                         try {
 562                                 reader = cmd.ExecuteReader ();
 563
 564                                 if (UseSimpleReader == false)
 565                                         DisplayData (reader);
 566                                 else
 567                                         DisplayDataSimple (reader);
 568
 569                                 reader.Close ();
 570                                 reader = null;
 571                         }
 572                         catch (Exception e) {
 573                                 msg = "Error: " + e.Message;
 574                                 Console.WriteLine (msg);
 575                                 reader = null;
 576                         }
 577                         finally {
 578                                 cmd = null;
 579                         }
 580                 }
 581
 582                 // ExecuteSql - Execute the SQL Commands (no SELECTs)
 583                 public void ExecuteSqlNonQuery (string sql)
 584                 {
 585                         string msg = "";
 586
 587                         IDbCommand cmd = null;
 588                         int rowsAffected = -1;
 589
 590                         cmd = conn.CreateCommand();
 591
 592                         // set command properties
 593                         cmd.CommandType = CommandType.Text;
 594                         cmd.CommandText = sql;
 595                         cmd.Connection = conn;
 596
 597                         BuildParameters(cmd);
 598
 599                         try {
 600                                 rowsAffected = cmd.ExecuteNonQuery ();
 601                                 cmd = null;
 602                                 Console.WriteLine ("Rows affected: " + rowsAffected);
 603                         }
 604                         catch(Exception e) {
 605                                 msg = "Error: " + e.Message;
 606                                 Console.WriteLine (msg);
 607                         }
 608                         finally {
 609                                 cmd = null;
 610                         }
 611                 }
 612
 613                 public void ExecuteSqlScalar(string sql)
 614                 {
 615                         string msg = "";
 616
 617                         IDbCommand cmd = null;
 618                         string retrievedValue = "";
 619
 620                         cmd = conn.CreateCommand ();
 621
 622                         // set command properties
 623                         cmd.CommandType = CommandType.Text;
 624                         cmd.CommandText = sql;
 625                         cmd.Connection = conn;
 626
 627                         BuildParameters(cmd);
 628
 629                         try {
 630                                 retrievedValue = (string) cmd.ExecuteScalar ().ToString ();
 631                                 Console.WriteLine ("Retrieved value: " + retrievedValue);
 632                         }
 633                         catch(Exception e) {
 634                                 msg = "Error: " + e.Message;
 635                                 Console.WriteLine (msg);
 636                         }
 637                         finally {
 638                                 cmd = null;
 639                         }
 640                 }
 641
 642                 public void ExecuteSqlXml(string sql, string[] parms)
 643                 {
 644                         string filename = "";
 645
 646                         if (parms.Length != 2) {
 647                                 Console.WriteLine ("Error: wrong number of parameters");
 648                                 return;
 649                         }
 650                         try {
 651                                 filename = parms [1];
 652                         }
 653                         catch (Exception e) {
 654                                 Console.WriteLine ("Error: Unable to setup output results file. " + e.Message);
 655                                 return;
 656                         }
 657
 658                         try {
 659                                 IDbCommand cmd = null;
 660
 661                                 cmd = conn.CreateCommand ();
 662
 663                                 // set command properties
 664                                 cmd.CommandType = CommandType.Text;
 665                                 cmd.CommandText = sql;
 666                                 cmd.Connection = conn;
 667
 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);
 674                         }
 675                         catch (Exception exexml) {
 676                                 Console.WriteLine ("Error: Execute SQL XML Failure: " + exexml);
 677                         }
 678                 }
 679
 680                 public DbDataAdapter CreateNewDataAdapter (IDbCommand command, IDbConnection connection)
 681                 {
 682                         DbDataAdapter adapter = null;
 683
 684                         switch(provider) {
 685                         case "OLEDB":
 686                                 adapter = (DbDataAdapter) new OleDbDataAdapter ();
 687                                 break;
 688                         case "SQLCLIENT":
 689                                 adapter = (DbDataAdapter) new SqlDataAdapter ();
 690                                 break;
 691                         case "LOADEXTPROVIDER":
 692                                 adapter = CreateExternalDataAdapter (command, connection);
 693                                 if (adapter == null)
 694                                         return null;
 695                                 break;
 696                         default:
 697                                 Console.WriteLine("Error: Data Adapter not found in provider.");
 698                                 return null;
 699                         }
 700                         IDbDataAdapter dbAdapter = (IDbDataAdapter) adapter;
 701                         dbAdapter.SelectCommand = command;
 702
 703                         return adapter;
 704                 }
 705
 706                 public DbDataAdapter CreateExternalDataAdapter (IDbCommand command, IDbConnection connection)
 707                 {
 708                         DbDataAdapter adapter = null;
 709
 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);
 716                                 }
 717                         }
 718
 719                         return adapter;
 720                 }
 721
 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 ()
 726                 {
 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.");
 737                         OutputLine ("");
 738                 }
 739
 740                 // ShowHelp - show the help - command a user can enter
 741                 public void ShowHelp ()
 742                 {
 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.");
 764                         WaitForEnterKey ();
 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 ();
 775                 }
 776
 777                 public bool WaitForEnterKey ()
 778                 {
 779                         Console.Write("Waiting... Press Enter key to continue. ");
 780                         string entry = Console.ReadLine();
 781                         if (entry.ToUpper() == "Q")
 782                                 return false;
 783                         return true;
 784                 }
 785
 786                 // ShowDefaults - show defaults for connection variables
 787                 public void ShowDefaults()
 788                 {
 789                         Console.WriteLine ();
 790                         if (provider.Equals (""))
 791                                 Console.WriteLine ("Provider is not set.");
 792                         else {
 793                                 Console.WriteLine ("The default Provider is " + provider);
 794                                 if (provider.Equals ("LOADEXTPROVIDER")) {
 795                                         Console.WriteLine ("  Assembly: " + providerAssembly);
 796                                         Console.WriteLine ("  Connection Class: " + providerConnectionClass);
 797                                 }
 798                         }
 799                         Console.WriteLine ();
 800                         if (connectionString.Equals (""))
 801                                 Console.WriteLine ("ConnectionString is not set.");
 802                         else {
 803                                 Console.WriteLine ("The default ConnectionString is: ");
 804                                 Console.WriteLine ("    \"" + connectionString + "\"");
 805                                 Console.WriteLine ();
 806                         }
 807                 }
 808
 809                 // OpenDataSource - open connection to the data source
 810                 public void OpenDataSource ()
 811                 {
 812                         string msg = "";
 813
 814                         OutputLine ("Opening connection...");
 815
 816                         try {
 817                                 switch (provider) {
 818                                 case "OLEDB":
 819                                         conn = new OleDbConnection ();
 820                                         break;
 821                                 case "SQLCLIENT":
 822                                         conn = new SqlConnection ();
 823                                         break;
 824                                 case "LOADEXTPROVIDER":
 825                                         if (LoadExternalProvider () == false)
 826                                                 return;
 827                                         break;
 828                                 default:
 829                                         Console.WriteLine ("Error: Bad argument or provider not supported.");
 830                                         return;
 831                                 }
 832                         } catch (Exception e) {
 833                                 msg = "Error: Unable to create Connection object because: " + e.Message;
 834                                 Console.WriteLine (msg);
 835                                 return;
 836                         }
 837
 838                         conn.ConnectionString = connectionString;
 839
 840                         try {
 841                                 conn.Open ();
 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);
 847                                 conn = null;
 848                         }
 849                 }
 850
 851                 // CloseDataSource - close the connection to the data source
 852                 public void CloseDataSource () {
 853                         string msg = "";
 854
 855                         if (conn != null) {
 856                                 OutputLine ("Attempt to Close...");
 857                                 try {
 858                                         conn.Close ();
 859                                         OutputLine ("Close was successfull.");
 860                                 } catch(Exception e) {
 861                                         msg = "Exeception Caught Closing. " + e.Message;
 862                                         Console.WriteLine (msg);
 863                                 }
 864                                 conn = null;
 865                         }
 866                 }
 867
 868                 // ChangeProvider - change the provider string variable
 869                 public void ChangeProvider (string[] parms) {
 870
 871                         string[] extp;
 872
 873                         if (parms.Length == 2) {
 874                                 string parm = parms [1].ToUpper ();
 875                                 switch (parm) {
 876                                 case "ORACLE":
 877                                         extp = new string[3] {
 878                                                                      "\\loadextprovider",
 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;
 884                                         break;
 885                                 case "TDS":
 886                                         extp = new string[3] {
 887                                                                      "\\loadextprovider",
 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;
 893                                         break;
 894                                 case "SYBASE":
 895                                         extp = new string[3] {
 896                                                                      "\\loadextprovider",
 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;
 902                                         break;
 903                                 case "BYTEFX":
 904                                         extp = new string[3] {
 905                                                                      "\\loadextprovider",
 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;
 911                                         break;
 912                                 case "MYSQL":
 913                                 case "MYSQLNET":
 914                                         extp = new string[3] {
 915                                                                      "\\loadextprovider",
 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;
 921                                         break;
 922                                 case "SQLITE":
 923                                         extp = new string[3] {
 924                                                                      "\\loadextprovider",
 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;
 930                                         break;
 931                                 case "SQLCLIENT":
 932                                         UseParameters = false;
 933                                         UseSimpleReader = false;
 934                                         provider = parm;
 935                                         break;
 936                                 case "ODBC": // for MS NET 1.1 and above
 937                                         extp = new string[3] {
 938                                                                      "\\loadextprovider",
 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;
 944                                         break;
 945                                 case "MSODBC": // for MS NET 1.0
 946                                         extp = new string[3] {
 947                                                                      "\\loadextprovider",
 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;
 953                                         break;
 954                                 case "OLEDB":
 955                                         UseParameters = false;
 956                                         UseSimpleReader = true;
 957                                         provider = parm;
 958                                         break;
 959                                 case "FIREBIRD":
 960                                         extp = new string[3] {
 961                                                                      "\\loadextprovider",
 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;
 967                                         break;
 968                                 case "POSTGRESQL":
 969                                 case "NPGSQL":
 970                                         extp = new string[3] {
 971                                                                      "\\loadextprovider",
 972                                                                      @"Npgsql, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7",
 973                                                                      "Npgsql.NpgsqlConnection"};
 974                                         SetupExternalProvider (extp);
 975                                         UseParameters = false;
 976                                         UseSimpleReader = false;
 977                                         break;
 978                                 default:
 979                                         Console.WriteLine ("Error: " + "Bad argument or Provider not supported.");
 980                                         break;
 981                                 }
 982                                 OutputLine ("The default Provider is " + provider);
 983                                 if (provider.Equals ("LOADEXTPROVIDER")) {
 984                                         OutputLine ("          Assembly: " +
 985                                                 providerAssembly);
 986                                         OutputLine ("  Connection Class: " +
 987                                                 providerConnectionClass);
 988                                 }
 989                         }
 990                         else
 991                                 Console.WriteLine ("Error: provider only has one parameter.");
 992                 }
 993
 994                 // ChangeConnectionString - change the connection string variable
 995                 public void ChangeConnectionString (string entry)
 996                 {
 997                         if (entry.Length > 18)
 998                                 connectionString = entry.Substring (18, entry.Length - 18);
 999                         else
1000                                 connectionString = "";
1001                 }
1002
1003                 public void SetupOutputResultsFile (string[] parms) {
1004                         if (parms.Length != 2) {
1005                                 Console.WriteLine ("Error: wrong number of parameters");
1006                                 return;
1007                         }
1008                         try {
1009                                 outputFilestream = new StreamWriter (parms[1]);
1010                         }
1011                         catch (Exception e) {
1012                                 Console.WriteLine ("Error: Unable to setup output results file. " + e.Message);
1013                                 return;
1014                         }
1015                 }
1016
1017                 public void SetupInputCommandsFile (string[] parms)
1018                 {
1019                         if (parms.Length != 2) {
1020                                 Console.WriteLine ("Error: wrong number of parameters");
1021                                 return;
1022                         }
1023                         try {
1024                                 inputFilestream = new StreamReader (parms[1]);
1025                         }
1026                         catch (Exception e) {
1027                                 Console.WriteLine ("Error: Unable to setup input commmands file. " + e.Message);
1028                                 return;
1029                         }
1030                 }
1031
1032                 public void LoadBufferFromFile (string[] parms)
1033                 {
1034                         if (parms.Length != 2) {
1035                                 Console.WriteLine ("Error: wrong number of parameters");
1036                                 return;
1037                         }
1038                         string inFilename = parms[1];
1039                         try {
1040                                 StreamReader sr = new StreamReader (inFilename);
1041                                 StringBuilder buffer = new StringBuilder ();
1042                                 string NextLine;
1043
1044                                 while ((NextLine = sr.ReadLine ()) != null) {
1045                                         buffer.Append (NextLine);
1046                                         buffer.Append ("\n");
1047                                 }
1048                                 sr.Close ();
1049                                 buff = buffer.ToString ();
1050                                 build = null;
1051                                 build = new StringBuilder ();
1052                                 build.Append(buff);
1053                         }
1054                         catch (Exception e) {
1055                                 Console.WriteLine ("Error: Unable to read file into SQL Buffer. " + e.Message);
1056                         }
1057                 }
1058
1059                 public void SaveBufferToFile(string[] parms)
1060                 {
1061                         if (parms.Length != 2) {
1062                                 Console.WriteLine ("Error: wrong number of parameters");
1063                                 return;
1064                         }
1065                         string outFilename = parms[1];
1066                         try {
1067                                 StreamWriter sw = new StreamWriter (outFilename);
1068                                 sw.WriteLine (buff);
1069                                 sw.Close ();
1070                         }
1071                         catch (Exception e) {
1072                                 Console.WriteLine ("Error: Could not save SQL Buffer to file." + e.Message);
1073                         }
1074                 }
1075
1076                 public void SetUseParameters (string[] parms)
1077                 {
1078                         if (parms.Length != 2) {
1079                                 Console.WriteLine ("Error: wrong number of parameters");
1080                                 return;
1081                         }
1082                         string parm = parms[1].ToUpper ();
1083                         if (parm.Equals ("TRUE"))
1084                                 UseParameters = true;
1085                         else if (parm.Equals ("FALSE"))
1086                                 UseParameters = false;
1087                         else
1088                                 Console.WriteLine ("Error: invalid parameter.");
1089
1090                 }
1091
1092                 public void SetUseSimpleReader (string[] parms)
1093                 {
1094                         if (parms.Length != 2) {
1095                                 Console.WriteLine ("Error: wrong number of parameters");
1096                                 return;
1097                         }
1098                         string parm = parms[1].ToUpper ();
1099                         if (parm.Equals ("TRUE"))
1100                                 UseSimpleReader = true;
1101                         else if (parm.Equals ("FALSE"))
1102                                 UseSimpleReader = false;
1103                         else
1104                                 Console.WriteLine ("Error: invalid parameter.");
1105                 }
1106
1107                 public void SetupSilentMode (string[] parms)
1108                 {
1109                         if (parms.Length != 2) {
1110                                 Console.WriteLine ("Error: wrong number of parameters");
1111                                 return;
1112                         }
1113                         string parm = parms[1].ToUpper ();
1114                         if (parm.Equals ("TRUE"))
1115                                 silent = true;
1116                         else if (parm.Equals ("FALSE"))
1117                                 silent = false;
1118                         else
1119                                 Console.WriteLine ("Error: invalid parameter.");
1120                 }
1121
1122                 public void SetInternalVariable(string[] parms)
1123                 {
1124                         if (parms.Length < 2) {
1125                                 Console.WriteLine ("Error: wrong number of parameters.");
1126                                 return;
1127                         }
1128                         string parm = parms[1];
1129                         StringBuilder ps = new StringBuilder ();
1130
1131                         for (int i = 2; i < parms.Length; i++)
1132                                 ps.Append (parms[i]);
1133
1134                         internalVariables[parm] = ps.ToString ();
1135                 }
1136
1137                 public void UnSetInternalVariable(string[] parms)
1138                 {
1139                         if (parms.Length != 2) {
1140                                 Console.WriteLine ("Error: wrong number of parameters.");
1141                                 return;
1142                         }
1143                         string parm = parms[1];
1144
1145                         try {
1146                                 internalVariables.Remove (parm);
1147                         } catch(Exception e) {
1148                                 Console.WriteLine ("Error: internal variable does not exist: " + e.Message);
1149                         }
1150                 }
1151
1152                 public void ShowInternalVariable(string[] parms)
1153                 {
1154                         string internalVariableValue = "";
1155
1156                         if (parms.Length != 2) {
1157                                 Console.WriteLine ("Error: wrong number of parameters.");
1158                                 return;
1159                         }
1160
1161                         string parm = parms[1];
1162
1163                         if (GetInternalVariable(parm, out internalVariableValue) == true)
1164                                 Console.WriteLine ("Internal Variable - Name: " +
1165                                         parm + "  Value: " + internalVariableValue);
1166                 }
1167
1168                 public bool GetInternalVariable(string name, out string sValue)
1169                 {
1170                         sValue = "";
1171                         bool valueReturned = false;
1172
1173                         try {
1174                                 if (internalVariables.ContainsKey (name) == true) {
1175                                         sValue = (string) internalVariables[name];
1176                                         valueReturned = true;
1177                                 }
1178                                 else
1179                                         Console.WriteLine ("Error: internal variable does not exist.");
1180
1181                         }
1182                         catch(Exception e) {
1183                                 Console.WriteLine ("Error: internal variable does not exist: "+ e.Message);
1184                         }
1185                         return valueReturned;
1186                 }
1187
1188                 public void SetupExternalProvider(string[] parms)
1189                 {
1190                         if (parms.Length != 3) {
1191                                 Console.WriteLine ("Error: Wrong number of parameters.");
1192                                 return;
1193                         }
1194                         provider = "LOADEXTPROVIDER";
1195                         providerAssembly = parms[1];
1196                         providerConnectionClass = parms[2];
1197                 }
1198
1199                 public bool LoadExternalProvider ()
1200                 {
1201                         string msg = "";
1202
1203                         bool success = false;
1204
1205                         // For example: for the MySQL provider in Mono.Data.MySql
1206                         //   \LoadExtProvider Mono.Data.MySql Mono.Data.MySql.MySqlConnection
1207                         //   \ConnectionString dbname=test
1208                         //   \open
1209                         //   insert into sometable (tid, tdesc, aint) values ('abc','def',12)
1210                         //   \exenonquery
1211                         //   \close
1212                         //   \quit
1213
1214                         try {
1215                                 OutputLine ("Loading external provider...");
1216
1217                                 Assembly ps = Assembly.Load (providerAssembly);
1218                                 conType = ps.GetType (providerConnectionClass);
1219                                 conn = (IDbConnection) Activator.CreateInstance (conType);
1220                                 success = true;
1221
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);
1227                         }
1228                         catch(Exception e) {
1229                                 msg = "Error: unable to load the assembly of the provider: " + providerAssembly + " : " + e.Message;
1230                                 Console.WriteLine(msg);
1231                         }
1232                         return success;
1233                 }
1234
1235                 // used for outputting message, but if silent is set,
1236                 // don't display
1237                 public void OutputLine (string line)
1238                 {
1239                         if (silent == false)
1240                                 OutputData (line);
1241                 }
1242
1243                 // used for outputting the header columns of a result
1244                 public void OutputHeader (string line)
1245                 {
1246                         if (showHeader == true)
1247                                 OutputData (line);
1248                 }
1249
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)
1254                 {
1255                         if (outputFilestream == null)
1256                                 Console.WriteLine (line);
1257                         else
1258                                 outputFilestream.WriteLine (line);
1259                 }
1260
1261                 // HandleCommand - handle SqlSharpCli commands entered
1262                 public void HandleCommand (string entry)
1263                 {
1264                         string[] parms;
1265
1266                         parms = entry.Split (new char[1] {' '});
1267                         string userCmd = parms[0].ToUpper ();
1268
1269                         switch (userCmd) {
1270                         case "\\PROVIDER":
1271                                 ChangeProvider (parms);
1272                                 break;
1273                         case "\\CONNECTIONSTRING":
1274                                 ChangeConnectionString (entry);
1275                                 break;
1276                         case "\\LOADEXTPROVIDER":
1277                                 SetupExternalProvider (parms);
1278                                 break;
1279                         case "\\OPEN":
1280                                 OpenDataSource ();
1281                                 break;
1282                         case "\\CLOSE":
1283                                 CloseDataSource ();
1284                                 break;
1285                         case "\\S":
1286                                 SetupSilentMode (parms);
1287                                 break;
1288                         case "\\E":
1289                         case "\\EXEQUERY":
1290                         case "\\EXEREADER":
1291                         case "\\EXECUTE":
1292                                 // Execute SQL Commands or Queries
1293                                 if (conn == null)
1294                                         Console.WriteLine ("Error: connection is not Open.");
1295                                 else if (conn.State == ConnectionState.Closed)
1296                                         Console.WriteLine ("Error: connection is not Open.");
1297                                 else {
1298                                         if (build == null)
1299                                                 Console.WriteLine ("Error: SQL Buffer is empty.");
1300                                         else {
1301                                                 buff = build.ToString ();
1302                                                 ExecuteSql (buff);
1303                                         }
1304                                         build = null;
1305                                 }
1306                                 break;
1307                         case "\\EXENONQUERY":
1308                                 if (conn == null)
1309                                         Console.WriteLine ("Error: connection is not Open.");
1310                                 else if (conn.State == ConnectionState.Closed)
1311                                         Console.WriteLine ("Error: connection is not Open.");
1312                                 else {
1313                                         if (build == null)
1314                                                 Console.WriteLine ("Error: SQL Buffer is empty.");
1315                                         else {
1316                                                 buff = build.ToString ();
1317                                                 ExecuteSqlNonQuery (buff);
1318                                         }
1319                                         build = null;
1320                                 }
1321                                 break;
1322                         case "\\EXESCALAR":
1323                                 if (conn == null)
1324                                         Console.WriteLine ("Error: connection is not Open.");
1325                                 else if (conn.State == ConnectionState.Closed)
1326                                         Console.WriteLine ("Error: connection is not Open.");
1327                                 else {
1328                                         if (build == null)
1329                                                 Console.WriteLine ("Error: SQL Buffer is empty.");
1330                                         else {
1331                                                 buff = build.ToString ();
1332                                                 ExecuteSqlScalar (buff);
1333                                         }
1334                                         build = null;
1335                                 }
1336                                 break;
1337                         case "\\EXEXML":
1338                                 // \exexml OUTPUT_FILENAME
1339                                 if (conn == null)
1340                                         Console.WriteLine ("Error: connection is not Open.");
1341                                 else if (conn.State == ConnectionState.Closed)
1342                                         Console.WriteLine ("Error: connection is not Open.");
1343                                 else {
1344                                         if (build == null)
1345                                                 Console.WriteLine ("Error: SQL Buffer is empty.");
1346                                         else {
1347                                                 buff = build.ToString ();
1348                                                 ExecuteSqlXml (buff, parms);
1349                                         }
1350                                         build = null;
1351                                 }
1352                                 break;
1353                         case "\\F":
1354                                 SetupInputCommandsFile (parms);
1355                                 break;
1356                         case "\\O":
1357                                 SetupOutputResultsFile (parms);
1358                                 break;
1359                         case "\\LOAD":
1360                                 // Load file into SQL buffer: \load FILENAME
1361                                 LoadBufferFromFile (parms);
1362                                 break;
1363                         case "\\SAVE":
1364                                 // Save SQL buffer to file: \save FILENAME
1365                                 SaveBufferToFile (parms);
1366                                 break;
1367                         case "\\H":
1368                         case "\\HELP":
1369                                 // Help
1370                                 ShowHelp ();
1371                                 break;
1372                         case "\\DEFAULTS":
1373                                 // show the defaults for provider and connection strings
1374                                 ShowDefaults ();
1375                                 break;
1376                         case "\\Q":
1377                         case "\\QUIT":
1378                                 // Quit
1379                                 break;
1380                         case "\\CLEAR":
1381                         case "\\RESET":
1382                         case "\\R":
1383                                 // reset (clear) the query buffer
1384                                 build = null;
1385                                 break;
1386                         case "\\SET":
1387                                 // sets internal variable
1388                                 // \set name value
1389                                 SetInternalVariable (parms);
1390                                 break;
1391                         case "\\UNSET":
1392                                 // deletes internal variable
1393                                 // \unset name
1394                                 UnSetInternalVariable (parms);
1395                                 break;
1396                         case "\\VARIABLE":
1397                                 ShowInternalVariable (parms);
1398                                 break;
1399                         case "\\PRINT":
1400                                 if (build == null)
1401                                         Console.WriteLine ("SQL Buffer is empty.");
1402                                 else
1403                                         Console.WriteLine ("SQL Bufer:\n" + buff);
1404                                 break;
1405                         case "\\USEPARAMETERS":
1406                                 SetUseParameters (parms);
1407                                 break;
1408                         case "\\USESIMPLEREADER":
1409                                 SetUseSimpleReader (parms);
1410                                 break;
1411                         default:
1412                                 // Error
1413                                 Console.WriteLine ("Error: Unknown user command.");
1414                                 break;
1415                         }
1416                 }
1417
1418                 public void DealWithArgs(string[] args)
1419                 {
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);
1423                                         switch (arg) {
1424                                         case "S":
1425                                                 silent = true;
1426                                                 break;
1427                                         case "F":
1428                                                 if (a + 1 >= args.Length)
1429                                                         Console.WriteLine ("Error: Missing FILENAME for -f switch");
1430                                                 else {
1431                                                         inputFilename = args [a + 1];
1432                                                         inputFilestream = new StreamReader (inputFilename);
1433                                                 }
1434                                                 break;
1435                                         case "O":
1436                                                 if (a + 1 >= args.Length)
1437                                                         Console.WriteLine ("Error: Missing FILENAME for -o switch");
1438                                                 else {
1439                                                         outputFilename = args [a + 1];
1440                                                         outputFilestream = new StreamWriter (outputFilename);
1441                                                 }
1442                                                 break;
1443                                         default:
1444                                                 Console.WriteLine ("Error: Unknow switch: " + args [a]);
1445                                                 break;
1446                                         }
1447                                 }
1448                         }
1449                 }
1450
1451                 public string ReadSqlSharpCommand()
1452                 {
1453                         string entry = "";
1454
1455                         if (inputFilestream == null) {
1456                                 if (silent == false)
1457                                         Console.Error.Write ("\nSQL# ");
1458                                 entry = Console.ReadLine ();
1459                         }
1460                         else {
1461                                 try {
1462                                         entry = inputFilestream.ReadLine ();
1463                                         if (entry == null) {
1464                                                 OutputLine ("Executing SQL# Commands from file done.");
1465                                         }
1466                                 }
1467                                 catch (Exception e) {
1468                                         Console.WriteLine ("Error: Reading command from file: " + e.Message);
1469                                 }
1470                                 if (silent == false)
1471                                         Console.Error.Write ("\nSQL# ");
1472                                 entry = Console.ReadLine ();
1473                         }
1474                         return entry;
1475                 }
1476
1477                 public void Run (string[] args)
1478                 {
1479                         DealWithArgs (args);
1480
1481                         string entry = "";
1482                         build = null;
1483
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");
1487
1488                                 StartupHelp ();
1489                                 ShowDefaults ();
1490                         }
1491
1492                         while (entry.ToUpper ().Equals ("\\Q") == false &&
1493                                 entry.ToUpper ().Equals ("\\QUIT") == false) {
1494
1495                                 while ((entry = ReadSqlSharpCommand ()) == "") {}
1496
1497
1498                                 if (entry.Substring(0,1).Equals ("\\")) {
1499                                         HandleCommand (entry);
1500                                 }
1501                                 else if (entry.IndexOf(";") >= 0) {
1502                                         // most likely the end of SQL Command or Query found
1503                                         // execute the SQL
1504                                         if (conn == null)
1505                                                 Console.WriteLine ("Error: connection is not Open.");
1506                                         else if (conn.State == ConnectionState.Closed)
1507                                                 Console.WriteLine ("Error: connection is not Open.");
1508                                         else {
1509                                                 if (build == null) {
1510                                                         build = new StringBuilder ();
1511                                                 }
1512                                                 build.Append (entry);
1513                                                 //build.Append ("\n");
1514                                                 buff = build.ToString ();
1515                                                 ExecuteSql (buff);
1516                                                 build = null;
1517                                         }
1518                                 }
1519                                 else {
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 ();
1524                                         }
1525                                         build.Append (entry + "\n");
1526                                         buff = build.ToString ();
1527                                 }
1528                         }
1529                         CloseDataSource ();
1530                         if (outputFilestream != null)
1531                                 outputFilestream.Close ();
1532                 }
1533         }
1534
1535         public enum BindVariableCharacter {
1536                 Colon,         // ':'  - named parameter - :name
1537                 At,            // '@'  - named parameter - @name
1538                 QuestionMark,  // '?'  - positioned parameter - ?
1539                 SquareBrackets // '[]' - delimited named parameter - [name]
1540         }
1541
1542         public class ParametersBuilder
1543         {
1544                 private BindVariableCharacter bindCharSetting;
1545                 private char bindChar;
1546                 private IDataParameterCollection parms;
1547                 private string sql;
1548                 private IDbCommand cmd;
1549
1550                 private void SetBindCharacter ()
1551                 {
1552                         switch(bindCharSetting) {
1553                         case BindVariableCharacter.Colon:
1554                                 bindChar = ':';
1555                                 break;
1556                         case BindVariableCharacter.At:
1557                                 bindChar = '@';
1558                                 break;
1559                         case BindVariableCharacter.SquareBrackets:
1560                                 bindChar = '[';
1561                                 break;
1562                         case BindVariableCharacter.QuestionMark:
1563                                 bindChar = '?';
1564                                 break;
1565                         }
1566                 }
1567
1568                 public ParametersBuilder (IDbCommand command, BindVariableCharacter bindVarChar)
1569                 {
1570                         cmd = command;
1571                         sql = cmd.CommandText;
1572                         parms = cmd.Parameters;
1573                         bindCharSetting = bindVarChar;
1574                         SetBindCharacter();
1575                 }
1576
1577                 public char ParameterMarkerCharacter {
1578                         get {
1579                                 return bindChar;
1580                         }
1581                 }
1582
1583                 public int ParseParameters ()
1584                 {
1585                         int numParms = 0;
1586
1587                         IDataParameterCollection parms = cmd.Parameters;
1588
1589                         char[] chars = sql.ToCharArray ();
1590                         bool bStringConstFound = false;
1591
1592                         for (int i = 0; i < chars.Length; i++) {
1593                                 if (chars[i] == '\'') {
1594                                         if (bStringConstFound == true)
1595                                                 bStringConstFound = false;
1596                                         else
1597                                                 bStringConstFound = true;
1598                                 }
1599                                 else if (chars[i] == bindChar &&
1600                                         bStringConstFound == false) {
1601                                         if (bindChar != '?') {
1602                                                 StringBuilder parm = new StringBuilder ();
1603                                                 i++;
1604                                                 if (bindChar.Equals ('[')) {
1605                                                         bool endingBracketFound = false;
1606                                                         while (i <= chars.Length) {
1607                                                                 char ch;
1608                                                                 if (i == chars.Length)
1609                                                                         ch = ' '; // a space
1610                                                                 else
1611                                                                         ch = chars[i];
1612
1613                                                                 if (Char.IsLetterOrDigit (ch) || ch == ' ') {
1614                                                                         parm.Append (ch);
1615                                                                 }
1616                                                                 else if (ch == ']') {
1617                                                                         endingBracketFound = true;
1618                                                                         string p = parm.ToString ();
1619                                                                         AddParameter (p);
1620                                                                         numParms ++;
1621                                                                         break;
1622                                                                 }
1623                                                                 else throw new Exception("SQL Parser Error: Invalid character in parameter name");
1624                                                                 i++;
1625                                                         }
1626                                                         i--;
1627                                                         if (endingBracketFound == false)
1628                                                                 throw new Exception("SQL Parser Error: Ending bracket not found for parameter");
1629                                                 }
1630                                                 else {
1631                                                         while (i <= chars.Length) {
1632                                                                 char ch;
1633                                                                 if (i == chars.Length)
1634                                                                         ch = ' '; // a space
1635                                                                 else
1636                                                                         ch = chars[i];
1637
1638                                                                 if (Char.IsLetterOrDigit(ch)) {
1639                                                                         parm.Append (ch);
1640                                                                 }
1641                                                                 else {
1642
1643                                                                         string p = parm.ToString ();
1644                                                                         AddParameter (p);
1645                                                                         numParms ++;
1646                                                                         break;
1647                                                                 }
1648                                                                 i++;
1649                                                         }
1650                                                         i--;
1651                                                 }
1652                                         }
1653                                         else {
1654                                                 // placeholder paramaeter for ?
1655                                                 string p = numParms.ToString ();
1656                                                 AddParameter (p);
1657                                                 numParms ++;
1658                                         }
1659                                 }
1660                         }
1661                         return numParms;
1662                 }
1663
1664                 public void AddParameter (string p)
1665                 {
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);
1674                         }
1675                 }
1676         }
1677
1678         public class SqlSharpDriver
1679         {
1680                 public static void Main (string[] args)
1681                 {
1682                         SqlSharpCli sqlCommandLineEngine = new SqlSharpCli ();
1683                         sqlCommandLineEngine.Run (args);
1684                 }
1685         }
1686 }
1687