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

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