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.");
}
}
private static string SetupFileStreamDB(ref string fileStreamDir, string connString)
{
try
{
if (fileStreamDir != null)
{
if (!fileStreamDir.EndsWith("\\"))
{
fileStreamDir += "\\";
}
string dbName = DataTestUtility.GetUniqueName("FS", false);
string createDBQuery = @$ "CREATE DATABASE [{dbName}]
ON PRIMARY
(NAME = PhotoLibrary_data,
FILENAME = '{fileStreamDir}PhotoLibrary_data.mdf'),
FILEGROUP FileStreamGroup CONTAINS FILESTREAM
(NAME = PhotoLibrary_blobs,
FILENAME = '{fileStreamDir}Photos')
LOG ON
(NAME = PhotoLibrary_log,
FILENAME = '{fileStreamDir}PhotoLibrary_log.ldf')";
public static void AccessTokenConnectionPoolingTest()
{
SqlConnection.ClearAllPools();
// Remove cred info and add invalid token
string[] credKeys = { "User ID", "Password", "UID", "PWD", "Authentication" };
string connectionString = DataTestUtility.RemoveKeysInConnStr(DataTestUtility.AADPasswordConnectionString, credKeys);
using SqlConnection connection = new SqlConnection(connectionString);
connection.AccessToken = DataTestUtility.GetAccessToken();
connection.Open();
InternalConnectionWrapper internalConnection = new InternalConnectionWrapper(connection);
ConnectionPoolWrapper connectionPool = new ConnectionPoolWrapper(connection);
connection.Close();
using SqlConnection connection2 = new SqlConnection(connectionString);
connection2.AccessToken = DataTestUtility.GetAccessToken();
connection2.Open();
Assert.True(internalConnection.IsInternalConnectionOf(connection2), "New connection does not use same internal connection");
Assert.True(connectionPool.ContainsConnection(connection2), "New connection is in a different pool");
connection2.Close();
using SqlConnection connection3 = new SqlConnection(connectionString + ";App=SqlConnectionPoolUnitTest;");
connection3.AccessToken = DataTestUtility.GetAccessToken();
connection3.Open();
Assert.False(internalConnection.IsInternalConnectionOf(connection3), "Connection with different connection string uses same internal connection");
Assert.False(connectionPool.ContainsConnection(connection3), "Connection with different connection string uses same connection pool");
connection3.Close();
connectionPool.Cleanup();
using SqlConnection connection4 = new SqlConnection(connectionString);
connection4.AccessToken = DataTestUtility.GetAccessToken();
connection4.Open();
Assert.True(internalConnection.IsInternalConnectionOf(connection4), "New connection does not use same internal connection");
Assert.True(connectionPool.ContainsConnection(connection4), "New connection is in a different pool");
connection4.Close();
}
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 static void GetSchemaTableTest()
{
using (SqlConnection conn = new SqlConnection(DataTestUtility.TCPConnectionString))
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);
}
}
}
public void ReaderTest()
{
using (SqlConnection conn = new SqlConnection(_connStr))
{
conn.Open();
using (SqlCommand com = new SqlCommand()
{
Connection = conn,
CommandText = "select * from TestTable"
})
using (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 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 static void TestCustomProviderAuthentication()
{
SqlAuthenticationProvider.SetProvider(SqlAuthenticationMethod.ActiveDirectoryPassword, new CustomSqlAuthenticationProvider(DataTestUtility.ApplicationClientId));
// Connect to Azure DB with password and retrieve user name using custom authentication provider
using (SqlConnection conn = new SqlConnection(DataTestUtility.AADPasswordConnectionString))
{
conn.Open();
using (SqlCommand sqlCommand = new SqlCommand
(
cmdText: $"SELECT SUSER_SNAME();",
connection: conn,
transaction: null
))
{
string customerId = (string)sqlCommand.ExecuteScalar();
string expected = DataTestUtility.RetrieveValueFromConnStr(DataTestUtility.AADPasswordConnectionString, new string[] { "User ID", "UID" });
Assert.Equal(expected, customerId);
}
}
// Reset to driver internal provider.
SqlAuthenticationProvider.SetProvider(SqlAuthenticationMethod.ActiveDirectoryPassword, new ActiveDirectoryAuthenticationProvider(DataTestUtility.ApplicationClientId));
}
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 = new SqlCommand("select top 5 * 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");
ColumnMappings.Add("FirstName", "col3");
bulkcopy.WriteToServer(reader);
DataTestUtility.AssertEqualsWithDescription(bulkcopy.RowsCopied, 5, "Unexpected number of rows.");
}
Helpers.VerifyResults(dstConn, dstTable, 3, 5);
}
}
}
finally
{
Helpers.TryExecute(dstCmd, "drop table " + dstTable);
}
}
}
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 PlainCancel(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();
using (SqlDataReader reader = cmd.ExecuteReader())
{
cmd.Cancel();
DataTestUtility.AssertThrowsWrapper <SqlException>(
() =>
{
do
{
while (reader.Read())
{
}
}while (reader.NextResult());
},
"A severe error occurred on the current command. The results, if any, should be discarded.");
}
}
}
public static void ConnectionKilledTest()
{
try
{
// Setup Database and Table.
DataTestUtility.RunNonQuery(s_connectionString, s_createDatabaseCmd);
DataTestUtility.RunNonQuery(s_dbConnectionString, s_createTableCmd);
// Kill all the connections and set Database to SINGLE_USER Mode.
DataTestUtility.RunNonQuery(s_connectionString, s_alterDatabaseSingleCmd, 4);
// Set Database back to MULTI_USER Mode
DataTestUtility.RunNonQuery(s_connectionString, s_alterDatabaseMultiCmd, 4);
// Execute SELECT statement.
DataTestUtility.RunNonQuery(s_dbConnectionString, s_selectTableCmd);
}
finally
{
// Kill all the connections, set Database to SINGLE_USER Mode and drop Database
DataTestUtility.RunNonQuery(s_connectionString, s_alterDatabaseSingleCmd, 4);
DataTestUtility.RunNonQuery(s_connectionString, s_dropDatabaseCmd, 4);
}
}
private static SqlDecimal BulkCopySqlDecimalToTable(SqlDecimal decimalValue, int sourcePrecision, int sourceScale, int targetPrecision, int targetScale)
{
string tableName = DataTestUtility.GetUniqueNameForSqlServer("Table");
string connectionString = DataTestUtility.TCPConnectionString;
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 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, 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.TryExecute(dstCmd, "drop table " + dstTable);
}
}
}
public void SqlAdapterTest()
{
string tableName = DataTestUtility.GetUniqueNameForSqlServer("Adapter");
string tableNameNoBrackets = tableName.Substring(1, tableName.Length - 2);
try
{
var createTableQuery = "IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='" + tableNameNoBrackets + "' AND xtype='U')" +
" CREATE TABLE [dbo]." + tableName + "([TransactionNumber][int] IDENTITY(1, 1) NOT NULL,[Level] [nvarchar] (50) NOT NULL," +
"[Message] [nvarchar] (500) NOT NULL,[EventTime] [datetime]NOT NULL,CONSTRAINT[" + "pk" + tableNameNoBrackets + "] " +
"PRIMARY KEY CLUSTERED([TransactionNumber] ASC)WITH(PAD_INDEX = OFF,STATISTICS_NORECOMPUTE = OFF, " +
"IGNORE_DUP_KEY = OFF,ALLOW_ROW_LOCKS = ON,ALLOW_PAGE_LOCKS = ON,FILLFACTOR = 90) ON[PRIMARY]) ON[PRIMARY]";
using (var connection = new SqlConnection(DataTestUtility.TCPConnectionString))
using (var cmd = new SqlCommand(createTableQuery, connection))
{
connection.Open();
cmd.ExecuteNonQuery();
}
ExecuteNonQueries(tableName);
}
catch (Exception ex)
{
Console.Error.WriteLine(ex.Message);
Console.Error.WriteLine(ex.StackTrace);
}
finally
{
var dropTableQuery = "DROP TABLE IF EXISTS " + tableName;
using (var connection = new SqlConnection(DataTestUtility.TCPConnectionString))
using (var cmd = new SqlCommand(dropTableQuery, connection))
{
connection.Open();
cmd.ExecuteNonQuery();
}
}
}
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 VerifyStatmentCompletedCalled()
{
string tableName = DataTestUtility.GetUniqueNameForSqlServer("stmt");
using (var conn = new SqlConnection(s_connStr))
using (var cmd = conn.CreateCommand())
{
try
{
cmd.StatementCompleted += StatementCompletedHandler;
conn.Open();
cmd.CommandText = $"CREATE TABLE {tableName} (c1 int)";
var res = cmd.ExecuteScalar();
cmd.CommandText = $"INSERT {tableName} VALUES(1)"; //DML (+1)
res = cmd.ExecuteScalar();
cmd.CommandText = $"Update {tableName} set c1=2"; //DML (+1)
res = cmd.ExecuteScalar();
cmd.CommandText = $"SELECT * from {tableName}"; //DQL (+1)
res = cmd.ExecuteScalar();
cmd.CommandText = $"DELETE FROM {tableName}"; //DML (+1)
res = cmd.ExecuteScalar();
}
finally
{
cmd.CommandText = $"DROP TABLE {tableName}";
var res = cmd.ExecuteScalar();
}
}
// DDL and DQL queries that return DoneRowCount are accounted here.
Assert.True(completedHandlerExecuted == 4);
}
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);
}
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;
string errorMessageSevereFailure = SystemDataResourceManager.Instance.SQL_SevereError;
DataTestUtility.ExpectFailure <SqlException>(() =>
{
threadsReady.SignalAndWait();
using (SqlDataReader r = command.ExecuteReader())
{
do
{
while (r.Read())
{
}
} while (r.NextResult());
}
}, new string[] { errorMessage, errorMessageSevereFailure });
}
public static void SqlDataReader_SqlBuffer_GetFieldValue()
{
string tableName = DataTestUtility.GetUniqueNameForSqlServer("SqlBuffer_GetFieldValue");
DateTimeOffset dtoffset = DateTimeOffset.Now;
DateTime dt = DateTime.Now;
//Exclude the millisecond because of rounding at some points by SQL Server.
DateTime dateTime = new DateTime(dt.Year, dt.Month, dt.Day, dt.Hour, dt.Minute, dt.Second);
//Arrange
DbProviderFactory provider = SqlClientFactory.Instance;
using DbConnection con = provider.CreateConnection();
con.ConnectionString = DataTestUtility.TCPConnectionString;
con.Open();
string sqlQueryOne = $"CREATE TABLE {tableName} ([CustomerId] [int],[FirstName] [nvarchar](50),[BoolCol] [BIT],[ShortCol] [SMALLINT],[ByteCol] [TINYINT],[LongCol] [BIGINT]);";
string sqlQueryTwo = $"ALTER TABLE {tableName} ADD [DoubleCol] [FLOAT],[SingleCol] [REAL],[GUIDCol] [uniqueidentifier],[DateTimeCol] [DateTime],[DecimalCol] [SmallMoney],[DateTimeOffsetCol] [DateTimeOffset];";
try
{
using (DbCommand command = provider.CreateCommand())
{
command.Connection = con;
command.CommandText = sqlQueryOne;
command.ExecuteNonQuery();
}
using (DbCommand command = provider.CreateCommand())
{
command.Connection = con;
command.CommandText = sqlQueryTwo;
command.ExecuteNonQuery();
}
System.Data.SqlTypes.SqlGuid sqlguid = new System.Data.SqlTypes.SqlGuid(Guid.NewGuid());
using (SqlCommand sqlCommand = new SqlCommand("", con as SqlConnection))
{
sqlCommand.CommandText = $"INSERT INTO {tableName} "
+ "VALUES (@CustomerId,@FirstName,@BoolCol,@ShortCol,@ByteCol,@LongCol,@DoubleCol,@SingleCol"
+ ",@GUIDCol,@DateTimeCol,@DecimalCol,@DateTimeOffsetCol)";
sqlCommand.Parameters.AddWithValue(@"CustomerId", 1);
sqlCommand.Parameters.AddWithValue(@"FirstName", "Microsoft");
sqlCommand.Parameters.AddWithValue(@"BoolCol", true);
sqlCommand.Parameters.AddWithValue(@"ShortCol", 3274);
sqlCommand.Parameters.AddWithValue(@"ByteCol", 253);
sqlCommand.Parameters.AddWithValue(@"LongCol", 922222222222);
sqlCommand.Parameters.AddWithValue(@"DoubleCol", 10.7);
sqlCommand.Parameters.AddWithValue(@"SingleCol", 123.546f);
sqlCommand.Parameters.AddWithValue(@"GUIDCol", sqlguid);
sqlCommand.Parameters.AddWithValue(@"DateTimeCol", dateTime);
sqlCommand.Parameters.AddWithValue(@"DecimalCol", 280);
sqlCommand.Parameters.AddWithValue(@"DateTimeOffsetCol", dtoffset);
sqlCommand.ExecuteNonQuery();
}
using (SqlCommand sqlCommand = new SqlCommand("", con as SqlConnection))
{
sqlCommand.CommandText = "select top 1 * from " + tableName;
using (DbDataReader reader = sqlCommand.ExecuteReader())
{
Assert.True(reader.Read());
Assert.Equal(1, reader.GetFieldValue <int>(0));
Assert.Equal("Microsoft", reader.GetFieldValue <string>(1));
Assert.True(reader.GetFieldValue <bool>(2));
Assert.Equal(3274, reader.GetFieldValue <short>(3));
Assert.Equal(253, reader.GetFieldValue <byte>(4));
Assert.Equal(922222222222, reader.GetFieldValue <long>(5));
Assert.Equal(10.7, reader.GetFieldValue <double>(6));
Assert.Equal(123.546f, reader.GetFieldValue <float>(7));
Assert.Equal(sqlguid, reader.GetFieldValue <Guid>(8));
Assert.Equal(sqlguid.Value, reader.GetFieldValue <System.Data.SqlTypes.SqlGuid>(8).Value);
Assert.Equal(dateTime.ToString("dd/MM/yyyy HH:mm:ss.fff"), reader.GetFieldValue <DateTime>(9).ToString("dd/MM/yyyy HH:mm:ss.fff"));
Assert.Equal(280, reader.GetFieldValue <decimal>(10));
Assert.Equal(dtoffset, reader.GetFieldValue <DateTimeOffset>(11));
}
}
}
finally
{
//cleanup
using (DbCommand cmd = provider.CreateCommand())
{
cmd.Connection = con;
cmd.CommandText = "drop table " + tableName;
cmd.ExecuteNonQuery();
}
}
}
public static void Test(string srcConstr, string dstConstr, string targettable)
{
string targetCustomerTable = targettable + "_customer";
using (SqlConnection dstConn = new SqlConnection(dstConstr))
using (SqlCommand dstCmd = dstConn.CreateCommand())
{
dstConn.Open();
try
{
Helpers.TryExecute(dstCmd, "CREATE TABLE [" + targetCustomerTable + "] ([CustomerID] [nchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, CONSTRAINT [PK_" + targetCustomerTable + "] PRIMARY KEY CLUSTERED (CustomerID) ON [PRIMARY]) ON [PRIMARY]");
Helpers.TryExecute(dstCmd,
"CREATE TABLE [" + targettable + "] ([OrderID] [int] NOT NULL , " +
" [CustomerID] [nchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , " +
" CONSTRAINT [PK_" + targettable + "] PRIMARY KEY CLUSTERED " +
" (" +
" [OrderID]" +
" ) ON [PRIMARY] ," +
" CONSTRAINT [FK_" + targettable + "_Customers] FOREIGN KEY " +
" (" +
" [CustomerID]" +
" ) REFERENCES [" + targetCustomerTable + "] (" +
" [CustomerID]" +
" )" +
") ON [PRIMARY]");
using (SqlConnection srcConn = new SqlConnection(srcConstr))
{
srcConn.Open();
// First copy the customer ID list across
SqlCommand customerCommand = new SqlCommand("SELECT CustomerID from Northwind..Customers", srcConn);
using (DbDataReader reader = customerCommand.ExecuteReader())
{
using (SqlBulkCopy bulkcopy = new SqlBulkCopy(dstConn))
{
bulkcopy.DestinationTableName = targetCustomerTable;
bulkcopy.WriteToServer(reader);
}
}
SqlCommand srcCmd = new SqlCommand("select OrderID, CustomerID from Northwind..Orders where OrderId = 10643", srcConn);
using (DbDataReader reader = srcCmd.ExecuteReader())
{
using (SqlBulkCopy bulkcopy = new SqlBulkCopy(dstConn))
{
bulkcopy.DestinationTableName = targettable;
SqlBulkCopyColumnMappingCollection ColumnMappings = bulkcopy.ColumnMappings;
ColumnMappings.Add("OrderID", "OrderID");
ColumnMappings.Add("CustomerID", "CustomerID");
bulkcopy.WriteToServer(reader);
DataTestUtility.AssertEqualsWithDescription(bulkcopy.RowsCopied, 1, "Unexpected number of rows.");
}
}
}
Helpers.VerifyResults(dstConn, targettable, 2, 1);
}
finally
{
Helpers.TryExecute(dstCmd, "drop table " + targettable);
Helpers.TryExecute(dstCmd, "drop table " + targetCustomerTable);
}
}
}
public static bool IsNotAzureServer()
{
return(AreConnStringsSetup() ? !DataTestUtility.IsAzureSqlServer(new SqlConnectionStringBuilder((DataTestUtility.TCPConnectionString)).DataSource) : true);
}
public static void TestMain()
{
string connectionString = DataTestUtility.TCPConnectionString;
string tempTable = DataTestUtility.GetUniqueNameForSqlServer("table");
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 LastName, FirstName, Title, Address, City, Region, PostalCode, Country into " + tempTable + " from Employees where EmployeeID=0";
cmd.ExecuteNonQuery();
#endregion
tx = con.BeginTransaction();
cmd.Transaction = tx;
cmd.CommandText = "insert into " + tempTable + "(LastName, FirstName, Title, Address, City, Region, PostalCode, Country) values ('Doe', 'Jane' , 'Ms.', 'One Microsoft Way', 'Redmond', 'WA', '98052', 'USA')";
cmd.ExecuteNonQuery();
cmd.CommandText = "insert into " + tempTable + "(LastName, FirstName, Title, Address, City, Region, PostalCode, Country) values ('Doe', 'John' , 'Mr.', NULL, NULL, NULL, NULL, NULL)";
cmd.ExecuteNonQuery();
tx.Commit();
cmd.Transaction = null;
string parameterName = "@p1";
DbParameter p1 = cmd.CreateParameter();
p1.ParameterName = parameterName;
p1.Value = "Doe";
cmd.Parameters.Add(p1);
cmd.CommandText = "select * from " + tempTable + " where LastName = " + parameterName;
// Test GetValue + IsDBNull
using (DbDataReader rdr = cmd.ExecuteReader())
{
StringBuilder actualResult = new StringBuilder();
int currentValue = 0;
string[] expectedValues =
{
"Doe,Jane,Ms.,One Microsoft Way,Redmond,WA,98052,USA",
"Doe,John,Mr.,(NULL),(NULL),(NULL),(NULL),(NULL)"
};
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 =
{
"Doe,Jane,Ms.,One Microsoft Way,Redmond,WA,98052,USA",
"Doe,John,Mr.,(NULL),(NULL),(NULL),(NULL),(NULL)"
};
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 if (rdr.GetFieldType(i) == typeof(int))
{
actualResult.Append(rdr.GetFieldValue <int>(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 =
{
"Doe,Jane,Ms.,One Microsoft Way,Redmond,WA,98052,USA",
"Doe,John,Mr.,(NULL),(NULL),(NULL),(NULL),(NULL)"
};
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 if (rdr.GetFieldType(i) == typeof(int))
{
actualResult.Append(rdr.GetFieldValue <int>(i));
}
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 IsIntegratedSecurityEnvironmentSet() => DataTestUtility.IsIntegratedSecuritySetup();
public void NullTest()
{
using (SqlConnection conn = new SqlConnection(_connStr))
{
conn.Open();
using (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;
bool rerun = false;
do
{
try
{
using (SqlTransaction trans = conn.BeginTransaction())
{
com.Transaction = trans;
using (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.");
rerun = false;
}
}
}
catch (SqlException e)
{
if (e.Message.Contains("Rerun the transaction"))
{
rerun = true;
}
else
{
throw;
}
}
}while (rerun);
}
}
}
private static bool AreConnectionStringsSetup() => DataTestUtility.AreConnStringsSetup();
private static bool IsFileStreamEnvironmentSet() => DataTestUtility.IsFileStreamSetup();
private static bool IsAADConnStringsSetup() => DataTestUtility.IsAADPasswordConnStrSetup();
private static bool IsAccessTokenSetup() => DataTestUtility.IsAccessTokenSetup();
private static bool IsAzure() => !DataTestUtility.IsNotAzureServer();
