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

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