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 "MYSQL":
 904                                 case "MYSQLNET":
 905                                         extp = new string[3] {
 906                                                                      "\\loadextprovider",
 907                                                                      @"ByteFX.Data, Version=0.7.6.1, Culture=neutral, PublicKeyToken=0738eb9f132ed756",
 908                                                                      "ByteFX.Data.MySqlClient.MySqlConnection"};
 909                                         SetupExternalProvider (extp);
 910                                         UseParameters = false;
 911                                         UseSimpleReader = false;
 912                                         break;
 913                                 case "SQLITE":
 914                                         extp = new string[3] {
 915                                                                      "\\loadextprovider",
 916                                                                      @"Mono.Data.SqliteClient, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=0738eb9f132ed756",
 917                                                                      "Mono.Data.SqliteClient.SqliteConnection"};
 918                                         SetupExternalProvider (extp);
 919                                         UseParameters = false;
 920                                         UseSimpleReader = true;
 921                                         break;
 922                                 case "SQLCLIENT":
 923                                         UseParameters = false;
 924                                         UseSimpleReader = false;
 925                                         provider = parm;
 926                                         break;
 927                                 case "ODBC": // for MS NET 1.1 and above
 928                                         extp = new string[3] {
 929                                                                      "\\loadextprovider",
 930                                                                      @"System.Data, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089",
 931                                                                      "System.Data.Odbc.OdbcConnection"};
 932                                         SetupExternalProvider (extp);
 933                                         UseParameters = false;
 934                                         UseSimpleReader = false;
 935                                         break;
 936                                 case "MSODBC": // for MS NET 1.0
 937                                         extp = new string[3] {
 938                                                                      "\\loadextprovider",
 939                                                                      @"Microsoft.Data.Odbc, Culture=neutral, PublicKeyToken=b77a5c561934e089, Version=1.0.3300.0",
 940                                                                      "Microsoft.Data.Odbc.OdbcConnection"};
 941                                         SetupExternalProvider (extp);
 942                                         UseParameters = false;
 943                                         UseSimpleReader = false;
 944                                         break;
 945                                 case "OLEDB":
 946                                         UseParameters = false;
 947                                         UseSimpleReader = true;
 948                                         provider = parm;
 949                                         break;
 950                                 case "FIREBIRD":
 951                                         extp = new string[3] {
 952                                                                      "\\loadextprovider",
 953                                                                      @"FirebirdSql.Data.Firebird, Version=1.6.3.0, Culture=neutral, PublicKeyToken=e1b4f92304d7b12f",
 954                                                                      "FirebirdSql.Data.Firebird.FbConnection"};
 955                                         SetupExternalProvider (extp);
 956                                         UseParameters = false;
 957                                         UseSimpleReader = false;
 958                                         break;
 959                                 case "POSTGRESQL":
 960                                 case "NPGSQL":
 961                                         extp = new string[3] {
 962                                                                      "\\loadextprovider",
 963                                                                      @"Npgsql, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7",
 964                                                                      "Npgsql.NpgsqlConnection"};
 965                                         SetupExternalProvider (extp);
 966                                         UseParameters = false;
 967                                         UseSimpleReader = false;
 968                                         break;
 969                                 default:
 970                                         Console.WriteLine ("Error: " + "Bad argument or Provider not supported.");
 971                                         break;
 972                                 }
 973                                 OutputLine ("The default Provider is " + provider);
 974                                 if (provider.Equals ("LOADEXTPROVIDER")) {
 975                                         OutputLine ("          Assembly: " +
 976                                                 providerAssembly);
 977                                         OutputLine ("  Connection Class: " +
 978                                                 providerConnectionClass);
 979                                 }
 980                         }
 981                         else
 982                                 Console.WriteLine ("Error: provider only has one parameter.");
 983                 }
 984
 985                 // ChangeConnectionString - change the connection string variable
 986                 public void ChangeConnectionString (string entry)
 987                 {
 988                         if (entry.Length > 18)
 989                                 connectionString = entry.Substring (18, entry.Length - 18);
 990                         else
 991                                 connectionString = "";
 992                 }
 993
 994                 public void SetupOutputResultsFile (string[] parms) {
 995                         if (parms.Length != 2) {
 996                                 Console.WriteLine ("Error: wrong number of parameters");
 997                                 return;
 998                         }
 999                         try {
1000                                 outputFilestream = new StreamWriter (parms[1]);
1001                         }
1002                         catch (Exception e) {
1003                                 Console.WriteLine ("Error: Unable to setup output results file. " + e.Message);
1004                                 return;
1005                         }
1006                 }
1007
1008                 public void SetupInputCommandsFile (string[] parms)
1009                 {
1010                         if (parms.Length != 2) {
1011                                 Console.WriteLine ("Error: wrong number of parameters");
1012                                 return;
1013                         }
1014                         try {
1015                                 inputFilestream = new StreamReader (parms[1]);
1016                         }
1017                         catch (Exception e) {
1018                                 Console.WriteLine ("Error: Unable to setup input commmands file. " + e.Message);
1019                                 return;
1020                         }
1021                 }
1022
1023                 public void LoadBufferFromFile (string[] parms)
1024                 {
1025                         if (parms.Length != 2) {
1026                                 Console.WriteLine ("Error: wrong number of parameters");
1027                                 return;
1028                         }
1029                         string inFilename = parms[1];
1030                         try {
1031                                 StreamReader sr = new StreamReader (inFilename);
1032                                 StringBuilder buffer = new StringBuilder ();
1033                                 string NextLine;
1034
1035                                 while ((NextLine = sr.ReadLine ()) != null) {
1036                                         buffer.Append (NextLine);
1037                                         buffer.Append ("\n");
1038                                 }
1039                                 sr.Close ();
1040                                 buff = buffer.ToString ();
1041                                 build = null;
1042                                 build = new StringBuilder ();
1043                                 build.Append(buff);
1044                         }
1045                         catch (Exception e) {
1046                                 Console.WriteLine ("Error: Unable to read file into SQL Buffer. " + e.Message);
1047                         }
1048                 }
1049
1050                 public void SaveBufferToFile(string[] parms)
1051                 {
1052                         if (parms.Length != 2) {
1053                                 Console.WriteLine ("Error: wrong number of parameters");
1054                                 return;
1055                         }
1056                         string outFilename = parms[1];
1057                         try {
1058                                 StreamWriter sw = new StreamWriter (outFilename);
1059                                 sw.WriteLine (buff);
1060                                 sw.Close ();
1061                         }
1062                         catch (Exception e) {
1063                                 Console.WriteLine ("Error: Could not save SQL Buffer to file." + e.Message);
1064                         }
1065                 }
1066
1067                 public void SetUseParameters (string[] parms)
1068                 {
1069                         if (parms.Length != 2) {
1070                                 Console.WriteLine ("Error: wrong number of parameters");
1071                                 return;
1072                         }
1073                         string parm = parms[1].ToUpper ();
1074                         if (parm.Equals ("TRUE"))
1075                                 UseParameters = true;
1076                         else if (parm.Equals ("FALSE"))
1077                                 UseParameters = false;
1078                         else
1079                                 Console.WriteLine ("Error: invalid parameter.");
1080
1081                 }
1082
1083                 public void SetUseSimpleReader (string[] parms)
1084                 {
1085                         if (parms.Length != 2) {
1086                                 Console.WriteLine ("Error: wrong number of parameters");
1087                                 return;
1088                         }
1089                         string parm = parms[1].ToUpper ();
1090                         if (parm.Equals ("TRUE"))
1091                                 UseSimpleReader = true;
1092                         else if (parm.Equals ("FALSE"))
1093                                 UseSimpleReader = false;
1094                         else
1095                                 Console.WriteLine ("Error: invalid parameter.");
1096                 }
1097
1098                 public void SetupSilentMode (string[] parms)
1099                 {
1100                         if (parms.Length != 2) {
1101                                 Console.WriteLine ("Error: wrong number of parameters");
1102                                 return;
1103                         }
1104                         string parm = parms[1].ToUpper ();
1105                         if (parm.Equals ("TRUE"))
1106                                 silent = true;
1107                         else if (parm.Equals ("FALSE"))
1108                                 silent = false;
1109                         else
1110                                 Console.WriteLine ("Error: invalid parameter.");
1111                 }
1112
1113                 public void SetInternalVariable(string[] parms)
1114                 {
1115                         if (parms.Length < 2) {
1116                                 Console.WriteLine ("Error: wrong number of parameters.");
1117                                 return;
1118                         }
1119                         string parm = parms[1];
1120                         StringBuilder ps = new StringBuilder ();
1121
1122                         for (int i = 2; i < parms.Length; i++)
1123                                 ps.Append (parms[i]);
1124
1125                         internalVariables[parm] = ps.ToString ();
1126                 }
1127
1128                 public void UnSetInternalVariable(string[] parms)
1129                 {
1130                         if (parms.Length != 2) {
1131                                 Console.WriteLine ("Error: wrong number of parameters.");
1132                                 return;
1133                         }
1134                         string parm = parms[1];
1135
1136                         try {
1137                                 internalVariables.Remove (parm);
1138                         } catch(Exception e) {
1139                                 Console.WriteLine ("Error: internal variable does not exist: " + e.Message);
1140                         }
1141                 }
1142
1143                 public void ShowInternalVariable(string[] parms)
1144                 {
1145                         string internalVariableValue = "";
1146
1147                         if (parms.Length != 2) {
1148                                 Console.WriteLine ("Error: wrong number of parameters.");
1149                                 return;
1150                         }
1151
1152                         string parm = parms[1];
1153
1154                         if (GetInternalVariable(parm, out internalVariableValue) == true)
1155                                 Console.WriteLine ("Internal Variable - Name: " +
1156                                         parm + "  Value: " + internalVariableValue);
1157                 }
1158
1159                 public bool GetInternalVariable(string name, out string sValue)
1160                 {
1161                         sValue = "";
1162                         bool valueReturned = false;
1163
1164                         try {
1165                                 if (internalVariables.ContainsKey (name) == true) {
1166                                         sValue = (string) internalVariables[name];
1167                                         valueReturned = true;
1168                                 }
1169                                 else
1170                                         Console.WriteLine ("Error: internal variable does not exist.");
1171
1172                         }
1173                         catch(Exception e) {
1174                                 Console.WriteLine ("Error: internal variable does not exist: "+ e.Message);
1175                         }
1176                         return valueReturned;
1177                 }
1178
1179                 public void SetupExternalProvider(string[] parms)
1180                 {
1181                         if (parms.Length != 3) {
1182                                 Console.WriteLine ("Error: Wrong number of parameters.");
1183                                 return;
1184                         }
1185                         provider = "LOADEXTPROVIDER";
1186                         providerAssembly = parms[1];
1187                         providerConnectionClass = parms[2];
1188                 }
1189
1190                 public bool LoadExternalProvider ()
1191                 {
1192                         string msg = "";
1193
1194                         bool success = false;
1195
1196                         // For example: for the MySQL provider in Mono.Data.MySql
1197                         //   \LoadExtProvider Mono.Data.MySql Mono.Data.MySql.MySqlConnection
1198                         //   \ConnectionString dbname=test
1199                         //   \open
1200                         //   insert into sometable (tid, tdesc, aint) values ('abc','def',12)
1201                         //   \exenonquery
1202                         //   \close
1203                         //   \quit
1204
1205                         try {
1206                                 OutputLine ("Loading external provider...");
1207
1208                                 Assembly ps = Assembly.Load (providerAssembly);
1209                                 conType = ps.GetType (providerConnectionClass);
1210                                 conn = (IDbConnection) Activator.CreateInstance (conType);
1211                                 success = true;
1212
1213                                 OutputLine ("External provider loaded.");
1214                                 UseParameters = false;
1215                         } catch(FileNotFoundException f) {
1216                                 msg = "Error: unable to load the assembly of the provider: " + providerAssembly + " : " + f.Message;
1217                                 Console.WriteLine(msg);
1218                         }
1219                         catch(Exception e) {
1220                                 msg = "Error: unable to load the assembly of the provider: " + providerAssembly + " : " + e.Message;
1221                                 Console.WriteLine(msg);
1222                         }
1223                         return success;
1224                 }
1225
1226                 // used for outputting message, but if silent is set,
1227                 // don't display
1228                 public void OutputLine (string line)
1229                 {
1230                         if (silent == false)
1231                                 OutputData (line);
1232                 }
1233
1234                 // used for outputting the header columns of a result
1235                 public void OutputHeader (string line)
1236                 {
1237                         if (showHeader == true)
1238                                 OutputData (line);
1239                 }
1240
1241                 // OutputData() - used for outputting data
1242                 //  if an output filename is set, then the data will
1243                 //  go to a file; otherwise, it will go to the Console.
1244                 public void OutputData(string line)
1245                 {
1246                         if (outputFilestream == null)
1247                                 Console.WriteLine (line);
1248                         else
1249                                 outputFilestream.WriteLine (line);
1250                 }
1251
1252                 // HandleCommand - handle SqlSharpCli commands entered
1253                 public void HandleCommand (string entry)
1254                 {
1255                         string[] parms;
1256
1257                         parms = entry.Split (new char[1] {' '});
1258                         string userCmd = parms[0].ToUpper ();
1259
1260                         switch (userCmd) {
1261                         case "\\PROVIDER":
1262                                 ChangeProvider (parms);
1263                                 break;
1264                         case "\\CONNECTIONSTRING":
1265                                 ChangeConnectionString (entry);
1266                                 break;
1267                         case "\\LOADEXTPROVIDER":
1268                                 SetupExternalProvider (parms);
1269                                 break;
1270                         case "\\OPEN":
1271                                 OpenDataSource ();
1272                                 break;
1273                         case "\\CLOSE":
1274                                 CloseDataSource ();
1275                                 break;
1276                         case "\\S":
1277                                 SetupSilentMode (parms);
1278                                 break;
1279                         case "\\E":
1280                         case "\\EXEQUERY":
1281                         case "\\EXEREADER":
1282                         case "\\EXECUTE":
1283                                 // Execute SQL Commands or Queries
1284                                 if (conn == null)
1285                                         Console.WriteLine ("Error: connection is not Open.");
1286                                 else if (conn.State == ConnectionState.Closed)
1287                                         Console.WriteLine ("Error: connection is not Open.");
1288                                 else {
1289                                         if (build == null)
1290                                                 Console.WriteLine ("Error: SQL Buffer is empty.");
1291                                         else {
1292                                                 buff = build.ToString ();
1293                                                 ExecuteSql (buff);
1294                                         }
1295                                         build = null;
1296                                 }
1297                                 break;
1298                         case "\\EXENONQUERY":
1299                                 if (conn == null)
1300                                         Console.WriteLine ("Error: connection is not Open.");
1301                                 else if (conn.State == ConnectionState.Closed)
1302                                         Console.WriteLine ("Error: connection is not Open.");
1303                                 else {
1304                                         if (build == null)
1305                                                 Console.WriteLine ("Error: SQL Buffer is empty.");
1306                                         else {
1307                                                 buff = build.ToString ();
1308                                                 ExecuteSqlNonQuery (buff);
1309                                         }
1310                                         build = null;
1311                                 }
1312                                 break;
1313                         case "\\EXESCALAR":
1314                                 if (conn == null)
1315                                         Console.WriteLine ("Error: connection is not Open.");
1316                                 else if (conn.State == ConnectionState.Closed)
1317                                         Console.WriteLine ("Error: connection is not Open.");
1318                                 else {
1319                                         if (build == null)
1320                                                 Console.WriteLine ("Error: SQL Buffer is empty.");
1321                                         else {
1322                                                 buff = build.ToString ();
1323                                                 ExecuteSqlScalar (buff);
1324                                         }
1325                                         build = null;
1326                                 }
1327                                 break;
1328                         case "\\EXEXML":
1329                                 // \exexml OUTPUT_FILENAME
1330                                 if (conn == null)
1331                                         Console.WriteLine ("Error: connection is not Open.");
1332                                 else if (conn.State == ConnectionState.Closed)
1333                                         Console.WriteLine ("Error: connection is not Open.");
1334                                 else {
1335                                         if (build == null)
1336                                                 Console.WriteLine ("Error: SQL Buffer is empty.");
1337                                         else {
1338                                                 buff = build.ToString ();
1339                                                 ExecuteSqlXml (buff, parms);
1340                                         }
1341                                         build = null;
1342                                 }
1343                                 break;
1344                         case "\\F":
1345                                 SetupInputCommandsFile (parms);
1346                                 break;
1347                         case "\\O":
1348                                 SetupOutputResultsFile (parms);
1349                                 break;
1350                         case "\\LOAD":
1351                                 // Load file into SQL buffer: \load FILENAME
1352                                 LoadBufferFromFile (parms);
1353                                 break;
1354                         case "\\SAVE":
1355                                 // Save SQL buffer to file: \save FILENAME
1356                                 SaveBufferToFile (parms);
1357                                 break;
1358                         case "\\H":
1359                         case "\\HELP":
1360                                 // Help
1361                                 ShowHelp ();
1362                                 break;
1363                         case "\\DEFAULTS":
1364                                 // show the defaults for provider and connection strings
1365                                 ShowDefaults ();
1366                                 break;
1367                         case "\\Q":
1368                         case "\\QUIT":
1369                                 // Quit
1370                                 break;
1371                         case "\\CLEAR":
1372                         case "\\RESET":
1373                         case "\\R":
1374                                 // reset (clear) the query buffer
1375                                 build = null;
1376                                 break;
1377                         case "\\SET":
1378                                 // sets internal variable
1379                                 // \set name value
1380                                 SetInternalVariable (parms);
1381                                 break;
1382                         case "\\UNSET":
1383                                 // deletes internal variable
1384                                 // \unset name
1385                                 UnSetInternalVariable (parms);
1386                                 break;
1387                         case "\\VARIABLE":
1388                                 ShowInternalVariable (parms);
1389                                 break;
1390                         case "\\PRINT":
1391                                 if (build == null)
1392                                         Console.WriteLine ("SQL Buffer is empty.");
1393                                 else
1394                                         Console.WriteLine ("SQL Bufer:\n" + buff);
1395                                 break;
1396                         case "\\USEPARAMETERS":
1397                                 SetUseParameters (parms);
1398                                 break;
1399                         case "\\USESIMPLEREADER":
1400                                 SetUseSimpleReader (parms);
1401                                 break;
1402                         default:
1403                                 // Error
1404                                 Console.WriteLine ("Error: Unknown user command.");
1405                                 break;
1406                         }
1407                 }
1408
1409                 public void DealWithArgs(string[] args)
1410                 {
1411                         for (int a = 0; a < args.Length; a++) {
1412                                 if (args[a].Substring (0,1).Equals ("-")) {
1413                                         string arg = args [a].ToUpper ().Substring (1, args [a].Length - 1);
1414                                         switch (arg) {
1415                                         case "S":
1416                                                 silent = true;
1417                                                 break;
1418                                         case "F":
1419                                                 if (a + 1 >= args.Length)
1420                                                         Console.WriteLine ("Error: Missing FILENAME for -f switch");
1421                                                 else {
1422                                                         inputFilename = args [a + 1];
1423                                                         inputFilestream = new StreamReader (inputFilename);
1424                                                 }
1425                                                 break;
1426                                         case "O":
1427                                                 if (a + 1 >= args.Length)
1428                                                         Console.WriteLine ("Error: Missing FILENAME for -o switch");
1429                                                 else {
1430                                                         outputFilename = args [a + 1];
1431                                                         outputFilestream = new StreamWriter (outputFilename);
1432                                                 }
1433                                                 break;
1434                                         default:
1435                                                 Console.WriteLine ("Error: Unknow switch: " + args [a]);
1436                                                 break;
1437                                         }
1438                                 }
1439                         }
1440                 }
1441
1442                 public string ReadSqlSharpCommand()
1443                 {
1444                         string entry = "";
1445
1446                         if (inputFilestream == null) {
1447                                 if (silent == false)
1448                                         Console.Error.Write ("\nSQL# ");
1449                                 entry = Console.ReadLine ();
1450                         }
1451                         else {
1452                                 try {
1453                                         entry = inputFilestream.ReadLine ();
1454                                         if (entry == null) {
1455                                                 OutputLine ("Executing SQL# Commands from file done.");
1456                                         }
1457                                 }
1458                                 catch (Exception e) {
1459                                         Console.WriteLine ("Error: Reading command from file: " + e.Message);
1460                                 }
1461                                 if (silent == false)
1462                                         Console.Error.Write ("\nSQL# ");
1463                                 entry = Console.ReadLine ();
1464                         }
1465                         return entry;
1466                 }
1467
1468                 public void Run (string[] args)
1469                 {
1470                         DealWithArgs (args);
1471
1472                         string entry = "";
1473                         build = null;
1474
1475                         if (silent == false) {
1476                                 Console.WriteLine ("Welcome to SQL#. The interactive SQL command-line client ");
1477                                 Console.WriteLine ("for Mono.Data.  See http://www.mono-project.com/ for more details.\n");
1478
1479                                 StartupHelp ();
1480                                 ShowDefaults ();
1481                         }
1482
1483                         while (entry.ToUpper ().Equals ("\\Q") == false &&
1484                                 entry.ToUpper ().Equals ("\\QUIT") == false) {
1485
1486                                 while ((entry = ReadSqlSharpCommand ()) == "") {}
1487
1488
1489                                 if (entry.Substring(0,1).Equals ("\\")) {
1490                                         HandleCommand (entry);
1491                                 }
1492                                 else if (entry.IndexOf(";") >= 0) {
1493                                         // most likely the end of SQL Command or Query found
1494                                         // execute the SQL
1495                                         if (conn == null)
1496                                                 Console.WriteLine ("Error: connection is not Open.");
1497                                         else if (conn.State == ConnectionState.Closed)
1498                                                 Console.WriteLine ("Error: connection is not Open.");
1499                                         else {
1500                                                 if (build == null) {
1501                                                         build = new StringBuilder ();
1502                                                 }
1503                                                 build.Append (entry);
1504                                                 //build.Append ("\n");
1505                                                 buff = build.ToString ();
1506                                                 ExecuteSql (buff);
1507                                                 build = null;
1508                                         }
1509                                 }
1510                                 else {
1511                                         // most likely a part of a SQL Command or Query found
1512                                         // append this part of the SQL
1513                                         if (build == null) {
1514                                                 build = new StringBuilder ();
1515                                         }
1516                                         build.Append (entry + "\n");
1517                                         buff = build.ToString ();
1518                                 }
1519                         }
1520                         CloseDataSource ();
1521                         if (outputFilestream != null)
1522                                 outputFilestream.Close ();
1523                 }
1524         }
1525
1526         public enum BindVariableCharacter {
1527                 Colon,         // ':'  - named parameter - :name
1528                 At,            // '@'  - named parameter - @name
1529                 QuestionMark,  // '?'  - positioned parameter - ?
1530                 SquareBrackets // '[]' - delimited named parameter - [name]
1531         }
1532
1533         public class ParametersBuilder
1534         {
1535                 private BindVariableCharacter bindCharSetting;
1536                 private char bindChar;
1537                 private IDataParameterCollection parms;
1538                 private string sql;
1539                 private IDbCommand cmd;
1540
1541                 private void SetBindCharacter ()
1542                 {
1543                         switch(bindCharSetting) {
1544                         case BindVariableCharacter.Colon:
1545                                 bindChar = ':';
1546                                 break;
1547                         case BindVariableCharacter.At:
1548                                 bindChar = '@';
1549                                 break;
1550                         case BindVariableCharacter.SquareBrackets:
1551                                 bindChar = '[';
1552                                 break;
1553                         case BindVariableCharacter.QuestionMark:
1554                                 bindChar = '?';
1555                                 break;
1556                         }
1557                 }
1558
1559                 public ParametersBuilder (IDbCommand command, BindVariableCharacter bindVarChar)
1560                 {
1561                         cmd = command;
1562                         sql = cmd.CommandText;
1563                         parms = cmd.Parameters;
1564                         bindCharSetting = bindVarChar;
1565                         SetBindCharacter();
1566                 }
1567
1568                 public char ParameterMarkerCharacter {
1569                         get {
1570                                 return bindChar;
1571                         }
1572                 }
1573
1574                 public int ParseParameters ()
1575                 {
1576                         int numParms = 0;
1577
1578                         IDataParameterCollection parms = cmd.Parameters;
1579
1580                         char[] chars = sql.ToCharArray ();
1581                         bool bStringConstFound = false;
1582
1583                         for (int i = 0; i < chars.Length; i++) {
1584                                 if (chars[i] == '\'') {
1585                                         if (bStringConstFound == true)
1586                                                 bStringConstFound = false;
1587                                         else
1588                                                 bStringConstFound = true;
1589                                 }
1590                                 else if (chars[i] == bindChar &&
1591                                         bStringConstFound == false) {
1592                                         if (bindChar != '?') {
1593                                                 StringBuilder parm = new StringBuilder ();
1594                                                 i++;
1595                                                 if (bindChar.Equals ('[')) {
1596                                                         bool endingBracketFound = false;
1597                                                         while (i <= chars.Length) {
1598                                                                 char ch;
1599                                                                 if (i == chars.Length)
1600                                                                         ch = ' '; // a space
1601                                                                 else
1602                                                                         ch = chars[i];
1603
1604                                                                 if (Char.IsLetterOrDigit (ch) || ch == ' ') {
1605                                                                         parm.Append (ch);
1606                                                                 }
1607                                                                 else if (ch == ']') {
1608                                                                         endingBracketFound = true;
1609                                                                         string p = parm.ToString ();
1610                                                                         AddParameter (p);
1611                                                                         numParms ++;
1612                                                                         break;
1613                                                                 }
1614                                                                 else throw new Exception("SQL Parser Error: Invalid character in parameter name");
1615                                                                 i++;
1616                                                         }
1617                                                         i--;
1618                                                         if (endingBracketFound == false)
1619                                                                 throw new Exception("SQL Parser Error: Ending bracket not found for parameter");
1620                                                 }
1621                                                 else {
1622                                                         while (i <= chars.Length) {
1623                                                                 char ch;
1624                                                                 if (i == chars.Length)
1625                                                                         ch = ' '; // a space
1626                                                                 else
1627                                                                         ch = chars[i];
1628
1629                                                                 if (Char.IsLetterOrDigit(ch)) {
1630                                                                         parm.Append (ch);
1631                                                                 }
1632                                                                 else {
1633
1634                                                                         string p = parm.ToString ();
1635                                                                         AddParameter (p);
1636                                                                         numParms ++;
1637                                                                         break;
1638                                                                 }
1639                                                                 i++;
1640                                                         }
1641                                                         i--;
1642                                                 }
1643                                         }
1644                                         else {
1645                                                 // placeholder paramaeter for ?
1646                                                 string p = numParms.ToString ();
1647                                                 AddParameter (p);
1648                                                 numParms ++;
1649                                         }
1650                                 }
1651                         }
1652                         return numParms;
1653                 }
1654
1655                 public void AddParameter (string p)
1656                 {
1657                         Console.WriteLine ("Add Parameter: " + p);
1658                         if (parms.Contains (p) == false) {
1659                                 IDataParameter prm = cmd.CreateParameter ();
1660                                 prm.ParameterName = p;
1661                                 prm.Direction = ParameterDirection.Input;
1662                                 prm.DbType = DbType.String; // default
1663                                 prm.Value = ""; // default
1664                                 cmd.Parameters.Add(prm);
1665                         }
1666                 }
1667         }
1668
1669         public class SqlSharpDriver
1670         {
1671                 public static void Main (string[] args)
1672                 {
1673                         SqlSharpCli sqlCommandLineEngine = new SqlSharpCli ();
1674                         sqlCommandLineEngine.Run (args);
1675                 }
1676         }
1677 }
1678