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();