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