public void NullTest() { using (SqlConnection conn = new SqlConnection(_connStr)) { conn.Open(); SqlCommand com = new SqlCommand() { Connection = conn, CommandText = "insert into TestTableNull values (@p);" + "SELECT * FROM TestTableNull" }; SqlParameter p = com.Parameters.Add("@p", SqlDbType.Udt); p.UdtTypeName = "Utf8String"; p.Value = DBNull.Value; using (SqlTransaction trans = conn.BeginTransaction()) { com.Transaction = trans; SqlDataReader reader = com.ExecuteReader(); Utf8String[] expectedValues = { new Utf8String("this"), new Utf8String("is"), new Utf8String("a"), new Utf8String("test") }; int currentValue = 0; do { while (reader.Read()) { DataTestUtility.AssertEqualsWithDescription(1, reader.FieldCount, "Unexpected FieldCount."); if (currentValue < expectedValues.Length) { DataTestUtility.AssertEqualsWithDescription(expectedValues[currentValue], reader.GetValue(0), "Unexpected Value."); DataTestUtility.AssertEqualsWithDescription(expectedValues[currentValue], reader.GetSqlValue(0), "Unexpected SQL Value."); } else { DataTestUtility.AssertEqualsWithDescription(DBNull.Value, reader.GetValue(0), "Unexpected Value."); Utf8String sqlValue = (Utf8String)reader.GetSqlValue(0); INullable iface = sqlValue as INullable; Assert.True(iface != null, "Expected interface cast to return a non-null value."); Assert.True(iface.IsNull, "Expected interface cast to have IsNull==true."); } currentValue++; Assert.True(currentValue <= (expectedValues.Length + 1), "Expected to only hit one extra result."); } }while (reader.NextResult()); DataTestUtility.AssertEqualsWithDescription(currentValue, (expectedValues.Length + 1), "Did not hit all expected values."); reader.Close(); } } }
public void OutputParameterTest() { using (SqlConnection conn = new SqlConnection(_connStr)) { conn.Open(); SqlCommand com = new SqlCommand() { Connection = conn, CommandText = "UDTTest", CommandType = CommandType.StoredProcedure }; SqlParameter p = com.Parameters.Add("@value", SqlDbType.Udt); p.UdtTypeName = "Utf8String"; p.Direction = ParameterDirection.Output; SqlDataReader reader = com.ExecuteReader(); do { while (reader.Read()) { DataTestUtility.AssertEqualsWithDescription(0, reader.FieldCount, "Should not have any reader results."); } }while (reader.NextResult()); reader.Close(); DataTestUtility.AssertEqualsWithDescription(new Utf8String("this is an outparam test"), p.Value, "Unexpected parameter value."); } }
/// <summary> /// Tests if killing the connection using the InternalConnectionWrapper is working /// </summary> /// <param name="connectionString"></param> private static void KillConnectionTest(string connectionString) { #if DEBUG InternalConnectionWrapper wrapper = null; using (SqlConnection connection = new SqlConnection(connectionString)) { connection.Open(); wrapper = new InternalConnectionWrapper(connection); using (SqlCommand command = new SqlCommand("SELECT 5;", connection)) { DataTestUtility.AssertEqualsWithDescription(5, command.ExecuteScalar(), "Incorrect scalar result."); } wrapper.KillConnection(); } using (SqlConnection connection2 = new SqlConnection(connectionString)) { connection2.Open(); Assert.False(wrapper.IsInternalConnectionOf(connection2), "New connection has internal connection that was just killed"); using (SqlCommand command = new SqlCommand("SELECT 5;", connection2)) { DataTestUtility.AssertEqualsWithDescription(5, command.ExecuteScalar(), "Incorrect scalar result."); } } #endif }
public void UDTParams_Early() { using (SqlConnection conn = new SqlConnection(_connStr)) using (SqlCommand cmd = conn.CreateCommand()) { conn.Open(); cmd.Transaction = conn.BeginTransaction(); cmd.CommandText = "vicinity"; // select proc cmd.CommandType = CommandType.StoredProcedure; SqlParameter p = cmd.Parameters.Add("@boundary", SqlDbType.Udt); p.UdtTypeName = "UdtTestDb.dbo.Point"; Point pt = new Point() { X = 250, Y = 250 }; p.Value = pt; using (SqlDataReader reader = cmd.ExecuteReader()) { DataTestUtility.AssertEqualsWithDescription( (new Point(250, 250)).ToString(), p.Value.ToString(), "Unexpected Point value."); } } }
public void Reader_PointLate() { using (SqlConnection conn = new SqlConnection(_connStr)) using (SqlCommand cmd = new SqlCommand("select name, location from cities", conn)) { conn.Open(); using (SqlDataReader reader = cmd.ExecuteReader()) { string expectedReaderValues = "ColumnName[0] = name" + Environment.NewLine + "DataType[0] = nvarchar" + Environment.NewLine + "FieldType[0] = System.String" + Environment.NewLine + "ColumnName[1] = location" + Environment.NewLine + "DataType[1] = UdtTestDb.dbo.Point" + Environment.NewLine + "FieldType[1] = Point" + Environment.NewLine + " redmond, p.X = 3, p.Y = 3, p.Distance() = 5" + Environment.NewLine + " bellevue, p.X = 6, p.Y = 6, p.Distance() = 10" + Environment.NewLine + " seattle, p.X = 10, p.Y = 10, p.Distance() = 14.8660687473185" + Environment.NewLine + " portland, p.X = 20, p.Y = 20, p.Distance() = 25" + Environment.NewLine + " LA, p.X = 3, p.Y = 3, p.Distance() = 5" + Environment.NewLine + " SFO, p.X = 6, p.Y = 6, p.Distance() = 10" + Environment.NewLine + " beaverton, p.X = 10, p.Y = 10, p.Distance() = 14.8660687473185" + Environment.NewLine + " new york, p.X = 20, p.Y = 20, p.Distance() = 25" + Environment.NewLine + " yukon, p.X = 20, p.Y = 20, p.Distance() = 32.0156211871642" + Environment.NewLine; DataTestUtility.AssertEqualsWithDescription( expectedReaderValues, UdtTestHelpers.DumpReaderString(reader), "Unexpected reader values."); } } }
public void UDTFields_WrongType() { using (SqlConnection cn = new SqlConnection(_connStr)) using (SqlCommand cmd = new SqlCommand("select name,location from cities order by name", cn)) { cn.Open(); cmd.CommandType = CommandType.Text; using (SqlDataReader reader = cmd.ExecuteReader()) { reader.Read(); DataTestUtility.AssertEqualsWithDescription( "beaverton", reader.GetValue(0), "Unexpected reader value."); DataTestUtility.AssertEqualsWithDescription( "14.8660687473185", ((Point)reader.GetValue(1)).Distance().ToString(), "Unexpected distance value."); reader.Read(); // retrieve the UDT as a string DataTestUtility.AssertThrowsWrapper <InvalidCastException>( () => reader.GetString(1), "Unable to cast object of type 'System.Byte[]' to type 'System.String'."); } } }
public void Test_SingleDependency_Timeout() { Assert.True(SqlDependency.Start(_startConnectionString), "Failed to start listener."); try { // with resolution of 15 seconds, SqlDependency should fire timeout notification only after 45 seconds, leave 5 seconds gap from both sides. const int SqlDependencyTimerResolution = 15; // seconds const int testTimeSeconds = SqlDependencyTimerResolution * 3 - 5; const int minTimeoutEventInterval = testTimeSeconds - 1; const int maxTimeoutEventInterval = testTimeSeconds + SqlDependencyTimerResolution + 1; // create a new event every time to avoid mixing notification callbacks ManualResetEvent notificationReceived = new ManualResetEvent(false); DateTime startUtcTime; using (SqlConnection conn = new SqlConnection(_execConnectionString)) using (SqlCommand cmd = new SqlCommand("SELECT a, b, c FROM " + _tableName, conn)) { conn.Open(); // create SqlDependency with timeout SqlDependency dep = new SqlDependency(cmd, null, testTimeSeconds); dep.OnChange += delegate(object o, SqlNotificationEventArgs arg) { // notification of Timeout can arrive either from server or from client timer. Handle both situations here: SqlNotificationInfo info = arg.Info; if (info == SqlNotificationInfo.Unknown) { // server timed out before the client, replace it with Error to produce consistent output for trun info = SqlNotificationInfo.Error; } DataTestUtility.AssertEqualsWithDescription(SqlNotificationType.Change, arg.Type, "Unexpected Type value."); DataTestUtility.AssertEqualsWithDescription(SqlNotificationInfo.Error, arg.Info, "Unexpected Info value."); DataTestUtility.AssertEqualsWithDescription(SqlNotificationSource.Timeout, arg.Source, "Unexpected Source value."); notificationReceived.Set(); }; cmd.ExecuteReader(); startUtcTime = DateTime.UtcNow; } Assert.True( notificationReceived.WaitOne(TimeSpan.FromSeconds(maxTimeoutEventInterval), false), string.Format("Notification not received within the maximum timeout period of {0} seconds", maxTimeoutEventInterval)); // notification received in time, check that it is not too early TimeSpan notificationTime = DateTime.UtcNow - startUtcTime; Assert.True( notificationTime >= TimeSpan.FromSeconds(minTimeoutEventInterval), string.Format( "Notification was not expected before {0} seconds: received after {1} seconds", minTimeoutEventInterval, notificationTime.TotalSeconds)); } finally { Assert.True(SqlDependency.Stop(_startConnectionString), "Failed to stop listener."); } }
public void Reader_CircleLate() { using (SqlConnection conn = new SqlConnection(_connStr)) using (SqlCommand cmd = new SqlCommand("select * from circles", conn)) { conn.Open(); using (SqlDataReader reader = cmd.ExecuteReader()) { string expectedReaderValues = "ColumnName[0] = num" + Environment.NewLine + "DataType[0] = int" + Environment.NewLine + "FieldType[0] = System.Int32" + Environment.NewLine + "ColumnName[1] = def" + Environment.NewLine + "DataType[1] = UdtTestDb.dbo.Circle" + Environment.NewLine + "FieldType[1] = Circle" + Environment.NewLine + " 1, Center = 1,2" + Environment.NewLine + " 2, Center = 3,4" + Environment.NewLine + " 3, Center = 11,23" + Environment.NewLine + " 4, Center = 444,555" + Environment.NewLine + " 5, Center = 1,2" + Environment.NewLine + " 6, Center = 3,4" + Environment.NewLine + " 7, Center = 11,23" + Environment.NewLine + " 8, Center = 444,245" + Environment.NewLine; DataTestUtility.AssertEqualsWithDescription( expectedReaderValues, UdtTestHelpers.DumpReaderString(reader), "Unexpected reader values."); } } }
public static void TestCommandBuilder() { using (SqlConnection connection = new SqlConnection(DataTestUtility.TcpConnStr)) using (SqlCommandBuilder commandBuilder = new SqlCommandBuilder()) using (SqlCommand command = connection.CreateCommand()) { string identifier = "TestIdentifier"; string quotedIdentifier = commandBuilder.QuoteIdentifier(identifier); DataTestUtility.AssertEqualsWithDescription( "[TestIdentifier]", quotedIdentifier, "Unexpected QuotedIdentifier string."); string unquotedIdentifier = commandBuilder.UnquoteIdentifier(quotedIdentifier); DataTestUtility.AssertEqualsWithDescription( "TestIdentifier", unquotedIdentifier, "Unexpected UnquotedIdentifier string."); identifier = "identifier]withclosesquarebracket"; quotedIdentifier = commandBuilder.QuoteIdentifier(identifier); DataTestUtility.AssertEqualsWithDescription( "[identifier]]withclosesquarebracket]", quotedIdentifier, "Unexpected QuotedIdentifier string."); unquotedIdentifier = null; unquotedIdentifier = commandBuilder.UnquoteIdentifier(quotedIdentifier); DataTestUtility.AssertEqualsWithDescription( "identifier]withclosesquarebracket", unquotedIdentifier, "Unexpected UnquotedIdentifier string."); } }
/// <summary> /// Checks if an 'emancipated' internal connection is reclaimed when a new connection is opened AND we hit max pool size /// NOTE: 'emancipated' means that the internal connection's SqlConnection has fallen out of scope and has no references, but was not explicitly disposed\closed /// </summary> /// <param name="connectionString"></param> private static void ReclaimEmancipatedOnOpenTest(string connectionString) { string newConnectionString = (new SqlConnectionStringBuilder(connectionString) { MaxPoolSize = 1 }).ConnectionString; SqlConnection.ClearAllPools(); InternalConnectionWrapper internalConnection = CreateEmancipatedConnection(newConnectionString); ConnectionPoolWrapper connectionPool = internalConnection.ConnectionPool; GC.Collect(); GC.WaitForPendingFinalizers(); DataTestUtility.AssertEqualsWithDescription(1, connectionPool.ConnectionCount, "Wrong number of connections in the pool."); DataTestUtility.AssertEqualsWithDescription(0, connectionPool.FreeConnectionCount, "Wrong number of free connections in the pool."); using (SqlConnection connection = new SqlConnection(newConnectionString)) { connection.Open(); Assert.True(internalConnection.IsInternalConnectionOf(connection), "Connection has wrong internal connection"); Assert.True(connectionPool.ContainsConnection(connection), "Connection is in wrong connection pool"); } }
public static void TestDbConnection() { MockConnection connection = new MockConnection(); CancellationTokenSource source = new CancellationTokenSource(); // ensure OpenAsync() calls OpenAsync(CancellationToken.None) DataTestUtility.AssertEqualsWithDescription(ConnectionState.Closed, connection.State, "Connection state should have been marked as Closed"); connection.OpenAsync().Wait(); Assert.False(connection.CancellationToken.CanBeCanceled, "Default cancellation token should not be cancellable"); DataTestUtility.AssertEqualsWithDescription(ConnectionState.Open, connection.State, "Connection state should have been marked as Open"); connection.Close(); // Verify cancellationToken over-ride DataTestUtility.AssertEqualsWithDescription(ConnectionState.Closed, connection.State, "Connection state should have been marked as Closed"); connection.OpenAsync(source.Token).Wait(); DataTestUtility.AssertEqualsWithDescription(ConnectionState.Open, connection.State, "Connection state should have been marked as Open"); connection.Close(); // Verify exceptions are routed through task MockConnection connectionFail = new MockConnection() { Fail = true }; connectionFail.OpenAsync().ContinueWith((t) => { }, TaskContinuationOptions.OnlyOnFaulted).Wait(); connectionFail.OpenAsync(source.Token).ContinueWith((t) => { }, TaskContinuationOptions.OnlyOnFaulted).Wait(); // Verify base implementation does not call Open when passed an already cancelled cancellation token source.Cancel(); DataTestUtility.AssertEqualsWithDescription(ConnectionState.Closed, connection.State, "Connection state should have been marked as Closed"); connection.OpenAsync(source.Token).ContinueWith((t) => { }, TaskContinuationOptions.OnlyOnCanceled).Wait(); DataTestUtility.AssertEqualsWithDescription(ConnectionState.Closed, connection.State, "Connection state should have been marked as Closed"); }
public static void TestInitialCatalogStandardValues() { using (SqlConnection connection = new SqlConnection(DataTestUtility.TcpConnStr)) { string currentDb = connection.Database; SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(connection.ConnectionString); PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(builder); PropertyDescriptor descriptor = properties["InitialCatalog"]; DataTestUtility.AssertEqualsWithDescription( "SqlInitialCatalogConverter", descriptor.Converter.GetType().Name, "Unexpected TypeConverter type."); // GetStandardValues of this converter calls GetSchema("DATABASES") var dbNames = descriptor.Converter.GetStandardValues(new DescriptorContext(descriptor, builder)); HashSet <string> searchSet = new HashSet <string>(StringComparer.InvariantCultureIgnoreCase); foreach (string name in dbNames) { searchSet.Add(name); } // ensure master and current database exist there Assert.True(searchSet.Contains("master"), "Cannot find database: master."); Assert.True(searchSet.Contains(currentDb), $"Cannot find database: {currentDb}."); } }
public void UDTParams_InputOutput() { string spInsertCity = DataTestUtility.GetUniqueNameForSqlServer("spUdtTest2_InsertCity"); string tableName = DataTestUtility.GetUniqueNameForSqlServer("UdtTest2"); using (SqlConnection conn = new SqlConnection(_connStr)) { conn.Open(); SqlTransaction tx = conn.BeginTransaction(); SqlCommand cmd = conn.CreateCommand(); cmd.Transaction = tx; // create the table cmd.CommandText = "create table " + tableName + " (name sysname,location Point)"; cmd.ExecuteNonQuery(); // create sp cmd.CommandText = "create proc " + spInsertCity + "(@name sysname, @location Point OUTPUT)" + " AS insert into " + tableName + " values (@name, @location)"; cmd.ExecuteNonQuery(); try { cmd.CommandText = spInsertCity; cmd.CommandType = CommandType.StoredProcedure; SqlParameter pName = cmd.Parameters.Add("@name", SqlDbType.NVarChar, 20); SqlParameter p = cmd.Parameters.Add("@location", SqlDbType.Udt); Point pt = new Point(100, 100); p.UdtTypeName = "Point"; p.Direction = ParameterDirection.InputOutput; p.Value = pt; pName.Value = "newcity"; cmd.ExecuteNonQuery(); DataTestUtility.AssertEqualsWithDescription( "141.42135623731", ((Point)(p.Value)).Distance().ToString(), "Unexpected distance value."); DataTestUtility.AssertEqualsWithDescription( "141.42135623731", ((Point)(p.Value)).Distance().ToString(), "Unexpected distance value after reading out param again."); cmd.Parameters.Clear(); cmd.CommandType = CommandType.Text; cmd.CommandText = "select * from " + tableName; using (SqlDataReader reader = cmd.ExecuteReader()) { string expectedValue = " newcity, p.X = 100, p.Y = 100, p.Distance() = 141.42135623731" + Environment.NewLine; DataTestUtility.AssertEqualsWithDescription( expectedValue, UdtTestHelpers.DumpReaderString(reader, false), "Unexpected reader dump string."); } } finally { tx.Rollback(); } } }
public static void XmlTest() { string tempTable = "xml_" + Guid.NewGuid().ToString().Replace('-', '_'); string initStr = "create table " + tempTable + " (xml_col XML)"; string insertNormStr = "INSERT " + tempTable + " VALUES('<doc>Hello World</doc>')"; string insertParamStr = "INSERT " + tempTable + " VALUES(@x)"; string queryStr = "select * from " + tempTable; using (SqlConnection conn = new SqlConnection(DataTestUtility.TcpConnStr)) { conn.Open(); SqlCommand cmd = conn.CreateCommand(); cmd.CommandText = initStr; cmd.ExecuteNonQuery(); try { cmd.CommandText = insertNormStr; cmd.ExecuteNonQuery(); SqlCommand cmd2 = new SqlCommand(insertParamStr, conn); cmd2.Parameters.Add("@x", SqlDbType.Xml); XmlReader xr = XmlReader.Create("data.xml"); cmd2.Parameters[0].Value = new SqlXml(xr); cmd2.ExecuteNonQuery(); cmd.CommandText = queryStr; using (SqlDataReader reader = cmd.ExecuteReader()) { int currentValue = 0; string[] expectedValues = { "<doc>Hello World</doc>", "<NewDataSet><builtinCLRtypes><colsbyte>1</colsbyte><colbyte>2</colbyte><colint16>-20</colint16><coluint16>40</coluint16><colint32>-300</colint32><coluint32>300</coluint32><colint64>-4000</colint64><coluint64>4000</coluint64><coldecimal>50000.01</coldecimal><coldouble>600000.987</coldouble><colsingle>70000.9</colsingle><colstring>string variable</colstring><colboolean>true</colboolean><coltimespan>P10675199DT2H48M5.4775807S</coltimespan><coldatetime>9999-12-30T23:59:59.9999999-08:00</coldatetime><colGuid>00000001-0002-0003-0405-060708010101</colGuid><colbyteArray>AQIDBAUGBwgJCgsMDQ4PEA==</colbyteArray><colUri>http://www.abc.com/</colUri><colobjectsbyte xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"byte\">1</colobjectsbyte><colobjectbyte xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"unsignedByte\">2</colobjectbyte><colobjectint16 xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"short\">-20</colobjectint16><colobjectuint16 xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"unsignedShort\">40</colobjectuint16><colobjectint32 xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"int\">-300</colobjectint32><colobjectuint32 xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"unsignedInt\">300</colobjectuint32><colobjectint64 xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"long\">-4000</colobjectint64><colobjectuint64 xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"unsignedLong\">4000</colobjectuint64><colobjectdecimal xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"decimal\">50000.01</colobjectdecimal><colobjectdouble xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"double\">600000.987</colobjectdouble><colobjectsingle xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"float\">70000.9</colobjectsingle><colobjectstring xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"string\">string variable</colobjectstring><colobjectboolean xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"boolean\">true</colobjectboolean><colobjecttimespan xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"duration\">P10675199DT2H48M5.4775807S</colobjecttimespan><colobjectdatetime xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"dateTime\">9999-12-30T23:59:59.9999999-08:00</colobjectdatetime><colobjectguid xmlns:msdata=\"urn:schemas-microsoft-com:xml-msdata\" msdata:InstanceType=\"System.Guid\">00000001-0002-0003-0405-060708010101</colobjectguid><colobjectbytearray xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"base64Binary\">AQIDBAUGBwgJCgsMDQ4PEA==</colobjectbytearray><colobjectUri xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"anyURI\">http://www.abc.com/</colobjectUri></builtinCLRtypes><builtinCLRtypes><colbyte>2</colbyte><colint16>-20</colint16><colint32>-300</colint32><coluint32>300</coluint32><coluint64>4000</coluint64><coldecimal>50000.01</coldecimal><coldouble>600000.987</coldouble><colsingle>70000.9</colsingle><colboolean>true</colboolean><colobjectsbyte xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"byte\">11</colobjectsbyte><colobjectbyte xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"unsignedByte\">22</colobjectbyte><colobjectint16 xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"short\">-200</colobjectint16><colobjectuint16 xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"unsignedShort\">400</colobjectuint16><colobjectint32 xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"int\">-3000</colobjectint32><colobjectuint32 xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"unsignedInt\">3000</colobjectuint32><colobjectint64 xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"long\">-40000</colobjectint64><colobjectuint64 xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"unsignedLong\">40000</colobjectuint64><colobjectdecimal xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"decimal\">500000.01</colobjectdecimal><colobjectdouble xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"double\">6000000.987</colobjectdouble><colobjectsingle xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"float\">700000.9</colobjectsingle><colobjectstring xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"string\">string variable 2</colobjectstring><colobjectboolean xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"boolean\">false</colobjectboolean><colobjecttimespan xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"duration\">-P10675199DT2H48M5.4775808S</colobjecttimespan><colobjectdatetime xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"dateTime\">0001-01-01T00:00:00.0000000-08:00</colobjectdatetime><colobjectguid xmlns:msdata=\"urn:schemas-microsoft-com:xml-msdata\" msdata:InstanceType=\"System.Guid\">00000002-0001-0001-0807-060504030201</colobjectguid><colobjectbytearray xmlns:xsi=\"http://www.w3.org/2001/XMLSchema-instance\" xsi:type=\"base64Binary\">EA8ODQwLCgkIBwYFBAMCAQ==</colobjectbytearray></builtinCLRtypes></NewDataSet>" }; while (reader.Read()) { Assert.True(currentValue < expectedValues.Length, "ERROR: Received more values than expected"); SqlXml sx = reader.GetSqlXml(0); xr = sx.CreateReader(); xr.Read(); DataTestUtility.AssertEqualsWithDescription(expectedValues[currentValue++], xr.ReadOuterXml(), "FAILED: Did not receive expected data"); } } } finally { cmd.CommandText = "drop table " + tempTable; cmd.ExecuteNonQuery(); } } }
private static void OpenGoodConnection(string connectionString) { using (SqlConnection conn = new SqlConnection(connectionString)) { conn.Open(); DataTestUtility.AssertEqualsWithDescription(ConnectionState.Open, conn.State, "FAILED: Connection should be in open state"); } }
private static void GetFieldValueAsync <T>(MockDataReader reader, CancellationToken cancellationToken, int ordinal, T expected) { Task <T> result = reader.GetFieldValueAsync <T>(ordinal, cancellationToken); result.Wait(); DataTestUtility.AssertEqualsWithDescription("GetValue", reader.LastCommand, "Last command was not as expected"); DataTestUtility.AssertEqualsWithDescription(expected, result.Result, "GetFieldValueAsync did not return expected value"); }
public void Reader_LineEarly() { using (SqlConnection conn = new SqlConnection(_connStr)) using (SqlCommand cmd = new SqlCommand("select * from lines", conn)) { conn.Open(); using (SqlDataReader reader = cmd.ExecuteReader()) { Line l = null; Point p = null; int x = 0, y = 0; double length = 0; string expectedReaderValues = "ids (int);pos (UdtTestDb.dbo.Line);"; StringBuilder builder = new StringBuilder(); for (int i = 0; i < reader.FieldCount; i++) { builder.Append(reader.GetName(i) + " (" + reader.GetDataTypeName(i) + ");"); } DataTestUtility.AssertEqualsWithDescription( expectedReaderValues, builder.ToString(), "Unexpected reader values."); string expectedLineValues = "1, IsNull = False, Length = 2.82842712474619" + Environment.NewLine + "2, IsNull = False, Length = 2.82842712474619" + Environment.NewLine + "3, IsNull = False, Length = 9.8488578017961" + Environment.NewLine + "4, IsNull = False, Length = 214.107449660212" + Environment.NewLine + "5, IsNull = False, Length = 2.82842712474619" + Environment.NewLine + "6, IsNull = False, Length = 2.82842712474619" + Environment.NewLine + "7, IsNull = False, Length = 9.8488578017961" + Environment.NewLine + "8, IsNull = False, Length = 214.107449660212" + Environment.NewLine; builder = new StringBuilder(); while (reader.Read()) { builder.Append(reader.GetValue(0).ToString() + ", "); l = (Line)reader.GetValue(1); if (!l.IsNull) { p = l.Start; x = p.X; y = p.Y; length = l.Length(); } builder.Append("IsNull = " + l.IsNull + ", "); builder.Append("Length = " + length); builder.AppendLine(); } DataTestUtility.AssertEqualsWithDescription( expectedLineValues, builder.ToString(), "Unexpected Line values."); } } }
private void VerifyDataSet(DataSet ds, Utf8String[] expectedValues) { DataTestUtility.AssertEqualsWithDescription(1, ds.Tables.Count, "Unexpected tables count."); DataTestUtility.AssertEqualsWithDescription(ds.Tables[0].Rows.Count, expectedValues.Length, "Unexpected rows count."); for (int i = 0; i < ds.Tables[0].Rows.Count; i++) { DataTestUtility.AssertEqualsWithDescription(1, ds.Tables[0].Columns.Count, "Unexpected columns count."); DataTestUtility.AssertEqualsWithDescription(expectedValues[i], ds.Tables[0].Rows[i][0], "Unexpected value."); } }
private static void TestTransactionSingleCase(string caseName, string connectionString, TransactionTestType testType) { WeakReference weak = null; using (SqlConnection con = new SqlConnection(connectionString)) { con.Open(); SqlTransaction gch = null; if ((testType != TransactionTestType.TransactionGC) && (testType != TransactionTestType.TransactionGCConnectionClose)) { gch = con.BeginTransaction(); } switch (testType) { case TransactionTestType.TransactionRollback: gch.Rollback(); break; case TransactionTestType.TransactionDispose: gch.Dispose(); break; case TransactionTestType.TransactionGC: weak = OpenNullifyTransaction(con); GC.Collect(); GC.WaitForPendingFinalizers(); Assert.False(weak.IsAlive, "Transaction is still alive on TestTransactionSingle: TransactionGC"); break; case TransactionTestType.ConnectionClose: GC.SuppressFinalize(gch); con.Close(); con.Open(); break; case TransactionTestType.TransactionGCConnectionClose: weak = OpenNullifyTransaction(con); GC.Collect(); GC.WaitForPendingFinalizers(); Assert.False(weak.IsAlive, "Transaction is still alive on TestTransactionSingle: TransactionGCConnectionClose"); con.Close(); con.Open(); break; } using (SqlCommand cmd = con.CreateCommand()) { cmd.CommandText = "select @@trancount"; int tranCount = (int)cmd.ExecuteScalar(); DataTestUtility.AssertEqualsWithDescription(0, tranCount, "TransactionSingle Case " + caseName + " should return expected trans count"); } } }
public static void Test(string constr, string srctable, string dstTable) { using (SqlConnection dstConn = new SqlConnection(constr)) using (SqlCommand dstCmd = dstConn.CreateCommand()) { dstConn.Open(); try { // create the source table Helpers.TryExecute(dstCmd, "create table " + srctable + " (col1 int , col2 int, col3 text)"); Helpers.TryExecute(dstCmd, "insert into " + srctable + " values (33, 498, 'Michael')"); Helpers.TryExecute(dstCmd, "insert into " + srctable + " values (34, 499, 'Astrid')"); Helpers.TryExecute(dstCmd, "insert into " + srctable + " values (65, 500, 'alles Käse')"); Helpers.TryExecute(dstCmd, "create table " + dstTable + " (col1 int primary key, col2 int CONSTRAINT CK_" + dstTable + " CHECK (col2 < 500), col3 text)"); using (SqlConnection srcConn = new SqlConnection(constr)) using (SqlCommand srcCmd = new SqlCommand("select * from " + srctable, srcConn)) { srcConn.Open(); using (DbDataReader reader = srcCmd.ExecuteReader()) { try { using (SqlBulkCopy bulkcopy = new SqlBulkCopy(dstConn, SqlBulkCopyOptions.CheckConstraints, null)) { bulkcopy.DestinationTableName = dstTable; SqlBulkCopyColumnMappingCollection ColumnMappings = bulkcopy.ColumnMappings; ColumnMappings.Add("col1", "col1"); ColumnMappings.Add("col2", "col2"); ColumnMappings.Add("col3", "col3"); bulkcopy.WriteToServer(reader); } } catch (SqlException sqlEx) { // Error 547 == The %ls statement conflicted with the %ls constraint "%.*ls". DataTestUtility.AssertEqualsWithDescription(547, sqlEx.Number, "Unexpected error number."); } } } } finally { Helpers.TryExecute(dstCmd, "drop table " + dstTable); Helpers.TryExecute(dstCmd, "drop table " + srctable); } } }
public static void Test(string srcConstr, string dstConstr, string dstTable) { using (SqlConnection dstConn = new SqlConnection(dstConstr)) using (SqlCommand dstCmd = dstConn.CreateCommand()) { dstConn.Open(); try { Helpers.TryExecute(dstCmd, "create table " + dstTable + " (col1 int, col2 nvarchar(20), col3 nvarchar(10))"); using (SqlConnection srcConn = new SqlConnection(srcConstr)) using (SqlCommand srcCmd = srcConn.CreateCommand()) { srcConn.Open(); using (SqlBulkCopy bulkcopy = new SqlBulkCopy(dstConn)) { bulkcopy.DestinationTableName = dstTable; srcCmd.CommandText = "select EmployeeID, LastName from employees where LastName < 'E%'"; using (DbDataReader reader = srcCmd.ExecuteReader()) { bulkcopy.WriteToServer(reader); } DataTestUtility.AssertEqualsWithDescription(0, bulkcopy.ColumnMappings.Count, "Unexpected ColumnMappings count."); srcCmd.CommandText = "select EmployeeID, LastName, FirstName from employees where LastName > 'D%'"; using (DbDataReader reader = srcCmd.ExecuteReader()) { bulkcopy.WriteToServer(reader); } DataTestUtility.AssertEqualsWithDescription(0, bulkcopy.ColumnMappings.Count, "Unexpected ColumnMappings count."); srcCmd.CommandText = "select EmployeeID, FirstName from employees where LastName < 'E%'"; using (DbDataReader reader = srcCmd.ExecuteReader()) { bulkcopy.WriteToServer(reader); } DataTestUtility.AssertEqualsWithDescription(0, bulkcopy.ColumnMappings.Count, "Unexpected ColumnMappings count."); Helpers.VerifyResults(dstConn, dstTable, 3, 15); } } } finally { Helpers.TryExecute(dstCmd, "drop table " + dstTable); } } }
public static bool CheckTableRows(DbConnection conn, string table, bool shouldHaveRows) { string query = "select * from " + table; using (DbCommand cmd = conn.CreateCommand()) { cmd.CommandText = query; using (DbDataReader reader = cmd.ExecuteReader()) { DataTestUtility.AssertEqualsWithDescription(shouldHaveRows, reader.HasRows, "Unexpected value for HasRows."); } } return(false); }
public static void Test(string dstConstr, string dstTable) { using (SqlConnection dstConn = new SqlConnection(dstConstr)) using (SqlCommand dstCmd = dstConn.CreateCommand()) { dstConn.Open(); Helpers.TryExecute(dstCmd, "create table " + dstTable + " (name_jp varchar(20) collate Japanese_CI_AS, " + "name_ru varchar(20) collate Cyrillic_General_CI_AS)"); string s_jp = "江戸糸あやつり人形"; string s_ru = "проверка"; DataTable table = new DataTable(); table.Columns.Add("name_jp", typeof(string)); table.Columns.Add("name_ru", typeof(string)); DataRow row = table.NewRow(); row["name_jp"] = s_jp; row["name_ru"] = s_ru; table.Rows.Add(row); using (SqlBulkCopy bcp = new SqlBulkCopy(dstConn)) { bcp.DestinationTableName = dstTable; bcp.WriteToServer(table); } using (SqlDataReader reader = (new SqlCommand("select * from " + dstTable, dstConn)).ExecuteReader()) { while (reader.Read()) { DataTestUtility.AssertEqualsWithDescription( 0, string.CompareOrdinal(s_jp, reader["name_jp"] as string), "Unexpected value: " + reader["name_jp"]); DataTestUtility.AssertEqualsWithDescription( 0, string.CompareOrdinal(s_ru, reader["name_ru"] as string), "Unexpected value: " + reader["name_ru"]); } } } using (SqlConnection dstConn = new SqlConnection(dstConstr)) using (SqlCommand dstCmd = dstConn.CreateCommand()) { dstConn.Open(); Helpers.TryExecute(dstCmd, "drop table " + dstTable); } }
public void ExecuteScalarTest() { using (SqlConnection conn = new SqlConnection(_connStr)) { conn.Open(); SqlCommand com = new SqlCommand() { Connection = conn, CommandText = "select * from TestTable" }; DataTestUtility.AssertEqualsWithDescription(new Utf8String("a"), com.ExecuteScalar(), "Unexpected value."); } }
public void InputParameterTest() { using (SqlConnection conn = new SqlConnection(_connStr)) { conn.Open(); SqlCommand com = new SqlCommand() { Connection = conn, CommandText = "insert into TestTable values (@p);" + "SELECT * FROM TestTable" }; SqlParameter p = com.Parameters.Add("@p", SqlDbType.Udt); p.UdtTypeName = "Utf8String"; p.Value = new Utf8String("this is an input param test"); using (SqlTransaction trans = conn.BeginTransaction()) { com.Transaction = trans; SqlDataReader reader = com.ExecuteReader(); Utf8String[] expectedValues = { new Utf8String("a"), new Utf8String("is"), new Utf8String("test"), new Utf8String("this"), new Utf8String("this is an input param test") }; int currentValue = 0; do { while (reader.Read()) { DataTestUtility.AssertEqualsWithDescription(1, reader.FieldCount, "Unexpected FieldCount."); DataTestUtility.AssertEqualsWithDescription(expectedValues[currentValue], reader.GetValue(0), "Unexpected Value."); currentValue++; } }while (reader.NextResult()); DataTestUtility.AssertEqualsWithDescription(expectedValues.Length, currentValue, "Received less values than expected."); reader.Close(); } } }
private static void OnRowCopied(object sender, SqlRowsCopiedEventArgs e) { Assert.True(currentRowCopyResult < ExpectedRowCopiedResults.Length, "More row copies than expected!"); DataTestUtility.AssertEqualsWithDescription(ExpectedRowCopiedResults[currentRowCopyResult++], e.RowsCopied, "Unexpected Rows Copied count."); if (e.RowsCopied > 50) { e.Abort = true; // Abort batch } else if (e.RowsCopied > 60) { bulkcopy.NotifyAfter = 0; // switch off notification } else if (e.RowsCopied > 3) { bulkcopy.NotifyAfter = 10; // decrease notification frequency } }
public static bool VerifyResults(DbConnection conn, string dstTable, int expectedColumns, int expectedRows) { using (DbCommand cmd = conn.CreateCommand()) { cmd.CommandText = "select * from " + dstTable + "; select count(*) from " + dstTable; using (DbDataReader reader = cmd.ExecuteReader()) { int numColumns = reader.FieldCount; reader.NextResult(); reader.Read(); int numRows = (int)reader[0]; reader.Close(); DataTestUtility.AssertEqualsWithDescription(expectedColumns, numColumns, "Unexpected number of columns."); DataTestUtility.AssertEqualsWithDescription(expectedRows, numRows, "Unexpected number of columns."); } } return(false); }
public void UDTParams_TypedNull() { string spInsertCustomer = DataTestUtility.GetUniqueNameForSqlServer("spUdtTest2_InsertCustomer"); string tableName = DataTestUtility.GetUniqueNameForSqlServer("UdtTest2_Customer"); using (SqlConnection conn = new SqlConnection(_connStr)) using (SqlCommand cmd = conn.CreateCommand()) { conn.Open(); cmd.Transaction = conn.BeginTransaction(); cmd.CommandText = "create table " + tableName + " (name nvarchar(30), address Address)"; cmd.ExecuteNonQuery(); // create proc sp_insert_customer(@name nvarchar(30), @addr Address OUTPUT) // AS // insert into customers values (@name, @addr) cmd.CommandText = "create proc " + spInsertCustomer + " (@name nvarchar(30), @addr Address OUTPUT)" + " AS insert into " + tableName + " values (@name, @addr)"; cmd.ExecuteNonQuery(); try { cmd.CommandText = spInsertCustomer; cmd.CommandType = CommandType.StoredProcedure; Address addr = Address.Parse("123 baker st || Redmond"); SqlParameter pName = cmd.Parameters.Add("@name", SqlDbType.NVarChar, 20); SqlParameter p = cmd.Parameters.Add("@addr", SqlDbType.Udt); p.UdtTypeName = "UdtTestDb.dbo.Address"; p.Value = Address.Null; pName.Value = "john"; cmd.ExecuteNonQuery(); DataTestUtility.AssertEqualsWithDescription( Address.Null.ToString(), p.Value.ToString(), "Unexpected parameter value."); } finally { cmd.Transaction.Rollback(); } } }
public void Test_SingleDependency_AllDefaults_SqlAuth() { Assert.True(SqlDependency.Start(_startConnectionString), "Failed to start listener."); try { // create a new event every time to avoid mixing notification callbacks ManualResetEvent notificationReceived = new ManualResetEvent(false); ManualResetEvent updateCompleted = new ManualResetEvent(false); using (SqlConnection conn = new SqlConnection(_execConnectionString)) using (SqlCommand cmd = new SqlCommand("SELECT a, b, c FROM " + _tableName, conn)) { conn.Open(); SqlDependency dep = new SqlDependency(cmd); dep.OnChange += delegate(object o, SqlNotificationEventArgs arg) { Assert.True(updateCompleted.WaitOne(CALLBACK_TIMEOUT, false), "Received notification, but update did not complete."); DataTestUtility.AssertEqualsWithDescription(SqlNotificationType.Change, arg.Type, "Unexpected Type value."); DataTestUtility.AssertEqualsWithDescription(SqlNotificationInfo.Update, arg.Info, "Unexpected Info value."); DataTestUtility.AssertEqualsWithDescription(SqlNotificationSource.Data, arg.Source, "Unexpected Source value."); notificationReceived.Set(); }; cmd.ExecuteReader(); } int count = RunSQL("UPDATE " + _tableName + " SET c=" + Environment.TickCount); DataTestUtility.AssertEqualsWithDescription(1, count, "Unexpected count value."); updateCompleted.Set(); Assert.True(notificationReceived.WaitOne(CALLBACK_TIMEOUT, false), "Notification not received within the timeout period"); } finally { Assert.True(SqlDependency.Stop(_startConnectionString), "Failed to stop listener."); } }
/// <summary> /// Tests if clearing all of the pools does actually remove the pools /// </summary> /// <param name="connectionString"></param> private static void ClearAllPoolsTest(string connectionString) { SqlConnection.ClearAllPools(); Assert.True(0 == ConnectionPoolWrapper.AllConnectionPools().Length, "Pools exist after clearing all pools"); SqlConnection connection = new SqlConnection(connectionString); connection.Open(); ConnectionPoolWrapper pool = new ConnectionPoolWrapper(connection); connection.Close(); ConnectionPoolWrapper[] allPools = ConnectionPoolWrapper.AllConnectionPools(); DataTestUtility.AssertEqualsWithDescription(1, allPools.Length, "Incorrect number of pools exist."); Assert.True(allPools[0].Equals(pool), "Saved pool is not in the list of all pools"); DataTestUtility.AssertEqualsWithDescription(1, pool.ConnectionCount, "Saved pool has incorrect number of connections"); SqlConnection.ClearAllPools(); Assert.True(0 == ConnectionPoolWrapper.AllConnectionPools().Length, "Pools exist after clearing all pools"); DataTestUtility.AssertEqualsWithDescription(0, pool.ConnectionCount, "Saved pool has incorrect number of connections."); }