2 // System.Data.SqlClient.SqlConnection.cs
5 // Rodrigo Moya (rodrigo@ximian.com)
6 // Daniel Morgan (danmorg@sc.rr.com)
7 // Tim Coleman (tim@timcoleman.com)
8 // Phillip Jerkins (Phillip.Jerkins@morgankeegan.com)
9 // Diego Caravana (diego@toth.it)
11 // Copyright (C) Ximian, Inc 2002
12 // Copyright (C) Daniel Morgan 2002, 2003
13 // Copyright (C) Tim Coleman, 2002, 2003
14 // Copyright (C) Phillip Jerkins, 2003
18 // Copyright (C) 2004 Novell, Inc (http://www.novell.com)
20 // Permission is hereby granted, free of charge, to any person obtaining
21 // a copy of this software and associated documentation files (the
22 // "Software"), to deal in the Software without restriction, including
23 // without limitation the rights to use, copy, modify, merge, publish,
24 // distribute, sublicense, and/or sell copies of the Software, and to
25 // permit persons to whom the Software is furnished to do so, subject to
26 // the following conditions:
28 // The above copyright notice and this permission notice shall be
29 // included in all copies or substantial portions of the Software.
31 // THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
32 // EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
33 // MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
34 // NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE
35 // LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION
36 // OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION
37 // WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
41 using Mono.Data.Tds.Protocol;
43 using System.Collections;
44 using System.Collections.Specialized;
45 using System.ComponentModel;
47 using System.Data.Common;
48 using System.EnterpriseServices;
49 using System.Globalization;
51 using System.Net.Sockets;
55 namespace System.Data.SqlClient {
56 [DefaultEvent ("InfoMessage")]
58 public sealed class SqlConnection : DbConnection, IDbConnection, ICloneable
60 public sealed class SqlConnection : Component, IDbConnection, ICloneable
64 bool disposed = false;
66 // The set of SQL connection pools
67 static TdsConnectionPoolManager sqlConnectionPools = new TdsConnectionPoolManager (TdsVersion.tds70);
69 // The current connection pool
70 TdsConnectionPool pool;
72 // The connection string that identifies this connection
73 string connectionString = null;
75 // The transaction object for the current transaction
76 SqlTransaction transaction = null;
78 // Connection parameters
80 TdsConnectionParameters parms = new TdsConnectionParameters ();
81 NameValueCollection connStringParameters = null;
85 int connectionTimeout;
92 ConnectionState state = ConnectionState.Closed;
94 SqlDataReader dataReader = null;
95 XmlReader xmlReader = null;
104 public SqlConnection ()
105 : this (String.Empty)
109 public SqlConnection (string connectionString)
111 Init (connectionString);
114 private void Init (string connectionString)
116 connectionTimeout = 15; // default timeout
117 dataSource = ""; // default datasource
118 packetSize = 8192; // default packetsize
119 ConnectionString = connectionString;
123 #endregion // Constructors
127 [DataCategory ("Data")]
129 [DataSysDescription ("Information used to connect to a DataSource, such as 'Data Source=x;Initial Catalog=x;Integrated Security=SSPI'.")]
132 [EditorAttribute ("Microsoft.VSDesigner.Data.SQL.Design.SqlConnectionStringEditor, "+ Consts.AssemblyMicrosoft_VSDesigner, "System.Drawing.Design.UITypeEditor, "+ Consts.AssemblySystem_Drawing )]
133 [RecommendedAsConfigurable (true)]
134 [RefreshProperties (RefreshProperties.All)]
135 [MonoTODO("persist security info, encrypt, enlist and , attachdbfilename keyword not implemented")]
140 string ConnectionString {
141 get { return connectionString; }
143 if (state == ConnectionState.Open)
144 throw new InvalidOperationException ("Not Allowed to change ConnectionString property while Connection state is OPEN");
145 SetConnectionString (value);
150 [DataSysDescription ("Current connection timeout value, 'Connect Timeout=X' in the ConnectionString.")]
152 [DesignerSerializationVisibility (DesignerSerializationVisibility.Hidden)]
158 int ConnectionTimeout {
159 get { return connectionTimeout; }
163 [DataSysDescription ("Current SQL Server database, 'Initial Catalog=X' in the connection string.")]
165 [DesignerSerializationVisibility (DesignerSerializationVisibility.Hidden)]
172 if (State == ConnectionState.Open)
174 return parms.Database ;
178 internal SqlDataReader DataReader {
179 get { return dataReader; }
180 set { dataReader = value; }
184 [DataSysDescription ("Current SqlServer that the connection is opened to, 'Data Source=X' in the connection string. ")]
186 [DesignerSerializationVisibility (DesignerSerializationVisibility.Hidden)]
192 get { return dataSource; }
196 [DataSysDescription ("Network packet size, 'Packet Size=x' in the connection string.")]
198 [DesignerSerializationVisibility (DesignerSerializationVisibility.Hidden)]
199 public int PacketSize {
201 if (State == ConnectionState.Open)
202 return ((Tds)tds).PacketSize ;
209 [DataSysDescription ("Version of the SQL Server accessed by the SqlConnection.")]
211 [DesignerSerializationVisibility (DesignerSerializationVisibility.Hidden)]
216 string ServerVersion {
218 if (state == ConnectionState.Closed)
219 throw new InvalidOperationException ("Invalid Operation.The Connection is Closed");
221 return tds.ServerVersion;
227 [DataSysDescription ("The ConnectionState indicating whether the connection is open or closed.")]
229 [DesignerSerializationVisibility (DesignerSerializationVisibility.Hidden)]
234 ConnectionState State {
235 get { return state; }
242 internal SqlTransaction Transaction {
243 get { return transaction; }
244 set { transaction = value; }
248 [DataSysDescription ("Workstation Id, 'Workstation ID=x' in the connection string.")]
250 [DesignerSerializationVisibility (DesignerSerializationVisibility.Hidden)]
251 public string WorkstationId {
252 get { return parms.Hostname; }
255 internal XmlReader XmlReader {
256 get { return xmlReader; }
257 set { xmlReader = value; }
260 #endregion // Properties
264 [DataCategory ("InfoMessage")]
266 [DataSysDescription ("Event triggered when messages arrive from the DataSource.")]
268 public event SqlInfoMessageEventHandler InfoMessage;
270 [DataCategory ("StateChange")]
272 [DataSysDescription ("Event triggered when the connection changes state.")]
278 event StateChangeEventHandler StateChange;
284 private void ErrorHandler (object sender, TdsInternalErrorMessageEventArgs e)
286 throw new SqlException (e.Class, e.LineNumber, e.Message, e.Number, e.Procedure, e.Server, "Mono SqlClient Data Provider", e.State);
289 private void MessageHandler (object sender, TdsInternalInfoMessageEventArgs e)
291 OnSqlInfoMessage (CreateSqlInfoMessageEvent (e.Errors));
294 #endregion // Delegates
298 internal string GetConnStringKeyValue (params string [] keys)
300 if (connStringParameters == null || connStringParameters.Count == 0)
302 foreach (string key in keys) {
303 string value = connStringParameters [key];
312 public new SqlTransaction BeginTransaction ()
314 return BeginTransaction (IsolationLevel.ReadCommitted, String.Empty);
317 public new SqlTransaction BeginTransaction (IsolationLevel iso)
319 return BeginTransaction (iso, String.Empty);
322 public SqlTransaction BeginTransaction (string transactionName)
324 return BeginTransaction (IsolationLevel.ReadCommitted, transactionName);
327 public SqlTransaction BeginTransaction (IsolationLevel iso, string transactionName)
329 if (state == ConnectionState.Closed)
330 throw new InvalidOperationException ("The connection is not open.");
331 if (transaction != null)
332 throw new InvalidOperationException ("SqlConnection does not support parallel transactions.");
334 if (iso == IsolationLevel.Chaos)
335 throw new ArgumentException ("Invalid IsolationLevel parameter: must be ReadCommitted, ReadUncommitted, RepeatableRead, or Serializable.");
337 string isolevel = String.Empty;
339 case IsolationLevel.ReadCommitted:
340 isolevel = "READ COMMITTED";
342 case IsolationLevel.ReadUncommitted:
343 isolevel = "READ UNCOMMITTED";
345 case IsolationLevel.RepeatableRead:
346 isolevel = "REPEATABLE READ";
348 case IsolationLevel.Serializable:
349 isolevel = "SERIALIZABLE";
353 tds.Execute (String.Format ("SET TRANSACTION ISOLATION LEVEL {0};BEGIN TRANSACTION {1}", isolevel, transactionName));
355 transaction = new SqlTransaction (this, iso);
363 void ChangeDatabase (string database)
365 if (!IsValidDatabaseName (database))
366 throw new ArgumentException (String.Format ("The database name {0} is not valid.", database));
367 if (state != ConnectionState.Open)
368 throw new InvalidOperationException ("The connection is not open.");
369 tds.Execute (String.Format ("use [{0}]", database));
372 private void ChangeState (ConnectionState currentState)
374 ConnectionState originalState = state;
375 state = currentState;
376 OnStateChange (CreateStateChangeEvent (originalState, currentState));
385 if (transaction != null && transaction.IsOpen)
386 transaction.Rollback ();
388 if (dataReader != null || xmlReader != null) {
389 if(tds != null) tds.SkipToEnd ();
395 if(pool != null) pool.ReleaseConnection (tds);
397 if(tds != null) tds.Disconnect ();
400 tds.TdsErrorMessage -= new TdsInternalErrorMessageEventHandler (ErrorHandler);
401 tds.TdsInfoMessage -= new TdsInternalInfoMessageEventHandler (MessageHandler);
404 ChangeState (ConnectionState.Closed);
407 public new SqlCommand CreateCommand ()
409 SqlCommand command = new SqlCommand ();
410 command.Connection = this;
414 private SqlInfoMessageEventArgs CreateSqlInfoMessageEvent (TdsInternalErrorCollection errors)
416 return new SqlInfoMessageEventArgs (errors);
419 private StateChangeEventArgs CreateStateChangeEvent (ConnectionState originalState, ConnectionState currentState)
421 return new StateChangeEventArgs (originalState, currentState);
424 protected override void Dispose (bool disposing)
431 if (State == ConnectionState.Open)
433 ConnectionString = "";
434 SetDefaultConnectionParameters (this.connStringParameters);
438 base.Dispose (disposing);
442 [MonoTODO ("Not sure what this means at present.")]
444 void EnlistDistributedTransaction (ITransaction transaction)
446 throw new NotImplementedException ();
449 object ICloneable.Clone ()
451 return new SqlConnection (ConnectionString);
454 IDbTransaction IDbConnection.BeginTransaction ()
456 return BeginTransaction ();
459 IDbTransaction IDbConnection.BeginTransaction (IsolationLevel iso)
461 return BeginTransaction (iso);
464 protected override DbTransaction BeginDbTransaction (IsolationLevel level)
466 return (DbTransaction)BeginTransaction (level);
469 protected override DbCommand CreateDbCommand ()
471 return CreateCommand ();
475 IDbCommand IDbConnection.CreateCommand ()
477 return CreateCommand ();
480 void IDisposable.Dispose ()
483 GC.SuppressFinalize (this);
492 string serverName = "";
493 if (state == ConnectionState.Open)
494 throw new InvalidOperationException ("The Connection is already Open (State=Open)");
496 if (connectionString == null || connectionString.Trim().Length == 0)
497 throw new InvalidOperationException ("Connection string has not been initialized.");
501 if(!ParseDataSource (dataSource, out port, out serverName))
502 throw new SqlException(20, 0, "SQL Server does not exist or access denied.", 17, "ConnectionOpen (Connect()).", dataSource, parms.ApplicationName, 0);
503 tds = new Tds70 (serverName, port, PacketSize, ConnectionTimeout);
506 if(!ParseDataSource (dataSource, out port, out serverName))
507 throw new SqlException(20, 0, "SQL Server does not exist or access denied.", 17, "ConnectionOpen (Connect()).", dataSource, parms.ApplicationName, 0);
509 TdsConnectionInfo info = new TdsConnectionInfo (serverName, port, packetSize, ConnectionTimeout, minPoolSize, maxPoolSize);
510 pool = sqlConnectionPools.GetConnectionPool (connectionString, info);
511 tds = pool.GetConnection ();
513 } catch (TdsTimeoutException e) {
514 throw SqlException.FromTdsInternalException ((TdsInternalException) e);
515 }catch (TdsInternalException e) {
516 throw SqlException.FromTdsInternalException (e);
519 tds.TdsErrorMessage += new TdsInternalErrorMessageEventHandler (ErrorHandler);
520 tds.TdsInfoMessage += new TdsInternalInfoMessageEventHandler (MessageHandler);
522 if (!tds.IsConnected) {
528 pool.ReleaseConnection (tds);
531 } else if (connectionReset) {
535 disposed = false; // reset this, so using () would call Close ().
536 ChangeState (ConnectionState.Open);
539 private bool ParseDataSource (string theDataSource, out int thePort, out string theServerName)
542 string theInstanceName = "";
544 if (theDataSource == null)
545 throw new ArgumentException("Format of initialization string does not conform to specifications");
547 thePort = 1433; // default TCP port for SQL Server
551 if ((idx = theDataSource.IndexOf (",")) > -1) {
552 theServerName = theDataSource.Substring (0, idx);
553 string p = theDataSource.Substring (idx + 1);
554 thePort = Int32.Parse (p);
556 else if ((idx = theDataSource.IndexOf ("\\")) > -1) {
557 theServerName = theDataSource.Substring (0, idx);
558 theInstanceName = theDataSource.Substring (idx + 1);
559 // do port discovery via UDP port 1434
560 port = DiscoverTcpPortViaSqlMonitor (theServerName, theInstanceName);
564 else if (theDataSource == "" || theDataSource == "(local)")
565 theServerName = "localhost";
567 theServerName = theDataSource;
572 private bool ConvertIntegratedSecurity (string value)
574 if (value.ToUpper() == "SSPI")
579 return ConvertToBoolean("integrated security", value);
582 private bool ConvertToBoolean(string key, string value)
584 string upperValue = value.ToUpper();
586 if (upperValue == "TRUE" ||upperValue == "YES")
590 else if (upperValue == "FALSE" || upperValue == "NO")
595 throw new ArgumentException(string.Format(CultureInfo.InvariantCulture,
596 "Invalid value \"{0}\" for key '{1}'.", value, key));
599 private int ConvertToInt32(string key, string value)
603 return int.Parse(value);
607 throw new ArgumentException(string.Format(CultureInfo.InvariantCulture,
608 "Invalid value \"{0}\" for key '{1}'.", value, key));
612 private int DiscoverTcpPortViaSqlMonitor(string ServerName, string InstanceName)
614 SqlMonitorSocket msock;
615 msock = new SqlMonitorSocket (ServerName, InstanceName);
616 int SqlServerPort = msock.DiscoverTcpPort ();
618 return SqlServerPort;
621 void SetConnectionString (string connectionString)
623 NameValueCollection parameters = new NameValueCollection ();
624 SetDefaultConnectionParameters (parameters);
626 if ((connectionString == null) || (connectionString.Trim().Length == 0)) {
627 this.connectionString = connectionString;
628 this.connStringParameters = parameters;
632 connectionString += ";";
634 bool inQuote = false;
635 bool inDQuote = false;
638 string name = String.Empty;
639 string value = String.Empty;
640 StringBuilder sb = new StringBuilder ();
642 for (int i = 0; i < connectionString.Length; i += 1) {
643 char c = connectionString [i];
645 if (i == connectionString.Length - 1)
648 peek = connectionString [i + 1];
654 else if (peek.Equals (c)) {
664 else if (peek.Equals (c)) {
669 inDQuote = !inDQuote;
672 if (inDQuote || inQuote)
675 if (name != String.Empty && name != null) {
676 value = sb.ToString ();
677 SetProperties (name.ToUpper ().Trim() , value);
678 parameters [name.ToUpper ().Trim ()] = value.Trim ();
680 else if (sb.Length != 0)
681 throw new ArgumentException ("Format of initialization string does not conform to specifications");
684 value = String.Empty;
685 sb = new StringBuilder ();
689 if (inDQuote || inQuote || !inName)
691 else if (peek.Equals (c)) {
697 name = sb.ToString ();
698 sb = new StringBuilder ();
703 if (inQuote || inDQuote)
705 else if (sb.Length > 0 && !peek.Equals (';'))
714 connectionString = connectionString.Substring (0 , connectionString.Length-1);
715 this.connectionString = connectionString;
716 this.connStringParameters = parameters;
719 void SetDefaultConnectionParameters (NameValueCollection parameters)
723 connectionTimeout= 15;
724 connectionReset = true;
730 parameters["APPLICATION NAME"] = "Mono SqlClient Data Provider";
731 parameters["CONNECT TIMEOUT"] = "15";
732 parameters["CONNECTION LIFETIME"] = "0";
733 parameters["CONNECTION RESET"] = "true";
734 parameters["ENLIST"] = "true";
735 parameters["INTEGRATED SECURITY"] = "false";
736 parameters["INITIAL CATALOG"] = "";
737 parameters["MAX POOL SIZE"] = "100";
738 parameters["MIN POOL SIZE"] = "0";
739 parameters["NETWORK LIBRARY"] = "dbmssocn";
740 parameters["PACKET SIZE"] = "8192";
741 parameters["PERSIST SECURITY INFO"] = "false";
742 parameters["POOLING"] = "true";
743 parameters["WORKSTATION ID"] = Dns.GetHostName();
746 parameters ["ASYNCHRONOUS PROCESSING"] = "false";
750 private void SetProperties (string name , string value)
756 case "APPLICATION NAME" :
757 parms.ApplicationName = value;
759 case "ATTACHDBFILENAME" :
760 case "EXTENDED PROPERTIES" :
761 case "INITIAL FILE NAME" :
762 parms.AttachDBFileName = value;
765 case "CONNECT TIMEOUT" :
766 case "CONNECTION TIMEOUT" :
767 int tmpTimeout = ConvertToInt32 ("connection timeout", value);
769 throw new ArgumentException ("Invalid CONNECTION TIMEOUT .. Must be an integer >=0 ");
771 connectionTimeout = tmpTimeout;
773 case "CONNECTION LIFETIME" :
775 case "CONNECTION RESET" :
776 connectionReset = ConvertToBoolean ("connection reset", value);
779 case "CURRENT LANGUAGE" :
780 parms.Language = value;
786 case "NETWORK ADDRESS" :
790 if (ConvertToBoolean("encrypt", value))
792 throw new NotImplementedException("SSL encryption for"
793 + " data sent between client and server is not"
798 if (!ConvertToBoolean("enlist", value))
800 throw new NotImplementedException("Disabling the automatic"
801 + " enlistment of connections in the thread's current"
802 + " transaction context is not implemented.");
805 case "INITIAL CATALOG" :
807 parms.Database = value;
809 case "INTEGRATED SECURITY" :
810 case "TRUSTED_CONNECTION" :
811 parms.DomainLogin = ConvertIntegratedSecurity(value);
813 case "MAX POOL SIZE" :
814 int tmpMaxPoolSize = ConvertToInt32 ("max pool size" , value);
815 if (tmpMaxPoolSize < 0)
816 throw new ArgumentException ("Invalid MAX POOL SIZE. Must be a intger >= 0");
818 maxPoolSize = tmpMaxPoolSize;
820 case "MIN POOL SIZE" :
821 int tmpMinPoolSize = ConvertToInt32 ("min pool size" , value);
822 if (tmpMinPoolSize < 0)
823 throw new ArgumentException ("Invalid MIN POOL SIZE. Must be a intger >= 0");
825 minPoolSize = tmpMinPoolSize;
828 case "MULTIPLEACTIVERESULTSETS":
830 case "ASYNCHRONOUS PROCESSING" :
832 async = ConvertToBoolean (name, value);
837 case "NETWORK LIBRARY" :
838 if (!value.ToUpper ().Equals ("DBMSSOCN"))
839 throw new ArgumentException ("Unsupported network library.");
842 int tmpPacketSize = ConvertToInt32 ("packet size", value);
843 if (tmpPacketSize < 512 || tmpPacketSize > 32767)
844 throw new ArgumentException ("Invalid PACKET SIZE. The integer must be between 512 and 32767");
846 packetSize = tmpPacketSize;
850 parms.Password = value;
852 case "PERSISTSECURITYINFO" :
853 case "PERSIST SECURITY INFO" :
854 // FIXME : not implemented
855 // throw new NotImplementedException ();
858 pooling = ConvertToBoolean("pooling", value);
866 case "WORKSTATION ID" :
867 parms.Hostname = value;
870 throw new ArgumentException("Keyword not supported :"+name);
874 static bool IsValidDatabaseName (string database)
876 if ( database == null || database.Trim() == String.Empty || database.Length > 128)
879 if (database[0] == '"' && database[database.Length] == '"')
880 database = database.Substring (1, database.Length - 2);
881 else if (Char.IsDigit (database[0]))
884 if (database[0] == '_')
887 foreach (char c in database.Substring (1, database.Length - 1))
888 if (!Char.IsLetterOrDigit (c) && c != '_' && c != '-')
893 private void OnSqlInfoMessage (SqlInfoMessageEventArgs value)
895 if (InfoMessage != null)
896 InfoMessage (this, value);
899 private new void OnStateChange (StateChangeEventArgs value)
901 if (StateChange != null)
902 StateChange (this, value);
905 private sealed class SqlMonitorSocket : UdpClient
907 // UDP port that the SQL Monitor listens
908 private static readonly int SqlMonitorUdpPort = 1434;
909 //private static readonly string SqlServerNotExist = "SQL Server does not exist or access denied";
911 private string server;
912 private string instance;
914 internal SqlMonitorSocket (string ServerName, string InstanceName)
915 : base (ServerName, SqlMonitorUdpPort)
918 instance = InstanceName;
921 internal int DiscoverTcpPort ()
923 int SqlServerTcpPort;
924 Client.Blocking = false;
925 // send command to UDP 1434 (SQL Monitor) to get
926 // the TCP port to connect to the MS SQL server
927 ASCIIEncoding enc = new ASCIIEncoding ();
928 Byte[] rawrq = new Byte [instance.Length + 1];
930 enc.GetBytes (instance, 0, instance.Length, rawrq, 1);
931 int bytes = Send (rawrq, rawrq.Length);
937 result = Client.Poll (100, SelectMode.SelectRead);
941 if (Client.Available <= 0)
944 IPEndPoint endpoint = new IPEndPoint (Dns.GetHostByName ("localhost").AddressList [0], 0);
947 rawrs = Receive (ref endpoint);
949 string rs = Encoding.ASCII.GetString (rawrs);
951 string[] rawtokens = rs.Split (';');
952 Hashtable data = new Hashtable ();
953 for (int i = 0; i < rawtokens.Length / 2 && i < 256; i++) {
954 data [rawtokens [i * 2]] = rawtokens [ i * 2 + 1];
956 if (!data.ContainsKey ("tcp"))
957 throw new NotImplementedException ("Only TCP/IP is supported.");
959 SqlServerTcpPort = int.Parse ((string) data ["tcp"]);
962 return SqlServerTcpPort;
970 public ColumnInfo (string name, Type type)
972 this.name = name; this.type = type;
976 static class ReservedWords
978 static readonly string [] reservedWords =
980 "ADD", "EXCEPT", "PERCENT", "ALL", "EXEC", "PLAN", "ALTER",
981 "EXECUTE", "PRECISION", "AND", "EXISTS", "PRIMARY", "ANY",
982 "EXIT", "PRINT", "AS", "FETCH", "PROC", "ASC", "FILE",
983 "PROCEDURE", "AUTHORIZATION", "FILLFACTOR", "PUBLIC",
984 "BACKUP", "FOR", "RAISERROR", "BEGIN", "FOREIGN", "READ",
985 "BETWEEN", "FREETEXT", "READTEXT", "BREAK", "FREETEXTTABLE",
986 "RECONFIGURE", "BROWSE", "FROM", "REFERENCES", "BULK",
987 "FULL", "REPLICATION", "BY", "FUNCTION", "RESTORE",
988 "CASCADE", "GOTO", "RESTRICT", "CASE", "GRANT", "RETURN",
989 "CHECK", "GROUP", "REVOKE", "CHECKPOINT", "HAVING", "RIGHT",
990 "CLOSE", "HOLDLOCK", "ROLLBACK", "CLUSTERED", "IDENTITY",
991 "ROWCOUNT", "COALESCE", "IDENTITY_INSERT", "ROWGUIDCOL",
992 "COLLATE", "IDENTITYCOL", "RULE", "COLUMN", "IF", "SAVE",
993 "COMMIT", "IN", "SCHEMA", "COMPUTE", "INDEX", "SELECT",
994 "CONSTRAINT", "INNER", "SESSION_USER", "CONTAINS", "INSERT",
995 "SET", "CONTAINSTABLE", "INTERSECT", "SETUSER", "CONTINUE",
996 "INTO", "SHUTDOWN", "CONVERT", "IS", "SOME", "CREATE",
997 "JOIN", "STATISTICS", "CROSS", "KEY", "SYSTEM_USER",
998 "CURRENT", "KILL", "TABLE", "CURRENT_DATE", "LEFT",
999 "TEXTSIZE", "CURRENT_TIME", "LIKE", "THEN",
1000 "CURRENT_TIMESTAMP", "LINENO", "TO", "CURRENT_USER", "LOAD",
1001 "TOP", "CURSOR", "NATIONAL", "TRAN", "DATABASE", "NOCHECK",
1002 "TRANSACTION", "DBCC", "NONCLUSTERED", "TRIGGER",
1003 "DEALLOCATE", "NOT", "TRUNCATE", "DECLARE", "NULL",
1004 "TSEQUAL", "DEFAULT", "NULLIF", "UNION", "DELETE", "OF",
1005 "UNIQUE", "DENY", "OFF", "UPDATE", "DESC", "OFFSETS",
1006 "UPDATETEXT", "DISK", "ON", "USE", "DISTINCT", "OPEN",
1007 "USER", "DISTRIBUTED", "OPENDATASOURCE", "VALUES", "DOUBLE",
1008 "OPENQUERY", "VARYING", "DROP", "OPENROWSET", "VIEW",
1009 "DUMMY", "OPENXML", "WAITFOR", "DUMP", "OPTION", "WHEN",
1010 "ELSE", "OR", "WHERE", "END", "ORDER", "WHILE", "ERRLVL",
1011 "OUTER", "WITH", "ESCAPE", "OVER", "WRITETEXT", "ABSOLUTE",
1012 "FOUND", "PRESERVE", "ACTION", "FREE", "PRIOR", "ADMIN",
1013 "GENERAL", "PRIVILEGES", "AFTER", "GET", "READS",
1014 "AGGREGATE", "GLOBAL", "REAL", "ALIAS", "GO", "RECURSIVE",
1015 "ALLOCATE", "GROUPING", "REF", "ARE", "HOST", "REFERENCING",
1016 "ARRAY", "HOUR", "RELATIVE", "ASSERTION", "IGNORE", "RESULT",
1017 "AT", "IMMEDIATE", "RETURNS", "BEFORE", "INDICATOR", "ROLE",
1018 "BINARY", "INITIALIZE", "ROLLUP", "BIT", "INITIALLY",
1019 "ROUTINE", "BLOB", "INOUT", "ROW", "BOOLEAN", "INPUT",
1020 "ROWS", "BOTH", "INT", "SAVEPOINT", "BREADTH", "INTEGER",
1021 "SCROLL", "CALL", "INTERVAL", "SCOPE", "CASCADED",
1022 "ISOLATION", "SEARCH", "CAST", "ITERATE", "SECOND",
1023 "CATALOG", "LANGUAGE", "SECTION", "CHAR", "LARGE",
1024 "SEQUENCE", "CHARACTER", "LAST", "SESSION", "CLASS",
1025 "LATERAL", "SETS", "CLOB", "LEADING", "SIZE", "COLLATION",
1026 "LESS", "SMALLINT", "COMPLETION", "LEVEL", "SPACE",
1027 "CONNECT", "LIMIT", "SPECIFIC", "CONNECTION", "LOCAL",
1028 "SPECIFICTYPE", "CONSTRAINTS", "LOCALTIME", "SQL",
1029 "CONSTRUCTOR", "LOCALTIMESTAMP", "SQLEXCEPTION",
1030 "CORRESPONDING", "LOCATOR", "SQLSTATE", "CUBE", "MAP",
1031 "SQLWARNING", "CURRENT_PATH", "MATCH", "START",
1032 "CURRENT_ROLE", "MINUTE", "STATE", "CYCLE", "MODIFIES",
1033 "STATEMENT", "DATA", "MODIFY", "STATIC", "DATE", "MODULE",
1034 "STRUCTURE", "DAY", "MONTH", "TEMPORARY", "DEC", "NAMES",
1035 "TERMINATE", "DECIMAL", "NATURAL", "THAN", "DEFERRABLE",
1036 "NCHAR", "TIME", "DEFERRED", "NCLOB", "TIMESTAMP", "DEPTH",
1037 "NEW", "TIMEZONE_HOUR", "DEREF", "NEXT", "TIMEZONE_MINUTE",
1038 "DESCRIBE", "NO", "TRAILING", "DESCRIPTOR", "NONE",
1039 "TRANSLATION", "DESTROY", "NUMERIC", "TREAT", "DESTRUCTOR",
1040 "OBJECT", "TRUE", "DETERMINISTIC", "OLD", "UNDER",
1041 "DICTIONARY", "ONLY", "UNKNOWN", "DIAGNOSTICS", "OPERATION",
1042 "UNNEST", "DISCONNECT", "ORDINALITY", "USAGE", "DOMAIN",
1043 "OUT", "USING", "DYNAMIC", "OUTPUT", "VALUE", "EACH",
1044 "PAD", "VARCHAR", "END-EXEC", "PARAMETER", "VARIABLE",
1045 "EQUALS", "PARAMETERS", "WHENEVER", "EVERY", "PARTIAL",
1046 "WITHOUT", "EXCEPTION", "PATH", "WORK", "EXTERNAL",
1047 "POSTFIX", "WRITE", "FALSE", "PREFIX", "YEAR", "FIRST",
1048 "PREORDER", "ZONE", "FLOAT", "PREPARE", "ADA", "AVG",
1049 "BIT_LENGTH", "CHAR_LENGTH", "CHARACTER_LENGTH", "COUNT",
1050 "EXTRACT", "FORTRAN", "INCLUDE", "INSENSITIVE", "LOWER",
1051 "MAX", "MIN", "OCTET_LENGTH", "OVERLAPS", "PASCAL",
1052 "POSITION", "SQLCA", "SQLCODE", "SQLERROR", "SUBSTRING",
1053 "SUM", "TRANSLATE", "TRIM", "UPPER"
1055 static DataTable instance;
1056 static public DataTable Instance {
1058 if (instance == null) {
1060 instance = new DataTable ("ReservedWords");
1061 instance.Columns.Add ("ReservedWord", typeof(string));
1062 foreach (string reservedWord in reservedWords)
1064 row = instance.NewRow();
1066 row["ReservedWord"] = reservedWord;
1067 instance.Rows.Add(row);
1075 static class MetaDataCollections
1077 static readonly ColumnInfo [] columns = {
1078 new ColumnInfo ("CollectionName", typeof (string)),
1079 new ColumnInfo ("NumberOfRestrictions", typeof (int)),
1080 new ColumnInfo ("NumberOfIdentifierParts", typeof (int))
1083 static readonly object [][] rows = {
1084 new object [] {"MetaDataCollections", 0, 0},
1085 new object [] {"DataSourceInformation", 0, 0},
1086 new object [] {"DataTypes", 0, 0},
1087 new object [] {"Restrictions", 0, 0},
1088 new object [] {"ReservedWords", 0, 0},
1089 new object [] {"Users", 1, 1},
1090 new object [] {"Databases", 1, 1},
1091 new object [] {"Tables", 4, 3},
1092 new object [] {"Columns", 4, 4},
1093 new object [] {"Views", 3, 3},
1094 new object [] {"ViewColumns", 4, 4},
1095 new object [] {"ProcedureParameters", 4, 1},
1096 new object [] {"Procedures", 4, 3},
1097 new object [] {"ForeignKeys", 4, 3},
1098 new object [] {"IndexColumns", 5, 4},
1099 new object [] {"Indexes", 4, 3},
1100 new object [] {"UserDefinedTypes", 2, 1}
1103 static DataTable instance;
1104 static public DataTable Instance {
1106 if (instance == null) {
1107 instance = new DataTable ("GetSchema");
1108 foreach (ColumnInfo c in columns)
1109 instance.Columns.Add (c.name, c.type);
1110 foreach (object [] row in rows)
1111 instance.LoadDataRow (row, true);
1118 static class DataTypes
1120 static readonly ColumnInfo [] columns = {
1121 new ColumnInfo ("TypeName", typeof(string)),
1122 new ColumnInfo ("ProviderDbType", typeof(int)),
1123 new ColumnInfo ("ColumnSize", typeof(long)),
1124 new ColumnInfo ("CreateFormat", typeof(string)),
1125 new ColumnInfo ("CreateParameters", typeof(string)),
1126 new ColumnInfo ("DataType", typeof(string)),
1127 new ColumnInfo ("IsAutoIncrementable", typeof(bool)),
1128 new ColumnInfo ("IsBestMatch", typeof(bool)),
1129 new ColumnInfo ("IsCaseSensitive", typeof(bool)),
1130 new ColumnInfo ("IsFixedLength", typeof(bool)),
1131 new ColumnInfo ("IsFixedPrecisionScale", typeof(bool)),
1132 new ColumnInfo ("IsLong", typeof(bool)),
1133 new ColumnInfo ("IsNullable", typeof(bool)),
1134 new ColumnInfo ("IsSearchable", typeof(bool)),
1135 new ColumnInfo ("IsSearchableWithLike", typeof(bool)),
1136 new ColumnInfo ("IsUnsigned", typeof(bool)),
1137 new ColumnInfo ("MaximumScale", typeof(short)),
1138 new ColumnInfo ("MinimumScale", typeof(short)),
1139 new ColumnInfo ("IsConcurrencyType", typeof(bool)),
1140 new ColumnInfo ("IsLiteralSupported", typeof(bool)),
1141 new ColumnInfo ("LiteralPrefix", typeof(string)),
1142 new ColumnInfo ("LiteralSuffix", typeof(string))
1145 static readonly object [][] rows = {
1146 new object [] {"smallint", 16, 5, "smallint", null, "System.Int16", true, true,
1147 false, true, true, false, true, true, false, false, null,
1148 null, false, null, null, null},
1149 new object [] {"int", 8, 10, "int", null, "System.Int32",
1150 true, true, false, true, true, false, true, true, false,
1151 false, null, null, false, null, null, null},
1152 new object [] {"real", 13, 7, "real", null,
1153 "System.Single", false, true, false, true, false, false,
1154 true, true, false, false, null, null, false, null, null, null},
1155 new object [] {"float", 6, 53, "float({0})",
1156 "number of bits used to store the mantissa", "System.Double",
1157 false, true, false, true, false, false, true, true,
1158 false, false, null, null, false, null, null, null},
1159 new object [] {"money", 9, 19, "money", null,
1160 "System.Decimal", false, false, false, true, true,
1161 false, true, true, false, false, null, null, false,
1163 new object [] {"smallmoney", 17, 10, "smallmoney", null,
1164 "System.Decimal", false, false, false, true, true, false,
1165 true, true, false, false, null, null, false, null, null, null},
1166 new object [] {"bit", 2, 1, "bit", null, "System.Boolean",
1167 false, false, false, true, false, false, true, true,
1168 false, null, null, null, false, null, null, null},
1169 new object [] {"tinyint", 20, 3, "tinyint", null,
1170 "System.SByte", true, true, false, true, true, false,
1171 true, true, false, true, null, null, false, null, null, null},
1172 new object [] {"bigint", 0, 19, "bigint", null,
1173 "System.Int64", true, true, false, true, true, false,
1174 true, true, false, false, null, null, false, null, null, null},
1175 new object [] {"timestamp", 19, 8, "timestamp", null,
1176 "System.Byte[]", false, false, false, true, false, false,
1177 false, true, false, null, null, null, true, null, "0x", null},
1178 new object [] {"binary", 1, 8000, "binary({0})", "length",
1179 "System.Byte[]", false, true, false, true, false, false,
1180 true, true, false, null, null, null, false, null, "0x", null},
1181 new object [] {"image", 7, 2147483647, "image", null,
1182 "System.Byte[]", false, true, false, false, false, true,
1183 true, false, false, null, null, null, false, null, "0x", null},
1184 new object [] {"text", 18, 2147483647, "text", null,
1185 "System.String", false, true, false, false, false, true,
1186 true, false, true, null, null, null, false, null, "'", "'"},
1187 new object [] {"ntext", 11, 1073741823, "ntext", null,
1188 "System.String", false, true, false, false, false, true,
1189 true, false, true, null, null, null, false, null, "N'", "'"},
1190 new object [] {"decimal", 5, 38, "decimal({0}, {1})",
1191 "precision,scale", "System.Decimal", true, true, false,
1192 true, false, false, true, true, false, false, 38, 0,
1193 false, null, null, null},
1194 new object [] {"numeric", 5, 38, "numeric({0}, {1})",
1195 "precision,scale", "System.Decimal", true, true, false,
1196 true, false, false, true, true, false, false, 38, 0,
1197 false, null, null, null},
1198 new object [] {"datetime", 4, 23, "datetime", null,
1199 "System.DateTime", false, true, false, true, false, false,
1200 true, true, true, null, null, null, false, null, "{ts '", "'}"},
1201 new object [] {"smalldatetime", 15, 16, "smalldatetime", null,
1202 "System.DateTime", false, true, false, true, false, false,
1203 true, true, true, null, null, null, false, null, "{ts '", "'}"},
1204 new object [] {"sql_variant", 23, null, "sql_variant",
1205 null, "System.Object", false, true, false, false, false,
1206 false, true, true, false, null, null, null, false, false,
1208 new object [] {"xml", 25, 2147483647, "xml", null,
1209 "System.String", false, false, false, false, false, true,
1210 true, false, false, null, null, null, false, false, null, null},
1211 new object [] {"varchar", 22, 2147483647, "varchar({0})",
1212 "max length", "System.String", false, true, false, false,
1213 false, false, true, true, true, null, null, null, false,
1215 new object [] {"char", 3, 2147483647, "char({0})", "length",
1216 "System.String", false, true, false, true, false, false,
1217 true, true, true, null, null, null, false, null, "'", "'"},
1218 new object [] {"nchar", 10, 1073741823, "nchar({0})", "length",
1219 "System.String", false, true, false, true, false, false,
1220 true, true, true, null, null, null, false, null, "N'", "'"},
1221 new object [] {"nvarchar", 12, 1073741823, "nvarchar({0})", "max length",
1222 "System.String", false, true, false, false, false, false, true, true,
1223 true, null, null, null, false, null, "N'", "'"},
1224 new object [] {"varbinary", 21, 1073741823, "varbinary({0})",
1225 "max length", "System.Byte[]", false, true, false, false,
1226 false, false, true, true, false, null, null, null, false,
1228 new object [] {"uniqueidentifier", 14, 16, "uniqueidentifier", null,
1229 "System.Guid", false, true, false, true, false, false, true,
1230 true, false, null, null, null, false, null, "'", "'"}
1233 static DataTable instance;
1234 static public DataTable Instance {
1236 if (instance == null) {
1237 instance = new DataTable ("DataTypes");
1238 foreach (ColumnInfo c in columns)
1239 instance.Columns.Add (c.name, c.type);
1240 foreach (object [] row in rows)
1241 instance.LoadDataRow (row, true);
1248 static class Restrictions
1250 static readonly ColumnInfo [] columns = {
1251 new ColumnInfo ("CollectionName", typeof (string)),
1252 new ColumnInfo ("RestrictionName", typeof(string)),
1253 new ColumnInfo ("ParameterName", typeof(string)),
1254 new ColumnInfo ("RestrictionDefault", typeof(string)),
1255 new ColumnInfo ("RestrictionNumber", typeof(int))
1258 static readonly object [][] rows = {
1259 new object [] {"Users", "User_Name", "@Name", "name", 1},
1260 new object [] {"Databases", "Name", "@Name", "Name", 1},
1262 new object [] {"Tables", "Catalog", "@Catalog", "TABLE_CATALOG", 1},
1263 new object [] {"Tables", "Owner", "@Owner", "TABLE_SCHEMA", 2},
1264 new object [] {"Tables", "Table", "@Name", "TABLE_NAME", 3},
1265 new object [] {"Tables", "TableType", "@TableType", "TABLE_TYPE", 4},
1267 new object [] {"Columns", "Catalog", "@Catalog", "TABLE_CATALOG", 1},
1268 new object [] {"Columns", "Owner", "@Owner", "TABLE_SCHEMA", 2},
1269 new object [] {"Columns", "Table", "@Table", "TABLE_NAME", 3},
1270 new object [] {"Columns", "Column", "@Column", "COLUMN_NAME", 4},
1272 new object [] {"Views", "Catalog", "@Catalog", "TABLE_CATALOG", 1},
1273 new object [] {"Views", "Owner", "@Owner", "TABLE_SCHEMA", 2},
1274 new object [] {"Views", "Table", "@Table", "TABLE_NAME", 3},
1276 new object [] {"ViewColumns", "Catalog", "@Catalog", "VIEW_CATALOG", 1},
1277 new object [] {"ViewColumns", "Owner", "@Owner", "VIEW_SCHEMA", 2},
1278 new object [] {"ViewColumns", "Table", "@Table", "VIEW_NAME", 3},
1279 new object [] {"ViewColumns", "Column", "@Column", "COLUMN_NAME", 4},
1281 new object [] {"ProcedureParameters", "Catalog", "@Catalog", "SPECIFIC_CATALOG", 1},
1282 new object [] {"ProcedureParameters", "Owner", "@Owner", "SPECIFIC_SCHEMA", 2},
1283 new object [] {"ProcedureParameters", "Name", "@Name", "SPECIFIC_NAME", 3},
1284 new object [] {"ProcedureParameters", "Parameter", "@Parameter", "PARAMETER_NAME", 4},
1286 new object [] {"Procedures", "Catalog", "@Catalog", "SPECIFIC_CATALOG", 1},
1287 new object [] {"Procedures", "Owner", "@Owner", "SPECIFIC_SCHEMA", 2},
1288 new object [] {"Procedures", "Name", "@Name", "SPECIFIC_NAME", 3},
1289 new object [] {"Procedures", "Type", "@Type", "ROUTINE_TYPE", 4},
1291 new object [] {"IndexColumns", "Catalog", "@Catalog", "db_name(}", 1},
1292 new object [] {"IndexColumns", "Owner", "@Owner", "user_name(}", 2},
1293 new object [] {"IndexColumns", "Table", "@Table", "o.name", 3},
1294 new object [] {"IndexColumns", "ConstraintName", "@ConstraintName", "x.name", 4},
1295 new object [] {"IndexColumns", "Column", "@Column", "c.name", 5},
1297 new object [] {"Indexes", "Catalog", "@Catalog", "db_name(}", 1},
1298 new object [] {"Indexes", "Owner", "@Owner", "user_name(}", 2},
1299 new object [] {"Indexes", "Table", "@Table", "o.name", 3},
1300 new object [] {"Indexes", "Name", "@Name", "x.name", 4},
1302 new object [] {"UserDefinedTypes", "assembly_name", "@AssemblyName", "assemblies.name", 1},
1303 new object [] {"UserDefinedTypes", "udt_name", "@UDTName", "types.assembly_class", 2},
1305 new object [] {"ForeignKeys", "Catalog", "@Catalog", "CONSTRAINT_CATALOG", 1},
1306 new object [] {"ForeignKeys", "Owner", "@Owner", "CONSTRAINT_SCHEMA", 2},
1307 new object [] {"ForeignKeys", "Table", "@Table", "TABLE_NAME", 3},
1308 new object [] {"ForeignKeys", "Name", "@Name", "CONSTRAINT_NAME", 4}
1311 static DataTable instance;
1312 static public DataTable Instance {
1314 if (instance == null) {
1315 instance = new DataTable ("Restrictions");
1316 foreach (ColumnInfo c in columns)
1317 instance.Columns.Add (c.name, c.type);
1318 foreach (object [] row in rows)
1319 instance.LoadDataRow (row, true);
1326 public override DataTable GetSchema ()
1328 return MetaDataCollections.Instance;
1331 public override DataTable GetSchema (String collectionName)
1333 return GetSchema (collectionName, null);
1336 public override DataTable GetSchema (String collectionName, string [] restrictionValues)
1338 if (collectionName == null)
1339 //LAMESPEC: In MS.NET, if collectionName is null, it throws ArgumentException.
1340 throw new ArgumentException ();
1342 String cName = null;
1343 DataTable schemaTable = MetaDataCollections.Instance;
1344 int length = restrictionValues == null ? 0 : restrictionValues.Length;
1346 foreach (DataRow row in schemaTable.Rows) {
1347 if (String.Compare ((string) row["CollectionName"], collectionName, true) == 0) {
1348 if (length > (int) row["NumberOfRestrictions"]) {
1349 throw new ArgumentException ("More restrictions were provided " +
1350 "than the requested schema ('" +
1351 row["CollectionName"].ToString () + "') supports");
1353 cName = row["CollectionName"].ToString();
1357 throw new ArgumentException ("The requested collection ('" + collectionName + "') is not defined.");
1359 SqlCommand command = null;
1360 DataTable dataTable = new DataTable ();
1361 SqlDataAdapter dataAdapter = new SqlDataAdapter ();
1366 command = new SqlCommand ("select name as database_name, dbid, crdate as create_date " +
1367 "from master.sys.sysdatabases where (name = @Name or (@Name " +
1369 command.Parameters.Add ("@Name", SqlDbType.NVarChar, 4000);
1372 command = new SqlCommand ("select CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME, " +
1373 "TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, CONSTRAINT_TYPE, " +
1374 "IS_DEFERRABLE, INITIALLY_DEFERRED from " +
1375 "INFORMATION_SCHEMA.TABLE_CONSTRAINTS where (CONSTRAINT_CATALOG" +
1376 " = @Catalog or (@Catalog is null)) and (CONSTRAINT_SCHEMA = " +
1377 "@Owner or (@Owner is null)) and (TABLE_NAME = @Table or (" +
1378 "@Table is null)) and (CONSTRAINT_NAME = @Name or (@Name is null))" +
1379 " and CONSTRAINT_TYPE = 'FOREIGN KEY' order by CONSTRAINT_CATALOG," +
1380 " CONSTRAINT_SCHEMA, CONSTRAINT_NAME", this);
1381 command.Parameters.Add ("@Catalog", SqlDbType.NVarChar, 4000);
1382 command.Parameters.Add ("@Owner", SqlDbType.NVarChar, 4000);
1383 command.Parameters.Add ("@Table", SqlDbType.NVarChar, 4000);
1384 command.Parameters.Add ("@Name", SqlDbType.NVarChar, 4000);
1387 command = new SqlCommand ("select distinct db_name() as constraint_catalog, " +
1388 "constraint_schema = user_name (o.uid), " +
1389 "constraint_name = x.name, table_catalog = db_name (), " +
1390 "table_schema = user_name (o.uid), table_name = o.name, " +
1391 "index_name = x.name from sysobjects o, sysindexes x, " +
1392 "sysindexkeys xk where o.type in ('U') and x.id = o.id and " +
1393 "o.id = xk.id and x.indid = xk.indid and xk.keyno = x.keycnt " +
1394 "and (db_name() = @Catalog or (@Catalog is null)) and " +
1395 "(user_name() = @Owner or (@Owner is null)) and (o.name = " +
1396 "@Table or (@Table is null)) and (x.name = @Name or (@Name is null))" +
1397 "order by table_name, index_name", this);
1398 command.Parameters.Add ("@Catalog", SqlDbType.NVarChar, 4000);
1399 command.Parameters.Add ("@Owner", SqlDbType.NVarChar, 4000);
1400 command.Parameters.Add ("@Table", SqlDbType.NVarChar, 4000);
1401 command.Parameters.Add ("@Name", SqlDbType.NVarChar, 4000);
1403 case "IndexColumns":
1404 command = new SqlCommand ("select distinct db_name() as constraint_catalog, " +
1405 "constraint_schema = user_name (o.uid), constraint_name = x.name, " +
1406 "table_catalog = db_name (), table_schema = user_name (o.uid), " +
1407 "table_name = o.name, column_name = c.name, " +
1408 "ordinal_position = convert (int, xk.keyno), keyType = c.xtype, " +
1409 "index_name = x.name from sysobjects o, sysindexes x, syscolumns c, " +
1410 "sysindexkeys xk where o.type in ('U') and x.id = o.id and o.id = c.id " +
1411 "and o.id = xk.id and x.indid = xk.indid and c.colid = xk.colid " +
1412 "and xk.keyno <= x.keycnt and permissions (o.id, c.name) <> 0 " +
1413 "and (db_name() = @Catalog or (@Catalog is null)) and (user_name() " +
1414 "= @Owner or (@Owner is null)) and (o.name = @Table or (@Table is" +
1415 " null)) and (x.name = @ConstraintName or (@ConstraintName is null)) " +
1416 "and (c.name = @Column or (@Column is null)) order by table_name, " +
1417 "index_name", this);
1418 command.Parameters.Add ("@Catalog", SqlDbType.NVarChar, 8);
1419 command.Parameters.Add ("@Owner", SqlDbType.NVarChar, 4000);
1420 command.Parameters.Add ("@Table", SqlDbType.NVarChar, 13);
1421 command.Parameters.Add ("@ConstraintName", SqlDbType.NVarChar, 4000);
1422 command.Parameters.Add ("@Column", SqlDbType.NVarChar, 4000);
1425 command = new SqlCommand ("select SPECIFIC_CATALOG, SPECIFIC_SCHEMA, SPECIFIC_NAME, " +
1426 "ROUTINE_CATALOG, ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE, " +
1427 "CREATED, LAST_ALTERED from INFORMATION_SCHEMA.ROUTINES where " +
1428 "(SPECIFIC_CATALOG = @Catalog or (@Catalog is null)) and " +
1429 "(SPECIFIC_SCHEMA = @Owner or (@Owner is null)) and (SPECIFIC_NAME" +
1430 " = @Name or (@Name is null)) and (ROUTINE_TYPE = @Type or (@Type " +
1431 "is null)) order by SPECIFIC_CATALOG, SPECIFIC_SCHEMA, SPECIFIC_NAME", this);
1432 command.Parameters.Add ("@Catalog", SqlDbType.NVarChar, 4000);
1433 command.Parameters.Add ("@Owner", SqlDbType.NVarChar, 4000);
1434 command.Parameters.Add ("@Name", SqlDbType.NVarChar, 4000);
1435 command.Parameters.Add ("@Type", SqlDbType.NVarChar, 4000);
1437 case "ProcedureParameters":
1438 command = new SqlCommand ("select SPECIFIC_CATALOG, SPECIFIC_SCHEMA, SPECIFIC_NAME, " +
1439 "ORDINAL_POSITION, PARAMETER_MODE, IS_RESULT, AS_LOCATOR, " +
1440 "PARAMETER_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, " +
1441 "CHARACTER_OCTET_LENGTH, COLLATION_CATALOG, COLLATION_SCHEMA, " +
1442 "COLLATION_NAME, CHARACTER_SET_CATALOG, CHARACTER_SET_SCHEMA, " +
1443 "CHARACTER_SET_NAME, NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX, " +
1444 "NUMERIC_SCALE, DATETIME_PRECISION, INTERVAL_TYPE, " +
1445 "INTERVAL_PRECISION from INFORMATION_SCHEMA.PARAMETERS where " +
1446 "(SPECIFIC_CATALOG = @Catalog or (@Catalog is null)) and " +
1447 "(SPECIFIC_SCHEMA = @Owner or (@Owner is null)) and (SPECIFIC_NAME = " +
1448 "@Name or (@Name is null)) and (PARAMETER_NAME = @Parameter or (" +
1449 "@Parameter is null)) order by SPECIFIC_CATALOG, SPECIFIC_SCHEMA," +
1450 " SPECIFIC_NAME, PARAMETER_NAME", this);
1451 command.Parameters.Add ("@Catalog", SqlDbType.NVarChar, 4000);
1452 command.Parameters.Add ("@Owner", SqlDbType.NVarChar, 4000);
1453 command.Parameters.Add ("@Name", SqlDbType.NVarChar, 4000);
1454 command.Parameters.Add ("@Parameter", SqlDbType.NVarChar, 4000);
1457 command = new SqlCommand ("select TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE " +
1458 "from INFORMATION_SCHEMA.TABLES where" +
1459 " (TABLE_CATALOG = @catalog or (@catalog is null)) and " +
1460 "(TABLE_SCHEMA = @owner or (@owner is null))and " +
1461 "(TABLE_NAME = @name or (@name is null)) and " +
1462 "(TABLE_TYPE = @table_type or (@table_type is null))", this);
1463 command.Parameters.Add ("@catalog", SqlDbType.NVarChar, 8);
1464 command.Parameters.Add ("@owner", SqlDbType.NVarChar, 3);
1465 command.Parameters.Add ("@name", SqlDbType.NVarChar, 11);
1466 command.Parameters.Add ("@table_type", SqlDbType.NVarChar, 10);
1469 command = new SqlCommand ("select TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, " +
1470 "ORDINAL_POSITION, COLUMN_DEFAULT, IS_NULLABLE, DATA_TYPE, " +
1471 "CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH, " +
1472 "NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX, NUMERIC_SCALE, " +
1473 "DATETIME_PRECISION, CHARACTER_SET_CATALOG, CHARACTER_SET_SCHEMA, " +
1474 "CHARACTER_SET_NAME, COLLATION_CATALOG from INFORMATION_SCHEMA.COLUMNS" +
1475 " where (TABLE_CATALOG = @Catalog or (@Catalog is null)) and (" +
1476 "TABLE_SCHEMA = @Owner or (@Owner is null)) and (TABLE_NAME = @table" +
1477 " or (@Table is null)) and (COLUMN_NAME = @column or (@Column is null" +
1478 ")) order by TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME", this);
1479 command.Parameters.Add ("@Catalog", SqlDbType.NVarChar, 4000);
1480 command.Parameters.Add ("@Owner", SqlDbType.NVarChar, 4000);
1481 command.Parameters.Add ("@Table", SqlDbType.NVarChar, 4000);
1482 command.Parameters.Add ("@Column", SqlDbType.NVarChar, 4000);
1485 command = new SqlCommand ("select uid, name as user_name, createdate, updatedate from sysusers" +
1486 " where (name = @Name or (@Name is null))", this);
1487 command.Parameters.Add ("@Name", SqlDbType.NVarChar, 4000);
1490 command = new SqlCommand ("select TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, CHECK_OPTION, " +
1491 "IS_UPDATABLE from INFORMATION_SCHEMA.VIEWS where (TABLE_CATALOG" +
1492 " = @Catalog or (@Catalog is null)) TABLE_SCHEMA = @Owner or " +
1493 "(@Owner is null)) and (TABLE_NAME = @table or (@Table is null))" +
1494 " order by TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME", this);
1495 command.Parameters.Add ("@Catalog", SqlDbType.NVarChar, 4000);
1496 command.Parameters.Add ("@Owner", SqlDbType.NVarChar, 4000);
1497 command.Parameters.Add ("@Table", SqlDbType.NVarChar, 4000);
1500 command = new SqlCommand ("select VIEW_CATALOG, VIEW_SCHEMA, VIEW_NAME, TABLE_CATALOG, " +
1501 "TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME from " +
1502 "INFORMATION_SCHEMA.VIEW_COLUMN_USAGE where (VIEW_CATALOG = " +
1503 "@Catalog (@Catalog is null)) and (VIEW_SCHEMA = @Owner (@Owner" +
1504 " is null)) and (VIEW_NAME = @Table or (@Table is null)) and " +
1505 "(COLUMN_NAME = @Column or (@Column is null)) order by " +
1506 "VIEW_CATALOG, VIEW_SCHEMA, VIEW_NAME", this);
1507 command.Parameters.Add ("@Catalog", SqlDbType.NVarChar, 4000);
1508 command.Parameters.Add ("@Owner", SqlDbType.NVarChar, 4000);
1509 command.Parameters.Add ("@Table", SqlDbType.NVarChar, 4000);
1510 command.Parameters.Add ("@Column", SqlDbType.NVarChar, 4000);
1512 case "UserDefinedTypes":
1513 command = new SqlCommand ("select assemblies.name as assembly_name, types.assembly_class " +
1514 "as udt_name, ASSEMBLYPROPERTY(assemblies.name, 'VersionMajor') " +
1515 "as version_major, ASSEMBLYPROPERTY(assemblies.name, 'VersionMinor') " +
1516 "as version_minor, ASSEMBLYPROPERTY(assemblies.name, 'VersionBuild') " +
1517 "as version_build, ASSEMBLYPROPERTY(assemblies.name, 'VersionRevision') " +
1518 "as version_revision, ASSEMBLYPROPERTY(assemblies.name, 'CultureInfo') " +
1519 "as culture_info, ASSEMBLYPROPERTY(assemblies.name, 'PublicKey') " +
1520 "as public_key, is_fixed_length, max_length, Create_Date, " +
1521 "Permission_set_desc from sys.assemblies as assemblies join " +
1522 "sys.assembly_types as types on assemblies.assembly_id = types.assembly_id" +
1523 " where (assemblies.name = @AssemblyName or (@AssemblyName is null)) and " +
1524 "(types.assembly_class = @UDTName or (@UDTName is null))",
1526 command.Parameters.Add ("@AssemblyName", SqlDbType.NVarChar, 4000);
1527 command.Parameters.Add ("@UDTName", SqlDbType.NVarChar, 4000);
1529 case "MetaDataCollections":
1530 return MetaDataCollections.Instance;
1531 case "DataSourceInformation":
1532 throw new NotImplementedException ();
1534 return DataTypes.Instance;
1535 case "ReservedWords":
1536 return ReservedWords.Instance;
1537 case "Restrictions":
1538 return Restrictions.Instance;
1540 for (int i = 0; i < length; i++) {
1541 command.Parameters[i].Value = restrictionValues[i];
1543 dataAdapter.SelectCommand = command;
1544 dataAdapter.Fill (dataTable);
1549 #endregion // Methods
1552 #region Fields Net 2
1556 #endregion // Fields Net 2
1558 #region Properties Net 2
1561 [DataSysDescription ("Enable Asynchronous processing, 'Asynchrouse Processing=true/false' in the ConnectionString.")]
1563 [DesignerSerializationVisibility (DesignerSerializationVisibility.Hidden)]
1564 internal bool AsyncProcessing {
1565 get { return async; }
1568 #endregion // Properties Net 2