2 // System.Data.Common.DbConnection
5 // Tim Coleman (tim@timcoleman.com)
7 // Copyright (C) Tim Coleman, 2003
11 // Copyright (C) 2004 Novell, Inc (http://www.novell.com)
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:
21 // The above copyright notice and this permission notice shall be
22 // included in all copies or substantial portions of the Software.
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.
33 #if NET_2_0 || TARGET_JVM
35 using System.ComponentModel;
37 #if NET_2_0 && !TARGET_JVM
38 using System.Transactions;
41 namespace System.Data.Common {
42 public abstract class DbConnection : Component, IDbConnection, IDisposable
46 protected DbConnection ()
50 #endregion // Constructors
53 #pragma warning disable 618
54 [RecommendedAsConfigurable (true)]
55 #pragma warning restore 618
56 [RefreshProperties (RefreshProperties.All)]
58 public abstract string ConnectionString { get; set; }
60 public abstract string Database { get; }
61 public abstract string DataSource { get; }
64 public abstract string ServerVersion { get; }
67 public abstract ConnectionState State { get; }
69 public virtual int ConnectionTimeout {
73 #endregion // Properties
77 protected abstract DbTransaction BeginDbTransaction (IsolationLevel isolationLevel);
79 public DbTransaction BeginTransaction ()
81 return BeginDbTransaction (IsolationLevel.Unspecified);
84 public DbTransaction BeginTransaction (IsolationLevel isolationLevel)
86 return BeginDbTransaction (isolationLevel);
89 public abstract void ChangeDatabase (string databaseName);
90 public abstract void Close ();
92 public DbCommand CreateCommand ()
94 return CreateDbCommand ();
97 protected abstract DbCommand CreateDbCommand ();
99 #if NET_2_0 && !TARGET_JVM
100 public virtual void EnlistTransaction (Transaction transaction)
102 throw new NotSupportedException ();
107 static class DataTypes
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))
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,
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,
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,
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,
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, "'", "'"}
222 static DataTable instance;
223 static public DataTable Instance {
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);
240 public ColumnInfo (string name, Type type)
242 this.name = name; this.type = type;
246 internal static class MetaDataCollections
248 static readonly ColumnInfo [] columns = {
249 new ColumnInfo ("CollectionName", typeof (string)),
250 new ColumnInfo ("NumberOfRestrictions", typeof (int)),
251 new ColumnInfo ("NumberOfIdentifierParts", typeof (int))
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}
274 static DataTable instance;
275 static public DataTable Instance {
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);
289 static class Restrictions
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))
299 static readonly object [][] rows = {
300 new object [] {"Users", "User_Name", "@Name", "name", 1},
301 new object [] {"Databases", "Name", "@Name", "Name", 1},
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},
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},
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},
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},
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},
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},
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},
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},
343 new object [] {"UserDefinedTypes", "assembly_name", "@AssemblyName", "assemblies.name", 1},
344 new object [] {"UserDefinedTypes", "udt_name", "@UDTName", "types.assembly_class", 2},
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}
352 static DataTable instance;
353 static public DataTable Instance {
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);
367 static class ReservedWords
369 static readonly string [] reservedWords =
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"
446 static DataTable instance;
447 static public DataTable Instance {
449 if (instance == null) {
451 instance = new DataTable ("ReservedWords");
452 instance.Columns.Add ("ReservedWord", typeof(string));
453 foreach (string reservedWord in reservedWords)
455 row = instance.NewRow();
457 row["ReservedWord"] = reservedWord;
458 instance.Rows.Add(row);
466 public virtual DataTable GetSchema ()
468 return MetaDataCollections.Instance;
471 public virtual DataTable GetSchema (string collectionName)
473 return GetSchema (collectionName, null);
476 private void AddParameter (DbCommand command, string parameterName, DbType parameterType, int parameterSize)
478 DbParameter parameter = command.CreateParameter ();
479 parameter.ParameterName = parameterName;
480 parameter.DbType = parameterType;
481 parameter.Size = parameterSize;
482 command.Parameters.Add (parameter);
485 public virtual DataTable GetSchema (string collectionName, string[] restrictionValues)
487 if (collectionName == null)
488 //LAMESPEC: In MS.NET, if collectionName is null, it throws ArgumentException.
489 throw new ArgumentException ();
492 DataTable schemaTable = MetaDataCollections.Instance;
493 int length = restrictionValues == null ? 0 : restrictionValues.Length;
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");
502 cName = row ["CollectionName"].ToString ();
506 throw new ArgumentException ("The requested collection ('" + collectionName + "') is not defined.");
508 DbCommand command = null;
509 DataTable dataTable = new DataTable ();
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 " +
519 AddParameter (command, "@Name", DbType.StringFixedLength, 4000);
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);
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);
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, " +
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);
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);
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);
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);
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);
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);
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);
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);
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);
700 case "MetaDataCollections":
701 return MetaDataCollections.Instance;
702 case "DataSourceInformation":
703 throw new NotImplementedException ();
705 return DataTypes.Instance;
706 case "ReservedWords":
707 return ReservedWords.Instance;
709 return Restrictions.Instance;
711 for (int i = 0; i < length; i++) {
712 command.Parameters [i].Value = restrictionValues [i];
714 DbDataAdapter dataAdapter = DbProviderFactory.CreateDataAdapter ();
715 dataAdapter.SelectCommand = command;
716 dataAdapter.Fill (dataTable);
720 protected virtual DbProviderFactory DbProviderFactory {
722 get {throw new NotImplementedException();}
724 get { return DbProviderFactories.GetFactory (this.GetType (). ToString ()); }
729 IDbTransaction IDbConnection.BeginTransaction ()
731 return BeginTransaction ();
734 IDbTransaction IDbConnection.BeginTransaction (IsolationLevel il)
736 return BeginTransaction (il);
739 IDbCommand IDbConnection.CreateCommand ()
741 return CreateCommand ();
744 public abstract void Open ();
746 protected virtual void OnStateChange (StateChangeEventArgs stateChange)
748 if (StateChange != null)
749 StateChange (this, stateChange);
752 #endregion // Methods
754 public virtual event StateChangeEventHandler StateChange;