mcs/tools/SqlSharp/SqlSharpCli.cs

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