mcs/class/System.Data/System.Data.Common/DbConnection.cs

   1 //
   2 // System.Data.Common.DbConnection
   3 //
   4 // Author:
   5 //   Tim Coleman (tim@timcoleman.com)
   6 //
   7 // Copyright (C) Tim Coleman, 2003
   8 //
   9
  10 //
  11 // Copyright (C) 2004 Novell, Inc (http://www.novell.com)
  12 //
  13 // Permission is hereby granted, free of charge, to any person obtaining
  14 // a copy of this software and associated documentation files (the
  15 // "Software"), to deal in the Software without restriction, including
  16 // without limitation the rights to use, copy, modify, merge, publish,
  17 // distribute, sublicense, and/or sell copies of the Software, and to
  18 // permit persons to whom the Software is furnished to do so, subject to
  19 // the following conditions:
  20 //
  21 // The above copyright notice and this permission notice shall be
  22 // included in all copies or substantial portions of the Software.
  23 //
  24 // THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
  25 // EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
  26 // MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
  27 // NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE
  28 // LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION
  29 // OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION
  30 // WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
  31 //
  32
  33 using System.ComponentModel;
  34 using System.Data;
  35 #if NET_2_0 && !TARGET_JVM
  36 using System.Transactions;
  37 #endif
  38
  39 #if NET_4_5
  40 using System.Threading;
  41 using System.Threading.Tasks;
  42 #endif
  43
  44 namespace System.Data.Common {
  45         public abstract class DbConnection : Component, IDbConnection, IDisposable
  46         {
  47                 #region Constructors
  48
  49                 protected DbConnection ()
  50                 {
  51                 }
  52
  53                 #endregion // Constructors
  54
  55                 #region Properties
  56                 [RecommendedAsConfigurable (true)]
  57                 [RefreshProperties (RefreshProperties.All)]
  58                 [DefaultValue ("")]
  59                 public abstract string ConnectionString { get; set; }
  60
  61                 public abstract string Database { get; }
  62                 public abstract string DataSource { get; }
  63
  64                 [Browsable (false)]
  65                 public abstract string ServerVersion { get; }
  66
  67                 [Browsable (false)]
  68                 public abstract ConnectionState State { get; }
  69
  70                 public virtual int ConnectionTimeout {
  71                         get { return 15; }
  72                 }
  73
  74                 #endregion // Properties
  75
  76                 #region Methods
  77
  78                 protected abstract DbTransaction BeginDbTransaction (IsolationLevel isolationLevel);
  79
  80                 public DbTransaction BeginTransaction ()
  81                 {
  82                         return BeginDbTransaction (IsolationLevel.Unspecified);
  83                 }
  84
  85                 public DbTransaction BeginTransaction (IsolationLevel isolationLevel)
  86                 {
  87                         return BeginDbTransaction (isolationLevel);
  88                 }
  89
  90                 public abstract void ChangeDatabase (string databaseName);
  91                 public abstract void Close ();
  92
  93                 public DbCommand CreateCommand ()
  94                 {
  95                         return CreateDbCommand ();
  96                 }
  97
  98                 protected abstract DbCommand CreateDbCommand ();
  99
 100 #if NET_2_0 && !TARGET_JVM
 101                 public virtual void EnlistTransaction (Transaction transaction)
 102                 {
 103                         throw new NotSupportedException ();
 104                 }
 105 #endif
 106
 107 #if NET_2_0
 108                 static class DataTypes
 109                 {
 110                         static readonly ColumnInfo [] columns = {
 111                                 new ColumnInfo ("TypeName", typeof(string)),
 112                                 new ColumnInfo ("ProviderDbType", typeof(int)),
 113                                 new ColumnInfo ("ColumnSize", typeof(long)),
 114                                 new ColumnInfo ("CreateFormat", typeof(string)),
 115                                 new ColumnInfo ("CreateParameters", typeof(string)),
 116                                 new ColumnInfo ("DataType", typeof(string)),
 117                                 new ColumnInfo ("IsAutoIncrementable", typeof(bool)),
 118                                 new ColumnInfo ("IsBestMatch", typeof(bool)),
 119                                 new ColumnInfo ("IsCaseSensitive", typeof(bool)),
 120                                 new ColumnInfo ("IsFixedLength", typeof(bool)),
 121                                 new ColumnInfo ("IsFixedPrecisionScale", typeof(bool)),
 122                                 new ColumnInfo ("IsLong", typeof(bool)),
 123                                 new ColumnInfo ("IsNullable", typeof(bool)),
 124                                 new ColumnInfo ("IsSearchable", typeof(bool)),
 125                                 new ColumnInfo ("IsSearchableWithLike", typeof(bool)),
 126                                 new ColumnInfo ("IsUnsigned", typeof(bool)),
 127                                 new ColumnInfo ("MaximumScale", typeof(short)),
 128                                 new ColumnInfo ("MinimumScale", typeof(short)),
 129                                 new ColumnInfo ("IsConcurrencyType", typeof(bool)),
 130                                 new ColumnInfo ("IsLiteralSupported", typeof(bool)),
 131                                 new ColumnInfo ("LiteralPrefix", typeof(string)),
 132                                 new ColumnInfo ("LiteralSuffix", typeof(string))
 133                         };
 134
 135                         static readonly object [][] rows = {
 136                                 new object [] {"smallint", 16, 5, "smallint", null, "System.Int16", true, true,
 137                                                false, true, true, false, true, true, false, false, null,
 138                                                null, false, null, null, null},
 139                                 new object [] {"int", 8, 10, "int", null, "System.Int32",
 140                                                true, true, false, true, true, false, true, true, false,
 141                                                false, null, null, false, null, null, null},
 142                                 new object [] {"real", 13, 7, "real", null,
 143                                                "System.Single", false, true, false, true, false, false,
 144                                                true, true, false, false, null, null, false, null, null, null},
 145                                 new object [] {"float", 6, 53, "float({0})",
 146                                                "number of bits used to store the mantissa", "System.Double",
 147                                                false, true, false, true, false, false, true, true,
 148                                                false, false, null, null, false, null, null, null},
 149                                 new object [] {"money", 9, 19, "money", null,
 150                                                "System.Decimal", false, false, false, true, true,
 151                                                false, true, true, false, false, null, null, false,
 152                                                null, null, null},
 153                                 new object [] {"smallmoney", 17, 10, "smallmoney", null,
 154                                                "System.Decimal", false, false, false, true, true, false,
 155                                                true, true, false, false, null, null, false, null, null, null},
 156                                 new object [] {"bit", 2, 1, "bit", null, "System.Boolean",
 157                                                false, false, false, true, false, false, true, true,
 158                                                false, null, null, null, false, null, null, null},
 159                                 new object [] {"tinyint", 20, 3, "tinyint", null,
 160                                                "System.SByte", true, true, false, true, true, false,
 161                                                true, true, false, true, null, null, false, null, null, null},
 162                                 new object [] {"bigint", 0, 19, "bigint", null,
 163                                                "System.Int64", true, true, false, true, true, false,
 164                                                true, true, false, false, null, null, false, null, null, null},
 165                                 new object [] {"timestamp", 19, 8, "timestamp", null,
 166                                                "System.Byte[]", false, false, false, true, false, false,
 167                                                false, true, false, null, null, null, true, null, "0x", null},
 168                                 new object [] {"binary", 1, 8000, "binary({0})", "length",
 169                                                "System.Byte[]", false, true, false, true, false, false,
 170                                                true, true, false, null, null, null, false, null, "0x", null},
 171                                 new object [] {"image", 7, 2147483647, "image", null,
 172                                                "System.Byte[]", false, true, false, false, false, true,
 173                                                true, false, false, null, null, null, false, null, "0x", null},
 174                                 new object [] {"text", 18, 2147483647, "text", null,
 175                                                "System.String", false, true, false, false, false, true,
 176                                                true, false, true, null, null, null, false, null, "'", "'"},
 177                                 new object [] {"ntext", 11, 1073741823, "ntext", null,
 178                                                "System.String", false, true, false, false, false, true,
 179                                                true, false, true, null, null, null, false, null, "N'", "'"},
 180                                 new object [] {"decimal", 5, 38, "decimal({0}, {1})",
 181                                                "precision,scale", "System.Decimal", true, true, false,
 182                                                true, false, false, true, true, false, false, 38, 0,
 183                                                false, null, null, null},
 184                                 new object [] {"numeric", 5, 38, "numeric({0}, {1})",
 185                                                "precision,scale", "System.Decimal", true, true, false,
 186                                                true, false, false, true, true, false, false, 38, 0,
 187                                                false, null, null, null},
 188                                 new object [] {"datetime", 4, 23, "datetime", null,
 189                                                "System.DateTime", false, true, false, true, false, false,
 190                                                true, true, true, null, null, null, false, null, "{ts '", "'}"},
 191                                 new object [] {"smalldatetime", 15, 16, "smalldatetime", null,
 192                                                "System.DateTime", false, true, false, true, false, false,
 193                                                true, true, true, null, null, null, false, null, "{ts '", "'}"},
 194                                 new object [] {"sql_variant", 23, null, "sql_variant",
 195                                                null, "System.Object", false, true, false, false, false,
 196                                                false, true, true, false, null, null, null, false, false,
 197                                                null, null},
 198                                 new object [] {"xml", 25, 2147483647, "xml", null,
 199                                                "System.String", false, false, false, false, false, true,
 200                                                true, false, false, null, null, null, false, false, null, null},
 201                                 new object [] {"varchar", 22, 2147483647, "varchar({0})",
 202                                                "max length", "System.String", false, true, false, false,
 203                                                false, false, true, true, true, null, null, null, false,
 204                                                null, "'", "'"},
 205                                 new object [] {"char", 3, 2147483647, "char({0})", "length",
 206                                                "System.String", false, true, false, true, false, false,
 207                                                true, true, true, null, null, null, false, null, "'", "'"},
 208                                 new object [] {"nchar", 10, 1073741823, "nchar({0})", "length",
 209                                                "System.String", false, true, false, true, false, false,
 210                                                true, true, true, null, null, null, false, null, "N'", "'"},
 211                                 new object [] {"nvarchar", 12, 1073741823, "nvarchar({0})", "max length",
 212                                                "System.String", false, true, false, false, false, false, true, true,
 213                                                true, null, null, null, false, null, "N'", "'"},
 214                                 new object [] {"varbinary", 21, 1073741823, "varbinary({0})",
 215                                                "max length", "System.Byte[]", false, true, false, false,
 216                                                false, false, true, true, false, null, null, null, false,
 217                                                null, "0x", null},
 218                                 new object [] {"uniqueidentifier", 14, 16, "uniqueidentifier", null,
 219                                                "System.Guid", false, true, false, true, false, false, true,
 220                                                true, false, null, null, null, false, null, "'", "'"}
 221                         };
 222
 223                         static DataTable instance;
 224                         static public DataTable Instance {
 225                                 get {
 226                                         if (instance == null) {
 227                                                 instance = new DataTable ("DataTypes");
 228                                                 foreach (ColumnInfo c in columns)
 229                                                         instance.Columns.Add (c.name, c.type);
 230                                                 foreach (object [] row in rows)
 231                                                         instance.LoadDataRow (row, true);
 232                                         }
 233                                         return instance;
 234                                 }
 235                         }
 236                 }
 237
 238                 struct ColumnInfo {
 239                         public string name;
 240                         public Type type;
 241                         public ColumnInfo (string name, Type type)
 242                         {
 243                                 this.name = name; this.type = type;
 244                         }
 245                 }
 246
 247                 internal static class MetaDataCollections
 248                 {
 249                         static readonly ColumnInfo [] columns = {
 250                                 new ColumnInfo ("CollectionName", typeof (string)),
 251                                 new ColumnInfo ("NumberOfRestrictions", typeof (int)),
 252                                 new ColumnInfo ("NumberOfIdentifierParts", typeof (int))
 253                         };
 254
 255                         static readonly object [][] rows = {
 256                                 new object [] {"MetaDataCollections", 0, 0},
 257                                 new object [] {"DataSourceInformation", 0, 0},
 258                                 new object [] {"DataTypes", 0, 0},
 259                                 new object [] {"Restrictions", 0, 0},
 260                                 new object [] {"ReservedWords", 0, 0},
 261                                 new object [] {"Users", 1, 1},
 262                                 new object [] {"Databases", 1, 1},
 263                                 new object [] {"Tables", 4, 3},
 264                                 new object [] {"Columns", 4, 4},
 265                                 new object [] {"Views", 3, 3},
 266                                 new object [] {"ViewColumns", 4, 4},
 267                                 new object [] {"ProcedureParameters", 4, 1},
 268                                 new object [] {"Procedures", 4, 3},
 269                                 new object [] {"ForeignKeys", 4, 3},
 270                                 new object [] {"IndexColumns", 5, 4},
 271                                 new object [] {"Indexes", 4, 3},
 272                                 new object [] {"UserDefinedTypes", 2, 1}
 273                         };
 274
 275                         static DataTable instance;
 276                         static public DataTable Instance {
 277                                 get {
 278                                         if (instance == null) {
 279                                                 instance = new DataTable ("GetSchema");
 280                                                 foreach (ColumnInfo c in columns)
 281                                                         instance.Columns.Add (c.name, c.type);
 282                                                 foreach (object [] row in rows)
 283                                                         instance.LoadDataRow (row, true);
 284                                         }
 285                                         return instance;
 286                                 }
 287                         }
 288                 }
 289
 290                 static class Restrictions
 291                 {
 292                         static readonly ColumnInfo [] columns = {
 293                                 new ColumnInfo ("CollectionName", typeof (string)),
 294                                 new ColumnInfo ("RestrictionName", typeof(string)),
 295                                 new ColumnInfo ("ParameterName", typeof(string)),
 296                                 new ColumnInfo ("RestrictionDefault", typeof(string)),
 297                                 new ColumnInfo ("RestrictionNumber", typeof(int))
 298                         };
 299
 300                         static readonly object [][] rows = {
 301                                 new object [] {"Users", "User_Name", "@Name", "name", 1},
 302                                 new object [] {"Databases", "Name", "@Name", "Name", 1},
 303
 304                                 new object [] {"Tables", "Catalog", "@Catalog", "TABLE_CATALOG", 1},
 305                                 new object [] {"Tables", "Owner", "@Owner", "TABLE_SCHEMA", 2},
 306                                 new object [] {"Tables", "Table", "@Name", "TABLE_NAME", 3},
 307                                 new object [] {"Tables", "TableType", "@TableType", "TABLE_TYPE", 4},
 308
 309                                 new object [] {"Columns", "Catalog", "@Catalog", "TABLE_CATALOG", 1},
 310                                 new object [] {"Columns", "Owner", "@Owner", "TABLE_SCHEMA", 2},
 311                                 new object [] {"Columns", "Table", "@Table", "TABLE_NAME", 3},
 312                                 new object [] {"Columns", "Column", "@Column", "COLUMN_NAME", 4},
 313
 314                                 new object [] {"Views", "Catalog", "@Catalog", "TABLE_CATALOG", 1},
 315                                 new object [] {"Views", "Owner", "@Owner", "TABLE_SCHEMA", 2},
 316                                 new object [] {"Views", "Table", "@Table", "TABLE_NAME", 3},
 317
 318                                 new object [] {"ViewColumns", "Catalog", "@Catalog", "VIEW_CATALOG", 1},
 319                                 new object [] {"ViewColumns", "Owner", "@Owner", "VIEW_SCHEMA", 2},
 320                                 new object [] {"ViewColumns", "Table", "@Table", "VIEW_NAME", 3},
 321                                 new object [] {"ViewColumns", "Column", "@Column", "COLUMN_NAME", 4},
 322
 323                                 new object [] {"ProcedureParameters", "Catalog", "@Catalog", "SPECIFIC_CATALOG", 1},
 324                                 new object [] {"ProcedureParameters", "Owner", "@Owner", "SPECIFIC_SCHEMA", 2},
 325                                 new object [] {"ProcedureParameters", "Name", "@Name", "SPECIFIC_NAME", 3},
 326                                 new object [] {"ProcedureParameters", "Parameter", "@Parameter", "PARAMETER_NAME", 4},
 327
 328                                 new object [] {"Procedures", "Catalog", "@Catalog", "SPECIFIC_CATALOG", 1},
 329                                 new object [] {"Procedures", "Owner", "@Owner", "SPECIFIC_SCHEMA", 2},
 330                                 new object [] {"Procedures", "Name", "@Name", "SPECIFIC_NAME", 3},
 331                                 new object [] {"Procedures", "Type", "@Type", "ROUTINE_TYPE", 4},
 332
 333                                 new object [] {"IndexColumns", "Catalog", "@Catalog", "db_name(}", 1},
 334                                 new object [] {"IndexColumns", "Owner", "@Owner", "user_name(}", 2},
 335                                 new object [] {"IndexColumns", "Table", "@Table", "o.name", 3},
 336                                 new object [] {"IndexColumns", "ConstraintName", "@ConstraintName", "x.name", 4},
 337                                 new object [] {"IndexColumns", "Column", "@Column", "c.name", 5},
 338
 339                                 new object [] {"Indexes", "Catalog", "@Catalog", "db_name(}", 1},
 340                                 new object [] {"Indexes", "Owner", "@Owner", "user_name(}", 2},
 341                                 new object [] {"Indexes", "Table", "@Table", "o.name", 3},
 342                                 new object [] {"Indexes", "Name", "@Name", "x.name", 4},
 343
 344                                 new object [] {"UserDefinedTypes", "assembly_name", "@AssemblyName", "assemblies.name", 1},
 345                                 new object [] {"UserDefinedTypes", "udt_name", "@UDTName", "types.assembly_class", 2},
 346
 347                                 new object [] {"ForeignKeys", "Catalog", "@Catalog", "CONSTRAINT_CATALOG", 1},
 348                                 new object [] {"ForeignKeys", "Owner", "@Owner", "CONSTRAINT_SCHEMA", 2},
 349                                 new object [] {"ForeignKeys", "Table", "@Table", "TABLE_NAME", 3},
 350                                 new object [] {"ForeignKeys", "Name", "@Name", "CONSTRAINT_NAME", 4}
 351                         };
 352
 353                         static DataTable instance;
 354                         static public DataTable Instance {
 355                                 get {
 356                                         if (instance == null) {
 357                                                 instance = new DataTable ("Restrictions");
 358                                                 foreach (ColumnInfo c in columns)
 359                                                         instance.Columns.Add (c.name, c.type);
 360                                                 foreach (object [] row in rows)
 361                                                         instance.LoadDataRow (row, true);
 362                                         }
 363                                         return instance;
 364                                 }
 365                         }
 366                 }
 367
 368                 static class ReservedWords
 369                 {
 370                         static readonly string [] reservedWords =
 371                         {
 372                                 "ADD", "EXCEPT", "PERCENT", "ALL", "EXEC", "PLAN", "ALTER",
 373                                   "EXECUTE", "PRECISION", "AND", "EXISTS", "PRIMARY", "ANY",
 374                                   "EXIT", "PRINT", "AS", "FETCH", "PROC", "ASC", "FILE",
 375                                   "PROCEDURE", "AUTHORIZATION", "FILLFACTOR", "PUBLIC",
 376                                   "BACKUP", "FOR", "RAISERROR", "BEGIN", "FOREIGN", "READ",
 377                                   "BETWEEN", "FREETEXT", "READTEXT", "BREAK", "FREETEXTTABLE",
 378                                   "RECONFIGURE", "BROWSE", "FROM", "REFERENCES", "BULK",
 379                                   "FULL", "REPLICATION", "BY", "FUNCTION", "RESTORE",
 380                                   "CASCADE", "GOTO", "RESTRICT", "CASE", "GRANT", "RETURN",
 381                                   "CHECK", "GROUP", "REVOKE", "CHECKPOINT", "HAVING", "RIGHT",
 382                                   "CLOSE", "HOLDLOCK", "ROLLBACK", "CLUSTERED", "IDENTITY",
 383                                   "ROWCOUNT", "COALESCE", "IDENTITY_INSERT", "ROWGUIDCOL",
 384                                   "COLLATE", "IDENTITYCOL", "RULE", "COLUMN", "IF", "SAVE",
 385                                   "COMMIT", "IN", "SCHEMA", "COMPUTE", "INDEX", "SELECT",
 386                                   "CONSTRAINT", "INNER", "SESSION_USER", "CONTAINS", "INSERT",
 387                                   "SET", "CONTAINSTABLE", "INTERSECT", "SETUSER", "CONTINUE",
 388                                   "INTO", "SHUTDOWN", "CONVERT", "IS", "SOME", "CREATE",
 389                                   "JOIN", "STATISTICS", "CROSS", "KEY", "SYSTEM_USER",
 390                                   "CURRENT", "KILL", "TABLE", "CURRENT_DATE", "LEFT",
 391                                   "TEXTSIZE", "CURRENT_TIME", "LIKE", "THEN",
 392                                   "CURRENT_TIMESTAMP", "LINENO", "TO", "CURRENT_USER", "LOAD",
 393                                   "TOP", "CURSOR", "NATIONAL", "TRAN", "DATABASE", "NOCHECK",
 394                                   "TRANSACTION", "DBCC", "NONCLUSTERED", "TRIGGER",
 395                                   "DEALLOCATE", "NOT", "TRUNCATE", "DECLARE", "NULL",
 396                                   "TSEQUAL", "DEFAULT", "NULLIF", "UNION", "DELETE", "OF",
 397                                   "UNIQUE", "DENY", "OFF", "UPDATE", "DESC", "OFFSETS",
 398                                   "UPDATETEXT", "DISK", "ON", "USE", "DISTINCT", "OPEN",
 399                                   "USER", "DISTRIBUTED", "OPENDATASOURCE", "VALUES", "DOUBLE",
 400                                   "OPENQUERY", "VARYING", "DROP", "OPENROWSET", "VIEW",
 401                                   "DUMMY", "OPENXML", "WAITFOR", "DUMP", "OPTION", "WHEN",
 402                                   "ELSE", "OR", "WHERE", "END", "ORDER", "WHILE", "ERRLVL",
 403                                   "OUTER", "WITH", "ESCAPE", "OVER", "WRITETEXT", "ABSOLUTE",
 404                                   "FOUND", "PRESERVE", "ACTION", "FREE", "PRIOR", "ADMIN",
 405                                   "GENERAL", "PRIVILEGES", "AFTER", "GET", "READS",
 406                                   "AGGREGATE", "GLOBAL", "REAL", "ALIAS", "GO", "RECURSIVE",
 407                                   "ALLOCATE", "GROUPING", "REF", "ARE", "HOST", "REFERENCING",
 408                                   "ARRAY", "HOUR", "RELATIVE", "ASSERTION", "IGNORE", "RESULT",
 409                                   "AT", "IMMEDIATE", "RETURNS", "BEFORE", "INDICATOR", "ROLE",
 410                                   "BINARY", "INITIALIZE", "ROLLUP", "BIT", "INITIALLY",
 411                                   "ROUTINE", "BLOB", "INOUT", "ROW", "BOOLEAN", "INPUT",
 412                                   "ROWS", "BOTH", "INT", "SAVEPOINT", "BREADTH", "INTEGER",
 413                                   "SCROLL", "CALL", "INTERVAL", "SCOPE", "CASCADED",
 414                                   "ISOLATION", "SEARCH", "CAST", "ITERATE", "SECOND",
 415                                   "CATALOG", "LANGUAGE", "SECTION", "CHAR", "LARGE",
 416                                   "SEQUENCE", "CHARACTER", "LAST", "SESSION", "CLASS",
 417                                   "LATERAL", "SETS", "CLOB", "LEADING", "SIZE", "COLLATION",
 418                                   "LESS", "SMALLINT", "COMPLETION", "LEVEL", "SPACE",
 419                                   "CONNECT", "LIMIT", "SPECIFIC", "CONNECTION", "LOCAL",
 420                                   "SPECIFICTYPE", "CONSTRAINTS", "LOCALTIME", "SQL",
 421                                   "CONSTRUCTOR", "LOCALTIMESTAMP", "SQLEXCEPTION",
 422                                   "CORRESPONDING", "LOCATOR", "SQLSTATE", "CUBE", "MAP",
 423                                   "SQLWARNING", "CURRENT_PATH", "MATCH", "START",
 424                                   "CURRENT_ROLE", "MINUTE", "STATE", "CYCLE", "MODIFIES",
 425                                   "STATEMENT", "DATA", "MODIFY", "STATIC", "DATE", "MODULE",
 426                                   "STRUCTURE", "DAY", "MONTH", "TEMPORARY", "DEC", "NAMES",
 427                                   "TERMINATE", "DECIMAL", "NATURAL", "THAN", "DEFERRABLE",
 428                                   "NCHAR", "TIME", "DEFERRED", "NCLOB", "TIMESTAMP", "DEPTH",
 429                                   "NEW", "TIMEZONE_HOUR", "DEREF", "NEXT", "TIMEZONE_MINUTE",
 430                                   "DESCRIBE", "NO", "TRAILING", "DESCRIPTOR", "NONE",
 431                                   "TRANSLATION", "DESTROY", "NUMERIC", "TREAT", "DESTRUCTOR",
 432                                   "OBJECT", "TRUE", "DETERMINISTIC", "OLD", "UNDER",
 433                                   "DICTIONARY", "ONLY", "UNKNOWN", "DIAGNOSTICS", "OPERATION",
 434                                   "UNNEST", "DISCONNECT", "ORDINALITY", "USAGE", "DOMAIN",
 435                                   "OUT", "USING", "DYNAMIC", "OUTPUT", "VALUE", "EACH",
 436                                   "PAD", "VARCHAR", "END-EXEC", "PARAMETER", "VARIABLE",
 437                                   "EQUALS", "PARAMETERS", "WHENEVER", "EVERY", "PARTIAL",
 438                                   "WITHOUT", "EXCEPTION", "PATH", "WORK", "EXTERNAL",
 439                                   "POSTFIX", "WRITE", "FALSE", "PREFIX", "YEAR", "FIRST",
 440                                   "PREORDER", "ZONE", "FLOAT", "PREPARE", "ADA", "AVG",
 441                                   "BIT_LENGTH", "CHAR_LENGTH", "CHARACTER_LENGTH", "COUNT",
 442                                   "EXTRACT", "FORTRAN", "INCLUDE", "INSENSITIVE", "LOWER",
 443                                   "MAX", "MIN", "OCTET_LENGTH", "OVERLAPS", "PASCAL",
 444                                   "POSITION", "SQLCA", "SQLCODE", "SQLERROR", "SUBSTRING",
 445                                   "SUM", "TRANSLATE", "TRIM", "UPPER"
 446                         };
 447                         static DataTable instance;
 448                         static public DataTable Instance {
 449                                 get {
 450                                         if (instance == null) {
 451                                                 DataRow row = null;
 452                                                 instance = new DataTable ("ReservedWords");
 453                                                 instance.Columns.Add ("ReservedWord", typeof(string));
 454                                                 foreach (string reservedWord in reservedWords)
 455                                                 {
 456                                                         row = instance.NewRow();
 457
 458                                                         row["ReservedWord"] = reservedWord;
 459                                                         instance.Rows.Add(row);
 460                                                 }
 461                                         }
 462                                         return instance;
 463                                 }
 464                         }
 465                 }
 466
 467                 public virtual DataTable GetSchema ()
 468                 {
 469                         return MetaDataCollections.Instance;
 470                 }
 471
 472                 public virtual DataTable GetSchema (string collectionName)
 473                 {
 474                         return GetSchema (collectionName, null);
 475                 }
 476
 477                 private void AddParameter (DbCommand command, string parameterName, DbType parameterType, int parameterSize)
 478                 {
 479                         DbParameter parameter = command.CreateParameter ();
 480                         parameter.ParameterName = parameterName;
 481                         parameter.DbType = parameterType;
 482                         parameter.Size = parameterSize;
 483                         command.Parameters.Add (parameter);
 484                 }
 485
 486                 public virtual DataTable GetSchema (string collectionName, string[] restrictionValues)
 487                 {
 488                         if (collectionName == null)
 489                                 //LAMESPEC: In MS.NET, if collectionName is null, it throws ArgumentException.
 490                                 throw new ArgumentException ();
 491
 492                         String cName          = null;
 493                         DataTable schemaTable = MetaDataCollections.Instance;
 494                         int length = restrictionValues == null ? 0 : restrictionValues.Length;
 495
 496                         foreach (DataRow row in schemaTable.Rows) {
 497                                 if (String.Compare ((string) row ["CollectionName"], collectionName, true) == 0) {
 498                                         if (length > (int) row ["NumberOfRestrictions"]) {
 499                                                 throw new ArgumentException ("More restrictions were provided " +
 500                                                                              "than the requested schema ('" +
 501                                                                              row ["CollectionName"].ToString () + "') supports");
 502                                         }
 503                                         cName = row ["CollectionName"].ToString ();
 504                                 }
 505                         }
 506                         if (cName == null)
 507                                 throw new ArgumentException ("The requested collection ('" + collectionName + "') is not defined.");
 508
 509                         DbCommand command     = null;
 510                         DataTable dataTable   = new DataTable ();
 511
 512                         switch (cName)
 513                         {
 514                         case "Databases":
 515                                 command = CreateCommand ();
 516                                 command.Connection = this;
 517                                 command.CommandText = "select name as database_name, dbid, crdate as create_date " +
 518                                   "from master.sys.sysdatabases where (name = @Name or (@Name " +
 519                                   "is null))";
 520                                 AddParameter (command, "@Name", DbType.StringFixedLength, 4000);
 521                                 break;
 522                         case "ForeignKeys":
 523                                 command = CreateCommand ();
 524                                 command.Connection = this;
 525                                 command.CommandText = "select CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME, " +
 526                                   "TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, CONSTRAINT_TYPE, " +
 527                                   "IS_DEFERRABLE, INITIALLY_DEFERRED from " +
 528                                   "INFORMATION_SCHEMA.TABLE_CONSTRAINTS where (CONSTRAINT_CATALOG" +
 529                                   " = @Catalog or (@Catalog is null)) and (CONSTRAINT_SCHEMA = " +
 530                                   "@Owner or (@Owner is null)) and (TABLE_NAME = @Table or (" +
 531                                   "@Table is null)) and (CONSTRAINT_NAME = @Name or (@Name is null))" +
 532                                   " and CONSTRAINT_TYPE = 'FOREIGN KEY' order by CONSTRAINT_CATALOG," +
 533                                   " CONSTRAINT_SCHEMA, CONSTRAINT_NAME";
 534                                 AddParameter (command, "@Catalog", DbType.StringFixedLength, 4000);
 535                                 AddParameter (command, "@Owner", DbType.StringFixedLength, 4000);
 536                                 AddParameter (command, "@Table", DbType.StringFixedLength, 4000);
 537                                 AddParameter (command, "@Name", DbType.StringFixedLength, 4000);
 538                                 break;
 539                         case "Indexes":
 540                                 command = CreateCommand ();
 541                                 command.Connection = this;
 542                                 command.CommandText = "select distinct db_name() as constraint_catalog, " +
 543                                   "constraint_schema = user_name (o.uid), " +
 544                                   "constraint_name = x.name, table_catalog = db_name (), " +
 545                                   "table_schema = user_name (o.uid), table_name = o.name, " +
 546                                   "index_name  = x.name from sysobjects o, sysindexes x, " +
 547                                   "sysindexkeys xk where o.type in ('U') and x.id = o.id and " +
 548                                   "o.id = xk.id and x.indid = xk.indid and xk.keyno = x.keycnt " +
 549                                   "and (db_name() = @Catalog or (@Catalog is null)) and " +
 550                                   "(user_name() = @Owner or (@Owner is null)) and (o.name = " +
 551                                   "@Table or (@Table is null)) and (x.name = @Name or (@Name is null))" +
 552                                   "order by table_name, index_name";
 553                                 AddParameter (command, "@Catalog", DbType.StringFixedLength, 4000);
 554                                 AddParameter (command, "@Owner", DbType.StringFixedLength, 4000);
 555                                 AddParameter (command, "@Table", DbType.StringFixedLength, 4000);
 556                                 AddParameter (command, "@Name", DbType.StringFixedLength, 4000);
 557                                 break;
 558                         case "IndexColumns":
 559                                 command = CreateCommand ();
 560                                 command.Connection = this;
 561                                 command.CommandText = "select distinct db_name() as constraint_catalog, " +
 562                                   "constraint_schema = user_name (o.uid), constraint_name = x.name, " +
 563                                   "table_catalog = db_name (), table_schema = user_name (o.uid), " +
 564                                   "table_name = o.name, column_name = c.name, " +
 565                                   "ordinal_position = convert (int, xk.keyno), keyType = c.xtype, " +
 566                                   "index_name = x.name from sysobjects o, sysindexes x, syscolumns c, " +
 567                                   "sysindexkeys xk where o.type in ('U') and x.id = o.id and o.id = c.id " +
 568                                   "and o.id = xk.id and x.indid = xk.indid and c.colid = xk.colid " +
 569                                   "and xk.keyno <= x.keycnt and permissions (o.id, c.name) <> 0 " +
 570                                   "and (db_name() = @Catalog or (@Catalog is null)) and (user_name() " +
 571                                   "= @Owner or (@Owner is null)) and (o.name = @Table or (@Table is" +
 572                                   " null)) and (x.name = @ConstraintName or (@ConstraintName is null)) " +
 573                                   "and (c.name = @Column or (@Column is null)) order by table_name, " +
 574                                   "index_name";
 575                                 AddParameter (command, "@Catalog", DbType.StringFixedLength, 8);
 576                                 AddParameter (command, "@Owner", DbType.StringFixedLength, 4000);
 577                                 AddParameter (command, "@Table", DbType.StringFixedLength, 13);
 578                                 AddParameter (command, "@ConstraintName", DbType.StringFixedLength, 4000);
 579                                 AddParameter (command, "@Column", DbType.StringFixedLength, 4000);
 580                                 break;
 581                         case "Procedures":
 582                                 command = CreateCommand ();
 583                                 command.Connection = this;
 584                                 command.CommandText = "select SPECIFIC_CATALOG, SPECIFIC_SCHEMA, SPECIFIC_NAME, " +
 585                                   "ROUTINE_CATALOG, ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE, " +
 586                                   "CREATED, LAST_ALTERED from INFORMATION_SCHEMA.ROUTINES where " +
 587                                   "(SPECIFIC_CATALOG = @Catalog or (@Catalog is null)) and " +
 588                                   "(SPECIFIC_SCHEMA = @Owner or (@Owner is null)) and (SPECIFIC_NAME" +
 589                                   " = @Name or (@Name is null)) and (ROUTINE_TYPE = @Type or (@Type " +
 590                                   "is null)) order by SPECIFIC_CATALOG, SPECIFIC_SCHEMA, SPECIFIC_NAME";
 591                                 AddParameter (command, "@Catalog", DbType.StringFixedLength, 4000);
 592                                 AddParameter (command, "@Owner", DbType.StringFixedLength, 4000);
 593                                 AddParameter (command, "@Name", DbType.StringFixedLength, 4000);
 594                                 AddParameter (command, "@Type", DbType.StringFixedLength, 4000);
 595                                 break;
 596                         case "ProcedureParameters":
 597                                 command = CreateCommand ();
 598                                 command.Connection = this;
 599                                 command.CommandText = "select SPECIFIC_CATALOG, SPECIFIC_SCHEMA, SPECIFIC_NAME, " +
 600                                   "ORDINAL_POSITION, PARAMETER_MODE, IS_RESULT, AS_LOCATOR, " +
 601                                   "PARAMETER_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, " +
 602                                   "CHARACTER_OCTET_LENGTH, COLLATION_CATALOG, COLLATION_SCHEMA, " +
 603                                   "COLLATION_NAME, CHARACTER_SET_CATALOG, CHARACTER_SET_SCHEMA, " +
 604                                   "CHARACTER_SET_NAME, NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX, " +
 605                                   "NUMERIC_SCALE, DATETIME_PRECISION, INTERVAL_TYPE, " +
 606                                   "INTERVAL_PRECISION from INFORMATION_SCHEMA.PARAMETERS where " +
 607                                   "(SPECIFIC_CATALOG = @Catalog or (@Catalog is null)) and " +
 608                                   "(SPECIFIC_SCHEMA = @Owner or (@Owner is null)) and (SPECIFIC_NAME = " +
 609                                   "@Name or (@Name is null)) and (PARAMETER_NAME = @Parameter or (" +
 610                                   "@Parameter is null)) order by SPECIFIC_CATALOG, SPECIFIC_SCHEMA," +
 611                                   " SPECIFIC_NAME, PARAMETER_NAME";
 612                                 AddParameter (command, "@Catalog", DbType.StringFixedLength, 4000);
 613                                 AddParameter (command, "@Owner", DbType.StringFixedLength, 4000);
 614                                 AddParameter (command, "@Name", DbType.StringFixedLength, 4000);
 615                                 AddParameter (command, "@Parameter", DbType.StringFixedLength, 4000);
 616                                 break;
 617                         case "Tables":
 618                                 command = CreateCommand ();
 619                                 command.Connection = this;
 620                                 command.CommandText = "select TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE " +
 621                                   "from INFORMATION_SCHEMA.TABLES where" +
 622                                   " (TABLE_CATALOG = @catalog or (@catalog is null)) and " +
 623                                   "(TABLE_SCHEMA = @owner or (@owner is null))and " +
 624                                   "(TABLE_NAME = @name or (@name is null)) and " +
 625                                   "(TABLE_TYPE = @table_type or (@table_type is null))";
 626                                 AddParameter (command, "@catalog", DbType.StringFixedLength, 8);
 627                                 AddParameter (command, "@owner", DbType.StringFixedLength, 3);
 628                                 AddParameter (command, "@name", DbType.StringFixedLength, 11);
 629                                 AddParameter (command, "@table_type", DbType.StringFixedLength, 10);
 630                                 break;
 631                         case "Columns":
 632                                 command = CreateCommand ();
 633                                 command.Connection = this;
 634                                 command.CommandText = "select TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, " +
 635                                   "ORDINAL_POSITION, COLUMN_DEFAULT, IS_NULLABLE, DATA_TYPE, " +
 636                                   "CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH, " +
 637                                   "NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX, NUMERIC_SCALE, " +
 638                                   "DATETIME_PRECISION, CHARACTER_SET_CATALOG, CHARACTER_SET_SCHEMA, " +
 639                                   "CHARACTER_SET_NAME, COLLATION_CATALOG from INFORMATION_SCHEMA.COLUMNS" +
 640                                   " where (TABLE_CATALOG = @Catalog or (@Catalog is null)) and (" +
 641                                   "TABLE_SCHEMA = @Owner or (@Owner is null)) and (TABLE_NAME = @table" +
 642                                   " or (@Table is null)) and (COLUMN_NAME = @column or (@Column is null" +
 643                                   ")) order by TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME";
 644                                 AddParameter (command, "@Catalog", DbType.StringFixedLength, 4000);
 645                                 AddParameter (command, "@Owner", DbType.StringFixedLength, 4000);
 646                                 AddParameter (command, "@Table", DbType.StringFixedLength, 4000);
 647                                 AddParameter (command, "@Column", DbType.StringFixedLength, 4000);
 648                                 break;
 649                         case "Users":
 650                                 command = CreateCommand ();
 651                                 command.Connection = this;
 652                                 command.CommandText = "select uid, name as user_name, createdate, updatedate from sysusers" +
 653                                   " where (name = @Name or (@Name is null))";
 654                                 AddParameter (command, "@Name", DbType.StringFixedLength, 4000);
 655                                 break;
 656                         case "Views":
 657                                 command = CreateCommand ();
 658                                 command.Connection = this;
 659                                 command.CommandText = "select TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, CHECK_OPTION, " +
 660                                   "IS_UPDATABLE from INFORMATION_SCHEMA.VIEWS where (TABLE_CATALOG" +
 661                                   " = @Catalog or (@Catalog is null)) TABLE_SCHEMA = @Owner or " +
 662                                   "(@Owner is null)) and (TABLE_NAME = @table or (@Table is null))" +
 663                                   " order by TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME";
 664                                 AddParameter (command, "@Catalog", DbType.StringFixedLength, 4000);
 665                                 AddParameter (command, "@Owner", DbType.StringFixedLength, 4000);
 666                                 AddParameter (command, "@Table", DbType.StringFixedLength, 4000);
 667                                 break;
 668                         case "ViewColumns":
 669                                 command = CreateCommand ();
 670                                 command.Connection = this;
 671                                 command.CommandText = "select VIEW_CATALOG, VIEW_SCHEMA, VIEW_NAME, TABLE_CATALOG, " +
 672                                   "TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME from " +
 673                                   "INFORMATION_SCHEMA.VIEW_COLUMN_USAGE where (VIEW_CATALOG = " +
 674                                   "@Catalog (@Catalog is null)) and (VIEW_SCHEMA = @Owner (@Owner" +
 675                                   " is null)) and (VIEW_NAME = @Table or (@Table is null)) and " +
 676                                   "(COLUMN_NAME = @Column or (@Column is null)) order by " +
 677                                   "VIEW_CATALOG, VIEW_SCHEMA, VIEW_NAME";
 678                                 AddParameter (command, "@Catalog", DbType.StringFixedLength, 4000);
 679                                 AddParameter (command, "@Owner", DbType.StringFixedLength, 4000);
 680                                 AddParameter (command, "@Table", DbType.StringFixedLength, 4000);
 681                                 AddParameter (command, "@Column", DbType.StringFixedLength, 4000);
 682                                 break;
 683                         case "UserDefinedTypes":
 684                                 command = CreateCommand ();
 685                                 command.Connection = this;
 686                                 command.CommandText = "select assemblies.name as assembly_name, types.assembly_class " +
 687                                   "as udt_name, ASSEMBLYPROPERTY(assemblies.name, 'VersionMajor') " +
 688                                   "as version_major, ASSEMBLYPROPERTY(assemblies.name, 'VersionMinor') " +
 689                                   "as version_minor, ASSEMBLYPROPERTY(assemblies.name, 'VersionBuild') " +
 690                                   "as version_build, ASSEMBLYPROPERTY(assemblies.name, 'VersionRevision') " +
 691                                   "as version_revision, ASSEMBLYPROPERTY(assemblies.name, 'CultureInfo') " +
 692                                   "as culture_info, ASSEMBLYPROPERTY(assemblies.name, 'PublicKey') " +
 693                                   "as public_key, is_fixed_length, max_length, Create_Date, " +
 694                                   "Permission_set_desc from sys.assemblies as assemblies join " +
 695                                   "sys.assembly_types as types on assemblies.assembly_id = types.assembly_id" +
 696                                   " where (assemblies.name = @AssemblyName or (@AssemblyName is null)) and " +
 697                                   "(types.assembly_class = @UDTName or (@UDTName is null))";
 698                                 AddParameter (command, "@AssemblyName", DbType.StringFixedLength, 4000);
 699                                 AddParameter (command, "@UDTName", DbType.StringFixedLength, 4000);
 700                                 break;
 701                         case "MetaDataCollections":
 702                                 return MetaDataCollections.Instance;
 703                         case "DataSourceInformation":
 704                                 throw new NotImplementedException ();
 705                         case "DataTypes":
 706                                 return DataTypes.Instance;
 707                         case "ReservedWords":
 708                                 return ReservedWords.Instance;
 709                         case "Restrictions":
 710                                 return Restrictions.Instance;
 711                         }
 712                         for (int i = 0; i < length; i++) {
 713                                 command.Parameters [i].Value = restrictionValues [i];
 714                         }
 715                         DbDataAdapter dataAdapter = DbProviderFactory.CreateDataAdapter ();
 716                         dataAdapter.SelectCommand = command;
 717                         dataAdapter.Fill (dataTable);
 718                         return dataTable;
 719                 }
 720
 721                 protected internal virtual DbProviderFactory DbProviderFactory {
 722                         get {
 723                                 return null;
 724                         }
 725                 }
 726 #endif
 727
 728                 IDbTransaction IDbConnection.BeginTransaction ()
 729                 {
 730                         return BeginTransaction ();
 731                 }
 732
 733                 IDbTransaction IDbConnection.BeginTransaction (IsolationLevel il)
 734                 {
 735                         return BeginTransaction (il);
 736                 }
 737
 738                 IDbCommand IDbConnection.CreateCommand ()
 739                 {
 740                         return CreateCommand ();
 741                 }
 742
 743                 public abstract void Open ();
 744
 745                 protected virtual void OnStateChange (StateChangeEventArgs stateChange)
 746                 {
 747                         if (StateChange != null)
 748                                 StateChange (this, stateChange);
 749                 }
 750
 751 #if NET_4_5
 752                 public Task OpenAsync ()
 753                 {
 754                         return OpenAsync (CancellationToken.None);
 755                 }
 756
 757                 public virtual Task OpenAsync (CancellationToken cancellationToken)
 758                 {
 759                         if (cancellationToken.IsCancellationRequested) {
 760                                 return TaskHelper.CreateCanceledTask ();
 761                         }
 762
 763                         try {
 764                                 Open ();
 765                                 return TaskHelper.CreateVoidTask ();
 766                         } catch (Exception e) {
 767                                 return TaskHelper.CreateExceptionTask (e);
 768                         }
 769                 }
 770 #endif
 771
 772                 #endregion // Methods
 773
 774                 public virtual event StateChangeEventHandler StateChange;
 775
 776         }
 777 }