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.Execute(dstCmd, "create table " + dstTable + " (col1 int, col2 nvarchar(20), col3 nvarchar(10))");
using (SqlConnection srcConn = new SqlConnection(srcConstr))
using (SqlCommand srcCmd = new SqlCommand("select top 5 EmployeeID, LastName, FirstName from employees", srcConn))
{
srcConn.Open();
using (DbDataReader reader = srcCmd.ExecuteReader())
using (SqlBulkCopy bulkcopy = new SqlBulkCopy(dstConn, SqlBulkCopyOptions.UseInternalTransaction, null))
{
bulkcopy.DestinationTableName = dstTable;
SqlBulkCopyColumnMappingCollection ColumnMappings = bulkcopy.ColumnMappings;
SqlTransaction myTrans = dstConn.BeginTransaction();
try
{
DataTestUtility.AssertThrowsWrapper <InvalidOperationException>(() => bulkcopy.WriteToServer(reader));
}
finally
{
myTrans.Rollback();
}
}
}
}
finally
{
Helpers.TryExecute(dstCmd, "drop table " + dstTable);
}
}
}
/// <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 = connectionString + ";Max Pool Size=1";
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 GetSchemaTableTest()
{
using (SqlConnection conn = new SqlConnection(DataTestUtility.TcpConnStr))
using (SqlCommand cmd = new SqlCommand("select hierarchyid::Parse('/1/') as col0", conn))
{
conn.Open();
using (SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.KeyInfo))
{
DataTable schemaTable = reader.GetSchemaTable();
DataTestUtility.AssertEqualsWithDescription(1, schemaTable.Rows.Count, "Unexpected schema table row count.");
string columnName = (string)(string)schemaTable.Rows[0][schemaTable.Columns["ColumnName"]];
DataTestUtility.AssertEqualsWithDescription("col0", columnName, "Unexpected column name.");
string dataTypeName = (string)schemaTable.Rows[0][schemaTable.Columns["DataTypeName"]];
DataTestUtility.AssertEqualsWithDescription("Northwind.sys.hierarchyid", dataTypeName, "Unexpected data type name.");
string udtAssemblyName = (string)schemaTable.Rows[0][schemaTable.Columns["UdtAssemblyQualifiedName"]];
Assert.True(udtAssemblyName?.StartsWith("Microsoft.SqlServer.Types.SqlHierarchyId"), "Unexpected UDT assembly name: " + udtAssemblyName);
}
}
}
private void CopyTableTest(string connStr, string sourceTable, string targetTable, string expectedResults)
{
using (SqlConnection srcConn = new SqlConnection(connStr))
{
srcConn.Open();
SqlCommand cmd = srcConn.CreateCommand();
cmd.CommandText = "select * from " + sourceTable;
using (SqlDataReader reader = cmd.ExecuteReader())
using (SqlBulkCopy bc = new SqlBulkCopy(connStr))
{
bc.DestinationTableName = targetTable;
bc.WriteToServer(reader);
}
cmd.CommandText = "select * from " + targetTable;
DataTestUtility.AssertEqualsWithDescription(
expectedResults, UdtTestHelpers.DumpReaderString(cmd.ExecuteReader()),
"Unexpected bulk copy results.");
}
}
public void Test_SingleDependency_CustomQueue_SqlAuth()
{
Assert.True(SqlDependency.Start(_startConnectionString, _queueName), "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, "service=" + _serviceName + ";local database=msdb", 0);
dep.OnChange += delegate(object o, SqlNotificationEventArgs args)
{
Assert.True(updateCompleted.WaitOne(CALLBACK_TIMEOUT, false), "Received notification, but update did not complete.");
Console.WriteLine("7 Notification callback. Type={0}, Info={1}, Source={2}", args.Type, args.Info, args.Source);
notificationReceived.Set();
};
cmd.ExecuteReader();
}
int count = RunSQL("UPDATE " + _tableName + " SET c=" + Environment.TickCount);
DataTestUtility.AssertEqualsWithDescription(1, count, "Unexpected count value.");
updateCompleted.Set();
Assert.False(notificationReceived.WaitOne(CALLBACK_TIMEOUT, false), "Notification should not be received.");
}
finally
{
Assert.True(SqlDependency.Stop(_startConnectionString, _queueName), "Failed to stop listener.");
}
}
public void UDT_DataSetFill()
{
using (SqlConnection cn = new SqlConnection(_connStr))
using (SqlCommand cmd = new SqlCommand("select * from cities", cn))
using (SqlDataAdapter adapter = new SqlDataAdapter("select * from cities", cn))
{
cn.Open();
cmd.CommandType = CommandType.Text;
adapter.SelectCommand = cmd;
DataSet ds = new DataSet("newset");
adapter.Fill(ds);
DataTestUtility.AssertEqualsWithDescription(
1, ds.Tables.Count,
"Unexpected Tables count.");
DataTestUtility.AssertEqualsWithDescription(
typeof(Point), ds.Tables[0].Columns[1].DataType,
"Unexpected DataType.");
}
}
public void UDTParams_Invalid2()
{
string spInsertCustomer = DataTestUtility.GetUniqueNameForSqlServer("spUdtTest2_InsertCustomer");
string tableName = DataTestUtility.GetUniqueNameForSqlServer("UdtTest2");
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();
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;
SqlParameter pName = cmd.Parameters.Add("@fname", SqlDbType.NVarChar, 20);
SqlParameter p = cmd.Parameters.Add("@addr", SqlDbType.Udt);
Address addr = Address.Parse("customer whose name is address");
p.UdtTypeName = "UdtTestDb.dbo.Address";
p.Value = addr;
pName.Value = addr;
DataTestUtility.AssertThrowsWrapper <InvalidCastException>(
() => cmd.ExecuteReader(),
"Failed to convert parameter value from a Address to a String.");
}
finally
{
cmd.Transaction.Rollback();
}
}
}
private static void PlainCancelAsync(string connString)
{
using (SqlConnection conn = new SqlConnection(connString))
using (SqlCommand cmd = new SqlCommand("select * from dbo.Orders; waitfor delay '00:00:10'; select * from dbo.Orders", conn))
{
conn.Open();
Task <SqlDataReader> readerTask = cmd.ExecuteReaderAsync();
DataTestUtility.AssertThrowsWrapper <SqlException>(
() =>
{
readerTask.Wait(2000);
SqlDataReader reader = readerTask.Result;
cmd.Cancel();
do
{
while (reader.Read())
{
}
}while (reader.NextResult());
},
"A severe error occurred on the current command. The results, if any, should be discarded.");
}
}
private static void ExecuteCommandCancelExpected(object state)
{
var stateTuple = (Tuple <bool, SqlCommand, Barrier>)state;
bool async = stateTuple.Item1;
SqlCommand command = stateTuple.Item2;
Barrier threadsReady = stateTuple.Item3;
string errorMessage = SystemDataResourceManager.Instance.SQL_OperationCancelled;
DataTestUtility.ExpectFailure <SqlException>(() =>
{
threadsReady.SignalAndWait();
using (SqlDataReader r = command.ExecuteReader())
{
do
{
while (r.Read())
{
}
} while (r.NextResult());
}
}, errorMessage);
}
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.Execute(dstCmd, "create table " + dstTable + " (col1 int, col3 nvarchar(10))");
using (SqlConnection srcConn = new SqlConnection(srcConstr))
using (SqlCommand srcCmd = new SqlCommand("select top 5 EmployeeID, LastName, FirstName from employees", srcConn))
{
srcConn.Open();
using (DbDataReader reader = srcCmd.ExecuteReader())
using (SqlBulkCopy bulkcopy = new SqlBulkCopy(dstConn))
{
bulkcopy.DestinationTableName = dstTable;
SqlBulkCopyColumnMappingCollection ColumnMappings = bulkcopy.ColumnMappings;
ColumnMappings.Add("EmployeeID", "col1");
ColumnMappings.Add("LastName", "col2"); // this column does not exist
ColumnMappings.Add("FirstName", "col3");
string errorMsg = SystemDataResourceManager.Instance.SQL_BulkLoadNonMatchingColumnMapping;
DataTestUtility.AssertThrowsWrapper <InvalidOperationException>(() => bulkcopy.WriteToServer(reader), exceptionMessage: errorMsg);
}
}
}
finally
{
Helpers.Execute(dstCmd, "drop table " + dstTable);
}
}
}
private static SqlDecimal BulkCopySqlDecimalToTable(SqlDecimal decimalValue, int sourcePrecision, int sourceScale, int targetPrecision, int targetScale)
{
string tableName = DataTestUtility.GenerateTableName();
string connectionString = DataTestUtility.TcpConnStr;
SqlDecimal resultValue;
try
{
DataTestUtility.RunNonQuery(connectionString, $"create table {tableName} (target_column decimal({targetPrecision}, {targetScale}))");
SqlDecimal inputValue = SqlDecimal.ConvertToPrecScale(decimalValue, sourcePrecision, sourceScale);
DataTable dt = new DataTable();
dt.Clear();
dt.Columns.Add("source_column", typeof(SqlDecimal));
DataRow row = dt.NewRow();
row["source_column"] = inputValue;
dt.Rows.Add(row);
using (SqlBulkCopy sbc = new SqlBulkCopy(connectionString, SqlBulkCopyOptions.KeepIdentity))
{
sbc.DestinationTableName = tableName;
sbc.ColumnMappings.Add("source_column", "target_column");
sbc.WriteToServer(dt);
}
DataTable resultTable = DataTestUtility.RunQuery(connectionString, $"select * from {tableName}");
resultValue = new SqlDecimal((Decimal)resultTable.Rows[0][0]);
}
finally
{
DataTestUtility.RunNonQuery(connectionString, $"drop table {tableName}");
}
return(resultValue);
}
public void ReaderTest()
{
using (SqlConnection conn = new SqlConnection(_connStr))
{
conn.Open();
SqlCommand com = new SqlCommand()
{
Connection = conn,
CommandText = "select * from TestTable"
};
SqlDataReader reader = com.ExecuteReader();
Utf8String[] expectedValues =
{
new Utf8String("a"),
new Utf8String("is"),
new Utf8String("test"),
new Utf8String("this")
};
int currentValue = 0;
do
{
while (reader.Read())
{
DataTestUtility.AssertEqualsWithDescription(1, reader.FieldCount, "Unexpected FieldCount.");
DataTestUtility.AssertEqualsWithDescription(expectedValues[currentValue], reader.GetValue(0), "Unexpected Value.");
DataTestUtility.AssertEqualsWithDescription(expectedValues[currentValue], reader.GetSqlValue(0), "Unexpected SQL Value.");
currentValue++;
}
}while (reader.NextResult());
DataTestUtility.AssertEqualsWithDescription(expectedValues.Length, currentValue, "Received less values than expected.");
}
}
private static void TestCase_AutoEnlistment_TxScopeNonComplete()
{
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder(ConnectionString);
builder.Enlist = true;
ConnectionString = builder.ConnectionString;
using (TransactionScope txScope = new TransactionScope(TransactionScopeOption.Required, TimeSpan.MaxValue))
{
using (SqlConnection connection = new SqlConnection(ConnectionString))
{
connection.Open();
using (SqlCommand command = connection.CreateCommand())
{
command.CommandText = $"INSERT INTO {TestTableName} VALUES ({InputCol1}, '{InputCol2}')";
command.ExecuteNonQuery();
}
}
}
DataTable result = DataTestUtility.RunQuery(ConnectionString, $"select col2 from {TestTableName} where col1 = {InputCol1}");
Assert.True(result.Rows.Count == 0);
}
public void TestSqlUserDefinedAggregateAttributeMaxByteSize()
{
Func <int, SqlUserDefinedAggregateAttribute> create
= (size) => new SqlUserDefinedAggregateAttribute(Format.UserDefined)
{
MaxByteSize = size
};
SqlUserDefinedAggregateAttribute attribute1 = create(-1);
SqlUserDefinedAggregateAttribute attribute2 = create(0);
SqlUserDefinedAggregateAttribute attribute3 = create(SqlUserDefinedAggregateAttribute.MaxByteSizeValue);
string udtError = SystemDataResourceManager.Instance.SQLUDT_MaxByteSizeValue;
string errorMessage = (new ArgumentOutOfRangeException("MaxByteSize", 8001, udtError)).Message;
DataTestUtility.AssertThrowsWrapper <ArgumentOutOfRangeException>(
() => create(SqlUserDefinedAggregateAttribute.MaxByteSizeValue + 1),
errorMessage);
errorMessage = (new ArgumentOutOfRangeException("MaxByteSize", -2, udtError)).Message;
DataTestUtility.AssertThrowsWrapper <ArgumentOutOfRangeException>(
() => create(-2),
errorMessage);
}
public static void CodeCoverageSqlClient()
{
SqlParameterCollection opc = new SqlCommand().Parameters;
Assert.True(opc.Count == 0, string.Format("FAILED: Expected count: {0}. Actual count: {1}.", 0, opc.Count));
Assert.False(((IList)opc).IsReadOnly, "FAILED: Expected collection to NOT be read only.");
Assert.False(((IList)opc).IsFixedSize, "FAILED: Expected collection to NOT be fixed size.");
Assert.False(((IList)opc).IsSynchronized, "FAILED: Expected collection to NOT be synchronized.");
DataTestUtility.AssertEqualsWithDescription("Object", ((IList)opc).SyncRoot.GetType().Name, "FAILED: Incorrect SyncRoot Name");
{
string failValue;
DataTestUtility.AssertThrowsWrapper <IndexOutOfRangeException>(() => failValue = opc[0].ParameterName, "Invalid index 0 for this SqlParameterCollection with Count=0.");
DataTestUtility.AssertThrowsWrapper <IndexOutOfRangeException>(() => failValue = opc["@p1"].ParameterName, "An SqlParameter with ParameterName '@p1' is not contained by this SqlParameterCollection.");
}
DataTestUtility.AssertThrowsWrapper <ArgumentNullException>(() => opc.Add(null), "The SqlParameterCollection only accepts non-null SqlParameter type objects.");
opc.Add((object)new SqlParameter());
IEnumerator enm = opc.GetEnumerator();
Assert.True(enm.MoveNext(), "FAILED: Expected MoveNext to be true");
DataTestUtility.AssertEqualsWithDescription("Parameter1", ((SqlParameter)enm.Current).ParameterName, "FAILED: Incorrect ParameterName");
opc.Add(new SqlParameter());
DataTestUtility.AssertEqualsWithDescription("Parameter2", opc[1].ParameterName, "FAILED: Incorrect ParameterName");
opc.Add(new SqlParameter(null, null));
opc.Add(new SqlParameter(null, SqlDbType.Int));
DataTestUtility.AssertEqualsWithDescription("Parameter4", opc["Parameter4"].ParameterName, "FAILED: Incorrect ParameterName");
opc.Add(new SqlParameter("Parameter5", SqlDbType.NVarChar, 20));
opc.Add(new SqlParameter(null, SqlDbType.NVarChar, 20, "a"));
opc.RemoveAt(opc[3].ParameterName);
DataTestUtility.AssertEqualsWithDescription(-1, opc.IndexOf(null), "FAILED: Incorrect index for null value");
SqlParameter p = opc[0];
DataTestUtility.AssertThrowsWrapper <ArgumentException>(() => opc.Add((object)p), "The SqlParameter is already contained by another SqlParameterCollection.");
DataTestUtility.AssertThrowsWrapper <ArgumentException>(() => new SqlCommand().Parameters.Add(p), "The SqlParameter is already contained by another SqlParameterCollection.");
DataTestUtility.AssertThrowsWrapper <ArgumentNullException>(() => opc.Remove(null), "The SqlParameterCollection only accepts non-null SqlParameter type objects.");
string pname = p.ParameterName;
p.ParameterName = pname;
p.ParameterName = pname.ToUpper();
p.ParameterName = pname.ToLower();
p.ParameterName = "@p1";
p.ParameterName = pname;
opc.Clear();
opc.Add(p);
opc.Clear();
opc.AddWithValue("@p1", null);
DataTestUtility.AssertEqualsWithDescription(-1, opc.IndexOf(p.ParameterName), "FAILED: Incorrect index for parameter name");
opc[0] = p;
DataTestUtility.AssertEqualsWithDescription(0, opc.IndexOf(p.ParameterName), "FAILED: Incorrect index for parameter name");
Assert.True(opc.Contains(p.ParameterName), "FAILED: Expected collection to contain provided parameter.");
Assert.True(opc.Contains(opc[0]), "FAILED: Expected collection to contain provided parameter.");
opc[0] = p;
opc[p.ParameterName] = new SqlParameter(p.ParameterName, null);
opc[p.ParameterName] = new SqlParameter();
opc.RemoveAt(0);
new SqlCommand().Parameters.Clear();
new SqlCommand().Parameters.CopyTo(new object[0], 0);
Assert.False(new SqlCommand().Parameters.GetEnumerator().MoveNext(), "FAILED: Expected MoveNext to be false");
DataTestUtility.AssertThrowsWrapper <InvalidCastException>(() => new SqlCommand().Parameters.Add(0), "The SqlParameterCollection only accepts non-null SqlParameter type objects, not Int32 objects.");
DataTestUtility.AssertThrowsWrapper <InvalidCastException>(() => new SqlCommand().Parameters.Insert(0, 0), "The SqlParameterCollection only accepts non-null SqlParameter type objects, not Int32 objects.");
DataTestUtility.AssertThrowsWrapper <InvalidCastException>(() => new SqlCommand().Parameters.Remove(0), "The SqlParameterCollection only accepts non-null SqlParameter type objects, not Int32 objects.");
DataTestUtility.AssertThrowsWrapper <ArgumentException>(() => new SqlCommand().Parameters.Remove(new SqlParameter()), "Attempted to remove an SqlParameter that is not contained by this SqlParameterCollection.");
}
private static bool IsFileStreamEnvironmentSet() => DataTestUtility.IsFileStreamSetup();
private static bool IsLocalDBEnvironmentSet() => DataTestUtility.IsLocalDBInstalled();
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.");
}
public static void MaxTypesTest()
{
string tempTable = "max_" + Guid.NewGuid().ToString().Replace('-', '_');
string initStr = "create table " + tempTable + " (col1 varchar(max), col2 nvarchar(max), col3 varbinary(max))";
string insertNormStr = "INSERT " + tempTable + " VALUES('ASCIASCIASCIASCIASCIASCIThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first row', ";
insertNormStr += "N'This is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first row', ";
insertNormStr += "0x010100110011000111000111000011110000111100001111000001111100000111110000011111000001111100000111110000011111000001111100000111110000011111)";
string insertParamStr = "INSERT " + tempTable + " VALUES(@x, @y, @z)";
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.VarChar);
cmd2.Parameters.Add("@y", SqlDbType.NVarChar);
cmd2.Parameters.Add("@z", SqlDbType.VarBinary);
cmd2.Parameters[1].Value = "second line, Insert big, Insert Big, This is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second row";
cmd2.Parameters[1].Value += "This is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second row";
cmd2.Parameters[1].Value += "This is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second row";
cmd2.Parameters[1].Value += "This is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second row";
cmd2.Parameters[1].Value += "This is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second row";
cmd2.Parameters[1].Value += "This is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second row";
cmd2.Parameters[1].Value += "This is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second row";
cmd2.Parameters[1].Value += "This is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second row";
cmd2.Parameters[1].Value += "This is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second row";
cmd2.Parameters[1].Value += "This is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second row";
cmd2.Parameters[1].Value += "This is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second row";
cmd2.Parameters[1].Value += "This is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second row";
cmd2.Parameters[1].Value += "This is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second row";
cmd2.Parameters[1].Value += "This is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second row";
cmd2.Parameters[1].Value += "This is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second row";
cmd2.Parameters[1].Value += "This is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second row";
cmd2.Parameters[1].Value += "This is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second row";
cmd2.Parameters[1].Value += "This is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second row";
cmd2.Parameters[1].Value += "This is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second row";
cmd2.Parameters[1].Value += "This is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second row";
cmd2.Parameters[1].Value += "This is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second row";
cmd2.Parameters[1].Value += "This is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second row";
cmd2.Parameters[1].Value += "This is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second row";
cmd2.Parameters[1].Value += "This is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second row";
cmd2.Parameters[1].Value += "This is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second row";
byte[] bytes = new byte[2];
for (int i = 0; i < bytes.Length; ++i)
{
bytes[i] = 0xad;
}
cmd2.Parameters[2].Value = bytes;
cmd2.Parameters[0].Value = "This is second row ANSI value";
cmd2.ExecuteNonQuery();
cmd.CommandText = queryStr;
using (SqlDataReader reader = cmd.ExecuteReader())
{
int currentValue = 0;
string[][] expectedValues =
{
new string[]
{
"ASCIASCIASCIASCIASCIASCIThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first row",
"This is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first rowThis is the first row",
"010100110011000111000111000011110000111100001111000001111100000111110000011111000001111100000111110000011111000001111100000111110000011111"
},
new string[]
{
"This is second row ANSI value",
"second line, Insert big, Insert Big, This is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second rowThis is the second row",
"ADAD"
}
};
while (reader.Read())
{
Assert.True(currentValue < expectedValues.Length, "ERROR: Received more values than expected");
char[] stringResult = reader.GetSqlChars(0).Value;
DataTestUtility.AssertEqualsWithDescription(expectedValues[currentValue][0], new string(stringResult, 0, stringResult.Length), "FAILED: Did not receive expected data");
stringResult = reader.GetSqlChars(1).Value;
DataTestUtility.AssertEqualsWithDescription(expectedValues[currentValue][1], new string(stringResult, 0, stringResult.Length), "FAILED: Did not receive expected data");
byte[] bb = reader.GetSqlBytes(2).Value;
char[] cc = new char[bb.Length * 2];
ConvertBinaryToChar(bb, cc);
DataTestUtility.AssertEqualsWithDescription(expectedValues[currentValue][2], new string(cc, 0, cc.Length), "FAILED: Did not receive expected data");
currentValue++;
}
}
}
finally
{
cmd.CommandText = "drop table " + tempTable;
cmd.ExecuteNonQuery();
}
}
}
/// <summary>
/// Round trip sql_variant value using SqlBulkCopy.
/// </summary>
private static void SendVariantBulkCopy(object paramValue, string expectedTypeName, string expectedBaseTypeName)
{
string bulkCopyTableName = DataTestUtility.GetUniqueNameForSqlServer("bulkDest");
// Fetch reader using type.
using (SqlDataReader dr = GetReaderForVariant(paramValue, false))
{
using (SqlConnection connBulk = new SqlConnection(s_connStr))
{
connBulk.Open();
ExecuteSQL(connBulk, "create table dbo.{0} (f1 sql_variant)", bulkCopyTableName);
try
{
// Perform bulk copy to target.
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connBulk))
{
bulkCopy.BulkCopyTimeout = 60;
bulkCopy.BatchSize = 1;
bulkCopy.DestinationTableName = bulkCopyTableName;
bulkCopy.WriteToServer(dr);
}
// Verify target.
using (SqlCommand cmd = connBulk.CreateCommand())
{
cmd.CommandText = string.Format("select f1, sql_variant_property(f1,'BaseType') as BaseType from {0}", bulkCopyTableName);
using (SqlDataReader drVerify = cmd.ExecuteReader())
{
VerifyReader("SendVariantBulkCopy[SqlDataReader]", drVerify, expectedTypeName, expectedBaseTypeName);
}
}
// Truncate target table for next pass.
ExecuteSQL(connBulk, "truncate table {0}", bulkCopyTableName);
// Send using DataTable as source.
DataTable t = new DataTable();
t.Columns.Add("f1", typeof(object));
t.Rows.Add(new object[] { paramValue });
// Perform bulk copy to target.
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connBulk))
{
bulkCopy.BulkCopyTimeout = 60;
bulkCopy.BatchSize = 1;
bulkCopy.DestinationTableName = bulkCopyTableName;
bulkCopy.WriteToServer(t, DataRowState.Added);
}
// Verify target.
using (SqlCommand cmd = connBulk.CreateCommand())
{
cmd.CommandText = string.Format("select f1, sql_variant_property(f1,'BaseType') as BaseType from {0}", bulkCopyTableName);
using (SqlDataReader drVerify = cmd.ExecuteReader())
{
VerifyReader("SendVariantBulkCopy[DataTable]", drVerify, expectedTypeName, expectedBaseTypeName);
}
}
// Truncate target table for next pass.
ExecuteSQL(connBulk, "truncate table {0}", bulkCopyTableName);
// Send using DataRow as source.
DataRow[] rowToSend = t.Select();
// Perform bulk copy to target.
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connBulk))
{
bulkCopy.BulkCopyTimeout = 60;
bulkCopy.BatchSize = 1;
bulkCopy.DestinationTableName = bulkCopyTableName;
bulkCopy.WriteToServer(rowToSend);
}
// Verify target.
using (SqlCommand cmd = connBulk.CreateCommand())
{
cmd.CommandText = string.Format("select f1, sql_variant_property(f1,'BaseType') as BaseType from {0}", bulkCopyTableName);
using (SqlDataReader drVerify = cmd.ExecuteReader())
{
VerifyReader("SendVariantBulkCopy[DataRow]", drVerify, expectedTypeName, expectedBaseTypeName);
}
}
}
finally
{
// Cleanup target table.
ExecuteSQL(connBulk, "drop table {0}", bulkCopyTableName);
}
}
}
}
private static bool IsIntegratedSecurityEnvironmentSet() => DataTestUtility.IsIntegratedSecuritySetup();
public static void Test(string srcConstr, string dstConstr, string dstTable)
{
Debug.Assert((int)SqlBulkCopyOptions.UseInternalTransaction == 1 << 5, "Compiler screwed up the options");
dstTable = destinationTable != null ? destinationTable : dstTable;
string sourceQuery = string.Format(sourceQueryTemplate, sourceTable);
string initialQuery = string.Format(initialQueryTemplate, dstTable);
using (SqlConnection dstConn = new SqlConnection(dstConstr))
using (SqlCommand dstCmd = dstConn.CreateCommand())
{
dstConn.Open();
try
{
Helpers.TryExecute(dstCmd, initialQuery);
using (SqlConnection srcConn = new SqlConnection(srcConstr))
using (SqlCommand srcCmd = new SqlCommand(sourceQuery, srcConn))
{
srcConn.Open();
using (DbDataReader reader = srcCmd.ExecuteReader())
{
IDictionary stats;
using (SqlBulkCopy bulkcopy = new SqlBulkCopy(dstConn))
{
bulkcopy.DestinationTableName = dstTable;
dstConn.StatisticsEnabled = true;
bulkcopy.WriteToServer(reader);
dstConn.StatisticsEnabled = false;
stats = dstConn.RetrieveStatistics();
}
Helpers.VerifyResults(dstConn, dstTable, 3, 5);
Assert.True(0 < (long)stats["BytesReceived"], "BytesReceived is non-positive.");
Assert.True(0 < (long)stats["BytesSent"], "BytesSent is non-positive.");
Assert.True((long)stats["ConnectionTime"] >= (long)stats["ExecutionTime"], "Connection Time is less than Execution Time.");
Assert.True((long)stats["ExecutionTime"] >= (long)stats["NetworkServerTime"], "Execution Time is less than Network Server Time.");
DataTestUtility.AssertEqualsWithDescription((long)0, (long)stats["UnpreparedExecs"], "Non-zero UnpreparedExecs value: " + (long)stats["UnpreparedExecs"]);
DataTestUtility.AssertEqualsWithDescription((long)0, (long)stats["PreparedExecs"], "Non-zero PreparedExecs value: " + (long)stats["PreparedExecs"]);
DataTestUtility.AssertEqualsWithDescription((long)0, (long)stats["Prepares"], "Non-zero Prepares value: " + (long)stats["Prepares"]);
DataTestUtility.AssertEqualsWithDescription((long)0, (long)stats["CursorOpens"], "Non-zero CursorOpens value: " + (long)stats["CursorOpens"]);
DataTestUtility.AssertEqualsWithDescription((long)0, (long)stats["IduRows"], "Non-zero IduRows value: " + (long)stats["IduRows"]);
DataTestUtility.AssertEqualsWithDescription((long)4, stats["BuffersReceived"], "Unexpected BuffersReceived value.");
DataTestUtility.AssertEqualsWithDescription((long)3, stats["BuffersSent"], "Unexpected BuffersSent value.");
DataTestUtility.AssertEqualsWithDescription((long)0, stats["IduCount"], "Unexpected IduCount value.");
DataTestUtility.AssertEqualsWithDescription((long)3, stats["SelectCount"], "Unexpected SelectCount value.");
DataTestUtility.AssertEqualsWithDescription((long)3, stats["ServerRoundtrips"], "Unexpected ServerRoundtrips value.");
DataTestUtility.AssertEqualsWithDescription((long)4, stats["SelectRows"], "Unexpected SelectRows value.");
DataTestUtility.AssertEqualsWithDescription((long)2, stats["SumResultSets"], "Unexpected SumResultSets value.");
DataTestUtility.AssertEqualsWithDescription((long)0, stats["Transactions"], "Unexpected Transactions value.");
}
}
}
finally
{
Helpers.TryExecute(dstCmd, "drop table " + dstTable);
}
}
}
public static void TestMain()
{
string connstr = (new SqlConnectionStringBuilder(DataTestUtility.TcpConnStr)
{
MultipleActiveResultSets = true
}).ConnectionString;
string cmdText1 = "select * from Orders; select count(*) from Customers";
string cmdText2 = "select * from Customers; select count(*) from Orders";
using (var conn = new SqlConnection(connstr))
{
conn.Open();
using (SqlTransaction tran = conn.BeginTransaction())
{
SqlCommand cmd1 = conn.CreateCommand();
cmd1.Transaction = tran;
cmd1.CommandText = cmdText1;
using (SqlDataReader reader1 = cmd1.ExecuteReader())
{
SqlCommand cmd2 = conn.CreateCommand();
cmd2.Transaction = tran;
cmd2.CommandText = cmdText2;
using (SqlDataReader reader2 = cmd2.ExecuteReader())
{
int actualOrderCount = 0;
while (reader1.Read())
{
Assert.True(actualOrderCount <= expectedOrders.Length, "FAILED: Received more results than expected");
DataTestUtility.AssertEqualsWithDescription(expectedOrders[actualOrderCount], reader1.GetValue(0), "FAILED: Received wrong value in order query at row " + actualOrderCount);
actualOrderCount++;
}
reader1.NextResult();
reader1.Read();
int customerCount = (int)reader1.GetValue(0);
int actualCustomerCount = 0;
while (reader2.Read())
{
Assert.True(actualCustomerCount <= expectedCustomers.Length, "FAILED: Received more results than expected");
DataTestUtility.AssertEqualsWithDescription(expectedCustomers[actualCustomerCount], reader2.GetValue(0), "FAILED: Received wrong value in customer query at row " + actualCustomerCount);
actualCustomerCount++;
}
reader2.NextResult();
reader2.Read();
int orderCount = (int)reader2.GetValue(0);
DataTestUtility.AssertEqualsWithDescription(expectedCustomers.Length, customerCount, "FAILED: Count query returned incorrect Customer count");
DataTestUtility.AssertEqualsWithDescription(expectedOrders.Length, orderCount, "FAILED: Count query returned incorrect Order count");
}
}
cmd1.CommandText = "select @@trancount";
int tranCount = (int)cmd1.ExecuteScalar();
Assert.True(tranCount == 1, "FAILED: Expected a transaction count of 1, but actually received " + tranCount);
tran.Commit();
}
}
}
private static void TestReaderMarsCase(string caseName, string connectionString, ReaderTestType testType, ReaderVerificationType verificationType)
{
WeakReference weak = null;
SqlCommand[] cmd = new SqlCommand[CONCURRENT_COMMANDS];
SqlDataReader[] gch = new SqlDataReader[CONCURRENT_COMMANDS];
using (SqlConnection con = new SqlConnection(connectionString))
{
con.Open();
for (int i = 0; i < CONCURRENT_COMMANDS; i++)
{
cmd[i] = con.CreateCommand();
cmd[i].CommandText = COMMAND_TEXT_1;
if ((testType != ReaderTestType.ReaderGC) && (testType != ReaderTestType.ReaderGCConnectionClose))
{
gch[i] = cmd[i].ExecuteReader();
}
else
{
gch[i] = null;
}
}
for (int i = 0; i < CONCURRENT_COMMANDS; i++)
{
switch (testType)
{
case ReaderTestType.ReaderClose:
gch[i].Dispose();
break;
case ReaderTestType.ReaderDispose:
gch[i].Dispose();
break;
case ReaderTestType.ReaderGC:
weak = OpenNullifyReader(cmd[i]);
GC.Collect();
GC.WaitForPendingFinalizers();
Assert.False(weak.IsAlive, "Transaction is still alive on TestReaderMars: ReaderGC");
break;
case ReaderTestType.ConnectionClose:
GC.SuppressFinalize(gch[i]);
con.Close();
con.Open();
break;
case ReaderTestType.ReaderGCConnectionClose:
weak = OpenNullifyReader(cmd[i]);
GC.Collect();
GC.WaitForPendingFinalizers();
Assert.False(weak.IsAlive, "Transaction is still alive on TestReaderMars: ReaderGCConnectionClose");
con.Close();
con.Open();
break;
}
cmd[i].Dispose();
}
SqlCommand verificationCmd = con.CreateCommand();
switch (verificationType)
{
case ReaderVerificationType.ExecuteReader:
verificationCmd.CommandText = COMMAND_TEXT_2;
using (SqlDataReader rdr = verificationCmd.ExecuteReader())
{
rdr.Read();
DataTestUtility.AssertEqualsWithDescription(1, rdr.FieldCount, "Execute Reader should return expected Field count");
DataTestUtility.AssertEqualsWithDescription(COLUMN_NAME_2, rdr.GetName(0), "Execute Reader should return expected Field name");
}
break;
case ReaderVerificationType.ChangeDatabase:
con.ChangeDatabase(DATABASE_NAME);
DataTestUtility.AssertEqualsWithDescription(DATABASE_NAME, con.Database, "Change Database should return expected Database Name");
break;
case ReaderVerificationType.BeginTransaction:
verificationCmd.Transaction = con.BeginTransaction();
verificationCmd.CommandText = "select @@trancount";
int tranCount = (int)verificationCmd.ExecuteScalar();
DataTestUtility.AssertEqualsWithDescription(1, tranCount, "Begin Transaction should return expected Transaction count");
break;
}
verificationCmd.Dispose();
}
}
public static void XmlTest()
{
string connStr = DataTestUtility.TcpConnStr;
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(connStr))
{
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();
}
}
}
public void TestSchemaTable()
{
using (SqlConnection conn = new SqlConnection(_connStr))
using (SqlCommand cmd = new SqlCommand("select * from lines", conn))
{
conn.Open();
cmd.CommandType = CommandType.Text;
using (SqlDataReader reader = cmd.ExecuteReader())
{
DataTable t = reader.GetSchemaTable();
string expectedSchemaTableValues =
"ids, 0, 4, 10, 255, False, , , , ids, , , System.Int32, True, 8, , , False, False, False, , False, False, System.Data.SqlTypes.SqlInt32, int, , , , , 8, False, " + Environment.NewLine +
"pos, 1, 20, 255, 255, False, , , , pos, , , Line, True, 29, , , False, False, False, , False, False, Line, UdtTestDb.dbo.Line, , , , Line, Shapes, Version=1.2.0.0, Culture=neutral, PublicKeyToken=a3e3aa32e6a16344, 29, False, " + Environment.NewLine;
StringBuilder builder = new StringBuilder();
foreach (DataRow row in t.Rows)
{
foreach (DataColumn col in t.Columns)
{
builder.Append(row[col] + ", ");
}
builder.AppendLine();
}
DataTestUtility.AssertEqualsWithDescription(
expectedSchemaTableValues, builder.ToString(),
"Unexpected DataTable values from GetSchemaTable.");
string expectedReaderValues =
"ids1" + Environment.NewLine +
"pos1,2,3,4" + Environment.NewLine +
"ids2" + Environment.NewLine +
"pos3,4,5,6" + Environment.NewLine +
"ids3" + Environment.NewLine +
"pos11,23,15,32" + Environment.NewLine +
"ids4" + Environment.NewLine +
"pos444,555,245,634" + Environment.NewLine +
"ids5" + Environment.NewLine +
"pos1,2,3,4" + Environment.NewLine +
"ids6" + Environment.NewLine +
"pos3,4,5,6" + Environment.NewLine +
"ids7" + Environment.NewLine +
"pos11,23,15,32" + Environment.NewLine +
"ids8" + Environment.NewLine +
"pos444,555,245,634" + Environment.NewLine;
builder = new StringBuilder();
while (reader.Read())
{
for (int i = 0; i < reader.FieldCount; i++)
{
builder.Append(reader.GetName(i) + reader.GetValue(i).ToString());
builder.AppendLine();
}
}
DataTestUtility.AssertEqualsWithDescription(
expectedReaderValues, builder.ToString(),
"Unexpected Reader values.");
}
}
}
private static bool AreConnectionStringsSetup() => DataTestUtility.AreConnStringsSetup();
private static bool IsAzureServer() => DataTestUtility.IsAzureSqlServer(new SqlConnectionStringBuilder((DataTestUtility.TcpConnStr)).DataSource);
public static void TestMain()
{
string connectionString = DataTestUtility.TcpConnStr;
string tempTable = DataTestUtility.GetUniqueName("T", "[", "]");
string tempKey = DataTestUtility.GetUniqueName("K", "[", "]");
DbProviderFactory provider = SqlClientFactory.Instance;
try
{
using (DbConnection con = provider.CreateConnection())
{
con.ConnectionString = connectionString;
con.Open();
using (DbCommand cmd = provider.CreateCommand())
{
cmd.Connection = con;
DbTransaction tx;
#region <<Create temp table>>
cmd.CommandText = "SELECT au_id, au_lname, au_fname, phone, address, city, state, zip, contract into " + tempTable + " from authors where au_id='UNKNOWN-ID'";
cmd.ExecuteNonQuery();
cmd.CommandText = "alter table " + tempTable + " add constraint " + tempKey + " primary key (au_id)";
cmd.ExecuteNonQuery();
#endregion
tx = con.BeginTransaction();
cmd.Transaction = tx;
cmd.CommandText = "insert into " + tempTable + "(au_id, au_lname, au_fname, phone, address, city, state, zip, contract) values ('876-54-3210', 'Doe', 'Jane' , '882-8080', 'One Microsoft Way', 'Redmond', 'WA', '98052', 0)";
cmd.ExecuteNonQuery();
cmd.CommandText = "insert into " + tempTable + "(au_id, au_lname, au_fname, phone, address, city, state, zip, contract) values ('876-54-3211', 'Doe', 'John' , '882-8181', NULL, NULL, NULL, NULL, 0)";
cmd.ExecuteNonQuery();
tx.Commit();
cmd.Transaction = null;
string parameterName = "@p1";
DbParameter p1 = cmd.CreateParameter();
p1.ParameterName = parameterName;
p1.Value = "876-54-3210";
cmd.Parameters.Add(p1);
cmd.CommandText = "select * from " + tempTable + " where au_id >= " + parameterName;
// Test GetValue + IsDBNull
using (DbDataReader rdr = cmd.ExecuteReader())
{
StringBuilder actualResult = new StringBuilder();
int currentValue = 0;
string[] expectedValues =
{
"876-54-3210,Doe,Jane,882-8080 ,One Microsoft Way,Redmond,WA,98052,False",
"876-54-3211,Doe,John,882-8181 ,(NULL),(NULL),(NULL),(NULL),False"
};
while (rdr.Read())
{
Assert.True(currentValue < expectedValues.Length, "ERROR: Received more values than expected");
for (int i = 0; i < rdr.FieldCount; i++)
{
if (i > 0)
{
actualResult.Append(",");
}
if (rdr.IsDBNull(i))
{
actualResult.Append("(NULL)");
}
else
{
actualResult.Append(rdr.GetValue(i));
}
}
DataTestUtility.AssertEqualsWithDescription(expectedValues[currentValue++], actualResult.ToString(), "FAILED: Did not receive expected data");
actualResult.Clear();
}
}
// Test GetFieldValue<T> + IsDBNull
using (DbDataReader rdr = cmd.ExecuteReader())
{
StringBuilder actualResult = new StringBuilder();
int currentValue = 0;
string[] expectedValues =
{
"876-54-3210,Doe,Jane,882-8080 ,One Microsoft Way,Redmond,WA,98052,False",
"876-54-3211,Doe,John,882-8181 ,(NULL),(NULL),(NULL),(NULL),False"
};
while (rdr.Read())
{
Assert.True(currentValue < expectedValues.Length, "ERROR: Received more values than expected");
for (int i = 0; i < rdr.FieldCount; i++)
{
if (i > 0)
{
actualResult.Append(",");
}
if (rdr.IsDBNull(i))
{
actualResult.Append("(NULL)");
}
else
{
if (rdr.GetFieldType(i) == typeof(bool))
{
actualResult.Append(rdr.GetFieldValue <bool>(i));
}
else if (rdr.GetFieldType(i) == typeof(decimal))
{
actualResult.Append(rdr.GetFieldValue <decimal>(i));
}
else
{
actualResult.Append(rdr.GetFieldValue <string>(i));
}
}
}
DataTestUtility.AssertEqualsWithDescription(expectedValues[currentValue++], actualResult.ToString(), "FAILED: Did not receive expected data");
actualResult.Clear();
}
}
// Test GetFieldValueAsync<T> + IsDBNullAsync
using (DbDataReader rdr = cmd.ExecuteReaderAsync().Result)
{
StringBuilder actualResult = new StringBuilder();
int currentValue = 0;
string[] expectedValues =
{
"876-54-3210,Doe,Jane,882-8080 ,One Microsoft Way,Redmond,WA,98052,False",
"876-54-3211,Doe,John,882-8181 ,(NULL),(NULL),(NULL),(NULL),False"
};
while (rdr.ReadAsync().Result)
{
Assert.True(currentValue < expectedValues.Length, "ERROR: Received more values than expected");
for (int i = 0; i < rdr.FieldCount; i++)
{
if (i > 0)
{
actualResult.Append(",");
}
if (rdr.IsDBNullAsync(i).Result)
{
actualResult.Append("(NULL)");
}
else
{
if (rdr.GetFieldType(i) == typeof(bool))
{
actualResult.Append(rdr.GetFieldValueAsync <bool>(i).Result);
}
else if (rdr.GetFieldType(i) == typeof(decimal))
{
actualResult.Append(rdr.GetFieldValueAsync <decimal>(i).Result);
}
else
{
actualResult.Append(rdr.GetFieldValueAsync <string>(i).Result);
}
}
}
DataTestUtility.AssertEqualsWithDescription(expectedValues[currentValue++], actualResult.ToString(), "FAILED: Did not receive expected data");
actualResult.Clear();
}
}
}
// GetStream
byte[] correctBytes = { 0x12, 0x34, 0x56, 0x78 };
string queryString;
string correctBytesAsString = "0x12345678";
queryString = string.Format("SELECT CAST({0} AS BINARY(20)), CAST({0} AS IMAGE), CAST({0} AS VARBINARY(20))", correctBytesAsString);
using (var command = provider.CreateCommand())
{
command.CommandText = queryString;
command.Connection = con;
using (var reader = command.ExecuteReader())
{
reader.Read();
for (int i = 0; i < reader.FieldCount; i++)
{
byte[] buffer = new byte[256];
Stream stream = reader.GetStream(i);
int bytesRead = stream.Read(buffer, 0, buffer.Length);
for (int j = 0; j < correctBytes.Length; j++)
{
Assert.True(correctBytes[j] == buffer[j], "ERROR: Bytes do not match");
}
}
}
}
// GetTextReader
string[] correctStrings = { "Hello World", "\uFF8A\uFF9B\uFF70\uFF9C\uFF70\uFF99\uFF84\uFF9E" };
string[] collations = { "Latin1_General_CI_AS", "Japanese_CI_AS" };
for (int j = 0; j < collations.Length; j++)
{
string substring = string.Format("(N'{0}' COLLATE {1})", correctStrings[j], collations[j]);
queryString = string.Format("SELECT CAST({0} AS CHAR(20)), CAST({0} AS NCHAR(20)), CAST({0} AS NTEXT), CAST({0} AS NVARCHAR(20)), CAST({0} AS TEXT), CAST({0} AS VARCHAR(20))", substring);
using (var command = provider.CreateCommand())
{
command.CommandText = queryString;
command.Connection = con;
using (var reader = command.ExecuteReader())
{
reader.Read();
for (int i = 0; i < reader.FieldCount; i++)
{
char[] buffer = new char[256];
TextReader textReader = reader.GetTextReader(i);
int charsRead = textReader.Read(buffer, 0, buffer.Length);
string stringRead = new string(buffer, 0, charsRead);
Assert.True(stringRead == (string)reader.GetValue(i), "ERROR: Strings to not match");
}
}
}
}
}
}
finally
{
using (DbConnection con = provider.CreateConnection())
{
con.ConnectionString = connectionString;
con.Open();
using (DbCommand cmd = provider.CreateCommand())
{
cmd.Connection = con;
cmd.CommandText = "drop table " + tempTable;
cmd.ExecuteNonQuery();
}
}
}
}
private static bool IsAccessTokenSetup() => DataTestUtility.IsAccessTokenSetup();