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