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