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