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.");
}