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;
49 using System.EnterpriseServices;
51 using System.Globalization;
53 using System.Net.Sockets;
56 using System.Collections.Generic;
57 using System.Security;
59 namespace System.Data.SqlClient
61 [DefaultEvent ("InfoMessage")]
62 public sealed class SqlConnection : DbConnection, IDbConnection, ICloneable
68 // The set of SQL connection pools
69 static TdsConnectionPoolManager sqlConnectionPools = new TdsConnectionPoolManager (TdsVersion.tds80);
70 const int DEFAULT_PACKETSIZE = 8000;
71 const int MAX_PACKETSIZE = 32768;
72 const int MIN_PACKETSIZE = 512;
73 const int DEFAULT_CONNECTIONTIMEOUT = 15;
74 const int DEFAULT_CONNECTIONLIFETIME = 0;
75 const int DEFAULT_MAXPOOLSIZE = 100;
76 const int MIN_MAXPOOLSIZE = 1;
77 const int DEFAULT_MINPOOLSIZE = 0;
78 const int DEFAULT_PORT = 1433;
80 // The current connection pool
81 TdsConnectionPool pool;
83 // The connection string that identifies this connection
84 string connectionString;
86 // The connection credentials
87 SqlCredential credentials;
89 // The transaction object for the current transaction
90 SqlTransaction transaction;
92 // Connection parameters
94 TdsConnectionParameters parms;
96 int connectionLifeTime;
99 int connectionTimeout;
104 bool fireInfoMessageEventOnUserErrors;
105 bool statisticsEnabled;
108 ConnectionState state = ConnectionState.Closed;
110 SqlDataReader dataReader;
120 public SqlConnection () : this (null)
124 public SqlConnection (string connectionString)
126 ConnectionString = connectionString;
129 public SqlConnection (string connectionString, SqlCredential cred)
131 ConnectionString = connectionString;
135 #endregion // Constructors
140 [EditorAttribute ("Microsoft.VSDesigner.Data.SQL.Design.SqlConnectionStringEditor, "+ Consts.AssemblyMicrosoft_VSDesigner, "System.Drawing.Design.UITypeEditor, "+ Consts.AssemblySystem_Drawing )]
141 [RecommendedAsConfigurable (true)]
142 [RefreshProperties (RefreshProperties.All)]
143 public override string ConnectionString {
145 if (connectionString == null)
147 return connectionString;
149 [MonoTODO("persist security info, encrypt, enlist keyword not implemented")]
151 if (state == ConnectionState.Open)
152 throw new InvalidOperationException ("Not Allowed to change ConnectionString property while Connection state is OPEN");
153 SetConnectionString (value);
157 public SqlCredential Credentials {
166 [DesignerSerializationVisibility (DesignerSerializationVisibility.Hidden)]
169 int ConnectionTimeout {
170 get { return connectionTimeout; }
173 [DesignerSerializationVisibility (DesignerSerializationVisibility.Hidden)]
178 if (State == ConnectionState.Open)
180 return parms.Database ;
184 internal SqlDataReader DataReader {
185 get { return dataReader; }
186 set { dataReader = value; }
190 [DesignerSerializationVisibility (DesignerSerializationVisibility.Hidden)]
194 get { return dataSource; }
197 [DesignerSerializationVisibility (DesignerSerializationVisibility.Hidden)]
198 public int PacketSize {
200 if (State == ConnectionState.Open)
201 return ((Tds) tds).PacketSize;
207 [DesignerSerializationVisibility (DesignerSerializationVisibility.Hidden)]
210 string ServerVersion {
212 if (state == ConnectionState.Closed)
213 throw ExceptionHelper.ConnectionClosed ();
215 return tds.ServerVersion;
220 [DesignerSerializationVisibility (DesignerSerializationVisibility.Hidden)]
223 ConnectionState State {
224 get { return state; }
231 internal SqlTransaction Transaction {
232 get { return transaction; }
233 set { transaction = value; }
236 [DesignerSerializationVisibility (DesignerSerializationVisibility.Hidden)]
237 public string WorkstationId {
238 get { return parms.Hostname; }
241 internal XmlReader XmlReader {
242 get { return xmlReader; }
243 set { xmlReader = value; }
246 public bool FireInfoMessageEventOnUserErrors {
247 get { return fireInfoMessageEventOnUserErrors; }
248 set { fireInfoMessageEventOnUserErrors = value; }
251 [DefaultValue (false)]
252 public bool StatisticsEnabled {
253 get { return statisticsEnabled; }
254 set { statisticsEnabled = value; }
257 protected internal override DbProviderFactory DbProviderFactory {
259 return SqlClientFactory.Instance;
263 #endregion // Properties
267 public event SqlInfoMessageEventHandler InfoMessage;
274 private void ErrorHandler (object sender, TdsInternalErrorMessageEventArgs e)
277 if (!tds.IsConnected)
285 throw new SqlException (e.Class, e.LineNumber, e.Message, e.Number, e.Procedure, e.Server, "Mono SqlClient Data Provider", e.State);
288 private void MessageHandler (object sender, TdsInternalInfoMessageEventArgs e)
290 OnSqlInfoMessage (CreateSqlInfoMessageEvent (e.Errors));
293 #endregion // Delegates
297 public new SqlTransaction BeginTransaction ()
299 return BeginTransaction (IsolationLevel.ReadCommitted, String.Empty);
302 public new SqlTransaction BeginTransaction (IsolationLevel iso)
304 return BeginTransaction (iso, String.Empty);
307 public SqlTransaction BeginTransaction (string transactionName)
309 return BeginTransaction (IsolationLevel.ReadCommitted, transactionName);
312 public SqlTransaction BeginTransaction (IsolationLevel iso, string transactionName)
314 if (state == ConnectionState.Closed)
315 throw ExceptionHelper.ConnectionClosed ();
316 if (transaction != null)
317 throw new InvalidOperationException ("SqlConnection does not support parallel transactions.");
319 string isolevel = String.Empty;
321 case IsolationLevel.ReadUncommitted:
322 isolevel = "READ UNCOMMITTED";
324 case IsolationLevel.RepeatableRead:
325 isolevel = "REPEATABLE READ";
327 case IsolationLevel.Serializable:
328 isolevel = "SERIALIZABLE";
330 case IsolationLevel.ReadCommitted:
331 isolevel = "READ COMMITTED";
333 case IsolationLevel.Snapshot:
334 isolevel = "SNAPSHOT";
336 case IsolationLevel.Unspecified:
337 iso = IsolationLevel.ReadCommitted;
338 isolevel = "READ COMMITTED";
340 case IsolationLevel.Chaos:
341 throw new ArgumentOutOfRangeException ("IsolationLevel",
342 string.Format (CultureInfo.CurrentCulture,
343 "The IsolationLevel enumeration " +
344 "value, {0}, is not supported by " +
345 "the .Net Framework SqlClient " +
346 "Data Provider.", (int) iso));
348 throw new ArgumentOutOfRangeException ("IsolationLevel",
349 string.Format (CultureInfo.CurrentCulture,
350 "The IsolationLevel enumeration value, {0}, is invalid.",
354 tds.Execute (String.Format ("SET TRANSACTION ISOLATION LEVEL {0};BEGIN TRANSACTION {1}", isolevel, transactionName));
356 transaction = new SqlTransaction (this, iso);
362 void ChangeDatabase (string database)
364 if (!IsValidDatabaseName (database))
365 throw new ArgumentException (String.Format ("The database name {0} is not valid.", database));
366 if (state != ConnectionState.Open)
367 throw new InvalidOperationException ("The connection is not open.");
368 tds.Execute (String.Format ("use [{0}]", database));
371 private void ChangeState (ConnectionState currentState)
373 if (currentState == state)
376 ConnectionState originalState = state;
377 state = currentState;
378 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 ();
394 if (tds != null && tds.IsConnected) {
395 if (pooling && tds.Pooling) {
397 pool.ReleaseConnection (tds);
406 tds.TdsErrorMessage -= new TdsInternalErrorMessageEventHandler (ErrorHandler);
407 tds.TdsInfoMessage -= new TdsInternalInfoMessageEventHandler (MessageHandler);
410 ChangeState (ConnectionState.Closed);
413 public new SqlCommand CreateCommand ()
415 SqlCommand command = new SqlCommand ();
416 command.Connection = this;
420 private SqlInfoMessageEventArgs CreateSqlInfoMessageEvent (TdsInternalErrorCollection errors)
422 return new SqlInfoMessageEventArgs (errors);
425 private StateChangeEventArgs CreateStateChangeEvent (ConnectionState originalState, ConnectionState currentState)
427 return new StateChangeEventArgs (originalState, currentState);
430 protected override void Dispose (bool disposing)
433 if (disposing && !disposed) {
434 if (State == ConnectionState.Open)
436 ConnectionString = null;
440 base.Dispose (disposing);
445 [MonoTODO ("Not sure what this means at present.")]
446 public void EnlistDistributedTransaction (ITransaction transaction)
448 throw new NotImplementedException ();
452 object ICloneable.Clone ()
454 return new SqlConnection (ConnectionString);
457 protected override DbTransaction BeginDbTransaction (IsolationLevel isolationLevel)
459 return BeginTransaction (isolationLevel);
462 protected override DbCommand CreateDbCommand ()
464 return CreateCommand ();
471 string serverName = string.Empty;
472 if (state == ConnectionState.Open)
473 throw new InvalidOperationException ("The Connection is already Open (State=Open)");
475 if (connectionString == null || connectionString.Trim().Length == 0)
476 throw new InvalidOperationException ("Connection string has not been initialized.");
480 if(!ParseDataSource (dataSource, out port, out serverName))
481 throw new SqlException(20, 0, "SQL Server does not exist or access denied.", 17, "ConnectionOpen (Connect()).", dataSource, parms.ApplicationName, 0);
482 tds = new Tds80 (serverName, port, PacketSize, ConnectionTimeout, 0);
486 if(!ParseDataSource (dataSource, out port, out serverName))
487 throw new SqlException(20, 0, "SQL Server does not exist or access denied.", 17, "ConnectionOpen (Connect()).", dataSource, parms.ApplicationName, 0);
489 TdsConnectionInfo info = new TdsConnectionInfo (serverName, port, packetSize, ConnectionTimeout, minPoolSize, maxPoolSize, connectionLifeTime);
490 pool = sqlConnectionPools.GetConnectionPool (connectionString, info);
491 tds = pool.GetConnection ();
493 } catch (TdsTimeoutException e) {
494 throw SqlException.FromTdsInternalException ((TdsInternalException) e);
495 } catch (TdsInternalException e) {
496 throw SqlException.FromTdsInternalException (e);
499 tds.TdsErrorMessage += new TdsInternalErrorMessageEventHandler (ErrorHandler);
500 tds.TdsInfoMessage += new TdsInternalInfoMessageEventHandler (MessageHandler);
502 if (!tds.IsConnected) {
504 if (Credentials != null) {
505 if (parms.User != String.Empty)
506 throw new ArgumentException("UserID already specified");
507 if (parms.PasswordSet)
508 throw new ArgumentException("Password already specified");
509 if (parms.DomainLogin != false)
510 throw new ArgumentException("Cannot use credentials with DomainLogin");
511 parms.User = Credentials.UserId;
512 parms.Password = Credentials.Password;
517 pool.ReleaseConnection (tds);
522 disposed = false; // reset this, so using () would call Close ().
523 ChangeState (ConnectionState.Open);
526 private bool ParseDataSource (string theDataSource, out int thePort, out string theServerName)
528 theServerName = string.Empty;
529 string theInstanceName = string.Empty;
531 if (theDataSource == null)
532 throw new ArgumentException("Format of initialization string does not conform to specifications");
534 thePort = DEFAULT_PORT; // default TCP port for SQL Server
538 if ((idx = theDataSource.IndexOf (',')) > -1) {
539 theServerName = theDataSource.Substring (0, idx);
540 string p = theDataSource.Substring (idx + 1);
541 thePort = Int32.Parse (p);
542 } else if ((idx = theDataSource.IndexOf ('\\')) > -1) {
543 theServerName = theDataSource.Substring (0, idx);
544 theInstanceName = theDataSource.Substring (idx + 1);
546 // do port discovery via UDP port 1434
547 port = DiscoverTcpPortViaSqlMonitor (theServerName, theInstanceName);
551 theServerName = theDataSource;
553 if (theServerName.Length == 0 || theServerName == "(local)" || theServerName == ".")
554 theServerName = "localhost";
556 if ((idx = theServerName.IndexOf ("tcp:")) > -1)
557 theServerName = theServerName.Substring (idx + 4);
562 private bool ConvertIntegratedSecurity (string value)
564 if (value.ToUpper() == "SSPI")
567 return ConvertToBoolean ("integrated security", value, false);
570 private bool ConvertToBoolean (string key, string value, bool defaultValue)
572 if (value.Length == 0)
575 string upperValue = value.ToUpper ();
577 if (upperValue == "TRUE" || upperValue == "YES")
579 else if (upperValue == "FALSE" || upperValue == "NO")
582 throw new ArgumentException (string.Format (CultureInfo.InvariantCulture,
583 "Invalid value \"{0}\" for key '{1}'.", value, key));
586 private int ConvertToInt32 (string key, string value, int defaultValue)
588 if (value.Length == 0)
592 return int.Parse (value);
593 } catch (Exception ex) {
594 throw new ArgumentException (string.Format (CultureInfo.InvariantCulture,
595 "Invalid value \"{0}\" for key '{1}'.", value, key), ex);
599 private int DiscoverTcpPortViaSqlMonitor (string ServerName, string InstanceName)
601 SqlMonitorSocket msock;
602 msock = new SqlMonitorSocket (ServerName, InstanceName);
603 int SqlServerPort = msock.DiscoverTcpPort (ConnectionTimeout);
605 return SqlServerPort;
608 void SetConnectionString (string connectionString)
610 SetDefaultConnectionParameters ();
612 if ((connectionString == null) || (connectionString.Trim().Length == 0)) {
613 this.connectionString = connectionString;
617 connectionString += ";";
619 bool inQuote = false;
620 bool inDQuote = false;
623 string name = String.Empty;
624 string value = String.Empty;
625 StringBuilder sb = new StringBuilder ();
627 for (int i = 0; i < connectionString.Length; i += 1) {
628 char c = connectionString [i];
630 if (i == connectionString.Length - 1)
633 peek = connectionString [i + 1];
639 else if (peek.Equals (c)) {
649 else if (peek.Equals (c)) {
654 inDQuote = !inDQuote;
657 if (inDQuote || inQuote)
660 if (name != String.Empty && name != null) {
661 value = sb.ToString ();
662 SetProperties (name.ToLower ().Trim() , value);
664 else if (sb.Length != 0)
665 throw new ArgumentException ("Format of initialization string does not conform to specifications");
668 value = String.Empty;
669 sb = new StringBuilder ();
673 if (inDQuote || inQuote || !inName)
675 else if (peek.Equals (c)) {
681 name = sb.ToString ();
682 sb = new StringBuilder ();
687 if (inQuote || inDQuote)
689 else if (sb.Length > 0 && !peek.Equals (';'))
698 if (minPoolSize > maxPoolSize)
699 throw new ArgumentException ("Invalid value for "
700 + "'min pool size' or 'max pool size'; "
701 + "'min pool size' must not be greater "
702 + "than 'max pool size'.");
704 connectionString = connectionString.Substring (0 , connectionString.Length-1);
705 this.connectionString = connectionString;
708 void SetDefaultConnectionParameters ()
711 parms = new TdsConnectionParameters ();
714 dataSource = string.Empty;
715 connectionTimeout = DEFAULT_CONNECTIONTIMEOUT;
716 connectionLifeTime = DEFAULT_CONNECTIONLIFETIME;
717 connectionReset = true;
719 maxPoolSize = DEFAULT_MAXPOOLSIZE;
720 minPoolSize = DEFAULT_MINPOOLSIZE;
721 packetSize = DEFAULT_PACKETSIZE;
726 private void SetProperties (string name , string value)
730 case "application name" :
731 parms.ApplicationName = value;
733 case "attachdbfilename" :
734 case "extended properties" :
735 case "initial file name" :
736 parms.AttachDBFileName = value;
739 case "connect timeout" :
740 case "connection timeout" :
741 int tmpTimeout = ConvertToInt32 ("connect timeout", value,
742 DEFAULT_CONNECTIONTIMEOUT);
744 throw new ArgumentException ("Invalid 'connect timeout'. Must be an integer >=0 ");
746 connectionTimeout = tmpTimeout;
748 case "connection lifetime" :
749 connectionLifeTime = ConvertToInt32 ("connection lifetime", value, DEFAULT_CONNECTIONLIFETIME);
751 case "connection reset" :
752 connectionReset = ConvertToBoolean ("connection reset", value, true);
755 case "current language" :
756 parms.Language = value;
762 case "network address" :
766 if (ConvertToBoolean (name, value, false))
767 throw new NotImplementedException("SSL encryption for"
768 + " data sent between client and server is not"
772 if (!ConvertToBoolean (name, value, true))
773 throw new NotImplementedException("Disabling the automatic"
774 + " enlistment of connections in the thread's current"
775 + " transaction context is not implemented.");
777 case "initial catalog" :
779 parms.Database = value;
781 case "integrated security" :
782 case "trusted_connection" :
783 parms.DomainLogin = ConvertIntegratedSecurity(value);
785 case "max pool size" :
786 int tmpMaxPoolSize = ConvertToInt32 (name, value, DEFAULT_MAXPOOLSIZE);
787 if (tmpMaxPoolSize < MIN_MAXPOOLSIZE)
788 throw new ArgumentException (string.Format (CultureInfo.InvariantCulture,
789 "Invalid '{0}'. The value must be greater than {1}.",
790 name, MIN_MAXPOOLSIZE));
792 maxPoolSize = tmpMaxPoolSize;
794 case "min pool size" :
795 int tmpMinPoolSize = ConvertToInt32 (name, value, DEFAULT_MINPOOLSIZE);
796 if (tmpMinPoolSize < 0)
797 throw new ArgumentException ("Invalid 'min pool size'. Must be a integer >= 0");
799 minPoolSize = tmpMinPoolSize;
801 case "multipleactiveresultsets":
802 // FIXME: not implemented
803 ConvertToBoolean (name, value, false);
805 case "asynchronous processing" :
807 async = ConvertToBoolean (name, value, false);
811 case "network library" :
812 if (!value.ToUpper ().Equals ("DBMSSOCN"))
813 throw new ArgumentException ("Unsupported network library.");
816 int tmpPacketSize = ConvertToInt32 (name, value, DEFAULT_PACKETSIZE);
817 if (tmpPacketSize < MIN_PACKETSIZE || tmpPacketSize > MAX_PACKETSIZE)
818 throw new ArgumentException (string.Format (CultureInfo.InvariantCulture,
819 "Invalid 'Packet Size'. The value must be between {0} and {1}.",
820 MIN_PACKETSIZE, MAX_PACKETSIZE));
822 packetSize = tmpPacketSize;
826 parms.Password = new SecureString();
827 foreach (char c in value)
828 parms.Password.AppendChar(c);
829 parms.PasswordSet = true;
831 case "persistsecurityinfo" :
832 case "persist security info" :
833 // FIXME : not implemented
834 // throw new NotImplementedException ();
837 pooling = ConvertToBoolean (name, value, true);
845 case "workstation id" :
846 parms.Hostname = value;
848 case "user instance":
849 userInstance = ConvertToBoolean (name, value, false);
852 throw new ArgumentException("Keyword not supported : '" + name + "'.");
856 static bool IsValidDatabaseName (string database)
858 if ( database == null || database.Trim().Length == 0 || database.Length > 128)
861 if (database[0] == '"' && database[database.Length] == '"')
862 database = database.Substring (1, database.Length - 2);
863 else if (Char.IsDigit (database[0]))
866 if (database[0] == '_')
869 foreach (char c in database.Substring (1, database.Length - 1))
870 if (!Char.IsLetterOrDigit (c) && c != '_' && c != '-')
875 private void OnSqlInfoMessage (SqlInfoMessageEventArgs value)
877 if (InfoMessage != null)
878 InfoMessage (this, value);
882 private sealed class SqlMonitorSocket : UdpClient
884 // UDP port that the SQL Monitor listens
885 private static readonly int SqlMonitorUdpPort = 1434;
886 //private static readonly string SqlServerNotExist = "SQL Server does not exist or access denied";
888 private string server;
889 private string instance;
891 internal SqlMonitorSocket (string ServerName, string InstanceName)
892 : base (ServerName, SqlMonitorUdpPort)
895 instance = InstanceName;
898 internal int DiscoverTcpPort (int timeoutSeconds)
900 int SqlServerTcpPort;
901 Client.Blocking = false;
902 // send command to UDP 1434 (SQL Monitor) to get
903 // the TCP port to connect to the MS SQL server
904 ASCIIEncoding enc = new ASCIIEncoding ();
905 Byte[] rawrq = new Byte [instance.Length + 1];
907 enc.GetBytes (instance, 0, instance.Length, rawrq, 1);
908 Send (rawrq, rawrq.Length);
914 long timeout = timeoutSeconds * 1000000;
915 result = Client.Poll ((int)timeout, SelectMode.SelectRead);
919 if (Client.Available <= 0)
921 IPEndPoint endpoint = new IPEndPoint (Dns.GetHostEntry ("localhost").AddressList [0], 0);
924 rawrs = Receive (ref endpoint);
926 string rs = Encoding.ASCII.GetString (rawrs);
928 string[] rawtokens = rs.Split (';');
929 Hashtable data = new Hashtable ();
930 for (int i = 0; i < rawtokens.Length / 2 && i < 256; i++) {
931 data [rawtokens [i * 2]] = rawtokens [ i * 2 + 1];
934 if (!data.ContainsKey ("tcp")) {
935 string msg = "Mono does not support names pipes or shared memory "
936 + "for connecting to SQL Server. Please enable the TCP/IP "
938 throw new NotImplementedException (msg);
941 SqlServerTcpPort = int.Parse ((string) data ["tcp"]);
944 return SqlServerTcpPort;
953 public ColumnInfo (string name, Type type)
955 this.name = name; this.type = type;
959 static class ReservedWords
961 static readonly string [] reservedWords =
963 "ADD", "EXCEPT", "PERCENT", "ALL", "EXEC", "PLAN", "ALTER",
964 "EXECUTE", "PRECISION", "AND", "EXISTS", "PRIMARY", "ANY",
965 "EXIT", "PRINT", "AS", "FETCH", "PROC", "ASC", "FILE",
966 "PROCEDURE", "AUTHORIZATION", "FILLFACTOR", "PUBLIC",
967 "BACKUP", "FOR", "RAISERROR", "BEGIN", "FOREIGN", "READ",
968 "BETWEEN", "FREETEXT", "READTEXT", "BREAK", "FREETEXTTABLE",
969 "RECONFIGURE", "BROWSE", "FROM", "REFERENCES", "BULK",
970 "FULL", "REPLICATION", "BY", "FUNCTION", "RESTORE",
971 "CASCADE", "GOTO", "RESTRICT", "CASE", "GRANT", "RETURN",
972 "CHECK", "GROUP", "REVOKE", "CHECKPOINT", "HAVING", "RIGHT",
973 "CLOSE", "HOLDLOCK", "ROLLBACK", "CLUSTERED", "IDENTITY",
974 "ROWCOUNT", "COALESCE", "IDENTITY_INSERT", "ROWGUIDCOL",
975 "COLLATE", "IDENTITYCOL", "RULE", "COLUMN", "IF", "SAVE",
976 "COMMIT", "IN", "SCHEMA", "COMPUTE", "INDEX", "SELECT",
977 "CONSTRAINT", "INNER", "SESSION_USER", "CONTAINS", "INSERT",
978 "SET", "CONTAINSTABLE", "INTERSECT", "SETUSER", "CONTINUE",
979 "INTO", "SHUTDOWN", "CONVERT", "IS", "SOME", "CREATE",
980 "JOIN", "STATISTICS", "CROSS", "KEY", "SYSTEM_USER",
981 "CURRENT", "KILL", "TABLE", "CURRENT_DATE", "LEFT",
982 "TEXTSIZE", "CURRENT_TIME", "LIKE", "THEN",
983 "CURRENT_TIMESTAMP", "LINENO", "TO", "CURRENT_USER", "LOAD",
984 "TOP", "CURSOR", "NATIONAL", "TRAN", "DATABASE", "NOCHECK",
985 "TRANSACTION", "DBCC", "NONCLUSTERED", "TRIGGER",
986 "DEALLOCATE", "NOT", "TRUNCATE", "DECLARE", "NULL",
987 "TSEQUAL", "DEFAULT", "NULLIF", "UNION", "DELETE", "OF",
988 "UNIQUE", "DENY", "OFF", "UPDATE", "DESC", "OFFSETS",
989 "UPDATETEXT", "DISK", "ON", "USE", "DISTINCT", "OPEN",
990 "USER", "DISTRIBUTED", "OPENDATASOURCE", "VALUES", "DOUBLE",
991 "OPENQUERY", "VARYING", "DROP", "OPENROWSET", "VIEW",
992 "DUMMY", "OPENXML", "WAITFOR", "DUMP", "OPTION", "WHEN",
993 "ELSE", "OR", "WHERE", "END", "ORDER", "WHILE", "ERRLVL",
994 "OUTER", "WITH", "ESCAPE", "OVER", "WRITETEXT", "ABSOLUTE",
995 "FOUND", "PRESERVE", "ACTION", "FREE", "PRIOR", "ADMIN",
996 "GENERAL", "PRIVILEGES", "AFTER", "GET", "READS",
997 "AGGREGATE", "GLOBAL", "REAL", "ALIAS", "GO", "RECURSIVE",
998 "ALLOCATE", "GROUPING", "REF", "ARE", "HOST", "REFERENCING",
999 "ARRAY", "HOUR", "RELATIVE", "ASSERTION", "IGNORE", "RESULT",
1000 "AT", "IMMEDIATE", "RETURNS", "BEFORE", "INDICATOR", "ROLE",
1001 "BINARY", "INITIALIZE", "ROLLUP", "BIT", "INITIALLY",
1002 "ROUTINE", "BLOB", "INOUT", "ROW", "BOOLEAN", "INPUT",
1003 "ROWS", "BOTH", "INT", "SAVEPOINT", "BREADTH", "INTEGER",
1004 "SCROLL", "CALL", "INTERVAL", "SCOPE", "CASCADED",
1005 "ISOLATION", "SEARCH", "CAST", "ITERATE", "SECOND",
1006 "CATALOG", "LANGUAGE", "SECTION", "CHAR", "LARGE",
1007 "SEQUENCE", "CHARACTER", "LAST", "SESSION", "CLASS",
1008 "LATERAL", "SETS", "CLOB", "LEADING", "SIZE", "COLLATION",
1009 "LESS", "SMALLINT", "COMPLETION", "LEVEL", "SPACE",
1010 "CONNECT", "LIMIT", "SPECIFIC", "CONNECTION", "LOCAL",
1011 "SPECIFICTYPE", "CONSTRAINTS", "LOCALTIME", "SQL",
1012 "CONSTRUCTOR", "LOCALTIMESTAMP", "SQLEXCEPTION",
1013 "CORRESPONDING", "LOCATOR", "SQLSTATE", "CUBE", "MAP",
1014 "SQLWARNING", "CURRENT_PATH", "MATCH", "START",
1015 "CURRENT_ROLE", "MINUTE", "STATE", "CYCLE", "MODIFIES",
1016 "STATEMENT", "DATA", "MODIFY", "STATIC", "DATE", "MODULE",
1017 "STRUCTURE", "DAY", "MONTH", "TEMPORARY", "DEC", "NAMES",
1018 "TERMINATE", "DECIMAL", "NATURAL", "THAN", "DEFERRABLE",
1019 "NCHAR", "TIME", "DEFERRED", "NCLOB", "TIMESTAMP", "DEPTH",
1020 "NEW", "TIMEZONE_HOUR", "DEREF", "NEXT", "TIMEZONE_MINUTE",
1021 "DESCRIBE", "NO", "TRAILING", "DESCRIPTOR", "NONE",
1022 "TRANSLATION", "DESTROY", "NUMERIC", "TREAT", "DESTRUCTOR",
1023 "OBJECT", "TRUE", "DETERMINISTIC", "OLD", "UNDER",
1024 "DICTIONARY", "ONLY", "UNKNOWN", "DIAGNOSTICS", "OPERATION",
1025 "UNNEST", "DISCONNECT", "ORDINALITY", "USAGE", "DOMAIN",
1026 "OUT", "USING", "DYNAMIC", "OUTPUT", "VALUE", "EACH",
1027 "PAD", "VARCHAR", "END-EXEC", "PARAMETER", "VARIABLE",
1028 "EQUALS", "PARAMETERS", "WHENEVER", "EVERY", "PARTIAL",
1029 "WITHOUT", "EXCEPTION", "PATH", "WORK", "EXTERNAL",
1030 "POSTFIX", "WRITE", "FALSE", "PREFIX", "YEAR", "FIRST",
1031 "PREORDER", "ZONE", "FLOAT", "PREPARE", "ADA", "AVG",
1032 "BIT_LENGTH", "CHAR_LENGTH", "CHARACTER_LENGTH", "COUNT",
1033 "EXTRACT", "FORTRAN", "INCLUDE", "INSENSITIVE", "LOWER",
1034 "MAX", "MIN", "OCTET_LENGTH", "OVERLAPS", "PASCAL",
1035 "POSITION", "SQLCA", "SQLCODE", "SQLERROR", "SUBSTRING",
1036 "SUM", "TRANSLATE", "TRIM", "UPPER"
1038 static DataTable instance;
1039 static public DataTable Instance {
1041 if (instance == null) {
1043 var newInstance = new DataTable ("ReservedWords");
1044 newInstance.Columns.Add ("ReservedWord", typeof(string));
1045 foreach (string reservedWord in reservedWords)
1047 row = newInstance.NewRow();
1049 row["ReservedWord"] = reservedWord;
1050 newInstance.Rows.Add(row);
1052 instance = newInstance;
1059 static class MetaDataCollections
1061 static readonly ColumnInfo [] columns = {
1062 new ColumnInfo ("CollectionName", typeof (string)),
1063 new ColumnInfo ("NumberOfRestrictions", typeof (int)),
1064 new ColumnInfo ("NumberOfIdentifierParts", typeof (int))
1067 static readonly object [][] rows = {
1068 new object [] {"MetaDataCollections", 0, 0},
1069 new object [] {"DataSourceInformation", 0, 0},
1070 new object [] {"DataTypes", 0, 0},
1071 new object [] {"Restrictions", 0, 0},
1072 new object [] {"ReservedWords", 0, 0},
1073 new object [] {"Users", 1, 1},
1074 new object [] {"Databases", 1, 1},
1075 new object [] {"Tables", 4, 3},
1076 new object [] {"Columns", 4, 4},
1077 new object [] {"StructuredTypeMembers", 4, 4},
1078 new object [] {"Views", 3, 3},
1079 new object [] {"ViewColumns", 4, 4},
1080 new object [] {"ProcedureParameters", 4, 1},
1081 new object [] {"Procedures", 4, 3},
1082 new object [] {"ForeignKeys", 4, 3},
1083 new object [] {"IndexColumns", 5, 4},
1084 new object [] {"Indexes", 4, 3},
1085 new object [] {"UserDefinedTypes", 2, 1}
1088 static DataTable instance;
1089 static public DataTable Instance {
1091 if (instance == null) {
1092 var newInstance = new DataTable ("MetaDataCollections");
1093 foreach (ColumnInfo c in columns)
1094 newInstance.Columns.Add (c.name, c.type);
1095 foreach (object [] row in rows)
1096 newInstance.LoadDataRow (row, true);
1097 instance = newInstance;
1104 static class DataSourceInformation
1106 static readonly ColumnInfo [] columns = {
1107 new ColumnInfo ("CompositeIdentifierSeparatorPattern", typeof (string)),
1108 new ColumnInfo ("DataSourceProductName", typeof(string)),
1109 new ColumnInfo ("DataSourceProductVersion", typeof(string)),
1110 new ColumnInfo ("DataSourceProductVersionNormalized", typeof(string)),
1111 new ColumnInfo ("GroupByBehavior", typeof(GroupByBehavior)),
1112 new ColumnInfo ("IdentifierPattern", typeof(string)),
1113 new ColumnInfo ("IdentifierCase", typeof(IdentifierCase)),
1114 new ColumnInfo ("OrderByColumnsInSelect", typeof(bool)),
1115 new ColumnInfo ("ParameterMarkerFormat", typeof(string)),
1116 new ColumnInfo ("ParameterMarkerPattern", typeof(string)),
1117 new ColumnInfo ("ParameterNameMaxLength", typeof(int)),
1118 new ColumnInfo ("ParameterNamePattern", typeof(string)),
1119 new ColumnInfo ("QuotedIdentifierPattern", typeof(string)),
1120 new ColumnInfo ("QuotedIdentifierCase", typeof(IdentifierCase)),
1121 new ColumnInfo ("StatementSeparatorPattern", typeof(string)),
1122 new ColumnInfo ("StringLiteralPattern", typeof(string)),
1123 new ColumnInfo ("SupportedJoinOperators", typeof(SupportedJoinOperators))
1126 static public DataTable GetInstance (SqlConnection conn)
1128 DataTable table = new DataTable ("DataSourceInformation");
1129 foreach (ColumnInfo c in columns)
1130 table.Columns.Add (c.name, c.type);
1131 DataRow row = table.NewRow ();
1133 row [1] = "Microsoft SQL Server";
1134 row [2] = conn.ServerVersion;;
1135 row [3] = conn.ServerVersion;;
1136 row [4] = GroupByBehavior.Unrelated;
1137 row [5] = @"(^\[\p{Lo}\p{Lu}\p{Ll}_@#][\p{Lo}\p{Lu}\p{Ll}\p{Nd}@$#_]*$)|(^\[[^\]\0]|\]\]+\]$)|(^\""[^\""\0]|\""\""+\""$)";
1138 row [6] = IdentifierCase.Insensitive; // FIXME: obtain this from SQL Server
1141 row [9] = @"@[\p{Lo}\p{Lu}\p{Ll}\p{Lm}_@#][\p{Lo}\p{Lu}\p{Ll}\p{Lm}\p{Nd}\uff3f_@#\$]*(?=\s+|$)";
1143 row [11] = @"^[\p{Lo}\p{Lu}\p{Ll}\p{Lm}_@#][\p{Lo}\p{Lu}\p{Ll}\p{Lm}\p{Nd}\uff3f_@#\$]*(?=\s+|$)";
1144 row [12] = @"(([^\[]|\]\])*)";
1145 row [13] = IdentifierCase.Insensitive; // FIXME: obtain this from SQL Server
1147 row [15] = "'(([^']|'')*)'";
1148 row [16] = (SupportedJoinOperators.FullOuter | SupportedJoinOperators.Inner |
1149 SupportedJoinOperators.LeftOuter | SupportedJoinOperators.RightOuter);
1150 table.Rows.Add (row);
1155 static class DataTypes
1157 static readonly ColumnInfo [] columns = {
1158 new ColumnInfo ("TypeName", typeof(string)),
1159 new ColumnInfo ("ProviderDbType", typeof(int)),
1160 new ColumnInfo ("ColumnSize", typeof(long)),
1161 new ColumnInfo ("CreateFormat", typeof(string)),
1162 new ColumnInfo ("CreateParameters", typeof(string)),
1163 new ColumnInfo ("DataType", typeof(string)),
1164 new ColumnInfo ("IsAutoIncrementable", typeof(bool)),
1165 new ColumnInfo ("IsBestMatch", typeof(bool)),
1166 new ColumnInfo ("IsCaseSensitive", typeof(bool)),
1167 new ColumnInfo ("IsFixedLength", typeof(bool)),
1168 new ColumnInfo ("IsFixedPrecisionScale", typeof(bool)),
1169 new ColumnInfo ("IsLong", typeof(bool)),
1170 new ColumnInfo ("IsNullable", typeof(bool)),
1171 new ColumnInfo ("IsSearchable", typeof(bool)),
1172 new ColumnInfo ("IsSearchableWithLike", typeof(bool)),
1173 new ColumnInfo ("IsUnsigned", typeof(bool)),
1174 new ColumnInfo ("MaximumScale", typeof(short)),
1175 new ColumnInfo ("MinimumScale", typeof(short)),
1176 new ColumnInfo ("IsConcurrencyType", typeof(bool)),
1177 new ColumnInfo ("IsLiteralSupported", typeof(bool)),
1178 new ColumnInfo ("LiteralPrefix", typeof(string)),
1179 new ColumnInfo ("LiteralSuffix", typeof(string))
1182 static readonly object [][] rows = {
1183 new object [] {"smallint", 16, 5, "smallint", null, "System.Int16", true, true,
1184 false, true, true, false, true, true, false, false, null,
1185 null, false, null, null, null},
1186 new object [] {"int", 8, 10, "int", null, "System.Int32",
1187 true, true, false, true, true, false, true, true, false,
1188 false, null, null, false, null, null, null},
1189 new object [] {"real", 13, 7, "real", null,
1190 "System.Single", false, true, false, true, false, false,
1191 true, true, false, false, null, null, false, null, null, null},
1192 new object [] {"float", 6, 53, "float({0})",
1193 "number of bits used to store the mantissa", "System.Double",
1194 false, true, false, true, false, false, true, true,
1195 false, false, null, null, false, null, null, null},
1196 new object [] {"money", 9, 19, "money", null,
1197 "System.Decimal", false, false, false, true, true,
1198 false, true, true, false, false, null, null, false,
1200 new object [] {"smallmoney", 17, 10, "smallmoney", null,
1201 "System.Decimal", false, false, false, true, true, false,
1202 true, true, false, false, null, null, false, null, null, null},
1203 new object [] {"bit", 2, 1, "bit", null, "System.Boolean",
1204 false, false, false, true, false, false, true, true,
1205 false, null, null, null, false, null, null, null},
1206 new object [] {"tinyint", 20, 3, "tinyint", null,
1207 "System.SByte", true, true, false, true, true, false,
1208 true, true, false, true, null, null, false, null, null, null},
1209 new object [] {"bigint", 0, 19, "bigint", null,
1210 "System.Int64", true, true, false, true, true, false,
1211 true, true, false, false, null, null, false, null, null, null},
1212 new object [] {"timestamp", 19, 8, "timestamp", null,
1213 "System.Byte[]", false, false, false, true, false, false,
1214 false, true, false, null, null, null, true, null, "0x", null},
1215 new object [] {"binary", 1, 8000, "binary({0})", "length",
1216 "System.Byte[]", false, true, false, true, false, false,
1217 true, true, false, null, null, null, false, null, "0x", null},
1218 new object [] {"image", 7, 2147483647, "image", null,
1219 "System.Byte[]", false, true, false, false, false, true,
1220 true, false, false, null, null, null, false, null, "0x", null},
1221 new object [] {"text", 18, 2147483647, "text", null,
1222 "System.String", false, true, false, false, false, true,
1223 true, false, true, null, null, null, false, null, "'", "'"},
1224 new object [] {"ntext", 11, 1073741823, "ntext", null,
1225 "System.String", false, true, false, false, false, true,
1226 true, false, true, null, null, null, false, null, "N'", "'"},
1227 new object [] {"decimal", 5, 38, "decimal({0}, {1})",
1228 "precision,scale", "System.Decimal", true, true, false,
1229 true, false, false, true, true, false, false, 38, 0,
1230 false, null, null, null},
1231 new object [] {"numeric", 5, 38, "numeric({0}, {1})",
1232 "precision,scale", "System.Decimal", true, true, false,
1233 true, false, false, true, true, false, false, 38, 0,
1234 false, null, null, null},
1235 new object [] {"datetime", 4, 23, "datetime", null,
1236 "System.DateTime", false, true, false, true, false, false,
1237 true, true, true, null, null, null, false, null, "{ts '", "'}"},
1238 new object [] {"smalldatetime", 15, 16, "smalldatetime", null,
1239 "System.DateTime", false, true, false, true, false, false,
1240 true, true, true, null, null, null, false, null, "{ts '", "'}"},
1241 new object [] {"sql_variant", 23, null, "sql_variant",
1242 null, "System.Object", false, true, false, false, false,
1243 false, true, true, false, null, null, null, false, false,
1245 new object [] {"xml", 25, 2147483647, "xml", null,
1246 "System.String", false, false, false, false, false, true,
1247 true, false, false, null, null, null, false, false, null, null},
1248 new object [] {"varchar", 22, 2147483647, "varchar({0})",
1249 "max length", "System.String", false, true, false, false,
1250 false, false, true, true, true, null, null, null, false,
1252 new object [] {"char", 3, 2147483647, "char({0})", "length",
1253 "System.String", false, true, false, true, false, false,
1254 true, true, true, null, null, null, false, null, "'", "'"},
1255 new object [] {"nchar", 10, 1073741823, "nchar({0})", "length",
1256 "System.String", false, true, false, true, false, false,
1257 true, true, true, null, null, null, false, null, "N'", "'"},
1258 new object [] {"nvarchar", 12, 1073741823, "nvarchar({0})", "max length",
1259 "System.String", false, true, false, false, false, false, true, true,
1260 true, null, null, null, false, null, "N'", "'"},
1261 new object [] {"varbinary", 21, 1073741823, "varbinary({0})",
1262 "max length", "System.Byte[]", false, true, false, false,
1263 false, false, true, true, false, null, null, null, false,
1265 new object [] {"uniqueidentifier", 14, 16, "uniqueidentifier", null,
1266 "System.Guid", false, true, false, true, false, false, true,
1267 true, false, null, null, null, false, null, "'", "'"},
1268 new object [] {"date", 31, 3L, "date", DBNull.Value,
1269 "System.DateTime", false, false, false, true, true, false,
1270 true, true, true, DBNull.Value, DBNull.Value, DBNull.Value,
1271 false, DBNull.Value, "{ts '", "'}"},
1272 new object [] {"time", 32, 5L, "time({0})", "scale",
1273 "System.TimeSpan", false, false, false, false, false, false,
1274 true, true, true, DBNull.Value, (short) 7, (short) 0,
1275 false, DBNull.Value, "{ts '", "'}"},
1276 new object [] {"datetime2", 33, 8L, "datetime2({0})", "scale",
1277 "System.DateTime", false, true, false, false, false, false,
1278 true, true, true, DBNull.Value, (short) 7, (short) 0,
1279 false, DBNull.Value, "{ts '", "'}"},
1280 new object [] {"datetimeoffset", 34, 10L, "datetimeoffset({0})",
1281 "scale", "System.DateTimeOffset", false, true, false, false,
1282 false, false, true, true, true, DBNull.Value, (short) 7, (short) 0,
1283 false, DBNull.Value, "{ts '", "'}"}
1286 static DataTable instance;
1287 static public DataTable Instance {
1289 if (instance == null) {
1290 instance = new DataTable ("DataTypes");
1291 foreach (ColumnInfo c in columns)
1292 instance.Columns.Add (c.name, c.type);
1293 foreach (object [] row in rows)
1294 instance.LoadDataRow (row, true);
1301 static class Restrictions
1303 static readonly ColumnInfo [] columns = {
1304 new ColumnInfo ("CollectionName", typeof (string)),
1305 new ColumnInfo ("RestrictionName", typeof(string)),
1306 new ColumnInfo ("ParameterName", typeof(string)),
1307 new ColumnInfo ("RestrictionDefault", typeof(string)),
1308 new ColumnInfo ("RestrictionNumber", typeof(int))
1311 static readonly object [][] rows = {
1312 new object [] {"Users", "User_Name", "@Name", "name", 1},
1313 new object [] {"Databases", "Name", "@Name", "Name", 1},
1315 new object [] {"Tables", "Catalog", "@Catalog", "TABLE_CATALOG", 1},
1316 new object [] {"Tables", "Owner", "@Owner", "TABLE_SCHEMA", 2},
1317 new object [] {"Tables", "Table", "@Name", "TABLE_NAME", 3},
1318 new object [] {"Tables", "TableType", "@TableType", "TABLE_TYPE", 4},
1320 new object [] {"Columns", "Catalog", "@Catalog", "TABLE_CATALOG", 1},
1321 new object [] {"Columns", "Owner", "@Owner", "TABLE_SCHEMA", 2},
1322 new object [] {"Columns", "Table", "@Table", "TABLE_NAME", 3},
1323 new object [] {"Columns", "Column", "@Column", "COLUMN_NAME", 4},
1325 new object [] {"StructuredTypeMembers", "Catalog", "@Catalog", "TYPE_CATALOG", 1},
1326 new object [] {"StructuredTypeMembers", "Owner", "@Owner", "TYPE_SCHEMA", 2},
1327 new object [] {"StructuredTypeMembers", "Type", "@Type", "TYPE_NAME", 3},
1328 new object [] {"StructuredTypeMembers", "Member", "@Member", "MEMBER_NAME", 4},
1330 new object [] {"Views", "Catalog", "@Catalog", "TABLE_CATALOG", 1},
1331 new object [] {"Views", "Owner", "@Owner", "TABLE_SCHEMA", 2},
1332 new object [] {"Views", "Table", "@Table", "TABLE_NAME", 3},
1334 new object [] {"ViewColumns", "Catalog", "@Catalog", "VIEW_CATALOG", 1},
1335 new object [] {"ViewColumns", "Owner", "@Owner", "VIEW_SCHEMA", 2},
1336 new object [] {"ViewColumns", "Table", "@Table", "VIEW_NAME", 3},
1337 new object [] {"ViewColumns", "Column", "@Column", "COLUMN_NAME", 4},
1339 new object [] {"ProcedureParameters", "Catalog", "@Catalog", "SPECIFIC_CATALOG", 1},
1340 new object [] {"ProcedureParameters", "Owner", "@Owner", "SPECIFIC_SCHEMA", 2},
1341 new object [] {"ProcedureParameters", "Name", "@Name", "SPECIFIC_NAME", 3},
1342 new object [] {"ProcedureParameters", "Parameter", "@Parameter", "PARAMETER_NAME", 4},
1344 new object [] {"Procedures", "Catalog", "@Catalog", "SPECIFIC_CATALOG", 1},
1345 new object [] {"Procedures", "Owner", "@Owner", "SPECIFIC_SCHEMA", 2},
1346 new object [] {"Procedures", "Name", "@Name", "SPECIFIC_NAME", 3},
1347 new object [] {"Procedures", "Type", "@Type", "ROUTINE_TYPE", 4},
1349 new object [] {"IndexColumns", "Catalog", "@Catalog", "db_name()", 1},
1350 new object [] {"IndexColumns", "Owner", "@Owner", "user_name()", 2},
1351 new object [] {"IndexColumns", "Table", "@Table", "o.name", 3},
1352 new object [] {"IndexColumns", "ConstraintName", "@ConstraintName", "x.name", 4},
1353 new object [] {"IndexColumns", "Column", "@Column", "c.name", 5},
1355 new object [] {"Indexes", "Catalog", "@Catalog", "db_name()", 1},
1356 new object [] {"Indexes", "Owner", "@Owner", "user_name()", 2},
1357 new object [] {"Indexes", "Table", "@Table", "o.name", 3},
1358 new object [] {"Indexes", "Name", "@Name", "x.name", 4},
1360 new object [] {"UserDefinedTypes", "assembly_name", "@AssemblyName", "assemblies.name", 1},
1361 new object [] {"UserDefinedTypes", "udt_name", "@UDTName", "types.assembly_class", 2},
1363 new object [] {"ForeignKeys", "Catalog", "@Catalog", "CONSTRAINT_CATALOG", 1},
1364 new object [] {"ForeignKeys", "Owner", "@Owner", "CONSTRAINT_SCHEMA", 2},
1365 new object [] {"ForeignKeys", "Table", "@Table", "TABLE_NAME", 3},
1366 new object [] {"ForeignKeys", "Name", "@Name", "CONSTRAINT_NAME", 4}
1369 static DataTable instance;
1370 static public DataTable Instance {
1372 if (instance == null) {
1373 instance = new DataTable ("Restrictions");
1374 foreach (ColumnInfo c in columns)
1375 instance.Columns.Add (c.name, c.type);
1376 foreach (object [] row in rows)
1377 instance.LoadDataRow (row, true);
1384 public override DataTable GetSchema ()
1386 if (state == ConnectionState.Closed)
1387 throw ExceptionHelper.ConnectionClosed ();
1389 return MetaDataCollections.Instance;
1392 public override DataTable GetSchema (String collectionName)
1394 return GetSchema (collectionName, null);
1397 public override DataTable GetSchema (String collectionName, string [] restrictionValues)
1399 // LAMESPEC: In MS.NET, if collectionName is null, it throws ArgumentException.
1401 if (state == ConnectionState.Closed)
1402 throw ExceptionHelper.ConnectionClosed ();
1404 String cName = null;
1405 DataTable schemaTable = MetaDataCollections.Instance;
1406 int length = restrictionValues == null ? 0 : restrictionValues.Length;
1408 foreach (DataRow row in schemaTable.Rows) {
1409 if (String.Compare ((string) row["CollectionName"], collectionName, true) == 0) {
1410 if (length > (int) row["NumberOfRestrictions"]) {
1411 throw new ArgumentException ("More restrictions were provided " +
1412 "than the requested schema ('" +
1413 row["CollectionName"].ToString () + "') supports");
1415 cName = row["CollectionName"].ToString();
1420 throw new ArgumentException (string.Format (CultureInfo.InvariantCulture,
1421 "The requested collection ({0}) is not defined.",
1424 SqlCommand command = null;
1425 DataTable dataTable = new DataTable ();
1426 SqlDataAdapter dataAdapter = new SqlDataAdapter ();
1431 command = new SqlCommand ("select name as database_name, dbid, crdate as create_date " +
1432 "from master.sys.sysdatabases where (name = @Name or (@Name " +
1434 command.Parameters.Add ("@Name", SqlDbType.NVarChar, 4000);
1437 command = new SqlCommand ("select CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME, " +
1438 "TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, CONSTRAINT_TYPE, " +
1439 "IS_DEFERRABLE, INITIALLY_DEFERRED from " +
1440 "INFORMATION_SCHEMA.TABLE_CONSTRAINTS where (CONSTRAINT_CATALOG" +
1441 " = @Catalog or (@Catalog is null)) and (CONSTRAINT_SCHEMA = " +
1442 "@Owner or (@Owner is null)) and (TABLE_NAME = @Table or (" +
1443 "@Table is null)) and (CONSTRAINT_NAME = @Name or (@Name is null))" +
1444 " and CONSTRAINT_TYPE = 'FOREIGN KEY' order by CONSTRAINT_CATALOG," +
1445 " CONSTRAINT_SCHEMA, CONSTRAINT_NAME", this);
1446 command.Parameters.Add ("@Catalog", SqlDbType.NVarChar, 4000);
1447 command.Parameters.Add ("@Owner", SqlDbType.NVarChar, 4000);
1448 command.Parameters.Add ("@Table", SqlDbType.NVarChar, 4000);
1449 command.Parameters.Add ("@Name", SqlDbType.NVarChar, 4000);
1452 command = new SqlCommand ("select distinct db_name() as constraint_catalog, " +
1453 "constraint_schema = user_name (o.uid), " +
1454 "constraint_name = x.name, table_catalog = db_name (), " +
1455 "table_schema = user_name (o.uid), table_name = o.name, " +
1456 "index_name = x.name from sysobjects o, sysindexes x, " +
1457 "sysindexkeys xk where o.type in ('U') and x.id = o.id and " +
1458 "o.id = xk.id and x.indid = xk.indid and xk.keyno = x.keycnt " +
1459 "and (db_name() = @Catalog or (@Catalog is null)) and " +
1460 "(user_name() = @Owner or (@Owner is null)) and (o.name = " +
1461 "@Table or (@Table is null)) and (x.name = @Name or (@Name is null))" +
1462 "order by table_name, index_name", this);
1463 command.Parameters.Add ("@Catalog", SqlDbType.NVarChar, 4000);
1464 command.Parameters.Add ("@Owner", SqlDbType.NVarChar, 4000);
1465 command.Parameters.Add ("@Table", SqlDbType.NVarChar, 4000);
1466 command.Parameters.Add ("@Name", SqlDbType.NVarChar, 4000);
1468 case "IndexColumns":
1469 command = new SqlCommand ("select distinct db_name() as constraint_catalog, " +
1470 "constraint_schema = user_name (o.uid), constraint_name = x.name, " +
1471 "table_catalog = db_name (), table_schema = user_name (o.uid), " +
1472 "table_name = o.name, column_name = c.name, " +
1473 "ordinal_position = convert (int, xk.keyno), keyType = c.xtype, " +
1474 "index_name = x.name from sysobjects o, sysindexes x, syscolumns c, " +
1475 "sysindexkeys xk where o.type in ('U') and x.id = o.id and o.id = c.id " +
1476 "and o.id = xk.id and x.indid = xk.indid and c.colid = xk.colid " +
1477 "and xk.keyno <= x.keycnt and permissions (o.id, c.name) <> 0 " +
1478 "and (db_name() = @Catalog or (@Catalog is null)) and (user_name() " +
1479 "= @Owner or (@Owner is null)) and (o.name = @Table or (@Table is" +
1480 " null)) and (x.name = @ConstraintName or (@ConstraintName is null)) " +
1481 "and (c.name = @Column or (@Column is null)) order by table_name, " +
1482 "index_name", this);
1483 command.Parameters.Add ("@Catalog", SqlDbType.NVarChar, 8);
1484 command.Parameters.Add ("@Owner", SqlDbType.NVarChar, 4000);
1485 command.Parameters.Add ("@Table", SqlDbType.NVarChar, 13);
1486 command.Parameters.Add ("@ConstraintName", SqlDbType.NVarChar, 4000);
1487 command.Parameters.Add ("@Column", SqlDbType.NVarChar, 4000);
1490 command = new SqlCommand ("select SPECIFIC_CATALOG, SPECIFIC_SCHEMA, SPECIFIC_NAME, " +
1491 "ROUTINE_CATALOG, ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE, " +
1492 "CREATED, LAST_ALTERED from INFORMATION_SCHEMA.ROUTINES where " +
1493 "(SPECIFIC_CATALOG = @Catalog or (@Catalog is null)) and " +
1494 "(SPECIFIC_SCHEMA = @Owner or (@Owner is null)) and (SPECIFIC_NAME" +
1495 " = @Name or (@Name is null)) and (ROUTINE_TYPE = @Type or (@Type " +
1496 "is null)) order by SPECIFIC_CATALOG, SPECIFIC_SCHEMA, SPECIFIC_NAME", this);
1497 command.Parameters.Add ("@Catalog", SqlDbType.NVarChar, 4000);
1498 command.Parameters.Add ("@Owner", SqlDbType.NVarChar, 4000);
1499 command.Parameters.Add ("@Name", SqlDbType.NVarChar, 4000);
1500 command.Parameters.Add ("@Type", SqlDbType.NVarChar, 4000);
1502 case "ProcedureParameters":
1503 command = new SqlCommand ("select SPECIFIC_CATALOG, SPECIFIC_SCHEMA, SPECIFIC_NAME, " +
1504 "ORDINAL_POSITION, PARAMETER_MODE, IS_RESULT, AS_LOCATOR, " +
1505 "PARAMETER_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, " +
1506 "CHARACTER_OCTET_LENGTH, COLLATION_CATALOG, COLLATION_SCHEMA, " +
1507 "COLLATION_NAME, CHARACTER_SET_CATALOG, CHARACTER_SET_SCHEMA, " +
1508 "CHARACTER_SET_NAME, NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX, " +
1509 "NUMERIC_SCALE, DATETIME_PRECISION, INTERVAL_TYPE, " +
1510 "INTERVAL_PRECISION from INFORMATION_SCHEMA.PARAMETERS where " +
1511 "(SPECIFIC_CATALOG = @Catalog or (@Catalog is null)) and " +
1512 "(SPECIFIC_SCHEMA = @Owner or (@Owner is null)) and (SPECIFIC_NAME = " +
1513 "@Name or (@Name is null)) and (PARAMETER_NAME = @Parameter or (" +
1514 "@Parameter is null)) order by SPECIFIC_CATALOG, SPECIFIC_SCHEMA," +
1515 " SPECIFIC_NAME, PARAMETER_NAME", this);
1516 command.Parameters.Add ("@Catalog", SqlDbType.NVarChar, 4000);
1517 command.Parameters.Add ("@Owner", SqlDbType.NVarChar, 4000);
1518 command.Parameters.Add ("@Name", SqlDbType.NVarChar, 4000);
1519 command.Parameters.Add ("@Parameter", SqlDbType.NVarChar, 4000);
1522 command = new SqlCommand ("select TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE " +
1523 "from INFORMATION_SCHEMA.TABLES where" +
1524 " (TABLE_CATALOG = @catalog or (@catalog is null)) and " +
1525 "(TABLE_SCHEMA = @owner or (@owner is null))and " +
1526 "(TABLE_NAME = @name or (@name is null)) and " +
1527 "(TABLE_TYPE = @table_type or (@table_type is null))", this);
1528 command.Parameters.Add ("@catalog", SqlDbType.NVarChar, 8);
1529 command.Parameters.Add ("@owner", SqlDbType.NVarChar, 3);
1530 command.Parameters.Add ("@name", SqlDbType.NVarChar, 11);
1531 command.Parameters.Add ("@table_type", SqlDbType.NVarChar, 10);
1534 command = new SqlCommand ("select TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, " +
1535 "ORDINAL_POSITION, COLUMN_DEFAULT, IS_NULLABLE, DATA_TYPE, " +
1536 "CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH, " +
1537 "NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX, NUMERIC_SCALE, " +
1538 "DATETIME_PRECISION, CHARACTER_SET_CATALOG, CHARACTER_SET_SCHEMA, " +
1539 "CHARACTER_SET_NAME, COLLATION_CATALOG from INFORMATION_SCHEMA.COLUMNS" +
1540 " where (TABLE_CATALOG = @Catalog or (@Catalog is null)) and (" +
1541 "TABLE_SCHEMA = @Owner or (@Owner is null)) and (TABLE_NAME = @table" +
1542 " or (@Table is null)) and (COLUMN_NAME = @column or (@Column is null" +
1543 ")) order by TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME", this);
1544 command.Parameters.Add ("@Catalog", SqlDbType.NVarChar, 4000);
1545 command.Parameters.Add ("@Owner", SqlDbType.NVarChar, 4000);
1546 command.Parameters.Add ("@Table", SqlDbType.NVarChar, 4000);
1547 command.Parameters.Add ("@Column", SqlDbType.NVarChar, 4000);
1550 command = new SqlCommand ("select uid, name as user_name, createdate, updatedate from sysusers" +
1551 " where (name = @Name or (@Name is null))", this);
1552 command.Parameters.Add ("@Name", SqlDbType.NVarChar, 4000);
1554 case "StructuredTypeMembers":
1555 // Only available on SQL Server 2008
1556 // Running it again SQL 2005 results in the following exception:
1557 // Unable to build the 'StructuredTypeMembers' collection because
1558 // execution of the SQL query failed. See the inner exception for details.
1559 // ---> System.Data.SqlClient.SqlException: Invalid object name 'sys.table_types'.
1561 // I don't have access to SQL Server 2008 right now,
1562 // and can't find any online documentation on the 'sys.table_types'
1564 throw new NotImplementedException ();
1566 command = new SqlCommand ("select TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, CHECK_OPTION, " +
1567 "IS_UPDATABLE from INFORMATION_SCHEMA.VIEWS where (TABLE_CATALOG" +
1568 " = @Catalog or (@Catalog is null)) TABLE_SCHEMA = @Owner or " +
1569 "(@Owner is null)) and (TABLE_NAME = @table or (@Table is null))" +
1570 " order by TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME", this);
1571 command.Parameters.Add ("@Catalog", SqlDbType.NVarChar, 4000);
1572 command.Parameters.Add ("@Owner", SqlDbType.NVarChar, 4000);
1573 command.Parameters.Add ("@Table", SqlDbType.NVarChar, 4000);
1576 command = new SqlCommand ("select VIEW_CATALOG, VIEW_SCHEMA, VIEW_NAME, TABLE_CATALOG, " +
1577 "TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME from " +
1578 "INFORMATION_SCHEMA.VIEW_COLUMN_USAGE where (VIEW_CATALOG = " +
1579 "@Catalog (@Catalog is null)) and (VIEW_SCHEMA = @Owner (@Owner" +
1580 " is null)) and (VIEW_NAME = @Table or (@Table is null)) and " +
1581 "(COLUMN_NAME = @Column or (@Column is null)) order by " +
1582 "VIEW_CATALOG, VIEW_SCHEMA, VIEW_NAME", this);
1583 command.Parameters.Add ("@Catalog", SqlDbType.NVarChar, 4000);
1584 command.Parameters.Add ("@Owner", SqlDbType.NVarChar, 4000);
1585 command.Parameters.Add ("@Table", SqlDbType.NVarChar, 4000);
1586 command.Parameters.Add ("@Column", SqlDbType.NVarChar, 4000);
1588 case "UserDefinedTypes":
1589 command = new SqlCommand ("select assemblies.name as assembly_name, types.assembly_class " +
1590 "as udt_name, ASSEMBLYPROPERTY(assemblies.name, 'VersionMajor') " +
1591 "as version_major, ASSEMBLYPROPERTY(assemblies.name, 'VersionMinor') " +
1592 "as version_minor, ASSEMBLYPROPERTY(assemblies.name, 'VersionBuild') " +
1593 "as version_build, ASSEMBLYPROPERTY(assemblies.name, 'VersionRevision') " +
1594 "as version_revision, ASSEMBLYPROPERTY(assemblies.name, 'CultureInfo') " +
1595 "as culture_info, ASSEMBLYPROPERTY(assemblies.name, 'PublicKey') " +
1596 "as public_key, is_fixed_length, max_length, Create_Date, " +
1597 "Permission_set_desc from sys.assemblies as assemblies join " +
1598 "sys.assembly_types as types on assemblies.assembly_id = types.assembly_id" +
1599 " where (assportemblies.name = @AssemblyName or (@AssemblyName is null)) and " +
1600 "(types.assembly_class = @UDTName or (@UDTName is null))",
1602 command.Parameters.Add ("@AssemblyName", SqlDbType.NVarChar, 4000);
1603 command.Parameters.Add ("@UDTName", SqlDbType.NVarChar, 4000);
1605 case "MetaDataCollections":
1606 return MetaDataCollections.Instance;
1607 case "DataSourceInformation":
1608 return DataSourceInformation.GetInstance (this);
1610 return DataTypes.Instance;
1611 case "ReservedWords":
1612 return ReservedWords.Instance;
1613 case "Restrictions":
1614 return Restrictions.Instance;
1616 for (int i = 0; i < length; i++) {
1617 command.Parameters[i].Value = restrictionValues[i];
1619 dataAdapter.SelectCommand = command;
1620 dataAdapter.Fill (dataTable);
1624 public static void ChangePassword (string connectionString, string newPassword)
1626 if (String.IsNullOrEmpty (connectionString))
1627 throw new ArgumentNullException ("The 'connectionString' cannot be null or empty.");
1628 if (String.IsNullOrEmpty (newPassword))
1629 throw new ArgumentNullException ("The 'newPassword' cannot be null or empty.");
1630 if (newPassword.Length > 128)
1631 throw new ArgumentException ("The length of 'newPassword' cannot exceed 128 characters.");
1632 using (SqlConnection conn = new SqlConnection (connectionString)) {
1634 conn.tds.Execute (String.Format ("sp_password '{0}', '{1}', '{2}'",
1635 conn.parms.Password, newPassword, conn.parms.User));
1639 public static void ClearAllPools ()
1642 IDictionary pools = SqlConnection.sqlConnectionPools.GetConnectionPool ();
1643 foreach (TdsConnectionPool pool in pools.Values) {
1645 pool.ResetConnectionPool ();
1650 public static void ClearPool (SqlConnection connection)
1652 if (connection == null)
1653 throw new ArgumentNullException ("connection");
1656 if (connection.pooling) {
1657 TdsConnectionPool pool = sqlConnectionPools.GetConnectionPool (connection.ConnectionString);
1659 pool.ResetConnectionPool ();
1664 #endregion // Methods
1666 #region Fields Net 2
1671 #endregion // Fields Net 2
1673 #region Properties Net 2
1675 [DesignerSerializationVisibility (DesignerSerializationVisibility.Hidden)]
1676 internal bool AsyncProcessing {
1677 get { return async; }
1680 #endregion // Properties Net 2