mcs/class/System.Data/System.Data.SqlClient/SqlConnection.cs

   1 //
   2 // System.Data.SqlClient.SqlConnection.cs
   3 //
   4 // Authors:
   5 //   Rodrigo Moya (rodrigo@ximian.com)
   6 //   Daniel Morgan (danmorg@sc.rr.com)
   7 //   Tim Coleman (tim@timcoleman.com)
   8 //   Phillip Jerkins (Phillip.Jerkins@morgankeegan.com)
   9 //   Diego Caravana (diego@toth.it)
  10 //
  11 // Copyright (C) Ximian, Inc 2002
  12 // Copyright (C) Daniel Morgan 2002, 2003
  13 // Copyright (C) Tim Coleman, 2002, 2003
  14 // Copyright (C) Phillip Jerkins, 2003
  15 //
  16
  17 //
  18 // Copyright (C) 2004 Novell, Inc (http://www.novell.com)
  19 //
  20 // Permission is hereby granted, free of charge, to any person obtaining
  21 // a copy of this software and associated documentation files (the
  22 // "Software"), to deal in the Software without restriction, including
  23 // without limitation the rights to use, copy, modify, merge, publish,
  24 // distribute, sublicense, and/or sell copies of the Software, and to
  25 // permit persons to whom the Software is furnished to do so, subject to
  26 // the following conditions:
  27 //
  28 // The above copyright notice and this permission notice shall be
  29 // included in all copies or substantial portions of the Software.
  30 //
  31 // THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
  32 // EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
  33 // MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
  34 // NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE
  35 // LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION
  36 // OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION
  37 // WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
  38 //
  39
  40 using Mono.Data.Tds;
  41 using Mono.Data.Tds.Protocol;
  42 using System;
  43 using System.Collections;
  44 using System.Collections.Specialized;
  45 using System.ComponentModel;
  46 using System.Data;
  47 using System.Data.Common;
  48 #if !MOBILE
  49 using System.EnterpriseServices;
  50 #endif
  51 using System.Globalization;
  52 using System.Net;
  53 using System.Net.Sockets;
  54 using System.Text;
  55 using System.Xml;
  56 using System.Collections.Generic;
  57 using System.Security;
  58
  59 namespace System.Data.SqlClient
  60 {
  61         [DefaultEvent ("InfoMessage")]
  62         public sealed class SqlConnection : DbConnection, IDbConnection, ICloneable
  63         {
  64                 #region Fields
  65
  66                 bool disposed;
  67
  68                 // The set of SQL connection pools
  69                 static TdsConnectionPoolManager sqlConnectionPools = new TdsConnectionPoolManager (TdsVersion.tds80);
  70                 const int DEFAULT_PACKETSIZE = 8000;
  71                 const int MAX_PACKETSIZE = 32768;
  72                 const int MIN_PACKETSIZE = 512;
  73                 const int DEFAULT_CONNECTIONTIMEOUT = 15;
  74                 const int DEFAULT_CONNECTIONLIFETIME = 0;
  75                 const int DEFAULT_MAXPOOLSIZE = 100;
  76                 const int MIN_MAXPOOLSIZE = 1;
  77                 const int DEFAULT_MINPOOLSIZE = 0;
  78                 const int DEFAULT_PORT = 1433;
  79
  80                 // The current connection pool
  81                 TdsConnectionPool pool;
  82
  83                 // The connection string that identifies this connection
  84                 string connectionString;
  85
  86                 // The connection credentials
  87                 SqlCredential credentials;
  88
  89                 // The transaction object for the current transaction
  90                 SqlTransaction transaction;
  91
  92                 // Connection parameters
  93
  94                 TdsConnectionParameters parms;
  95                 bool connectionReset;
  96                 int connectionLifeTime;
  97                 bool pooling;
  98                 string dataSource;
  99                 int connectionTimeout;
 100                 int minPoolSize;
 101                 int maxPoolSize;
 102                 int packetSize;
 103                 int port;
 104                 bool fireInfoMessageEventOnUserErrors;
 105                 bool statisticsEnabled;
 106
 107                 // The current state
 108                 ConnectionState state = ConnectionState.Closed;
 109
 110                 SqlDataReader dataReader;
 111                 XmlReader xmlReader;
 112
 113                 // The TDS object
 114                 Tds tds;
 115
 116                 #endregion // Fields
 117
 118                 #region Constructors
 119
 120                 public SqlConnection () : this (null)
 121                 {
 122                 }
 123
 124                 public SqlConnection (string connectionString)
 125                 {
 126                         ConnectionString = connectionString;
 127                 }
 128
 129                 public SqlConnection (string connectionString, SqlCredential cred)
 130                 {
 131                         ConnectionString = connectionString;
 132                         Credentials = cred;
 133                 }
 134
 135                 #endregion // Constructors
 136
 137                 #region Properties
 138
 139                 [DefaultValue ("")]
 140                 [EditorAttribute ("Microsoft.VSDesigner.Data.SQL.Design.SqlConnectionStringEditor, "+ Consts.AssemblyMicrosoft_VSDesigner, "System.Drawing.Design.UITypeEditor, "+ Consts.AssemblySystem_Drawing )]
 141                 [RecommendedAsConfigurable (true)]
 142                 [RefreshProperties (RefreshProperties.All)]
 143                 public override string ConnectionString {
 144                         get {
 145                                 if (connectionString == null)
 146                                         return string.Empty;
 147                                 return connectionString;
 148                         }
 149                         [MonoTODO("persist security info, encrypt, enlist keyword not implemented")]
 150                         set {
 151                                 if (state == ConnectionState.Open)
 152                                         throw new InvalidOperationException ("Not Allowed to change ConnectionString property while Connection state is OPEN");
 153                                 SetConnectionString (value);
 154                         }
 155                 }
 156
 157                 public SqlCredential Credentials {
 158                         get {
 159                                 return credentials;
 160                         }
 161                         set {
 162                                 credentials = value;
 163                         }
 164                 }
 165
 166                 [DesignerSerializationVisibility (DesignerSerializationVisibility.Hidden)]
 167                 public
 168                 override
 169                 int ConnectionTimeout {
 170                         get { return connectionTimeout; }
 171                 }
 172
 173                 [DesignerSerializationVisibility (DesignerSerializationVisibility.Hidden)]
 174                 public
 175                 override
 176                 string Database {
 177                         get {
 178                                 if (State == ConnectionState.Open)
 179                                         return tds.Database;
 180                                 return parms.Database ;
 181                         }
 182                 }
 183
 184                 internal SqlDataReader DataReader {
 185                         get { return dataReader; }
 186                         set { dataReader = value; }
 187                 }
 188
 189                 [Browsable(true)]
 190                 [DesignerSerializationVisibility (DesignerSerializationVisibility.Hidden)]
 191                 public
 192                 override
 193                 string DataSource {
 194                         get { return dataSource; }
 195                 }
 196
 197                 [DesignerSerializationVisibility (DesignerSerializationVisibility.Hidden)]
 198                 public int PacketSize {
 199                         get {
 200                                 if (State == ConnectionState.Open)
 201                                         return ((Tds) tds).PacketSize;
 202                                 return packetSize;
 203                         }
 204                 }
 205
 206                 [Browsable (false)]
 207                 [DesignerSerializationVisibility (DesignerSerializationVisibility.Hidden)]
 208                 public
 209                 override
 210                 string ServerVersion {
 211                         get {
 212                                 if (state == ConnectionState.Closed)
 213                                         throw ExceptionHelper.ConnectionClosed ();
 214                                 else
 215                                         return tds.ServerVersion;
 216                         }
 217                 }
 218
 219                 [Browsable (false)]
 220                 [DesignerSerializationVisibility (DesignerSerializationVisibility.Hidden)]
 221                 public
 222                 override
 223                 ConnectionState State {
 224                         get { return state; }
 225                 }
 226
 227                 internal Tds Tds {
 228                         get { return tds; }
 229                 }
 230
 231                 internal SqlTransaction Transaction {
 232                         get { return transaction; }
 233                         set { transaction = value; }
 234                 }
 235
 236                 [DesignerSerializationVisibility (DesignerSerializationVisibility.Hidden)]
 237                 public string WorkstationId {
 238                         get { return parms.Hostname; }
 239                 }
 240
 241                 internal XmlReader XmlReader {
 242                         get { return xmlReader; }
 243                         set { xmlReader = value; }
 244                 }
 245
 246                 public bool FireInfoMessageEventOnUserErrors {
 247                         get { return fireInfoMessageEventOnUserErrors; }
 248                         set { fireInfoMessageEventOnUserErrors = value; }
 249                 }
 250
 251                 [DefaultValue (false)]
 252                 public bool StatisticsEnabled {
 253                         get { return statisticsEnabled; }
 254                         set { statisticsEnabled = value; }
 255                 }
 256
 257                 protected override DbProviderFactory DbProviderFactory {
 258                         get {
 259                                 return SqlClientFactory.Instance;
 260                         }
 261                 }
 262
 263                 #endregion // Properties
 264
 265                 #region Events
 266
 267                 public event SqlInfoMessageEventHandler InfoMessage;
 268
 269
 270                 #endregion // Events
 271
 272                 #region Delegates
 273
 274                 private void ErrorHandler (object sender, TdsInternalErrorMessageEventArgs e)
 275                 {
 276                         try {
 277                                 if (!tds.IsConnected)
 278                                         Close ();
 279                         } catch {
 280                                 try {
 281                                         Close ();
 282                                 } catch {
 283                                 }
 284                         }
 285                         throw new SqlException (e.Class, e.LineNumber, e.Message, e.Number, e.Procedure, e.Server, "Mono SqlClient Data Provider", e.State);
 286                 }
 287
 288                 private void MessageHandler (object sender, TdsInternalInfoMessageEventArgs e)
 289                 {
 290                         OnSqlInfoMessage (CreateSqlInfoMessageEvent (e.Errors));
 291                 }
 292
 293                 #endregion // Delegates
 294
 295                 #region Methods
 296
 297                 public new SqlTransaction BeginTransaction ()
 298                 {
 299                         return BeginTransaction (IsolationLevel.ReadCommitted, String.Empty);
 300                 }
 301
 302                 public new SqlTransaction BeginTransaction (IsolationLevel iso)
 303                 {
 304                         return BeginTransaction (iso, String.Empty);
 305                 }
 306
 307                 public SqlTransaction BeginTransaction (string transactionName)
 308                 {
 309                         return BeginTransaction (IsolationLevel.ReadCommitted, transactionName);
 310                 }
 311
 312                 public SqlTransaction BeginTransaction (IsolationLevel iso, string transactionName)
 313                 {
 314                         if (state == ConnectionState.Closed)
 315                                 throw ExceptionHelper.ConnectionClosed ();
 316                         if (transaction != null)
 317                                 throw new InvalidOperationException ("SqlConnection does not support parallel transactions.");
 318
 319                         string isolevel = String.Empty;
 320                         switch (iso) {
 321                         case IsolationLevel.ReadUncommitted:
 322                                 isolevel = "READ UNCOMMITTED";
 323                                 break;
 324                         case IsolationLevel.RepeatableRead:
 325                                 isolevel = "REPEATABLE READ";
 326                                 break;
 327                         case IsolationLevel.Serializable:
 328                                 isolevel = "SERIALIZABLE";
 329                                 break;
 330                         case IsolationLevel.ReadCommitted:
 331                                 isolevel = "READ COMMITTED";
 332                                 break;
 333                         case IsolationLevel.Snapshot:
 334                                 isolevel = "SNAPSHOT";
 335                                 break;
 336                         case IsolationLevel.Unspecified:
 337                                 iso = IsolationLevel.ReadCommitted;
 338                                 isolevel = "READ COMMITTED";
 339                                 break;
 340                         case IsolationLevel.Chaos:
 341                                 throw new ArgumentOutOfRangeException ("IsolationLevel",
 342                                         string.Format (CultureInfo.CurrentCulture,
 343                                                 "The IsolationLevel enumeration " +
 344                                                 "value, {0}, is not supported by " +
 345                                                 "the .Net Framework SqlClient " +
 346                                                 "Data Provider.", (int) iso));
 347                         default:
 348                                 throw new ArgumentOutOfRangeException ("IsolationLevel",
 349                                         string.Format (CultureInfo.CurrentCulture,
 350                                                 "The IsolationLevel enumeration value, {0}, is invalid.",
 351                                                 (int) iso));
 352                         }
 353
 354                         tds.Execute (String.Format ("SET TRANSACTION ISOLATION LEVEL {0};BEGIN TRANSACTION {1}", isolevel, transactionName));
 355
 356                         transaction = new SqlTransaction (this, iso);
 357                         return transaction;
 358                 }
 359
 360                 public
 361                 override
 362                 void ChangeDatabase (string database)
 363                 {
 364                         if (!IsValidDatabaseName (database))
 365                                 throw new ArgumentException (String.Format ("The database name {0} is not valid.", database));
 366                         if (state != ConnectionState.Open)
 367                                 throw new InvalidOperationException ("The connection is not open.");
 368                         tds.Execute (String.Format ("use [{0}]", database));
 369                 }
 370
 371                 private void ChangeState (ConnectionState currentState)
 372                 {
 373                         if (currentState == state)
 374                                 return;
 375
 376                         ConnectionState originalState = state;
 377                         state = currentState;
 378                         OnStateChange (CreateStateChangeEvent (originalState, currentState));
 379                 }
 380
 381                 public
 382                 override
 383                 void Close ()
 384                 {
 385                         if (transaction != null && transaction.IsOpen)
 386                                 transaction.Rollback ();
 387
 388                         if (dataReader != null || xmlReader != null) {
 389                                 if(tds != null) tds.SkipToEnd ();
 390                                 dataReader = null;
 391                                 xmlReader = null;
 392                         }
 393
 394                         if (tds != null && tds.IsConnected) {
 395                                 if (pooling && tds.Pooling) {
 396                                         if (pool != null) {
 397                                                 pool.ReleaseConnection (tds);
 398                                                 pool = null;
 399                                         }
 400                                 } else {
 401                                         tds.Disconnect ();
 402                                 }
 403                         }
 404
 405                         if (tds != null) {
 406                                 tds.TdsErrorMessage -= new TdsInternalErrorMessageEventHandler (ErrorHandler);
 407                                 tds.TdsInfoMessage -= new TdsInternalInfoMessageEventHandler (MessageHandler);
 408                         }
 409
 410                         ChangeState (ConnectionState.Closed);
 411                 }
 412
 413                 public new SqlCommand CreateCommand ()
 414                 {
 415                         SqlCommand command = new SqlCommand ();
 416                         command.Connection = this;
 417                         return command;
 418                 }
 419
 420                 private SqlInfoMessageEventArgs CreateSqlInfoMessageEvent (TdsInternalErrorCollection errors)
 421                 {
 422                         return new SqlInfoMessageEventArgs (errors);
 423                 }
 424
 425                 private StateChangeEventArgs CreateStateChangeEvent (ConnectionState originalState, ConnectionState currentState)
 426                 {
 427                         return new StateChangeEventArgs (originalState, currentState);
 428                 }
 429
 430                 protected override void Dispose (bool disposing)
 431                 {
 432                         try {
 433                                 if (disposing && !disposed) {
 434                                         if (State == ConnectionState.Open)
 435                                                 Close ();
 436                                         ConnectionString = null;
 437                                 }
 438                         } finally {
 439                                 disposed = true;
 440                                 base.Dispose (disposing);
 441                         }
 442                 }
 443
 444 #if !MOBILE
 445                 [MonoTODO ("Not sure what this means at present.")]
 446                 public void EnlistDistributedTransaction (ITransaction transaction)
 447                 {
 448                         throw new NotImplementedException ();
 449                 }
 450 #endif
 451
 452                 object ICloneable.Clone ()
 453                 {
 454                         return new SqlConnection (ConnectionString);
 455                 }
 456
 457                 protected override DbTransaction BeginDbTransaction (IsolationLevel isolationLevel)
 458                 {
 459                         return BeginTransaction (isolationLevel);
 460                 }
 461
 462                 protected override DbCommand CreateDbCommand ()
 463                 {
 464                         return CreateCommand ();
 465                 }
 466
 467                 public
 468                 override
 469                 void Open ()
 470                 {
 471                         string serverName = string.Empty;
 472                         if (state == ConnectionState.Open)
 473                                 throw new InvalidOperationException ("The Connection is already Open (State=Open)");
 474
 475                         if (connectionString == null || connectionString.Trim().Length == 0)
 476                                 throw new InvalidOperationException ("Connection string has not been initialized.");
 477
 478                         try {
 479                                 if (!pooling) {
 480                                         if(!ParseDataSource (dataSource, out port, out serverName))
 481                                                 throw new SqlException(20, 0, "SQL Server does not exist or access denied.",  17, "ConnectionOpen (Connect()).", dataSource, parms.ApplicationName, 0);
 482                                         tds = new Tds80 (serverName, port, PacketSize, ConnectionTimeout, 0);
 483                                         tds.Pooling = false;
 484                                 }
 485                                 else {
 486                                         if(!ParseDataSource (dataSource, out port, out serverName))
 487                                                 throw new SqlException(20, 0, "SQL Server does not exist or access denied.",  17, "ConnectionOpen (Connect()).", dataSource, parms.ApplicationName, 0);
 488
 489                                         TdsConnectionInfo info = new TdsConnectionInfo (serverName, port, packetSize, ConnectionTimeout, minPoolSize, maxPoolSize, connectionLifeTime);
 490                                         pool = sqlConnectionPools.GetConnectionPool (connectionString, info);
 491                                         tds = pool.GetConnection ();
 492                                 }
 493                         } catch (TdsTimeoutException e) {
 494                                 throw SqlException.FromTdsInternalException ((TdsInternalException) e);
 495                         } catch (TdsInternalException e) {
 496                                 throw SqlException.FromTdsInternalException (e);
 497                         }
 498
 499                         tds.TdsErrorMessage += new TdsInternalErrorMessageEventHandler (ErrorHandler);
 500                         tds.TdsInfoMessage += new TdsInternalInfoMessageEventHandler (MessageHandler);
 501
 502                         if (!tds.IsConnected) {
 503                                 try {
 504                                         if (Credentials != null) {
 505                                                 if (parms.User != String.Empty)
 506                                                         throw new ArgumentException("UserID already specified");
 507                                                 if (parms.PasswordSet)
 508                                                         throw new ArgumentException("Password already specified");
 509                                                 if (parms.DomainLogin != false)
 510                                                         throw new ArgumentException("Cannot use credentials with DomainLogin");
 511                                                 parms.User = Credentials.UserId;
 512                                                 parms.Password = Credentials.Password;
 513                                         }
 514                                         tds.Connect (parms);
 515                                 } catch {
 516                                         if (pooling)
 517                                                 pool.ReleaseConnection (tds);
 518                                         throw;
 519                                 }
 520                         }
 521
 522                         disposed = false; // reset this, so using () would call Close ().
 523                         ChangeState (ConnectionState.Open);
 524                 }
 525
 526                 private bool ParseDataSource (string theDataSource, out int thePort, out string theServerName)
 527                 {
 528                         theServerName = string.Empty;
 529                         string theInstanceName = string.Empty;
 530
 531                         if (theDataSource == null)
 532                                 throw new ArgumentException("Format of initialization string does not conform to specifications");
 533
 534                         thePort = DEFAULT_PORT; // default TCP port for SQL Server
 535                         bool success = true;
 536
 537                         int idx = 0;
 538                         if ((idx = theDataSource.IndexOf (',')) > -1) {
 539                                 theServerName = theDataSource.Substring (0, idx);
 540                                 string p = theDataSource.Substring (idx + 1);
 541                                 thePort = Int32.Parse (p);
 542                         } else if ((idx = theDataSource.IndexOf ('\\')) > -1) {
 543                                 theServerName = theDataSource.Substring (0, idx);
 544                                 theInstanceName = theDataSource.Substring (idx + 1);
 545
 546                                 // do port discovery via UDP port 1434
 547                                 port = DiscoverTcpPortViaSqlMonitor (theServerName, theInstanceName);
 548                                 if (port == -1)
 549                                         success = false;
 550                         } else
 551                                 theServerName = theDataSource;
 552
 553                         if (theServerName.Length == 0 || theServerName == "(local)" || theServerName == ".")
 554                                 theServerName = "localhost";
 555
 556                         if ((idx = theServerName.IndexOf ("tcp:")) > -1)
 557                                 theServerName = theServerName.Substring (idx + 4);
 558
 559                         return success;
 560                 }
 561
 562                 private bool ConvertIntegratedSecurity (string value)
 563                 {
 564                         if (value.ToUpper() == "SSPI")
 565                                 return true;
 566
 567                         return ConvertToBoolean ("integrated security", value, false);
 568                 }
 569
 570                 private bool ConvertToBoolean (string key, string value, bool defaultValue)
 571                 {
 572                         if (value.Length == 0)
 573                                 return defaultValue;
 574
 575                         string upperValue = value.ToUpper ();
 576
 577                         if (upperValue == "TRUE" || upperValue == "YES")
 578                                 return true;
 579                         else if (upperValue == "FALSE" || upperValue == "NO")
 580                                 return false;
 581
 582                         throw new ArgumentException (string.Format (CultureInfo.InvariantCulture,
 583                                 "Invalid value \"{0}\" for key '{1}'.", value, key));
 584                 }
 585
 586                 private int ConvertToInt32 (string key, string value, int defaultValue)
 587                 {
 588                         if (value.Length == 0)
 589                                 return defaultValue;
 590
 591                         try {
 592                                 return int.Parse (value);
 593                         } catch (Exception ex) {
 594                                 throw new ArgumentException (string.Format (CultureInfo.InvariantCulture,
 595                                         "Invalid value \"{0}\" for key '{1}'.", value, key), ex);
 596                         }
 597                 }
 598
 599                 private int DiscoverTcpPortViaSqlMonitor (string ServerName, string InstanceName)
 600                 {
 601                         SqlMonitorSocket msock;
 602                         msock = new SqlMonitorSocket (ServerName, InstanceName);
 603                         int SqlServerPort = msock.DiscoverTcpPort (ConnectionTimeout);
 604                         msock = null;
 605                         return SqlServerPort;
 606                 }
 607
 608                 void SetConnectionString (string connectionString)
 609                 {
 610                         SetDefaultConnectionParameters ();
 611
 612                         if ((connectionString == null) || (connectionString.Trim().Length == 0)) {
 613                                 this.connectionString = connectionString;
 614                                 return;
 615                         }
 616
 617                         connectionString += ";";
 618
 619                         bool inQuote = false;
 620                         bool inDQuote = false;
 621                         bool inName = true;
 622
 623                         string name = String.Empty;
 624                         string value = String.Empty;
 625                         StringBuilder sb = new StringBuilder ();
 626
 627                         for (int i = 0; i < connectionString.Length; i += 1) {
 628                                 char c = connectionString [i];
 629                                 char peek;
 630                                 if (i == connectionString.Length - 1)
 631                                         peek = '\0';
 632                                 else
 633                                         peek = connectionString [i + 1];
 634
 635                                 switch (c) {
 636                                 case '\'':
 637                                         if (inDQuote)
 638                                                 sb.Append (c);
 639                                         else if (peek.Equals (c)) {
 640                                                 sb.Append (c);
 641                                                 i += 1;
 642                                         }
 643                                         else
 644                                                 inQuote = !inQuote;
 645                                         break;
 646                                 case '"':
 647                                         if (inQuote)
 648                                                 sb.Append (c);
 649                                         else if (peek.Equals (c)) {
 650                                                 sb.Append (c);
 651                                                 i += 1;
 652                                         }
 653                                         else
 654                                                 inDQuote = !inDQuote;
 655                                         break;
 656                                 case ';':
 657                                         if (inDQuote || inQuote)
 658                                                 sb.Append (c);
 659                                         else {
 660                                                 if (name != String.Empty && name != null) {
 661                                                         value = sb.ToString ();
 662                                                         SetProperties (name.ToLower ().Trim() , value);
 663                                                 }
 664                                                 else if (sb.Length != 0)
 665                                                         throw new ArgumentException ("Format of initialization string does not conform to specifications");
 666                                                 inName = true;
 667                                                 name = String.Empty;
 668                                                 value = String.Empty;
 669                                                 sb = new StringBuilder ();
 670                                         }
 671                                         break;
 672                                 case '=':
 673                                         if (inDQuote || inQuote || !inName)
 674                                                 sb.Append (c);
 675                                         else if (peek.Equals (c)) {
 676                                                 sb.Append (c);
 677                                                 i += 1;
 678
 679                                         }
 680                                         else {
 681                                                 name = sb.ToString ();
 682                                                 sb = new StringBuilder ();
 683                                                 inName = false;
 684                                         }
 685                                         break;
 686                                 case ' ':
 687                                         if (inQuote || inDQuote)
 688                                                 sb.Append (c);
 689                                         else if (sb.Length > 0 && !peek.Equals (';'))
 690                                                 sb.Append (c);
 691                                         break;
 692                                 default:
 693                                         sb.Append (c);
 694                                         break;
 695                                 }
 696                         }
 697
 698                         if (minPoolSize > maxPoolSize)
 699                                 throw new ArgumentException ("Invalid value for "
 700                                         + "'min pool size' or 'max pool size'; "
 701                                         + "'min pool size' must not be greater "
 702                                         + "than 'max pool size'.");
 703
 704                         connectionString = connectionString.Substring (0 , connectionString.Length-1);
 705                         this.connectionString = connectionString;
 706                 }
 707
 708                 void SetDefaultConnectionParameters ()
 709                 {
 710                         if (parms == null)
 711                                 parms = new TdsConnectionParameters ();
 712                         else
 713                                 parms.Reset ();
 714                         dataSource = string.Empty;
 715                         connectionTimeout = DEFAULT_CONNECTIONTIMEOUT;
 716                         connectionLifeTime = DEFAULT_CONNECTIONLIFETIME;
 717                         connectionReset = true;
 718                         pooling = true;
 719                         maxPoolSize = DEFAULT_MAXPOOLSIZE;
 720                         minPoolSize = DEFAULT_MINPOOLSIZE;
 721                         packetSize = DEFAULT_PACKETSIZE;
 722                         port = DEFAULT_PORT;
 723                         async = false;
 724                 }
 725
 726                 private void SetProperties (string name , string value)
 727                 {
 728                         switch (name) {
 729                         case "app" :
 730                         case "application name" :
 731                                 parms.ApplicationName = value;
 732                                 break;
 733                         case "attachdbfilename" :
 734                         case "extended properties" :
 735                         case "initial file name" :
 736                                 parms.AttachDBFileName = value;
 737                                 break;
 738                         case "timeout" :
 739                         case "connect timeout" :
 740                         case "connection timeout" :
 741                                 int tmpTimeout = ConvertToInt32 ("connect timeout", value,
 742                                         DEFAULT_CONNECTIONTIMEOUT);
 743                                 if (tmpTimeout < 0)
 744                                         throw new ArgumentException ("Invalid 'connect timeout'. Must be an integer >=0 ");
 745                                 else
 746                                         connectionTimeout = tmpTimeout;
 747                                 break;
 748                         case "connection lifetime" :
 749                                 connectionLifeTime = ConvertToInt32 ("connection lifetime", value, DEFAULT_CONNECTIONLIFETIME);
 750                                 break;
 751                         case "connection reset" :
 752                                 connectionReset = ConvertToBoolean ("connection reset", value, true);
 753                                 break;
 754                         case "language" :
 755                         case "current language" :
 756                                 parms.Language = value;
 757                                 break;
 758                         case "data source" :
 759                         case "server" :
 760                         case "address" :
 761                         case "addr" :
 762                         case "network address" :
 763                                 dataSource = value;
 764                                 break;
 765                         case "encrypt":
 766                                 if (ConvertToBoolean (name, value, false))
 767                                         throw new NotImplementedException("SSL encryption for"
 768                                                 + " data sent between client and server is not"
 769                                                 + " implemented.");
 770                                 break;
 771                         case "enlist" :
 772                                 if (!ConvertToBoolean (name, value, true))
 773                                         throw new NotImplementedException("Disabling the automatic"
 774                                                 + " enlistment of connections in the thread's current"
 775                                                 + " transaction context is not implemented.");
 776                                 break;
 777                         case "initial catalog" :
 778                         case "database" :
 779                                 parms.Database = value;
 780                                 break;
 781                         case "integrated security" :
 782                         case "trusted_connection" :
 783                                 parms.DomainLogin = ConvertIntegratedSecurity(value);
 784                                 break;
 785                         case "max pool size" :
 786                                 int tmpMaxPoolSize = ConvertToInt32 (name, value, DEFAULT_MAXPOOLSIZE);
 787                                 if (tmpMaxPoolSize < MIN_MAXPOOLSIZE)
 788                                         throw new ArgumentException (string.Format (CultureInfo.InvariantCulture,
 789                                                 "Invalid '{0}'. The value must be greater than {1}.",
 790                                                 name, MIN_MAXPOOLSIZE));
 791                                 else
 792                                         maxPoolSize = tmpMaxPoolSize;
 793                                 break;
 794                         case "min pool size" :
 795                                 int tmpMinPoolSize = ConvertToInt32 (name, value, DEFAULT_MINPOOLSIZE);
 796                                 if (tmpMinPoolSize < 0)
 797                                         throw new ArgumentException ("Invalid 'min pool size'. Must be a integer >= 0");
 798                                 else
 799                                         minPoolSize = tmpMinPoolSize;
 800                                 break;
 801                         case "multipleactiveresultsets":
 802                                 // FIXME: not implemented
 803                                 ConvertToBoolean (name, value, false);
 804                                 break;
 805                         case "asynchronous processing" :
 806                         case "async" :
 807                                 async = ConvertToBoolean (name, value, false);
 808                                 break;
 809                         case "net" :
 810                         case "network" :
 811                         case "network library" :
 812                                 if (!value.ToUpper ().Equals ("DBMSSOCN"))
 813                                         throw new ArgumentException ("Unsupported network library.");
 814                                 break;
 815                         case "packet size" :
 816                                 int tmpPacketSize = ConvertToInt32 (name, value, DEFAULT_PACKETSIZE);
 817                                 if (tmpPacketSize < MIN_PACKETSIZE || tmpPacketSize > MAX_PACKETSIZE)
 818                                         throw new ArgumentException (string.Format (CultureInfo.InvariantCulture,
 819                                                 "Invalid 'Packet Size'. The value must be between {0} and {1}.",
 820                                                 MIN_PACKETSIZE, MAX_PACKETSIZE));
 821                                 else
 822                                         packetSize = tmpPacketSize;
 823                                 break;
 824                         case "password" :
 825                         case "pwd" :
 826                                 parms.Password = new SecureString();
 827                                 foreach (char c in value)
 828                                         parms.Password.AppendChar(c);
 829                                 parms.PasswordSet = true;
 830                                 break;
 831                         case "persistsecurityinfo" :
 832                         case "persist security info" :
 833                                 // FIXME : not implemented
 834                                 // throw new NotImplementedException ();
 835                                 break;
 836                         case "pooling" :
 837                                 pooling = ConvertToBoolean (name, value, true);
 838                                 break;
 839                         case "uid" :
 840                         case "user" :
 841                         case "user id" :
 842                                 parms.User = value;
 843                                 break;
 844                         case "wsid" :
 845                         case "workstation id" :
 846                                 parms.Hostname = value;
 847                                 break;
 848                         case "user instance":
 849                                 userInstance = ConvertToBoolean (name, value, false);
 850                                 break;
 851                         default :
 852                                 throw new ArgumentException("Keyword not supported : '" + name + "'.");
 853                         }
 854                 }
 855
 856                 static bool IsValidDatabaseName (string database)
 857                 {
 858                         if ( database == null || database.Trim().Length == 0 || database.Length > 128)
 859                                 return false ;
 860
 861                         if (database[0] == '"' && database[database.Length] == '"')
 862                                 database = database.Substring (1, database.Length - 2);
 863                         else if (Char.IsDigit (database[0]))
 864                                 return false;
 865
 866                         if (database[0] == '_')
 867                                 return false;
 868
 869                         foreach (char c  in database.Substring (1, database.Length - 1))
 870                                 if (!Char.IsLetterOrDigit (c) && c != '_' && c != '-')
 871                                         return false;
 872                         return true;
 873                 }
 874
 875                 private void OnSqlInfoMessage (SqlInfoMessageEventArgs value)
 876                 {
 877                         if (InfoMessage != null)
 878                                 InfoMessage (this, value);
 879                 }
 880
 881
 882                 private sealed class SqlMonitorSocket : UdpClient
 883                 {
 884                         // UDP port that the SQL Monitor listens
 885                         private static readonly int SqlMonitorUdpPort = 1434;
 886                         //private static readonly string SqlServerNotExist = "SQL Server does not exist or access denied";
 887
 888                         private string server;
 889                         private string instance;
 890
 891                         internal SqlMonitorSocket (string ServerName, string InstanceName)
 892                                 : base (ServerName, SqlMonitorUdpPort)
 893                         {
 894                                 server = ServerName;
 895                                 instance = InstanceName;
 896                         }
 897
 898                         internal int DiscoverTcpPort (int timeoutSeconds)
 899                         {
 900                                 int SqlServerTcpPort;
 901                                 Client.Blocking = false;
 902                                 // send command to UDP 1434 (SQL Monitor) to get
 903                                 // the TCP port to connect to the MS SQL server
 904                                 ASCIIEncoding enc = new ASCIIEncoding ();
 905                                 Byte[] rawrq = new Byte [instance.Length + 1];
 906                                 rawrq[0] = 4;
 907                                 enc.GetBytes (instance, 0, instance.Length, rawrq, 1);
 908                                 Send (rawrq, rawrq.Length);
 909
 910                                 if (!Active)
 911                                         return -1; // Error
 912
 913                                 bool result;
 914                                 long timeout = timeoutSeconds * 1000000;
 915                                 result = Client.Poll ((int)timeout, SelectMode.SelectRead);
 916                                 if (result == false)
 917                                         return -1; // Error
 918
 919                                 if (Client.Available <= 0)
 920                                         return -1; // Error
 921                                 IPEndPoint endpoint = new IPEndPoint (Dns.GetHostEntry ("localhost").AddressList [0], 0);
 922                                 Byte [] rawrs;
 923
 924                                 rawrs = Receive (ref endpoint);
 925
 926                                 string rs = Encoding.ASCII.GetString (rawrs);
 927
 928                                 string[] rawtokens = rs.Split (';');
 929                                 Hashtable data = new Hashtable ();
 930                                 for (int i = 0; i < rawtokens.Length / 2 && i < 256; i++) {
 931                                         data [rawtokens [i * 2]] = rawtokens [ i * 2 + 1];
 932                                 }
 933
 934                                 if (!data.ContainsKey ("tcp")) {
 935                                         string msg = "Mono does not support names pipes or shared memory "
 936                                                 + "for connecting to SQL Server. Please enable the TCP/IP "
 937                                                 + "protocol.";
 938                                         throw new NotImplementedException (msg);
 939                                 }
 940
 941                                 SqlServerTcpPort = int.Parse ((string) data ["tcp"]);
 942                                 Close ();
 943
 944                                 return SqlServerTcpPort;
 945                         }
 946                 }
 947
 948                 struct ColumnInfo
 949                 {
 950                         public string name;
 951                         public Type type;
 952
 953                         public ColumnInfo (string name, Type type)
 954                         {
 955                                 this.name = name; this.type = type;
 956                         }
 957                 }
 958
 959                 static class ReservedWords
 960                 {
 961                         static readonly string [] reservedWords =
 962                         {
 963                                 "ADD", "EXCEPT", "PERCENT", "ALL", "EXEC", "PLAN", "ALTER",
 964                                   "EXECUTE", "PRECISION", "AND", "EXISTS", "PRIMARY", "ANY",
 965                                   "EXIT", "PRINT", "AS", "FETCH", "PROC", "ASC", "FILE",
 966                                   "PROCEDURE", "AUTHORIZATION", "FILLFACTOR", "PUBLIC",
 967                                   "BACKUP", "FOR", "RAISERROR", "BEGIN", "FOREIGN", "READ",
 968                                   "BETWEEN", "FREETEXT", "READTEXT", "BREAK", "FREETEXTTABLE",
 969                                   "RECONFIGURE", "BROWSE", "FROM", "REFERENCES", "BULK",
 970                                   "FULL", "REPLICATION", "BY", "FUNCTION", "RESTORE",
 971                                   "CASCADE", "GOTO", "RESTRICT", "CASE", "GRANT", "RETURN",
 972                                   "CHECK", "GROUP", "REVOKE", "CHECKPOINT", "HAVING", "RIGHT",
 973                                   "CLOSE", "HOLDLOCK", "ROLLBACK", "CLUSTERED", "IDENTITY",
 974                                   "ROWCOUNT", "COALESCE", "IDENTITY_INSERT", "ROWGUIDCOL",
 975                                   "COLLATE", "IDENTITYCOL", "RULE", "COLUMN", "IF", "SAVE",
 976                                   "COMMIT", "IN", "SCHEMA", "COMPUTE", "INDEX", "SELECT",
 977                                   "CONSTRAINT", "INNER", "SESSION_USER", "CONTAINS", "INSERT",
 978                                   "SET", "CONTAINSTABLE", "INTERSECT", "SETUSER", "CONTINUE",
 979                                   "INTO", "SHUTDOWN", "CONVERT", "IS", "SOME", "CREATE",
 980                                   "JOIN", "STATISTICS", "CROSS", "KEY", "SYSTEM_USER",
 981                                   "CURRENT", "KILL", "TABLE", "CURRENT_DATE", "LEFT",
 982                                   "TEXTSIZE", "CURRENT_TIME", "LIKE", "THEN",
 983                                   "CURRENT_TIMESTAMP", "LINENO", "TO", "CURRENT_USER", "LOAD",
 984                                   "TOP", "CURSOR", "NATIONAL", "TRAN", "DATABASE", "NOCHECK",
 985                                   "TRANSACTION", "DBCC", "NONCLUSTERED", "TRIGGER",
 986                                   "DEALLOCATE", "NOT", "TRUNCATE", "DECLARE", "NULL",
 987                                   "TSEQUAL", "DEFAULT", "NULLIF", "UNION", "DELETE", "OF",
 988                                   "UNIQUE", "DENY", "OFF", "UPDATE", "DESC", "OFFSETS",
 989                                   "UPDATETEXT", "DISK", "ON", "USE", "DISTINCT", "OPEN",
 990                                   "USER", "DISTRIBUTED", "OPENDATASOURCE", "VALUES", "DOUBLE",
 991                                   "OPENQUERY", "VARYING", "DROP", "OPENROWSET", "VIEW",
 992                                   "DUMMY", "OPENXML", "WAITFOR", "DUMP", "OPTION", "WHEN",
 993                                   "ELSE", "OR", "WHERE", "END", "ORDER", "WHILE", "ERRLVL",
 994                                   "OUTER", "WITH", "ESCAPE", "OVER", "WRITETEXT", "ABSOLUTE",
 995                                   "FOUND", "PRESERVE", "ACTION", "FREE", "PRIOR", "ADMIN",
 996                                   "GENERAL", "PRIVILEGES", "AFTER", "GET", "READS",
 997                                   "AGGREGATE", "GLOBAL", "REAL", "ALIAS", "GO", "RECURSIVE",
 998                                   "ALLOCATE", "GROUPING", "REF", "ARE", "HOST", "REFERENCING",
 999                                   "ARRAY", "HOUR", "RELATIVE", "ASSERTION", "IGNORE", "RESULT",
1000                                   "AT", "IMMEDIATE", "RETURNS", "BEFORE", "INDICATOR", "ROLE",
1001                                   "BINARY", "INITIALIZE", "ROLLUP", "BIT", "INITIALLY",
1002                                   "ROUTINE", "BLOB", "INOUT", "ROW", "BOOLEAN", "INPUT",
1003                                   "ROWS", "BOTH", "INT", "SAVEPOINT", "BREADTH", "INTEGER",
1004                                   "SCROLL", "CALL", "INTERVAL", "SCOPE", "CASCADED",
1005                                   "ISOLATION", "SEARCH", "CAST", "ITERATE", "SECOND",
1006                                   "CATALOG", "LANGUAGE", "SECTION", "CHAR", "LARGE",
1007                                   "SEQUENCE", "CHARACTER", "LAST", "SESSION", "CLASS",
1008                                   "LATERAL", "SETS", "CLOB", "LEADING", "SIZE", "COLLATION",
1009                                   "LESS", "SMALLINT", "COMPLETION", "LEVEL", "SPACE",
1010                                   "CONNECT", "LIMIT", "SPECIFIC", "CONNECTION", "LOCAL",
1011                                   "SPECIFICTYPE", "CONSTRAINTS", "LOCALTIME", "SQL",
1012                                   "CONSTRUCTOR", "LOCALTIMESTAMP", "SQLEXCEPTION",
1013                                   "CORRESPONDING", "LOCATOR", "SQLSTATE", "CUBE", "MAP",
1014                                   "SQLWARNING", "CURRENT_PATH", "MATCH", "START",
1015                                   "CURRENT_ROLE", "MINUTE", "STATE", "CYCLE", "MODIFIES",
1016                                   "STATEMENT", "DATA", "MODIFY", "STATIC", "DATE", "MODULE",
1017                                   "STRUCTURE", "DAY", "MONTH", "TEMPORARY", "DEC", "NAMES",
1018                                   "TERMINATE", "DECIMAL", "NATURAL", "THAN", "DEFERRABLE",
1019                                   "NCHAR", "TIME", "DEFERRED", "NCLOB", "TIMESTAMP", "DEPTH",
1020                                   "NEW", "TIMEZONE_HOUR", "DEREF", "NEXT", "TIMEZONE_MINUTE",
1021                                   "DESCRIBE", "NO", "TRAILING", "DESCRIPTOR", "NONE",
1022                                   "TRANSLATION", "DESTROY", "NUMERIC", "TREAT", "DESTRUCTOR",
1023                                   "OBJECT", "TRUE", "DETERMINISTIC", "OLD", "UNDER",
1024                                   "DICTIONARY", "ONLY", "UNKNOWN", "DIAGNOSTICS", "OPERATION",
1025                                   "UNNEST", "DISCONNECT", "ORDINALITY", "USAGE", "DOMAIN",
1026                                   "OUT", "USING", "DYNAMIC", "OUTPUT", "VALUE", "EACH",
1027                                   "PAD", "VARCHAR", "END-EXEC", "PARAMETER", "VARIABLE",
1028                                   "EQUALS", "PARAMETERS", "WHENEVER", "EVERY", "PARTIAL",
1029                                   "WITHOUT", "EXCEPTION", "PATH", "WORK", "EXTERNAL",
1030                                   "POSTFIX", "WRITE", "FALSE", "PREFIX", "YEAR", "FIRST",
1031                                   "PREORDER", "ZONE", "FLOAT", "PREPARE", "ADA", "AVG",
1032                                   "BIT_LENGTH", "CHAR_LENGTH", "CHARACTER_LENGTH", "COUNT",
1033                                   "EXTRACT", "FORTRAN", "INCLUDE", "INSENSITIVE", "LOWER",
1034                                   "MAX", "MIN", "OCTET_LENGTH", "OVERLAPS", "PASCAL",
1035                                   "POSITION", "SQLCA", "SQLCODE", "SQLERROR", "SUBSTRING",
1036                                   "SUM", "TRANSLATE", "TRIM", "UPPER"
1037                         };
1038                         static DataTable instance;
1039                         static public DataTable Instance {
1040                                 get {
1041                                         if (instance == null) {
1042                                                 DataRow row = null;
1043                                                 var newInstance = new DataTable ("ReservedWords");
1044                                                 newInstance.Columns.Add ("ReservedWord", typeof(string));
1045                                                 foreach (string reservedWord in reservedWords)
1046                                                 {
1047                                                         row = newInstance.NewRow();
1048
1049                                                         row["ReservedWord"] = reservedWord;
1050                                                         newInstance.Rows.Add(row);
1051                                                 }
1052                                                 instance = newInstance;
1053                                         }
1054                                         return instance;
1055                                 }
1056                         }
1057                 }
1058
1059                 static class MetaDataCollections
1060                 {
1061                         static readonly ColumnInfo [] columns = {
1062                                 new ColumnInfo ("CollectionName", typeof (string)),
1063                                 new ColumnInfo ("NumberOfRestrictions", typeof (int)),
1064                                 new ColumnInfo ("NumberOfIdentifierParts", typeof (int))
1065                         };
1066
1067                         static readonly object [][] rows = {
1068                                 new object [] {"MetaDataCollections", 0, 0},
1069                                 new object [] {"DataSourceInformation", 0, 0},
1070                                 new object [] {"DataTypes", 0, 0},
1071                                 new object [] {"Restrictions", 0, 0},
1072                                 new object [] {"ReservedWords", 0, 0},
1073                                 new object [] {"Users", 1, 1},
1074                                 new object [] {"Databases", 1, 1},
1075                                 new object [] {"Tables", 4, 3},
1076                                 new object [] {"Columns", 4, 4},
1077                                 new object [] {"StructuredTypeMembers", 4, 4},
1078                                 new object [] {"Views", 3, 3},
1079                                 new object [] {"ViewColumns", 4, 4},
1080                                 new object [] {"ProcedureParameters", 4, 1},
1081                                 new object [] {"Procedures", 4, 3},
1082                                 new object [] {"ForeignKeys", 4, 3},
1083                                 new object [] {"IndexColumns", 5, 4},
1084                                 new object [] {"Indexes", 4, 3},
1085                                 new object [] {"UserDefinedTypes", 2, 1}
1086                         };
1087
1088                         static DataTable instance;
1089                         static public DataTable Instance {
1090                                 get {
1091                                         if (instance == null) {
1092                                                 var newInstance = new DataTable ("MetaDataCollections");
1093                                                 foreach (ColumnInfo c in columns)
1094                                                         newInstance.Columns.Add (c.name, c.type);
1095                                                 foreach (object [] row in rows)
1096                                                         newInstance.LoadDataRow (row, true);
1097                                                 instance = newInstance;
1098                                         }
1099                                         return instance;
1100                                 }
1101                         }
1102                 }
1103
1104                 static class DataSourceInformation
1105                 {
1106                         static readonly ColumnInfo [] columns = {
1107                                 new ColumnInfo ("CompositeIdentifierSeparatorPattern", typeof (string)),
1108                                 new ColumnInfo ("DataSourceProductName", typeof(string)),
1109                                 new ColumnInfo ("DataSourceProductVersion", typeof(string)),
1110                                 new ColumnInfo ("DataSourceProductVersionNormalized", typeof(string)),
1111                                 new ColumnInfo ("GroupByBehavior", typeof(GroupByBehavior)),
1112                                 new ColumnInfo ("IdentifierPattern", typeof(string)),
1113                                 new ColumnInfo ("IdentifierCase", typeof(IdentifierCase)),
1114                                 new ColumnInfo ("OrderByColumnsInSelect", typeof(bool)),
1115                                 new ColumnInfo ("ParameterMarkerFormat", typeof(string)),
1116                                 new ColumnInfo ("ParameterMarkerPattern", typeof(string)),
1117                                 new ColumnInfo ("ParameterNameMaxLength", typeof(int)),
1118                                 new ColumnInfo ("ParameterNamePattern", typeof(string)),
1119                                 new ColumnInfo ("QuotedIdentifierPattern", typeof(string)),
1120                                 new ColumnInfo ("QuotedIdentifierCase", typeof(IdentifierCase)),
1121                                 new ColumnInfo ("StatementSeparatorPattern", typeof(string)),
1122                                 new ColumnInfo ("StringLiteralPattern", typeof(string)),
1123                                 new ColumnInfo ("SupportedJoinOperators", typeof(SupportedJoinOperators))
1124                         };
1125
1126                         static public DataTable GetInstance (SqlConnection conn)
1127                         {
1128                                 DataTable table = new DataTable ("DataSourceInformation");
1129                                 foreach (ColumnInfo c in columns)
1130                                         table.Columns.Add (c.name, c.type);
1131                                 DataRow row = table.NewRow ();
1132                                 row [0] = "\\.";
1133                                 row [1] = "Microsoft SQL Server";
1134                                 row [2] = conn.ServerVersion;;
1135                                 row [3] = conn.ServerVersion;;
1136                                 row [4] = GroupByBehavior.Unrelated;
1137                                 row [5] = @"(^\[\p{Lo}\p{Lu}\p{Ll}_@#][\p{Lo}\p{Lu}\p{Ll}\p{Nd}@$#_]*$)|(^\[[^\]\0]|\]\]+\]$)|(^\""[^\""\0]|\""\""+\""$)";
1138                                 row [6] = IdentifierCase.Insensitive; // FIXME: obtain this from SQL Server
1139                                 row [7] = false;
1140                                 row [8] = "{0}";
1141                                 row [9] = @"@[\p{Lo}\p{Lu}\p{Ll}\p{Lm}_@#][\p{Lo}\p{Lu}\p{Ll}\p{Lm}\p{Nd}\uff3f_@#\$]*(?=\s+|$)";
1142                                 row [10] = 128;
1143                                 row [11] = @"^[\p{Lo}\p{Lu}\p{Ll}\p{Lm}_@#][\p{Lo}\p{Lu}\p{Ll}\p{Lm}\p{Nd}\uff3f_@#\$]*(?=\s+|$)";
1144                                 row [12] = @"(([^\[]|\]\])*)";
1145                                 row [13] = IdentifierCase.Insensitive;  // FIXME: obtain this from SQL Server
1146                                 row [14] =";";
1147                                 row [15] = "'(([^']|'')*)'";
1148                                 row [16] = (SupportedJoinOperators.FullOuter | SupportedJoinOperators.Inner |
1149                                         SupportedJoinOperators.LeftOuter | SupportedJoinOperators.RightOuter);
1150                                 table.Rows.Add (row);
1151                                 return table;
1152                         }
1153                 }
1154
1155                 static class DataTypes
1156                 {
1157                         static readonly ColumnInfo [] columns = {
1158                                 new ColumnInfo ("TypeName", typeof(string)),
1159                                 new ColumnInfo ("ProviderDbType", typeof(int)),
1160                                 new ColumnInfo ("ColumnSize", typeof(long)),
1161                                 new ColumnInfo ("CreateFormat", typeof(string)),
1162                                 new ColumnInfo ("CreateParameters", typeof(string)),
1163                                 new ColumnInfo ("DataType", typeof(string)),
1164                                 new ColumnInfo ("IsAutoIncrementable", typeof(bool)),
1165                                 new ColumnInfo ("IsBestMatch", typeof(bool)),
1166                                 new ColumnInfo ("IsCaseSensitive", typeof(bool)),
1167                                 new ColumnInfo ("IsFixedLength", typeof(bool)),
1168                                 new ColumnInfo ("IsFixedPrecisionScale", typeof(bool)),
1169                                 new ColumnInfo ("IsLong", typeof(bool)),
1170                                 new ColumnInfo ("IsNullable", typeof(bool)),
1171                                 new ColumnInfo ("IsSearchable", typeof(bool)),
1172                                 new ColumnInfo ("IsSearchableWithLike", typeof(bool)),
1173                                 new ColumnInfo ("IsUnsigned", typeof(bool)),
1174                                 new ColumnInfo ("MaximumScale", typeof(short)),
1175                                 new ColumnInfo ("MinimumScale", typeof(short)),
1176                                 new ColumnInfo ("IsConcurrencyType", typeof(bool)),
1177                                 new ColumnInfo ("IsLiteralSupported", typeof(bool)),
1178                                 new ColumnInfo ("LiteralPrefix", typeof(string)),
1179                                 new ColumnInfo ("LiteralSuffix", typeof(string))
1180                         };
1181
1182                         static readonly object [][] rows = {
1183                                 new object [] {"smallint", 16, 5, "smallint", null, "System.Int16", true, true,
1184                                                false, true, true, false, true, true, false, false, null,
1185                                                null, false, null, null, null},
1186                                 new object [] {"int", 8, 10, "int", null, "System.Int32",
1187                                                true, true, false, true, true, false, true, true, false,
1188                                                false, null, null, false, null, null, null},
1189                                 new object [] {"real", 13, 7, "real", null,
1190                                                "System.Single", false, true, false, true, false, false,
1191                                                true, true, false, false, null, null, false, null, null, null},
1192                                 new object [] {"float", 6, 53, "float({0})",
1193                                                "number of bits used to store the mantissa", "System.Double",
1194                                                false, true, false, true, false, false, true, true,
1195                                                false, false, null, null, false, null, null, null},
1196                                 new object [] {"money", 9, 19, "money", null,
1197                                                "System.Decimal", false, false, false, true, true,
1198                                                false, true, true, false, false, null, null, false,
1199                                                null, null, null},
1200                                 new object [] {"smallmoney", 17, 10, "smallmoney", null,
1201                                                "System.Decimal", false, false, false, true, true, false,
1202                                                true, true, false, false, null, null, false, null, null, null},
1203                                 new object [] {"bit", 2, 1, "bit", null, "System.Boolean",
1204                                                false, false, false, true, false, false, true, true,
1205                                                false, null, null, null, false, null, null, null},
1206                                 new object [] {"tinyint", 20, 3, "tinyint", null,
1207                                                "System.SByte", true, true, false, true, true, false,
1208                                                true, true, false, true, null, null, false, null, null, null},
1209                                 new object [] {"bigint", 0, 19, "bigint", null,
1210                                                "System.Int64", true, true, false, true, true, false,
1211                                                true, true, false, false, null, null, false, null, null, null},
1212                                 new object [] {"timestamp", 19, 8, "timestamp", null,
1213                                                "System.Byte[]", false, false, false, true, false, false,
1214                                                false, true, false, null, null, null, true, null, "0x", null},
1215                                 new object [] {"binary", 1, 8000, "binary({0})", "length",
1216                                                "System.Byte[]", false, true, false, true, false, false,
1217                                                true, true, false, null, null, null, false, null, "0x", null},
1218                                 new object [] {"image", 7, 2147483647, "image", null,
1219                                                "System.Byte[]", false, true, false, false, false, true,
1220                                                true, false, false, null, null, null, false, null, "0x", null},
1221                                 new object [] {"text", 18, 2147483647, "text", null,
1222                                                "System.String", false, true, false, false, false, true,
1223                                                true, false, true, null, null, null, false, null, "'", "'"},
1224                                 new object [] {"ntext", 11, 1073741823, "ntext", null,
1225                                                "System.String", false, true, false, false, false, true,
1226                                                true, false, true, null, null, null, false, null, "N'", "'"},
1227                                 new object [] {"decimal", 5, 38, "decimal({0}, {1})",
1228                                                "precision,scale", "System.Decimal", true, true, false,
1229                                                true, false, false, true, true, false, false, 38, 0,
1230                                                false, null, null, null},
1231                                 new object [] {"numeric", 5, 38, "numeric({0}, {1})",
1232                                                "precision,scale", "System.Decimal", true, true, false,
1233                                                true, false, false, true, true, false, false, 38, 0,
1234                                                false, null, null, null},
1235                                 new object [] {"datetime", 4, 23, "datetime", null,
1236                                                "System.DateTime", false, true, false, true, false, false,
1237                                                true, true, true, null, null, null, false, null, "{ts '", "'}"},
1238                                 new object [] {"smalldatetime", 15, 16, "smalldatetime", null,
1239                                                "System.DateTime", false, true, false, true, false, false,
1240                                                true, true, true, null, null, null, false, null, "{ts '", "'}"},
1241                                 new object [] {"sql_variant", 23, null, "sql_variant",
1242                                                null, "System.Object", false, true, false, false, false,
1243                                                false, true, true, false, null, null, null, false, false,
1244                                                null, null},
1245                                 new object [] {"xml", 25, 2147483647, "xml", null,
1246                                                "System.String", false, false, false, false, false, true,
1247                                                true, false, false, null, null, null, false, false, null, null},
1248                                 new object [] {"varchar", 22, 2147483647, "varchar({0})",
1249                                                "max length", "System.String", false, true, false, false,
1250                                                false, false, true, true, true, null, null, null, false,
1251                                                null, "'", "'"},
1252                                 new object [] {"char", 3, 2147483647, "char({0})", "length",
1253                                                "System.String", false, true, false, true, false, false,
1254                                                true, true, true, null, null, null, false, null, "'", "'"},
1255                                 new object [] {"nchar", 10, 1073741823, "nchar({0})", "length",
1256                                                "System.String", false, true, false, true, false, false,
1257                                                true, true, true, null, null, null, false, null, "N'", "'"},
1258                                 new object [] {"nvarchar", 12, 1073741823, "nvarchar({0})", "max length",
1259                                                "System.String", false, true, false, false, false, false, true, true,
1260                                                true, null, null, null, false, null, "N'", "'"},
1261                                 new object [] {"varbinary", 21, 1073741823, "varbinary({0})",
1262                                                "max length", "System.Byte[]", false, true, false, false,
1263                                                false, false, true, true, false, null, null, null, false,
1264                                                null, "0x", null},
1265                                 new object [] {"uniqueidentifier", 14, 16, "uniqueidentifier", null,
1266                                                "System.Guid", false, true, false, true, false, false, true,
1267                                                true, false, null, null, null, false, null, "'", "'"},
1268                                 new object [] {"date", 31, 3L, "date", DBNull.Value,
1269                                                "System.DateTime", false, false, false, true, true, false,
1270                                                true, true, true, DBNull.Value, DBNull.Value, DBNull.Value,
1271                                                false, DBNull.Value, "{ts '", "'}"},
1272                                 new object [] {"time", 32, 5L, "time({0})", "scale",
1273                                                "System.TimeSpan", false, false, false, false, false, false,
1274                                                true, true, true, DBNull.Value, (short) 7, (short) 0,
1275                                                false, DBNull.Value, "{ts '", "'}"},
1276                                 new object [] {"datetime2", 33, 8L, "datetime2({0})", "scale",
1277                                                "System.DateTime", false, true, false, false, false, false,
1278                                                true, true, true, DBNull.Value, (short) 7, (short) 0,
1279                                                false, DBNull.Value, "{ts '", "'}"},
1280                                 new object [] {"datetimeoffset", 34, 10L, "datetimeoffset({0})",
1281                                                "scale", "System.DateTimeOffset", false, true, false, false,
1282                                                false, false, true, true, true, DBNull.Value, (short) 7, (short) 0,
1283                                                false, DBNull.Value, "{ts '", "'}"}
1284                         };
1285
1286                         static DataTable instance;
1287                         static public DataTable Instance {
1288                                 get {
1289                                         if (instance == null) {
1290                                                 instance = new DataTable ("DataTypes");
1291                                                 foreach (ColumnInfo c in columns)
1292                                                         instance.Columns.Add (c.name, c.type);
1293                                                 foreach (object [] row in rows)
1294                                                         instance.LoadDataRow (row, true);
1295                                         }
1296                                         return instance;
1297                                 }
1298                         }
1299                 }
1300
1301                 static class Restrictions
1302                 {
1303                         static readonly ColumnInfo [] columns = {
1304                                 new ColumnInfo ("CollectionName", typeof (string)),
1305                                 new ColumnInfo ("RestrictionName", typeof(string)),
1306                                 new ColumnInfo ("ParameterName", typeof(string)),
1307                                 new ColumnInfo ("RestrictionDefault", typeof(string)),
1308                                 new ColumnInfo ("RestrictionNumber", typeof(int))
1309                         };
1310
1311                         static readonly object [][] rows = {
1312                                 new object [] {"Users", "User_Name", "@Name", "name", 1},
1313                                 new object [] {"Databases", "Name", "@Name", "Name", 1},
1314
1315                                 new object [] {"Tables", "Catalog", "@Catalog", "TABLE_CATALOG", 1},
1316                                 new object [] {"Tables", "Owner", "@Owner", "TABLE_SCHEMA", 2},
1317                                 new object [] {"Tables", "Table", "@Name", "TABLE_NAME", 3},
1318                                 new object [] {"Tables", "TableType", "@TableType", "TABLE_TYPE", 4},
1319
1320                                 new object [] {"Columns", "Catalog", "@Catalog", "TABLE_CATALOG", 1},
1321                                 new object [] {"Columns", "Owner", "@Owner", "TABLE_SCHEMA", 2},
1322                                 new object [] {"Columns", "Table", "@Table", "TABLE_NAME", 3},
1323                                 new object [] {"Columns", "Column", "@Column", "COLUMN_NAME", 4},
1324
1325                                 new object [] {"StructuredTypeMembers", "Catalog", "@Catalog", "TYPE_CATALOG", 1},
1326                                 new object [] {"StructuredTypeMembers", "Owner", "@Owner", "TYPE_SCHEMA", 2},
1327                                 new object [] {"StructuredTypeMembers", "Type", "@Type", "TYPE_NAME", 3},
1328                                 new object [] {"StructuredTypeMembers", "Member", "@Member", "MEMBER_NAME", 4},
1329
1330                                 new object [] {"Views", "Catalog", "@Catalog", "TABLE_CATALOG", 1},
1331                                 new object [] {"Views", "Owner", "@Owner", "TABLE_SCHEMA", 2},
1332                                 new object [] {"Views", "Table", "@Table", "TABLE_NAME", 3},
1333
1334                                 new object [] {"ViewColumns", "Catalog", "@Catalog", "VIEW_CATALOG", 1},
1335                                 new object [] {"ViewColumns", "Owner", "@Owner", "VIEW_SCHEMA", 2},
1336                                 new object [] {"ViewColumns", "Table", "@Table", "VIEW_NAME", 3},
1337                                 new object [] {"ViewColumns", "Column", "@Column", "COLUMN_NAME", 4},
1338
1339                                 new object [] {"ProcedureParameters", "Catalog", "@Catalog", "SPECIFIC_CATALOG", 1},
1340                                 new object [] {"ProcedureParameters", "Owner", "@Owner", "SPECIFIC_SCHEMA", 2},
1341                                 new object [] {"ProcedureParameters", "Name", "@Name", "SPECIFIC_NAME", 3},
1342                                 new object [] {"ProcedureParameters", "Parameter", "@Parameter", "PARAMETER_NAME", 4},
1343
1344                                 new object [] {"Procedures", "Catalog", "@Catalog", "SPECIFIC_CATALOG", 1},
1345                                 new object [] {"Procedures", "Owner", "@Owner", "SPECIFIC_SCHEMA", 2},
1346                                 new object [] {"Procedures", "Name", "@Name", "SPECIFIC_NAME", 3},
1347                                 new object [] {"Procedures", "Type", "@Type", "ROUTINE_TYPE", 4},
1348
1349                                 new object [] {"IndexColumns", "Catalog", "@Catalog", "db_name()", 1},
1350                                 new object [] {"IndexColumns", "Owner", "@Owner", "user_name()", 2},
1351                                 new object [] {"IndexColumns", "Table", "@Table", "o.name", 3},
1352                                 new object [] {"IndexColumns", "ConstraintName", "@ConstraintName", "x.name", 4},
1353                                 new object [] {"IndexColumns", "Column", "@Column", "c.name", 5},
1354
1355                                 new object [] {"Indexes", "Catalog", "@Catalog", "db_name()", 1},
1356                                 new object [] {"Indexes", "Owner", "@Owner", "user_name()", 2},
1357                                 new object [] {"Indexes", "Table", "@Table", "o.name", 3},
1358                                 new object [] {"Indexes", "Name", "@Name", "x.name", 4},
1359
1360                                 new object [] {"UserDefinedTypes", "assembly_name", "@AssemblyName", "assemblies.name", 1},
1361                                 new object [] {"UserDefinedTypes", "udt_name", "@UDTName", "types.assembly_class", 2},
1362
1363                                 new object [] {"ForeignKeys", "Catalog", "@Catalog", "CONSTRAINT_CATALOG", 1},
1364                                 new object [] {"ForeignKeys", "Owner", "@Owner", "CONSTRAINT_SCHEMA", 2},
1365                                 new object [] {"ForeignKeys", "Table", "@Table", "TABLE_NAME", 3},
1366                                 new object [] {"ForeignKeys", "Name", "@Name", "CONSTRAINT_NAME", 4}
1367                         };
1368
1369                         static DataTable instance;
1370                         static public DataTable Instance {
1371                                 get {
1372                                         if (instance == null) {
1373                                                 instance = new DataTable ("Restrictions");
1374                                                 foreach (ColumnInfo c in columns)
1375                                                         instance.Columns.Add (c.name, c.type);
1376                                                 foreach (object [] row in rows)
1377                                                         instance.LoadDataRow (row, true);
1378                                         }
1379                                         return instance;
1380                                 }
1381                         }
1382                 }
1383
1384                 public override DataTable GetSchema ()
1385                 {
1386                         if (state == ConnectionState.Closed)
1387                                 throw ExceptionHelper.ConnectionClosed ();
1388
1389                         return MetaDataCollections.Instance;
1390                 }
1391
1392                 public override DataTable GetSchema (String collectionName)
1393                 {
1394                         return GetSchema (collectionName, null);
1395                 }
1396
1397                 public override DataTable GetSchema (String collectionName, string [] restrictionValues)
1398                 {
1399                         // LAMESPEC: In MS.NET, if collectionName is null, it throws ArgumentException.
1400
1401                         if (state == ConnectionState.Closed)
1402                                 throw ExceptionHelper.ConnectionClosed ();
1403
1404                         String cName = null;
1405                         DataTable schemaTable = MetaDataCollections.Instance;
1406                         int length = restrictionValues == null ? 0 : restrictionValues.Length;
1407
1408                         foreach (DataRow row in schemaTable.Rows) {
1409                                 if (String.Compare ((string) row["CollectionName"], collectionName, true) == 0) {
1410                                         if (length > (int) row["NumberOfRestrictions"]) {
1411                                                 throw new ArgumentException ("More restrictions were provided " +
1412                                                                              "than the requested schema ('" +
1413                                                                              row["CollectionName"].ToString () + "') supports");
1414                                         }
1415                                         cName = row["CollectionName"].ToString();
1416                                 }
1417                         }
1418
1419                         if (cName == null)
1420                                 throw new ArgumentException (string.Format (CultureInfo.InvariantCulture,
1421                                         "The requested collection ({0}) is not defined.",
1422                                         collectionName));
1423
1424                         SqlCommand command     = null;
1425                         DataTable dataTable    = new DataTable ();
1426                         SqlDataAdapter dataAdapter = new SqlDataAdapter ();
1427
1428                         switch (cName)
1429                         {
1430                         case "Databases":
1431                                 command = new SqlCommand ("select name as database_name, dbid, crdate as create_date " +
1432                                                           "from master.sys.sysdatabases where (name = @Name or (@Name " +
1433                                                           "is null))", this);
1434                                 command.Parameters.Add ("@Name", SqlDbType.NVarChar, 4000);
1435                                 break;
1436                         case "ForeignKeys":
1437                                 command = new SqlCommand ("select CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME, " +
1438                                                           "TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, CONSTRAINT_TYPE, " +
1439                                                           "IS_DEFERRABLE, INITIALLY_DEFERRED from " +
1440                                                           "INFORMATION_SCHEMA.TABLE_CONSTRAINTS where (CONSTRAINT_CATALOG" +
1441                                                           " = @Catalog or (@Catalog is null)) and (CONSTRAINT_SCHEMA = " +
1442                                                           "@Owner or (@Owner is null)) and (TABLE_NAME = @Table or (" +
1443                                                           "@Table is null)) and (CONSTRAINT_NAME = @Name or (@Name is null))" +
1444                                                           " and CONSTRAINT_TYPE = 'FOREIGN KEY' order by CONSTRAINT_CATALOG," +
1445                                                           " CONSTRAINT_SCHEMA, CONSTRAINT_NAME", this);
1446                                 command.Parameters.Add ("@Catalog", SqlDbType.NVarChar, 4000);
1447                                 command.Parameters.Add ("@Owner", SqlDbType.NVarChar, 4000);
1448                                 command.Parameters.Add ("@Table", SqlDbType.NVarChar, 4000);
1449                                 command.Parameters.Add ("@Name", SqlDbType.NVarChar, 4000);
1450                                 break;
1451                         case "Indexes":
1452                                 command = new SqlCommand ("select distinct db_name() as constraint_catalog, " +
1453                                                           "constraint_schema = user_name (o.uid), " +
1454                                                           "constraint_name = x.name, table_catalog = db_name (), " +
1455                                                           "table_schema = user_name (o.uid), table_name = o.name, " +
1456                                                           "index_name  = x.name from sysobjects o, sysindexes x, " +
1457                                                           "sysindexkeys xk where o.type in ('U') and x.id = o.id and " +
1458                                                           "o.id = xk.id and x.indid = xk.indid and xk.keyno = x.keycnt " +
1459                                                           "and (db_name() = @Catalog or (@Catalog is null)) and " +
1460                                                           "(user_name() = @Owner or (@Owner is null)) and (o.name = " +
1461                                                           "@Table or (@Table is null)) and (x.name = @Name or (@Name is null))" +
1462                                                           "order by table_name, index_name", this);
1463                                 command.Parameters.Add ("@Catalog", SqlDbType.NVarChar, 4000);
1464                                 command.Parameters.Add ("@Owner", SqlDbType.NVarChar, 4000);
1465                                 command.Parameters.Add ("@Table", SqlDbType.NVarChar, 4000);
1466                                 command.Parameters.Add ("@Name", SqlDbType.NVarChar, 4000);
1467                                 break;
1468                         case "IndexColumns":
1469                                 command = new SqlCommand ("select distinct db_name() as constraint_catalog, " +
1470                                                           "constraint_schema = user_name (o.uid), constraint_name = x.name, " +
1471                                                           "table_catalog = db_name (), table_schema = user_name (o.uid), " +
1472                                                           "table_name = o.name, column_name = c.name, " +
1473                                                           "ordinal_position = convert (int, xk.keyno), keyType = c.xtype, " +
1474                                                           "index_name = x.name from sysobjects o, sysindexes x, syscolumns c, " +
1475                                                           "sysindexkeys xk where o.type in ('U') and x.id = o.id and o.id = c.id " +
1476                                                           "and o.id = xk.id and x.indid = xk.indid and c.colid = xk.colid " +
1477                                                           "and xk.keyno <= x.keycnt and permissions (o.id, c.name) <> 0 " +
1478                                                           "and (db_name() = @Catalog or (@Catalog is null)) and (user_name() " +
1479                                                           "= @Owner or (@Owner is null)) and (o.name = @Table or (@Table is" +
1480                                                           " null)) and (x.name = @ConstraintName or (@ConstraintName is null)) " +
1481                                                           "and (c.name = @Column or (@Column is null)) order by table_name, " +
1482                                                           "index_name", this);
1483                                 command.Parameters.Add ("@Catalog", SqlDbType.NVarChar, 8);
1484                                 command.Parameters.Add ("@Owner", SqlDbType.NVarChar, 4000);
1485                                 command.Parameters.Add ("@Table", SqlDbType.NVarChar, 13);
1486                                 command.Parameters.Add ("@ConstraintName", SqlDbType.NVarChar, 4000);
1487                                 command.Parameters.Add ("@Column", SqlDbType.NVarChar, 4000);
1488                                 break;
1489                         case "Procedures":
1490                                 command = new SqlCommand ("select SPECIFIC_CATALOG, SPECIFIC_SCHEMA, SPECIFIC_NAME, " +
1491                                                           "ROUTINE_CATALOG, ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE, " +
1492                                                           "CREATED, LAST_ALTERED from INFORMATION_SCHEMA.ROUTINES where " +
1493                                                           "(SPECIFIC_CATALOG = @Catalog or (@Catalog is null)) and " +
1494                                                           "(SPECIFIC_SCHEMA = @Owner or (@Owner is null)) and (SPECIFIC_NAME" +
1495                                                           " = @Name or (@Name is null)) and (ROUTINE_TYPE = @Type or (@Type " +
1496                                                           "is null)) order by SPECIFIC_CATALOG, SPECIFIC_SCHEMA, SPECIFIC_NAME", this);
1497                                 command.Parameters.Add ("@Catalog", SqlDbType.NVarChar, 4000);
1498                                 command.Parameters.Add ("@Owner", SqlDbType.NVarChar, 4000);
1499                                 command.Parameters.Add ("@Name", SqlDbType.NVarChar, 4000);
1500                                 command.Parameters.Add ("@Type", SqlDbType.NVarChar, 4000);
1501                                 break;
1502                         case "ProcedureParameters":
1503                                 command = new SqlCommand ("select SPECIFIC_CATALOG, SPECIFIC_SCHEMA, SPECIFIC_NAME, " +
1504                                                           "ORDINAL_POSITION, PARAMETER_MODE, IS_RESULT, AS_LOCATOR, " +
1505                                                           "PARAMETER_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, " +
1506                                                           "CHARACTER_OCTET_LENGTH, COLLATION_CATALOG, COLLATION_SCHEMA, " +
1507                                                           "COLLATION_NAME, CHARACTER_SET_CATALOG, CHARACTER_SET_SCHEMA, " +
1508                                                           "CHARACTER_SET_NAME, NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX, " +
1509                                                           "NUMERIC_SCALE, DATETIME_PRECISION, INTERVAL_TYPE, " +
1510                                                           "INTERVAL_PRECISION from INFORMATION_SCHEMA.PARAMETERS where " +
1511                                                           "(SPECIFIC_CATALOG = @Catalog or (@Catalog is null)) and " +
1512                                                           "(SPECIFIC_SCHEMA = @Owner or (@Owner is null)) and (SPECIFIC_NAME = " +
1513                                                           "@Name or (@Name is null)) and (PARAMETER_NAME = @Parameter or (" +
1514                                                           "@Parameter is null)) order by SPECIFIC_CATALOG, SPECIFIC_SCHEMA," +
1515                                                           " SPECIFIC_NAME, PARAMETER_NAME", this);
1516                                 command.Parameters.Add ("@Catalog", SqlDbType.NVarChar, 4000);
1517                                 command.Parameters.Add ("@Owner", SqlDbType.NVarChar, 4000);
1518                                 command.Parameters.Add ("@Name", SqlDbType.NVarChar, 4000);
1519                                 command.Parameters.Add ("@Parameter", SqlDbType.NVarChar, 4000);
1520                                 break;
1521                         case "Tables":
1522                                 command = new SqlCommand ("select TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE " +
1523                                                           "from INFORMATION_SCHEMA.TABLES where" +
1524                                                           " (TABLE_CATALOG = @catalog or (@catalog is null)) and " +
1525                                                           "(TABLE_SCHEMA = @owner or (@owner is null))and " +
1526                                                           "(TABLE_NAME = @name or (@name is null)) and " +
1527                                                           "(TABLE_TYPE = @table_type or (@table_type is null))", this);
1528                                 command.Parameters.Add ("@catalog", SqlDbType.NVarChar, 8);
1529                                 command.Parameters.Add ("@owner", SqlDbType.NVarChar, 3);
1530                                 command.Parameters.Add ("@name", SqlDbType.NVarChar, 11);
1531                                 command.Parameters.Add ("@table_type", SqlDbType.NVarChar, 10);
1532                                 break;
1533                         case "Columns":
1534                                 command = new SqlCommand ("select TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, " +
1535                                                           "ORDINAL_POSITION, COLUMN_DEFAULT, IS_NULLABLE, DATA_TYPE, " +
1536                                                           "CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH, " +
1537                                                           "NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX, NUMERIC_SCALE, " +
1538                                                           "DATETIME_PRECISION, CHARACTER_SET_CATALOG, CHARACTER_SET_SCHEMA, " +
1539                                                           "CHARACTER_SET_NAME, COLLATION_CATALOG from INFORMATION_SCHEMA.COLUMNS" +
1540                                                           " where (TABLE_CATALOG = @Catalog or (@Catalog is null)) and (" +
1541                                                           "TABLE_SCHEMA = @Owner or (@Owner is null)) and (TABLE_NAME = @table" +
1542                                                           " or (@Table is null)) and (COLUMN_NAME = @column or (@Column is null" +
1543                                                           ")) order by TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME", this);
1544                                 command.Parameters.Add ("@Catalog", SqlDbType.NVarChar, 4000);
1545                                 command.Parameters.Add ("@Owner", SqlDbType.NVarChar, 4000);
1546                                 command.Parameters.Add ("@Table", SqlDbType.NVarChar, 4000);
1547                                 command.Parameters.Add ("@Column", SqlDbType.NVarChar, 4000);
1548                                 break;
1549                         case "Users":
1550                                 command = new SqlCommand ("select uid, name as user_name, createdate, updatedate from sysusers" +
1551                                                           " where (name = @Name or (@Name is null))", this);
1552                                 command.Parameters.Add ("@Name", SqlDbType.NVarChar, 4000);
1553                                 break;
1554                         case "StructuredTypeMembers":
1555                                 // Only available on SQL Server 2008
1556                                 // Running it again SQL 2005 results in the following exception:
1557                                 // Unable to build the 'StructuredTypeMembers' collection because
1558                                 // execution of the SQL query failed. See the inner exception for details.
1559                                 // ---> System.Data.SqlClient.SqlException: Invalid object name 'sys.table_types'.
1560                                 //
1561                                 // I don't have access to SQL Server 2008 right now,
1562                                 // and can't find any online documentation on the 'sys.table_types'
1563                                 // view
1564                                 throw new NotImplementedException ();
1565                         case "Views":
1566                                 command = new SqlCommand ("select TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, CHECK_OPTION, " +
1567                                                           "IS_UPDATABLE from INFORMATION_SCHEMA.VIEWS where (TABLE_CATALOG" +
1568                                                           " = @Catalog or (@Catalog is null)) TABLE_SCHEMA = @Owner or " +
1569                                                           "(@Owner is null)) and (TABLE_NAME = @table or (@Table is null))" +
1570                                                           " order by TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME", this);
1571                                 command.Parameters.Add ("@Catalog", SqlDbType.NVarChar, 4000);
1572                                 command.Parameters.Add ("@Owner", SqlDbType.NVarChar, 4000);
1573                                 command.Parameters.Add ("@Table", SqlDbType.NVarChar, 4000);
1574                                 break;
1575                         case "ViewColumns":
1576                                 command = new SqlCommand ("select VIEW_CATALOG, VIEW_SCHEMA, VIEW_NAME, TABLE_CATALOG, " +
1577                                                           "TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME from " +
1578                                                           "INFORMATION_SCHEMA.VIEW_COLUMN_USAGE where (VIEW_CATALOG = " +
1579                                                           "@Catalog (@Catalog is null)) and (VIEW_SCHEMA = @Owner (@Owner" +
1580                                                           " is null)) and (VIEW_NAME = @Table or (@Table is null)) and " +
1581                                                           "(COLUMN_NAME = @Column or (@Column is null)) order by " +
1582                                                           "VIEW_CATALOG, VIEW_SCHEMA, VIEW_NAME", this);
1583                                 command.Parameters.Add ("@Catalog", SqlDbType.NVarChar, 4000);
1584                                 command.Parameters.Add ("@Owner", SqlDbType.NVarChar, 4000);
1585                                 command.Parameters.Add ("@Table", SqlDbType.NVarChar, 4000);
1586                                 command.Parameters.Add ("@Column", SqlDbType.NVarChar, 4000);
1587                                 break;
1588                         case "UserDefinedTypes":
1589                                 command = new SqlCommand ("select assemblies.name as assembly_name, types.assembly_class " +
1590                                                           "as udt_name, ASSEMBLYPROPERTY(assemblies.name, 'VersionMajor') " +
1591                                                           "as version_major, ASSEMBLYPROPERTY(assemblies.name, 'VersionMinor') " +
1592                                                           "as version_minor, ASSEMBLYPROPERTY(assemblies.name, 'VersionBuild') " +
1593                                                           "as version_build, ASSEMBLYPROPERTY(assemblies.name, 'VersionRevision') " +
1594                                                           "as version_revision, ASSEMBLYPROPERTY(assemblies.name, 'CultureInfo') " +
1595                                                           "as culture_info, ASSEMBLYPROPERTY(assemblies.name, 'PublicKey') " +
1596                                                           "as public_key, is_fixed_length, max_length, Create_Date, " +
1597                                                           "Permission_set_desc from sys.assemblies as assemblies join " +
1598                                                           "sys.assembly_types as types on assemblies.assembly_id = types.assembly_id" +
1599                                                           " where (assportemblies.name = @AssemblyName or (@AssemblyName is null)) and " +
1600                                                           "(types.assembly_class = @UDTName or (@UDTName is null))",
1601                                                           this);
1602                                 command.Parameters.Add ("@AssemblyName", SqlDbType.NVarChar, 4000);
1603                                 command.Parameters.Add ("@UDTName", SqlDbType.NVarChar, 4000);
1604                                 break;
1605                         case "MetaDataCollections":
1606                                 return MetaDataCollections.Instance;
1607                         case "DataSourceInformation":
1608                                 return DataSourceInformation.GetInstance (this);
1609                         case "DataTypes":
1610                                 return DataTypes.Instance;
1611                         case "ReservedWords":
1612                                 return ReservedWords.Instance;
1613                         case "Restrictions":
1614                                 return Restrictions.Instance;
1615                         }
1616                         for (int i = 0; i < length; i++) {
1617                                 command.Parameters[i].Value = restrictionValues[i];
1618                         }
1619                         dataAdapter.SelectCommand = command;
1620                         dataAdapter.Fill (dataTable);
1621                         return dataTable;
1622                 }
1623
1624                 public static void ChangePassword (string connectionString, string newPassword)
1625                 {
1626                         if (String.IsNullOrEmpty (connectionString))
1627                                 throw new ArgumentNullException ("The 'connectionString' cannot be null or empty.");
1628                         if (String.IsNullOrEmpty (newPassword))
1629                                 throw new ArgumentNullException ("The 'newPassword' cannot be null or empty.");
1630                         if (newPassword.Length > 128)
1631                                 throw new ArgumentException ("The length of 'newPassword' cannot exceed 128 characters.");
1632                         using (SqlConnection conn = new SqlConnection (connectionString)) {
1633                                 conn.Open ();
1634                                 conn.tds.Execute (String.Format ("sp_password '{0}', '{1}', '{2}'",
1635                                                                  conn.parms.Password, newPassword, conn.parms.User));
1636                         }
1637                 }
1638
1639                 public static void ClearAllPools ()
1640                 {
1641                         // FIXME: locking
1642                         IDictionary pools = SqlConnection.sqlConnectionPools.GetConnectionPool ();
1643                         foreach (TdsConnectionPool pool in pools.Values) {
1644                                 if (pool != null)
1645                                         pool.ResetConnectionPool ();
1646                         }
1647                         pools.Clear ();
1648                 }
1649
1650                 public static void ClearPool (SqlConnection connection)
1651                 {
1652                         if (connection == null)
1653                                 throw new ArgumentNullException ("connection");
1654
1655                         // FIXME: locking
1656                         if (connection.pooling) {
1657                                 TdsConnectionPool pool = sqlConnectionPools.GetConnectionPool (connection.ConnectionString);
1658                                 if (pool != null)
1659                                         pool.ResetConnectionPool ();
1660                         }
1661                 }
1662
1663
1664                 #endregion // Methods
1665
1666                 #region Fields Net 2
1667
1668                 bool async;
1669                 bool userInstance;
1670
1671                 #endregion // Fields  Net 2
1672
1673                 #region Properties Net 2
1674
1675                 [DesignerSerializationVisibility (DesignerSerializationVisibility.Hidden)]
1676                 internal bool AsyncProcessing  {
1677                         get { return async; }
1678                 }
1679
1680                 #endregion // Properties Net 2
1681
1682
1683         }
1684 }