2 // SqlConnectionTest.cs - NUnit Test Cases for testing the
5 // Senganal T (tsenganal@novell.com)
7 // Copyright (c) 2004 Novell Inc., and the individuals listed
8 // on the ChangeLog entries.
10 // Permission is hereby granted, free of charge, to any person obtaining
11 // a copy of this software and associated documentation files (the
12 // "Software"), to deal in the Software without restriction, including
13 // without limitation the rights to use, copy, modify, merge, publish,
14 // distribute, sublicense, and/or sell copies of the Software, and to
15 // permit persons to whom the Software is furnished to do so, subject to
16 // the following conditions:
18 // The above copyright notice and this permission notice shall be
19 // included in all copies or substantial portions of the Software.
21 // THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND,
22 // EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
23 // MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND
24 // NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE
25 // LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION
26 // OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION
27 // WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.
31 using System.Collections;
33 using System.Data.SqlClient;
35 using System.Threading;
37 using NUnit.Framework;
39 namespace MonoTests.System.Data
42 [Category ("sqlserver")]
43 public class SqlConnectionTest
46 String connectionString;
53 events = new ArrayList ();
54 connectionString = ConnectionManager.Singleton.ConnectionString;
55 engine = ConnectionManager.Singleton.Engine;
59 public void TearDown ()
63 SqlConnection.ClearAllPools ();
67 public void OverloadedConstructorTest ()
70 //do i need to check for all the synonyms..
71 conn = new SqlConnection ("Timeout=10;Connect Timeout=20;Connection Timeout=30");
72 Assert.AreEqual (30, conn.ConnectionTimeout, "#A1");
73 conn = new SqlConnection ("Connect Timeout=100;Connection Timeout=200;Timeout=300");
74 Assert.AreEqual (300, conn.ConnectionTimeout, "#A2");
75 conn = new SqlConnection ("Connection Timeout=1000;Timeout=2000;Connect Timeout=3000");
76 Assert.AreEqual (3000, conn.ConnectionTimeout, "#A3");
78 //'==' doesent work correctly in both msdotnet and mono
80 conn = new SqlConnection ("server=local==host;database=tmp;");
81 Assert.AreEqual ("local==host", conn.DataSource,
82 "# Datasource name is set incorrectly");
84 string connStr = "Server='loca\"lhost';Database='''Db'; packet Size=\"512\";"
85 + "connect Timeout=20;Workstation Id=\"'\"\"desktop\";";
86 conn = new SqlConnection (connStr);
87 Assert.AreEqual (connStr , conn.ConnectionString , "#B1");
88 Assert.AreEqual ("loca\"lhost" , conn.DataSource , "#B2");
89 Assert.AreEqual ("'Db" , conn.Database , "#B3");
90 Assert.AreEqual (512 , conn.PacketSize , "#B4");
91 Assert.AreEqual (20 , conn.ConnectionTimeout , "#B5");
92 Assert.AreEqual ("'\"desktop" , conn.WorkstationId , "#B6");
98 conn = new SqlConnection (connectionString);
99 conn.StateChange += new StateChangeEventHandler (Connection_StateChange);
102 Assert.AreEqual (ConnectionState.Open, conn.State, "#1");
103 Assert.AreEqual (1, events.Count, "#2");
104 StateChangeEventArgs args = events [0] as StateChangeEventArgs;
105 Assert.IsNotNull (args, "#3");
106 Assert.AreEqual (ConnectionState.Closed, args.OriginalState, "#4");
107 Assert.AreEqual (ConnectionState.Open, args.CurrentState, "#5");
113 public void Open_Connection_Open ()
115 conn = new SqlConnection (connectionString);
121 } catch (InvalidOperationException ex) {
122 // The connection was not closed. The connection's
123 // current state is open
124 Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#2");
125 Assert.IsNull (ex.InnerException, "#3");
126 Assert.IsNotNull (ex.Message, "#4");
133 public void Open_ConnectionString_LoginInvalid ()
136 conn = new SqlConnection (connectionString + "user id=invalidLogin");
140 } catch (SqlException ex) {
141 // Login failed for user 'invalidLogin'
142 Assert.AreEqual (typeof (SqlException), ex.GetType (), "#2");
143 Assert.AreEqual ((byte) 14, ex.Class, "#3");
144 Assert.IsNull (ex.InnerException, "#4");
145 Assert.IsNotNull (ex.Message, "#5");
146 Assert.IsTrue (ex.Message.IndexOf ("'invalidLogin'") != -1, "#6");
147 Assert.AreEqual (18456, ex.Number, "#7");
148 Assert.AreEqual ((byte) 1, ex.State, "#8");
155 public void Open_ConnectionString_DatabaseInvalid ()
157 conn = new SqlConnection (connectionString + "database=invalidDB");
161 } catch (SqlException ex) {
162 // Cannot open database "invalidDB" requested
163 // by the login. The login failed
164 Assert.AreEqual (typeof (SqlException), ex.GetType (), "#2");
165 Assert.AreEqual ((byte) 11, ex.Class, "#3");
166 Assert.IsNull (ex.InnerException, "#4");
167 Assert.IsNotNull (ex.Message, "#5");
168 Assert.IsTrue (ex.Message.IndexOf ("invalidDB") != -1, "#6: " + ex.Message);
169 Assert.AreEqual (4060, ex.Number, "#7");
170 Assert.AreEqual ((byte) 1, ex.State, "#8");
178 public void Open_ConnectionString_PasswordInvalid ()
181 conn = new SqlConnection (connectionString + ";password=invalidPassword");
185 } catch (SqlException ex) {
186 // Login failed for user '...'
187 Assert.AreEqual (typeof (SqlException), ex.GetType (), "#2");
188 Assert.AreEqual ((byte) 14, ex.Class, "#3");
189 Assert.IsNull (ex.InnerException, "#4");
190 Assert.IsNotNull (ex.Message, "#5");
191 Assert.AreEqual (18456, ex.Number, "#6");
192 Assert.AreEqual ((byte) 1, ex.State, "#7");
199 public void Open_ConnectionString_ServerInvalid ()
201 Assert.Ignore ("Long running");
204 conn = new SqlConnection (connectionString + ";server=invalidServerName");
208 } catch (SqlException ex) {
209 // An error has occurred while establishing a
210 // connection to the server...
211 Assert.AreEqual (typeof (SqlException), ex.GetType (), "#2");
212 Assert.AreEqual ((byte) 20, ex.Class, "#3");
213 Assert.IsNull (ex.InnerException, "#4");
214 Assert.IsNotNull (ex.Message, "#5");
215 Assert.AreEqual (53, ex.Number, "#6");
216 Assert.AreEqual ((byte) 0, ex.State, "#7");
222 [Test] // bug #383061
223 public void Open_MaxPoolSize_Reached ()
225 connectionString += "Pooling=true;Connection Lifetime=6;"
226 + "Connect Timeout=3;Max Pool Size=2";
228 SqlConnection conn1 = new SqlConnection (connectionString);
231 SqlConnection conn2 = new SqlConnection (connectionString);
234 DateTime start = DateTime.Now;
237 using (SqlConnection sqlConnection = new SqlConnection (connectionString)) {
238 sqlConnection.Open ();
241 } catch (InvalidOperationException ex) {
242 // System.InvalidOperationException: Timeout expired.
243 // The timeout period elapsed prior to obtaining a
244 // connection from the pool. This may have occurred
245 // because all pooled connections were in use and max
246 // pool size was reached.
247 Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#A2");
248 Assert.IsNull (ex.InnerException, "#A3");
249 Assert.IsNotNull (ex.Message, "#A4");
252 TimeSpan elapsed = DateTime.Now - start;
254 Assert.IsTrue (elapsed.TotalSeconds >= 3, "#B1:" + elapsed.TotalSeconds);
255 Assert.IsTrue (elapsed.TotalSeconds < 4, "#B2:" + elapsed.TotalSeconds);
259 // as the second connection is closed, we should now be
260 // able to open a new connection (which essentially
261 // uses the pooled connection from conn2)
262 SqlConnection conn3 = new SqlConnection (connectionString);
269 [Test] // bug #412574
272 conn = new SqlConnection (connectionString);
274 conn.StateChange += new StateChangeEventHandler (Connection_StateChange);
277 Assert.AreEqual (ConnectionState.Closed, conn.State, "#1");
278 Assert.AreEqual (1, events.Count, "#2");
279 StateChangeEventArgs args = events [0] as StateChangeEventArgs;
280 Assert.IsNotNull (args, "#3");
281 Assert.AreEqual (ConnectionState.Open, args.OriginalState, "#4");
282 Assert.AreEqual (ConnectionState.Closed, args.CurrentState, "5");
286 Assert.AreEqual (1, events.Count, "#6");
290 public void ChangeDatabase ()
292 conn = new SqlConnection (connectionString);
294 conn.ChangeDatabase ("master");
295 Assert.AreEqual ("master", conn.Database);
299 public void ChangeDatabase_DatabaseName_DoesNotExist ()
301 conn = new SqlConnection (connectionString);
304 String database = conn.Database;
307 conn.ChangeDatabase ("doesnotexist");
309 } catch (SqlException ex) {
310 // Could not locate entry in sysdatabases for
311 // database 'doesnotexist'. No entry found with
312 // that name. Make sure that the name is entered
314 Assert.AreEqual (typeof (SqlException), ex.GetType (), "#2");
315 Assert.AreEqual ((byte) 16, ex.Class, "#3");
316 Assert.IsNull (ex.InnerException, "#4");
317 Assert.IsNotNull (ex.Message, "#5");
318 Assert.IsTrue (ex.Message.IndexOf ("'doesnotexist'") != -1, "#6");
319 Assert.AreEqual (911, ex.Number, "#7");
320 Assert.AreEqual ((byte) 1, ex.State, "#8");
322 Assert.AreEqual (database, conn.Database, "#9");
329 public void ChangeDatabase_DatabaseName_Empty ()
331 conn = new SqlConnection (connectionString);
334 conn.ChangeDatabase (string.Empty);
336 } catch (ArgumentException ex) {
337 // Database cannot be null, the empty string,
338 // or string of only whitespace
339 Assert.AreEqual (typeof (ArgumentException), ex.GetType (), "#2");
340 Assert.IsNull (ex.InnerException, "#3");
341 Assert.IsNotNull (ex.Message, "#4");
342 Assert.IsNull (ex.ParamName);
347 public void ChangeDatabase_DatabaseName_Null ()
349 conn = new SqlConnection (connectionString);
352 conn.ChangeDatabase ((string) null);
354 } catch (ArgumentException ex) {
355 // Database cannot be null, the empty string,
356 // or string of only whitespace
357 Assert.AreEqual (typeof (ArgumentException), ex.GetType (), "#2");
358 Assert.IsNull (ex.InnerException, "#3");
359 Assert.IsNotNull (ex.Message, "#4");
360 Assert.IsNull (ex.ParamName);
364 [Test] // bug #412581
365 public void ChangeDatabase_DatabaseName_Whitespace ()
367 Assert.Ignore ("bug #412581");
369 conn = new SqlConnection (connectionString);
372 conn.ChangeDatabase (" ");
374 } catch (SqlException ex) {
375 // Could not locate entry in sysdatabases for
376 // database ' '. No entry found with that name.
377 // Make sure that the name is entered correctly
378 Assert.AreEqual (typeof (SqlException), ex.GetType (), "#2");
379 Assert.AreEqual ((byte) 16, ex.Class, "#3");
380 Assert.IsNull (ex.InnerException, "#4");
381 Assert.IsNotNull (ex.Message, "#5");
382 Assert.IsTrue (ex.Message.IndexOf ("' '") != -1, "#6");
383 Assert.AreEqual (911, ex.Number, "#7");
384 Assert.AreEqual ((byte) 1, ex.State, "#8");
389 public void ClearAllPools ()
391 SqlConnection conn1 = new SqlConnection (connectionString + ";Pooling=false");
394 int initial_connection_count = GetConnectionCount (conn1);
396 SqlConnection conn2 = new SqlConnection (connectionString + ";App=A");
400 SqlConnection conn3 = new SqlConnection (connectionString + ";App=B");
404 Assert.AreEqual (initial_connection_count + 2, GetConnectionCount (conn1), "#1");
406 SqlConnection.ClearAllPools ();
408 Assert.AreEqual (initial_connection_count, GetConnectionCount (conn1), "#2");
412 [Test] // bug #443131
413 public void ClearPool ()
415 SqlConnection conn1 = new SqlConnection (connectionString);
418 int initial_connection_count = GetConnectionCount (conn1);
420 SqlConnection conn2 = new SqlConnection (connectionString);
423 SqlConnection conn3 = new SqlConnection (connectionString);
427 Assert.AreEqual (initial_connection_count + 2, GetConnectionCount (conn1), "#1");
429 SqlConnection.ClearPool (conn1);
431 // check if pooled connections that were not in use are
433 Assert.AreEqual (initial_connection_count + 1, GetConnectionCount (conn1), "#2");
437 // check if connections that were in use when the pool
438 // was cleared will not be returned to the pool when
439 // closed (and are closed instead)
440 Assert.AreEqual (initial_connection_count, GetConnectionCount (conn1), "#3");
442 SqlConnection conn4 = new SqlConnection (connectionString);
445 SqlConnection conn5 = new SqlConnection (connectionString);
448 SqlConnection conn6 = new SqlConnection (connectionString);
451 Assert.AreEqual (initial_connection_count + 3, GetConnectionCount (conn1), "#4");
456 // check if new connections are stored in the pool again
457 Assert.AreEqual (initial_connection_count + 3, GetConnectionCount (conn1), "#5");
461 Assert.AreEqual (initial_connection_count + 2, GetConnectionCount (conn4), "#6");
463 SqlConnection.ClearPool (conn3);
465 // the connection passed to ClearPool does not have to
467 Assert.AreEqual (initial_connection_count, GetConnectionCount (conn4), "#7");
469 SqlConnection conn7 = new SqlConnection (connectionString);
473 Assert.AreEqual (initial_connection_count + 1, GetConnectionCount (conn4), "#8");
475 conn3.ConnectionString += ";App=B";
476 SqlConnection.ClearPool (conn3);
478 // check if a pool is identified by its connection string
479 Assert.AreEqual (initial_connection_count + 1, GetConnectionCount (conn4), "#9");
481 SqlConnection conn8 = new SqlConnection (connectionString);
482 SqlConnection.ClearPool (conn8);
484 // connection should not have been opened before to
485 // clear the corresponding pool
486 Assert.AreEqual (initial_connection_count, GetConnectionCount (conn4), "#10");
488 SqlConnection conn9 = new SqlConnection (connectionString);
492 Assert.AreEqual (initial_connection_count + 1, GetConnectionCount (conn4), "#11");
494 conn3.ConnectionString = connectionString;
495 SqlConnection.ClearPool (conn3);
497 Assert.AreEqual (initial_connection_count, GetConnectionCount (conn4), "#12");
499 SqlConnection conn10 = new SqlConnection (connectionString);
502 SqlConnection conn11 = new SqlConnection (connectionString + ";App=B");
505 SqlConnection conn12 = new SqlConnection (connectionString + ";App=B");
508 SqlConnection conn13 = new SqlConnection (connectionString + ";App=B");
516 Assert.AreEqual (initial_connection_count + 4, GetConnectionCount (conn4), "#13");
518 // check that other connection pools are not affected
519 SqlConnection.ClearPool (conn13);
521 Assert.AreEqual (initial_connection_count + 1, GetConnectionCount (conn4), "#14");
523 SqlConnection conn14 = new SqlConnection (connectionString);
527 // a disposed connection cannot be used to clear a pool
528 SqlConnection.ClearPool (conn14);
530 Assert.AreEqual (initial_connection_count + 1, GetConnectionCount (conn4), "#15");
532 SqlConnection.ClearPool (conn4);
534 Assert.AreEqual (initial_connection_count, GetConnectionCount (conn4), "#16");
540 public void InterfaceTransactionTest ()
542 conn = new SqlConnection (connectionString);
544 IDbCommand idbCommand = new SqlCommand ("use [mono-test]", conn);
545 idbCommand.Connection = null;
546 Assert.AreEqual (null, idbCommand.Connection, "Connection should be null");
547 idbCommand.Transaction = null;
548 Assert.AreEqual (null, idbCommand.Transaction, "Transaction should be null");
554 public void BeginTransaction ()
556 conn = new SqlConnection (connectionString);
559 SqlTransaction trans = conn.BeginTransaction ();
560 Assert.AreSame (conn, trans.Connection, "#A1");
561 Assert.AreEqual (IsolationLevel.ReadCommitted, trans.IsolationLevel, "#A2");
564 trans = conn.BeginTransaction ();
567 conn.BeginTransaction ();
569 } catch (InvalidOperationException ex) {
570 // SqlConnection does not support parallel transactions
571 Assert.AreEqual (typeof(InvalidOperationException), ex.GetType(), "#B2");
572 Assert.IsNull (ex.InnerException, "#B3");
573 Assert.IsNotNull (ex.Message, "#B4");
579 trans = conn.BeginTransaction ();
581 trans = conn.BeginTransaction ();
583 trans = conn.BeginTransaction ();
590 public void ConnectionString ()
592 conn = new SqlConnection (connectionString);
593 // Test Repeated Keyoword should take the latest value
594 conn.ConnectionString = conn.ConnectionString + ";server=RepeatedServer;";
595 Assert.AreEqual ("RepeatedServer", ((SqlConnection)conn).DataSource, "#A1");
596 conn.ConnectionString += ";database=gen;Initial Catalog=gen1";
597 Assert.AreEqual ("gen1", conn.Database, "#A2");
599 // Test if properties are set correctly
600 string str = "server=localhost1;database=db;user id=user;";
601 str += "password=pwd;Workstation ID=workstation;Packet Size=512;";
602 str += "Connect Timeout=10";
603 conn.ConnectionString = str;
605 Assert.AreEqual ("localhost1", conn.DataSource, "#B1");
606 Assert.AreEqual ("db", conn.Database, "#B2");
607 Assert.AreEqual (ConnectionState.Closed, conn.State, "#B3");
608 Assert.AreEqual ("workstation", conn.WorkstationId, "#B4");
609 Assert.AreEqual (512, conn.PacketSize, "#B5");
610 Assert.AreEqual (10, conn.ConnectionTimeout, "#B6");
612 // Test if any leftover values exist from previous invocation
613 conn.ConnectionString = connectionString;
614 conn.ConnectionString = string.Empty;
615 Assert.AreEqual (string.Empty, conn.DataSource, "#C1");
616 Assert.AreEqual ("", conn.Database, "#C2");
617 Assert.AreEqual (8000, conn.PacketSize, "#C3");
618 Assert.AreEqual (15, conn.ConnectionTimeout, "#C4");
619 Assert.IsTrue (string.Compare (conn.WorkstationId, Environment.MachineName, true) == 0, "#C5");
623 public void ConnectionString_Connection_Open ()
625 conn = new SqlConnection (connectionString);
626 conn.ConnectionString = connectionString;
629 conn.ConnectionString = "server=localhost;database=tmp;";
631 } catch (InvalidOperationException ex) {
632 // Not allowed to change the 'ConnectionString'
633 // property. The connection's current state is open
634 Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#2");
635 Assert.IsNull (ex.InnerException, "#3");
636 Assert.IsNotNull (ex.Message, "#4");
643 public void ServerVersionTest ()
645 conn = new SqlConnection (connectionString);
647 // Test InvalidOperation Exception is thrown if Connection is CLOSED
649 string s = conn.ServerVersion;
650 Assert.Fail ("#A1:" + s);
651 } catch (InvalidOperationException ex) {
652 Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#A2");
653 Assert.IsNull (ex.InnerException, "#A3");
654 Assert.IsNotNull (ex.Message, "#A4");
657 // Test if Release Version is as per specification.
659 String [] version = conn.ServerVersion.Split ('.');
660 Assert.AreEqual (2, version[0].Length,
661 "#B1 The Major release shud be exactly 2 characters");
662 Assert.AreEqual (2, version[1].Length,
663 "#B2 The Minor release shud be exactly 2 characters");
664 Assert.AreEqual (4, version[2].Length,
665 "#B3 The Release version should be exactly 4 digits");
669 public void Database ()
671 conn = new SqlConnection (connectionString);
672 string database = conn.Database;
676 // Test if database property is updated when a query changes database
678 cmd = new SqlCommand ("use [master]" , conn);
679 cmd.ExecuteNonQuery ();
680 Assert.AreEqual ("master", conn.Database, "#1");
682 // ensure we're really in the expected database
683 if (ClientVersion == 7)
684 cmd.CommandText = "SELECT name FROM sysdatabases WHERE name = 'master'";
686 cmd.CommandText = "SELECT name FROM sys.databases WHERE name = 'master'";
687 using (SqlDataReader dr = cmd.ExecuteReader ()) {
688 Assert.IsTrue (dr.Read (), "#2");
692 Assert.AreEqual (database, conn.Database, "#3");
694 // Test if the database property is reset on re-opening the connection
695 conn.ConnectionString = connectionString;
697 Assert.AreEqual (database, conn.Database, "#4");
699 // ensure we're really in the expected database
700 cmd.CommandText = "SELECT fname FROM employee WHERE id = 2";
701 using (SqlDataReader dr = cmd.ExecuteReader ()) {
702 Assert.IsTrue (dr.Read (), "#5");
703 Assert.AreEqual ("ramesh", dr.GetValue (0), "#6");
709 [Test] // bug #412571
710 public void Dispose ()
712 StateChangeEventArgs stateChangeArgs;
713 EventArgs disposedArgs;
715 conn = new SqlConnection (connectionString + ";Connection Timeout=30;Packet Size=512;Workstation Id=Desktop");
716 conn.Disposed += new EventHandler (Connection_Disposed);
718 conn.StateChange += new StateChangeEventHandler (Connection_StateChange);
719 Assert.AreEqual (0, events.Count, "#A1");
721 Assert.AreEqual (string.Empty, conn.ConnectionString, "#A2");
722 Assert.AreEqual (15, conn.ConnectionTimeout, "#A3");
723 Assert.AreEqual (string.Empty, conn.Database, "#A4");
724 Assert.AreEqual (string.Empty, conn.DataSource, "#A5");
725 Assert.AreEqual (8000, conn.PacketSize, "#A6");
726 Assert.AreEqual (ConnectionState.Closed, conn.State, "#A7");
727 Assert.IsTrue (string.Compare (conn.WorkstationId, Environment.MachineName, true) == 0, "#A8");
728 Assert.AreEqual (2, events.Count, "#A9");
730 stateChangeArgs = events [0] as StateChangeEventArgs;
731 Assert.IsNotNull (stateChangeArgs, "#B1");
732 Assert.AreEqual (typeof (StateChangeEventArgs), stateChangeArgs.GetType (), "#B2");
733 Assert.AreEqual (ConnectionState.Open, stateChangeArgs.OriginalState, "#B3");
734 Assert.AreEqual (ConnectionState.Closed, stateChangeArgs.CurrentState, "B4");
736 disposedArgs = events [1] as EventArgs;
737 Assert.IsNotNull (disposedArgs, "#C1");
738 Assert.AreEqual (typeof (EventArgs), disposedArgs.GetType (), "#C2");
742 Assert.AreEqual (ConnectionState.Closed, conn.State, "#D1");
743 Assert.AreEqual (3, events.Count, "#D2");
745 disposedArgs = events [2] as EventArgs;
746 Assert.IsNotNull (disposedArgs, "#E1");
747 Assert.AreEqual (typeof (EventArgs), disposedArgs.GetType (), "#E2");
750 void Connection_StateChange (object sender , StateChangeEventArgs e)
755 void Connection_Disposed (object sender , EventArgs e)
761 public void FireInfoMessageEventOnUserErrorsTest ()
763 conn = new SqlConnection ();
764 Assert.AreEqual(false, conn.FireInfoMessageEventOnUserErrors, "#1 The default value should be false");
765 conn.FireInfoMessageEventOnUserErrors = true;
766 Assert.AreEqual(true, conn.FireInfoMessageEventOnUserErrors, "#1 The value should be true after setting the property to true");
770 public void StatisticsEnabledTest ()
772 conn = new SqlConnection ();
773 Assert.AreEqual(false, conn.StatisticsEnabled, "#1 The default value should be false");
774 conn.StatisticsEnabled = true;
775 Assert.AreEqual(true, conn.StatisticsEnabled, "#1 The value should be true after setting the property to true");
779 public void ChangePasswordTest ()
781 string tmpPassword = "modifiedbymonosqlclient";
782 SqlConnection.ChangePassword (connectionString, tmpPassword);
783 SqlConnectionStringBuilder connBuilder = new SqlConnectionStringBuilder (connectionString);
784 string oldPassword = connBuilder.Password;
785 connBuilder.Password = tmpPassword;
786 SqlConnection.ChangePassword (connBuilder.ConnectionString, oldPassword); // Modify to the original password
789 static int GetConnectionCount (SqlConnection conn)
793 SqlCommand cmd = conn.CreateCommand ();
794 cmd.CommandText = "select count(*) from master..sysprocesses where db_name(dbid) = @dbname";
795 cmd.Parameters.Add (new SqlParameter ("@dbname", conn.Database));
796 int connection_count = (int) cmd.ExecuteScalar ();
799 return connection_count;
804 return (engine.ClientVersion);
810 [Category ("sqlserver")]
811 public class GetSchemaTest
813 SqlConnection conn = null;
814 String connectionString = ConnectionManager.Singleton.ConnectionString;
819 conn = new SqlConnection(connectionString);
824 public void TearDown()
830 public void GetSchemaTest1()
833 DataTable tab1 = conn.GetSchema("databases");
834 foreach (DataRow row in tab1.Rows)
836 foreach (DataColumn col in tab1.Columns)
838 if (col.ColumnName.ToString() == "database_name" && row[col].ToString() == "monotest")
847 Assert.AreEqual(true, flag, "#GS1 failed");
851 [ExpectedException(typeof(ArgumentException))]
852 public void GetSchemaTest2()
854 conn.GetSchema(null);
858 public void GetSchemaTest3 ()
860 Assert.Ignore ("We currently have no foreign keys defined in the test database");
863 DataTable tab1 = conn.GetSchema("ForeignKeys");
864 foreach (DataRow row in tab1.Rows)
866 foreach (DataColumn col in tab1.Columns)
869 * We need to consider multiple values
871 if (col.ColumnName.ToString() == "TABLE_NAME" && row[col].ToString() == "tmptable1")
880 Assert.AreEqual(true, flag, "#GS3 failed");
884 public void GetSchemaTest4()
887 DataTable tab1 = conn.GetSchema("Indexes");
888 foreach (DataRow row in tab1.Rows)
890 foreach (DataColumn col in tab1.Columns)
893 * We need to consider multiple values
895 if (col.ColumnName.ToString() == "table_name" && row[col].ToString() == "binary_family")
904 Assert.AreEqual(true, flag, "#GS4 failed");
908 public void GetSchemaTest5()
911 DataTable tab1 = conn.GetSchema("IndexColumns");
912 foreach (DataRow row in tab1.Rows)
914 foreach (DataColumn col in tab1.Columns)
917 * We need to consider multiple values
919 if (col.ColumnName.ToString() == "table_name" && row[col].ToString() == "binary_family")
928 Assert.AreEqual(true, flag, "#GS5 failed");
932 public void GetSchemaTest6()
935 DataTable tab1 = conn.GetSchema("Procedures");
936 foreach (DataRow row in tab1.Rows)
938 foreach (DataColumn col in tab1.Columns)
941 * We need to consider multiple values
943 if (col.ColumnName.ToString() == "SPECIFIC_NAME" && row[col].ToString() == "sp_get_age")
952 Assert.AreEqual(true, flag, "#GS6 failed");
956 public void GetSchemaTest7()
959 DataTable tab1 = conn.GetSchema("ProcedureParameters");
960 foreach (DataRow row in tab1.Rows)
962 foreach (DataColumn col in tab1.Columns)
965 * We need to consider multiple values
967 if (col.ColumnName.ToString() == "SPECIFIC_NAME" && row[col].ToString() == "sp_get_age")
976 Assert.AreEqual(true, flag, "#GS7 failed");
980 public void GetSchemaTest8()
983 DataTable tab1 = conn.GetSchema("Tables");
984 foreach (DataRow row in tab1.Rows)
986 foreach (DataColumn col in tab1.Columns)
989 * We need to consider multiple values
991 if (col.ColumnName.ToString() == "TABLE_NAME" && row[col].ToString() == "binary_family")
1000 Assert.AreEqual(true, flag, "#GS8 failed");
1004 public void GetSchemaTest9()
1007 DataTable tab1 = conn.GetSchema("Columns");
1008 foreach (DataRow row in tab1.Rows)
1010 foreach (DataColumn col in tab1.Columns)
1013 * We need to consider multiple values
1015 if (col.ColumnName.ToString() == "TABLE_NAME" && row[col].ToString() == "binary_family")
1024 Assert.AreEqual(true, flag, "#GS9 failed");
1028 public void GetSchemaTest10()
1031 DataTable tab1 = conn.GetSchema("Users");
1032 foreach (DataRow row in tab1.Rows)
1034 foreach (DataColumn col in tab1.Columns)
1037 * We need to consider multiple values
1039 if (col.ColumnName.ToString() == "user_name" && row[col].ToString() == "public")
1048 Assert.AreEqual(true, flag, "#GS10 failed");
1052 public void GetSchemaTest11 ()
1054 Assert.Ignore ("Incorrect syntax near 'TABLE_SCHEMA'");
1057 DataTable tab1 = conn.GetSchema("Views");
1058 flag = true; // FIXME: Currently MS-SQL 2005 returns empty table. Remove this flag ASAP.
1059 foreach (DataRow row in tab1.Rows)
1061 foreach (DataColumn col in tab1.Columns)
1064 * We need to consider multiple values.
1066 if (col.ColumnName.ToString() == "user_name" && row[col].ToString() == "public")
1075 Assert.AreEqual(true, flag, "#GS11 failed");
1079 public void GetSchemaTest12 ()
1081 Assert.Ignore ("Incorrect syntax near '('");
1084 DataTable tab1 = conn.GetSchema("ViewColumns");
1085 flag = true; // FIXME: Currently MS-SQL 2005 returns empty table. Remove this flag ASAP.
1086 foreach (DataRow row in tab1.Rows)
1088 foreach (DataColumn col in tab1.Columns)
1091 * We need to consider multiple values.
1093 if (col.ColumnName.ToString() == "user_name" && row[col].ToString() == "public")
1102 Assert.AreEqual(true, flag, "#GS12 failed");
1106 public void GetSchemaTest13 ()
1108 Assert.Ignore ("The multi-part identifier \"assportemblies.name\" could not be bound");
1111 DataTable tab1 = conn.GetSchema("UserDefinedTypes");
1112 flag = true; // FIXME: Currently MS-SQL 2005 returns empty table. Remove this flag ASAP.
1113 foreach (DataRow row in tab1.Rows)
1115 foreach (DataColumn col in tab1.Columns)
1118 * We need to consider multiple values.
1120 if (col.ColumnName.ToString() == "user_name" && row[col].ToString() == "public")
1129 Assert.AreEqual(true, flag, "#GS13 failed");
1133 public void GetSchemaTest14()
1136 string [] restrictions = new string[4];
1138 restrictions[0] = "monotest";
1139 restrictions[1] = "dbo";
1140 restrictions[2] = null;
1141 restrictions[3] = "BASE TABLE";
1142 DataTable tab1 = conn.GetSchema("Tables", restrictions);
1143 foreach (DataRow row in tab1.Rows)
1145 foreach (DataColumn col in tab1.Columns)
1148 * We need to consider multiple values
1150 if (col.ColumnName.ToString() == "TABLE_NAME" && row[col].ToString() == "binary_family")
1159 Assert.AreEqual(true, flag, "#GS14 failed");
1163 public void GetSchemaTest15()
1166 string [] restrictions = new string[4];
1168 restrictions[0] = "monotest";
1169 restrictions[1] = null;
1170 restrictions[2] = "binary_family";
1171 restrictions[3] = null;
1172 DataTable tab1 = conn.GetSchema("IndexColumns", restrictions);
1173 foreach (DataRow row in tab1.Rows)
1175 foreach (DataColumn col in tab1.Columns)
1178 * We need to consider multiple values
1180 if (col.ColumnName.ToString() == "table_name" && row[col].ToString() == "binary_family")
1189 Assert.AreEqual(true, flag, "#GS15 failed");
1193 public void GetSchemaTest16()
1196 string [] restrictions = new string[4];
1198 restrictions[0] = "monotest";
1199 restrictions[1] = null;
1200 restrictions[2] = "sp_get_age";
1201 restrictions[3] = null;
1202 DataTable tab1 = conn.GetSchema("Procedures", restrictions);
1203 foreach (DataRow row in tab1.Rows)
1205 foreach (DataColumn col in tab1.Columns)
1208 * We need to consider multiple values
1210 if (col.ColumnName.ToString() == "ROUTINE_NAME" && row[col].ToString() == "sp_get_age")
1219 Assert.AreEqual(true, flag, "#GS16 failed");
1223 public void GetSchemaTest17()
1226 DataTable tab1 = conn.GetSchema();
1227 foreach (DataRow row in tab1.Rows)
1229 foreach (DataColumn col in tab1.Columns)
1232 * We need to consider multiple values
1234 if (col.ColumnName.ToString() == "CollectionName" && row[col].ToString() == "UserDefinedTypes")
1243 Assert.AreEqual(true, flag, "#GS17 failed");
1247 public void GetSchemaTest18()
1250 DataTable tab1 = conn.GetSchema("RESTRICTIONS");
1251 foreach (DataRow row in tab1.Rows)
1253 foreach (DataColumn col in tab1.Columns)
1256 * We need to consider multiple values
1258 if (col.ColumnName.ToString() == "RestrictionDefault" && row[col].ToString() == "CONSTRAINT_NAME")
1267 Assert.AreEqual(true, flag, "#GS18 failed");
1271 [ExpectedException (typeof (ArgumentException))]
1272 public void GetSchemaTest19 ()
1274 String [] restrictions = new String[1];
1275 conn.GetSchema("RESTRICTIONS", restrictions);
1279 public void GetSchemaTest20 ()
1282 DataTable tab1 = conn.GetSchema("DataTypes");
1283 foreach (DataRow row in tab1.Rows)
1285 foreach (DataColumn col in tab1.Columns)
1288 * We need to consider multiple values
1290 if (col.ColumnName.ToString() == "TypeName" && row[col].ToString() == "uniqueidentifier")
1299 Assert.AreEqual(true, flag, "#GS20 failed");
1303 public void GetSchemaTest21()
1306 DataTable tab1 = conn.GetSchema();
1307 foreach (DataRow row in tab1.Rows)
1309 foreach (DataColumn col in tab1.Columns)
1312 * We need to consider multiple values
1314 if (col.ColumnName.ToString() == "CollectionName" && row[col].ToString() == "UserDefinedTypes")
1323 Assert.AreEqual(true, flag, "#GS21 failed");
1326 public void GetSchemaTest22()
1329 DataTable tab1 = conn.GetSchema("ReservedWords");
1330 foreach (DataRow row in tab1.Rows)
1332 foreach (DataColumn col in tab1.Columns)
1335 * We need to consider multiple values
1337 if (col.ColumnName.ToString() == "ReservedWord" && row[col].ToString() == "UPPER")
1346 Assert.AreEqual(true, flag, "#GS22 failed");
1350 public void GetSchemaTest23()
1353 DataTable tab1 = conn.GetSchema("ReservedWords");
1354 foreach (DataRow row in tab1.Rows)
1356 foreach (DataColumn col in tab1.Columns)
1359 * We need to consider multiple values
1361 if (col.ColumnName.ToString() == "ReservedWord" && row[col].ToString() == "upper")
1370 Assert.AreEqual(false, flag, "#GS23 failed");
1374 public void GetSchemaTest24()
1377 string [] restrictions = new string[4];
1379 restrictions[0] = "monotest";
1380 restrictions[1] = null;
1381 restrictions[2] = "sp_get_age";
1382 restrictions[3] = null;
1383 DataTable tab1 = conn.GetSchema("Procedures", restrictions);
1384 foreach (DataRow row in tab1.Rows)
1386 foreach (DataColumn col in tab1.Columns)
1389 * We need to consider multiple values
1391 if (col.ColumnName.ToString() == "ROUTINE_NAME" && row[col].ToString() == "mono")
1400 Assert.AreEqual(false, flag, "#GS24 failed");
1404 [ExpectedException (typeof (ArgumentException))]
1405 public void GetSchemaTest25 ()
1407 String [] restrictions = new String [1];
1408 conn.GetSchema ("Mono", restrictions);