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