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