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