//
using System;
+using System.Collections;
using System.Data;
using System.Data.SqlClient;
using System.Net;
+using System.Threading;
+
using NUnit.Framework;
-using System.Collections;
-namespace MonoTests.System.Data
+namespace MonoTests.System.Data.Connected.SqlClient
{
[TestFixture]
[Category ("sqlserver")]
public class SqlConnectionTest
{
SqlConnection conn;
- String connectionString = ConnectionManager.Singleton.ConnectionString;
+ String connectionString;
ArrayList events;
+ EngineConfig engine;
[SetUp]
public void SetUp ()
{
events = new ArrayList ();
+ connectionString = ConnectionManager.Instance.Sql.ConnectionString;
+ engine = ConnectionManager.Instance.Sql.EngineConfig;
}
[TearDown]
{
if (conn != null)
conn.Dispose ();
+ if (connectionString != null)
+ SqlConnection.ClearAllPools ();
}
[Test]
}
[Test]
- public void Open_ConnectionString_Incorrect ()
+ public void Open_ConnectionString_LoginInvalid ()
{
- Assert.Ignore ("NotWorking");
-
// login invalid
conn = new SqlConnection (connectionString + "user id=invalidLogin");
try {
conn.Open ();
- Assert.Fail ("#A1");
+ Assert.Fail ("#1");
} catch (SqlException ex) {
// Login failed for user 'invalidLogin'
- Assert.AreEqual (typeof (SqlException), ex.GetType (), "#A2");
- Assert.AreEqual ((byte) 14, ex.Class, "#A3");
- Assert.IsNull (ex.InnerException, "#A4");
- Assert.IsNotNull (ex.Message, "#A5");
- Assert.IsTrue (ex.Message.IndexOf ("'invalidLogin'") != -1, "#A6");
- Assert.AreEqual (18456, ex.Number, "#A7");
- Assert.AreEqual ((byte) 1, ex.State, "#A8");
+ Assert.AreEqual (typeof (SqlException), ex.GetType (), "#2");
+ Assert.AreEqual ((byte) 14, ex.Class, "#3");
+ Assert.IsNull (ex.InnerException, "#4");
+ Assert.IsNotNull (ex.Message, "#5");
+ Assert.AreEqual (18456, ex.Number, "#7");
+ Assert.AreEqual ((byte) 1, ex.State, "#8");
} finally {
conn.Close ();
}
+ }
- // database invalid
+ [Test]
+ public void Open_ConnectionString_DatabaseInvalid ()
+ {
conn = new SqlConnection (connectionString + "database=invalidDB");
try {
conn.Open ();
- Assert.Fail ("#B1");
+ Assert.Fail ("#1");
} catch (SqlException ex) {
// Cannot open database "invalidDB" requested
// by the login. The login failed
- Assert.AreEqual (typeof (SqlException), ex.GetType (), "#B2");
- Assert.AreEqual ((byte) 11, ex.Class, "#B3");
- Assert.IsNull (ex.InnerException, "#B4");
- Assert.IsNotNull (ex.Message, "#B5");
- Assert.IsTrue (ex.Message.IndexOf ("\"invalidDB\"") != -1, "#B6");
- Assert.AreEqual (4060, ex.Number, "#B7");
- Assert.AreEqual ((byte) 1, ex.State, "#B8");
+ Assert.AreEqual (typeof (SqlException), ex.GetType (), "#2");
+ Assert.AreEqual ((byte) 14, ex.Class, "#3");
+ Assert.IsNull (ex.InnerException, "#4");
+ Assert.IsNotNull (ex.Message, "#5");
+ Assert.AreEqual (18456, ex.Number, "#7");
+ Assert.AreEqual ((byte) 1, ex.State, "#8");
} finally {
conn.Close ();
}
+ }
+
+ [Test]
+ public void Open_ConnectionString_PasswordInvalid ()
+ {
// password invalid
conn = new SqlConnection (connectionString + ";password=invalidPassword");
try {
conn.Open ();
- Assert.Fail ("#C1");
+ Assert.Fail ("#1");
} catch (SqlException ex) {
// Login failed for user '...'
- Assert.AreEqual (typeof (SqlException), ex.GetType (), "#C2");
- Assert.AreEqual ((byte) 14, ex.Class, "#C3");
- Assert.IsNull (ex.InnerException, "#C4");
- Assert.IsNotNull (ex.Message, "#C5");
- Assert.AreEqual (18456, ex.Number, "#C6");
- Assert.AreEqual ((byte) 1, ex.State, "#C7");
+ Assert.AreEqual (typeof (SqlException), ex.GetType (), "#2");
+ Assert.AreEqual ((byte) 14, ex.Class, "#3");
+ Assert.IsNull (ex.InnerException, "#4");
+ Assert.IsNotNull (ex.Message, "#5");
+ Assert.AreEqual (18456, ex.Number, "#6");
+ Assert.AreEqual ((byte) 1, ex.State, "#7");
} finally {
conn.Close ();
}
+ }
+
+ [Test]
+ public void Open_ConnectionString_ServerInvalid ()
+ {
+ Assert.Ignore ("Long running");
// server invalid
conn = new SqlConnection (connectionString + ";server=invalidServerName");
try {
conn.Open ();
- Assert.Fail ("#D1");
+ Assert.Fail ("#1");
} catch (SqlException ex) {
// An error has occurred while establishing a
// connection to the server...
- Assert.AreEqual (typeof (SqlException), ex.GetType (), "#D2");
- Assert.AreEqual ((byte) 20, ex.Class, "#D3");
- Assert.IsNull (ex.InnerException, "#D4");
- Assert.IsNotNull (ex.Message, "#D5");
-#if NET_2_0
- Assert.AreEqual (53, ex.Number, "#D6");
-#else
- Assert.AreEqual (17, ex.Number, "#D6");
-#endif
- Assert.AreEqual ((byte) 0, ex.State, "#D7");
+ Assert.AreEqual (typeof (SqlException), ex.GetType (), "#2");
+ Assert.AreEqual ((byte) 20, ex.Class, "#3");
+ Assert.IsNull (ex.InnerException, "#4");
+ Assert.IsNotNull (ex.Message, "#5");
+ Assert.AreEqual (53, ex.Number, "#6");
+ Assert.AreEqual ((byte) 0, ex.State, "#7");
} finally {
conn.Close ();
}
+ }
+
+ [Test] // bug #383061
+ [Category("NotWorking")]
+ public void Open_MaxPoolSize_Reached ()
+ {
+ connectionString += ";Pooling=true;Connection Lifetime=6;Connect Timeout=3;Max Pool Size=2";
+
+ SqlConnection conn1 = new SqlConnection (connectionString);
+ conn1.Open ();
+
+ SqlConnection conn2 = new SqlConnection (connectionString);
+ conn2.Open ();
+
+ DateTime start = DateTime.Now;
+
+ try {
+ using (SqlConnection sqlConnection = new SqlConnection (connectionString)) {
+ sqlConnection.Open ();
+ }
+ Assert.Fail ("#A1");
+ } catch (InvalidOperationException ex) {
+ // System.InvalidOperationException: Timeout expired.
+ // The timeout period elapsed prior to obtaining a
+ // connection from the pool. This may have occurred
+ // because all pooled connections were in use and max
+ // pool size was reached.
+ Assert.AreEqual (typeof (InvalidOperationException), ex.GetType (), "#A2");
+ Assert.IsNull (ex.InnerException, "#A3");
+ Assert.IsNotNull (ex.Message, "#A4");
+ }
+
+ TimeSpan elapsed = DateTime.Now - start;
+
+ Assert.IsTrue (elapsed.TotalSeconds >= 3, "#B1:" + elapsed.TotalSeconds);
+ Assert.IsTrue (elapsed.TotalSeconds < 4, "#B2:" + elapsed.TotalSeconds);
+
+ conn2.Close ();
+
+ // as the second connection is closed, we should now be
+ // able to open a new connection (which essentially
+ // uses the pooled connection from conn2)
+ SqlConnection conn3 = new SqlConnection (connectionString);
+ conn3.Open ();
+ conn3.Close ();
+
+ conn1.Close ();
}
[Test] // bug #412574
public void Close ()
{
- Assert.Ignore ("bug #412574");
-
conn = new SqlConnection (connectionString);
conn.Open ();
conn.StateChange += new StateChangeEventHandler (Connection_StateChange);
[Test]
public void ChangeDatabase ()
{
- conn = new SqlConnection (connectionString);
- conn.Open ();
- conn.ChangeDatabase ("master");
- Assert.AreEqual ("master", conn.Database);
+ conn = new SqlConnection(connectionString);
+ conn.Open();
+
+ if (ConnectionManager.Instance.Sql.IsAzure)
+ {
+ var exc = Assert.Throws<SqlException>(() => conn.ChangeDatabase("master"));
+ Assert.Equals(40508, exc.Number); //USE statement is not supported to switch between databases (Azure).
+ }
+ else
+ {
+ conn.ChangeDatabase("master");
+ Assert.AreEqual("master", conn.Database);
+ }
}
[Test]
public void ChangeDatabase_DatabaseName_DoesNotExist ()
{
+ if (ConnectionManager.Instance.Sql.IsAzure)
+ Assert.Ignore("SQL Azure doesn't support 'ChangeDatabase'");
+
conn = new SqlConnection (connectionString);
conn.Open ();
[Test] // bug #412581
public void ChangeDatabase_DatabaseName_Whitespace ()
{
-#if NET_2_0
Assert.Ignore ("bug #412581");
-#endif
conn = new SqlConnection (connectionString);
conn.Open ();
try {
conn.ChangeDatabase (" ");
Assert.Fail ("#1");
-#if NET_2_0
} catch (SqlException ex) {
// Could not locate entry in sysdatabases for
// database ' '. No entry found with that name.
Assert.AreEqual (911, ex.Number, "#7");
Assert.AreEqual ((byte) 1, ex.State, "#8");
}
-#else
- } catch (ArgumentException ex) {
- // Database cannot be null, the empty string,
- // or string of only whitespace
- Assert.AreEqual (typeof (ArgumentException), ex.GetType (), "#2");
- Assert.IsNull (ex.InnerException, "#3");
- Assert.IsNotNull (ex.Message, "#4");
- Assert.IsNull (ex.ParamName);
- }
-#endif
+ }
+
+ [Test]
+ [Category("NotWorking")]
+ public void ClearAllPools ()
+ {
+ SqlConnection conn1 = new SqlConnection (connectionString + ";Pooling=false");
+ conn1.Open ();
+
+ int initial_connection_count = GetConnectionCount (conn1);
+
+ SqlConnection conn2 = new SqlConnection (connectionString + ";App=A");
+ conn2.Open ();
+ conn2.Close ();
+
+ SqlConnection conn3 = new SqlConnection (connectionString + ";App=B");
+ conn3.Open ();
+ conn3.Close ();
+
+ Assert.AreEqual (initial_connection_count + 2, GetConnectionCount (conn1), "#1");
+
+ SqlConnection.ClearAllPools ();
+
+ Assert.AreEqual (initial_connection_count, GetConnectionCount (conn1), "#2");
+ conn1.Close ();
+ }
+
+ [Test] // bug #443131
+ [Category("NotWorking")]
+ public void ClearPool ()
+ {
+ SqlConnection conn1 = new SqlConnection (connectionString);
+ conn1.Open ();
+
+ int initial_connection_count = GetConnectionCount (conn1);
+
+ SqlConnection conn2 = new SqlConnection (connectionString);
+ conn2.Open ();
+
+ SqlConnection conn3 = new SqlConnection (connectionString);
+ conn3.Open ();
+ conn3.Close ();
+
+ Assert.AreEqual (initial_connection_count + 2, GetConnectionCount (conn1), "#1");
+
+ SqlConnection.ClearPool (conn1);
+
+ // check if pooled connections that were not in use are
+ // actually closed
+ Assert.AreEqual (initial_connection_count + 1, GetConnectionCount (conn1), "#2");
+
+ conn2.Close ();
+
+ // check if connections that were in use when the pool
+ // was cleared will not be returned to the pool when
+ // closed (and are closed instead)
+ Assert.AreEqual (initial_connection_count, GetConnectionCount (conn1), "#3");
+
+ SqlConnection conn4 = new SqlConnection (connectionString);
+ conn4.Open ();
+
+ SqlConnection conn5 = new SqlConnection (connectionString);
+ conn5.Open ();
+
+ SqlConnection conn6 = new SqlConnection (connectionString);
+ conn6.Open ();
+
+ Assert.AreEqual (initial_connection_count + 3, GetConnectionCount (conn1), "#4");
+
+ conn5.Close ();
+ conn6.Close ();
+
+ // check if new connections are stored in the pool again
+ Assert.AreEqual (initial_connection_count + 3, GetConnectionCount (conn1), "#5");
+
+ conn1.Close ();
+
+ Assert.AreEqual (initial_connection_count + 2, GetConnectionCount (conn4), "#6");
+
+ SqlConnection.ClearPool (conn3);
+
+ // the connection passed to ClearPool does not have to
+ // be open
+ Assert.AreEqual (initial_connection_count, GetConnectionCount (conn4), "#7");
+
+ SqlConnection conn7 = new SqlConnection (connectionString);
+ conn7.Open ();
+ conn7.Close ();
+
+ Assert.AreEqual (initial_connection_count + 1, GetConnectionCount (conn4), "#8");
+
+ conn3.ConnectionString += ";App=B";
+ SqlConnection.ClearPool (conn3);
+
+ // check if a pool is identified by its connection string
+ Assert.AreEqual (initial_connection_count + 1, GetConnectionCount (conn4), "#9");
+
+ SqlConnection conn8 = new SqlConnection (connectionString);
+ SqlConnection.ClearPool (conn8);
+
+ // connection should not have been opened before to
+ // clear the corresponding pool
+ Assert.AreEqual (initial_connection_count, GetConnectionCount (conn4), "#10");
+
+ SqlConnection conn9 = new SqlConnection (connectionString);
+ conn9.Open ();
+ conn9.Close ();
+
+ Assert.AreEqual (initial_connection_count + 1, GetConnectionCount (conn4), "#11");
+
+ conn3.ConnectionString = connectionString;
+ SqlConnection.ClearPool (conn3);
+
+ Assert.AreEqual (initial_connection_count, GetConnectionCount (conn4), "#12");
+
+ SqlConnection conn10 = new SqlConnection (connectionString);
+ conn10.Open ();
+
+ SqlConnection conn11 = new SqlConnection (connectionString + ";App=B");
+ conn11.Open ();
+
+ SqlConnection conn12 = new SqlConnection (connectionString + ";App=B");
+ conn12.Open ();
+
+ SqlConnection conn13 = new SqlConnection (connectionString + ";App=B");
+ conn13.Open ();
+
+ conn10.Close ();
+ conn11.Close ();
+ conn12.Close ();
+ conn13.Close ();
+
+ Assert.AreEqual (initial_connection_count + 4, GetConnectionCount (conn4), "#13");
+
+ // check that other connection pools are not affected
+ SqlConnection.ClearPool (conn13);
+
+ Assert.AreEqual (initial_connection_count + 1, GetConnectionCount (conn4), "#14");
+
+ SqlConnection conn14 = new SqlConnection (connectionString);
+ conn14.Open ();
+ conn14.Dispose ();
+
+ // a disposed connection cannot be used to clear a pool
+ SqlConnection.ClearPool (conn14);
+
+ Assert.AreEqual (initial_connection_count + 1, GetConnectionCount (conn4), "#15");
+
+ SqlConnection.ClearPool (conn4);
+
+ Assert.AreEqual (initial_connection_count, GetConnectionCount (conn4), "#16");
+
+ conn4.Close ();
}
[Test]
conn.ConnectionString = string.Empty;
Assert.AreEqual (string.Empty, conn.DataSource, "#C1");
Assert.AreEqual ("", conn.Database, "#C2");
-#if NET_2_0
Assert.AreEqual (8000, conn.PacketSize, "#C3");
-#else
- Assert.AreEqual (8192, conn.PacketSize, "#C3");
-#endif
Assert.AreEqual (15, conn.ConnectionTimeout, "#C4");
Assert.IsTrue (string.Compare (conn.WorkstationId, Environment.MachineName, true) == 0, "#C5");
}
[Test]
public void Database ()
{
+ if (ConnectionManager.Instance.Sql.IsAzure)
+ Assert.Ignore("SQL Azure doesn't support 'use [db]'");
+
conn = new SqlConnection (connectionString);
string database = conn.Database;
+ SqlCommand cmd;
+
// Test if database property is updated when a query changes database
conn.Open ();
- SqlCommand cmd = new SqlCommand ("use [master]" , conn);
+ cmd = new SqlCommand ("use [master]" , conn);
cmd.ExecuteNonQuery ();
Assert.AreEqual ("master", conn.Database, "#1");
+
+ // ensure we're really in the expected database
+ if (ClientVersion == 7)
+ cmd.CommandText = "SELECT name FROM sysdatabases WHERE name = 'master'";
+ else
+ cmd.CommandText = "SELECT name FROM sys.databases WHERE name = 'master'";
+ using (SqlDataReader dr = cmd.ExecuteReader ()) {
+ Assert.IsTrue (dr.Read (), "#2");
+ }
+
conn.Close ();
- Assert.AreEqual (database, conn.Database, "#2");
+ Assert.AreEqual (database, conn.Database, "#3");
// Test if the database property is reset on re-opening the connection
conn.ConnectionString = connectionString;
conn.Open ();
- Assert.AreEqual (database, conn.Database, "#3");
+ Assert.AreEqual (database, conn.Database, "#4");
+
+ // ensure we're really in the expected database
+ cmd.CommandText = "SELECT fname FROM employee WHERE id = 2";
+ using (SqlDataReader dr = cmd.ExecuteReader ()) {
+ Assert.IsTrue (dr.Read (), "#5");
+ Assert.AreEqual ("ramesh", dr.GetValue (0), "#6");
+ }
+
conn.Close ();
}
[Test] // bug #412571
public void Dispose ()
{
- Assert.Ignore ("bug #412571");
-
StateChangeEventArgs stateChangeArgs;
EventArgs disposedArgs;
- conn = new SqlConnection (connectionString);
+ conn = new SqlConnection (connectionString + ";Connection Timeout=30;Packet Size=512;Workstation Id=Desktop");
conn.Disposed += new EventHandler (Connection_Disposed);
conn.Open ();
conn.StateChange += new StateChangeEventHandler (Connection_StateChange);
Assert.AreEqual (0, events.Count, "#A1");
conn.Dispose ();
- Assert.AreEqual (ConnectionState.Closed, conn.State, "#A2");
- Assert.AreEqual (2, events.Count, "#A3");
+ Assert.AreEqual (string.Empty, conn.ConnectionString, "#A2");
+ Assert.AreEqual (15, conn.ConnectionTimeout, "#A3");
+ Assert.AreEqual (string.Empty, conn.Database, "#A4");
+ Assert.AreEqual (string.Empty, conn.DataSource, "#A5");
+ Assert.AreEqual (8000, conn.PacketSize, "#A6");
+ Assert.AreEqual (ConnectionState.Closed, conn.State, "#A7");
+ Assert.IsTrue (string.Compare (conn.WorkstationId, Environment.MachineName, true) == 0, "#A8");
+ Assert.AreEqual (2, events.Count, "#A9");
stateChangeArgs = events [0] as StateChangeEventArgs;
Assert.IsNotNull (stateChangeArgs, "#B1");
events.Add (e);
}
-#if NET_2_0
[Test]
public void FireInfoMessageEventOnUserErrorsTest ()
{
}
[Test]
+ [Category("NotWorking")]
public void ChangePasswordTest ()
{
string tmpPassword = "modifiedbymonosqlclient";
connBuilder.Password = tmpPassword;
SqlConnection.ChangePassword (connBuilder.ConnectionString, oldPassword); // Modify to the original password
}
-#endif
+
+ static int GetConnectionCount (SqlConnection conn)
+ {
+ Thread.Sleep (200);
+
+ SqlCommand cmd = conn.CreateCommand ();
+ cmd.CommandText = "select count(*) from master..sysprocesses where db_name(dbid) = @dbname";
+ cmd.Parameters.Add (new SqlParameter ("@dbname", conn.Database));
+ int connection_count = (int) cmd.ExecuteScalar ();
+ cmd.Dispose ();
+
+ return connection_count;
+ }
+
+ int ClientVersion {
+ get {
+ return (engine.ClientVersion);
+ }
+ }
}
-#if NET_2_0
[TestFixture]
[Category ("sqlserver")]
public class GetSchemaTest
{
SqlConnection conn = null;
- String connectionString = ConnectionManager.Singleton.ConnectionString;
+ String connectionString = ConnectionManager.Instance.Sql.ConnectionString;
[SetUp]
- public void Setup()
+ public void SetUp()
{
conn = new SqlConnection(connectionString);
conn.Open();
[TearDown]
public void TearDown()
{
- conn.Close();
+ conn?.Close();
}
[Test]
public void GetSchemaTest1()
{
+ if (ConnectionManager.Instance.Sql.IsAzure)
+ Assert.Ignore("SQL Azure - Not supported'");
+
bool flag = false;
DataTable tab1 = conn.GetSchema("databases");
foreach (DataRow row in tab1.Rows)
{
foreach (DataColumn col in tab1.Columns)
{
- if (col.ColumnName.ToString() == "database_name" && row[col].ToString() == "monotest")
+ if (col.ColumnName.ToString() == "database_name" && row[col].ToString() == ConnectionManager.Instance.DatabaseName)
{
flag = true;
break;
[Test]
public void GetSchemaTest9()
{
+ if (ConnectionManager.Instance.Sql.IsAzure)
+ Assert.Ignore("SQL Azure - Not supported'");
+
bool flag = false;
DataTable tab1 = conn.GetSchema("Columns");
foreach (DataRow row in tab1.Rows)
}
[Test]
+ [Ignore("TODO: fix restrictions")]
public void GetSchemaTest14()
{
bool flag = false;
string [] restrictions = new string[4];
- restrictions[0] = "monotest";
+ restrictions[0] = ConnectionManager.Instance.DatabaseName;
restrictions[1] = "dbo";
restrictions[2] = null;
restrictions[3] = "BASE TABLE";
}
[Test]
+ [Ignore("TODO: fix restrictions")]
public void GetSchemaTest15()
{
bool flag = false;
string [] restrictions = new string[4];
- restrictions[0] = "monotest";
+ restrictions[0] = ConnectionManager.Instance.DatabaseName;
restrictions[1] = null;
restrictions[2] = "binary_family";
restrictions[3] = null;
}
[Test]
+ [Ignore("TODO: fix restrictions")]
public void GetSchemaTest16()
{
bool flag = false;
string [] restrictions = new string[4];
- restrictions[0] = "monotest";
+ restrictions[0] = ConnectionManager.Instance.DatabaseName;
restrictions[1] = null;
restrictions[2] = "sp_get_age";
restrictions[3] = null;
bool flag = false;
string [] restrictions = new string[4];
- restrictions[0] = "monotest";
+ restrictions[0] = ConnectionManager.Instance.DatabaseName;
restrictions[1] = null;
restrictions[2] = "sp_get_age";
restrictions[3] = null;
conn.GetSchema ("Mono", restrictions);
}
}
-#endif
}