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                                                 var newInstance = new DataTable ("ReservedWords");
1107                                                 newInstance.Columns.Add ("ReservedWord", typeof(string));
1108                                                 foreach (string reservedWord in reservedWords)
1109                                                 {
1110                                                         row = newInstance.NewRow();
1111
1112                                                         row["ReservedWord"] = reservedWord;
1113                                                         newInstance.Rows.Add(row);
1114                                                 }
1115                                                 instance = newInstance;
1116                                         }
1117                                         return instance;
1118                                 }
1119                         }
1120                 }
1121
1122                 static class MetaDataCollections
1123                 {
1124                         static readonly ColumnInfo [] columns = {
1125                                 new ColumnInfo ("CollectionName", typeof (string)),
1126                                 new ColumnInfo ("NumberOfRestrictions", typeof (int)),
1127                                 new ColumnInfo ("NumberOfIdentifierParts", typeof (int))
1128                         };
1129
1130                         static readonly object [][] rows = {
1131                                 new object [] {"MetaDataCollections", 0, 0},
1132                                 new object [] {"DataSourceInformation", 0, 0},
1133                                 new object [] {"DataTypes", 0, 0},
1134                                 new object [] {"Restrictions", 0, 0},
1135                                 new object [] {"ReservedWords", 0, 0},
1136                                 new object [] {"Users", 1, 1},
1137                                 new object [] {"Databases", 1, 1},
1138                                 new object [] {"Tables", 4, 3},
1139                                 new object [] {"Columns", 4, 4},
1140                                 new object [] {"StructuredTypeMembers", 4, 4},
1141                                 new object [] {"Views", 3, 3},
1142                                 new object [] {"ViewColumns", 4, 4},
1143                                 new object [] {"ProcedureParameters", 4, 1},
1144                                 new object [] {"Procedures", 4, 3},
1145                                 new object [] {"ForeignKeys", 4, 3},
1146                                 new object [] {"IndexColumns", 5, 4},
1147                                 new object [] {"Indexes", 4, 3},
1148                                 new object [] {"UserDefinedTypes", 2, 1}
1149                         };
1150
1151                         static DataTable instance;
1152                         static public DataTable Instance {
1153                                 get {
1154                                         if (instance == null) {
1155                                                 var newInstance = new DataTable ("MetaDataCollections");
1156                                                 foreach (ColumnInfo c in columns)
1157                                                         newInstance.Columns.Add (c.name, c.type);
1158                                                 foreach (object [] row in rows)
1159                                                         newInstance.LoadDataRow (row, true);
1160                                                 instance = newInstance;
1161                                         }
1162                                         return instance;
1163                                 }
1164                         }
1165                 }
1166
1167                 static class DataSourceInformation
1168                 {
1169                         static readonly ColumnInfo [] columns = {
1170                                 new ColumnInfo ("CompositeIdentifierSeparatorPattern", typeof (string)),
1171                                 new ColumnInfo ("DataSourceProductName", typeof(string)),
1172                                 new ColumnInfo ("DataSourceProductVersion", typeof(string)),
1173                                 new ColumnInfo ("DataSourceProductVersionNormalized", typeof(string)),
1174                                 new ColumnInfo ("GroupByBehavior", typeof(GroupByBehavior)),
1175                                 new ColumnInfo ("IdentifierPattern", typeof(string)),
1176                                 new ColumnInfo ("IdentifierCase", typeof(IdentifierCase)),
1177                                 new ColumnInfo ("OrderByColumnsInSelect", typeof(bool)),
1178                                 new ColumnInfo ("ParameterMarkerFormat", typeof(string)),
1179                                 new ColumnInfo ("ParameterMarkerPattern", typeof(string)),
1180                                 new ColumnInfo ("ParameterNameMaxLength", typeof(int)),
1181                                 new ColumnInfo ("ParameterNamePattern", typeof(string)),
1182                                 new ColumnInfo ("QuotedIdentifierPattern", typeof(string)),
1183                                 new ColumnInfo ("QuotedIdentifierCase", typeof(IdentifierCase)),
1184                                 new ColumnInfo ("StatementSeparatorPattern", typeof(string)),
1185                                 new ColumnInfo ("StringLiteralPattern", typeof(string)),
1186                                 new ColumnInfo ("SupportedJoinOperators", typeof(SupportedJoinOperators))
1187                         };
1188
1189                         static public DataTable GetInstance (SqlConnection conn)
1190                         {
1191                                 DataTable table = new DataTable ("DataSourceInformation");
1192                                 foreach (ColumnInfo c in columns)
1193                                         table.Columns.Add (c.name, c.type);
1194                                 DataRow row = table.NewRow ();
1195                                 row [0] = "\\.";
1196                                 row [1] = "Microsoft SQL Server";
1197                                 row [2] = conn.ServerVersion;;
1198                                 row [3] = conn.ServerVersion;;
1199                                 row [4] = GroupByBehavior.Unrelated;
1200                                 row [5] = @"(^\[\p{Lo}\p{Lu}\p{Ll}_@#][\p{Lo}\p{Lu}\p{Ll}\p{Nd}@$#_]*$)|(^\[[^\]\0]|\]\]+\]$)|(^\""[^\""\0]|\""\""+\""$)";
1201                                 row [6] = IdentifierCase.Insensitive; // FIXME: obtain this from SQL Server
1202                                 row [7] = false;
1203                                 row [8] = "{0}";
1204                                 row [9] = @"@[\p{Lo}\p{Lu}\p{Ll}\p{Lm}_@#][\p{Lo}\p{Lu}\p{Ll}\p{Lm}\p{Nd}\uff3f_@#\$]*(?=\s+|$)";
1205                                 row [10] = 128;
1206                                 row [11] = @"^[\p{Lo}\p{Lu}\p{Ll}\p{Lm}_@#][\p{Lo}\p{Lu}\p{Ll}\p{Lm}\p{Nd}\uff3f_@#\$]*(?=\s+|$)";
1207                                 row [12] = @"(([^\[]|\]\])*)";
1208                                 row [13] = IdentifierCase.Insensitive;  // FIXME: obtain this from SQL Server
1209                                 row [14] =";";
1210                                 row [15] = "'(([^']|'')*)'";
1211                                 row [16] = (SupportedJoinOperators.FullOuter | SupportedJoinOperators.Inner |
1212                                         SupportedJoinOperators.LeftOuter | SupportedJoinOperators.RightOuter);
1213                                 table.Rows.Add (row);
1214                                 return table;
1215                         }
1216                 }
1217
1218                 static class DataTypes
1219                 {
1220                         static readonly ColumnInfo [] columns = {
1221                                 new ColumnInfo ("TypeName", typeof(string)),
1222                                 new ColumnInfo ("ProviderDbType", typeof(int)),
1223                                 new ColumnInfo ("ColumnSize", typeof(long)),
1224                                 new ColumnInfo ("CreateFormat", typeof(string)),
1225                                 new ColumnInfo ("CreateParameters", typeof(string)),
1226                                 new ColumnInfo ("DataType", typeof(string)),
1227                                 new ColumnInfo ("IsAutoIncrementable", typeof(bool)),
1228                                 new ColumnInfo ("IsBestMatch", typeof(bool)),
1229                                 new ColumnInfo ("IsCaseSensitive", typeof(bool)),
1230                                 new ColumnInfo ("IsFixedLength", typeof(bool)),
1231                                 new ColumnInfo ("IsFixedPrecisionScale", typeof(bool)),
1232                                 new ColumnInfo ("IsLong", typeof(bool)),
1233                                 new ColumnInfo ("IsNullable", typeof(bool)),
1234                                 new ColumnInfo ("IsSearchable", typeof(bool)),
1235                                 new ColumnInfo ("IsSearchableWithLike", typeof(bool)),
1236                                 new ColumnInfo ("IsUnsigned", typeof(bool)),
1237                                 new ColumnInfo ("MaximumScale", typeof(short)),
1238                                 new ColumnInfo ("MinimumScale", typeof(short)),
1239                                 new ColumnInfo ("IsConcurrencyType", typeof(bool)),
1240                                 new ColumnInfo ("IsLiteralSupported", typeof(bool)),
1241                                 new ColumnInfo ("LiteralPrefix", typeof(string)),
1242                                 new ColumnInfo ("LiteralSuffix", typeof(string))
1243                         };
1244
1245                         static readonly object [][] rows = {
1246                                 new object [] {"smallint", 16, 5, "smallint", null, "System.Int16", true, true,
1247                                                false, true, true, false, true, true, false, false, null,
1248                                                null, false, null, null, null},
1249                                 new object [] {"int", 8, 10, "int", null, "System.Int32",
1250                                                true, true, false, true, true, false, true, true, false,
1251                                                false, null, null, false, null, null, null},
1252                                 new object [] {"real", 13, 7, "real", null,
1253                                                "System.Single", false, true, false, true, false, false,
1254                                                true, true, false, false, null, null, false, null, null, null},
1255                                 new object [] {"float", 6, 53, "float({0})",
1256                                                "number of bits used to store the mantissa", "System.Double",
1257                                                false, true, false, true, false, false, true, true,
1258                                                false, false, null, null, false, null, null, null},
1259                                 new object [] {"money", 9, 19, "money", null,
1260                                                "System.Decimal", false, false, false, true, true,
1261                                                false, true, true, false, false, null, null, false,
1262                                                null, null, null},
1263                                 new object [] {"smallmoney", 17, 10, "smallmoney", null,
1264                                                "System.Decimal", false, false, false, true, true, false,
1265                                                true, true, false, false, null, null, false, null, null, null},
1266                                 new object [] {"bit", 2, 1, "bit", null, "System.Boolean",
1267                                                false, false, false, true, false, false, true, true,
1268                                                false, null, null, null, false, null, null, null},
1269                                 new object [] {"tinyint", 20, 3, "tinyint", null,
1270                                                "System.SByte", true, true, false, true, true, false,
1271                                                true, true, false, true, null, null, false, null, null, null},
1272                                 new object [] {"bigint", 0, 19, "bigint", null,
1273                                                "System.Int64", true, true, false, true, true, false,
1274                                                true, true, false, false, null, null, false, null, null, null},
1275                                 new object [] {"timestamp", 19, 8, "timestamp", null,
1276                                                "System.Byte[]", false, false, false, true, false, false,
1277                                                false, true, false, null, null, null, true, null, "0x", null},
1278                                 new object [] {"binary", 1, 8000, "binary({0})", "length",
1279                                                "System.Byte[]", false, true, false, true, false, false,
1280                                                true, true, false, null, null, null, false, null, "0x", null},
1281                                 new object [] {"image", 7, 2147483647, "image", null,
1282                                                "System.Byte[]", false, true, false, false, false, true,
1283                                                true, false, false, null, null, null, false, null, "0x", null},
1284                                 new object [] {"text", 18, 2147483647, "text", null,
1285                                                "System.String", false, true, false, false, false, true,
1286                                                true, false, true, null, null, null, false, null, "'", "'"},
1287                                 new object [] {"ntext", 11, 1073741823, "ntext", null,
1288                                                "System.String", false, true, false, false, false, true,
1289                                                true, false, true, null, null, null, false, null, "N'", "'"},
1290                                 new object [] {"decimal", 5, 38, "decimal({0}, {1})",
1291                                                "precision,scale", "System.Decimal", true, true, false,
1292                                                true, false, false, true, true, false, false, 38, 0,
1293                                                false, null, null, null},
1294                                 new object [] {"numeric", 5, 38, "numeric({0}, {1})",
1295                                                "precision,scale", "System.Decimal", true, true, false,
1296                                                true, false, false, true, true, false, false, 38, 0,
1297                                                false, null, null, null},
1298                                 new object [] {"datetime", 4, 23, "datetime", null,
1299                                                "System.DateTime", false, true, false, true, false, false,
1300                                                true, true, true, null, null, null, false, null, "{ts '", "'}"},
1301                                 new object [] {"smalldatetime", 15, 16, "smalldatetime", null,
1302                                                "System.DateTime", false, true, false, true, false, false,
1303                                                true, true, true, null, null, null, false, null, "{ts '", "'}"},
1304                                 new object [] {"sql_variant", 23, null, "sql_variant",
1305                                                null, "System.Object", false, true, false, false, false,
1306                                                false, true, true, false, null, null, null, false, false,
1307                                                null, null},
1308                                 new object [] {"xml", 25, 2147483647, "xml", null,
1309                                                "System.String", false, false, false, false, false, true,
1310                                                true, false, false, null, null, null, false, false, null, null},
1311                                 new object [] {"varchar", 22, 2147483647, "varchar({0})",
1312                                                "max length", "System.String", false, true, false, false,
1313                                                false, false, true, true, true, null, null, null, false,
1314                                                null, "'", "'"},
1315                                 new object [] {"char", 3, 2147483647, "char({0})", "length",
1316                                                "System.String", false, true, false, true, false, false,
1317                                                true, true, true, null, null, null, false, null, "'", "'"},
1318                                 new object [] {"nchar", 10, 1073741823, "nchar({0})", "length",
1319                                                "System.String", false, true, false, true, false, false,
1320                                                true, true, true, null, null, null, false, null, "N'", "'"},
1321                                 new object [] {"nvarchar", 12, 1073741823, "nvarchar({0})", "max length",
1322                                                "System.String", false, true, false, false, false, false, true, true,
1323                                                true, null, null, null, false, null, "N'", "'"},
1324                                 new object [] {"varbinary", 21, 1073741823, "varbinary({0})",
1325                                                "max length", "System.Byte[]", false, true, false, false,
1326                                                false, false, true, true, false, null, null, null, false,
1327                                                null, "0x", null},
1328                                 new object [] {"uniqueidentifier", 14, 16, "uniqueidentifier", null,
1329                                                "System.Guid", false, true, false, true, false, false, true,
1330                                                true, false, null, null, null, false, null, "'", "'"},
1331                                 new object [] {"date", 31, 3L, "date", DBNull.Value,
1332                                                "System.DateTime", false, false, false, true, true, false,
1333                                                true, true, true, DBNull.Value, DBNull.Value, DBNull.Value,
1334                                                false, DBNull.Value, "{ts '", "'}"},
1335                                 new object [] {"time", 32, 5L, "time({0})", "scale",
1336                                                "System.TimeSpan", false, false, false, false, false, false,
1337                                                true, true, true, DBNull.Value, (short) 7, (short) 0,
1338                                                false, DBNull.Value, "{ts '", "'}"},
1339                                 new object [] {"datetime2", 33, 8L, "datetime2({0})", "scale",
1340                                                "System.DateTime", false, true, false, false, false, false,
1341                                                true, true, true, DBNull.Value, (short) 7, (short) 0,
1342                                                false, DBNull.Value, "{ts '", "'}"},
1343                                 new object [] {"datetimeoffset", 34, 10L, "datetimeoffset({0})",
1344                                                "scale", "System.DateTimeOffset", false, true, false, false,
1345                                                false, false, true, true, true, DBNull.Value, (short) 7, (short) 0,
1346                                                false, DBNull.Value, "{ts '", "'}"}
1347                         };
1348
1349                         static DataTable instance;
1350                         static public DataTable Instance {
1351                                 get {
1352                                         if (instance == null) {
1353                                                 instance = new DataTable ("DataTypes");
1354                                                 foreach (ColumnInfo c in columns)
1355                                                         instance.Columns.Add (c.name, c.type);
1356                                                 foreach (object [] row in rows)
1357                                                         instance.LoadDataRow (row, true);
1358                                         }
1359                                         return instance;
1360                                 }
1361                         }
1362                 }
1363
1364                 static class Restrictions
1365                 {
1366                         static readonly ColumnInfo [] columns = {
1367                                 new ColumnInfo ("CollectionName", typeof (string)),
1368                                 new ColumnInfo ("RestrictionName", typeof(string)),
1369                                 new ColumnInfo ("ParameterName", typeof(string)),
1370                                 new ColumnInfo ("RestrictionDefault", typeof(string)),
1371                                 new ColumnInfo ("RestrictionNumber", typeof(int))
1372                         };
1373
1374                         static readonly object [][] rows = {
1375                                 new object [] {"Users", "User_Name", "@Name", "name", 1},
1376                                 new object [] {"Databases", "Name", "@Name", "Name", 1},
1377
1378                                 new object [] {"Tables", "Catalog", "@Catalog", "TABLE_CATALOG", 1},
1379                                 new object [] {"Tables", "Owner", "@Owner", "TABLE_SCHEMA", 2},
1380                                 new object [] {"Tables", "Table", "@Name", "TABLE_NAME", 3},
1381                                 new object [] {"Tables", "TableType", "@TableType", "TABLE_TYPE", 4},
1382
1383                                 new object [] {"Columns", "Catalog", "@Catalog", "TABLE_CATALOG", 1},
1384                                 new object [] {"Columns", "Owner", "@Owner", "TABLE_SCHEMA", 2},
1385                                 new object [] {"Columns", "Table", "@Table", "TABLE_NAME", 3},
1386                                 new object [] {"Columns", "Column", "@Column", "COLUMN_NAME", 4},
1387
1388                                 new object [] {"StructuredTypeMembers", "Catalog", "@Catalog", "TYPE_CATALOG", 1},
1389                                 new object [] {"StructuredTypeMembers", "Owner", "@Owner", "TYPE_SCHEMA", 2},
1390                                 new object [] {"StructuredTypeMembers", "Type", "@Type", "TYPE_NAME", 3},
1391                                 new object [] {"StructuredTypeMembers", "Member", "@Member", "MEMBER_NAME", 4},
1392
1393                                 new object [] {"Views", "Catalog", "@Catalog", "TABLE_CATALOG", 1},
1394                                 new object [] {"Views", "Owner", "@Owner", "TABLE_SCHEMA", 2},
1395                                 new object [] {"Views", "Table", "@Table", "TABLE_NAME", 3},
1396
1397                                 new object [] {"ViewColumns", "Catalog", "@Catalog", "VIEW_CATALOG", 1},
1398                                 new object [] {"ViewColumns", "Owner", "@Owner", "VIEW_SCHEMA", 2},
1399                                 new object [] {"ViewColumns", "Table", "@Table", "VIEW_NAME", 3},
1400                                 new object [] {"ViewColumns", "Column", "@Column", "COLUMN_NAME", 4},
1401
1402                                 new object [] {"ProcedureParameters", "Catalog", "@Catalog", "SPECIFIC_CATALOG", 1},
1403                                 new object [] {"ProcedureParameters", "Owner", "@Owner", "SPECIFIC_SCHEMA", 2},
1404                                 new object [] {"ProcedureParameters", "Name", "@Name", "SPECIFIC_NAME", 3},
1405                                 new object [] {"ProcedureParameters", "Parameter", "@Parameter", "PARAMETER_NAME", 4},
1406
1407                                 new object [] {"Procedures", "Catalog", "@Catalog", "SPECIFIC_CATALOG", 1},
1408                                 new object [] {"Procedures", "Owner", "@Owner", "SPECIFIC_SCHEMA", 2},
1409                                 new object [] {"Procedures", "Name", "@Name", "SPECIFIC_NAME", 3},
1410                                 new object [] {"Procedures", "Type", "@Type", "ROUTINE_TYPE", 4},
1411
1412                                 new object [] {"IndexColumns", "Catalog", "@Catalog", "db_name()", 1},
1413                                 new object [] {"IndexColumns", "Owner", "@Owner", "user_name()", 2},
1414                                 new object [] {"IndexColumns", "Table", "@Table", "o.name", 3},
1415                                 new object [] {"IndexColumns", "ConstraintName", "@ConstraintName", "x.name", 4},
1416                                 new object [] {"IndexColumns", "Column", "@Column", "c.name", 5},
1417
1418                                 new object [] {"Indexes", "Catalog", "@Catalog", "db_name()", 1},
1419                                 new object [] {"Indexes", "Owner", "@Owner", "user_name()", 2},
1420                                 new object [] {"Indexes", "Table", "@Table", "o.name", 3},
1421                                 new object [] {"Indexes", "Name", "@Name", "x.name", 4},
1422
1423                                 new object [] {"UserDefinedTypes", "assembly_name", "@AssemblyName", "assemblies.name", 1},
1424                                 new object [] {"UserDefinedTypes", "udt_name", "@UDTName", "types.assembly_class", 2},
1425
1426                                 new object [] {"ForeignKeys", "Catalog", "@Catalog", "CONSTRAINT_CATALOG", 1},
1427                                 new object [] {"ForeignKeys", "Owner", "@Owner", "CONSTRAINT_SCHEMA", 2},
1428                                 new object [] {"ForeignKeys", "Table", "@Table", "TABLE_NAME", 3},
1429                                 new object [] {"ForeignKeys", "Name", "@Name", "CONSTRAINT_NAME", 4}
1430                         };
1431
1432                         static DataTable instance;
1433                         static public DataTable Instance {
1434                                 get {
1435                                         if (instance == null) {
1436                                                 instance = new DataTable ("Restrictions");
1437                                                 foreach (ColumnInfo c in columns)
1438                                                         instance.Columns.Add (c.name, c.type);
1439                                                 foreach (object [] row in rows)
1440                                                         instance.LoadDataRow (row, true);
1441                                         }
1442                                         return instance;
1443                                 }
1444                         }
1445                 }
1446
1447                 public override DataTable GetSchema ()
1448                 {
1449                         if (state == ConnectionState.Closed)
1450                                 throw ExceptionHelper.ConnectionClosed ();
1451
1452                         return MetaDataCollections.Instance;
1453                 }
1454
1455                 public override DataTable GetSchema (String collectionName)
1456                 {
1457                         return GetSchema (collectionName, null);
1458                 }
1459
1460                 public override DataTable GetSchema (String collectionName, string [] restrictionValues)
1461                 {
1462                         // LAMESPEC: In MS.NET, if collectionName is null, it throws ArgumentException.
1463
1464                         if (state == ConnectionState.Closed)
1465                                 throw ExceptionHelper.ConnectionClosed ();
1466
1467                         String cName = null;
1468                         DataTable schemaTable = MetaDataCollections.Instance;
1469                         int length = restrictionValues == null ? 0 : restrictionValues.Length;
1470
1471                         foreach (DataRow row in schemaTable.Rows) {
1472                                 if (String.Compare ((string) row["CollectionName"], collectionName, true) == 0) {
1473                                         if (length > (int) row["NumberOfRestrictions"]) {
1474                                                 throw new ArgumentException ("More restrictions were provided " +
1475                                                                              "than the requested schema ('" +
1476                                                                              row["CollectionName"].ToString () + "') supports");
1477                                         }
1478                                         cName = row["CollectionName"].ToString();
1479                                 }
1480                         }
1481
1482                         if (cName == null)
1483                                 throw new ArgumentException (string.Format (CultureInfo.InvariantCulture,
1484                                         "The requested collection ({0}) is not defined.",
1485                                         collectionName));
1486
1487                         SqlCommand command     = null;
1488                         DataTable dataTable    = new DataTable ();
1489                         SqlDataAdapter dataAdapter = new SqlDataAdapter ();
1490
1491                         switch (cName)
1492                         {
1493                         case "Databases":
1494                                 command = new SqlCommand ("select name as database_name, dbid, crdate as create_date " +
1495                                                           "from master.sys.sysdatabases where (name = @Name or (@Name " +
1496                                                           "is null))", this);
1497                                 command.Parameters.Add ("@Name", SqlDbType.NVarChar, 4000);
1498                                 break;
1499                         case "ForeignKeys":
1500                                 command = new SqlCommand ("select CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME, " +
1501                                                           "TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, CONSTRAINT_TYPE, " +
1502                                                           "IS_DEFERRABLE, INITIALLY_DEFERRED from " +
1503                                                           "INFORMATION_SCHEMA.TABLE_CONSTRAINTS where (CONSTRAINT_CATALOG" +
1504                                                           " = @Catalog or (@Catalog is null)) and (CONSTRAINT_SCHEMA = " +
1505                                                           "@Owner or (@Owner is null)) and (TABLE_NAME = @Table or (" +
1506                                                           "@Table is null)) and (CONSTRAINT_NAME = @Name or (@Name is null))" +
1507                                                           " and CONSTRAINT_TYPE = 'FOREIGN KEY' order by CONSTRAINT_CATALOG," +
1508                                                           " CONSTRAINT_SCHEMA, CONSTRAINT_NAME", this);
1509                                 command.Parameters.Add ("@Catalog", SqlDbType.NVarChar, 4000);
1510                                 command.Parameters.Add ("@Owner", SqlDbType.NVarChar, 4000);
1511                                 command.Parameters.Add ("@Table", SqlDbType.NVarChar, 4000);
1512                                 command.Parameters.Add ("@Name", SqlDbType.NVarChar, 4000);
1513                                 break;
1514                         case "Indexes":
1515                                 command = new SqlCommand ("select distinct db_name() as constraint_catalog, " +
1516                                                           "constraint_schema = user_name (o.uid), " +
1517                                                           "constraint_name = x.name, table_catalog = db_name (), " +
1518                                                           "table_schema = user_name (o.uid), table_name = o.name, " +
1519                                                           "index_name  = x.name from sysobjects o, sysindexes x, " +
1520                                                           "sysindexkeys xk where o.type in ('U') and x.id = o.id and " +
1521                                                           "o.id = xk.id and x.indid = xk.indid and xk.keyno = x.keycnt " +
1522                                                           "and (db_name() = @Catalog or (@Catalog is null)) and " +
1523                                                           "(user_name() = @Owner or (@Owner is null)) and (o.name = " +
1524                                                           "@Table or (@Table is null)) and (x.name = @Name or (@Name is null))" +
1525                                                           "order by table_name, index_name", this);
1526                                 command.Parameters.Add ("@Catalog", SqlDbType.NVarChar, 4000);
1527                                 command.Parameters.Add ("@Owner", SqlDbType.NVarChar, 4000);
1528                                 command.Parameters.Add ("@Table", SqlDbType.NVarChar, 4000);
1529                                 command.Parameters.Add ("@Name", SqlDbType.NVarChar, 4000);
1530                                 break;
1531                         case "IndexColumns":
1532                                 command = new SqlCommand ("select distinct db_name() as constraint_catalog, " +
1533                                                           "constraint_schema = user_name (o.uid), constraint_name = x.name, " +
1534                                                           "table_catalog = db_name (), table_schema = user_name (o.uid), " +
1535                                                           "table_name = o.name, column_name = c.name, " +
1536                                                           "ordinal_position = convert (int, xk.keyno), keyType = c.xtype, " +
1537                                                           "index_name = x.name from sysobjects o, sysindexes x, syscolumns c, " +
1538                                                           "sysindexkeys xk where o.type in ('U') and x.id = o.id and o.id = c.id " +
1539                                                           "and o.id = xk.id and x.indid = xk.indid and c.colid = xk.colid " +
1540                                                           "and xk.keyno <= x.keycnt and permissions (o.id, c.name) <> 0 " +
1541                                                           "and (db_name() = @Catalog or (@Catalog is null)) and (user_name() " +
1542                                                           "= @Owner or (@Owner is null)) and (o.name = @Table or (@Table is" +
1543                                                           " null)) and (x.name = @ConstraintName or (@ConstraintName is null)) " +
1544                                                           "and (c.name = @Column or (@Column is null)) order by table_name, " +
1545                                                           "index_name", this);
1546                                 command.Parameters.Add ("@Catalog", SqlDbType.NVarChar, 8);
1547                                 command.Parameters.Add ("@Owner", SqlDbType.NVarChar, 4000);
1548                                 command.Parameters.Add ("@Table", SqlDbType.NVarChar, 13);
1549                                 command.Parameters.Add ("@ConstraintName", SqlDbType.NVarChar, 4000);
1550                                 command.Parameters.Add ("@Column", SqlDbType.NVarChar, 4000);
1551                                 break;
1552                         case "Procedures":
1553                                 command = new SqlCommand ("select SPECIFIC_CATALOG, SPECIFIC_SCHEMA, SPECIFIC_NAME, " +
1554                                                           "ROUTINE_CATALOG, ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE, " +
1555                                                           "CREATED, LAST_ALTERED from INFORMATION_SCHEMA.ROUTINES where " +
1556                                                           "(SPECIFIC_CATALOG = @Catalog or (@Catalog is null)) and " +
1557                                                           "(SPECIFIC_SCHEMA = @Owner or (@Owner is null)) and (SPECIFIC_NAME" +
1558                                                           " = @Name or (@Name is null)) and (ROUTINE_TYPE = @Type or (@Type " +
1559                                                           "is null)) order by SPECIFIC_CATALOG, SPECIFIC_SCHEMA, SPECIFIC_NAME", this);
1560                                 command.Parameters.Add ("@Catalog", SqlDbType.NVarChar, 4000);
1561                                 command.Parameters.Add ("@Owner", SqlDbType.NVarChar, 4000);
1562                                 command.Parameters.Add ("@Name", SqlDbType.NVarChar, 4000);
1563                                 command.Parameters.Add ("@Type", SqlDbType.NVarChar, 4000);
1564                                 break;
1565                         case "ProcedureParameters":
1566                                 command = new SqlCommand ("select SPECIFIC_CATALOG, SPECIFIC_SCHEMA, SPECIFIC_NAME, " +
1567                                                           "ORDINAL_POSITION, PARAMETER_MODE, IS_RESULT, AS_LOCATOR, " +
1568                                                           "PARAMETER_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, " +
1569                                                           "CHARACTER_OCTET_LENGTH, COLLATION_CATALOG, COLLATION_SCHEMA, " +
1570                                                           "COLLATION_NAME, CHARACTER_SET_CATALOG, CHARACTER_SET_SCHEMA, " +
1571                                                           "CHARACTER_SET_NAME, NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX, " +
1572                                                           "NUMERIC_SCALE, DATETIME_PRECISION, INTERVAL_TYPE, " +
1573                                                           "INTERVAL_PRECISION from INFORMATION_SCHEMA.PARAMETERS where " +
1574                                                           "(SPECIFIC_CATALOG = @Catalog or (@Catalog is null)) and " +
1575                                                           "(SPECIFIC_SCHEMA = @Owner or (@Owner is null)) and (SPECIFIC_NAME = " +
1576                                                           "@Name or (@Name is null)) and (PARAMETER_NAME = @Parameter or (" +
1577                                                           "@Parameter is null)) order by SPECIFIC_CATALOG, SPECIFIC_SCHEMA," +
1578                                                           " SPECIFIC_NAME, PARAMETER_NAME", this);
1579                                 command.Parameters.Add ("@Catalog", SqlDbType.NVarChar, 4000);
1580                                 command.Parameters.Add ("@Owner", SqlDbType.NVarChar, 4000);
1581                                 command.Parameters.Add ("@Name", SqlDbType.NVarChar, 4000);
1582                                 command.Parameters.Add ("@Parameter", SqlDbType.NVarChar, 4000);
1583                                 break;
1584                         case "Tables":
1585                                 command = new SqlCommand ("select TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE " +
1586                                                           "from INFORMATION_SCHEMA.TABLES where" +
1587                                                           " (TABLE_CATALOG = @catalog or (@catalog is null)) and " +
1588                                                           "(TABLE_SCHEMA = @owner or (@owner is null))and " +
1589                                                           "(TABLE_NAME = @name or (@name is null)) and " +
1590                                                           "(TABLE_TYPE = @table_type or (@table_type is null))", this);
1591                                 command.Parameters.Add ("@catalog", SqlDbType.NVarChar, 8);
1592                                 command.Parameters.Add ("@owner", SqlDbType.NVarChar, 3);
1593                                 command.Parameters.Add ("@name", SqlDbType.NVarChar, 11);
1594                                 command.Parameters.Add ("@table_type", SqlDbType.NVarChar, 10);
1595                                 break;
1596                         case "Columns":
1597                                 command = new SqlCommand ("select TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, " +
1598                                                           "ORDINAL_POSITION, COLUMN_DEFAULT, IS_NULLABLE, DATA_TYPE, " +
1599                                                           "CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH, " +
1600                                                           "NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX, NUMERIC_SCALE, " +
1601                                                           "DATETIME_PRECISION, CHARACTER_SET_CATALOG, CHARACTER_SET_SCHEMA, " +
1602                                                           "CHARACTER_SET_NAME, COLLATION_CATALOG from INFORMATION_SCHEMA.COLUMNS" +
1603                                                           " where (TABLE_CATALOG = @Catalog or (@Catalog is null)) and (" +
1604                                                           "TABLE_SCHEMA = @Owner or (@Owner is null)) and (TABLE_NAME = @table" +
1605                                                           " or (@Table is null)) and (COLUMN_NAME = @column or (@Column is null" +
1606                                                           ")) order by TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME", this);
1607                                 command.Parameters.Add ("@Catalog", SqlDbType.NVarChar, 4000);
1608                                 command.Parameters.Add ("@Owner", SqlDbType.NVarChar, 4000);
1609                                 command.Parameters.Add ("@Table", SqlDbType.NVarChar, 4000);
1610                                 command.Parameters.Add ("@Column", SqlDbType.NVarChar, 4000);
1611                                 break;
1612                         case "Users":
1613                                 command = new SqlCommand ("select uid, name as user_name, createdate, updatedate from sysusers" +
1614                                                           " where (name = @Name or (@Name is null))", this);
1615                                 command.Parameters.Add ("@Name", SqlDbType.NVarChar, 4000);
1616                                 break;
1617                         case "StructuredTypeMembers":
1618                                 // Only available on SQL Server 2008
1619                                 // Running it again SQL 2005 results in the following exception:
1620                                 // Unable to build the 'StructuredTypeMembers' collection because
1621                                 // execution of the SQL query failed. See the inner exception for details.
1622                                 // ---> System.Data.SqlClient.SqlException: Invalid object name 'sys.table_types'.
1623                                 //
1624                                 // I don't have access to SQL Server 2008 right now,
1625                                 // and can't find any online documentation on the 'sys.table_types'
1626                                 // view
1627                                 throw new NotImplementedException ();
1628                         case "Views":
1629                                 command = new SqlCommand ("select TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, CHECK_OPTION, " +
1630                                                           "IS_UPDATABLE from INFORMATION_SCHEMA.VIEWS where (TABLE_CATALOG" +
1631                                                           " = @Catalog or (@Catalog is null)) TABLE_SCHEMA = @Owner or " +
1632                                                           "(@Owner is null)) and (TABLE_NAME = @table or (@Table is null))" +
1633                                                           " order by TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME", this);
1634                                 command.Parameters.Add ("@Catalog", SqlDbType.NVarChar, 4000);
1635                                 command.Parameters.Add ("@Owner", SqlDbType.NVarChar, 4000);
1636                                 command.Parameters.Add ("@Table", SqlDbType.NVarChar, 4000);
1637                                 break;
1638                         case "ViewColumns":
1639                                 command = new SqlCommand ("select VIEW_CATALOG, VIEW_SCHEMA, VIEW_NAME, TABLE_CATALOG, " +
1640                                                           "TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME from " +
1641                                                           "INFORMATION_SCHEMA.VIEW_COLUMN_USAGE where (VIEW_CATALOG = " +
1642                                                           "@Catalog (@Catalog is null)) and (VIEW_SCHEMA = @Owner (@Owner" +
1643                                                           " is null)) and (VIEW_NAME = @Table or (@Table is null)) and " +
1644                                                           "(COLUMN_NAME = @Column or (@Column is null)) order by " +
1645                                                           "VIEW_CATALOG, VIEW_SCHEMA, VIEW_NAME", this);
1646                                 command.Parameters.Add ("@Catalog", SqlDbType.NVarChar, 4000);
1647                                 command.Parameters.Add ("@Owner", SqlDbType.NVarChar, 4000);
1648                                 command.Parameters.Add ("@Table", SqlDbType.NVarChar, 4000);
1649                                 command.Parameters.Add ("@Column", SqlDbType.NVarChar, 4000);
1650                                 break;
1651                         case "UserDefinedTypes":
1652                                 command = new SqlCommand ("select assemblies.name as assembly_name, types.assembly_class " +
1653                                                           "as udt_name, ASSEMBLYPROPERTY(assemblies.name, 'VersionMajor') " +
1654                                                           "as version_major, ASSEMBLYPROPERTY(assemblies.name, 'VersionMinor') " +
1655                                                           "as version_minor, ASSEMBLYPROPERTY(assemblies.name, 'VersionBuild') " +
1656                                                           "as version_build, ASSEMBLYPROPERTY(assemblies.name, 'VersionRevision') " +
1657                                                           "as version_revision, ASSEMBLYPROPERTY(assemblies.name, 'CultureInfo') " +
1658                                                           "as culture_info, ASSEMBLYPROPERTY(assemblies.name, 'PublicKey') " +
1659                                                           "as public_key, is_fixed_length, max_length, Create_Date, " +
1660                                                           "Permission_set_desc from sys.assemblies as assemblies join " +
1661                                                           "sys.assembly_types as types on assemblies.assembly_id = types.assembly_id" +
1662                                                           " where (assportemblies.name = @AssemblyName or (@AssemblyName is null)) and " +
1663                                                           "(types.assembly_class = @UDTName or (@UDTName is null))",
1664                                                           this);
1665                                 command.Parameters.Add ("@AssemblyName", SqlDbType.NVarChar, 4000);
1666                                 command.Parameters.Add ("@UDTName", SqlDbType.NVarChar, 4000);
1667                                 break;
1668                         case "MetaDataCollections":
1669                                 return MetaDataCollections.Instance;
1670                         case "DataSourceInformation":
1671                                 return DataSourceInformation.GetInstance (this);
1672                         case "DataTypes":
1673                                 return DataTypes.Instance;
1674                         case "ReservedWords":
1675                                 return ReservedWords.Instance;
1676                         case "Restrictions":
1677                                 return Restrictions.Instance;
1678                         }
1679                         for (int i = 0; i < length; i++) {
1680                                 command.Parameters[i].Value = restrictionValues[i];
1681                         }
1682                         dataAdapter.SelectCommand = command;
1683                         dataAdapter.Fill (dataTable);
1684                         return dataTable;
1685                 }
1686
1687                 public static void ChangePassword (string connectionString, string newPassword)
1688                 {
1689                         if (String.IsNullOrEmpty (connectionString))
1690                                 throw new ArgumentNullException ("The 'connectionString' cannot be null or empty.");
1691                         if (String.IsNullOrEmpty (newPassword))
1692                                 throw new ArgumentNullException ("The 'newPassword' cannot be null or empty.");
1693                         if (newPassword.Length > 128)
1694                                 throw new ArgumentException ("The length of 'newPassword' cannot exceed 128 characters.");
1695                         using (SqlConnection conn = new SqlConnection (connectionString)) {
1696                                 conn.Open ();
1697                                 conn.tds.Execute (String.Format ("sp_password '{0}', '{1}', '{2}'",
1698                                                                  conn.parms.Password, newPassword, conn.parms.User));
1699                         }
1700                 }
1701
1702                 public static void ClearAllPools ()
1703                 {
1704                         // FIXME: locking
1705                         IDictionary pools = SqlConnection.sqlConnectionPools.GetConnectionPool ();
1706                         foreach (TdsConnectionPool pool in pools.Values) {
1707                                 if (pool != null)
1708                                         pool.ResetConnectionPool ();
1709                         }
1710                         pools.Clear ();
1711                 }
1712
1713                 public static void ClearPool (SqlConnection connection)
1714                 {
1715                         if (connection == null)
1716                                 throw new ArgumentNullException ("connection");
1717
1718                         // FIXME: locking
1719                         if (connection.pooling) {
1720                                 TdsConnectionPool pool = sqlConnectionPools.GetConnectionPool (connection.ConnectionString);
1721                                 if (pool != null)
1722                                         pool.ResetConnectionPool ();
1723                         }
1724                 }
1725
1726 #endif // NET_2_0
1727
1728                 #endregion // Methods
1729
1730 #if NET_2_0
1731                 #region Fields Net 2
1732
1733                 bool async;
1734                 bool userInstance;
1735
1736                 #endregion // Fields  Net 2
1737
1738                 #region Properties Net 2
1739
1740 #if NET_1_0
1741                 [DataSysDescription ("Enable Asynchronous processing, 'Asynchrouse Processing=true/false' in the ConnectionString.")]
1742 #endif
1743                 [DesignerSerializationVisibility (DesignerSerializationVisibility.Hidden)]
1744                 internal bool AsyncProcessing  {
1745                         get { return async; }
1746                 }
1747
1748                 #endregion // Properties Net 2
1749
1750 #endif // NET_2_0
1751
1752         }
1753 }