public void FindParameters_NoParameter()
{
SqlParser sqlParser = new SqlParser();
var paramters = sqlParser.FindParameters("SELECT * FROM Users");
Assert.AreEqual(0, paramters.Count);
}
public void FindParameters_WithOneParameter()
{
SqlParser sqlParser = new SqlParser();
var parameters = sqlParser.FindParameters("SELECT * FROM Users WHERE Id = @Id");
Assert.AreEqual(1, parameters.Count);
Assert.AreEqual("Id", parameters[0]);
}
public void FindParameters_WithTwoParameters()
{
SqlParser sqlParser = new SqlParser();
var parameters = sqlParser.FindParameters("SELECT * FROM Users WHERE Id = @Id AND Name = @Name");
Assert.AreEqual(2, parameters.Count);
Assert.AreEqual("Id", parameters[0]);
Assert.AreEqual("Name", parameters[1]);
}
public void FindSqlVariables_DeclarationWithKeyWord()
{
var sql =
@"DECLARE @month_difference VARCHAR(50) = '2566927'
SELECT * FROM table WHERE a = @month_difference";
var parameters = SqlParser.FindParameters(sql);
Assert.AreEqual(0, parameters.Count);
}
public void FindSqlVariables_ExecuteStoreProcedure()
{
var sql = @"EXEC sp_api_user_GetServicesSchedule @id = @uid, @adminID = @aid";
var parameters = SqlParser.FindParameters(sql);
Assert.AreEqual(2, parameters.Count);
Assert.IsTrue(parameters.Contains("uid"));
Assert.IsTrue(parameters.Contains("aid"));
}
public void FindSqlVariables_MultipleDeclaration()
{
var sql =
@"DECLARE @Planner1 VARCHAR(50) = '2566927',
@Planner2 varchar(10) = '12201704',
@OtherVar int = 42
SELECT * FROM table";
var parameters = SqlParser.FindParameters(sql);
Assert.AreEqual(0, parameters.Count);
}
public void FindSqlVariables_Identity()
{
var sql =
@"SELECT id, @@IDENTITY
FROM dbo.table
WHERE name = @name;";
var parameters = SqlParser.FindParameters(sql);
Assert.AreEqual(1, parameters.Count);
Assert.AreEqual("name", parameters.First());
}
public void FindSqlVariables_SingleLineComment()
{
var sql =
@"SELECT * FROM table
WHERE -- a = @month_difference AND
@z = 1";
var parameters = SqlParser.FindParameters(sql);
Assert.AreEqual(1, parameters.Count);
Assert.IsTrue(parameters.Contains("z"));
}
public void FindSqlVariables_MultiLineComment()
{
var sql =
@"SELECT * FROM table
WHERE /*a = @month_difference AND
@z = 1 AND */ @b = 4
-- @c = 2";
var parameters = SqlParser.FindParameters(sql);
Assert.AreEqual(1, parameters.Count);
Assert.IsTrue(parameters.Contains("b"));
}
public void FindParameters_WithTwoParametersWhenInsert()
{
SqlParser sqlParser = new SqlParser();
var parameters = sqlParser.FindParameters(@"INSERT INTO [dbo].[DimCustomer]
([GeographyKey]
,[CustomerAlternateKey])
VALUES (
@GeographyKey
,@CustomerAlternateKey)
");
Assert.AreEqual(2, parameters.Count);
Assert.AreEqual("GeographyKey", parameters[0]);
Assert.AreEqual("CustomerAlternateKey", parameters[1]);
}
public void FindSqlVariables_IfStatement()
{
var sql =
@"declare @result tinyint = 0;
IF @ip = 'unknown'
SET @result = 0;
ELSE
SET @result = 1;
select @result;";
var parameters = SqlParser.FindParameters(sql);
Assert.AreEqual(1, parameters.Count);
Assert.AreEqual("ip", parameters.First());
}
public static void TryReportDiagnostics(
string sqlText,
ICollection <string> sharpParameters,
Location location,
SyntaxNodeAnalysisContext context,
Orm orm)
{
if (string.IsNullOrEmpty(sqlText))
{
return;
}
if (sharpParameters == null)
{
return;
}
const char SqlVariableDeclarationSymbol = '@';
var sqlVariables = SqlParser.FindParameters(sqlText).ToList();
if (orm == Orm.Dapper)
{
sqlVariables.AddRange(SqlParser.FindDapperLiterals(sqlText));
}
sharpParameters = sharpParameters.Select(p => p.Trim(SqlVariableDeclarationSymbol)).ToList();
foreach (var notFoundArgument in sqlVariables.Except(
sharpParameters,
StringComparer.InvariantCultureIgnoreCase))
{
context.ReportDiagnostic(Diagnostic.Create(CsharpArgumentNotFoundRule, location, notFoundArgument));
}
foreach (var notFoundVariable in sharpParameters.Except(
sqlVariables,
StringComparer.InvariantCultureIgnoreCase))
{
context.ReportDiagnostic(Diagnostic.Create(SqlParameterNotFoundRule, location, notFoundVariable));
}
}
public void FindSqlVariables_DeclareVariable()
{
var sql =
@"DECLARE @ids udt_ids_list;
INSERT INTO @ids
SELECT id
FROM dbo.table
WHERE name = @name;
DECLARE @idsa udt_ids_list;
INSERT INTO @idsa
SELECT id
FROM dbo.table
WHERE name = @name;";
var parameters = SqlParser.FindParameters(sql);
Assert.AreEqual(1, parameters.Count);
Assert.AreEqual("name", parameters.First());
}
