public async Task GetCommand(bool includeIdentity, bool isMemoryOptimized) { // Setup: // ... Create a row update with cell updates var columns = Common.GetColumns(includeIdentity); var rs = await Common.GetResultSet(columns, includeIdentity); var etm = Common.GetStandardMetadata(columns, isMemoryOptimized); RowUpdate ru = new RowUpdate(0, rs, etm); Common.AddCells(ru, includeIdentity); // ... Mock db connection for building the command var mockConn = new TestSqlConnection(null); // If: I ask for a command to be generated for update DbCommand cmd = ru.GetCommand(mockConn); // Then: // ... The command should not be null Assert.NotNull(cmd); // ... There should be an appropriate number of parameters in it // (1 or 3 keys, 3 value parameters) int expectedKeys = includeIdentity ? 1 : 3; Assert.Equal(expectedKeys + 3, cmd.Parameters.Count); // ... It should be formatted into an update script with output string regexFormat = isMemoryOptimized ? @"UPDATE (.+) WITH \(SNAPSHOT\) SET (.+) OUTPUT (.+) WHERE (.+)" : @"UPDATE (.+) SET (.+) OUTPUT(.+) WHERE (.+)"; Regex r = new Regex(regexFormat); var m = r.Match(cmd.CommandText); Assert.True(m.Success); // ... There should be a table string tbl = m.Groups[1].Value; Assert.Equal(etm.EscapedMultipartName, tbl); // ... There should be 3 parameters for input string[] inCols = m.Groups[2].Value.Split(','); Assert.Equal(3, inCols.Length); Assert.All(inCols, s => Assert.Matches(@"\[.+\] = @Value\d+", s)); // ... There should be 3 OR 4 columns for output string[] outCols = m.Groups[3].Value.Split(','); Assert.Equal(includeIdentity ? 4 : 3, outCols.Length); Assert.All(outCols, s => Assert.StartsWith("inserted.", s.Trim())); // ... There should be 1 OR 3 columns for where components string[] whereComponents = m.Groups[4].Value.Split(new[] { "AND" }, StringSplitOptions.None); Assert.Equal(expectedKeys, whereComponents.Length); Assert.All(whereComponents, s => Assert.Matches(@"\(.+ = @Param\d+\)", s)); }
public async Task GetCommandNullConnection() { // Setup: Create a row update RowUpdate ru = await GetStandardRowUpdate(); // If: I attempt to create a command with a null connection // Then: It should throw an exception Assert.Throws <ArgumentNullException>(() => ru.GetCommand(null)); }
public async Task GetCommand(bool includeIdentity, bool isMemoryOptimized) { // Setup: // ... Create a row update with cell updates var data = new Common.TestDbColumnsWithTableMetadata(isMemoryOptimized, includeIdentity, 0, 0); var rs = await Common.GetResultSet(data.DbColumns, includeIdentity); RowUpdate ru = new RowUpdate(0, rs, data.TableMetadata); Common.AddCells(ru, includeIdentity ? 1 : 0); // ... Mock db connection for building the command var mockConn = new TestSqlConnection(null); // If: I ask for a command to be generated for update DbCommand cmd = ru.GetCommand(mockConn); // Then: // ... The command should not be null Assert.NotNull(cmd); // ... Validate the command's makeup // Break the query into parts string[] splitSql = cmd.CommandText.Split(Environment.NewLine); Assert.True(splitSql.Length >= 3); // Check the declare statement first Regex declareRegex = new Regex(@"^DECLARE @(.+) TABLE \((.+)\)$"); Match declareMatch = declareRegex.Match(splitSql[0]); Assert.True(declareMatch.Success); // Declared table name matches Assert.True(declareMatch.Groups[1].Value.StartsWith("Update")); Assert.True(declareMatch.Groups[1].Value.EndsWith("Output")); // Correct number of columns in declared table string[] declareCols = declareMatch.Groups[2].Value.Split(", "); Assert.Equal(rs.Columns.Length, declareCols.Length); // Check the update statement in the middle string regex = isMemoryOptimized ? @"^UPDATE (.+) WITH \(SNAPSHOT\) SET (.+) OUTPUT (.+) INTO @(.+) WHERE .+$" : @"^UPDATE (.+) SET (.+) OUTPUT (.+) INTO @(.+) WHERE .+$"; Regex updateRegex = new Regex(regex); Match updateMatch = updateRegex.Match(splitSql[10]); Assert.True(updateMatch.Success); // Table name matches Assert.Equal(Common.TableName, updateMatch.Groups[1].Value); // Output columns match string[] outCols = updateMatch.Groups[3].Value.Split(", "); Assert.Equal(rs.Columns.Length, outCols.Length); Assert.All(outCols, col => Assert.StartsWith("inserted.", col)); // Set columns match string[] setCols = updateMatch.Groups[2].Value.Split(", "); Assert.Equal(3, setCols.Length); Assert.All(setCols, s => Assert.Matches(@".+ = @Value\d+_\d+", s)); // Output table name matches Assert.StartsWith("Update", updateMatch.Groups[4].Value); Assert.EndsWith("Output", updateMatch.Groups[4].Value); // Check the select statement last Regex selectRegex = new Regex(@"^SELECT (.+) FROM @(.+)$"); Match selectMatch = selectRegex.Match(splitSql[11]); Assert.True(selectMatch.Success); // Correct number of columns in select statement string[] selectCols = selectMatch.Groups[1].Value.Split(", "); Assert.Equal(rs.Columns.Length, selectCols.Length); // Select table name matches Assert.StartsWith("Update", selectMatch.Groups[2].Value); Assert.EndsWith("Output", selectMatch.Groups[2].Value); // ... There should be an appropriate number of parameters in it // (1 or 3 keys, 3 value parameters) int expectedKeys = includeIdentity ? 1 : 3; Assert.Equal(expectedKeys + 3, cmd.Parameters.Count); }