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                         string[] extp;
 920
 921                         if (parms.Length == 2) {
 922                                 string parm = parms [1].ToUpper ();
 923                                 switch (parm) {
 924                                 case "ORACLE":
 925                                 case "ORACLECLIENT":
 926                                 case "SYSTEM.DATA.ORACLECLIENT":
 927                                         factoryName = "SYSTEM.DATA.ORACLECLIENT";
 928                                         break;
 929                                 case "SYBASE":
 930                                 case "MONO.DATA.SYBASECLIENT":
 931                                         factoryName = "MONO.DATA.SYBASECLIENT";
 932                                         break;
 933                                 case "BYTEFX":
 934                                 case "MYSQL":
 935                                 case "MYSQL.DATA.MYSQLCLIENT":
 936                                         factoryName = "MYSQL.DATA.MYSQLCLIENT";
 937                                         break;
 938                                 case "SQLITE":
 939                                 case "MONO.DATA.SQLITE":
 940                                         factoryName = "MONO.DATA.SQLITE";
 941                                         break;
 942                                 case "ODBC":
 943                                 case "SYSTEM.DATA.ODBC":
 944                                         factoryName = "SYSTEM.DATA.ODBC";
 945                                         break;
 946                                 case "OLEDB":
 947                                 case "SYSTEM.DATA.OLEDB":
 948                                         factoryName = "SYSTEM.DATA.OLEDB";
 949                                         break;
 950                                 case "FIREBIRD":
 951                                 case "FIREBIRDSQL.DATA.FIREBIRD":
 952                                         factoryName = "FIREBIRDSQL.DATA.FIREBIRD";
 953                                         break;
 954                                 case "POSTGRESQL":
 955                                 case "NPGSQL":
 956                                 case "NPGSQL.DATA":
 957                                         factoryName = "NPGSQL.DATA";
 958                                         break;
 959                                 case "SQLCLIENT":
 960                                 case "SYSTEM.DATA.SQLCLIENT":
 961                                         factoryName = "SYSTEM.DATA.SQLCLIENT";
 962                                         break;
 963                                 default:
 964                                         Console.WriteLine ("Error: " + "Bad argument or Provider not supported.");
 965                                         return;
 966                                 }
 967                                 try {
 968                                         factory = DbProviderFactories.GetFactory(factoryName);
 969                                 } catch(ConfigurationException e) {
 970                                         Console.Error.WriteLine("*** Error: Unable to load provider factory: " +
 971                                                 factoryName + "\n" +
 972                                                 "*** Check your machine.config to see if the provider is " +
 973                                                 "listed under section system.data and DbProviderFactories " +
 974                                                 "and that your provider assembly is in the GAC.  Your provider " +
 975                                                 "may not support ADO.NET 2.0 factory and other features yet.");
 976                                         factoryName = null;
 977                                         ChangeProviderBackwardsCompat (parms);
 978                                         return;
 979                                 }
 980                                 OutputLine ("The default Provider is " + factoryName);
 981                         }
 982                         else
 983                                 Console.WriteLine ("Error: provider only has one parameter.");
 984                 }
 985
 986                 public void ChangeProviderBackwardsCompat (string[] parms)
 987                 {
 988                         Console.Error.WriteLine ("*** Setting provider using Backwards Compatibility mode.");
 989
 990                         string[] extp;
 991
 992                         if (parms.Length == 2) {
 993                                 string parm = parms [1].ToUpper ();
 994                                 switch (parm) {
 995                                 case "ORACLE":
 996                                         extp = new string[3] {
 997                                                                      "\\loadextprovider",
 998                                                                      @"System.Data.OracleClient, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089",
 999                                                                      "System.Data.OracleClient.OracleConnection"};
1000                                         SetupExternalProvider (extp);
1001                                         UseParameters = false;
1002                                         UseSimpleReader = false;
1003                                         break;
1004                                 case "TDS":
1005                                         extp = new string[3] {
1006                                                                      "\\loadextprovider",
1007                                                                      @"Mono.Data.TdsClient, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=0738eb9f132ed756",
1008                                                                      "Mono.Data.TdsClient.TdsConnection"};
1009                                         SetupExternalProvider (extp);
1010                                         UseParameters = false;
1011                                         UseSimpleReader = false;
1012                                         break;
1013                                 case "SYBASE":
1014                                         extp = new string[3] {
1015                                                                      "\\loadextprovider",
1016                                                                      @"Mono.Data.SybaseClient, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=0738eb9f132ed756",
1017                                                                      "Mono.Data.SybaseClient.SybaseConnection"};
1018                                         SetupExternalProvider (extp);
1019                                         UseParameters = false;
1020                                         UseSimpleReader = false;
1021                                         break;
1022                                 case "BYTEFX":
1023                                         extp = new string[3] {
1024                                                                      "\\loadextprovider",
1025                                                                      @"ByteFX.Data, Version=0.7.6.1, Culture=neutral, PublicKeyToken=0738eb9f132ed756",
1026                                                                      "ByteFX.Data.MySqlClient.MySqlConnection"};
1027                                         SetupExternalProvider (extp);
1028                                         UseParameters = false;
1029                                         UseSimpleReader = false;
1030                                         break;
1031                                 case "MYSQL":
1032                                 case "MYSQLNET":
1033                                         extp = new string[3] {
1034                                                                      "\\loadextprovider",
1035                                                                      @"MySql.Data, Version=1.0.7.30073, Culture=neutral, PublicKeyToken=8e323390df8d9ed4",
1036                                                                      "MySql.Data.MySqlClient.MySqlConnection"};
1037                                         SetupExternalProvider (extp);
1038                                         UseParameters = false;
1039                                         UseSimpleReader = false;
1040                                         break;
1041                                 case "SQLITE":
1042                                         extp = new string[3] {
1043                                                                      "\\loadextprovider",
1044                                                                      @"Mono.Data.SqliteClient, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=0738eb9f132ed756",
1045                                                                      "Mono.Data.SqliteClient.SqliteConnection"};
1046                                         SetupExternalProvider (extp);
1047                                         UseParameters = false;
1048                                         UseSimpleReader = true;
1049                                         break;
1050                                 case "SQLCLIENT":
1051                                         UseParameters = false;
1052                                         UseSimpleReader = false;
1053                                         provider = parm;
1054                                         break;
1055                                 case "ODBC": // for MS NET 1.1 and above
1056                                         extp = new string[3] {
1057                                                                      "\\loadextprovider",
1058                                                                      @"System.Data, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089",
1059                                                                      "System.Data.Odbc.OdbcConnection"};
1060                                         SetupExternalProvider (extp);
1061                                         UseParameters = false;
1062                                         UseSimpleReader = false;
1063                                         break;
1064                                 case "MSODBC": // for MS NET 1.0
1065                                         extp = new string[3] {
1066                                                                      "\\loadextprovider",
1067                                                                      @"Microsoft.Data.Odbc, Culture=neutral, PublicKeyToken=b77a5c561934e089, Version=1.0.3300.0",
1068                                                                      "Microsoft.Data.Odbc.OdbcConnection"};
1069                                         SetupExternalProvider (extp);
1070                                         UseParameters = false;
1071                                         UseSimpleReader = false;
1072                                         break;
1073                                 case "OLEDB":
1074                                         UseParameters = false;
1075                                         UseSimpleReader = true;
1076                                         provider = parm;
1077                                         break;
1078                                 case "FIREBIRD":
1079                                         extp = new string[3] {
1080                                                                      "\\loadextprovider",
1081                                                                      @"FirebirdSql.Data.Firebird, Version=1.7.1.0, Culture=neutral, PublicKeyToken=0706f5520aae4ff4",
1082                                                                      "FirebirdSql.Data.Firebird.FbConnection"};
1083                                         SetupExternalProvider (extp);
1084                                         UseParameters = false;
1085                                         UseSimpleReader = false;
1086                                         break;
1087                                 case "POSTGRESQL":
1088                                 case "NPGSQL":
1089                                         extp = new string[3] {
1090                                                                      "\\loadextprovider",
1091                                                                      @"Npgsql, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=5d8b90d52f46fda7",
1092                                                                      "Npgsql.NpgsqlConnection"};
1093                                         SetupExternalProvider (extp);
1094                                         UseParameters = false;
1095                                         UseSimpleReader = false;
1096                                         break;
1097                                 default:
1098                                         Console.WriteLine ("Error: " + "Bad argument or Provider not supported.");
1099                                         break;
1100                                 }
1101                                 OutputLine ("The default Provider is " + provider);
1102                                 if (provider.Equals ("LOADEXTPROVIDER")) {
1103                                         OutputLine ("          Assembly: " +
1104                                                 providerAssembly);
1105                                         OutputLine ("  Connection Class: " +
1106                                                 providerConnectionClass);
1107                                 }
1108                         }
1109                         else
1110                                 Console.WriteLine ("Error: provider only has one parameter.");
1111                 }
1112
1113                 // ChangeConnectionString - change the connection string variable
1114                 public void ChangeConnectionString (string[] parms, string entry)
1115                 {
1116                         if (parms.Length >= 2)
1117                                 connectionString = entry.Substring (parms[0].Length, entry.Length - (parms[0].Length + 1));
1118                         else
1119                                 connectionString = "";
1120                 }
1121
1122                 public void SetupOutputResultsFile (string[] parms) {
1123                         if (parms.Length != 2) {
1124                                 Console.WriteLine ("Error: wrong number of parameters");
1125                                 return;
1126                         }
1127                         try {
1128                                 outputFilestream = new StreamWriter (parms[1]);
1129                         }
1130                         catch (Exception e) {
1131                                 Console.WriteLine ("Error: Unable to setup output results file. " + e.Message);
1132                                 return;
1133                         }
1134                 }
1135
1136                 public void SetupInputCommandsFile (string[] parms)
1137                 {
1138                         if (parms.Length != 2) {
1139                                 Console.WriteLine ("Error: wrong number of parameters");
1140                                 return;
1141                         }
1142                         try {
1143                                 inputFilestream = new StreamReader (parms[1]);
1144                         }
1145                         catch (Exception e) {
1146                                 Console.WriteLine ("Error: Unable to setup input commmands file. " + e.Message);
1147                                 return;
1148                         }
1149                 }
1150
1151                 public void LoadBufferFromFile (string[] parms)
1152                 {
1153                         if (parms.Length != 2) {
1154                                 Console.WriteLine ("Error: wrong number of parameters");
1155                                 return;
1156                         }
1157                         string inFilename = parms[1];
1158                         try {
1159                                 StreamReader sr = new StreamReader (inFilename);
1160                                 StringBuilder buffer = new StringBuilder ();
1161                                 string NextLine;
1162
1163                                 while ((NextLine = sr.ReadLine ()) != null) {
1164                                         buffer.Append (NextLine);
1165                                         buffer.Append ("\n");
1166                                 }
1167                                 sr.Close ();
1168                                 buff = buffer.ToString ();
1169                                 build = null;
1170                                 build = new StringBuilder ();
1171                                 build.Append(buff);
1172                         }
1173                         catch (Exception e) {
1174                                 Console.WriteLine ("Error: Unable to read file into SQL Buffer. " + e.Message);
1175                         }
1176                 }
1177
1178                 public void SaveBufferToFile(string[] parms)
1179                 {
1180                         if (parms.Length != 2) {
1181                                 Console.WriteLine ("Error: wrong number of parameters");
1182                                 return;
1183                         }
1184                         string outFilename = parms[1];
1185                         try {
1186                                 StreamWriter sw = new StreamWriter (outFilename);
1187                                 sw.WriteLine (buff);
1188                                 sw.Close ();
1189                         }
1190                         catch (Exception e) {
1191                                 Console.WriteLine ("Error: Could not save SQL Buffer to file." + e.Message);
1192                         }
1193                 }
1194
1195                 public void SetUseParameters (string[] parms)
1196                 {
1197                         if (parms.Length != 2) {
1198                                 Console.WriteLine ("Error: wrong number of parameters");
1199                                 return;
1200                         }
1201                         string parm = parms[1].ToUpper ();
1202                         if (parm.Equals ("TRUE"))
1203                                 UseParameters = true;
1204                         else if (parm.Equals ("FALSE"))
1205                                 UseParameters = false;
1206                         else
1207                                 Console.WriteLine ("Error: invalid parameter.");
1208
1209                 }
1210
1211                 public void SetUseSimpleReader (string[] parms)
1212                 {
1213                         if (parms.Length != 2) {
1214                                 Console.WriteLine ("Error: wrong number of parameters");
1215                                 return;
1216                         }
1217                         string parm = parms[1].ToUpper ();
1218                         if (parm.Equals ("TRUE"))
1219                                 UseSimpleReader = true;
1220                         else if (parm.Equals ("FALSE"))
1221                                 UseSimpleReader = false;
1222                         else
1223                                 Console.WriteLine ("Error: invalid parameter.");
1224                 }
1225
1226                 public void SetupSilentMode (string[] parms)
1227                 {
1228                         if (parms.Length != 2) {
1229                                 Console.WriteLine ("Error: wrong number of parameters");
1230                                 return;
1231                         }
1232                         string parm = parms[1].ToUpper ();
1233                         if (parm.Equals ("TRUE"))
1234                                 silent = true;
1235                         else if (parm.Equals ("FALSE"))
1236                                 silent = false;
1237                         else
1238                                 Console.WriteLine ("Error: invalid parameter.");
1239                 }
1240
1241                 public void SetInternalVariable(string[] parms)
1242                 {
1243                         if (parms.Length < 2) {
1244                                 Console.WriteLine ("Error: wrong number of parameters.");
1245                                 return;
1246                         }
1247                         string parm = parms[1];
1248                         StringBuilder ps = new StringBuilder ();
1249
1250                         for (int i = 2; i < parms.Length; i++)
1251                                 ps.Append (parms[i]);
1252
1253                         internalVariables[parm] = ps.ToString ();
1254                 }
1255
1256                 public void UnSetInternalVariable(string[] parms)
1257                 {
1258                         if (parms.Length != 2) {
1259                                 Console.WriteLine ("Error: wrong number of parameters.");
1260                                 return;
1261                         }
1262                         string parm = parms[1];
1263
1264                         try {
1265                                 internalVariables.Remove (parm);
1266                         } catch(Exception e) {
1267                                 Console.WriteLine ("Error: internal variable does not exist: " + e.Message);
1268                         }
1269                 }
1270
1271                 public void ShowInternalVariable(string[] parms)
1272                 {
1273                         string internalVariableValue = "";
1274
1275                         if (parms.Length != 2) {
1276                                 Console.WriteLine ("Error: wrong number of parameters.");
1277                                 return;
1278                         }
1279
1280                         string parm = parms[1];
1281
1282                         if (GetInternalVariable(parm, out internalVariableValue) == true)
1283                                 Console.WriteLine ("Internal Variable - Name: " +
1284                                         parm + "  Value: " + internalVariableValue);
1285                 }
1286
1287                 public bool GetInternalVariable(string name, out string sValue)
1288                 {
1289                         sValue = "";
1290                         bool valueReturned = false;
1291
1292                         try {
1293                                 if (internalVariables.ContainsKey (name) == true) {
1294                                         sValue = (string) internalVariables[name];
1295                                         valueReturned = true;
1296                                 }
1297                                 else
1298                                         Console.WriteLine ("Error: internal variable does not exist.");
1299
1300                         }
1301                         catch(Exception e) {
1302                                 Console.WriteLine ("Error: internal variable does not exist: "+ e.Message);
1303                         }
1304                         return valueReturned;
1305                 }
1306
1307                 public void SetupExternalProvider(string[] parms)
1308                 {
1309                         if (parms.Length != 3) {
1310                                 Console.WriteLine ("Error: Wrong number of parameters.");
1311                                 return;
1312                         }
1313                         provider = "LOADEXTPROVIDER";
1314                         providerAssembly = parms[1];
1315                         providerConnectionClass = parms[2];
1316                 }
1317
1318                 public bool LoadExternalProvider ()
1319                 {
1320                         string msg = "";
1321
1322                         bool success = false;
1323
1324                         // For example: for the MySQL provider in Mono.Data.MySql
1325                         //   \LoadExtProvider Mono.Data.MySql Mono.Data.MySql.MySqlConnection
1326                         //   \ConnectionString dbname=test
1327                         //   \open
1328                         //   insert into sometable (tid, tdesc, aint) values ('abc','def',12)
1329                         //   \exenonquery
1330                         //   \close
1331                         //   \quit
1332
1333                         try {
1334                                 OutputLine ("Loading external provider...");
1335
1336                                 Assembly ps = Assembly.Load (providerAssembly);
1337                                 conType = ps.GetType (providerConnectionClass);
1338                                 conn = (IDbConnection) Activator.CreateInstance (conType);
1339                                 success = true;
1340
1341                                 OutputLine ("External provider loaded.");
1342                                 UseParameters = false;
1343                         } catch(FileNotFoundException f) {
1344                                 msg = "Error: unable to load the assembly of the provider: " + providerAssembly + " : " + f.Message;
1345                                 Console.WriteLine(msg);
1346                         }
1347                         catch(Exception e) {
1348                                 msg = "Error: unable to load the assembly of the provider: " + providerAssembly + " : " + e.Message;
1349                                 Console.WriteLine(msg);
1350                         }
1351                         return success;
1352                 }
1353
1354                 // used for outputting message, but if silent is set,
1355                 // don't display
1356                 public void OutputLine (string line)
1357                 {
1358                         if (silent == false)
1359                                 OutputData (line);
1360                 }
1361
1362                 // used for outputting the header columns of a result
1363                 public void OutputHeader (string line)
1364                 {
1365                         if (showHeader == true)
1366                                 OutputData (line);
1367                 }
1368
1369                 // OutputData() - used for outputting data
1370                 //  if an output filename is set, then the data will
1371                 //  go to a file; otherwise, it will go to the Console.
1372                 public void OutputData(string line)
1373                 {
1374                         if (outputFilestream == null)
1375                                 Console.WriteLine (line);
1376                         else
1377                                 outputFilestream.WriteLine (line);
1378                 }
1379
1380                 // HandleCommand - handle SqlSharpCli commands entered
1381                 public void HandleCommand (string entry)
1382                 {
1383                         string[] parms;
1384
1385                         parms = entry.Split (new char[1] {' '});
1386                         string userCmd = parms[0].ToUpper ();
1387
1388                         switch (userCmd) {
1389                         case "\\LISTPROVIDERS":
1390                         case "\\LISTP":
1391                                 ListProviders ();
1392                                 break;
1393                         case "\\PROVIDER":
1394                         case "\\P":
1395                                 ChangeProvider (parms);
1396                                 break;
1397                         case "\\CONNECTIONSTRING":
1398                         case "\\CS":
1399                                 ChangeConnectionString (parms, entry);
1400                                 break;
1401                         case "\\LOADEXTPROVIDER":
1402                                 SetupExternalProvider (parms);
1403                                 break;
1404                         case "\\OPEN":
1405                                 OpenDataSource ();
1406                                 break;
1407                         case "\\CLOSE":
1408                                 CloseDataSource ();
1409                                 break;
1410                         case "\\S":
1411                                 SetupSilentMode (parms);
1412                                 break;
1413                         case "\\E":
1414                         case "\\EXEQUERY":
1415                         case "\\EXEREADER":
1416                         case "\\EXECUTE":
1417                                 // Execute SQL Commands or Queries
1418                                 if (conn == null)
1419                                         Console.WriteLine ("Error: connection is not Open.");
1420                                 else if (conn.State == ConnectionState.Closed)
1421                                         Console.WriteLine ("Error: connection is not Open.");
1422                                 else {
1423                                         if (build == null)
1424                                                 Console.WriteLine ("Error: SQL Buffer is empty.");
1425                                         else {
1426                                                 buff = build.ToString ();
1427                                                 ExecuteSql (buff);
1428                                         }
1429                                         build = null;
1430                                 }
1431                                 break;
1432                         case "\\EXENONQUERY":
1433                                 if (conn == null)
1434                                         Console.WriteLine ("Error: connection is not Open.");
1435                                 else if (conn.State == ConnectionState.Closed)
1436                                         Console.WriteLine ("Error: connection is not Open.");
1437                                 else {
1438                                         if (build == null)
1439                                                 Console.WriteLine ("Error: SQL Buffer is empty.");
1440                                         else {
1441                                                 buff = build.ToString ();
1442                                                 ExecuteSqlNonQuery (buff);
1443                                         }
1444                                         build = null;
1445                                 }
1446                                 break;
1447                         case "\\EXESCALAR":
1448                                 if (conn == null)
1449                                         Console.WriteLine ("Error: connection is not Open.");
1450                                 else if (conn.State == ConnectionState.Closed)
1451                                         Console.WriteLine ("Error: connection is not Open.");
1452                                 else {
1453                                         if (build == null)
1454                                                 Console.WriteLine ("Error: SQL Buffer is empty.");
1455                                         else {
1456                                                 buff = build.ToString ();
1457                                                 ExecuteSqlScalar (buff);
1458                                         }
1459                                         build = null;
1460                                 }
1461                                 break;
1462                         case "\\EXEXML":
1463                                 // \exexml OUTPUT_FILENAME
1464                                 if (conn == null)
1465                                         Console.WriteLine ("Error: connection is not Open.");
1466                                 else if (conn.State == ConnectionState.Closed)
1467                                         Console.WriteLine ("Error: connection is not Open.");
1468                                 else {
1469                                         if (build == null)
1470                                                 Console.WriteLine ("Error: SQL Buffer is empty.");
1471                                         else {
1472                                                 buff = build.ToString ();
1473                                                 ExecuteSqlXml (buff, parms);
1474                                         }
1475                                         build = null;
1476                                 }
1477                                 break;
1478                         case "\\F":
1479                                 SetupInputCommandsFile (parms);
1480                                 break;
1481                         case "\\O":
1482                                 SetupOutputResultsFile (parms);
1483                                 break;
1484                         case "\\LOAD":
1485                                 // Load file into SQL buffer: \load FILENAME
1486                                 LoadBufferFromFile (parms);
1487                                 break;
1488                         case "\\SAVE":
1489                                 // Save SQL buffer to file: \save FILENAME
1490                                 SaveBufferToFile (parms);
1491                                 break;
1492                         case "\\H":
1493                         case "\\HELP":
1494                                 // Help
1495                                 ShowHelp ();
1496                                 break;
1497                         case "\\DEFAULTS":
1498                                 // show the defaults for provider and connection strings
1499                                 ShowDefaults ();
1500                                 break;
1501                         case "\\BCS":
1502                                 BuildConnectionString ();
1503                                 break;
1504                         case "\\Q":
1505                         case "\\QUIT":
1506                                 // Quit
1507                                 break;
1508                         case "\\CLEAR":
1509                         case "\\RESET":
1510                         case "\\R":
1511                                 // reset (clear) the query buffer
1512                                 build = null;
1513                                 break;
1514                         case "\\SET":
1515                                 // sets internal variable
1516                                 // \set name value
1517                                 SetInternalVariable (parms);
1518                                 break;
1519                         case "\\UNSET":
1520                                 // deletes internal variable
1521                                 // \unset name
1522                                 UnSetInternalVariable (parms);
1523                                 break;
1524                         case "\\VARIABLE":
1525                                 ShowInternalVariable (parms);
1526                                 break;
1527                         case "\\PRINT":
1528                                 if (build == null)
1529                                         Console.WriteLine ("SQL Buffer is empty.");
1530                                 else
1531                                         Console.WriteLine ("SQL Bufer:\n" + buff);
1532                                 break;
1533                         case "\\USEPARAMETERS":
1534                                 SetUseParameters (parms);
1535                                 break;
1536                         case "\\USESIMPLEREADER":
1537                                 SetUseSimpleReader (parms);
1538                                 break;
1539                         default:
1540                                 // Error
1541                                 Console.WriteLine ("Error: Unknown user command.");
1542                                 break;
1543                         }
1544                 }
1545
1546                 public void ListProviders()
1547                 {
1548                         DataTable table = DbProviderFactories.GetFactoryClasses();
1549                         Console.WriteLine("List of Providers:");
1550                         for (int r = 0; r < table.Rows.Count; r++)
1551                         {
1552                                 Console.WriteLine("---------------------");
1553                                 Console.WriteLine("   Name: " + table.Rows[r][0].ToString());
1554                                 Console.WriteLine("      Description: " + table.Rows[r][1].ToString());
1555                                 Console.WriteLine("      InvariantName: " + table.Rows[r][2].ToString());
1556                                 Console.WriteLine("      AssemblyQualifiedName: " + table.Rows[r][3].ToString());
1557                         }
1558                         Console.WriteLine("---------------------");
1559                         Console.WriteLine("Providers found: " + table.Rows.Count.ToString());
1560                 }
1561
1562                 public void DealWithArgs(string[] args)
1563                 {
1564                         for (int a = 0; a < args.Length; a++) {
1565                                 if (args[a].Substring (0,1).Equals ("-")) {
1566                                         string arg = args [a].ToUpper ().Substring (1, args [a].Length - 1);
1567                                         switch (arg) {
1568                                         case "S":
1569                                                 silent = true;
1570                                                 break;
1571                                         case "F":
1572                                                 if (a + 1 >= args.Length)
1573                                                         Console.WriteLine ("Error: Missing FILENAME for -f switch");
1574                                                 else {
1575                                                         inputFilename = args [a + 1];
1576                                                         inputFilestream = new StreamReader (inputFilename);
1577                                                 }
1578                                                 break;
1579                                         case "O":
1580                                                 if (a + 1 >= args.Length)
1581                                                         Console.WriteLine ("Error: Missing FILENAME for -o switch");
1582                                                 else {
1583                                                         outputFilename = args [a + 1];
1584                                                         outputFilestream = new StreamWriter (outputFilename);
1585                                                 }
1586                                                 break;
1587                                         default:
1588                                                 Console.WriteLine ("Error: Unknow switch: " + args [a]);
1589                                                 break;
1590                                         }
1591                                 }
1592                         }
1593                 }
1594
1595                 public string GetPasswordFromConsole ()
1596                 {
1597                         StringBuilder pb = new StringBuilder ();
1598                         Console.Write ("\nPassword: ");
1599                         ConsoleKeyInfo cki = Console.ReadKey (true);
1600
1601                         while (cki.Key != ConsoleKey.Enter) {
1602                                 if (cki.Key == ConsoleKey.Backspace) {
1603                                         if (pb.Length > 0) {
1604                                                 pb.Remove (pb.Length - 1, 1);
1605                                                 Console.Write ("\b");
1606                                                 Console.Write (" ");
1607                                                 Console.Write ("\b");
1608                                         }
1609                                 } else {
1610                                         pb.Append (cki.KeyChar);
1611                                         Console.Write ("*");
1612                                 }
1613                                 cki = Console.ReadKey (true);
1614                         }
1615
1616                         Console.WriteLine ();
1617                         return pb.ToString ();
1618                 }
1619
1620                 public string ReadSqlSharpCommand()
1621                 {
1622                         string entry = "";
1623
1624                         if (inputFilestream == null) {
1625                                 if (silent == false)
1626                                         Console.Error.Write ("\nSQL# ");
1627                                 entry = Console.ReadLine ();
1628                         }
1629                         else {
1630                                 try {
1631                                         entry = inputFilestream.ReadLine ();
1632                                         if (entry == null) {
1633                                                 OutputLine ("Executing SQL# Commands from file done.");
1634                                         }
1635                                 }
1636                                 catch (Exception e) {
1637                                         Console.WriteLine ("Error: Reading command from file: " + e.Message);
1638                                 }
1639                                 if (silent == false)
1640                                         Console.Error.Write ("\nSQL# ");
1641                                 entry = Console.ReadLine ();
1642                         }
1643                         return entry;
1644                 }
1645
1646                 public string ReadConnectionOption(string option, string defaultVal)
1647                 {
1648                         Console.Error.Write ("\nConnectionString Option: {0} [{1}] SQL# ", option, defaultVal);
1649                         return Console.ReadLine ();
1650                 }
1651
1652                 public void BuildConnectionString ()
1653                 {
1654                         if (factory == null) {
1655                                 Console.WriteLine("Provider is not set.");
1656                                 return;
1657                         }
1658
1659                         DbConnectionStringBuilder sb = factory.CreateConnectionStringBuilder ();
1660                         if (!connectionString.Equals(String.Empty))
1661                                 sb.ConnectionString = connectionString;
1662
1663                         bool found = false;
1664                         foreach (string key in sb.Keys) {
1665                                 if (key.ToUpper().Equals("PASSWORD") || key.ToUpper().Equals("PWD")) {
1666                                         string pwd = GetPasswordFromConsole ();
1667                                         try {
1668                                                 sb[key] = pwd;
1669                                         } catch(Exception e) {
1670                                                 Console.Error.WriteLine("Error: unable to set key.  Reason: " + e.Message);
1671                                                 return;
1672                                         }
1673                                 } else {
1674                                         string defaultVal = sb[key].ToString ();
1675                                         String val = "";
1676                                         val = ReadConnectionOption (key, defaultVal);
1677                                         if (val.ToUpper ().Equals ("\\STOP"))
1678                                                 return;
1679                                         if (val != "") {
1680                                                 try {
1681                                                         sb[key] = val;
1682                                                 } catch(Exception e) {
1683                                                         Console.Error.WriteLine("Error: unable to set key.  Reason: " + e.Message);
1684                                                         return;
1685                                                 }
1686                                         }
1687                                 }
1688                                 found = true;
1689                         }
1690                         if (!found) {
1691                                 Console.Error.WriteLine("Warning: your provider does not subclass DbConnectionStringBuilder fully.");
1692                                 return;
1693                         }
1694
1695                         connectionString = sb.ConnectionString;
1696                         Console.WriteLine("ConnectionString is set.");
1697                 }
1698
1699                 public void Run (string[] args)
1700                 {
1701                         DealWithArgs (args);
1702
1703                         string entry = "";
1704                         build = null;
1705
1706                         if (silent == false) {
1707                                 Console.WriteLine ("Welcome to SQL#. The interactive SQL command-line client ");
1708                                 Console.WriteLine ("for Mono.Data.  See http://www.mono-project.com/ for more details.\n");
1709
1710                                 StartupHelp ();
1711                                 ShowDefaults ();
1712                         }
1713
1714                         while (entry.ToUpper ().Equals ("\\Q") == false &&
1715                                 entry.ToUpper ().Equals ("\\QUIT") == false) {
1716
1717                                 while ((entry = ReadSqlSharpCommand ()) == "") {}
1718
1719
1720                                 if (entry.Substring(0,1).Equals ("\\")) {
1721                                         HandleCommand (entry);
1722                                 }
1723                                 else if (entry.IndexOf(";") >= 0) {
1724                                         // most likely the end of SQL Command or Query found
1725                                         // execute the SQL
1726                                         if (conn == null)
1727                                                 Console.WriteLine ("Error: connection is not Open.");
1728                                         else if (conn.State == ConnectionState.Closed)
1729                                                 Console.WriteLine ("Error: connection is not Open.");
1730                                         else {
1731                                                 if (build == null) {
1732                                                         build = new StringBuilder ();
1733                                                 }
1734                                                 build.Append (entry);
1735                                                 //build.Append ("\n");
1736                                                 buff = build.ToString ();
1737                                                 ExecuteSql (buff);
1738                                                 build = null;
1739                                         }
1740                                 }
1741                                 else {
1742                                         // most likely a part of a SQL Command or Query found
1743                                         // append this part of the SQL
1744                                         if (build == null) {
1745                                                 build = new StringBuilder ();
1746                                         }
1747                                         build.Append (entry + "\n");
1748                                         buff = build.ToString ();
1749                                 }
1750                         }
1751                         CloseDataSource ();
1752                         if (outputFilestream != null)
1753                                 outputFilestream.Close ();
1754                 }
1755         }
1756
1757         public enum BindVariableCharacter {
1758                 Colon,         // ':'  - named parameter - :name
1759                 At,            // '@'  - named parameter - @name
1760                 QuestionMark,  // '?'  - positioned parameter - ?
1761                 SquareBrackets // '[]' - delimited named parameter - [name]
1762         }
1763
1764         public class ParametersBuilder
1765         {
1766                 private BindVariableCharacter bindCharSetting;
1767                 private char bindChar;
1768                 private IDataParameterCollection parms;
1769                 private string sql;
1770                 private IDbCommand cmd;
1771
1772                 private void SetBindCharacter ()
1773                 {
1774                         switch(bindCharSetting) {
1775                         case BindVariableCharacter.Colon:
1776                                 bindChar = ':';
1777                                 break;
1778                         case BindVariableCharacter.At:
1779                                 bindChar = '@';
1780                                 break;
1781                         case BindVariableCharacter.SquareBrackets:
1782                                 bindChar = '[';
1783                                 break;
1784                         case BindVariableCharacter.QuestionMark:
1785                                 bindChar = '?';
1786                                 break;
1787                         }
1788                 }
1789
1790                 public ParametersBuilder (IDbCommand command, BindVariableCharacter bindVarChar)
1791                 {
1792                         cmd = command;
1793                         sql = cmd.CommandText;
1794                         parms = cmd.Parameters;
1795                         bindCharSetting = bindVarChar;
1796                         SetBindCharacter();
1797                 }
1798
1799                 public char ParameterMarkerCharacter {
1800                         get {
1801                                 return bindChar;
1802                         }
1803                 }
1804
1805                 public int ParseParameters ()
1806                 {
1807                         int numParms = 0;
1808
1809                         char[] chars = sql.ToCharArray ();
1810                         bool bStringConstFound = false;
1811
1812                         for (int i = 0; i < chars.Length; i++) {
1813                                 if (chars[i] == '\'') {
1814                                         if (bStringConstFound == true)
1815                                                 bStringConstFound = false;
1816                                         else
1817                                                 bStringConstFound = true;
1818                                 }
1819                                 else if (chars[i] == bindChar &&
1820                                         bStringConstFound == false) {
1821                                         if (bindChar != '?') {
1822                                                 StringBuilder parm = new StringBuilder ();
1823                                                 i++;
1824                                                 if (bindChar.Equals ('[')) {
1825                                                         bool endingBracketFound = false;
1826                                                         while (i <= chars.Length) {
1827                                                                 char ch;
1828                                                                 if (i == chars.Length)
1829                                                                         ch = ' '; // a space
1830                                                                 else
1831                                                                         ch = chars[i];
1832
1833                                                                 if (Char.IsLetterOrDigit (ch) || ch == ' ') {
1834                                                                         parm.Append (ch);
1835                                                                 }
1836                                                                 else if (ch == ']') {
1837                                                                         endingBracketFound = true;
1838                                                                         string p = parm.ToString ();
1839                                                                         AddParameter (p);
1840                                                                         numParms ++;
1841                                                                         break;
1842                                                                 }
1843                                                                 else throw new Exception("SQL Parser Error: Invalid character in parameter name");
1844                                                                 i++;
1845                                                         }
1846                                                         i--;
1847                                                         if (endingBracketFound == false)
1848                                                                 throw new Exception("SQL Parser Error: Ending bracket not found for parameter");
1849                                                 }
1850                                                 else {
1851                                                         while (i <= chars.Length) {
1852                                                                 char ch;
1853                                                                 if (i == chars.Length)
1854                                                                         ch = ' '; // a space
1855                                                                 else
1856                                                                         ch = chars[i];
1857
1858                                                                 if (Char.IsLetterOrDigit(ch)) {
1859                                                                         parm.Append (ch);
1860                                                                 }
1861                                                                 else {
1862
1863                                                                         string p = parm.ToString ();
1864                                                                         AddParameter (p);
1865                                                                         numParms ++;
1866                                                                         break;
1867                                                                 }
1868                                                                 i++;
1869                                                         }
1870                                                         i--;
1871                                                 }
1872                                         }
1873                                         else {
1874                                                 // placeholder paramaeter for ?
1875                                                 string p = numParms.ToString ();
1876                                                 AddParameter (p);
1877                                                 numParms ++;
1878                                         }
1879                                 }
1880                         }
1881                         return numParms;
1882                 }
1883
1884                 public void AddParameter (string p)
1885                 {
1886                         Console.WriteLine ("Add Parameter: " + p);
1887                         if (parms.Contains (p) == false) {
1888                                 IDataParameter prm = cmd.CreateParameter ();
1889                                 prm.ParameterName = p;
1890                                 prm.Direction = ParameterDirection.Input;
1891                                 prm.DbType = DbType.String; // default
1892                                 prm.Value = ""; // default
1893                                 cmd.Parameters.Add(prm);
1894                         }
1895                 }
1896
1897         }
1898
1899         public class SqlSharpDriver
1900         {
1901                 public static void Main (string[] args)
1902                 {
1903                         SqlSharpCli sqlCommandLineEngine = new SqlSharpCli ();
1904                         sqlCommandLineEngine.Run (args);
1905                 }
1906         }
1907 }
1908