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 batch commands and get the output, do something like:
  18 // $ cat commands_example.txt | mono sqlsharp.exe -s > results.txt
  19 //
  20 // Author:
  21 //    Daniel Morgan <monodanmorg@yahoo.com>
  22 //
  23 // (C)Copyright 2002-2004, 2008 Daniel Morgan
  24 //
  25
  26 using System;
  27 using System.Collections;
  28 using System.Collections.Generic;
  29 using System.Configuration;
  30 using System.Data;
  31 using System.Data.Common;
  32 using System.Data.OleDb;
  33 using System.Data.SqlClient;
  34 using System.IO;
  35 using System.Reflection;
  36 using System.Runtime.Remoting;
  37 using System.Text;
  38
  39 namespace Mono.Data.SqlSharp {
  40
  41         public enum FileFormat {
  42                 Html,
  43                 Xml,
  44                 CommaSeparatedValues,
  45                 TabSeparated,
  46                 Normal
  47         }
  48
  49         // SQL Sharp - Command Line Interface
  50         public class SqlSharpCli
  51         {
  52                 // provider supports
  53                 private bool UseParameters = true;
  54                 private bool UseSimpleReader = false;
  55
  56                 private IDbConnection conn = null;
  57
  58                 private string provider = ""; // name of internal provider
  59                 // {OleDb,SqlClient,MySql,Odbc,Oracle,
  60                 // PostgreSql,SqlLite,Sybase,Tds} however, it
  61                 // can be set to LOADEXTPROVIDER to load an external provider
  62                 private string providerAssembly = "";
  63                 // filename of assembly
  64                 // for example: "Mono.Data.MySql"
  65                 private string providerConnectionClass = "";
  66                 // Connection class
  67                 // in the provider assembly that implements the IDbConnection
  68                 // interface.  for example: "Mono.Data.MySql.MySqlConnection"
  69                 Type conType;
  70                 private StringBuilder build = null; // SQL string to build
  71                 private string buff = ""; // SQL string buffer
  72
  73                 private string connectionString = "";
  74
  75                 private string inputFilename = "";
  76                 private string outputFilename = "";
  77                 private StreamReader inputFilestream = null;
  78                 private StreamWriter outputFilestream = null;
  79
  80                 private string factoryName = null;
  81                 private DbProviderFactory factory = null;
  82
  83                 private FileFormat outputFileFormat = FileFormat.Html;
  84
  85                 private bool silent = false;
  86                 private bool showHeader = true;
  87
  88                 private Hashtable internalVariables = new Hashtable();
  89
  90                 // DisplayResult - used to Read() display a result set
  91                 //                   called by DisplayData()
  92
  93                 public bool DisplayResult (IDataReader reader, DataTable schemaTable)
  94                 {
  95                         StringBuilder column = null;
  96                         StringBuilder line = null;
  97                         StringBuilder hdrUnderline = null;
  98                         string outData = "";
  99                         int hdrLen = 0;
 100
 101                         int spacing = 0;
 102                         int columnSize = 0;
 103                         int c;
 104
 105                         char spacingChar = ' '; // a space
 106                         char underlineChar = '='; // an equal sign
 107
 108                         string dataType; // .NET Type
 109                         Type theType;
 110                         DataRow row; // schema row
 111
 112                         line = new StringBuilder ();
 113                         hdrUnderline = new StringBuilder ();
 114
 115                         OutputLine ("");
 116
 117                         for (c = 0; c < reader.FieldCount; c++) {
 118                                 try {
 119                                         DataRow schemaRow = schemaTable.Rows [c];
 120                                         string columnHeader = reader.GetName (c);
 121                                         if (columnHeader.Equals (""))
 122                                                 columnHeader = "column";
 123                                         if (columnHeader.Length > 32)
 124                                                 columnHeader = columnHeader.Substring (0,32);
 125
 126                                         // spacing
 127                                         columnSize = (int) schemaRow ["ColumnSize"];
 128                                         theType = reader.GetFieldType (c);
 129                                         dataType = theType.ToString ();
 130
 131                                         switch (dataType) {
 132                                         case "System.DateTime":
 133                                                 columnSize = 25;
 134                                                 break;
 135                                         case "System.Boolean":
 136                                                 columnSize = 5;
 137                                                 break;
 138                                         case "System.Byte":
 139                                                 columnSize = 1;
 140                                                 break;
 141                                         case "System.Single":
 142                                                 columnSize = 12;
 143                                                 break;
 144                                         case "System.Double":
 145                                                 columnSize = 21;
 146                                                 break;
 147                                         case "System.Int16":
 148                                         case "System.Unt16":
 149                                                 columnSize = 5;
 150                                                 break;
 151                                         case "System.Int32":
 152                                         case "System.UInt32":
 153                                                 columnSize = 10;
 154                                                 break;
 155                                         case "System.Int64":
 156                                                 columnSize = 19;
 157                                                 break;
 158                                         case "System.UInt64":
 159                                                 columnSize = 20;
 160                                                 break;
 161                                         case "System.Decimal":
 162                                                 columnSize = 29;
 163                                                 break;
 164                                         }
 165
 166                                         if (columnSize < 0)
 167                                                 columnSize = 32;
 168                                         if (columnSize > 32)
 169                                                 columnSize = 32;
 170
 171                                         hdrLen = columnHeader.Length;
 172                                         if (hdrLen < 0)
 173                                                 hdrLen = 0;
 174                                         if (hdrLen > 32)
 175                                                 hdrLen = 32;
 176
 177                                         hdrLen = System.Math.Max (hdrLen, columnSize);
 178
 179                                         line.Append (columnHeader);
 180                                         if (columnHeader.Length < hdrLen) {
 181                                                 spacing = hdrLen - columnHeader.Length;
 182                                                 line.Append (spacingChar, spacing);
 183                                         }
 184                                         hdrUnderline.Append (underlineChar, hdrLen);
 185
 186                                         line.Append (" ");
 187                                         hdrUnderline.Append (" ");
 188                                 }
 189                                 catch (Exception e) {
 190                                         OutputLine ("Error: Unable to display header: " + e.Message);
 191                                         return false;
 192                                 }
 193                         }
 194                         OutputHeader (line.ToString ());
 195                         line = null;
 196
 197                         OutputHeader (hdrUnderline.ToString ());
 198                         OutputHeader ("");
 199                         hdrUnderline = null;
 200
 201                         int numRows = 0;
 202
 203                         // column data
 204                         try {
 205                                 while (reader.Read ()) {
 206                                         numRows++;
 207
 208                                         line = new StringBuilder ();
 209                                         for(c = 0; c < reader.FieldCount; c++) {
 210                                                 int dataLen = 0;
 211                                                 string dataValue = "";
 212                                                 column = new StringBuilder ();
 213                                                 outData = "";
 214
 215                                                 row = schemaTable.Rows [c];
 216                                                 string colhdr = (string) reader.GetName (c);
 217                                                 if (colhdr.Equals (""))
 218                                                         colhdr = "column";
 219                                                 if (colhdr.Length > 32)
 220                                                         colhdr = colhdr.Substring (0, 32);
 221
 222                                                 columnSize = (int) row ["ColumnSize"];
 223                                                 theType = reader.GetFieldType (c);
 224                                                 dataType = theType.ToString ();
 225
 226                                                 switch (dataType) {
 227                                                 case "System.DateTime":
 228                                                         columnSize = 25;
 229                                                         break;
 230                                                 case "System.Boolean":
 231                                                         columnSize = 5;
 232                                                         break;
 233                                                 case "System.Byte":
 234                                                         columnSize = 1;
 235                                                         break;
 236                                                 case "System.Single":
 237                                                         columnSize = 12;
 238                                                         break;
 239                                                 case "System.Double":
 240                                                         columnSize = 21;
 241                                                         break;
 242                                                 case "System.Int16":
 243                                                 case "System.Unt16":
 244                                                         columnSize = 5;
 245                                                         break;
 246                                                 case "System.Int32":
 247                                                 case "System.UInt32":
 248                                                         columnSize = 10;
 249                                                         break;
 250                                                 case "System.Int64":
 251                                                         columnSize = 19;
 252                                                         break;
 253                                                 case "System.UInt64":
 254                                                         columnSize = 20;
 255                                                         break;
 256                                                 case "System.Decimal":
 257                                                         columnSize = 29;
 258                                                         break;
 259                                                 }
 260
 261                                                 if (columnSize < 0)
 262                                                         columnSize = 32;
 263                                                 if (columnSize > 32)
 264                                                         columnSize = 32;
 265
 266                                                 hdrLen = colhdr.Length;
 267                                                 if (hdrLen < 0)
 268                                                         hdrLen = 0;
 269                                                 if (hdrLen > 32)
 270                                                         hdrLen = 32;
 271
 272                                                 columnSize = System.Math.Max (colhdr.Length, columnSize);
 273
 274                                                 dataValue = "";
 275                                                 dataLen = 0;
 276
 277                                                 if (!reader.IsDBNull (c)) {
 278                                                         object o = reader.GetValue (c);
 279                                                         if (o.GetType ().ToString ().Equals ("System.Byte[]"))
 280                                                                 dataValue = GetHexString ( (byte[]) o);
 281                                                         else
 282                                                                 dataValue = o.ToString ();
 283
 284                                                         dataLen = dataValue.Length;
 285
 286                                                         if (dataLen <= 0) {
 287                                                                 dataValue = "";
 288                                                                 dataLen = 0;
 289                                                         }
 290                                                         if (dataLen > 32) {
 291                                                                 dataValue = dataValue.Substring (0, 32);
 292                                                                 dataLen = 32;
 293                                                         }
 294
 295                                                         if (dataValue.Equals(""))
 296                                                                 dataLen = 0;
 297                                                 }
 298                                                 columnSize = System.Math.Max (columnSize, dataLen);
 299
 300                                                 if (dataLen < columnSize) {
 301                                                         switch (dataType) {
 302                                                         case "System.Byte":
 303                                                         case "System.SByte":
 304                                                         case "System.Int16":
 305                                                         case "System.UInt16":
 306                                                         case "System.Int32":
 307                                                         case "System.UInt32":
 308                                                         case "System.Int64":
 309                                                         case "System.UInt64":
 310                                                         case "System.Single":
 311                                                         case "System.Double":
 312                                                         case "System.Decimal":
 313                                                                 outData = dataValue.PadLeft (columnSize);
 314                                                                 break;
 315                                                         default:
 316                                                                 outData = dataValue.PadRight (columnSize);
 317                                                                 break;
 318                                                         }
 319                                                 }
 320                                                 else
 321                                                         outData = dataValue;
 322
 323                                                 line.Append (outData);
 324                                                 line.Append (" ");
 325                                         }
 326                                         OutputData (line.ToString ());
 327                                 }
 328                         }
 329                         catch (Exception rr) {
 330                                 OutputLine ("Error: Unable to read next row: " + rr.Message);
 331                                 return false;
 332                         }
 333
 334                         OutputLine ("\nRows retrieved: " + numRows.ToString ());
 335
 336                         return true; // return true - success
 337                 }
 338
 339                 public static string GetHexString (byte[] bytes)
 340                 {
 341                         string bvalue = "";
 342
 343                         if (bytes.Length > 0) {
 344                                 StringBuilder sb = new StringBuilder ();
 345
 346                                 for (int z = 0; z < bytes.Length; z++)
 347                                         sb.AppendFormat("{0:X2}", bytes [z]);
 348
 349                                 bvalue = "0x" + sb.ToString ();
 350                         }
 351
 352                         return bvalue;
 353                 }
 354
 355                 public void OutputDataToHtmlFile (IDataReader rdr, DataTable dt)
 356                 {
 357                         StringBuilder strHtml = new StringBuilder ();
 358
 359                         strHtml.Append ("<html> \n <head> <title>");
 360                         strHtml.Append ("Results");
 361                         strHtml.Append ("</title> </head>");
 362                         strHtml.Append ("<body>");
 363                         strHtml.Append ("<h1> Results </h1>");
 364                         strHtml.Append ("<table border=1>");
 365
 366                         outputFilestream.WriteLine (strHtml.ToString ());
 367
 368                         strHtml = new StringBuilder ();
 369
 370                         strHtml.Append ("<tr>");
 371                         foreach (DataRow schemaRow in dt.Rows) {
 372                                 strHtml.Append ("<td> <b>");
 373                                 object dataObj = schemaRow ["ColumnName"];
 374                                 string sColumnName = dataObj.ToString ();
 375                                 strHtml.Append (sColumnName);
 376                                 strHtml.Append ("</b> </td>");
 377                         }
 378                         strHtml.Append ("</tr>");
 379                         outputFilestream.WriteLine (strHtml.ToString ());
 380                         strHtml = null;
 381
 382                         int col = 0;
 383                         string dataValue = "";
 384
 385                         while (rdr.Read ()) {
 386                                 strHtml = new StringBuilder ();
 387
 388                                 strHtml.Append ("<tr>");
 389                                 for (col = 0; col < rdr.FieldCount; col++) {
 390
 391                                         // column data
 392                                         if (rdr.IsDBNull (col) == true)
 393                                                 dataValue = "NULL";
 394                                         else {
 395                                                 object obj = rdr.GetValue (col);
 396                                                 dataValue = obj.ToString ();
 397                                         }
 398                                         strHtml.Append ("<td>");
 399                                         strHtml.Append (dataValue);
 400                                         strHtml.Append ("</td>");
 401                                 }
 402                                 strHtml.Append ("\t\t</tr>");
 403                                 outputFilestream.WriteLine (strHtml.ToString ());
 404                                 strHtml = null;
 405                         }
 406                         outputFilestream.WriteLine (" </table> </body> \n </html>");
 407                         strHtml = null;
 408                 }
 409
 410                 // DisplayData - used to display any Result Sets
 411                 //                 from execution of SQL SELECT Query or Queries
 412                 //                 called by DisplayData.
 413                 //                 ExecuteSql() only calls this function
 414                 //                 for a Query, it does not get
 415                 //                 for a Command.
 416                 public void DisplayData (IDataReader reader)
 417                 {
 418                         DataTable schemaTable = null;
 419                         int ResultSet = 0;
 420
 421                         do {
 422                                 // by Default, SqlDataReader has the
 423                                 // first Result set if any
 424
 425                                 ResultSet++;
 426                                 OutputLine ("Display the result set " + ResultSet);
 427
 428                                 schemaTable = reader.GetSchemaTable ();
 429
 430                                 if (reader.FieldCount > 0) {
 431                                         // SQL Query (SELECT)
 432                                         // RecordsAffected -1 and DataTable has a reference
 433                                         OutputQueryResult (reader, schemaTable);
 434                                 }
 435                                 else if (reader.RecordsAffected >= 0) {
 436                                         // SQL Command (INSERT, UPDATE, or DELETE)
 437                                         // RecordsAffected >= 0
 438                                         Console.WriteLine ("SQL Command Records Affected: " + reader.RecordsAffected);
 439                                 }
 440                                 else {
 441                                         // SQL Command (not INSERT, UPDATE, nor DELETE)
 442                                         // RecordsAffected -1 and DataTable has a null reference
 443                                         Console.WriteLine ("SQL Command Executed.");
 444                                 }
 445
 446                                 // get next result set (if anymore is left)
 447                         } while (reader.NextResult ());
 448                 }
 449
 450                 // display the result in a simple way
 451                 // new ADO.NET providers may have not certain
 452                 // things implemented yet, such as, TableSchema
 453                 // support
 454                 public void DisplayDataSimple (IDataReader reader)
 455                 {
 456                         int row = 0;
 457                         Console.WriteLine ("Reading Data using simple reader...");
 458                         while (reader.Read ()){
 459                                 row++;
 460                                 Console.WriteLine ("Row: " + row);
 461                                 for (int col = 0; col < reader.FieldCount; col++) {
 462                                         int co = col + 1;
 463                                         Console.WriteLine ("  Field: " + co);
 464
 465                                         string dname = (string) reader.GetName (col);
 466                                         if (dname == null)
 467                                                 dname = "?column?";
 468                                         if (dname.Equals (String.Empty))
 469                                                 dname = "?column?";
 470                                         Console.WriteLine ("      Name: " + dname);
 471
 472                                         string dvalue = "";
 473                                         if (reader.IsDBNull (col))
 474                                                 dvalue = "(null)";
 475                                         else
 476                                                 dvalue = reader.GetValue (col).ToString ();
 477                                         Console.WriteLine ("      Value: " + dvalue);
 478                                 }
 479                         }
 480                         Console.WriteLine ("\n" + row + " ROWS RETRIEVED\n");
 481                 }
 482
 483                 public void OutputQueryResult (IDataReader dreader, DataTable dtable)
 484                 {
 485                         if (outputFilestream == null) {
 486                                 DisplayResult (dreader, dtable);
 487                         }
 488                         else {
 489                                 switch (outputFileFormat) {
 490                                 case FileFormat.Normal:
 491                                         DisplayResult (dreader, dtable);
 492                                         break;
 493                                 case FileFormat.Html:
 494                                         OutputDataToHtmlFile (dreader, dtable);
 495                                         break;
 496                                 default:
 497                                         Console.WriteLine ("Error: Output data file format not supported.");
 498                                         break;
 499                                 }
 500                         }
 501                 }
 502
 503                 public void BuildParameters (IDbCommand cmd)
 504                 {
 505                         if (UseParameters == true) {
 506
 507                                 ParametersBuilder parmsBuilder = new ParametersBuilder (cmd, BindVariableCharacter.Colon);
 508
 509                                 Console.WriteLine ("Get Parameters (if any)...");
 510                                 parmsBuilder.ParseParameters ();
 511                                 IList parms = (IList) cmd.Parameters;
 512
 513                                 Console.WriteLine ("Print each parm...");
 514                                 for (int p = 0; p < parms.Count; p++) {
 515                                         string theParmName;
 516
 517                                         IDataParameter prm = (IDataParameter) parms[p];
 518                                         theParmName = prm.ParameterName;
 519
 520                                         string inValue = "";
 521                                         bool found;
 522                                         if (parmsBuilder.ParameterMarkerCharacter == '?') {
 523                                                 Console.Write ("Enter Parameter " +
 524                                                         (p + 1).ToString() +
 525                                                         ": ");
 526                                                 inValue = Console.ReadLine();
 527                                                 prm.Value = inValue;
 528                                         }
 529                                         else {
 530                                                 found = GetInternalVariable (theParmName, out inValue);
 531                                                 if (found == true) {
 532                                                         prm.Value = inValue;
 533                                                 }
 534                                                 else {
 535                                                         Console.Write ("Enter Parameter " + (p + 1).ToString () +
 536                                                                 ": " + theParmName + ": ");
 537                                                         inValue = Console.ReadLine ();
 538                                                         prm.Value = inValue;
 539                                                 }
 540                                         }
 541                                 }
 542                                 parmsBuilder = null;
 543                         }
 544                 }
 545
 546                 // ExecuteSql - Execute the SQL Command(s) and/or Query(ies)
 547                 public void ExecuteSql (string sql)
 548                 {
 549                         string msg = "";
 550
 551                         IDbCommand cmd = null;
 552                         IDataReader reader = null;
 553
 554                         cmd = conn.CreateCommand();
 555
 556                         // set command properties
 557                         cmd.CommandType = CommandType.Text;
 558                         cmd.CommandText = sql;
 559                         cmd.Connection = conn;
 560
 561                         BuildParameters (cmd);
 562
 563                         try {
 564                                 reader = cmd.ExecuteReader ();
 565
 566                                 if (UseSimpleReader == false)
 567                                         DisplayData (reader);
 568                                 else
 569                                         DisplayDataSimple (reader);
 570
 571                                 reader.Close ();
 572                                 reader = null;
 573                         }
 574                         catch (Exception e) {
 575                                 msg = "Error: " + e.Message;
 576                                 Console.WriteLine (msg);
 577                                 reader = null;
 578                         }
 579                         finally {
 580                                 cmd = null;
 581                         }
 582                 }
 583
 584                 // ExecuteSql - Execute the SQL Commands (no SELECTs)
 585                 public void ExecuteSqlNonQuery (string sql)
 586                 {
 587                         string msg = "";
 588
 589                         IDbCommand cmd = null;
 590                         int rowsAffected = -1;
 591
 592                         cmd = conn.CreateCommand();
 593
 594                         // set command properties
 595                         cmd.CommandType = CommandType.Text;
 596                         cmd.CommandText = sql;
 597                         cmd.Connection = conn;
 598
 599                         BuildParameters(cmd);
 600
 601                         try {
 602                                 rowsAffected = cmd.ExecuteNonQuery ();
 603                                 cmd = null;
 604                                 Console.WriteLine ("Rows affected: " + rowsAffected);
 605                         }
 606                         catch(Exception e) {
 607                                 msg = "Error: " + e.Message;
 608                                 Console.WriteLine (msg);
 609                         }
 610                         finally {
 611                                 cmd = null;
 612                         }
 613                 }
 614
 615                 public void ExecuteSqlScalar(string sql)
 616                 {
 617                         string msg = "";
 618
 619                         IDbCommand cmd = null;
 620                         string retrievedValue = "";
 621
 622                         cmd = conn.CreateCommand ();
 623
 624                         // set command properties
 625                         cmd.CommandType = CommandType.Text;
 626                         cmd.CommandText = sql;
 627                         cmd.Connection = conn;
 628
 629                         BuildParameters(cmd);
 630
 631                         try {
 632                                 retrievedValue = (string) cmd.ExecuteScalar ().ToString ();
 633                                 Console.WriteLine ("Retrieved value: " + retrievedValue);
 634                         }
 635                         catch(Exception e) {
 636                                 msg = "Error: " + e.Message;
 637                                 Console.WriteLine (msg);
 638                         }
 639                         finally {
 640                                 cmd = null;
 641                         }
 642                 }
 643
 644                 public void ExecuteSqlXml(string sql, string[] parms)
 645                 {
 646                         string filename = "";
 647
 648                         if (parms.Length != 2) {
 649                                 Console.WriteLine ("Error: wrong number of parameters");
 650                                 return;
 651                         }
 652                         try {
 653                                 filename = parms [1];
 654                         }
 655                         catch (Exception e) {
 656                                 Console.WriteLine ("Error: Unable to setup output results file. " + e.Message);
 657                                 return;
 658                         }
 659
 660                         try {
 661                                 IDbCommand cmd = null;
 662
 663                                 cmd = conn.CreateCommand ();
 664
 665                                 // set command properties
 666                                 cmd.CommandType = CommandType.Text;
 667                                 cmd.CommandText = sql;
 668                                 cmd.Connection = conn;
 669
 670                                 BuildParameters (cmd);
 671                                 DataSet dataSet = new DataSet ();
 672                                 DbDataAdapter adapter = CreateNewDataAdapter (cmd, conn);
 673                                 adapter.Fill (dataSet);
 674                                 dataSet.WriteXml (filename);
 675                                 OutputLine ("Data written to xml file: " + filename);
 676                         }
 677                         catch (Exception exexml) {
 678                                 Console.WriteLine ("Error: Execute SQL XML Failure: " + exexml);
 679                         }
 680                 }
 681
 682                 public DbDataAdapter CreateNewDataAdapter (IDbCommand command, IDbConnection connection)
 683                 {
 684                         DbDataAdapter adapter = null;
 685
 686                         if (factory != null) {
 687                                 adapter = factory.CreateDataAdapter();
 688                                 DbCommand cmd = (DbCommand) command;
 689                                 adapter.SelectCommand = cmd;
 690                         }
 691                         else {
 692                                 switch(provider) {
 693                                 case "OLEDB":
 694                                         adapter = (DbDataAdapter) new OleDbDataAdapter ();
 695                                         break;
 696                                 case "SQLCLIENT":
 697                                         adapter = (DbDataAdapter) new SqlDataAdapter ();
 698                                         break;
 699                                 case "LOADEXTPROVIDER":
 700                                         adapter = CreateExternalDataAdapter (command, connection);
 701                                         if (adapter == null)
 702                                                 return null;
 703                                         break;
 704                                 default:
 705                                         Console.WriteLine("Error: Data Adapter not found in provider.");
 706                                         return null;
 707                                 }
 708
 709                                 IDbDataAdapter dbAdapter = (IDbDataAdapter) adapter;
 710                                 dbAdapter.SelectCommand = command;
 711                         }
 712                         return adapter;
 713                 }
 714
 715                 public DbDataAdapter CreateExternalDataAdapter (IDbCommand command, IDbConnection connection)
 716                 {
 717                         DbDataAdapter adapter = null;
 718
 719                         Assembly ass = Assembly.Load (providerAssembly);
 720                         Type [] types = ass.GetTypes ();
 721                         foreach (Type t in types) {
 722                                 if (t.IsSubclassOf (typeof (System.Data.Common.DbDataAdapter))) {
 723                                         if (t.Namespace.Equals (conType.Namespace))
 724                                                 adapter = (DbDataAdapter) Activator.CreateInstance (t);
 725                                 }
 726                         }
 727
 728                         return adapter;
 729                 }
 730
 731                 // like ShowHelp - but only show at the beginning
 732                 // only the most important commands are shown
 733                 // like help and quit
 734                 public void StartupHelp ()
 735                 {
 736                         OutputLine (@"Type:  \Q to quit");
 737                         OutputLine (@"       \ConnectionString to set the ConnectionString");
 738                         OutputLine (@"       \Provider to set the Provider:");
 739                         OutputLine (@"                 {OleDb,SqlClient,MySql,Odbc,DB2,");
 740                         OutputLine (@"                  Oracle,PostgreSql,Sqlite,Sybase,Tds)");
 741                         OutputLine (@"       \Open to open the connection");
 742                         OutputLine (@"       \Close to close the connection");
 743                         OutputLine (@"       \e to execute SQL query (SELECT)");
 744                         OutputLine (@"       \h to show help (all commands).");
 745                         OutputLine (@"       \defaults to show default variables.");
 746                         OutputLine ("");
 747                 }
 748
 749                 // ShowHelp - show the help - command a user can enter
 750                 public void ShowHelp ()
 751                 {
 752                         Console.WriteLine ("");
 753                         Console.WriteLine (@"Type:  \Q to quit");
 754                         Console.WriteLine (@"       \ListP or \ListProviders to get factory providers");
 755                         Console.WriteLine (@"       \CS or \ConnectionString to set the ConnectionString");
 756                         Console.WriteLine (@"       \BCS to Build Connection String");
 757                         Console.WriteLine (@"       \P or \Provider to set the Provider:");
 758                         Console.WriteLine (@"                 {OleDb,SqlClient,MySql,Odbc,");
 759                         Console.WriteLine (@"                  Oracle,PostgreSql,Sqlite,Sybase,Firebird}");
 760                         Console.WriteLine (@"       \Open to open the connection");
 761                         Console.WriteLine (@"       \Close to close the connection");
 762                         Console.WriteLine (@"       \e to execute SQL query (SELECT)");
 763                         Console.WriteLine (@"       \exenonquery to execute an SQL non query (not a SELECT).");
 764                         Console.WriteLine (@"       \exescalar to execute SQL to get a single row and single column.");
 765                         Console.WriteLine (@"       \exexml FILENAME to execute SQL and save output to XML file.");
 766                         if (!WaitForEnterKey ())
 767                                 return;
 768                         Console.WriteLine (@"       \f FILENAME to read a batch of SQL# commands from file.");
 769                         Console.WriteLine (@"       \o FILENAME to write result of commands executed to file.");
 770                         Console.WriteLine (@"       \load FILENAME to load from file SQL commands into SQL buffer.");
 771                         Console.WriteLine (@"       \save FILENAME to save SQL commands from SQL buffer to file.");
 772                         Console.WriteLine (@"       \h to show help (all commands).");
 773                         Console.WriteLine (@"       \defaults to show default variables, such as,");
 774                         Console.WriteLine (@"            Provider and ConnectionString.");
 775                         Console.WriteLine (@"       \s {TRUE, FALSE} to silent messages.");
 776                         Console.WriteLine (@"       \r to reset or clear the query buffer.");
 777                         if (!WaitForEnterKey ())
 778                                 return;
 779                         Console.WriteLine (@"       \set NAME VALUE to set an internal variable.");
 780                         Console.WriteLine (@"       \unset NAME to remove an internal variable.");
 781                         Console.WriteLine (@"       \variable NAME to display the value of an internal variable.");
 782                         Console.WriteLine (@"       \loadextprovider ASSEMBLY CLASS to load the provider");
 783                         Console.WriteLine (@"            use the complete name of its assembly and");
 784                         Console.WriteLine (@"            its Connection class.");
 785                         Console.WriteLine (@"       \print - show what's in the SQL buffer now.");
 786                         Console.WriteLine (@"       \UseParameters (TRUE,FALSE) to use parameters when executing SQL.");
 787                         Console.WriteLine (@"       \UseSimpleReader (TRUE,FALSE) to use simple reader when displaying results.");
 788                         Console.WriteLine ();
 789                 }
 790
 791                 public bool WaitForEnterKey ()
 792                 {
 793                         Console.Write("Waiting... Press Enter key to continue. ");
 794                         string entry = Console.ReadLine();
 795                         if (entry.ToUpper() == "Q")
 796                                 return false;
 797                         return true;
 798                 }
 799
 800                 // ShowDefaults - show defaults for connection variables
 801                 public void ShowDefaults()
 802                 {
 803                         Console.WriteLine ();
 804                         if (provider.Equals (String.Empty) && factory == null)
 805                                 Console.WriteLine ("Provider is not set.");
 806                         else if(factory != null) {
 807                                 Console.WriteLine ("The default Provider is " + factoryName);
 808                         }
 809                         else {
 810                                 Console.WriteLine ("The default Provider is " + provider);
 811                                 if (provider.Equals ("LOADEXTPROVIDER")) {
 812                                         Console.WriteLine ("  Assembly: " + providerAssembly);
 813                                         Console.WriteLine ("  Connection Class: " + providerConnectionClass);
 814                                 }
 815                         }
 816                         Console.WriteLine ();
 817                         if (connectionString.Equals (""))
 818                                 Console.WriteLine ("ConnectionString is not set.");
 819                         else {
 820                                 Console.WriteLine ("The default ConnectionString is: ");
 821                                 Console.WriteLine ("    \"" + connectionString + "\"");
 822                                 Console.WriteLine ();
 823                         }
 824                 }
 825
 826                 // OpenDataSource - open connection to the data source
 827                 public void OpenDataSource ()
 828                 {
 829                         string msg = "";
 830
 831                         if (factoryName.Equals(String.Empty) && provider.Equals(String.Empty)) {
 832                                 Console.Error.WriteLine("Provider not set.");
 833                                 return;
 834                         }
 835
 836                         if (IsOpen()) {
 837                                 Console.Error.WriteLine("Error: already connected.");
 838                                 return;
 839                         }
 840
 841                         OutputLine ("Opening connection...");
 842
 843                         try {
 844                                 if (!factoryName.Equals(String.Empty))
 845                                         conn = factory.CreateConnection();
 846                                 else {
 847                                         switch (provider) {
 848                                         case "OLEDB":
 849                                                 conn = new OleDbConnection ();
 850                                                 break;
 851                                         case "SQLCLIENT":
 852                                                 conn = new SqlConnection ();
 853                                                 break;
 854                                         case "LOADEXTPROVIDER":
 855                                                 if (LoadExternalProvider () == false)
 856                                                         return;
 857                                                 break;
 858                                         default:
 859                                                 Console.WriteLine ("Error: Bad argument or provider not supported.");
 860                                                 return;
 861                                         }
 862                                 }
 863                         } catch (Exception e) {
 864                                 msg = "Error: Unable to create Connection object because: " + e.Message;
 865                                 Console.WriteLine (msg);
 866                                 return;
 867                         }
 868
 869                         conn.ConnectionString = connectionString;
 870
 871                         try {
 872                                 conn.Open ();
 873                                 if (conn.State == ConnectionState.Open)
 874                                         OutputLine ("Open was successfull.");
 875                         } catch (Exception e) {
 876                                 msg = "Exception Caught Opening. " + e.Message;
 877                                 Console.WriteLine (msg);
 878                                 conn = null;
 879                         }
 880                 }
 881
 882                 // CloseDataSource - close the connection to the data source
 883                 public void CloseDataSource () {
 884                         string msg = "";
 885
 886                         if (conn != null) {
 887                                 OutputLine ("Attempt to Close...");
 888                                 try {
 889                                         conn.Close ();
 890                                         OutputLine ("Close was successfull.");
 891                                 } catch(Exception e) {
 892                                         msg = "Exeception Caught Closing. " + e.Message;
 893                                         Console.WriteLine (msg);
 894                                 }
 895                                 conn = null;
 896                         }
 897                 }
 898
 899                 public bool IsOpen () {
 900                         if (conn != null)
 901                                 if (conn.State.Equals(ConnectionState.Open))
 902                                         return true;
 903                         return false;
 904                 }
 905
 906                 // ChangeProvider - change the provider string variable
 907                 public void ChangeProvider (string[] parms) {
 908
 909                         if (IsOpen()) {
 910                                 Console.Error.WriteLine("Error: already connected.");
 911                                 return;
 912                         }
 913
 914                         factory = null;
 915                         factoryName = null;
 916                         connectionString = "";
 917                         provider = "";
 918
 919                         if (parms.Length == 2) {
 920                                 string parm = parms [1].ToUpper ();
 921                                 switch (parm) {
 922                                 case "ORACLE":
 923                                 case "ORACLECLIENT":
 924                                 case "SYSTEM.DATA.ORACLECLIENT":
 925                                         factoryName = "SYSTEM.DATA.ORACLECLIENT";
 926                                         break;
 927                                 case "SYBASE":
 928                                 case "MONO.DATA.SYBASECLIENT":
 929                                         factoryName = "MONO.DATA.SYBASECLIENT";
 930                                         break;
 931                                 case "BYTEFX":
 932                                 case "MYSQL":
 933                                 case "MYSQL.DATA.MYSQLCLIENT":
 934                                         factoryName = "MYSQL.DATA.MYSQLCLIENT";
 935                                         break;
 936                                 case "SQLITE":
 937                                 case "MONO.DATA.SQLITE":
 938                                         factoryName = "MONO.DATA.SQLITE";
 939                                         break;
 940                                 case "ODBC":
 941                                 case "SYSTEM.DATA.ODBC":
 942                                         factoryName = "SYSTEM.DATA.ODBC";
 943                                         break;
 944                                 case "OLEDB":
 945                                 case "SYSTEM.DATA.OLEDB":
 946                                         factoryName = "SYSTEM.DATA.OLEDB";
 947                                         break;
 948                                 case "FIREBIRD":
 949                                 case "FIREBIRDSQL.DATA.FIREBIRD":
 950                                         factoryName = "FIREBIRDSQL.DATA.FIREBIRD";
 951                                         break;
 952                                 case "POSTGRESQL":
 953                                 case "NPGSQL":
 954                                 case "NPGSQL.DATA":
 955                                         factoryName = "NPGSQL.DATA";
 956                                         break;
 957                                 case "SQLCLIENT":
 958                                 case "SYSTEM.DATA.SQLCLIENT":
 959                                         factoryName = "SYSTEM.DATA.SQLCLIENT";
 960                                         break;
 961                                 default:
 962                                         Console.WriteLine ("Error: " + "Bad argument or Provider not supported.");
 963                                         return;
 964                                 }
 965                                 try {
 966                                         factory = DbProviderFactories.GetFactory(factoryName);
 967                                 } catch(ConfigurationException) {
 968                                         Console.Error.WriteLine("*** Error: Unable to load provider factory: " +
 969                                                 factoryName + "\n" +
 970                                                 "*** Check your machine.config to see if the provider is " +
 971                                                 "listed under section system.data and DbProviderFactories " +
 972                                                 "and that your provider assembly is in the GAC.  Your provider " +
 973                                                 "may not support ADO.NET 2.0 factory and other features yet.");
 974                                         factoryName = null;
 975                                         ChangeProviderBackwardsCompat (parms);
 976                                         return;
 977                                 }
 978                                 OutputLine ("The default Provider is " + factoryName);
 979                         }
 980                         else
 981                                 Console.WriteLine ("Error: provider only has one parameter.");
 982                 }
 983
 984                 public void ChangeProviderBackwardsCompat (string[] parms)
 985                 {
 986                         Console.Error.WriteLine ("*** Setting provider using Backwards Compatibility mode.");
 987
 988                         string[] extp;
 989
 990                         if (parms.Length == 2) {
 991                                 string parm = parms [1].ToUpper ();
 992                                 switch (parm) {
 993                                 case "ORACLE":
 994                                         extp = new string[3] {
 995                                                                      "\\loadextprovider",
 996                                                                      @"System.Data.OracleClient, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089",
 997                                                                      "System.Data.OracleClient.OracleConnection"};
 998                                         SetupExternalProvider (extp);
 999                                         UseParameters = false;
1000                                         UseSimpleReader = false;
1001                                         break;
1002                                 case "TDS":
1003                                         extp = new string[3] {
1004                                                                      "\\loadextprovider",
1005                                                                      @"Mono.Data.TdsClient, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=0738eb9f132ed756",
1006                                                                      "Mono.Data.TdsClient.TdsConnection"};
1007                                         SetupExternalProvider (extp);
1008                                         UseParameters = false;
1009                                         UseSimpleReader = false;
1010                                         break;
1011                                 case "SYBASE":
1012                                         extp = new string[3] {
1013                                                                      "\\loadextprovider",
1014                                                                      @"Mono.Data.SybaseClient, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=0738eb9f132ed756",
1015                                                                      "Mono.Data.SybaseClient.SybaseConnection"};
1016                                         SetupExternalProvider (extp);
1017                                         UseParameters = false;
1018                                         UseSimpleReader = false;
1019                                         break;
1020                                 case "BYTEFX":
1021                                         extp = new string[3] {
1022                                                                      "\\loadextprovider",
1023                                                                      @"ByteFX.Data, Version=0.7.6.1, Culture=neutral, PublicKeyToken=0738eb9f132ed756",
1024                                                                      "ByteFX.Data.MySqlClient.MySqlConnection"};
1025                                         SetupExternalProvider (extp);
1026                                         UseParameters = false;
1027                                         UseSimpleReader = false;
1028                                         break;
1029                                 case "MYSQL":
1030                                 case "MYSQLNET":
1031                                         extp = new string[3] {
1032                                                                      "\\loadextprovider",
1033                                                                      @"MySql.Data, Version=1.0.7.30073, Culture=neutral, PublicKeyToken=8e323390df8d9ed4",
1034                                                                      "MySql.Data.MySqlClient.MySqlConnection"};
1035                                         SetupExternalProvider (extp);
1036                                         UseParameters = false;
1037                                         UseSimpleReader = false;
1038                                         break;
1039                                 case "SQLITE":
1040                                         extp = new string[3] {
1041                                                                      "\\loadextprovider",
1042                                                                      @"Mono.Data.SqliteClient, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=0738eb9f132ed756",
1043                                                                      "Mono.Data.SqliteClient.SqliteConnection"};
1044                                         SetupExternalProvider (extp);
1045                                         UseParameters = false;
1046                                         UseSimpleReader = true;
1047                                         break;
1048                                 case "SQLCLIENT":
1049                                         UseParameters = false;
1050                                         UseSimpleReader = false;
1051                                         provider = parm;
1052                                         break;
1053                                 case "ODBC": // for MS NET 1.1 and above
1054                                         extp = new string[3] {
1055                                                                      "\\loadextprovider",
1056                                                                      @"System.Data, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089",
1057                                                                      "System.Data.Odbc.OdbcConnection"};
1058                                         SetupExternalProvider (extp);
1059                                         UseParameters = false;
1060                                         UseSimpleReader = false;
1061                                         break;
1062                                 case "MSODBC": // for MS NET 1.0
1063                                         extp = new string[3] {
1064                                                                      "\\loadextprovider",
1065                                                                      @"Microsoft.Data.Odbc, Culture=neutral, PublicKeyToken=b77a5c561934e089, Version=1.0.3300.0",
1066                                                                      "Microsoft.Data.Odbc.OdbcConnection"};
1067                                         SetupExternalProvider (extp);
1068                                         UseParameters = false;
1069                                         UseSimpleReader = false;
1070                                         break;
1071                                 case "OLEDB":
1072                                         UseParameters = false;
1073                                         UseSimpleReader = true;
1074                                         provider = parm;
1075                                         break;
1076                                 case "FIREBIRD":
1077                                         extp = new string[3] {
1078                                                                      "\\loadextprovider",
1079                                                                      @"FirebirdSql.Data.Firebird, Version=1.7.1.0, Culture=neutral, PublicKeyToken=0706f5520aae4ff4",
1080                                                                      "FirebirdSql.Data.Firebird.FbConnection"};
1081                                         SetupExternalProvider (extp);
1082                                         UseParameters = false;
1083                                         UseSimpleReader = false;
1084                                         break;
1085                                 case "POSTGRESQL":
1086                                 case "NPGSQL":
1087                                         extp = new string[3] {
1088                                                                      "\\loadextprovider",
1089                                                                      @"Npgsql, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7",
1090                                                                      "Npgsql.NpgsqlConnection"};
1091                                         SetupExternalProvider (extp);
1092                                         UseParameters = false;
1093                                         UseSimpleReader = false;
1094                                         break;
1095                                 default:
1096                                         Console.WriteLine ("Error: " + "Bad argument or Provider not supported.");
1097                                         break;
1098                                 }
1099                                 OutputLine ("The default Provider is " + provider);
1100                                 if (provider.Equals ("LOADEXTPROVIDER")) {
1101                                         OutputLine ("          Assembly: " +
1102                                                 providerAssembly);
1103                                         OutputLine ("  Connection Class: " +
1104                                                 providerConnectionClass);
1105                                 }
1106                         }
1107                         else
1108                                 Console.WriteLine ("Error: provider only has one parameter.");
1109                 }
1110
1111                 // ChangeConnectionString - change the connection string variable
1112                 public void ChangeConnectionString (string[] parms, string entry)
1113                 {
1114                         if (parms.Length >= 2)
1115                                 connectionString = entry.Substring (parms[0].Length, entry.Length - (parms[0].Length + 1));
1116                         else
1117                                 connectionString = "";
1118                 }
1119
1120                 public void SetupOutputResultsFile (string[] parms) {
1121                         if (parms.Length != 2) {
1122                                 Console.WriteLine ("Error: wrong number of parameters");
1123                                 return;
1124                         }
1125                         try {
1126                                 outputFilestream = new StreamWriter (parms[1]);
1127                         }
1128                         catch (Exception e) {
1129                                 Console.WriteLine ("Error: Unable to setup output results file. " + e.Message);
1130                                 return;
1131                         }
1132                 }
1133
1134                 public void SetupInputCommandsFile (string[] parms)
1135                 {
1136                         if (parms.Length != 2) {
1137                                 Console.WriteLine ("Error: wrong number of parameters");
1138                                 return;
1139                         }
1140                         try {
1141                                 inputFilestream = new StreamReader (parms[1]);
1142                         }
1143                         catch (Exception e) {
1144                                 Console.WriteLine ("Error: Unable to setup input commmands file. " + e.Message);
1145                                 return;
1146                         }
1147                 }
1148
1149                 public void LoadBufferFromFile (string[] parms)
1150                 {
1151                         if (parms.Length != 2) {
1152                                 Console.WriteLine ("Error: wrong number of parameters");
1153                                 return;
1154                         }
1155                         string inFilename = parms[1];
1156                         try {
1157                                 StreamReader sr = new StreamReader (inFilename);
1158                                 StringBuilder buffer = new StringBuilder ();
1159                                 string NextLine;
1160
1161                                 while ((NextLine = sr.ReadLine ()) != null) {
1162                                         buffer.Append (NextLine);
1163                                         buffer.Append ("\n");
1164                                 }
1165                                 sr.Close ();
1166                                 buff = buffer.ToString ();
1167                                 build = null;
1168                                 build = new StringBuilder ();
1169                                 build.Append(buff);
1170                         }
1171                         catch (Exception e) {
1172                                 Console.WriteLine ("Error: Unable to read file into SQL Buffer. " + e.Message);
1173                         }
1174                 }
1175
1176                 public void SaveBufferToFile(string[] parms)
1177                 {
1178                         if (parms.Length != 2) {
1179                                 Console.WriteLine ("Error: wrong number of parameters");
1180                                 return;
1181                         }
1182                         string outFilename = parms[1];
1183                         try {
1184                                 StreamWriter sw = new StreamWriter (outFilename);
1185                                 sw.WriteLine (buff);
1186                                 sw.Close ();
1187                         }
1188                         catch (Exception e) {
1189                                 Console.WriteLine ("Error: Could not save SQL Buffer to file." + e.Message);
1190                         }
1191                 }
1192
1193                 public void SetUseParameters (string[] parms)
1194                 {
1195                         if (parms.Length != 2) {
1196                                 Console.WriteLine ("Error: wrong number of parameters");
1197                                 return;
1198                         }
1199                         string parm = parms[1].ToUpper ();
1200                         if (parm.Equals ("TRUE"))
1201                                 UseParameters = true;
1202                         else if (parm.Equals ("FALSE"))
1203                                 UseParameters = false;
1204                         else
1205                                 Console.WriteLine ("Error: invalid parameter.");
1206
1207                 }
1208
1209                 public void SetUseSimpleReader (string[] parms)
1210                 {
1211                         if (parms.Length != 2) {
1212                                 Console.WriteLine ("Error: wrong number of parameters");
1213                                 return;
1214                         }
1215                         string parm = parms[1].ToUpper ();
1216                         if (parm.Equals ("TRUE"))
1217                                 UseSimpleReader = true;
1218                         else if (parm.Equals ("FALSE"))
1219                                 UseSimpleReader = false;
1220                         else
1221                                 Console.WriteLine ("Error: invalid parameter.");
1222                 }
1223
1224                 public void SetupSilentMode (string[] parms)
1225                 {
1226                         if (parms.Length != 2) {
1227                                 Console.WriteLine ("Error: wrong number of parameters");
1228                                 return;
1229                         }
1230                         string parm = parms[1].ToUpper ();
1231                         if (parm.Equals ("TRUE"))
1232                                 silent = true;
1233                         else if (parm.Equals ("FALSE"))
1234                                 silent = false;
1235                         else
1236                                 Console.WriteLine ("Error: invalid parameter.");
1237                 }
1238
1239                 public void SetInternalVariable(string[] parms)
1240                 {
1241                         if (parms.Length < 2) {
1242                                 Console.WriteLine ("Error: wrong number of parameters.");
1243                                 return;
1244                         }
1245                         string parm = parms[1];
1246                         StringBuilder ps = new StringBuilder ();
1247
1248                         for (int i = 2; i < parms.Length; i++)
1249                                 ps.Append (parms[i]);
1250
1251                         internalVariables[parm] = ps.ToString ();
1252                 }
1253
1254                 public void UnSetInternalVariable(string[] parms)
1255                 {
1256                         if (parms.Length != 2) {
1257                                 Console.WriteLine ("Error: wrong number of parameters.");
1258                                 return;
1259                         }
1260                         string parm = parms[1];
1261
1262                         try {
1263                                 internalVariables.Remove (parm);
1264                         } catch(Exception e) {
1265                                 Console.WriteLine ("Error: internal variable does not exist: " + e.Message);
1266                         }
1267                 }
1268
1269                 public void ShowInternalVariable(string[] parms)
1270                 {
1271                         string internalVariableValue = "";
1272
1273                         if (parms.Length != 2) {
1274                                 Console.WriteLine ("Error: wrong number of parameters.");
1275                                 return;
1276                         }
1277
1278                         string parm = parms[1];
1279
1280                         if (GetInternalVariable(parm, out internalVariableValue) == true)
1281                                 Console.WriteLine ("Internal Variable - Name: " +
1282                                         parm + "  Value: " + internalVariableValue);
1283                 }
1284
1285                 public bool GetInternalVariable(string name, out string sValue)
1286                 {
1287                         sValue = "";
1288                         bool valueReturned = false;
1289
1290                         try {
1291                                 if (internalVariables.ContainsKey (name) == true) {
1292                                         sValue = (string) internalVariables[name];
1293                                         valueReturned = true;
1294                                 }
1295                                 else
1296                                         Console.WriteLine ("Error: internal variable does not exist.");
1297
1298                         }
1299                         catch(Exception e) {
1300                                 Console.WriteLine ("Error: internal variable does not exist: "+ e.Message);
1301                         }
1302                         return valueReturned;
1303                 }
1304
1305                 public void SetupExternalProvider(string[] parms)
1306                 {
1307                         if (parms.Length != 3) {
1308                                 Console.WriteLine ("Error: Wrong number of parameters.");
1309                                 return;
1310                         }
1311                         provider = "LOADEXTPROVIDER";
1312                         providerAssembly = parms[1];
1313                         providerConnectionClass = parms[2];
1314                 }
1315
1316                 public bool LoadExternalProvider ()
1317                 {
1318                         string msg = "";
1319
1320                         bool success = false;
1321
1322                         // For example: for the MySQL provider in Mono.Data.MySql
1323                         //   \LoadExtProvider Mono.Data.MySql Mono.Data.MySql.MySqlConnection
1324                         //   \ConnectionString dbname=test
1325                         //   \open
1326                         //   insert into sometable (tid, tdesc, aint) values ('abc','def',12)
1327                         //   \exenonquery
1328                         //   \close
1329                         //   \quit
1330
1331                         try {
1332                                 OutputLine ("Loading external provider...");
1333
1334                                 Assembly ps = Assembly.Load (providerAssembly);
1335                                 conType = ps.GetType (providerConnectionClass);
1336                                 conn = (IDbConnection) Activator.CreateInstance (conType);
1337                                 success = true;
1338
1339                                 OutputLine ("External provider loaded.");
1340                                 UseParameters = false;
1341                         } catch(FileNotFoundException f) {
1342                                 msg = "Error: unable to load the assembly of the provider: " + providerAssembly + " : " + f.Message;
1343                                 Console.WriteLine(msg);
1344                         }
1345                         catch(Exception e) {
1346                                 msg = "Error: unable to load the assembly of the provider: " + providerAssembly + " : " + e.Message;
1347                                 Console.WriteLine(msg);
1348                         }
1349                         return success;
1350                 }
1351
1352                 // used for outputting message, but if silent is set,
1353                 // don't display
1354                 public void OutputLine (string line)
1355                 {
1356                         if (silent == false)
1357                                 OutputData (line);
1358                 }
1359
1360                 // used for outputting the header columns of a result
1361                 public void OutputHeader (string line)
1362                 {
1363                         if (showHeader == true)
1364                                 OutputData (line);
1365                 }
1366
1367                 // OutputData() - used for outputting data
1368                 //  if an output filename is set, then the data will
1369                 //  go to a file; otherwise, it will go to the Console.
1370                 public void OutputData(string line)
1371                 {
1372                         if (outputFilestream == null)
1373                                 Console.WriteLine (line);
1374                         else
1375                                 outputFilestream.WriteLine (line);
1376                 }
1377
1378                 // HandleCommand - handle SqlSharpCli commands entered
1379                 public void HandleCommand (string entry)
1380                 {
1381                         string[] parms;
1382
1383                         parms = entry.Split (new char[1] {' '});
1384                         string userCmd = parms[0].ToUpper ();
1385
1386                         switch (userCmd) {
1387                         case "\\LISTPROVIDERS":
1388                         case "\\LISTP":
1389                                 ListProviders ();
1390                                 break;
1391                         case "\\PROVIDER":
1392                         case "\\P":
1393                                 ChangeProvider (parms);
1394                                 break;
1395                         case "\\CONNECTIONSTRING":
1396                         case "\\CS":
1397                                 ChangeConnectionString (parms, entry);
1398                                 break;
1399                         case "\\LOADEXTPROVIDER":
1400                                 SetupExternalProvider (parms);
1401                                 break;
1402                         case "\\OPEN":
1403                                 OpenDataSource ();
1404                                 break;
1405                         case "\\CLOSE":
1406                                 CloseDataSource ();
1407                                 break;
1408                         case "\\S":
1409                                 SetupSilentMode (parms);
1410                                 break;
1411                         case "\\E":
1412                         case "\\EXEQUERY":
1413                         case "\\EXEREADER":
1414                         case "\\EXECUTE":
1415                                 // Execute SQL Commands or Queries
1416                                 if (conn == null)
1417                                         Console.WriteLine ("Error: connection is not Open.");
1418                                 else if (conn.State == ConnectionState.Closed)
1419                                         Console.WriteLine ("Error: connection is not Open.");
1420                                 else {
1421                                         if (build == null)
1422                                                 Console.WriteLine ("Error: SQL Buffer is empty.");
1423                                         else {
1424                                                 buff = build.ToString ();
1425                                                 ExecuteSql (buff);
1426                                         }
1427                                         build = null;
1428                                 }
1429                                 break;
1430                         case "\\EXENONQUERY":
1431                                 if (conn == null)
1432                                         Console.WriteLine ("Error: connection is not Open.");
1433                                 else if (conn.State == ConnectionState.Closed)
1434                                         Console.WriteLine ("Error: connection is not Open.");
1435                                 else {
1436                                         if (build == null)
1437                                                 Console.WriteLine ("Error: SQL Buffer is empty.");
1438                                         else {
1439                                                 buff = build.ToString ();
1440                                                 ExecuteSqlNonQuery (buff);
1441                                         }
1442                                         build = null;
1443                                 }
1444                                 break;
1445                         case "\\EXESCALAR":
1446                                 if (conn == null)
1447                                         Console.WriteLine ("Error: connection is not Open.");
1448                                 else if (conn.State == ConnectionState.Closed)
1449                                         Console.WriteLine ("Error: connection is not Open.");
1450                                 else {
1451                                         if (build == null)
1452                                                 Console.WriteLine ("Error: SQL Buffer is empty.");
1453                                         else {
1454                                                 buff = build.ToString ();
1455                                                 ExecuteSqlScalar (buff);
1456                                         }
1457                                         build = null;
1458                                 }
1459                                 break;
1460                         case "\\EXEXML":
1461                                 // \exexml OUTPUT_FILENAME
1462                                 if (conn == null)
1463                                         Console.WriteLine ("Error: connection is not Open.");
1464                                 else if (conn.State == ConnectionState.Closed)
1465                                         Console.WriteLine ("Error: connection is not Open.");
1466                                 else {
1467                                         if (build == null)
1468                                                 Console.WriteLine ("Error: SQL Buffer is empty.");
1469                                         else {
1470                                                 buff = build.ToString ();
1471                                                 ExecuteSqlXml (buff, parms);
1472                                         }
1473                                         build = null;
1474                                 }
1475                                 break;
1476                         case "\\F":
1477                                 SetupInputCommandsFile (parms);
1478                                 break;
1479                         case "\\O":
1480                                 SetupOutputResultsFile (parms);
1481                                 break;
1482                         case "\\LOAD":
1483                                 // Load file into SQL buffer: \load FILENAME
1484                                 LoadBufferFromFile (parms);
1485                                 break;
1486                         case "\\SAVE":
1487                                 // Save SQL buffer to file: \save FILENAME
1488                                 SaveBufferToFile (parms);
1489                                 break;
1490                         case "\\H":
1491                         case "\\HELP":
1492                                 // Help
1493                                 ShowHelp ();
1494                                 break;
1495                         case "\\DEFAULTS":
1496                                 // show the defaults for provider and connection strings
1497                                 ShowDefaults ();
1498                                 break;
1499                         case "\\BCS":
1500                                 BuildConnectionString ();
1501                                 break;
1502                         case "\\Q":
1503                         case "\\QUIT":
1504                                 // Quit
1505                                 break;
1506                         case "\\CLEAR":
1507                         case "\\RESET":
1508                         case "\\R":
1509                                 // reset (clear) the query buffer
1510                                 build = null;
1511                                 break;
1512                         case "\\SET":
1513                                 // sets internal variable
1514                                 // \set name value
1515                                 SetInternalVariable (parms);
1516                                 break;
1517                         case "\\UNSET":
1518                                 // deletes internal variable
1519                                 // \unset name
1520                                 UnSetInternalVariable (parms);
1521                                 break;
1522                         case "\\VARIABLE":
1523                                 ShowInternalVariable (parms);
1524                                 break;
1525                         case "\\PRINT":
1526                                 if (build == null)
1527                                         Console.WriteLine ("SQL Buffer is empty.");
1528                                 else
1529                                         Console.WriteLine ("SQL Bufer:\n" + buff);
1530                                 break;
1531                         case "\\USEPARAMETERS":
1532                                 SetUseParameters (parms);
1533                                 break;
1534                         case "\\USESIMPLEREADER":
1535                                 SetUseSimpleReader (parms);
1536                                 break;
1537                         default:
1538                                 // Error
1539                                 Console.WriteLine ("Error: Unknown user command.");
1540                                 break;
1541                         }
1542                 }
1543
1544                 public void ListProviders()
1545                 {
1546                         DataTable table = DbProviderFactories.GetFactoryClasses();
1547                         Console.WriteLine("List of Providers:");
1548                         for (int r = 0; r < table.Rows.Count; r++)
1549                         {
1550                                 Console.WriteLine("---------------------");
1551                                 Console.WriteLine("   Name: " + table.Rows[r][0].ToString());
1552                                 Console.WriteLine("      Description: " + table.Rows[r][1].ToString());
1553                                 Console.WriteLine("      InvariantName: " + table.Rows[r][2].ToString());
1554                                 Console.WriteLine("      AssemblyQualifiedName: " + table.Rows[r][3].ToString());
1555                         }
1556                         Console.WriteLine("---------------------");
1557                         Console.WriteLine("Providers found: " + table.Rows.Count.ToString());
1558                 }
1559
1560                 public void DealWithArgs(string[] args)
1561                 {
1562                         for (int a = 0; a < args.Length; a++) {
1563                                 if (args[a].Substring (0,1).Equals ("-")) {
1564                                         string arg = args [a].ToUpper ().Substring (1, args [a].Length - 1);
1565                                         switch (arg) {
1566                                         case "S":
1567                                                 silent = true;
1568                                                 break;
1569                                         case "F":
1570                                                 if (a + 1 >= args.Length)
1571                                                         Console.WriteLine ("Error: Missing FILENAME for -f switch");
1572                                                 else {
1573                                                         inputFilename = args [a + 1];
1574                                                         inputFilestream = new StreamReader (inputFilename);
1575                                                 }
1576                                                 break;
1577                                         case "O":
1578                                                 if (a + 1 >= args.Length)
1579                                                         Console.WriteLine ("Error: Missing FILENAME for -o switch");
1580                                                 else {
1581                                                         outputFilename = args [a + 1];
1582                                                         outputFilestream = new StreamWriter (outputFilename);
1583                                                 }
1584                                                 break;
1585                                         default:
1586                                                 Console.WriteLine ("Error: Unknow switch: " + args [a]);
1587                                                 break;
1588                                         }
1589                                 }
1590                         }
1591                 }
1592
1593                 public string GetPasswordFromConsole ()
1594                 {
1595                         StringBuilder pb = new StringBuilder ();
1596                         Console.Write ("\nPassword: ");
1597                         ConsoleKeyInfo cki = Console.ReadKey (true);
1598
1599                         while (cki.Key != ConsoleKey.Enter) {
1600                                 if (cki.Key == ConsoleKey.Backspace) {
1601                                         if (pb.Length > 0) {
1602                                                 pb.Remove (pb.Length - 1, 1);
1603                                                 Console.Write ("\b");
1604                                                 Console.Write (" ");
1605                                                 Console.Write ("\b");
1606                                         }
1607                                 } else {
1608                                         pb.Append (cki.KeyChar);
1609                                         Console.Write ("*");
1610                                 }
1611                                 cki = Console.ReadKey (true);
1612                         }
1613
1614                         Console.WriteLine ();
1615                         return pb.ToString ();
1616                 }
1617
1618                 public string ReadSqlSharpCommand()
1619                 {
1620                         string entry = "";
1621
1622                         if (inputFilestream == null) {
1623                                 if (silent == false)
1624                                         Console.Error.Write ("\nSQL# ");
1625                                 entry = Console.ReadLine ();
1626                         }
1627                         else {
1628                                 try {
1629                                         entry = inputFilestream.ReadLine ();
1630                                         if (entry == null) {
1631                                                 OutputLine ("Executing SQL# Commands from file done.");
1632                                         }
1633                                 }
1634                                 catch (Exception e) {
1635                                         Console.WriteLine ("Error: Reading command from file: " + e.Message);
1636                                 }
1637                                 if (silent == false)
1638                                         Console.Error.Write ("\nSQL# ");
1639                                 entry = Console.ReadLine ();
1640                         }
1641                         return entry;
1642                 }
1643
1644                 public string ReadConnectionOption(string option, string defaultVal)
1645                 {
1646                         Console.Error.Write ("\nConnectionString Option: {0} [{1}] SQL# ", option, defaultVal);
1647                         return Console.ReadLine ();
1648                 }
1649
1650                 public void BuildConnectionString ()
1651                 {
1652                         if (factory == null) {
1653                                 Console.WriteLine("Provider is not set.");
1654                                 return;
1655                         }
1656
1657                         DbConnectionStringBuilder sb = factory.CreateConnectionStringBuilder ();
1658                         if (!connectionString.Equals(String.Empty))
1659                                 sb.ConnectionString = connectionString;
1660
1661                         bool found = false;
1662                         foreach (string key in sb.Keys) {
1663                                 if (key.ToUpper().Equals("PASSWORD") || key.ToUpper().Equals("PWD")) {
1664                                         string pwd = GetPasswordFromConsole ();
1665                                         try {
1666                                                 sb[key] = pwd;
1667                                         } catch(Exception e) {
1668                                                 Console.Error.WriteLine("Error: unable to set key.  Reason: " + e.Message);
1669                                                 return;
1670                                         }
1671                                 } else {
1672                                         string defaultVal = sb[key].ToString ();
1673                                         String val = "";
1674                                         val = ReadConnectionOption (key, defaultVal);
1675                                         if (val.ToUpper ().Equals ("\\STOP"))
1676                                                 return;
1677                                         if (val != "") {
1678                                                 try {
1679                                                         sb[key] = val;
1680                                                 } catch(Exception e) {
1681                                                         Console.Error.WriteLine("Error: unable to set key.  Reason: " + e.Message);
1682                                                         return;
1683                                                 }
1684                                         }
1685                                 }
1686                                 found = true;
1687                         }
1688                         if (!found) {
1689                                 Console.Error.WriteLine("Warning: your provider does not subclass DbConnectionStringBuilder fully.");
1690                                 return;
1691                         }
1692
1693                         connectionString = sb.ConnectionString;
1694                         Console.WriteLine("ConnectionString is set.");
1695                 }
1696
1697                 public void Run (string[] args)
1698                 {
1699                         DealWithArgs (args);
1700
1701                         string entry = "";
1702                         build = null;
1703
1704                         if (silent == false) {
1705                                 Console.WriteLine ("Welcome to SQL#. The interactive SQL command-line client ");
1706                                 Console.WriteLine ("for Mono.Data.  See http://www.mono-project.com/ for more details.\n");
1707
1708                                 StartupHelp ();
1709                                 ShowDefaults ();
1710                         }
1711
1712                         while (entry.ToUpper ().Equals ("\\Q") == false &&
1713                                 entry.ToUpper ().Equals ("\\QUIT") == false) {
1714
1715                                 while ((entry = ReadSqlSharpCommand ()) == "") {}
1716
1717
1718                                 if (entry.Substring(0,1).Equals ("\\")) {
1719                                         HandleCommand (entry);
1720                                 }
1721                                 else if (entry.IndexOf(";") >= 0) {
1722                                         // most likely the end of SQL Command or Query found
1723                                         // execute the SQL
1724                                         if (conn == null)
1725                                                 Console.WriteLine ("Error: connection is not Open.");
1726                                         else if (conn.State == ConnectionState.Closed)
1727                                                 Console.WriteLine ("Error: connection is not Open.");
1728                                         else {
1729                                                 if (build == null) {
1730                                                         build = new StringBuilder ();
1731                                                 }
1732                                                 build.Append (entry);
1733                                                 //build.Append ("\n");
1734                                                 buff = build.ToString ();
1735                                                 ExecuteSql (buff);
1736                                                 build = null;
1737                                         }
1738                                 }
1739                                 else {
1740                                         // most likely a part of a SQL Command or Query found
1741                                         // append this part of the SQL
1742                                         if (build == null) {
1743                                                 build = new StringBuilder ();
1744                                         }
1745                                         build.Append (entry + "\n");
1746                                         buff = build.ToString ();
1747                                 }
1748                         }
1749                         CloseDataSource ();
1750                         if (outputFilestream != null)
1751                                 outputFilestream.Close ();
1752                 }
1753         }
1754
1755         public enum BindVariableCharacter {
1756                 Colon,         // ':'  - named parameter - :name
1757                 At,            // '@'  - named parameter - @name
1758                 QuestionMark,  // '?'  - positioned parameter - ?
1759                 SquareBrackets // '[]' - delimited named parameter - [name]
1760         }
1761
1762         public class ParametersBuilder
1763         {
1764                 private BindVariableCharacter bindCharSetting;
1765                 private char bindChar;
1766                 private IDataParameterCollection parms;
1767                 private string sql;
1768                 private IDbCommand cmd;
1769
1770                 private void SetBindCharacter ()
1771                 {
1772                         switch(bindCharSetting) {
1773                         case BindVariableCharacter.Colon:
1774                                 bindChar = ':';
1775                                 break;
1776                         case BindVariableCharacter.At:
1777                                 bindChar = '@';
1778                                 break;
1779                         case BindVariableCharacter.SquareBrackets:
1780                                 bindChar = '[';
1781                                 break;
1782                         case BindVariableCharacter.QuestionMark:
1783                                 bindChar = '?';
1784                                 break;
1785                         }
1786                 }
1787
1788                 public ParametersBuilder (IDbCommand command, BindVariableCharacter bindVarChar)
1789                 {
1790                         cmd = command;
1791                         sql = cmd.CommandText;
1792                         parms = cmd.Parameters;
1793                         bindCharSetting = bindVarChar;
1794                         SetBindCharacter();
1795                 }
1796
1797                 public char ParameterMarkerCharacter {
1798                         get {
1799                                 return bindChar;
1800                         }
1801                 }
1802
1803                 public int ParseParameters ()
1804                 {
1805                         int numParms = 0;
1806
1807                         char[] chars = sql.ToCharArray ();
1808                         bool bStringConstFound = false;
1809
1810                         for (int i = 0; i < chars.Length; i++) {
1811                                 if (chars[i] == '\'') {
1812                                         if (bStringConstFound == true)
1813                                                 bStringConstFound = false;
1814                                         else
1815                                                 bStringConstFound = true;
1816                                 }
1817                                 else if (chars[i] == bindChar &&
1818                                         bStringConstFound == false) {
1819                                         if (bindChar != '?') {
1820                                                 StringBuilder parm = new StringBuilder ();
1821                                                 i++;
1822                                                 if (bindChar.Equals ('[')) {
1823                                                         bool endingBracketFound = false;
1824                                                         while (i <= chars.Length) {
1825                                                                 char ch;
1826                                                                 if (i == chars.Length)
1827                                                                         ch = ' '; // a space
1828                                                                 else
1829                                                                         ch = chars[i];
1830
1831                                                                 if (Char.IsLetterOrDigit (ch) || ch == ' ') {
1832                                                                         parm.Append (ch);
1833                                                                 }
1834                                                                 else if (ch == ']') {
1835                                                                         endingBracketFound = true;
1836                                                                         string p = parm.ToString ();
1837                                                                         AddParameter (p);
1838                                                                         numParms ++;
1839                                                                         break;
1840                                                                 }
1841                                                                 else throw new Exception("SQL Parser Error: Invalid character in parameter name");
1842                                                                 i++;
1843                                                         }
1844                                                         i--;
1845                                                         if (endingBracketFound == false)
1846                                                                 throw new Exception("SQL Parser Error: Ending bracket not found for parameter");
1847                                                 }
1848                                                 else {
1849                                                         while (i <= chars.Length) {
1850                                                                 char ch;
1851                                                                 if (i == chars.Length)
1852                                                                         ch = ' '; // a space
1853                                                                 else
1854                                                                         ch = chars[i];
1855
1856                                                                 if (Char.IsLetterOrDigit(ch)) {
1857                                                                         parm.Append (ch);
1858                                                                 }
1859                                                                 else {
1860
1861                                                                         string p = parm.ToString ();
1862                                                                         AddParameter (p);
1863                                                                         numParms ++;
1864                                                                         break;
1865                                                                 }
1866                                                                 i++;
1867                                                         }
1868                                                         i--;
1869                                                 }
1870                                         }
1871                                         else {
1872                                                 // placeholder paramaeter for ?
1873                                                 string p = numParms.ToString ();
1874                                                 AddParameter (p);
1875                                                 numParms ++;
1876                                         }
1877                                 }
1878                         }
1879                         return numParms;
1880                 }
1881
1882                 public void AddParameter (string p)
1883                 {
1884                         Console.WriteLine ("Add Parameter: " + p);
1885                         if (parms.Contains (p) == false) {
1886                                 IDataParameter prm = cmd.CreateParameter ();
1887                                 prm.ParameterName = p;
1888                                 prm.Direction = ParameterDirection.Input;
1889                                 prm.DbType = DbType.String; // default
1890                                 prm.Value = ""; // default
1891                                 cmd.Parameters.Add(prm);
1892                         }
1893                 }
1894
1895         }
1896
1897         public class SqlSharpDriver
1898         {
1899                 public static void Main (string[] args)
1900                 {
1901                         SqlSharpCli sqlCommandLineEngine = new SqlSharpCli ();
1902                         sqlCommandLineEngine.Run (args);
1903                 }
1904         }
1905 }
1906