/// <summary>
/// Constructs a query for selecting rows in a table based on the filters provided.
/// Internal for unit testing purposes only.
/// </summary>
internal static string ConstructInitializeQuery(EditTableMetadata metadata, EditInitializeFiltering initFilters)
{
StringBuilder queryBuilder = new StringBuilder("SELECT ");
// If there is a filter for top n rows, then apply it
if (initFilters.LimitResults.HasValue)
{
if (initFilters.LimitResults < 0)
{
throw new ArgumentOutOfRangeException(nameof(initFilters.LimitResults), SR.EditDataFilteringNegativeLimit);
}
queryBuilder.AppendFormat("TOP {0} ", initFilters.LimitResults.Value);
}
// Using the columns we know, add them to the query
var columns = metadata.Columns.Select(col => col.EscapedName);
var columnClause = string.Join(", ", columns);
queryBuilder.Append(columnClause);
// Add the FROM
queryBuilder.AppendFormat(" FROM {0}", metadata.EscapedMultipartName);
return(queryBuilder.ToString());
}
public void ConstructQueryWithLimit(int limit)
{
// Setup: Create a metadata provider for some basic columns
var data = new Common.TestDbColumnsWithTableMetadata(false, false, 0, 0);
// If: I generate a query for selecting rows without a limit
EditInitializeFiltering eif = new EditInitializeFiltering
{
LimitResults = limit
};
string query = EditSession.ConstructInitializeQuery(data.TableMetadata, eif);
// Then:
// ... The query should look like a select statement
Regex selectRegex = new Regex(@"SELECT TOP (\d+) (.+) FROM (.+)", RegexOptions.IgnoreCase);
var match = selectRegex.Match(query);
Assert.True(match.Success);
// ... There should be columns in it
Assert.Equal(data.DbColumns.Length, match.Groups[2].Value.Split(',').Length);
// ... The table name should be in it
Assert.Equal(data.TableMetadata.EscapedMultipartName, match.Groups[3].Value);
// ... The top count should be equal to what we provided
int limitFromQuery;
Assert.True(int.TryParse(match.Groups[1].Value, out limitFromQuery));
Assert.Equal(limit, limitFromQuery);
}
public void ConstructQueryWithoutLimit()
{
// Setup: Create a metadata provider for some basic columns
var data = new Common.TestDbColumnsWithTableMetadata(false, false, 0, 0);
// If: I generate a query for selecting rows without a limit
EditInitializeFiltering eif = new EditInitializeFiltering
{
LimitResults = null
};
string query = EditSession.ConstructInitializeQuery(data.TableMetadata, eif);
// Then:
// ... The query should look like a select statement
Regex selectRegex = new Regex("SELECT (.+) FROM (.+)", RegexOptions.IgnoreCase);
var match = selectRegex.Match(query);
Assert.True(match.Success);
// ... There should be columns in it
Assert.Equal(data.DbColumns.Length, match.Groups[1].Value.Split(',').Length);
// ... The table name should be in it
Assert.Equal(data.TableMetadata.EscapedMultipartName, match.Groups[2].Value);
// ... It should NOT have a TOP clause in it
Assert.DoesNotContain("TOP", query);
}
public void ConstructQueryNegativeLimit()
{
// Setup: Create a metadata provider for some basic columns
var data = new Common.TestDbColumnsWithTableMetadata(false, false, 0, 0);
// If: I generate a query for selecting rows with a negative limit
// Then: An exception should be thrown
EditInitializeFiltering eif = new EditInitializeFiltering
{
LimitResults = -1
};
Assert.Throws <ArgumentOutOfRangeException>(() => EditSession.ConstructInitializeQuery(data.TableMetadata, eif));
}
public void ConstructQueryNegativeLimit()
{
// Setup: Create a metadata provider for some basic columns
var cols = Common.GetColumns(false);
var etm = Common.GetStandardMetadata(cols);
// If: I generate a query for selecting rows with a negative limit
// Then: An exception should be thrown
EditInitializeFiltering eif = new EditInitializeFiltering
{
LimitResults = -1
};
Assert.Throws <ArgumentOutOfRangeException>(() => EditSession.ConstructInitializeQuery(etm, eif));
}
