public void CaseExpression_Dont_Overrun()
{
List <TSQLStatement> statements = TSQLStatementReader.ParseStatements(
@"
BEGIN
SELECT CASE WHEN 1 = 2 THEN 0 ELSE 1 END
END"
// normalizing line endings to unix format to ensure passing
// tests in various environments
.Replace("\r", ""),
includeWhitespace: false);
Assert.AreEqual(3, statements.Count);
Assert.AreEqual(1, statements[0].Tokens.Count);
TokenComparisons.CompareTokenLists(
new List <TSQLToken>()
{
new TSQLKeyword(18, "SELECT"),
new TSQLKeyword(25, "CASE"),
new TSQLKeyword(30, "WHEN"),
new TSQLNumericLiteral(35, "1"),
new TSQLOperator(37, "="),
new TSQLNumericLiteral(39, "2"),
new TSQLKeyword(41, "THEN"),
new TSQLNumericLiteral(46, "0"),
new TSQLKeyword(48, "ELSE"),
new TSQLNumericLiteral(53, "1"),
new TSQLKeyword(55, "END")
},
statements[1].Tokens);
Assert.AreEqual(1, statements[2].Tokens.Count);
Assert.IsTrue(statements[2].Tokens[0].IsKeyword(TSQLKeywords.END));
}
public void WithStatement_Select()
{
List <TSQLStatement> statements = TSQLStatementReader.ParseStatements(
@"WITH [EMP_cte]([BusinessEntityID], [OrganizationNode], [FirstName], [LastName], [RecursionLevel]) -- CTE name and columns
AS (
SELECT e.[BusinessEntityID], e.[OrganizationNode], p.[FirstName], p.[LastName], 0 -- Get the initial list of Employees for Manager n
FROM [HumanResources].[Employee] e
INNER JOIN [Person].[Person] p
ON p.[BusinessEntityID] = e.[BusinessEntityID]
WHERE e.[BusinessEntityID] = @BusinessEntityID
UNION ALL
SELECT e.[BusinessEntityID], e.[OrganizationNode], p.[FirstName], p.[LastName], [RecursionLevel] + 1 -- Join recursive member to anchor
FROM [HumanResources].[Employee] e
INNER JOIN [EMP_cte]
ON e.[OrganizationNode].GetAncestor(1) = [EMP_cte].[OrganizationNode]
INNER JOIN [Person].[Person] p
ON p.[BusinessEntityID] = e.[BusinessEntityID]
)
-- Join back to Employee to return the manager name
SELECT [EMP_cte].[RecursionLevel], [EMP_cte].[OrganizationNode].ToString() as [OrganizationNode], p.[FirstName] AS 'ManagerFirstName', p.[LastName] AS 'ManagerLastName',
[EMP_cte].[BusinessEntityID], [EMP_cte].[FirstName], [EMP_cte].[LastName] -- Outer select from the CTE
FROM [EMP_cte]
INNER JOIN [HumanResources].[Employee] e
ON [EMP_cte].[OrganizationNode].GetAncestor(1) = e.[OrganizationNode]
INNER JOIN [Person].[Person] p
ON p.[BusinessEntityID] = e.[BusinessEntityID]
ORDER BY [RecursionLevel], [EMP_cte].[OrganizationNode].ToString()
OPTION (MAXRECURSION 25)" ,
includeWhitespace: false);
Assert.AreEqual(1, statements.Count);
Assert.IsInstanceOf(typeof(TSQLSelectStatement), statements[0]);
}
public void CaseExpression_Regression_Duplicated_Case()
{
List <TSQLStatement> statements = TSQLStatementReader.ParseStatements(
@"select case when abi = '03654' then 0 else 1 end as abi;",
includeWhitespace: false);
Assert.AreEqual(1, statements.Count);
List <TSQLToken> tokens = statements[0].Tokens;
TokenComparisons.CompareTokenLists(
new List <TSQLToken>()
{
new TSQLKeyword(0, "select"),
new TSQLKeyword(7, "case"),
new TSQLKeyword(12, "when"),
new TSQLIdentifier(17, "abi"),
new TSQLOperator(21, "="),
new TSQLStringLiteral(23, "'03654'"),
new TSQLKeyword(31, "then"),
new TSQLNumericLiteral(36, "0"),
new TSQLKeyword(38, "else"),
new TSQLNumericLiteral(43, "1"),
new TSQLKeyword(45, "end"),
new TSQLKeyword(49, "as"),
new TSQLIdentifier(52, "abi")
},
tokens);
}
public void UpdateStatement_FullFunctionality()
{
List <TSQLStatement> statements = TSQLStatementReader.ParseStatements(
@"
UPDATE t
SET
t.Column1 = 'something',
t.Column2 = 2
OUTPUT deleted.*
INTO #holder (ID, Column1, Column2)
FROM dbo.SomeTable t
WHERE
t.ID = 1
OPTION (RECOMPILE);" ,
includeWhitespace: false);
TSQLUpdateStatement update = statements[0].AsUpdate;
Assert.AreEqual(42, update.Tokens.Count);
Assert.AreEqual(12, update.Set.Tokens.Count);
Assert.AreEqual(13, update.Output.Tokens.Count);
Assert.AreEqual(9, update.Output.Into.Tokens.Count);
Assert.AreEqual(5, update.From.Tokens.Count);
Assert.AreEqual(6, update.Where.Tokens.Count);
Assert.AreEqual(4, update.Option.Tokens.Count);
}
public void InsertStatement_Select()
{
string sql = @"
INSERT INTO dbo.EmployeeSales
SELECT 'SELECT', sp.BusinessEntityID, c.LastName, sp.SalesYTD
FROM Sales.SalesPerson AS sp
INNER JOIN Person.Person AS c
ON sp.BusinessEntityID = c.BusinessEntityID
WHERE sp.BusinessEntityID LIKE '2%'
ORDER BY sp.BusinessEntityID, c.LastName;" ;
List <TSQLStatement> statements = TSQLStatementReader.ParseStatements(
sql,
includeWhitespace: false);
TSQLInsertStatement insert = statements[0].AsInsert;
Assert.AreEqual(55, insert.Tokens.Count);
Assert.IsNull(insert.With);
Assert.IsNull(insert.Output);
Assert.IsNull(insert.Values);
Assert.IsNull(insert.Default);
Assert.IsNull(insert.Execute);
Assert.AreEqual(5, insert.Insert.Tokens.Count);
Assert.AreEqual(50, insert.Select.Tokens.Count);
}
private void ExecuteSql(int update, ref bool started)
{
var queryText = AddSqlForVariables(SqlQuery);
var statements = TSQLStatementReader.ParseStatements(queryText);
if (queryText.Contains("UNION") && statements.Count == 2)
{
var tables = statements[0].GetAllTables();
foreach (var table in tables)
{
LoadRecordset(table.TableName);
}
var sqlQueryToUpdate = queryText;
foreach (var item in AdvancedRecordset.HashedRecSets)
{
sqlQueryToUpdate = sqlQueryToUpdate.Replace(item.recSet, item.hashCode);
}
var results = AdvancedRecordset.ExecuteQuery(sqlQueryToUpdate);
foreach (DataTable dt in results.Tables)
{
AdvancedRecordset.ApplyResultToEnvironment(dt.TableName, Outputs, dt.Rows.Cast <DataRow>().ToList(), false, update, ref started);
}
}
else
{
ExecuteAllSqlStatements(update, statements, ref started);
}
}
public void SelectStatement_WindowedAggregate()
{
List <TSQLStatement> statements = TSQLStatementReader.ParseStatements(@"
SELECT
*,
ROW_NUMBER() OVER (
PARTITION BY
some_field
ORDER BY
some_other_field) AS some_row_number
FROM my_db.my_schema.my_table" , includeWhitespace: false);
Assert.AreEqual(1, statements.Count);
Assert.AreEqual(TSQLStatementType.Select, statements[0].Type);
TSQLSelectStatement select = statements[0] as TSQLSelectStatement;
Assert.AreEqual(23, select.Tokens.Count);
Assert.AreEqual(17, select.Select.Tokens.Count);
Assert.AreEqual(6, select.From.Tokens.Count);
Assert.AreEqual(2, select.Select.Columns.Count);
}
public void AdvancedRecordset_Converter_ExpectCanRunMultipleQueries()
{
string query = "select CURRENT_TIMESTAMP;" +
"select * from address;update person set Age=20 where Name=\"zak\";" +
"select * from person p join address a on p.address_id=a.id where a.addr=\"11 test lane\" order by Name";
var worker = CreatePersonAddressWorkers();
var statements = TSQLStatementReader.ParseStatements(query);
var updatedQuery = "";
foreach (var statement in statements)
{
updatedQuery += worker.UpdateSqlWithHashCodes(statement) + ";";
}
var results = worker.ExecuteQuery(updatedQuery);
Assert.AreEqual("bob", Encoding.UTF8.GetString(results.Tables[2].Rows[0]["Name"] as byte[]));
Assert.AreEqual(21, int.Parse(Encoding.UTF8.GetString(results.Tables[2].Rows[0]["Age"] as byte[])));
Assert.AreEqual(1, int.Parse(Encoding.UTF8.GetString(results.Tables[2].Rows[0]["address_id"] as byte[])));
Assert.AreEqual("11 test lane", Encoding.UTF8.GetString(results.Tables[1].Rows[0]["Addr"] as byte[]));
Assert.AreEqual(3421, int.Parse(Encoding.UTF8.GetString(results.Tables[1].Rows[0]["Postcode"] as byte[])));
Assert.AreEqual("jef", Encoding.UTF8.GetString(results.Tables[2].Rows[1]["Name"] as byte[]));
Assert.AreEqual(24, int.Parse(Encoding.UTF8.GetString(results.Tables[2].Rows[1]["Age"] as byte[])));
}
public void AdvancedRecordset_Converter_ConvertDataTableToRecordset_ExpectDataInIEnvironment()
{
string returnRecordsetName = "person";
string query = "select * from person";
var worker = CreatePersonAddressWorkers();
var statements = TSQLStatementReader.ParseStatements(query);
var updatedQuery = worker.UpdateSqlWithHashCodes(statements[0]);
var results = worker.ExecuteQuery(updatedQuery);
var started = false;
// apply sql results to environment
worker.ApplyResultToEnvironment(returnRecordsetName, new List <IServiceOutputMapping>(), results.Tables[0].Rows.Cast <DataRow>().ToList(), false, 0, ref started);
// fetch newly inserted data from environment
var internalResult = worker.Environment.EvalAsList("[[person(*).name]]", 0);
// assert that data fetched is what we expect from sql
var e = internalResult.GetEnumerator();
if (e.MoveNext())
{
Assert.AreEqual(e.Current, "bob");
}
else
{
Assert.Fail();
}
}
public IEnumerable <SqlMatch> Parse(string sqlText)
{
foreach (var tokens in TSQLStatementReader.ParseStatements(CleanUp(sqlText)).Select(s => s.Tokens))
{
var previous = tokens.First();
var queryType = GetQueryType(previous);
if (queryType == QueryType.Unknown)
{
continue;
}
var targets = new HashSet <string>();
var collect = previous.Text == "UPDATE";
string tmp = null;
foreach (var token in tokens.Skip(1))
{
switch (token)
{
case TSQLKeyword keyword:
collect = _keywords.Contains(keyword.Text, StringComparer.InvariantCultureIgnoreCase);
if (!string.IsNullOrEmpty(tmp))
{
targets.Add(tmp);
tmp = null;
}
break;
case TSQLIdentifier identifier:
if (!collect || _ignore.Contains(identifier.Text, StringComparer.InvariantCultureIgnoreCase))
{
continue;
}
if (previous.Text == ".")
{
tmp += $".{identifier.Name}";
}
else
{
if (tmp != null)
{
targets.Add(tmp);
}
tmp = previous is TSQLIdentifier ? null : identifier.Name;
}
break;
}
previous = token;
}
if (!string.IsNullOrEmpty(tmp))
{
targets.Add(tmp);
}
yield return(new SqlMatch
{
Targets = targets.ToArray(),
Type = queryType
});
}
}
public static void ParseTSqlFragment()
{
List <TSQLStatement> statements = TSQLStatementReader.ParseStatements(
@"select AreaId = A.mcw_areaId, SurrogateKey = A.AreaKey, Code = S.statecode, Name = S.statename From CRM.dim_Area as A inner join CRM.dim_AreaState as S ON A.statecode = S.statecode ; DELETE FROM blogs where url='dasfds'",
includeWhitespace: true);
Console.WriteLine(statements.Count);
for (int i = 0; i < statements.Count; i++)
{
Console.WriteLine("-------Statement-----{0}", i);
Console.WriteLine(statements[0]);
TSQLSelectStatement select = statements[i] as TSQLSelectStatement;
Console.WriteLine(TSQLStatementType.Select + "----->" + statements[i].Type);
if (statements[i].Type != TSQLStatementType.Unknown)
{
Console.WriteLine("----->" + select.Tokens.Count);
Console.WriteLine(TSQLKeywords.SELECT + "----->" + select.Tokens[0].AsKeyword.Keyword);
Console.WriteLine(" ", select.Tokens[1].AsWhitespace.Text);
Console.WriteLine("t", select.Tokens[2].AsIdentifier.Name);
Console.WriteLine(TSQLCharacters.Period + "----->" + select.Tokens[3].AsCharacter.Character);
Console.WriteLine("----->" + select.Select.Tokens.Count);
Console.WriteLine("----->" + select.Into.Tokens.Count);
Console.WriteLine("----->" + select.From.Tokens.Count);
Console.WriteLine("----->" + select.Where.Tokens.Count);
Console.WriteLine("----->" + select.GroupBy.Tokens.Count);
Console.WriteLine("----->" + select.Having.Tokens.Count);
Console.WriteLine("----->" + select.OrderBy.Tokens.Count);
}
}
}
public void SqliteServer_ParseTSQL_SelectStatement_SelectLiteral()
{
//------------Setup for test--------------------------
try
{
//------------Execute Test---------------------------
List <TSQLStatement> statements = TSQLStatementReader.ParseStatements(
"select 1;",
includeWhitespace: true);
TSQLSelectStatement select = statements[0] as TSQLSelectStatement;
Assert.IsNotNull(statements);
Assert.AreEqual(1, statements.Count);
Assert.AreEqual(TSQLStatementType.Select, statements[0].Type);
Assert.AreEqual(3, select.Tokens.Count);
Assert.AreEqual(TSQLKeywords.SELECT, select.Tokens[0].AsKeyword.Keyword);
Assert.AreEqual(" ", select.Tokens[1].AsWhitespace.Text);
Assert.AreEqual("1", select.Tokens[2].AsNumericLiteral.Text);
//------------Assert Results-------------------------
}
finally
{
}
}
private bool MssqlIsStoredProcForXmlResult(SqlConnection connection, string procedureName)
{
bool result = false;
var procSqlScript = MssqlGetSqlForProcedure(connection, procedureName);
var statements = TSQLStatementReader.ParseStatements(procSqlScript);
foreach (var statement in statements)
{
var tokens = statement.Tokens;
var cnt = tokens.Count;
if (cnt < 3)
{
continue;
}
var i = 0;
for (; i < tokens.Count; i++)
{
if (tokens[i].Type == TSQL.Tokens.TSQLTokenType.Keyword && tokens[i].Text.ToUpper() == "FOR")
{
i++;
result = (i < tokens.Count && tokens[i].Type == TSQL.Tokens.TSQLTokenType.Identifier && tokens[i].Text == "XML");
}
}
}
return(result);
}
public void SelectStatement_TwoLiteralSelects()
{
List <TSQLStatement> statements = TSQLStatementReader.ParseStatements(
"select 1;select 2;",
includeWhitespace: true);
TSQLSelectStatement select1 = statements[0] as TSQLSelectStatement;
TSQLSelectStatement select2 = statements[1] as TSQLSelectStatement;
Assert.IsNotNull(statements);
Assert.AreEqual(2, statements.Count);
Assert.AreEqual(TSQLStatementType.Select, select1.Type);
Assert.AreEqual(3, select1.Tokens.Count);
Assert.AreEqual(TSQLKeywords.SELECT, select1.Tokens[0].AsKeyword.Keyword);
Assert.AreEqual(" ", select1.Tokens[1].AsWhitespace.Text);
Assert.AreEqual("1", select1.Tokens[2].AsNumericLiteral.Text);
Assert.AreEqual(1, select1.Select.Columns.Count);
Assert.AreEqual(TSQLStatementType.Select, select2.Type);
Assert.AreEqual(3, select2.Tokens.Count);
Assert.AreEqual(TSQLKeywords.SELECT, select2.Tokens[0].AsKeyword.Keyword);
Assert.AreEqual(" ", select2.Tokens[1].AsWhitespace.Text);
Assert.AreEqual("2", select2.Tokens[2].AsNumericLiteral.Text);
Assert.AreEqual(1, select2.Select.Columns.Count);
}
public void SelectStatement_CommonSelect()
{
List <TSQLStatement> statements = TSQLStatementReader.ParseStatements(
@"select t.a, t.b, (select 1) as e
into #tempt
from
[table] t
inner join [table] t2 on
t.id = t2.id
where
t.c = 5
group by
t.a,
t.b
having
count(*) > 1
order by
t.a,
t.b;" ,
includeWhitespace: true);
TSQLSelectStatement select = statements[0] as TSQLSelectStatement;
Assert.IsNotNull(statements);
Assert.AreEqual(1, statements.Count);
Assert.AreEqual(TSQLStatementType.Select, statements[0].Type);
Assert.AreEqual(98, select.Tokens.Count);
Assert.AreEqual(TSQLKeywords.SELECT, select.Tokens[0].AsKeyword.Keyword);
TSQLSelectClause selectClause = select.Select;
Assert.AreEqual(3, selectClause.Columns.Count);
TSQLSelectColumn column = selectClause.Columns[0];
Assert.AreEqual(TSQLExpressionType.Column, column.Expression.Type);
Assert.AreEqual("t", column.Expression.AsColumn.TableReference.Single().AsIdentifier.Name);
Assert.AreEqual("a", column.Expression.AsColumn.Column.Name);
column = selectClause.Columns[1];
Assert.AreEqual(TSQLExpressionType.Column, column.Expression.Type);
Assert.AreEqual("t", column.Expression.AsColumn.TableReference.Single().AsIdentifier.Name);
Assert.AreEqual("b", column.Expression.AsColumn.Column.Name);
column = selectClause.Columns[2];
Assert.AreEqual("e", column.ColumnAlias.AsIdentifier.Name);
Assert.AreEqual(TSQLExpressionType.Subquery, column.Expression.Type);
TSQLSubqueryExpression subquery = column.Expression.AsSubquery;
Assert.AreEqual(1, subquery.Select.Select.Columns.Count);
Assert.AreEqual(1, subquery.Select.Select.Columns[0].Expression.AsConstant.Literal.AsNumericLiteral.Value);
Assert.AreEqual(" ", select.Tokens[1].AsWhitespace.Text);
Assert.AreEqual("t", select.Tokens[2].AsIdentifier.Name);
Assert.AreEqual(TSQLCharacters.Period, select.Tokens[3].AsCharacter.Character);
Assert.AreEqual(22, select.Select.Tokens.Count);
Assert.AreEqual(4, select.Into.Tokens.Count);
Assert.AreEqual(26, select.From.Tokens.Count);
Assert.AreEqual(10, select.Where.Tokens.Count);
Assert.AreEqual(13, select.GroupBy.Tokens.Count);
Assert.AreEqual(11, select.Having.Tokens.Count);
Assert.AreEqual(12, select.OrderBy.Tokens.Count);
Assert.AreEqual(3, select.Select.Columns.Count);
Assert.AreEqual("e", select.Select.Columns[2].ColumnAlias.Text);
}
public void SelectStatement_MultiLevelParens()
{
string query = "SELECT ((A/B)-1) FROM SomeTable";
var statements = TSQLStatementReader.ParseStatements(query);
Assert.AreEqual(1, statements.Count);
Assert.AreEqual(12, statements[0].Tokens.Count);
}
public void TSqlStatementExtensions_GetAllTables_SkipToken()
{
var statements = TSQLStatementReader.ParseStatements("Select person.name as firstname from Person p");
var tables = statements[0].GetAllTables();
Assert.AreEqual(1, tables.Count);
Assert.AreEqual("Person", tables[0].TableName);
}
public void TSqlStatementExtensions_GetAllTables_Offset()
{
var statements = TSQLStatementReader.ParseStatements("SELECT Name, ProductNumber, StandardCost FROM Production.Product ORDER BY StandardCost OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY");
var tables = statements[0].GetAllTables();
Assert.AreEqual(2, tables.Count);
Assert.AreEqual("Production", tables[0].TableName);
Assert.AreEqual("Product", tables[1].TableName);
}
public void AdvancedRecordset_ReturnSql()
{
List <TSQLStatement> statements = TSQLStatementReader.ParseStatements("select * from person;", includeWhitespace: true);
TSQLSelectStatement select = statements[0] as TSQLSelectStatement;
var Worker = CreatePersonAddressWorkers();
var sql = Worker.ReturnSql(select.Tokens);
Assert.AreEqual("select * from person", sql);
}
public void MergeStatement_StandardMerge_UsingSelectFromCTE()
{
List <TSQLStatement> statements = TSQLStatementReader.ParseStatements(
@"
WITH myCTE (cteColumn1, cteColumn2)
AS
(
SELECT column1, column2
FROM cteSourceTable
WHERE column2 IS NOT NULL
)
MERGE INTO [t].[a] AS [Target]
USING (
SELECT cteColumn1, cteColumn2
FROM myCTE
) AS [Source]
ON ([Target].[ID] = [Source].[cteColumn1])
WHEN MATCHED AND (
NULLIF([Source].[cteColumn2], [Target].[Val]) IS NOT NULL OR NULLIF([Target].[Val], [Source].[cteColumn2]) IS NOT NULL) THEN
UPDATE SET
[Target].[Val] = [Source].[cteColumn2]
WHEN NOT MATCHED BY TARGET THEN
INSERT([ID],[Val])
VALUES([Source].[cteColumn1],[Source].[cteColumn2])
WHEN NOT MATCHED BY SOURCE THEN
DELETE
OUTPUT
$ACTION AS [Action]
,DELETED.[ID]
,DELETED.[Val]
,INSERTED.[ID]
,INSERTED.[Val];" ,
includeWhitespace: true);
TSQLMergeStatement merge = statements[0] as TSQLMergeStatement;
Assert.IsNotNull(statements);
Assert.AreEqual(1, statements.Count);
Assert.AreEqual(TSQLStatementType.Merge, statements[0].Type);
Assert.AreEqual(219, merge.Tokens.Count);
Assert.AreEqual(TSQLKeywords.WITH, merge.Tokens[0].AsKeyword.Keyword);
Assert.AreEqual(TSQLKeywords.MERGE, merge.Tokens[38].AsKeyword.Keyword);
Assert.AreEqual(" ", merge.Tokens[39].AsWhitespace.Text);
Assert.AreEqual(TSQLKeywords.INTO, merge.Tokens[40].AsKeyword.Keyword);
Assert.AreEqual(" ", merge.Tokens[41].AsWhitespace.Text);
Assert.AreEqual("t", merge.Tokens[42].AsIdentifier.Name);
Assert.AreEqual(38, merge.With.Tokens.Count);
Assert.AreEqual(2, merge.Merge.Tokens.Count);
Assert.AreEqual(10, merge.Into.Tokens.Count);
Assert.AreEqual(21, merge.Using.Tokens.Count);
Assert.AreEqual(14, merge.On.Tokens.Count);
Assert.AreEqual(3, merge.When.Count);
Assert.AreEqual(63, merge.When[0].Tokens.Count);
Assert.AreEqual(30, merge.When[1].Tokens.Count);
Assert.AreEqual(14, merge.When[2].Tokens.Count);
Assert.AreEqual(27, merge.Output.Tokens.Count);
}
public void ExecuteStatement_ShortWord()
{
List <TSQLStatement> statements = TSQLStatementReader.ParseStatements(
@"EXEC sp_who2;",
includeWhitespace: false);
TSQLExecuteStatement exec = statements[0].AsExecute;
Assert.AreEqual(1, statements.Count);
Assert.AreEqual(2, exec.Tokens.Count);
}
public void ExecuteStatement_NoEnd()
{
List <TSQLStatement> statements = TSQLStatementReader.ParseStatements(
@"sp_who2 select 1",
includeWhitespace: false);
TSQLExecuteStatement exec = statements[0].AsExecute;
Assert.AreEqual(2, statements.Count);
Assert.AreEqual(1, exec.Tokens.Count);
}
public void AdvancedRecordset_ExecuteScalar()
{
var worker = CreatePersonAddressWorkers();
var query = "select * from person where Name=\"zak\";";
var statements = TSQLStatementReader.ParseStatements(query);
var updatedQuery = worker.UpdateSqlWithHashCodes(statements[0]);
var result = worker.ExecuteScalar(updatedQuery);
Assert.AreEqual(3, result);
}
public void AdvancedRecordset_Converter_CanRunWhereQuery_ExpectNoResults()
{
string query = "select * from person p join address a on p.address_id=a.id where p.Name=\"zak\"";
var worker = CreatePersonAddressWorkers();
var statements = TSQLStatementReader.ParseStatements(query);
var updatedQuery = worker.UpdateSqlWithHashCodes(statements[0]);
var results = worker.ExecuteQuery(updatedQuery);
Assert.AreEqual(0, results.Tables[0].Rows.Count);
}
public void AdvancedRecordset_Converter_CanRunQueryContainingAlias()
{
string query = "select name as username from person";
var worker = CreatePersonAddressWorkers();
var statements = TSQLStatementReader.ParseStatements(query);
var updatedQuery = worker.UpdateSqlWithHashCodes(statements[0]);
var results = worker.ExecuteQuery(updatedQuery);
Assert.AreEqual(4, results.Tables[0].Rows.Count);
}
static void Main(string[] args)
{
TSQLSelectStatement select = TSQLStatementReader.ParseStatements(@"
SELECT OrderDateKey, SUM(SalesAmount) AS TotalSales
FROM FactInternetSales
GROUP BY OrderDateKey
HAVING OrderDateKey > 20010000
ORDER BY OrderDateKey;" )[0] as TSQLSelectStatement;
Console.ReadKey();
}
private List <string> ParseColumnNamesFromSql()
{
var statement = TSQLStatementReader.ParseStatements(SqlForRead).FirstOrDefault() as TSQLSelectStatement;
List <string> columNames = statement?.Select
.Tokens
.Where(token => token.Type == TSQL.Tokens.TSQLTokenType.Identifier)
.Select(token => token.AsIdentifier.Name)
.ToList();
return(columNames);
}
public void AdvancedRecordset_Converter_CanRunJoinQuery_ExpectAllResults()
{
var worker = CreatePersonAddressWorkers();
string query = "select * from person p join address a on p.address_id=a.id";
var statements = TSQLStatementReader.ParseStatements(query);
var updatedQuery = worker.UpdateSqlWithHashCodes(statements[0]);
var results = worker.ExecuteQuery(updatedQuery);
Assert.IsInstanceOfType(results, typeof(DataSet));
Assert.AreEqual(3, results.Tables[0].Rows.Count);
}
public void SelectStatement_CASTMissingASRegression()
{
// regression test for https://github.com/bruce-dunwiddie/tsql-parser/issues/89
List <TSQLStatement> statements = TSQLStatementReader.ParseStatements(
@"SELECT CAST ( 123.45 AS INT ) ",
includeWhitespace: true);
Assert.AreEqual(1, statements.Count);
TSQLSelectStatement select = statements.Single().AsSelect;
Assert.AreEqual(14, select.Tokens.Count);
}
public void SelectStatement_MultipleSetOperatorsRegressionWithWhitespace()
{
// regression test for https://github.com/bruce-dunwiddie/tsql-parser/issues/81
List <TSQLStatement> statements = TSQLStatementReader.ParseStatements(
@"SELECT 1
UNION
SELECT 2
UNION
SELECT 3" ,
includeWhitespace: true);
Assert.AreEqual(1, statements.Count);
TSQLSelectStatement select = statements.Single().AsSelect;
Assert.AreEqual(15, select.Tokens.Count);
Assert.AreEqual(2, select.SetOperators.Count);
Assert.AreEqual(1, select
.Select
.Columns
.Single()
.Expression
.AsConstant
.Literal
.AsNumericLiteral
.Value);
Assert.AreEqual(2, select
.SetOperators[0]
.Select
.Select
.Columns
.Single()
.Expression
.AsConstant
.Literal
.AsNumericLiteral
.Value);
Assert.AreEqual(3, select
.SetOperators[1]
.Select
.Select
.Columns
.Single()
.Expression
.AsConstant
.Literal
.AsNumericLiteral
.Value);
}
