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

   1 //
   2 // System.Data.SqlClient.SqlConnection.cs
   3 //
   4 // Authors:
   5 //   Rodrigo Moya (rodrigo@ximian.com)
   6 //   Daniel Morgan (danmorg@sc.rr.com)
   7 //   Tim Coleman (tim@timcoleman.com)
   8 //   Phillip Jerkins (Phillip.Jerkins@morgankeegan.com)
   9 //   Diego Caravana (diego@toth.it)
  10 //
  11 // Copyright (C) Ximian, Inc 2002
  12 // Copyright (C) Daniel Morgan 2002, 2003
  13 // Copyright (C) Tim Coleman, 2002, 2003
  14 // Copyright (C) Phillip Jerkins, 2003
  15 //
  16
  17 //
  18 // Copyright (C) 2004 Novell, Inc (http://www.novell.com)
  19 //
  20 // Permission is hereby granted, free of charge, to any person obtaining
  21 // a copy of this software and associated documentation files (the
  22 // "Software"), to deal in the Software without restriction, including
  23 // without limitation the rights to use, copy, modify, merge, publish,
  24 // distribute, sublicense, and/or sell copies of the Software, and to
  25 // permit persons to whom the Software is furnished to do so, subject to
  26 // the following conditions:
  27 //
  28 // The above copyright notice and this permission notice shall be
  29 // included in all copies or substantial portions of the Software.
  30 //
  31 // THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
  32 // EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
  33 // MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
  34 // NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE
  35 // LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION
  36 // OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION
  37 // WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
  38 //
  39
  40 using Mono.Data.Tds;
  41 using Mono.Data.Tds.Protocol;
  42 using System;
  43 using System.Collections;
  44 using System.Collections.Specialized;
  45 using System.ComponentModel;
  46 using System.Data;
  47 using System.Data.Common;
  48 using System.EnterpriseServices;
  49 using System.Globalization;
  50 using System.Net;
  51 using System.Net.Sockets;
  52 using System.Text;
  53 using System.Xml;
  54
  55 namespace System.Data.SqlClient
  56 {
  57         [DefaultEvent ("InfoMessage")]
  58 #if NET_2_0
  59         public sealed class SqlConnection : DbConnection, IDbConnection, ICloneable
  60 #else
  61         public sealed class SqlConnection : Component, IDbConnection, ICloneable
  62 #endif // NET_2_0
  63         {
  64                 #region Fields
  65
  66                 bool disposed;
  67
  68                 // The set of SQL connection pools
  69                 static TdsConnectionPoolManager sqlConnectionPools = new TdsConnectionPoolManager (TdsVersion.tds70);
  70 #if NET_2_0
  71                 const int DEFAULT_PACKETSIZE = 8000;
  72 #else
  73                 const int DEFAULT_PACKETSIZE = 8192;
  74 #endif
  75                 const int DEFAULT_CONNECTIONTIMEOUT = 15;
  76                 const int DEFAULT_MAXPOOLSIZE = 100;
  77                 const int DEFAULT_MINPOOLSIZE = 0;
  78                 const int DEFAULT_PORT = 1433;
  79
  80                 // The current connection pool
  81                 TdsConnectionPool pool;
  82
  83                 // The connection string that identifies this connection
  84                 string connectionString;
  85
  86                 // The transaction object for the current transaction
  87                 SqlTransaction transaction;
  88
  89                 // Connection parameters
  90
  91                 TdsConnectionParameters parms = new TdsConnectionParameters ();
  92                 NameValueCollection connStringParameters;
  93                 bool connectionReset;
  94                 bool pooling;
  95                 string dataSource;
  96                 int connectionTimeout;
  97                 int minPoolSize;
  98                 int maxPoolSize;
  99                 int packetSize;
 100                 int port;
 101                 bool fireInfoMessageEventOnUserErrors;
 102                 bool statisticsEnabled;
 103                 bool userInstance;
 104
 105                 // The current state
 106                 ConnectionState state = ConnectionState.Closed;
 107
 108                 SqlDataReader dataReader;
 109                 XmlReader xmlReader;
 110
 111                 // The TDS object
 112                 Tds tds;
 113
 114                 #endregion // Fields
 115
 116                 #region Constructors
 117
 118                 public SqlConnection () : this (String.Empty)
 119                 {
 120                 }
 121
 122                 public SqlConnection (string connectionString)
 123                 {
 124                         Init (connectionString);
 125                 }
 126
 127                 private void Init (string connectionString)
 128                 {
 129                         connectionTimeout = DEFAULT_CONNECTIONTIMEOUT;
 130                         dataSource = string.Empty;
 131                         packetSize = DEFAULT_PACKETSIZE;
 132                         port = DEFAULT_PORT;
 133                         ConnectionString = connectionString;
 134                 }
 135
 136                 #endregion // Constructors
 137
 138                 #region Properties
 139
 140 #if NET_1_0 || ONLY_1_1
 141                 [DataSysDescription ("Information used to connect to a DataSource, such as 'Data Source=x;Initial Catalog=x;Integrated Security=SSPI'.")]
 142 #endif
 143                 [DefaultValue ("")]
 144                 [EditorAttribute ("Microsoft.VSDesigner.Data.SQL.Design.SqlConnectionStringEditor, "+ Consts.AssemblyMicrosoft_VSDesigner, "System.Drawing.Design.UITypeEditor, "+ Consts.AssemblySystem_Drawing )]
 145                 [RecommendedAsConfigurable (true)]
 146                 [RefreshProperties (RefreshProperties.All)]
 147                 public
 148 #if NET_2_0
 149                 override
 150 #endif // NET_2_0
 151                 string ConnectionString {
 152                         get {
 153                                 if (connectionString == null)
 154                                         return string.Empty;
 155                                 return connectionString;
 156                         }
 157                         [MonoTODO("persist security info, encrypt, enlist keyword not implemented")]
 158                         set {
 159                                 if (state == ConnectionState.Open)
 160                                         throw new InvalidOperationException ("Not Allowed to change ConnectionString property while Connection state is OPEN");
 161                                 SetConnectionString (value);
 162                         }
 163                 }
 164
 165 #if !NET_2_0
 166                 [DataSysDescription ("Current connection timeout value, 'Connect Timeout=X' in the ConnectionString.")]
 167 #endif
 168                 [DesignerSerializationVisibility (DesignerSerializationVisibility.Hidden)]
 169                 public
 170 #if NET_2_0
 171                 override
 172 #endif // NET_2_0
 173                 int ConnectionTimeout {
 174                         get { return connectionTimeout; }
 175                 }
 176
 177 #if !NET_2_0
 178                 [DataSysDescription ("Current SQL Server database, 'Initial Catalog=X' in the connection string.")]
 179 #endif
 180                 [DesignerSerializationVisibility (DesignerSerializationVisibility.Hidden)]
 181                 public
 182 #if NET_2_0
 183                 override
 184 #endif // NET_2_0
 185                 string Database {
 186                         get {
 187                                 if (State == ConnectionState.Open)
 188                                         return tds.Database;
 189                                 return parms.Database ;
 190                         }
 191                 }
 192
 193                 internal SqlDataReader DataReader {
 194                         get { return dataReader; }
 195                         set { dataReader = value; }
 196                 }
 197
 198 #if !NET_2_0
 199                 [DataSysDescription ("Current SqlServer that the connection is opened to, 'Data Source=X' in the connection string. ")]
 200 #else
 201                 [Browsable(true)]
 202 #endif
 203                 [DesignerSerializationVisibility (DesignerSerializationVisibility.Hidden)]
 204                 public
 205 #if NET_2_0
 206                 override
 207 #endif // NET_2_0
 208                 string DataSource {
 209                         get { return dataSource; }
 210                 }
 211
 212 #if !NET_2_0
 213                 [DataSysDescription ("Network packet size, 'Packet Size=x' in the connection string.")]
 214 #endif
 215                 [DesignerSerializationVisibility (DesignerSerializationVisibility.Hidden)]
 216                 public int PacketSize {
 217                         get {
 218                                 if (State == ConnectionState.Open)
 219                                         return ((Tds) tds).PacketSize;
 220                                 return packetSize;
 221                         }
 222                 }
 223
 224                 [Browsable (false)]
 225 #if !NET_2_0
 226                 [DataSysDescription ("Version of the SQL Server accessed by the SqlConnection.")]
 227 #endif
 228                 [DesignerSerializationVisibility (DesignerSerializationVisibility.Hidden)]
 229                 public
 230 #if NET_2_0
 231                 override
 232 #endif // NET_2_0
 233                 string ServerVersion {
 234                         get {
 235                                 if (state == ConnectionState.Closed)
 236                                         throw ExceptionHelper.ConnectionClosed ();
 237                                 else
 238                                         return tds.ServerVersion;
 239                         }
 240                 }
 241
 242                 [Browsable (false)]
 243 #if !NET_2_0
 244                 [DataSysDescription ("The ConnectionState indicating whether the connection is open or closed.")]
 245 #endif
 246                 [DesignerSerializationVisibility (DesignerSerializationVisibility.Hidden)]
 247                 public
 248 #if NET_2_0
 249                 override
 250 #endif // NET_2_0
 251                 ConnectionState State {
 252                         get { return state; }
 253                 }
 254
 255                 internal Tds Tds {
 256                         get { return tds; }
 257                 }
 258
 259                 internal SqlTransaction Transaction {
 260                         get { return transaction; }
 261                         set { transaction = value; }
 262                 }
 263
 264 #if !NET_2_0
 265                 [DataSysDescription ("Workstation Id, 'Workstation ID=x' in the connection string.")]
 266 #endif
 267                 [DesignerSerializationVisibility (DesignerSerializationVisibility.Hidden)]
 268                 public string WorkstationId {
 269                         get { return parms.Hostname; }
 270                 }
 271
 272                 internal XmlReader XmlReader {
 273                         get { return xmlReader; }
 274                         set { xmlReader = value; }
 275                 }
 276
 277 #if NET_2_0
 278                 public bool FireInfoMessageEventOnUserErrors {
 279                         get { return fireInfoMessageEventOnUserErrors; }
 280                         set { fireInfoMessageEventOnUserErrors = value; }
 281                 }
 282
 283                 [DefaultValue (false)]
 284                 public bool StatisticsEnabled {
 285                         get { return statisticsEnabled; }
 286                         set { statisticsEnabled = value; }
 287                 }
 288 #endif
 289                 #endregion // Properties
 290
 291                 #region Events
 292
 293 #if !NET_2_0
 294                 [DataSysDescription ("Event triggered when messages arrive from the DataSource.")]
 295 #endif
 296                 public event SqlInfoMessageEventHandler InfoMessage;
 297
 298 #if !NET_2_0
 299                 [DataSysDescription ("Event triggered when the connection changes state.")]
 300                 public new event StateChangeEventHandler StateChange;
 301 #endif
 302
 303                 #endregion // Events
 304
 305                 #region Delegates
 306
 307                 private void ErrorHandler (object sender, TdsInternalErrorMessageEventArgs e)
 308                 {
 309                         throw new SqlException (e.Class, e.LineNumber, e.Message, e.Number, e.Procedure, e.Server, "Mono SqlClient Data Provider", e.State);
 310                 }
 311
 312                 private void MessageHandler (object sender, TdsInternalInfoMessageEventArgs e)
 313                 {
 314                         OnSqlInfoMessage (CreateSqlInfoMessageEvent (e.Errors));
 315                 }
 316
 317                 #endregion // Delegates
 318
 319                 #region Methods
 320
 321                 internal string GetConnStringKeyValue (params string [] keys)
 322                 {
 323                         if (connStringParameters == null || connStringParameters.Count == 0)
 324                                 return string.Empty;
 325
 326                         foreach (string key in keys) {
 327                                 string value = connStringParameters [key];
 328                                 if (value != null)
 329                                         return value;
 330                         }
 331
 332                         return string.Empty;
 333                 }
 334
 335                 public new SqlTransaction BeginTransaction ()
 336                 {
 337                         return BeginTransaction (IsolationLevel.ReadCommitted, String.Empty);
 338                 }
 339
 340                 public new SqlTransaction BeginTransaction (IsolationLevel iso)
 341                 {
 342                         return BeginTransaction (iso, String.Empty);
 343                 }
 344
 345                 public SqlTransaction BeginTransaction (string transactionName)
 346                 {
 347                         return BeginTransaction (IsolationLevel.ReadCommitted, transactionName);
 348                 }
 349
 350                 public SqlTransaction BeginTransaction (IsolationLevel iso, string transactionName)
 351                 {
 352                         if (state == ConnectionState.Closed)
 353                                 throw ExceptionHelper.ConnectionClosed ();
 354                         if (transaction != null)
 355                                 throw new InvalidOperationException ("SqlConnection does not support parallel transactions.");
 356
 357                         string isolevel = String.Empty;
 358                         switch (iso) {
 359                         case IsolationLevel.ReadUncommitted:
 360                                 isolevel = "READ UNCOMMITTED";
 361                                 break;
 362                         case IsolationLevel.RepeatableRead:
 363                                 isolevel = "REPEATABLE READ";
 364                                 break;
 365                         case IsolationLevel.Serializable:
 366                                 isolevel = "SERIALIZABLE";
 367                                 break;
 368                         case IsolationLevel.ReadCommitted:
 369                                 isolevel = "READ COMMITTED";
 370                                 break;
 371 #if NET_2_0
 372                         case IsolationLevel.Snapshot:
 373                                 isolevel = "SNAPSHOT";
 374                                 break;
 375                         case IsolationLevel.Unspecified:
 376                                 iso = IsolationLevel.ReadCommitted;
 377                                 isolevel = "READ COMMITTED";
 378                                 break;
 379                         case IsolationLevel.Chaos:
 380                                 throw new ArgumentOutOfRangeException ("IsolationLevel",
 381                                         string.Format (CultureInfo.CurrentCulture,
 382                                                 "The IsolationLevel enumeration " +
 383                                                 "value, {0}, is not supported by " +
 384                                                 "the .Net Framework SqlClient " +
 385                                                 "Data Provider.", (int) iso));
 386 #endif
 387                         default:
 388 #if NET_2_0
 389                                 throw new ArgumentOutOfRangeException ("IsolationLevel",
 390                                         string.Format (CultureInfo.CurrentCulture,
 391                                                 "The IsolationLevel enumeration value, {0}, is invalid.",
 392                                                 (int) iso));
 393 #else
 394                                 throw new ArgumentException ("Invalid IsolationLevel parameter: must be ReadCommitted, ReadUncommitted, RepeatableRead, or Serializable.");
 395 #endif
 396                         }
 397
 398                         tds.Execute (String.Format ("SET TRANSACTION ISOLATION LEVEL {0};BEGIN TRANSACTION {1}", isolevel, transactionName));
 399
 400                         transaction = new SqlTransaction (this, iso);
 401                         return transaction;
 402                 }
 403
 404                 public
 405 #if NET_2_0
 406                 override
 407 #endif // NET_2_0
 408                 void ChangeDatabase (string database)
 409                 {
 410                         if (!IsValidDatabaseName (database))
 411                                 throw new ArgumentException (String.Format ("The database name {0} is not valid.", database));
 412                         if (state != ConnectionState.Open)
 413                                 throw new InvalidOperationException ("The connection is not open.");
 414                         tds.Execute (String.Format ("use [{0}]", database));
 415                 }
 416
 417                 private void ChangeState (ConnectionState currentState)
 418                 {
 419                         ConnectionState originalState = state;
 420                         state = currentState;
 421                         OnStateChange (CreateStateChangeEvent (originalState, currentState));
 422                 }
 423
 424                 public
 425 #if NET_2_0
 426                 override
 427 #endif // NET_2_0
 428                 void Close ()
 429                 {
 430                         if (transaction != null && transaction.IsOpen)
 431                                 transaction.Rollback ();
 432
 433                         if (dataReader != null || xmlReader != null) {
 434                                 if(tds != null) tds.SkipToEnd ();
 435                                 dataReader = null;
 436                                 xmlReader = null;
 437                         }
 438
 439                         if (tds != null && tds.IsConnected) {
 440                                 if (pooling && tds.Pooling) {
 441 #if NET_2_0
 442                                         if(pool != null) pool.ReleaseConnection (ref tds);
 443 #else
 444                                         if(pool != null) pool.ReleaseConnection (tds);
 445 #endif
 446                                 }else
 447                                         if(tds != null) tds.Disconnect ();
 448                         }
 449
 450                         if (tds != null) {
 451                                 tds.TdsErrorMessage -= new TdsInternalErrorMessageEventHandler (ErrorHandler);
 452                                 tds.TdsInfoMessage -= new TdsInternalInfoMessageEventHandler (MessageHandler);
 453                         }
 454
 455                         ChangeState (ConnectionState.Closed);
 456                 }
 457
 458                 public new SqlCommand CreateCommand ()
 459                 {
 460                         SqlCommand command = new SqlCommand ();
 461                         command.Connection = this;
 462                         return command;
 463                 }
 464
 465                 private SqlInfoMessageEventArgs CreateSqlInfoMessageEvent (TdsInternalErrorCollection errors)
 466                 {
 467                         return new SqlInfoMessageEventArgs (errors);
 468                 }
 469
 470                 private StateChangeEventArgs CreateStateChangeEvent (ConnectionState originalState, ConnectionState currentState)
 471                 {
 472                         return new StateChangeEventArgs (originalState, currentState);
 473                 }
 474
 475                 protected override void Dispose (bool disposing)
 476                 {
 477                         if (disposed)
 478                                 return;
 479
 480                         try {
 481                                 if (disposing) {
 482                                         if (State == ConnectionState.Open)
 483                                                 Close ();
 484                                         ConnectionString = string.Empty;
 485                                         SetDefaultConnectionParameters (this.connStringParameters);
 486                                 }
 487                         } finally {
 488                                 disposed = true;
 489                                 base.Dispose (disposing);
 490                         }
 491                 }
 492
 493                 [MonoTODO ("Not sure what this means at present.")]
 494                 public void EnlistDistributedTransaction (ITransaction transaction)
 495                 {
 496                         throw new NotImplementedException ();
 497                 }
 498
 499                 object ICloneable.Clone ()
 500                 {
 501                         return new SqlConnection (ConnectionString);
 502                 }
 503
 504 #if NET_2_0
 505                 protected override DbTransaction BeginDbTransaction (IsolationLevel isolationLevel)
 506                 {
 507                         return BeginTransaction (isolationLevel);
 508                 }
 509
 510                 protected override DbCommand CreateDbCommand ()
 511                 {
 512                         return CreateCommand ();
 513                 }
 514 #else
 515                 IDbTransaction IDbConnection.BeginTransaction ()
 516                 {
 517                         return BeginTransaction ();
 518                 }
 519
 520                 IDbTransaction IDbConnection.BeginTransaction (IsolationLevel iso)
 521                 {
 522                         return BeginTransaction (iso);
 523                 }
 524
 525                 IDbCommand IDbConnection.CreateCommand ()
 526                 {
 527                         return CreateCommand ();
 528                 }
 529 #endif
 530
 531                 public
 532 #if NET_2_0
 533                 override
 534 #endif // NET_2_0
 535                 void Open ()
 536                 {
 537                         string serverName = string.Empty;
 538                         if (state == ConnectionState.Open)
 539                                 throw new InvalidOperationException ("The Connection is already Open (State=Open)");
 540
 541                         if (connectionString == null || connectionString.Trim().Length == 0)
 542                                 throw new InvalidOperationException ("Connection string has not been initialized.");
 543
 544                         try {
 545                                 if (!pooling) {
 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                                         tds = new Tds70 (serverName, port, PacketSize, ConnectionTimeout);
 549                                 }
 550                                 else {
 551                                         if(!ParseDataSource (dataSource, out port, out serverName))
 552                                                 throw new SqlException(20, 0, "SQL Server does not exist or access denied.",  17, "ConnectionOpen (Connect()).", dataSource, parms.ApplicationName, 0);
 553
 554                                         TdsConnectionInfo info = new TdsConnectionInfo (serverName, port, packetSize, ConnectionTimeout, minPoolSize, maxPoolSize);
 555                                         pool = sqlConnectionPools.GetConnectionPool (connectionString, info);
 556                                         tds = pool.GetConnection ();
 557                                 }
 558                         } catch (TdsTimeoutException e) {
 559                                 throw SqlException.FromTdsInternalException ((TdsInternalException) e);
 560                         } catch (TdsInternalException e) {
 561                                 throw SqlException.FromTdsInternalException (e);
 562                         }
 563
 564                         tds.TdsErrorMessage += new TdsInternalErrorMessageEventHandler (ErrorHandler);
 565                         tds.TdsInfoMessage += new TdsInternalInfoMessageEventHandler (MessageHandler);
 566
 567                         if (!tds.IsConnected) {
 568                                 try {
 569                                         tds.Connect (parms);
 570                                 } catch {
 571                                         if (pooling)
 572                                                 pool.ReleaseConnection (tds);
 573                                         throw;
 574                                 }
 575                         } else if (connectionReset) {
 576                                 tds.Reset ();
 577                         }
 578
 579                         disposed = false; // reset this, so using () would call Close ().
 580                         ChangeState (ConnectionState.Open);
 581                 }
 582
 583                 private bool ParseDataSource (string theDataSource, out int thePort, out string theServerName)
 584                 {
 585                         theServerName = string.Empty;
 586                         string theInstanceName = string.Empty;
 587
 588                         if (theDataSource == null)
 589                                 throw new ArgumentException("Format of initialization string does not conform to specifications");
 590
 591                         thePort = DEFAULT_PORT; // default TCP port for SQL Server
 592                         bool success = true;
 593
 594                         int idx = 0;
 595                         if ((idx = theDataSource.IndexOf (",")) > -1) {
 596                                 theServerName = theDataSource.Substring (0, idx);
 597                                 string p = theDataSource.Substring (idx + 1);
 598                                 thePort = Int32.Parse (p);
 599                         } else if ((idx = theDataSource.IndexOf ("\\")) > -1) {
 600                                 theServerName = theDataSource.Substring (0, idx);
 601                                 theInstanceName = theDataSource.Substring (idx + 1);
 602
 603                                 // do port discovery via UDP port 1434
 604                                 port = DiscoverTcpPortViaSqlMonitor (theServerName, theInstanceName);
 605                                 if (port == -1)
 606                                         success = false;
 607                         } else
 608                                 theServerName = theDataSource;
 609
 610                         if (theServerName.Length == 0 || theServerName == "(local)" || theServerName == ".")
 611                                 theServerName = "localhost";
 612
 613                         if ((idx = theServerName.IndexOf ("tcp:")) > -1)
 614                                 theServerName = theServerName.Substring (idx + 4);
 615
 616                         return success;
 617                 }
 618
 619                 private bool ConvertIntegratedSecurity (string value)
 620                 {
 621                         if (value.ToUpper() == "SSPI")
 622                                 return true;
 623
 624                         return ConvertToBoolean("integrated security", value);
 625                 }
 626
 627                 private bool ConvertToBoolean (string key, string value)
 628                 {
 629                         string upperValue = value.ToUpper ();
 630
 631                         if (upperValue == "TRUE" || upperValue == "YES")
 632                                 return true;
 633                         else if (upperValue == "FALSE" || upperValue == "NO")
 634                                 return false;
 635
 636                         throw new ArgumentException (string.Format (CultureInfo.InvariantCulture,
 637                                 "Invalid value \"{0}\" for key '{1}'.", value, key));
 638                 }
 639
 640                 private int ConvertToInt32 (string key, string value)
 641                 {
 642                         try {
 643                                 return int.Parse (value);
 644                         } catch (Exception ex) {
 645                                 throw new ArgumentException (string.Format (CultureInfo.InvariantCulture,
 646                                         "Invalid value \"{0}\" for key '{1}'.", value, key));
 647                         }
 648                 }
 649
 650                 private int DiscoverTcpPortViaSqlMonitor (string ServerName, string InstanceName)
 651                 {
 652                         SqlMonitorSocket msock;
 653                         msock = new SqlMonitorSocket (ServerName, InstanceName);
 654                         int SqlServerPort = msock.DiscoverTcpPort (ConnectionTimeout);
 655                         msock = null;
 656                         return SqlServerPort;
 657                 }
 658
 659                 void SetConnectionString (string connectionString)
 660                 {
 661                         NameValueCollection parameters = new NameValueCollection ();
 662                         SetDefaultConnectionParameters (parameters);
 663
 664                         if ((connectionString == null) || (connectionString.Trim().Length == 0)) {
 665                                 this.connectionString = connectionString;
 666                                 this.connStringParameters = parameters;
 667                                 return;
 668                         }
 669
 670                         connectionString += ";";
 671
 672                         bool inQuote = false;
 673                         bool inDQuote = false;
 674                         bool inName = true;
 675
 676                         string name = String.Empty;
 677                         string value = String.Empty;
 678                         StringBuilder sb = new StringBuilder ();
 679
 680                         for (int i = 0; i < connectionString.Length; i += 1) {
 681                                 char c = connectionString [i];
 682                                 char peek;
 683                                 if (i == connectionString.Length - 1)
 684                                         peek = '\0';
 685                                 else
 686                                         peek = connectionString [i + 1];
 687
 688                                 switch (c) {
 689                                 case '\'':
 690                                         if (inDQuote)
 691                                                 sb.Append (c);
 692                                         else if (peek.Equals (c)) {
 693                                                 sb.Append (c);
 694                                                 i += 1;
 695                                         }
 696                                         else
 697                                                 inQuote = !inQuote;
 698                                         break;
 699                                 case '"':
 700                                         if (inQuote)
 701                                                 sb.Append (c);
 702                                         else if (peek.Equals (c)) {
 703                                                 sb.Append (c);
 704                                                 i += 1;
 705                                         }
 706                                         else
 707                                                 inDQuote = !inDQuote;
 708                                         break;
 709                                 case ';':
 710                                         if (inDQuote || inQuote)
 711                                                 sb.Append (c);
 712                                         else {
 713                                                 if (name != String.Empty && name != null) {
 714                                                         value = sb.ToString ();
 715                                                         SetProperties (name.ToUpper ().Trim() , value);
 716                                                         parameters [name.ToUpper ().Trim ()] = value.Trim ();
 717                                                 }
 718                                                 else if (sb.Length != 0)
 719                                                         throw new ArgumentException ("Format of initialization string does not conform to specifications");
 720                                                 inName = true;
 721                                                 name = String.Empty;
 722                                                 value = String.Empty;
 723                                                 sb = new StringBuilder ();
 724                                         }
 725                                         break;
 726                                 case '=':
 727                                         if (inDQuote || inQuote || !inName)
 728                                                 sb.Append (c);
 729                                         else if (peek.Equals (c)) {
 730                                                 sb.Append (c);
 731                                                 i += 1;
 732
 733                                         }
 734                                         else {
 735                                                 name = sb.ToString ();
 736                                                 sb = new StringBuilder ();
 737                                                 inName = false;
 738                                         }
 739                                         break;
 740                                 case ' ':
 741                                         if (inQuote || inDQuote)
 742                                                 sb.Append (c);
 743                                         else if (sb.Length > 0 && !peek.Equals (';'))
 744                                                 sb.Append (c);
 745                                         break;
 746                                 default:
 747                                         sb.Append (c);
 748                                         break;
 749                                 }
 750                         }
 751
 752                         connectionString = connectionString.Substring (0 , connectionString.Length-1);
 753                         this.connectionString = connectionString;
 754                         this.connStringParameters = parameters;
 755                 }
 756
 757                 void SetDefaultConnectionParameters (NameValueCollection parameters)
 758                 {
 759                         parms.Reset ();
 760                         dataSource = string.Empty;
 761                         connectionTimeout = DEFAULT_CONNECTIONTIMEOUT;
 762                         connectionReset = true;
 763                         pooling = true;
 764                         maxPoolSize = DEFAULT_MAXPOOLSIZE;
 765                         minPoolSize = DEFAULT_MINPOOLSIZE;
 766                         packetSize = DEFAULT_PACKETSIZE;
 767
 768                         parameters["APPLICATION NAME"] = "Mono SqlClient Data Provider";
 769                         parameters["CONNECT TIMEOUT"] = connectionTimeout.ToString (CultureInfo.InvariantCulture);
 770                         parameters["CONNECTION LIFETIME"] = "0";
 771                         parameters["CONNECTION RESET"] = "true";
 772                         parameters["ENLIST"] = "true";
 773                         parameters["INTEGRATED SECURITY"] = "false";
 774                         parameters["INITIAL CATALOG"] = string.Empty;
 775                         parameters["MAX POOL SIZE"] = maxPoolSize.ToString (CultureInfo.InvariantCulture);
 776                         parameters["MIN POOL SIZE"] = minPoolSize.ToString (CultureInfo.InvariantCulture);
 777                         parameters["NETWORK LIBRARY"] = "dbmssocn";
 778                         parameters["PACKET SIZE"] = packetSize.ToString (CultureInfo.InvariantCulture);
 779                         parameters["PERSIST SECURITY INFO"] = "false";
 780                         parameters["POOLING"] = "true";
 781                         parameters["WORKSTATION ID"] = Environment.MachineName;
 782                         parameters["USER INSTANCE"] = "false";
 783  #if NET_2_0
 784                         async = false;
 785                         parameters ["ASYNCHRONOUS PROCESSING"] = "false";
 786  #endif
 787                 }
 788
 789                 private void SetProperties (string name , string value)
 790                 {
 791                         switch (name) {
 792                         case "APP" :
 793                         case "APPLICATION NAME" :
 794                                 parms.ApplicationName = value;
 795                                 break;
 796                         case "ATTACHDBFILENAME" :
 797                         case "EXTENDED PROPERTIES" :
 798                         case "INITIAL FILE NAME" :
 799                                 parms.AttachDBFileName = value;
 800                                 break;
 801                         case "TIMEOUT" :
 802                         case "CONNECT TIMEOUT" :
 803                         case "CONNECTION TIMEOUT" :
 804                                 int tmpTimeout = ConvertToInt32 ("connection timeout", value);
 805                                 if (tmpTimeout < 0)
 806                                         throw new ArgumentException ("Invalid CONNECTION TIMEOUT .. Must be an integer >=0 ");
 807                                 else
 808                                         connectionTimeout = tmpTimeout;
 809                                 break;
 810                         case "CONNECTION LIFETIME" :
 811                                 break;
 812                         case "CONNECTION RESET" :
 813                                 connectionReset = ConvertToBoolean ("connection reset", value);
 814                                 break;
 815                         case "LANGUAGE" :
 816                         case "CURRENT LANGUAGE" :
 817                                 parms.Language = value;
 818                                 break;
 819                         case "DATA SOURCE" :
 820                         case "SERVER" :
 821                         case "ADDRESS" :
 822                         case "ADDR" :
 823                         case "NETWORK ADDRESS" :
 824                                 dataSource = value;
 825                                 break;
 826                         case "ENCRYPT":
 827                                 if (ConvertToBoolean("encrypt", value))
 828                                         throw new NotImplementedException("SSL encryption for"
 829                                                 + " data sent between client and server is not"
 830                                                 + " implemented.");
 831                                 break;
 832                         case "ENLIST" :
 833                                 if (!ConvertToBoolean("enlist", value))
 834                                         throw new NotImplementedException("Disabling the automatic"
 835                                                 + " enlistment of connections in the thread's current"
 836                                                 + " transaction context is not implemented.");
 837                                 break;
 838                         case "INITIAL CATALOG" :
 839                         case "DATABASE" :
 840                                 parms.Database = value;
 841                                 break;
 842                         case "INTEGRATED SECURITY" :
 843                         case "TRUSTED_CONNECTION" :
 844                                 parms.DomainLogin = ConvertIntegratedSecurity(value);
 845                                 break;
 846                         case "MAX POOL SIZE" :
 847                                 int tmpMaxPoolSize = ConvertToInt32 ("max pool size" , value);
 848                                 if (tmpMaxPoolSize < 0)
 849                                         throw new ArgumentException ("Invalid MAX POOL SIZE. Must be a intger >= 0");
 850                                 else
 851                                         maxPoolSize = tmpMaxPoolSize;
 852                                 break;
 853                         case "MIN POOL SIZE" :
 854                                 int tmpMinPoolSize = ConvertToInt32 ("min pool size" , value);
 855                                 if (tmpMinPoolSize < 0)
 856                                         throw new ArgumentException ("Invalid MIN POOL SIZE. Must be a intger >= 0");
 857                                 else
 858                                         minPoolSize = tmpMinPoolSize;
 859                                 break;
 860 #if NET_2_0
 861                         case "MULTIPLEACTIVERESULTSETS":
 862                                 break;
 863                         case "ASYNCHRONOUS PROCESSING" :
 864                         case "ASYNC" :
 865                                 async = ConvertToBoolean (name, value);
 866                                 break;
 867 #endif
 868                         case "NET" :
 869                         case "NETWORK" :
 870                         case "NETWORK LIBRARY" :
 871                                 if (!value.ToUpper ().Equals ("DBMSSOCN"))
 872                                         throw new ArgumentException ("Unsupported network library.");
 873                                 break;
 874                         case "PACKET SIZE" :
 875                                 int tmpPacketSize = ConvertToInt32 ("packet size", value);
 876                                 if (tmpPacketSize < 512 || tmpPacketSize > 32767)
 877                                         throw new ArgumentException ("Invalid PACKET SIZE. The integer must be between 512 and 32767");
 878                                 else
 879                                         packetSize = tmpPacketSize;
 880                                 break;
 881                         case "PASSWORD" :
 882                         case "PWD" :
 883                                 parms.Password = value;
 884                                 break;
 885                         case "PERSISTSECURITYINFO" :
 886                         case "PERSIST SECURITY INFO" :
 887                                 // FIXME : not implemented
 888                                 // throw new NotImplementedException ();
 889                                 break;
 890                         case "POOLING" :
 891                                 pooling = ConvertToBoolean("pooling", value);
 892                                 break;
 893                         case "UID" :
 894                         case "USER" :
 895                         case "USER ID" :
 896                                 parms.User = value;
 897                                 break;
 898                         case "WSID" :
 899                         case "WORKSTATION ID" :
 900                                 parms.Hostname = value;
 901                                 break;
 902
 903                         case "USER INSTANCE":
 904                                 userInstance = ConvertToBoolean ("user instance", value);
 905                                 break;
 906                         default :
 907                                 throw new ArgumentException("Keyword not supported :"+name);
 908                         }
 909                 }
 910
 911                 static bool IsValidDatabaseName (string database)
 912                 {
 913                         if ( database == null || database.Trim().Length == 0 || database.Length > 128)
 914                                 return false ;
 915
 916                         if (database[0] == '"' && database[database.Length] == '"')
 917                                 database = database.Substring (1, database.Length - 2);
 918                         else if (Char.IsDigit (database[0]))
 919                                 return false;
 920
 921                         if (database[0] == '_')
 922                                 return false;
 923
 924                         foreach (char c  in database.Substring (1, database.Length - 1))
 925                                 if (!Char.IsLetterOrDigit (c) && c != '_' && c != '-')
 926                                         return false;
 927                         return true;
 928                 }
 929
 930                 private void OnSqlInfoMessage (SqlInfoMessageEventArgs value)
 931                 {
 932                         if (InfoMessage != null)
 933                                 InfoMessage (this, value);
 934                 }
 935
 936 #if !NET_2_0
 937                 private new void OnStateChange (StateChangeEventArgs value)
 938                 {
 939                         if (StateChange != null)
 940                                 StateChange (this, value);
 941                 }
 942 #endif
 943
 944                 private sealed class SqlMonitorSocket : UdpClient
 945                 {
 946                         // UDP port that the SQL Monitor listens
 947                         private static readonly int SqlMonitorUdpPort = 1434;
 948                         //private static readonly string SqlServerNotExist = "SQL Server does not exist or access denied";
 949
 950                         private string server;
 951                         private string instance;
 952
 953                         internal SqlMonitorSocket (string ServerName, string InstanceName)
 954                                 : base (ServerName, SqlMonitorUdpPort)
 955                         {
 956                                 server = ServerName;
 957                                 instance = InstanceName;
 958                         }
 959
 960                         internal int DiscoverTcpPort (int timeoutSeconds)
 961                         {
 962                                 int SqlServerTcpPort;
 963                                 Client.Blocking = false;
 964                                 // send command to UDP 1434 (SQL Monitor) to get
 965                                 // the TCP port to connect to the MS SQL server
 966                                 ASCIIEncoding enc = new ASCIIEncoding ();
 967                                 Byte[] rawrq = new Byte [instance.Length + 1];
 968                                 rawrq[0] = 4;
 969                                 enc.GetBytes (instance, 0, instance.Length, rawrq, 1);
 970                                 int bytes = Send (rawrq, rawrq.Length);
 971
 972                                 if (!Active)
 973                                         return -1; // Error
 974
 975                                 bool result;
 976                                 long timeout = timeoutSeconds * 1000000;
 977                                 result = Client.Poll ((int)timeout, SelectMode.SelectRead);
 978                                 if (result == false)
 979                                         return -1; // Error
 980
 981                                 if (Client.Available <= 0)
 982                                         return -1; // Error
 983 #if NET_2_0
 984                                 IPEndPoint endpoint = new IPEndPoint (Dns.GetHostEntry ("localhost").AddressList [0], 0);
 985 #else
 986                                 IPEndPoint endpoint = new IPEndPoint (Dns.GetHostByName ("localhost").AddressList [0], 0);
 987 #endif
 988                                 Byte [] rawrs;
 989
 990                                 rawrs = Receive (ref endpoint);
 991
 992                                 string rs = Encoding.ASCII.GetString (rawrs);
 993
 994                                 string[] rawtokens = rs.Split (';');
 995                                 Hashtable data = new Hashtable ();
 996                                 for (int i = 0; i < rawtokens.Length / 2 && i < 256; i++) {
 997                                         data [rawtokens [i * 2]] = rawtokens [ i * 2 + 1];
 998                                 }
 999
1000                                 if (!data.ContainsKey ("tcp")) {
1001                                         string msg = "Mono does not support names pipes or shared memory "
1002                                                 + "for connecting to SQL Server. Please enable the TCP/IP "
1003                                                 + "protocol.";
1004                                         throw new NotImplementedException (msg);
1005                                 }
1006
1007                                 SqlServerTcpPort = int.Parse ((string) data ["tcp"]);
1008                                 Close ();
1009
1010                                 return SqlServerTcpPort;
1011                         }
1012                 }
1013
1014 #if NET_2_0
1015                 struct ColumnInfo {
1016                         public string name;
1017                         public Type type;
1018                         public ColumnInfo (string name, Type type)
1019                         {
1020                                 this.name = name; this.type = type;
1021                         }
1022                 }
1023
1024                 static class ReservedWords
1025                 {
1026                         static readonly string [] reservedWords =
1027                         {
1028                                 "ADD", "EXCEPT", "PERCENT", "ALL", "EXEC", "PLAN", "ALTER",
1029                                   "EXECUTE", "PRECISION", "AND", "EXISTS", "PRIMARY", "ANY",
1030                                   "EXIT", "PRINT", "AS", "FETCH", "PROC", "ASC", "FILE",
1031                                   "PROCEDURE", "AUTHORIZATION", "FILLFACTOR", "PUBLIC",
1032                                   "BACKUP", "FOR", "RAISERROR", "BEGIN", "FOREIGN", "READ",
1033                                   "BETWEEN", "FREETEXT", "READTEXT", "BREAK", "FREETEXTTABLE",
1034                                   "RECONFIGURE", "BROWSE", "FROM", "REFERENCES", "BULK",
1035                                   "FULL", "REPLICATION", "BY", "FUNCTION", "RESTORE",
1036                                   "CASCADE", "GOTO", "RESTRICT", "CASE", "GRANT", "RETURN",
1037                                   "CHECK", "GROUP", "REVOKE", "CHECKPOINT", "HAVING", "RIGHT",
1038                                   "CLOSE", "HOLDLOCK", "ROLLBACK", "CLUSTERED", "IDENTITY",
1039                                   "ROWCOUNT", "COALESCE", "IDENTITY_INSERT", "ROWGUIDCOL",
1040                                   "COLLATE", "IDENTITYCOL", "RULE", "COLUMN", "IF", "SAVE",
1041                                   "COMMIT", "IN", "SCHEMA", "COMPUTE", "INDEX", "SELECT",
1042                                   "CONSTRAINT", "INNER", "SESSION_USER", "CONTAINS", "INSERT",
1043                                   "SET", "CONTAINSTABLE", "INTERSECT", "SETUSER", "CONTINUE",
1044                                   "INTO", "SHUTDOWN", "CONVERT", "IS", "SOME", "CREATE",
1045                                   "JOIN", "STATISTICS", "CROSS", "KEY", "SYSTEM_USER",
1046                                   "CURRENT", "KILL", "TABLE", "CURRENT_DATE", "LEFT",
1047                                   "TEXTSIZE", "CURRENT_TIME", "LIKE", "THEN",
1048                                   "CURRENT_TIMESTAMP", "LINENO", "TO", "CURRENT_USER", "LOAD",
1049                                   "TOP", "CURSOR", "NATIONAL", "TRAN", "DATABASE", "NOCHECK",
1050                                   "TRANSACTION", "DBCC", "NONCLUSTERED", "TRIGGER",
1051                                   "DEALLOCATE", "NOT", "TRUNCATE", "DECLARE", "NULL",
1052                                   "TSEQUAL", "DEFAULT", "NULLIF", "UNION", "DELETE", "OF",
1053                                   "UNIQUE", "DENY", "OFF", "UPDATE", "DESC", "OFFSETS",
1054                                   "UPDATETEXT", "DISK", "ON", "USE", "DISTINCT", "OPEN",
1055                                   "USER", "DISTRIBUTED", "OPENDATASOURCE", "VALUES", "DOUBLE",
1056                                   "OPENQUERY", "VARYING", "DROP", "OPENROWSET", "VIEW",
1057                                   "DUMMY", "OPENXML", "WAITFOR", "DUMP", "OPTION", "WHEN",
1058                                   "ELSE", "OR", "WHERE", "END", "ORDER", "WHILE", "ERRLVL",
1059                                   "OUTER", "WITH", "ESCAPE", "OVER", "WRITETEXT", "ABSOLUTE",
1060                                   "FOUND", "PRESERVE", "ACTION", "FREE", "PRIOR", "ADMIN",
1061                                   "GENERAL", "PRIVILEGES", "AFTER", "GET", "READS",
1062                                   "AGGREGATE", "GLOBAL", "REAL", "ALIAS", "GO", "RECURSIVE",
1063                                   "ALLOCATE", "GROUPING", "REF", "ARE", "HOST", "REFERENCING",
1064                                   "ARRAY", "HOUR", "RELATIVE", "ASSERTION", "IGNORE", "RESULT",
1065                                   "AT", "IMMEDIATE", "RETURNS", "BEFORE", "INDICATOR", "ROLE",
1066                                   "BINARY", "INITIALIZE", "ROLLUP", "BIT", "INITIALLY",
1067                                   "ROUTINE", "BLOB", "INOUT", "ROW", "BOOLEAN", "INPUT",
1068                                   "ROWS", "BOTH", "INT", "SAVEPOINT", "BREADTH", "INTEGER",
1069                                   "SCROLL", "CALL", "INTERVAL", "SCOPE", "CASCADED",
1070                                   "ISOLATION", "SEARCH", "CAST", "ITERATE", "SECOND",
1071                                   "CATALOG", "LANGUAGE", "SECTION", "CHAR", "LARGE",
1072                                   "SEQUENCE", "CHARACTER", "LAST", "SESSION", "CLASS",
1073                                   "LATERAL", "SETS", "CLOB", "LEADING", "SIZE", "COLLATION",
1074                                   "LESS", "SMALLINT", "COMPLETION", "LEVEL", "SPACE",
1075                                   "CONNECT", "LIMIT", "SPECIFIC", "CONNECTION", "LOCAL",
1076                                   "SPECIFICTYPE", "CONSTRAINTS", "LOCALTIME", "SQL",
1077                                   "CONSTRUCTOR", "LOCALTIMESTAMP", "SQLEXCEPTION",
1078                                   "CORRESPONDING", "LOCATOR", "SQLSTATE", "CUBE", "MAP",
1079                                   "SQLWARNING", "CURRENT_PATH", "MATCH", "START",
1080                                   "CURRENT_ROLE", "MINUTE", "STATE", "CYCLE", "MODIFIES",
1081                                   "STATEMENT", "DATA", "MODIFY", "STATIC", "DATE", "MODULE",
1082                                   "STRUCTURE", "DAY", "MONTH", "TEMPORARY", "DEC", "NAMES",
1083                                   "TERMINATE", "DECIMAL", "NATURAL", "THAN", "DEFERRABLE",
1084                                   "NCHAR", "TIME", "DEFERRED", "NCLOB", "TIMESTAMP", "DEPTH",
1085                                   "NEW", "TIMEZONE_HOUR", "DEREF", "NEXT", "TIMEZONE_MINUTE",
1086                                   "DESCRIBE", "NO", "TRAILING", "DESCRIPTOR", "NONE",
1087                                   "TRANSLATION", "DESTROY", "NUMERIC", "TREAT", "DESTRUCTOR",
1088                                   "OBJECT", "TRUE", "DETERMINISTIC", "OLD", "UNDER",
1089                                   "DICTIONARY", "ONLY", "UNKNOWN", "DIAGNOSTICS", "OPERATION",
1090                                   "UNNEST", "DISCONNECT", "ORDINALITY", "USAGE", "DOMAIN",
1091                                   "OUT", "USING", "DYNAMIC", "OUTPUT", "VALUE", "EACH",
1092                                   "PAD", "VARCHAR", "END-EXEC", "PARAMETER", "VARIABLE",
1093                                   "EQUALS", "PARAMETERS", "WHENEVER", "EVERY", "PARTIAL",
1094                                   "WITHOUT", "EXCEPTION", "PATH", "WORK", "EXTERNAL",
1095                                   "POSTFIX", "WRITE", "FALSE", "PREFIX", "YEAR", "FIRST",
1096                                   "PREORDER", "ZONE", "FLOAT", "PREPARE", "ADA", "AVG",
1097                                   "BIT_LENGTH", "CHAR_LENGTH", "CHARACTER_LENGTH", "COUNT",
1098                                   "EXTRACT", "FORTRAN", "INCLUDE", "INSENSITIVE", "LOWER",
1099                                   "MAX", "MIN", "OCTET_LENGTH", "OVERLAPS", "PASCAL",
1100                                   "POSITION", "SQLCA", "SQLCODE", "SQLERROR", "SUBSTRING",
1101                                   "SUM", "TRANSLATE", "TRIM", "UPPER"
1102                         };
1103                         static DataTable instance;
1104                         static public DataTable Instance {
1105                                 get {
1106                                         if (instance == null) {
1107                                                 DataRow row = null;
1108                                                 instance = new DataTable ("ReservedWords");
1109                                                 instance.Columns.Add ("ReservedWord", typeof(string));
1110                                                 foreach (string reservedWord in reservedWords)
1111                                                 {
1112                                                         row = instance.NewRow();
1113
1114                                                         row["ReservedWord"] = reservedWord;
1115                                                         instance.Rows.Add(row);
1116                                                 }
1117                                         }
1118                                         return instance;
1119                                 }
1120                         }
1121                 }
1122
1123                 static class MetaDataCollections
1124                 {
1125                         static readonly ColumnInfo [] columns = {
1126                                 new ColumnInfo ("CollectionName", typeof (string)),
1127                                 new ColumnInfo ("NumberOfRestrictions", typeof (int)),
1128                                 new ColumnInfo ("NumberOfIdentifierParts", typeof (int))
1129                         };
1130
1131                         static readonly object [][] rows = {
1132                                 new object [] {"MetaDataCollections", 0, 0},
1133                                 new object [] {"DataSourceInformation", 0, 0},
1134                                 new object [] {"DataTypes", 0, 0},
1135                                 new object [] {"Restrictions", 0, 0},
1136                                 new object [] {"ReservedWords", 0, 0},
1137                                 new object [] {"Users", 1, 1},
1138                                 new object [] {"Databases", 1, 1},
1139                                 new object [] {"Tables", 4, 3},
1140                                 new object [] {"Columns", 4, 4},
1141                                 new object [] {"StructuredTypeMembers", 4, 4},
1142                                 new object [] {"Views", 3, 3},
1143                                 new object [] {"ViewColumns", 4, 4},
1144                                 new object [] {"ProcedureParameters", 4, 1},
1145                                 new object [] {"Procedures", 4, 3},
1146                                 new object [] {"ForeignKeys", 4, 3},
1147                                 new object [] {"IndexColumns", 5, 4},
1148                                 new object [] {"Indexes", 4, 3},
1149                                 new object [] {"UserDefinedTypes", 2, 1}
1150                         };
1151
1152                         static DataTable instance;
1153                         static public DataTable Instance {
1154                                 get {
1155                                         if (instance == null) {
1156                                                 instance = new DataTable ("MetaDataCollections");
1157                                                 foreach (ColumnInfo c in columns)
1158                                                         instance.Columns.Add (c.name, c.type);
1159                                                 foreach (object [] row in rows)
1160                                                         instance.LoadDataRow (row, true);
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 (connectionString == null || newPassword == null || newPassword == String.Empty)
1690                                 throw new ArgumentNullException ();
1691                         if (newPassword.Length > 128)
1692                                 throw new ArgumentException ("The value of newPassword exceeds its permittable length which is 128");
1693                         using (SqlConnection conn = new SqlConnection (connectionString)) {
1694                                 conn.Open ();
1695                                 conn.tds.Execute (String.Format ("sp_password '{0}', '{1}', '{2}'",
1696                                                                  conn.parms.Password, newPassword, conn.parms.User));
1697                         }
1698                 }
1699
1700                 public static void ClearAllPools ()
1701                 {
1702                         Hashtable pools = SqlConnection.sqlConnectionPools.GetConnectionPool ();
1703                         foreach (TdsConnectionPool pool in pools.Values) {
1704                                 if (pool != null) {
1705                                         pool.ResetConnectionPool ();
1706                                         Tds tds = pool.GetConnection ();
1707                                         tds.Pooling = false;
1708                                 }
1709                         }
1710                 }
1711
1712                 public static void ClearPool (SqlConnection connection)
1713                 {
1714                         if (connection.pooling) {
1715                                 connection.pooling = false;
1716                                 if (connection.pool != null)
1717                                         connection.pool.ResetConnectionPool (connection.Tds);
1718                         }
1719                 }
1720
1721 #endif // NET_2_0
1722
1723                 #endregion // Methods
1724
1725 #if NET_2_0
1726                 #region Fields Net 2
1727
1728                 bool async = false;
1729
1730                 #endregion // Fields  Net 2
1731
1732                 #region Properties Net 2
1733
1734 #if NET_1_0
1735                 [DataSysDescription ("Enable Asynchronous processing, 'Asynchrouse Processing=true/false' in the ConnectionString.")]
1736 #endif
1737                 [DesignerSerializationVisibility (DesignerSerializationVisibility.Hidden)]
1738                 internal bool AsyncProcessing  {
1739                         get { return async; }
1740                 }
1741
1742                 #endregion // Properties Net 2
1743
1744 #endif // NET_2_0
1745
1746         }
1747 }