?  search:
summary | log | graphiclog1 | graphiclog2 | commit | commitdiff | tree | refs | edit | fork
blame | history | raw | HEAD
2010-06-03 Jb Evain <jbevain@novell.com>
[mcs.git] / class / System.Data / System.Data.SqlClient / SqlConnection.cs
blobc6cb08f21785ca3a21e02264393211b6d6bb5c2e
1 //
2 // System.Data.SqlClient.SqlConnection.cs
3 //
4 // Authors:
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)
10 //
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
15 //
16
17 //
18 // Copyright (C) 2004 Novell, Inc (http://www.novell.com)
19 //
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:
27 //
28 // The above copyright notice and this permission notice shall be
29 // included in all copies or substantial portions of the Software.
30 //
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.
38 //
39
40 using Mono.Data.Tds;
41 using Mono.Data.Tds.Protocol;
42 using System;
43 using System.Collections;
44 using System.Collections.Specialized;
45 using System.ComponentModel;
46 using System.Data;
47 using System.Data.Common;
48 #if !MONOTOUCH
49 using System.EnterpriseServices;
50 #endif // !MONOTOUCH
51 using System.Globalization;
52 using System.Net;
53 using System.Net.Sockets;
54 using System.Text;
55 using System.Xml;
56 #if NET_2_0
57 using System.Collections.Generic;
58 #endif
59
60 namespace System.Data.SqlClient
61 {
62 [DefaultEvent ("InfoMessage")]
63 #if NET_2_0
64 public sealed class SqlConnection : DbConnection, IDbConnection, ICloneable
65 #else
66 public sealed class SqlConnection : Component, IDbConnection, ICloneable
67 #endif // NET_2_0
68 {
69 #region Fields
70
71 bool disposed;
72
73 // The set of SQL connection pools
74 static TdsConnectionPoolManager sqlConnectionPools = new TdsConnectionPoolManager (TdsVersion.tds80);
75 #if NET_2_0
76 const int DEFAULT_PACKETSIZE = 8000;
77 const int MAX_PACKETSIZE = 32768;
78 #else
79 const int DEFAULT_PACKETSIZE = 8192;
80 const int MAX_PACKETSIZE = 32767;
81 #endif
82 const int MIN_PACKETSIZE = 512;
83 const int DEFAULT_CONNECTIONTIMEOUT = 15;
84 const int DEFAULT_MAXPOOLSIZE = 100;
85 const int MIN_MAXPOOLSIZE = 1;
86 const int DEFAULT_MINPOOLSIZE = 0;
87 const int DEFAULT_PORT = 1433;
88
89 // The current connection pool
90 TdsConnectionPool pool;
91
92 // The connection string that identifies this connection
93 string connectionString;
94
95 // The transaction object for the current transaction
96 SqlTransaction transaction;
97
98 // Connection parameters
99
100 TdsConnectionParameters parms;
101 bool connectionReset;
102 bool pooling;
103 string dataSource;
104 int connectionTimeout;
105 int minPoolSize;
106 int maxPoolSize;
107 int packetSize;
108 int port;
109 bool fireInfoMessageEventOnUserErrors;
110 bool statisticsEnabled;
111
112 // The current state
113 ConnectionState state = ConnectionState.Closed;
114
115 SqlDataReader dataReader;
116 XmlReader xmlReader;
117
118 // The TDS object
119 Tds tds;
120
121 #endregion // Fields
122
123 #region Constructors
124
125 public SqlConnection () : this (null)
126 {
127 }
128
129 public SqlConnection (string connectionString)
130 {
131 ConnectionString = connectionString;
132 }
133
134 #endregion // Constructors
135
136 #region Properties
137
138 #if NET_1_0 || ONLY_1_1
139 [DataSysDescription ("Information used to connect to a DataSource, such as 'Data Source=x;Initial Catalog=x;Integrated Security=SSPI'.")]
140 #endif
141 [DefaultValue ("")]
142 [EditorAttribute ("Microsoft.VSDesigner.Data.SQL.Design.SqlConnectionStringEditor, "+ Consts.AssemblyMicrosoft_VSDesigner, "System.Drawing.Design.UITypeEditor, "+ Consts.AssemblySystem_Drawing )]
143 [RecommendedAsConfigurable (true)]
144 [RefreshProperties (RefreshProperties.All)]
145 public
146 #if NET_2_0
147 override
148 #endif // NET_2_0
149 string ConnectionString {
150 get {
151 if (connectionString == null)
152 return string.Empty;
153 return connectionString;
154 }
155 [MonoTODO("persist security info, encrypt, enlist keyword not implemented")]
156 set {
157 if (state == ConnectionState.Open)
158 throw new InvalidOperationException ("Not Allowed to change ConnectionString property while Connection state is OPEN");
159 SetConnectionString (value);
160 }
161 }
162
163 #if !NET_2_0
164 [DataSysDescription ("Current connection timeout value, 'Connect Timeout=X' in the ConnectionString.")]
165 #endif
166 [DesignerSerializationVisibility (DesignerSerializationVisibility.Hidden)]
167 public
168 #if NET_2_0
169 override
170 #endif // NET_2_0
171 int ConnectionTimeout {
172 get { return connectionTimeout; }
173 }
174
175 #if !NET_2_0
176 [DataSysDescription ("Current SQL Server database, 'Initial Catalog=X' in the connection string.")]
177 #endif
178 [DesignerSerializationVisibility (DesignerSerializationVisibility.Hidden)]
179 public
180 #if NET_2_0
181 override
182 #endif // NET_2_0
183 string Database {
184 get {
185 if (State == ConnectionState.Open)
186 return tds.Database;
187 return parms.Database ;
188 }
189 }
190
191 internal SqlDataReader DataReader {
192 get { return dataReader; }
193 set { dataReader = value; }
194 }
195
196 #if !NET_2_0
197 [DataSysDescription ("Current SqlServer that the connection is opened to, 'Data Source=X' in the connection string. ")]
198 #else
199 [Browsable(true)]
200 #endif
201 [DesignerSerializationVisibility (DesignerSerializationVisibility.Hidden)]
202 public
203 #if NET_2_0
204 override
205 #endif // NET_2_0
206 string DataSource {
207 get { return dataSource; }
208 }
209
210 #if !NET_2_0
211 [DataSysDescription ("Network packet size, 'Packet Size=x' in the connection string.")]
212 #endif
213 [DesignerSerializationVisibility (DesignerSerializationVisibility.Hidden)]
214 public int PacketSize {
215 get {
216 if (State == ConnectionState.Open)
217 return ((Tds) tds).PacketSize;
218 return packetSize;
219 }
220 }
221
222 [Browsable (false)]
223 #if !NET_2_0
224 [DataSysDescription ("Version of the SQL Server accessed by the SqlConnection.")]
225 #endif
226 [DesignerSerializationVisibility (DesignerSerializationVisibility.Hidden)]
227 public
228 #if NET_2_0
229 override
230 #endif // NET_2_0
231 string ServerVersion {
232 get {
233 if (state == ConnectionState.Closed)
234 throw ExceptionHelper.ConnectionClosed ();
235 else
236 return tds.ServerVersion;
237 }
238 }
239
240 [Browsable (false)]
241 #if !NET_2_0
242 [DataSysDescription ("The ConnectionState indicating whether the connection is open or closed.")]
243 #endif
244 [DesignerSerializationVisibility (DesignerSerializationVisibility.Hidden)]
245 public
246 #if NET_2_0
247 override
248 #endif // NET_2_0
249 ConnectionState State {
250 get { return state; }
251 }
252
253 internal Tds Tds {
254 get { return tds; }
255 }
256
257 internal SqlTransaction Transaction {
258 get { return transaction; }
259 set { transaction = value; }
260 }
261
262 #if !NET_2_0
263 [DataSysDescription ("Workstation Id, 'Workstation ID=x' in the connection string.")]
264 #endif
265 [DesignerSerializationVisibility (DesignerSerializationVisibility.Hidden)]
266 public string WorkstationId {
267 get { return parms.Hostname; }
268 }
269
270 internal XmlReader XmlReader {
271 get { return xmlReader; }
272 set { xmlReader = value; }
273 }
274
275 #if NET_2_0
276 public bool FireInfoMessageEventOnUserErrors {
277 get { return fireInfoMessageEventOnUserErrors; }
278 set { fireInfoMessageEventOnUserErrors = value; }
279 }
280
281 [DefaultValue (false)]
282 public bool StatisticsEnabled {
283 get { return statisticsEnabled; }
284 set { statisticsEnabled = value; }
285 }
286 #endif
287 #endregion // Properties
288
289 #region Events
290
291 #if !NET_2_0
292 [DataSysDescription ("Event triggered when messages arrive from the DataSource.")]
293 #endif
294 public event SqlInfoMessageEventHandler InfoMessage;
295
296 #if !NET_2_0
297 [DataSysDescription ("Event triggered when the connection changes state.")]
298 public new event StateChangeEventHandler StateChange;
299 #endif
300
301 #endregion // Events
302
303 #region Delegates
304
305 private void ErrorHandler (object sender, TdsInternalErrorMessageEventArgs e)
306 {
307 try {
308 if (!tds.IsConnected)
309 Close ();
310 } catch {
311 try {
312 Close ();
313 } catch {
314 }
315 }
316 throw new SqlException (e.Class, e.LineNumber, e.Message, e.Number, e.Procedure, e.Server, "Mono SqlClient Data Provider", e.State);
317 }
318
319 private void MessageHandler (object sender, TdsInternalInfoMessageEventArgs e)
320 {
321 OnSqlInfoMessage (CreateSqlInfoMessageEvent (e.Errors));
322 }
323
324 #endregion // Delegates
325
326 #region Methods
327
328 public new SqlTransaction BeginTransaction ()
329 {
330 return BeginTransaction (IsolationLevel.ReadCommitted, String.Empty);
331 }
332
333 public new SqlTransaction BeginTransaction (IsolationLevel iso)
334 {
335 return BeginTransaction (iso, String.Empty);
336 }
337
338 public SqlTransaction BeginTransaction (string transactionName)
339 {
340 return BeginTransaction (IsolationLevel.ReadCommitted, transactionName);
341 }
342
343 public SqlTransaction BeginTransaction (IsolationLevel iso, string transactionName)
344 {
345 if (state == ConnectionState.Closed)
346 throw ExceptionHelper.ConnectionClosed ();
347 if (transaction != null)
348 throw new InvalidOperationException ("SqlConnection does not support parallel transactions.");
349
350 string isolevel = String.Empty;
351 switch (iso) {
352 case IsolationLevel.ReadUncommitted:
353 isolevel = "READ UNCOMMITTED";
354 break;
355 case IsolationLevel.RepeatableRead:
356 isolevel = "REPEATABLE READ";
357 break;
358 case IsolationLevel.Serializable:
359 isolevel = "SERIALIZABLE";
360 break;
361 case IsolationLevel.ReadCommitted:
362 isolevel = "READ COMMITTED";
363 break;
364 #if NET_2_0
365 case IsolationLevel.Snapshot:
366 isolevel = "SNAPSHOT";
367 break;
368 case IsolationLevel.Unspecified:
369 iso = IsolationLevel.ReadCommitted;
370 isolevel = "READ COMMITTED";
371 break;
372 case IsolationLevel.Chaos:
373 throw new ArgumentOutOfRangeException ("IsolationLevel",
374 string.Format (CultureInfo.CurrentCulture,
375 "The IsolationLevel enumeration " +
376 "value, {0}, is not supported by " +
377 "the .Net Framework SqlClient " +
378 "Data Provider.", (int) iso));
379 #endif
380 default:
381 #if NET_2_0
382 throw new ArgumentOutOfRangeException ("IsolationLevel",
383 string.Format (CultureInfo.CurrentCulture,
384 "The IsolationLevel enumeration value, {0}, is invalid.",
385 (int) iso));
386 #else
387 throw new ArgumentException ("Invalid IsolationLevel parameter: must be ReadCommitted, ReadUncommitted, RepeatableRead, or Serializable.");
388 #endif
389 }
390
391 tds.Execute (String.Format ("SET TRANSACTION ISOLATION LEVEL {0};BEGIN TRANSACTION {1}", isolevel, transactionName));
392
393 transaction = new SqlTransaction (this, iso);
394 return transaction;
395 }
396
397 public
398 #if NET_2_0
399 override
400 #endif // NET_2_0
401 void ChangeDatabase (string database)
402 {
403 if (!IsValidDatabaseName (database))
404 throw new ArgumentException (String.Format ("The database name {0} is not valid.", database));
405 if (state != ConnectionState.Open)
406 throw new InvalidOperationException ("The connection is not open.");
407 tds.Execute (String.Format ("use [{0}]", database));
408 }
409
410 private void ChangeState (ConnectionState currentState)
411 {
412 if (currentState == state)
413 return;
414
415 ConnectionState originalState = state;
416 state = currentState;
417 OnStateChange (CreateStateChangeEvent (originalState, currentState));
418 }
419
420 public
421 #if NET_2_0
422 override
423 #endif // NET_2_0
424 void Close ()
425 {
426 if (transaction != null && transaction.IsOpen)
427 transaction.Rollback ();
428
429 if (dataReader != null || xmlReader != null) {
430 if(tds != null) tds.SkipToEnd ();
431 dataReader = null;
432 xmlReader = null;
433 }
434
435 if (tds != null && tds.IsConnected) {
436 if (pooling && tds.Pooling) {
437 if (pool != null) {
438 pool.ReleaseConnection (tds);
439 pool = null;
440 }
441 } else {
442 tds.Disconnect ();
443 }
444 }
445
446 if (tds != null) {
447 tds.TdsErrorMessage -= new TdsInternalErrorMessageEventHandler (ErrorHandler);
448 tds.TdsInfoMessage -= new TdsInternalInfoMessageEventHandler (MessageHandler);
449 }
450
451 ChangeState (ConnectionState.Closed);
452 }
453
454 public new SqlCommand CreateCommand ()
455 {
456 SqlCommand command = new SqlCommand ();
457 command.Connection = this;
458 return command;
459 }
460
461 private SqlInfoMessageEventArgs CreateSqlInfoMessageEvent (TdsInternalErrorCollection errors)
462 {
463 return new SqlInfoMessageEventArgs (errors);
464 }
465
466 private StateChangeEventArgs CreateStateChangeEvent (ConnectionState originalState, ConnectionState currentState)
467 {
468 return new StateChangeEventArgs (originalState, currentState);
469 }
470
471 protected override void Dispose (bool disposing)
472 {
473 try {
474 if (disposing && !disposed) {
475 if (State == ConnectionState.Open)
476 Close ();
477 ConnectionString = null;
478 }
479 } finally {
480 disposed = true;
481 base.Dispose (disposing);
482 }
483 }
484
485 #if !MONOTOUCH
486 [MonoTODO ("Not sure what this means at present.")]
487 public void EnlistDistributedTransaction (ITransaction transaction)
488 {
489 throw new NotImplementedException ();
490 }
491 #endif // !MONOTOUCH
492
493 object ICloneable.Clone ()
494 {
495 return new SqlConnection (ConnectionString);
496 }
497
498 #if NET_2_0
499 protected override DbTransaction BeginDbTransaction (IsolationLevel isolationLevel)
500 {
501 return BeginTransaction (isolationLevel);
502 }
503
504 protected override DbCommand CreateDbCommand ()
505 {
506 return CreateCommand ();
507 }
508 #else
509 IDbTransaction IDbConnection.BeginTransaction ()
510 {
511 return BeginTransaction ();
512 }
513
514 IDbTransaction IDbConnection.BeginTransaction (IsolationLevel iso)
515 {
516 return BeginTransaction (iso);
517 }
518
519 IDbCommand IDbConnection.CreateCommand ()
520 {
521 return CreateCommand ();
522 }
523 #endif
524
525 public
526 #if NET_2_0
527 override
528 #endif // NET_2_0
529 void Open ()
530 {
531 string serverName = string.Empty;
532 if (state == ConnectionState.Open)
533 throw new InvalidOperationException ("The Connection is already Open (State=Open)");
534
535 if (connectionString == null || connectionString.Trim().Length == 0)
536 throw new InvalidOperationException ("Connection string has not been initialized.");
537
538 try {
539 if (!pooling) {
540 if(!ParseDataSource (dataSource, out port, out serverName))
541 throw new SqlException(20, 0, "SQL Server does not exist or access denied.", 17, "ConnectionOpen (Connect()).", dataSource, parms.ApplicationName, 0);
542 tds = new Tds80 (serverName, port, PacketSize, ConnectionTimeout);
543 tds.Pooling = false;
544 }
545 else {
546 if(!ParseDataSource (dataSource, out port, out serverName))
547 throw new SqlException(20, 0, "SQL Server does not exist or access denied.", 17, "ConnectionOpen (Connect()).", dataSource, parms.ApplicationName, 0);
548
549 TdsConnectionInfo info = new TdsConnectionInfo (serverName, port, packetSize, ConnectionTimeout, minPoolSize, maxPoolSize);
550 pool = sqlConnectionPools.GetConnectionPool (connectionString, info);
551 tds = pool.GetConnection ();
552 }
553 } catch (TdsTimeoutException e) {
554 throw SqlException.FromTdsInternalException ((TdsInternalException) e);
555 } catch (TdsInternalException e) {
556 throw SqlException.FromTdsInternalException (e);
557 }
558
559 tds.TdsErrorMessage += new TdsInternalErrorMessageEventHandler (ErrorHandler);
560 tds.TdsInfoMessage += new TdsInternalInfoMessageEventHandler (MessageHandler);
561
562 if (!tds.IsConnected) {
563 try {
564 tds.Connect (parms);
565 } catch {
566 if (pooling)
567 pool.ReleaseConnection (tds);
568 throw;
569 }
570 }
571
572 disposed = false; // reset this, so using () would call Close ().
573 ChangeState (ConnectionState.Open);
574 }
575
576 private bool ParseDataSource (string theDataSource, out int thePort, out string theServerName)
577 {
578 theServerName = string.Empty;
579 string theInstanceName = string.Empty;
580
581 if (theDataSource == null)
582 throw new ArgumentException("Format of initialization string does not conform to specifications");
583
584 thePort = DEFAULT_PORT; // default TCP port for SQL Server
585 bool success = true;
586
587 int idx = 0;
588 if ((idx = theDataSource.IndexOf (',')) > -1) {
589 theServerName = theDataSource.Substring (0, idx);
590 string p = theDataSource.Substring (idx + 1);
591 thePort = Int32.Parse (p);
592 } else if ((idx = theDataSource.IndexOf ('\\')) > -1) {
593 theServerName = theDataSource.Substring (0, idx);
594 theInstanceName = theDataSource.Substring (idx + 1);
595
596 // do port discovery via UDP port 1434
597 port = DiscoverTcpPortViaSqlMonitor (theServerName, theInstanceName);
598 if (port == -1)
599 success = false;
600 } else
601 theServerName = theDataSource;
602
603 if (theServerName.Length == 0 || theServerName == "(local)" || theServerName == ".")
604 theServerName = "localhost";
605
606 if ((idx = theServerName.IndexOf ("tcp:")) > -1)
607 theServerName = theServerName.Substring (idx + 4);
608
609 return success;
610 }
611
612 private bool ConvertIntegratedSecurity (string value)
613 {
614 if (value.ToUpper() == "SSPI")
615 return true;
616
617 return ConvertToBoolean ("integrated security", value, false);
618 }
619
620 private bool ConvertToBoolean (string key, string value, bool defaultValue)
621 {
622 if (value.Length == 0)
623 return defaultValue;
624
625 string upperValue = value.ToUpper ();
626
627 if (upperValue == "TRUE" || upperValue == "YES")
628 return true;
629 else if (upperValue == "FALSE" || upperValue == "NO")
630 return false;
631
632 throw new ArgumentException (string.Format (CultureInfo.InvariantCulture,
633 "Invalid value \"{0}\" for key '{1}'.", value, key));
634 }
635
636 private int ConvertToInt32 (string key, string value, int defaultValue)
637 {
638 if (value.Length == 0)
639 return defaultValue;
640
641 try {
642 return int.Parse (value);
643 } catch (Exception ex) {
644 throw new ArgumentException (string.Format (CultureInfo.InvariantCulture,
645 "Invalid value \"{0}\" for key '{1}'.", value, key), ex);
646 }
647 }
648
649 private int DiscoverTcpPortViaSqlMonitor (string ServerName, string InstanceName)
650 {
651 SqlMonitorSocket msock;
652 msock = new SqlMonitorSocket (ServerName, InstanceName);
653 int SqlServerPort = msock.DiscoverTcpPort (ConnectionTimeout);
654 msock = null;
655 return SqlServerPort;
656 }
657
658 void SetConnectionString (string connectionString)
659 {
660 SetDefaultConnectionParameters ();
661
662 if ((connectionString == null) || (connectionString.Trim().Length == 0)) {
663 this.connectionString = connectionString;
664 return;
665 }
666
667 connectionString += ";";
668
669 bool inQuote = false;
670 bool inDQuote = false;
671 bool inName = true;
672
673 string name = String.Empty;
674 string value = String.Empty;
675 StringBuilder sb = new StringBuilder ();
676
677 for (int i = 0; i < connectionString.Length; i += 1) {
678 char c = connectionString [i];
679 char peek;
680 if (i == connectionString.Length - 1)
681 peek = '\0';
682 else
683 peek = connectionString [i + 1];
684
685 switch (c) {
686 case '\'':
687 if (inDQuote)
688 sb.Append (c);
689 else if (peek.Equals (c)) {
690 sb.Append (c);
691 i += 1;
692 }
693 else
694 inQuote = !inQuote;
695 break;
696 case '"':
697 if (inQuote)
698 sb.Append (c);
699 else if (peek.Equals (c)) {
700 sb.Append (c);
701 i += 1;
702 }
703 else
704 inDQuote = !inDQuote;
705 break;
706 case ';':
707 if (inDQuote || inQuote)
708 sb.Append (c);
709 else {
710 if (name != String.Empty && name != null) {
711 value = sb.ToString ();
712 SetProperties (name.ToLower ().Trim() , value);
713 }
714 else if (sb.Length != 0)
715 throw new ArgumentException ("Format of initialization string does not conform to specifications");
716 inName = true;
717 name = String.Empty;
718 value = String.Empty;
719 sb = new StringBuilder ();
720 }
721 break;
722 case '=':
723 if (inDQuote || inQuote || !inName)
724 sb.Append (c);
725 else if (peek.Equals (c)) {
726 sb.Append (c);
727 i += 1;
728
729 }
730 else {
731 name = sb.ToString ();
732 sb = new StringBuilder ();
733 inName = false;
734 }
735 break;
736 case ' ':
737 if (inQuote || inDQuote)
738 sb.Append (c);
739 else if (sb.Length > 0 && !peek.Equals (';'))
740 sb.Append (c);
741 break;
742 default:
743 sb.Append (c);
744 break;
745 }
746 }
747
748 if (minPoolSize > maxPoolSize)
749 throw new ArgumentException ("Invalid value for "
750 + "'min pool size' or 'max pool size'; "
751 + "'min pool size' must not be greater "
752 + "than 'max pool size'.");
753
754 connectionString = connectionString.Substring (0 , connectionString.Length-1);
755 this.connectionString = connectionString;
756 }
757
758 void SetDefaultConnectionParameters ()
759 {
760 if (parms == null)
761 parms = new TdsConnectionParameters ();
762 else
763 parms.Reset ();
764 dataSource = string.Empty;
765 connectionTimeout = DEFAULT_CONNECTIONTIMEOUT;
766 connectionReset = true;
767 pooling = true;
768 maxPoolSize = DEFAULT_MAXPOOLSIZE;
769 minPoolSize = DEFAULT_MINPOOLSIZE;
770 packetSize = DEFAULT_PACKETSIZE;
771 port = DEFAULT_PORT;
772 #if NET_2_0
773 async = false;
774 #endif
775 }
776
777 private void SetProperties (string name , string value)
778 {
779 switch (name) {
780 case "app" :
781 case "application name" :
782 parms.ApplicationName = value;
783 break;
784 case "attachdbfilename" :
785 case "extended properties" :
786 case "initial file name" :
787 parms.AttachDBFileName = value;
788 break;
789 case "timeout" :
790 case "connect timeout" :
791 case "connection timeout" :
792 int tmpTimeout = ConvertToInt32 ("connect timeout", value,
793 DEFAULT_CONNECTIONTIMEOUT);
794 if (tmpTimeout < 0)
795 throw new ArgumentException ("Invalid 'connect timeout'. Must be an integer >=0 ");
796 else
797 connectionTimeout = tmpTimeout;
798 break;
799 case "connection lifetime" :
800 break;
801 case "connection reset" :
802 connectionReset = ConvertToBoolean ("connection reset", value, true);
803 break;
804 case "language" :
805 case "current language" :
806 parms.Language = value;
807 break;
808 case "data source" :
809 case "server" :
810 case "address" :
811 case "addr" :
812 case "network address" :
813 dataSource = value;
814 break;
815 case "encrypt":
816 if (ConvertToBoolean (name, value, false))
817 throw new NotImplementedException("SSL encryption for"
818 + " data sent between client and server is not"
819 + " implemented.");
820 break;
821 case "enlist" :
822 if (!ConvertToBoolean (name, value, true))
823 throw new NotImplementedException("Disabling the automatic"
824 + " enlistment of connections in the thread's current"
825 + " transaction context is not implemented.");
826 break;
827 case "initial catalog" :
828 case "database" :
829 parms.Database = value;
830 break;
831 case "integrated security" :
832 case "trusted_connection" :
833 parms.DomainLogin = ConvertIntegratedSecurity(value);
834 break;
835 case "max pool size" :
836 int tmpMaxPoolSize = ConvertToInt32 (name, value, DEFAULT_MAXPOOLSIZE);
837 if (tmpMaxPoolSize < MIN_MAXPOOLSIZE)
838 throw new ArgumentException (string.Format (CultureInfo.InvariantCulture,
839 "Invalid '{0}'. The value must be greater than {1}.",
840 name, MIN_MAXPOOLSIZE));
841 else
842 maxPoolSize = tmpMaxPoolSize;
843 break;
844 case "min pool size" :
845 int tmpMinPoolSize = ConvertToInt32 (name, value, DEFAULT_MINPOOLSIZE);
846 if (tmpMinPoolSize < 0)
847 throw new ArgumentException ("Invalid 'min pool size'. Must be a integer >= 0");
848 else
849 minPoolSize = tmpMinPoolSize;
850 break;
851 #if NET_2_0
852 case "multipleactiveresultsets":
853 // FIXME: not implemented
854 ConvertToBoolean (name, value, false);
855 break;
856 case "asynchronous processing" :
857 case "async" :
858 async = ConvertToBoolean (name, value, false);
859 break;
860 #endif
861 case "net" :
862 case "network" :
863 case "network library" :
864 if (!value.ToUpper ().Equals ("DBMSSOCN"))
865 throw new ArgumentException ("Unsupported network library.");
866 break;
867 case "packet size" :
868 int tmpPacketSize = ConvertToInt32 (name, value, DEFAULT_PACKETSIZE);
869 if (tmpPacketSize < MIN_PACKETSIZE || tmpPacketSize > MAX_PACKETSIZE)
870 throw new ArgumentException (string.Format (CultureInfo.InvariantCulture,
871 "Invalid 'Packet Size'. The value must be between {0} and {1}.",
872 MIN_PACKETSIZE, MAX_PACKETSIZE));
873 else
874 packetSize = tmpPacketSize;
875 break;
876 case "password" :
877 case "pwd" :
878 parms.Password = value;
879 break;
880 case "persistsecurityinfo" :
881 case "persist security info" :
882 // FIXME : not implemented
883 // throw new NotImplementedException ();
884 break;
885 case "pooling" :
886 pooling = ConvertToBoolean (name, value, true);
887 break;
888 case "uid" :
889 case "user" :
890 case "user id" :
891 parms.User = value;
892 break;
893 case "wsid" :
894 case "workstation id" :
895 parms.Hostname = value;
896 break;
897 #if NET_2_0
898 case "user instance":
899 userInstance = ConvertToBoolean (name, value, false);
900 break;
901 #endif
902 default :
903 throw new ArgumentException("Keyword not supported : '" + name + "'.");
904 }
905 }
906
907 static bool IsValidDatabaseName (string database)
908 {
909 if ( database == null || database.Trim().Length == 0 || database.Length > 128)
910 return false ;
911
912 if (database[0] == '"' && database[database.Length] == '"')
913 database = database.Substring (1, database.Length - 2);
914 else if (Char.IsDigit (database[0]))
915 return false;
916
917 if (database[0] == '_')
918 return false;
919
920 foreach (char c in database.Substring (1, database.Length - 1))
921 if (!Char.IsLetterOrDigit (c) && c != '_' && c != '-')
922 return false;
923 return true;
924 }
925
926 private void OnSqlInfoMessage (SqlInfoMessageEventArgs value)
927 {
928 if (InfoMessage != null)
929 InfoMessage (this, value);
930 }
931
932 #if !NET_2_0
933 private new void OnStateChange (StateChangeEventArgs value)
934 {
935 if (StateChange != null)
936 StateChange (this, value);
937 }
938 #endif
939
940 private sealed class SqlMonitorSocket : UdpClient
941 {
942 // UDP port that the SQL Monitor listens
943 private static readonly int SqlMonitorUdpPort = 1434;
944 //private static readonly string SqlServerNotExist = "SQL Server does not exist or access denied";
945
946 private string server;
947 private string instance;
948
949 internal SqlMonitorSocket (string ServerName, string InstanceName)
950 : base (ServerName, SqlMonitorUdpPort)
951 {
952 server = ServerName;
953 instance = InstanceName;
954 }
955
956 internal int DiscoverTcpPort (int timeoutSeconds)
957 {
958 int SqlServerTcpPort;
959 Client.Blocking = false;
960 // send command to UDP 1434 (SQL Monitor) to get
961 // the TCP port to connect to the MS SQL server
962 ASCIIEncoding enc = new ASCIIEncoding ();
963 Byte[] rawrq = new Byte [instance.Length + 1];
964 rawrq[0] = 4;
965 enc.GetBytes (instance, 0, instance.Length, rawrq, 1);
966 Send (rawrq, rawrq.Length);
967
968 if (!Active)
969 return -1; // Error
970
971 bool result;
972 long timeout = timeoutSeconds * 1000000;
973 result = Client.Poll ((int)timeout, SelectMode.SelectRead);
974 if (result == false)
975 return -1; // Error
976
977 if (Client.Available <= 0)
978 return -1; // Error
979 #if NET_2_0
980 IPEndPoint endpoint = new IPEndPoint (Dns.GetHostEntry ("localhost").AddressList [0], 0);
981 #else
982 IPEndPoint endpoint = new IPEndPoint (Dns.GetHostByName ("localhost").AddressList [0], 0);
983 #endif
984 Byte [] rawrs;
985
986 rawrs = Receive (ref endpoint);
987
988 string rs = Encoding.ASCII.GetString (rawrs);
989
990 string[] rawtokens = rs.Split (';');
991 Hashtable data = new Hashtable ();
992 for (int i = 0; i < rawtokens.Length / 2 && i < 256; i++) {
993 data [rawtokens [i * 2]] = rawtokens [ i * 2 + 1];
994 }
995
996 if (!data.ContainsKey ("tcp")) {
997 string msg = "Mono does not support names pipes or shared memory "
998 + "for connecting to SQL Server. Please enable the TCP/IP "
999 + "protocol.";
1000 throw new NotImplementedException (msg);
1001 }
1002
1003 SqlServerTcpPort = int.Parse ((string) data ["tcp"]);
1004 Close ();
1005
1006 return SqlServerTcpPort;
1007 }
1008 }
1009
1010 #if NET_2_0
1011 struct ColumnInfo
1012 {
1013 public string name;
1014 public Type type;
1015
1016 public ColumnInfo (string name, Type type)
1017 {
1018 this.name = name; this.type = type;
1019 }
1020 }
1021
1022 static class ReservedWords
1023 {
1024 static readonly string [] reservedWords =
1025 {
1026 "ADD", "EXCEPT", "PERCENT", "ALL", "EXEC", "PLAN", "ALTER",
1027 "EXECUTE", "PRECISION", "AND", "EXISTS", "PRIMARY", "ANY",
1028 "EXIT", "PRINT", "AS", "FETCH", "PROC", "ASC", "FILE",
1029 "PROCEDURE", "AUTHORIZATION", "FILLFACTOR", "PUBLIC",
1030 "BACKUP", "FOR", "RAISERROR", "BEGIN", "FOREIGN", "READ",
1031 "BETWEEN", "FREETEXT", "READTEXT", "BREAK", "FREETEXTTABLE",
1032 "RECONFIGURE", "BROWSE", "FROM", "REFERENCES", "BULK",
1033 "FULL", "REPLICATION", "BY", "FUNCTION", "RESTORE",
1034 "CASCADE", "GOTO", "RESTRICT", "CASE", "GRANT", "RETURN",
1035 "CHECK", "GROUP", "REVOKE", "CHECKPOINT", "HAVING", "RIGHT",
1036 "CLOSE", "HOLDLOCK", "ROLLBACK", "CLUSTERED", "IDENTITY",
1037 "ROWCOUNT", "COALESCE", "IDENTITY_INSERT", "ROWGUIDCOL",
1038 "COLLATE", "IDENTITYCOL", "RULE", "COLUMN", "IF", "SAVE",
1039 "COMMIT", "IN", "SCHEMA", "COMPUTE", "INDEX", "SELECT",
1040 "CONSTRAINT", "INNER", "SESSION_USER", "CONTAINS", "INSERT",
1041 "SET", "CONTAINSTABLE", "INTERSECT", "SETUSER", "CONTINUE",
1042 "INTO", "SHUTDOWN", "CONVERT", "IS", "SOME", "CREATE",
1043 "JOIN", "STATISTICS", "CROSS", "KEY", "SYSTEM_USER",
1044 "CURRENT", "KILL", "TABLE", "CURRENT_DATE", "LEFT",
1045 "TEXTSIZE", "CURRENT_TIME", "LIKE", "THEN",
1046 "CURRENT_TIMESTAMP", "LINENO", "TO", "CURRENT_USER", "LOAD",
1047 "TOP", "CURSOR", "NATIONAL", "TRAN", "DATABASE", "NOCHECK",
1048 "TRANSACTION", "DBCC", "NONCLUSTERED", "TRIGGER",
1049 "DEALLOCATE", "NOT", "TRUNCATE", "DECLARE", "NULL",
1050 "TSEQUAL", "DEFAULT", "NULLIF", "UNION", "DELETE", "OF",
1051 "UNIQUE", "DENY", "OFF", "UPDATE", "DESC", "OFFSETS",
1052 "UPDATETEXT", "DISK", "ON", "USE", "DISTINCT", "OPEN",
1053 "USER", "DISTRIBUTED", "OPENDATASOURCE", "VALUES", "DOUBLE",
1054 "OPENQUERY", "VARYING", "DROP", "OPENROWSET", "VIEW",
1055 "DUMMY", "OPENXML", "WAITFOR", "DUMP", "OPTION", "WHEN",
1056 "ELSE", "OR", "WHERE", "END", "ORDER", "WHILE", "ERRLVL",
1057 "OUTER", "WITH", "ESCAPE", "OVER", "WRITETEXT", "ABSOLUTE",
1058 "FOUND", "PRESERVE", "ACTION", "FREE", "PRIOR", "ADMIN",
1059 "GENERAL", "PRIVILEGES", "AFTER", "GET", "READS",
1060 "AGGREGATE", "GLOBAL", "REAL", "ALIAS", "GO", "RECURSIVE",
1061 "ALLOCATE", "GROUPING", "REF", "ARE", "HOST", "REFERENCING",
1062 "ARRAY", "HOUR", "RELATIVE", "ASSERTION", "IGNORE", "RESULT",
1063 "AT", "IMMEDIATE", "RETURNS", "BEFORE", "INDICATOR", "ROLE",
1064 "BINARY", "INITIALIZE", "ROLLUP", "BIT", "INITIALLY",
1065 "ROUTINE", "BLOB", "INOUT", "ROW", "BOOLEAN", "INPUT",
1066 "ROWS", "BOTH", "INT", "SAVEPOINT", "BREADTH", "INTEGER",
1067 "SCROLL", "CALL", "INTERVAL", "SCOPE", "CASCADED",
1068 "ISOLATION", "SEARCH", "CAST", "ITERATE", "SECOND",
1069 "CATALOG", "LANGUAGE", "SECTION", "CHAR", "LARGE",
1070 "SEQUENCE", "CHARACTER", "LAST", "SESSION", "CLASS",
1071 "LATERAL", "SETS", "CLOB", "LEADING", "SIZE", "COLLATION",
1072 "LESS", "SMALLINT", "COMPLETION", "LEVEL", "SPACE",
1073 "CONNECT", "LIMIT", "SPECIFIC", "CONNECTION", "LOCAL",
1074 "SPECIFICTYPE", "CONSTRAINTS", "LOCALTIME", "SQL",
1075 "CONSTRUCTOR", "LOCALTIMESTAMP", "SQLEXCEPTION",
1076 "CORRESPONDING", "LOCATOR", "SQLSTATE", "CUBE", "MAP",
1077 "SQLWARNING", "CURRENT_PATH", "MATCH", "START",
1078 "CURRENT_ROLE", "MINUTE", "STATE", "CYCLE", "MODIFIES",
1079 "STATEMENT", "DATA", "MODIFY", "STATIC", "DATE", "MODULE",
1080 "STRUCTURE", "DAY", "MONTH", "TEMPORARY", "DEC", "NAMES",
1081 "TERMINATE", "DECIMAL", "NATURAL", "THAN", "DEFERRABLE",
1082 "NCHAR", "TIME", "DEFERRED", "NCLOB", "TIMESTAMP", "DEPTH",
1083 "NEW", "TIMEZONE_HOUR", "DEREF", "NEXT", "TIMEZONE_MINUTE",
1084 "DESCRIBE", "NO", "TRAILING", "DESCRIPTOR", "NONE",
1085 "TRANSLATION", "DESTROY", "NUMERIC", "TREAT", "DESTRUCTOR",
1086 "OBJECT", "TRUE", "DETERMINISTIC", "OLD", "UNDER",
1087 "DICTIONARY", "ONLY", "UNKNOWN", "DIAGNOSTICS", "OPERATION",
1088 "UNNEST", "DISCONNECT", "ORDINALITY", "USAGE", "DOMAIN",
1089 "OUT", "USING", "DYNAMIC", "OUTPUT", "VALUE", "EACH",
1090 "PAD", "VARCHAR", "END-EXEC", "PARAMETER", "VARIABLE",
1091 "EQUALS", "PARAMETERS", "WHENEVER", "EVERY", "PARTIAL",
1092 "WITHOUT", "EXCEPTION", "PATH", "WORK", "EXTERNAL",
1093 "POSTFIX", "WRITE", "FALSE", "PREFIX", "YEAR", "FIRST",
1094 "PREORDER", "ZONE", "FLOAT", "PREPARE", "ADA", "AVG",
1095 "BIT_LENGTH", "CHAR_LENGTH", "CHARACTER_LENGTH", "COUNT",
1096 "EXTRACT", "FORTRAN", "INCLUDE", "INSENSITIVE", "LOWER",
1097 "MAX", "MIN", "OCTET_LENGTH", "OVERLAPS", "PASCAL",
1098 "POSITION", "SQLCA", "SQLCODE", "SQLERROR", "SUBSTRING",
1099 "SUM", "TRANSLATE", "TRIM", "UPPER"
1100 };
1101 static DataTable instance;
1102 static public DataTable Instance {
1103 get {
1104 if (instance == null) {
1105 DataRow row = null;
1106 instance = new DataTable ("ReservedWords");
1107 instance.Columns.Add ("ReservedWord", typeof(string));
1108 foreach (string reservedWord in reservedWords)
1109 {
1110 row = instance.NewRow();
1111
1112 row["ReservedWord"] = reservedWord;
1113 instance.Rows.Add(row);
1114 }
1115 }
1116 return instance;
1117 }
1118 }
1119 }
1120
1121 static class MetaDataCollections
1122 {
1123 static readonly ColumnInfo [] columns = {
1124 new ColumnInfo ("CollectionName", typeof (string)),
1125 new ColumnInfo ("NumberOfRestrictions", typeof (int)),
1126 new ColumnInfo ("NumberOfIdentifierParts", typeof (int))
1127 };
1128
1129 static readonly object [][] rows = {
1130 new object [] {"MetaDataCollections", 0, 0},
1131 new object [] {"DataSourceInformation", 0, 0},
1132 new object [] {"DataTypes", 0, 0},
1133 new object [] {"Restrictions", 0, 0},
1134 new object [] {"ReservedWords", 0, 0},
1135 new object [] {"Users", 1, 1},
1136 new object [] {"Databases", 1, 1},
1137 new object [] {"Tables", 4, 3},
1138 new object [] {"Columns", 4, 4},
1139 new object [] {"StructuredTypeMembers", 4, 4},
1140 new object [] {"Views", 3, 3},
1141 new object [] {"ViewColumns", 4, 4},
1142 new object [] {"ProcedureParameters", 4, 1},
1143 new object [] {"Procedures", 4, 3},
1144 new object [] {"ForeignKeys", 4, 3},
1145 new object [] {"IndexColumns", 5, 4},
1146 new object [] {"Indexes", 4, 3},
1147 new object [] {"UserDefinedTypes", 2, 1}
1148 };
1149
1150 static DataTable instance;
1151 static public DataTable Instance {
1152 get {
1153 if (instance == null) {
1154 instance = new DataTable ("MetaDataCollections");
1155 foreach (ColumnInfo c in columns)
1156 instance.Columns.Add (c.name, c.type);
1157 foreach (object [] row in rows)
1158 instance.LoadDataRow (row, true);
1159 }
1160 return instance;
1161 }
1162 }
1163 }
1164
1165 static class DataSourceInformation
1166 {
1167 static readonly ColumnInfo [] columns = {
1168 new ColumnInfo ("CompositeIdentifierSeparatorPattern", typeof (string)),
1169 new ColumnInfo ("DataSourceProductName", typeof(string)),
1170 new ColumnInfo ("DataSourceProductVersion", typeof(string)),
1171 new ColumnInfo ("DataSourceProductVersionNormalized", typeof(string)),
1172 new ColumnInfo ("GroupByBehavior", typeof(GroupByBehavior)),
1173 new ColumnInfo ("IdentifierPattern", typeof(string)),
1174 new ColumnInfo ("IdentifierCase", typeof(IdentifierCase)),
1175 new ColumnInfo ("OrderByColumnsInSelect", typeof(bool)),
1176 new ColumnInfo ("ParameterMarkerFormat", typeof(string)),
1177 new ColumnInfo ("ParameterMarkerPattern", typeof(string)),
1178 new ColumnInfo ("ParameterNameMaxLength", typeof(int)),
1179 new ColumnInfo ("ParameterNamePattern", typeof(string)),
1180 new ColumnInfo ("QuotedIdentifierPattern", typeof(string)),
1181 new ColumnInfo ("QuotedIdentifierCase", typeof(IdentifierCase)),
1182 new ColumnInfo ("StatementSeparatorPattern", typeof(string)),
1183 new ColumnInfo ("StringLiteralPattern", typeof(string)),
1184 new ColumnInfo ("SupportedJoinOperators", typeof(SupportedJoinOperators))
1185 };
1186
1187 static public DataTable GetInstance (SqlConnection conn)
1188 {
1189 DataTable table = new DataTable ("DataSourceInformation");
1190 foreach (ColumnInfo c in columns)
1191 table.Columns.Add (c.name, c.type);
1192 DataRow row = table.NewRow ();
1193 row [0] = "\\.";
1194 row [1] = "Microsoft SQL Server";
1195 row [2] = conn.ServerVersion;;
1196 row [3] = conn.ServerVersion;;
1197 row [4] = GroupByBehavior.Unrelated;
1198 row [5] = @"(^\[\p{Lo}\p{Lu}\p{Ll}_@#][\p{Lo}\p{Lu}\p{Ll}\p{Nd}@$#_]*$)|(^\[[^\]\0]|\]\]+\]$)|(^\""[^\""\0]|\""\""+\""$)";
1199 row [6] = IdentifierCase.Insensitive; // FIXME: obtain this from SQL Server
1200 row [7] = false;
1201 row [8] = "{0}";
1202 row [9] = @"@[\p{Lo}\p{Lu}\p{Ll}\p{Lm}_@#][\p{Lo}\p{Lu}\p{Ll}\p{Lm}\p{Nd}\uff3f_@#\$]*(?=\s+|$)";
1203 row [10] = 128;
1204 row [11] = @"^[\p{Lo}\p{Lu}\p{Ll}\p{Lm}_@#][\p{Lo}\p{Lu}\p{Ll}\p{Lm}\p{Nd}\uff3f_@#\$]*(?=\s+|$)";
1205 row [12] = @"(([^\[]|\]\])*)";
1206 row [13] = IdentifierCase.Insensitive; // FIXME: obtain this from SQL Server
1207 row [14] =";";
1208 row [15] = "'(([^']|'')*)'";
1209 row [16] = (SupportedJoinOperators.FullOuter | SupportedJoinOperators.Inner |
1210 SupportedJoinOperators.LeftOuter | SupportedJoinOperators.RightOuter);
1211 table.Rows.Add (row);
1212 return table;
1213 }
1214 }
1215
1216 static class DataTypes
1217 {
1218 static readonly ColumnInfo [] columns = {
1219 new ColumnInfo ("TypeName", typeof(string)),
1220 new ColumnInfo ("ProviderDbType", typeof(int)),
1221 new ColumnInfo ("ColumnSize", typeof(long)),
1222 new ColumnInfo ("CreateFormat", typeof(string)),
1223 new ColumnInfo ("CreateParameters", typeof(string)),
1224 new ColumnInfo ("DataType", typeof(string)),
1225 new ColumnInfo ("IsAutoIncrementable", typeof(bool)),
1226 new ColumnInfo ("IsBestMatch", typeof(bool)),
1227 new ColumnInfo ("IsCaseSensitive", typeof(bool)),
1228 new ColumnInfo ("IsFixedLength", typeof(bool)),
1229 new ColumnInfo ("IsFixedPrecisionScale", typeof(bool)),
1230 new ColumnInfo ("IsLong", typeof(bool)),
1231 new ColumnInfo ("IsNullable", typeof(bool)),
1232 new ColumnInfo ("IsSearchable", typeof(bool)),
1233 new ColumnInfo ("IsSearchableWithLike", typeof(bool)),
1234 new ColumnInfo ("IsUnsigned", typeof(bool)),
1235 new ColumnInfo ("MaximumScale", typeof(short)),
1236 new ColumnInfo ("MinimumScale", typeof(short)),
1237 new ColumnInfo ("IsConcurrencyType", typeof(bool)),
1238 new ColumnInfo ("IsLiteralSupported", typeof(bool)),
1239 new ColumnInfo ("LiteralPrefix", typeof(string)),
1240 new ColumnInfo ("LiteralSuffix", typeof(string))
1241 };
1242
1243 static readonly object [][] rows = {
1244 new object [] {"smallint", 16, 5, "smallint", null, "System.Int16", true, true,
1245 false, true, true, false, true, true, false, false, null,
1246 null, false, null, null, null},
1247 new object [] {"int", 8, 10, "int", null, "System.Int32",
1248 true, true, false, true, true, false, true, true, false,
1249 false, null, null, false, null, null, null},
1250 new object [] {"real", 13, 7, "real", null,
1251 "System.Single", false, true, false, true, false, false,
1252 true, true, false, false, null, null, false, null, null, null},
1253 new object [] {"float", 6, 53, "float({0})",
1254 "number of bits used to store the mantissa", "System.Double",
1255 false, true, false, true, false, false, true, true,
1256 false, false, null, null, false, null, null, null},
1257 new object [] {"money", 9, 19, "money", null,
1258 "System.Decimal", false, false, false, true, true,
1259 false, true, true, false, false, null, null, false,
1260 null, null, null},
1261 new object [] {"smallmoney", 17, 10, "smallmoney", null,
1262 "System.Decimal", false, false, false, true, true, false,
1263 true, true, false, false, null, null, false, null, null, null},
1264 new object [] {"bit", 2, 1, "bit", null, "System.Boolean",
1265 false, false, false, true, false, false, true, true,
1266 false, null, null, null, false, null, null, null},
1267 new object [] {"tinyint", 20, 3, "tinyint", null,
1268 "System.SByte", true, true, false, true, true, false,
1269 true, true, false, true, null, null, false, null, null, null},
1270 new object [] {"bigint", 0, 19, "bigint", null,
1271 "System.Int64", true, true, false, true, true, false,
1272 true, true, false, false, null, null, false, null, null, null},
1273 new object [] {"timestamp", 19, 8, "timestamp", null,
1274 "System.Byte[]", false, false, false, true, false, false,
1275 false, true, false, null, null, null, true, null, "0x", null},
1276 new object [] {"binary", 1, 8000, "binary({0})", "length",
1277 "System.Byte[]", false, true, false, true, false, false,
1278 true, true, false, null, null, null, false, null, "0x", null},
1279 new object [] {"image", 7, 2147483647, "image", null,
1280 "System.Byte[]", false, true, false, false, false, true,
1281 true, false, false, null, null, null, false, null, "0x", null},
1282 new object [] {"text", 18, 2147483647, "text", null,
1283 "System.String", false, true, false, false, false, true,
1284 true, false, true, null, null, null, false, null, "'", "'"},
1285 new object [] {"ntext", 11, 1073741823, "ntext", null,
1286 "System.String", false, true, false, false, false, true,
1287 true, false, true, null, null, null, false, null, "N'", "'"},
1288 new object [] {"decimal", 5, 38, "decimal({0}, {1})",
1289 "precision,scale", "System.Decimal", true, true, false,
1290 true, false, false, true, true, false, false, 38, 0,
1291 false, null, null, null},
1292 new object [] {"numeric", 5, 38, "numeric({0}, {1})",
1293 "precision,scale", "System.Decimal", true, true, false,
1294 true, false, false, true, true, false, false, 38, 0,
1295 false, null, null, null},
1296 new object [] {"datetime", 4, 23, "datetime", null,
1297 "System.DateTime", false, true, false, true, false, false,
1298 true, true, true, null, null, null, false, null, "{ts '", "'}"},
1299 new object [] {"smalldatetime", 15, 16, "smalldatetime", null,
1300 "System.DateTime", false, true, false, true, false, false,
1301 true, true, true, null, null, null, false, null, "{ts '", "'}"},
1302 new object [] {"sql_variant", 23, null, "sql_variant",
1303 null, "System.Object", false, true, false, false, false,
1304 false, true, true, false, null, null, null, false, false,
1305 null, null},
1306 new object [] {"xml", 25, 2147483647, "xml", null,
1307 "System.String", false, false, false, false, false, true,
1308 true, false, false, null, null, null, false, false, null, null},
1309 new object [] {"varchar", 22, 2147483647, "varchar({0})",
1310 "max length", "System.String", false, true, false, false,
1311 false, false, true, true, true, null, null, null, false,
1312 null, "'", "'"},
1313 new object [] {"char", 3, 2147483647, "char({0})", "length",
1314 "System.String", false, true, false, true, false, false,
1315 true, true, true, null, null, null, false, null, "'", "'"},
1316 new object [] {"nchar", 10, 1073741823, "nchar({0})", "length",
1317 "System.String", false, true, false, true, false, false,
1318 true, true, true, null, null, null, false, null, "N'", "'"},
1319 new object [] {"nvarchar", 12, 1073741823, "nvarchar({0})", "max length",
1320 "System.String", false, true, false, false, false, false, true, true,
1321 true, null, null, null, false, null, "N'", "'"},
1322 new object [] {"varbinary", 21, 1073741823, "varbinary({0})",
1323 "max length", "System.Byte[]", false, true, false, false,
1324 false, false, true, true, false, null, null, null, false,
1325 null, "0x", null},
1326 new object [] {"uniqueidentifier", 14, 16, "uniqueidentifier", null,
1327 "System.Guid", false, true, false, true, false, false, true,
1328 true, false, null, null, null, false, null, "'", "'"},
1329 new object [] {"date", 31, 3L, "date", DBNull.Value,
1330 "System.DateTime", false, false, false, true, true, false,
1331 true, true, true, DBNull.Value, DBNull.Value, DBNull.Value,
1332 false, DBNull.Value, "{ts '", "'}"},
1333 new object [] {"time", 32, 5L, "time({0})", "scale",
1334 "System.TimeSpan", false, false, false, false, false, false,
1335 true, true, true, DBNull.Value, (short) 7, (short) 0,
1336 false, DBNull.Value, "{ts '", "'}"},
1337 new object [] {"datetime2", 33, 8L, "datetime2({0})", "scale",
1338 "System.DateTime", false, true, false, false, false, false,
1339 true, true, true, DBNull.Value, (short) 7, (short) 0,
1340 false, DBNull.Value, "{ts '", "'}"},
1341 new object [] {"datetimeoffset", 34, 10L, "datetimeoffset({0})",
1342 "scale", "System.DateTimeOffset", false, true, false, false,
1343 false, false, true, true, true, DBNull.Value, (short) 7, (short) 0,
1344 false, DBNull.Value, "{ts '", "'}"}
1345 };
1346
1347 static DataTable instance;
1348 static public DataTable Instance {
1349 get {
1350 if (instance == null) {
1351 instance = new DataTable ("DataTypes");
1352 foreach (ColumnInfo c in columns)
1353 instance.Columns.Add (c.name, c.type);
1354 foreach (object [] row in rows)
1355 instance.LoadDataRow (row, true);
1356 }
1357 return instance;
1358 }
1359 }
1360 }
1361
1362 static class Restrictions
1363 {
1364 static readonly ColumnInfo [] columns = {
1365 new ColumnInfo ("CollectionName", typeof (string)),
1366 new ColumnInfo ("RestrictionName", typeof(string)),
1367 new ColumnInfo ("ParameterName", typeof(string)),
1368 new ColumnInfo ("RestrictionDefault", typeof(string)),
1369 new ColumnInfo ("RestrictionNumber", typeof(int))
1370 };
1371
1372 static readonly object [][] rows = {
1373 new object [] {"Users", "User_Name", "@Name", "name", 1},
1374 new object [] {"Databases", "Name", "@Name", "Name", 1},
1375
1376 new object [] {"Tables", "Catalog", "@Catalog", "TABLE_CATALOG", 1},
1377 new object [] {"Tables", "Owner", "@Owner", "TABLE_SCHEMA", 2},
1378 new object [] {"Tables", "Table", "@Name", "TABLE_NAME", 3},
1379 new object [] {"Tables", "TableType", "@TableType", "TABLE_TYPE", 4},
1380
1381 new object [] {"Columns", "Catalog", "@Catalog", "TABLE_CATALOG", 1},
1382 new object [] {"Columns", "Owner", "@Owner", "TABLE_SCHEMA", 2},
1383 new object [] {"Columns", "Table", "@Table", "TABLE_NAME", 3},
1384 new object [] {"Columns", "Column", "@Column", "COLUMN_NAME", 4},
1385
1386 new object [] {"StructuredTypeMembers", "Catalog", "@Catalog", "TYPE_CATALOG", 1},
1387 new object [] {"StructuredTypeMembers", "Owner", "@Owner", "TYPE_SCHEMA", 2},
1388 new object [] {"StructuredTypeMembers", "Type", "@Type", "TYPE_NAME", 3},
1389 new object [] {"StructuredTypeMembers", "Member", "@Member", "MEMBER_NAME", 4},
1390
1391 new object [] {"Views", "Catalog", "@Catalog", "TABLE_CATALOG", 1},
1392 new object [] {"Views", "Owner", "@Owner", "TABLE_SCHEMA", 2},
1393 new object [] {"Views", "Table", "@Table", "TABLE_NAME", 3},
1394
1395 new object [] {"ViewColumns", "Catalog", "@Catalog", "VIEW_CATALOG", 1},
1396 new object [] {"ViewColumns", "Owner", "@Owner", "VIEW_SCHEMA", 2},
1397 new object [] {"ViewColumns", "Table", "@Table", "VIEW_NAME", 3},
1398 new object [] {"ViewColumns", "Column", "@Column", "COLUMN_NAME", 4},
1399
1400 new object [] {"ProcedureParameters", "Catalog", "@Catalog", "SPECIFIC_CATALOG", 1},
1401 new object [] {"ProcedureParameters", "Owner", "@Owner", "SPECIFIC_SCHEMA", 2},
1402 new object [] {"ProcedureParameters", "Name", "@Name", "SPECIFIC_NAME", 3},
1403 new object [] {"ProcedureParameters", "Parameter", "@Parameter", "PARAMETER_NAME", 4},
1404
1405 new object [] {"Procedures", "Catalog", "@Catalog", "SPECIFIC_CATALOG", 1},
1406 new object [] {"Procedures", "Owner", "@Owner", "SPECIFIC_SCHEMA", 2},
1407 new object [] {"Procedures", "Name", "@Name", "SPECIFIC_NAME", 3},
1408 new object [] {"Procedures", "Type", "@Type", "ROUTINE_TYPE", 4},
1409
1410 new object [] {"IndexColumns", "Catalog", "@Catalog", "db_name()", 1},
1411 new object [] {"IndexColumns", "Owner", "@Owner", "user_name()", 2},
1412 new object [] {"IndexColumns", "Table", "@Table", "o.name", 3},
1413 new object [] {"IndexColumns", "ConstraintName", "@ConstraintName", "x.name", 4},
1414 new object [] {"IndexColumns", "Column", "@Column", "c.name", 5},
1415
1416 new object [] {"Indexes", "Catalog", "@Catalog", "db_name()", 1},
1417 new object [] {"Indexes", "Owner", "@Owner", "user_name()", 2},
1418 new object [] {"Indexes", "Table", "@Table", "o.name", 3},
1419 new object [] {"Indexes", "Name", "@Name", "x.name", 4},
1420
1421 new object [] {"UserDefinedTypes", "assembly_name", "@AssemblyName", "assemblies.name", 1},
1422 new object [] {"UserDefinedTypes", "udt_name", "@UDTName", "types.assembly_class", 2},
1423
1424 new object [] {"ForeignKeys", "Catalog", "@Catalog", "CONSTRAINT_CATALOG", 1},
1425 new object [] {"ForeignKeys", "Owner", "@Owner", "CONSTRAINT_SCHEMA", 2},
1426 new object [] {"ForeignKeys", "Table", "@Table", "TABLE_NAME", 3},
1427 new object [] {"ForeignKeys", "Name", "@Name", "CONSTRAINT_NAME", 4}
1428 };
1429
1430 static DataTable instance;
1431 static public DataTable Instance {
1432 get {
1433 if (instance == null) {
1434 instance = new DataTable ("Restrictions");
1435 foreach (ColumnInfo c in columns)
1436 instance.Columns.Add (c.name, c.type);
1437 foreach (object [] row in rows)
1438 instance.LoadDataRow (row, true);
1439 }
1440 return instance;
1441 }
1442 }
1443 }
1444
1445 public override DataTable GetSchema ()
1446 {
1447 if (state == ConnectionState.Closed)
1448 throw ExceptionHelper.ConnectionClosed ();
1449
1450 return MetaDataCollections.Instance;
1451 }
1452
1453 public override DataTable GetSchema (String collectionName)
1454 {
1455 return GetSchema (collectionName, null);
1456 }
1457
1458 public override DataTable GetSchema (String collectionName, string [] restrictionValues)
1459 {
1460 // LAMESPEC: In MS.NET, if collectionName is null, it throws ArgumentException.
1461
1462 if (state == ConnectionState.Closed)
1463 throw ExceptionHelper.ConnectionClosed ();
1464
1465 String cName = null;
1466 DataTable schemaTable = MetaDataCollections.Instance;
1467 int length = restrictionValues == null ? 0 : restrictionValues.Length;
1468
1469 foreach (DataRow row in schemaTable.Rows) {
1470 if (String.Compare ((string) row["CollectionName"], collectionName, true) == 0) {
1471 if (length > (int) row["NumberOfRestrictions"]) {
1472 throw new ArgumentException ("More restrictions were provided " +
1473 "than the requested schema ('" +
1474 row["CollectionName"].ToString () + "') supports");
1475 }
1476 cName = row["CollectionName"].ToString();
1477 }
1478 }
1479
1480 if (cName == null)
1481 throw new ArgumentException (string.Format (CultureInfo.InvariantCulture,
1482 "The requested collection ({0}) is not defined.",
1483 collectionName));
1484
1485 SqlCommand command = null;
1486 DataTable dataTable = new DataTable ();
1487 SqlDataAdapter dataAdapter = new SqlDataAdapter ();
1488
1489 switch (cName)
1490 {
1491 case "Databases":
1492 command = new SqlCommand ("select name as database_name, dbid, crdate as create_date " +
1493 "from master.sys.sysdatabases where (name = @Name or (@Name " +
1494 "is null))", this);
1495 command.Parameters.Add ("@Name", SqlDbType.NVarChar, 4000);
1496 break;
1497 case "ForeignKeys":
1498 command = new SqlCommand ("select CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME, " +
1499 "TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, CONSTRAINT_TYPE, " +
1500 "IS_DEFERRABLE, INITIALLY_DEFERRED from " +
1501 "INFORMATION_SCHEMA.TABLE_CONSTRAINTS where (CONSTRAINT_CATALOG" +
1502 " = @Catalog or (@Catalog is null)) and (CONSTRAINT_SCHEMA = " +
1503 "@Owner or (@Owner is null)) and (TABLE_NAME = @Table or (" +
1504 "@Table is null)) and (CONSTRAINT_NAME = @Name or (@Name is null))" +
1505 " and CONSTRAINT_TYPE = 'FOREIGN KEY' order by CONSTRAINT_CATALOG," +
1506 " CONSTRAINT_SCHEMA, CONSTRAINT_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 ("@Name", SqlDbType.NVarChar, 4000);
1511 break;
1512 case "Indexes":
1513 command = new SqlCommand ("select distinct db_name() as constraint_catalog, " +
1514 "constraint_schema = user_name (o.uid), " +
1515 "constraint_name = x.name, table_catalog = db_name (), " +
1516 "table_schema = user_name (o.uid), table_name = o.name, " +
1517 "index_name = x.name from sysobjects o, sysindexes x, " +
1518 "sysindexkeys xk where o.type in ('U') and x.id = o.id and " +
1519 "o.id = xk.id and x.indid = xk.indid and xk.keyno = x.keycnt " +
1520 "and (db_name() = @Catalog or (@Catalog is null)) and " +
1521 "(user_name() = @Owner or (@Owner is null)) and (o.name = " +
1522 "@Table or (@Table is null)) and (x.name = @Name or (@Name is null))" +
1523 "order by table_name, index_name", this);
1524 command.Parameters.Add ("@Catalog", SqlDbType.NVarChar, 4000);
1525 command.Parameters.Add ("@Owner", SqlDbType.NVarChar, 4000);
1526 command.Parameters.Add ("@Table", SqlDbType.NVarChar, 4000);
1527 command.Parameters.Add ("@Name", SqlDbType.NVarChar, 4000);
1528 break;
1529 case "IndexColumns":
1530 command = new SqlCommand ("select distinct db_name() as constraint_catalog, " +
1531 "constraint_schema = user_name (o.uid), constraint_name = x.name, " +
1532 "table_catalog = db_name (), table_schema = user_name (o.uid), " +
1533 "table_name = o.name, column_name = c.name, " +
1534 "ordinal_position = convert (int, xk.keyno), keyType = c.xtype, " +
1535 "index_name = x.name from sysobjects o, sysindexes x, syscolumns c, " +
1536 "sysindexkeys xk where o.type in ('U') and x.id = o.id and o.id = c.id " +
1537 "and o.id = xk.id and x.indid = xk.indid and c.colid = xk.colid " +
1538 "and xk.keyno <= x.keycnt and permissions (o.id, c.name) <> 0 " +
1539 "and (db_name() = @Catalog or (@Catalog is null)) and (user_name() " +
1540 "= @Owner or (@Owner is null)) and (o.name = @Table or (@Table is" +
1541 " null)) and (x.name = @ConstraintName or (@ConstraintName is null)) " +
1542 "and (c.name = @Column or (@Column is null)) order by table_name, " +
1543 "index_name", this);
1544 command.Parameters.Add ("@Catalog", SqlDbType.NVarChar, 8);
1545 command.Parameters.Add ("@Owner", SqlDbType.NVarChar, 4000);
1546 command.Parameters.Add ("@Table", SqlDbType.NVarChar, 13);
1547 command.Parameters.Add ("@ConstraintName", SqlDbType.NVarChar, 4000);
1548 command.Parameters.Add ("@Column", SqlDbType.NVarChar, 4000);
1549 break;
1550 case "Procedures":
1551 command = new SqlCommand ("select SPECIFIC_CATALOG, SPECIFIC_SCHEMA, SPECIFIC_NAME, " +
1552 "ROUTINE_CATALOG, ROUTINE_SCHEMA, ROUTINE_NAME, ROUTINE_TYPE, " +
1553 "CREATED, LAST_ALTERED from INFORMATION_SCHEMA.ROUTINES where " +
1554 "(SPECIFIC_CATALOG = @Catalog or (@Catalog is null)) and " +
1555 "(SPECIFIC_SCHEMA = @Owner or (@Owner is null)) and (SPECIFIC_NAME" +
1556 " = @Name or (@Name is null)) and (ROUTINE_TYPE = @Type or (@Type " +
1557 "is null)) order by SPECIFIC_CATALOG, SPECIFIC_SCHEMA, SPECIFIC_NAME", this);
1558 command.Parameters.Add ("@Catalog", SqlDbType.NVarChar, 4000);
1559 command.Parameters.Add ("@Owner", SqlDbType.NVarChar, 4000);
1560 command.Parameters.Add ("@Name", SqlDbType.NVarChar, 4000);
1561 command.Parameters.Add ("@Type", SqlDbType.NVarChar, 4000);
1562 break;
1563 case "ProcedureParameters":
1564 command = new SqlCommand ("select SPECIFIC_CATALOG, SPECIFIC_SCHEMA, SPECIFIC_NAME, " +
1565 "ORDINAL_POSITION, PARAMETER_MODE, IS_RESULT, AS_LOCATOR, " +
1566 "PARAMETER_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, " +
1567 "CHARACTER_OCTET_LENGTH, COLLATION_CATALOG, COLLATION_SCHEMA, " +
1568 "COLLATION_NAME, CHARACTER_SET_CATALOG, CHARACTER_SET_SCHEMA, " +
1569 "CHARACTER_SET_NAME, NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX, " +
1570 "NUMERIC_SCALE, DATETIME_PRECISION, INTERVAL_TYPE, " +
1571 "INTERVAL_PRECISION from INFORMATION_SCHEMA.PARAMETERS where " +
1572 "(SPECIFIC_CATALOG = @Catalog or (@Catalog is null)) and " +
1573 "(SPECIFIC_SCHEMA = @Owner or (@Owner is null)) and (SPECIFIC_NAME = " +
1574 "@Name or (@Name is null)) and (PARAMETER_NAME = @Parameter or (" +
1575 "@Parameter is null)) order by SPECIFIC_CATALOG, SPECIFIC_SCHEMA," +
1576 " SPECIFIC_NAME, PARAMETER_NAME", this);
1577 command.Parameters.Add ("@Catalog", SqlDbType.NVarChar, 4000);
1578 command.Parameters.Add ("@Owner", SqlDbType.NVarChar, 4000);
1579 command.Parameters.Add ("@Name", SqlDbType.NVarChar, 4000);
1580 command.Parameters.Add ("@Parameter", SqlDbType.NVarChar, 4000);
1581 break;
1582 case "Tables":
1583 command = new SqlCommand ("select TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, TABLE_TYPE " +
1584 "from INFORMATION_SCHEMA.TABLES where" +
1585 " (TABLE_CATALOG = @catalog or (@catalog is null)) and " +
1586 "(TABLE_SCHEMA = @owner or (@owner is null))and " +
1587 "(TABLE_NAME = @name or (@name is null)) and " +
1588 "(TABLE_TYPE = @table_type or (@table_type is null))", this);
1589 command.Parameters.Add ("@catalog", SqlDbType.NVarChar, 8);
1590 command.Parameters.Add ("@owner", SqlDbType.NVarChar, 3);
1591 command.Parameters.Add ("@name", SqlDbType.NVarChar, 11);
1592 command.Parameters.Add ("@table_type", SqlDbType.NVarChar, 10);
1593 break;
1594 case "Columns":
1595 command = new SqlCommand ("select TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, " +
1596 "ORDINAL_POSITION, COLUMN_DEFAULT, IS_NULLABLE, DATA_TYPE, " +
1597 "CHARACTER_MAXIMUM_LENGTH, CHARACTER_OCTET_LENGTH, " +
1598 "NUMERIC_PRECISION, NUMERIC_PRECISION_RADIX, NUMERIC_SCALE, " +
1599 "DATETIME_PRECISION, CHARACTER_SET_CATALOG, CHARACTER_SET_SCHEMA, " +
1600 "CHARACTER_SET_NAME, COLLATION_CATALOG from INFORMATION_SCHEMA.COLUMNS" +
1601 " where (TABLE_CATALOG = @Catalog or (@Catalog is null)) and (" +
1602 "TABLE_SCHEMA = @Owner or (@Owner is null)) and (TABLE_NAME = @table" +
1603 " or (@Table is null)) and (COLUMN_NAME = @column or (@Column is null" +
1604 ")) order by TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME", this);
1605 command.Parameters.Add ("@Catalog", SqlDbType.NVarChar, 4000);
1606 command.Parameters.Add ("@Owner", SqlDbType.NVarChar, 4000);
1607 command.Parameters.Add ("@Table", SqlDbType.NVarChar, 4000);
1608 command.Parameters.Add ("@Column", SqlDbType.NVarChar, 4000);
1609 break;
1610 case "Users":
1611 command = new SqlCommand ("select uid, name as user_name, createdate, updatedate from sysusers" +
1612 " where (name = @Name or (@Name is null))", this);
1613 command.Parameters.Add ("@Name", SqlDbType.NVarChar, 4000);
1614 break;
1615 case "StructuredTypeMembers":
1616 // Only available on SQL Server 2008
1617 // Running it again SQL 2005 results in the following exception:
1618 // Unable to build the 'StructuredTypeMembers' collection because
1619 // execution of the SQL query failed. See the inner exception for details.
1620 // ---> System.Data.SqlClient.SqlException: Invalid object name 'sys.table_types'.
1621 //
1622 // I don't have access to SQL Server 2008 right now,
1623 // and can't find any online documentation on the 'sys.table_types'
1624 // view
1625 throw new NotImplementedException ();
1626 case "Views":
1627 command = new SqlCommand ("select TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, CHECK_OPTION, " +
1628 "IS_UPDATABLE from INFORMATION_SCHEMA.VIEWS where (TABLE_CATALOG" +
1629 " = @Catalog or (@Catalog is null)) TABLE_SCHEMA = @Owner or " +
1630 "(@Owner is null)) and (TABLE_NAME = @table or (@Table is null))" +
1631 " order by TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME", this);
1632 command.Parameters.Add ("@Catalog", SqlDbType.NVarChar, 4000);
1633 command.Parameters.Add ("@Owner", SqlDbType.NVarChar, 4000);
1634 command.Parameters.Add ("@Table", SqlDbType.NVarChar, 4000);
1635 break;
1636 case "ViewColumns":
1637 command = new SqlCommand ("select VIEW_CATALOG, VIEW_SCHEMA, VIEW_NAME, TABLE_CATALOG, " +
1638 "TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME from " +
1639 "INFORMATION_SCHEMA.VIEW_COLUMN_USAGE where (VIEW_CATALOG = " +
1640 "@Catalog (@Catalog is null)) and (VIEW_SCHEMA = @Owner (@Owner" +
1641 " is null)) and (VIEW_NAME = @Table or (@Table is null)) and " +
1642 "(COLUMN_NAME = @Column or (@Column is null)) order by " +
1643 "VIEW_CATALOG, VIEW_SCHEMA, VIEW_NAME", this);
1644 command.Parameters.Add ("@Catalog", SqlDbType.NVarChar, 4000);
1645 command.Parameters.Add ("@Owner", SqlDbType.NVarChar, 4000);
1646 command.Parameters.Add ("@Table", SqlDbType.NVarChar, 4000);
1647 command.Parameters.Add ("@Column", SqlDbType.NVarChar, 4000);
1648 break;
1649 case "UserDefinedTypes":
1650 command = new SqlCommand ("select assemblies.name as assembly_name, types.assembly_class " +
1651 "as udt_name, ASSEMBLYPROPERTY(assemblies.name, 'VersionMajor') " +
1652 "as version_major, ASSEMBLYPROPERTY(assemblies.name, 'VersionMinor') " +
1653 "as version_minor, ASSEMBLYPROPERTY(assemblies.name, 'VersionBuild') " +
1654 "as version_build, ASSEMBLYPROPERTY(assemblies.name, 'VersionRevision') " +
1655 "as version_revision, ASSEMBLYPROPERTY(assemblies.name, 'CultureInfo') " +
1656 "as culture_info, ASSEMBLYPROPERTY(assemblies.name, 'PublicKey') " +
1657 "as public_key, is_fixed_length, max_length, Create_Date, " +
1658 "Permission_set_desc from sys.assemblies as assemblies join " +
1659 "sys.assembly_types as types on assemblies.assembly_id = types.assembly_id" +
1660 " where (assportemblies.name = @AssemblyName or (@AssemblyName is null)) and " +
1661 "(types.assembly_class = @UDTName or (@UDTName is null))",
1662 this);
1663 command.Parameters.Add ("@AssemblyName", SqlDbType.NVarChar, 4000);
1664 command.Parameters.Add ("@UDTName", SqlDbType.NVarChar, 4000);
1665 break;
1666 case "MetaDataCollections":
1667 return MetaDataCollections.Instance;
1668 case "DataSourceInformation":
1669 return DataSourceInformation.GetInstance (this);
1670 case "DataTypes":
1671 return DataTypes.Instance;
1672 case "ReservedWords":
1673 return ReservedWords.Instance;
1674 case "Restrictions":
1675 return Restrictions.Instance;
1676 }
1677 for (int i = 0; i < length; i++) {
1678 command.Parameters[i].Value = restrictionValues[i];
1679 }
1680 dataAdapter.SelectCommand = command;
1681 dataAdapter.Fill (dataTable);
1682 return dataTable;
1683 }
1684
1685 public static void ChangePassword (string connectionString, string newPassword)
1686 {
1687 if (String.IsNullOrEmpty (connectionString))
1688 throw new ArgumentNullException ("The 'connectionString' cannot be null or empty.");
1689 if (String.IsNullOrEmpty (newPassword))
1690 throw new ArgumentNullException ("The 'newPassword' cannot be null or empty.");
1691 if (newPassword.Length > 128)
1692 throw new ArgumentException ("The length of 'newPassword' cannot exceed 128 characters.");
1693 using (SqlConnection conn = new SqlConnection (connectionString)) {
1694 conn.Open ();
1695 conn.tds.Execute (String.Format ("sp_password '{0}', '{1}', '{2}'",
1696 conn.parms.Password, newPassword, conn.parms.User));
1697 }
1698 }
1699
1700 public static void ClearAllPools ()
1701 {
1702 // FIXME: locking
1703 IDictionary pools = SqlConnection.sqlConnectionPools.GetConnectionPool ();
1704 foreach (TdsConnectionPool pool in pools.Values) {
1705 if (pool != null)
1706 pool.ResetConnectionPool ();
1707 }
1708 pools.Clear ();
1709 }
1710
1711 public static void ClearPool (SqlConnection connection)
1712 {
1713 if (connection == null)
1714 throw new ArgumentNullException ("connection");
1715
1716 // FIXME: locking
1717 if (connection.pooling) {
1718 TdsConnectionPool pool = sqlConnectionPools.GetConnectionPool (connection.ConnectionString);
1719 if (pool != null)
1720 pool.ResetConnectionPool ();
1721 }
1722 }
1723
1724 #endif // NET_2_0
1725
1726 #endregion // Methods
1727
1728 #if NET_2_0
1729 #region Fields Net 2
1730
1731 bool async;
1732 bool userInstance;
1733
1734 #endregion // Fields Net 2
1735
1736 #region Properties Net 2
1737
1738 #if NET_1_0
1739 [DataSysDescription ("Enable Asynchronous processing, 'Asynchrouse Processing=true/false' in the ConnectionString.")]
1740 #endif
1741 [DesignerSerializationVisibility (DesignerSerializationVisibility.Hidden)]
1742 internal bool AsyncProcessing {
1743 get { return async; }
1744 }
1745
1746 #endregion // Properties Net 2
1747
1748 #endif // NET_2_0
1749
1750 }
1751 }
git-svn mirror of mcs