public void GetParameterNamesWithTwentyTwoNamedParameters()
{
var parameterNames = SqlUtility.GetParameterNames("SELECT * FROM [Table1] WHERE [Column1] >= @p0 AND [Column2] <= @p1 AND [Column3] IN (@p2,@p3,@p4,@p5,@p6,@p7,@p8,@p9,@p10,@p11,@p12,@p13,@p14,@p15,@p16,@p17,@p18,@p19,@p20,@p21)");
Assert.NotNull(parameterNames);
Assert.Equal(22, parameterNames.Count);
}
public void GetParameterNamesWithAQuotedStringAndParameters()
{
var parameterNames = SqlUtility.GetParameterNames("SELECT 1 FROM table WHERE column1 <> '' AND id = @p0 AND column2 <> ''");
Assert.Equal(1, parameterNames.Count);
Assert.Equal("@p0", parameterNames[0]);
}
public void GetParameterNamesWithNoNamedParameters()
{
var parameterNames = SqlUtility.GetParameterNames("SELECT * FROM TABLE WHERE Col1 = ? AND (Col2 = ? OR ? IS NULL)");
Assert.NotNull(parameterNames);
Assert.Equal(0, parameterNames.Count);
}
public void GetParameterNamesShouldNotMatchTheLabelStatement()
{
var parameterNames = SqlUtility.GetParameterNames(@"delete_more:
DELETE TOP(500) FROM LogMessages WHERE LogDate < @p0
IF @@ROWCOUNT > 0 GOTO delete_more");
Assert.Equal(1, parameterNames.Count);
Assert.Equal("@p0", parameterNames[0]);
}
public void GetParameterNamesWithColonPrefixWherePredicateOnlyContainsSingleParameter()
{
var parameterNames = SqlUtility.GetParameterNames(":p0");
Assert.NotNull(parameterNames);
Assert.Equal(1, parameterNames.Count);
Assert.Equal(new List <string> {
":p0"
}, parameterNames);
}
public void GetParameterNamesWithColonPrefixAndNoAdditionalPrefixCharacter()
{
var parameterNames = SqlUtility.GetParameterNames("SELECT * FROM TABLE WHERE Col1 = :0 AND (Col2 = :1 OR :1 IS NULL)");
Assert.NotNull(parameterNames);
Assert.Equal(2, parameterNames.Count);
Assert.Equal(new List <string> {
":0", ":1"
}, parameterNames);
}
public void GetParameterNamesWithColonPrefix()
{
var parameterNames = SqlUtility.GetParameterNames("SELECT * FROM TABLE WHERE Col1 = :p0 AND (Col2 = :p1 OR :p1 IS NULL)");
Assert.NotNull(parameterNames);
Assert.Equal(2, parameterNames.Count);
Assert.Equal(new List <string> {
":p0", ":p1"
}, parameterNames);
}
public void GetParameterNamesWithAtPrefixAndMoreThanPrefixCharacter()
{
var parameterNames = SqlUtility.GetParameterNames("SELECT * FROM TABLE WHERE Col1 = @param0 AND (Col2 = @param1 OR @param1 IS NULL)");
Assert.NotNull(parameterNames);
Assert.Equal(2, parameterNames.Count);
Assert.Equal(new List <string> {
"@param0", "@param1"
}, parameterNames);
}
public void GetParameterNamesWhereTextStartsWithIdentifierAndContainsFurtherText()
{
var parameterNames = SqlUtility.GetParameterNames("@p0 IS NOT NULL AND [FirstName] LIKE @p0");
Assert.NotNull(parameterNames);
Assert.Equal(1, parameterNames.Count);
Assert.Equal(new List <string> {
"@p0"
}, parameterNames);
}
public void GetParameterNamesShouldNotMatchIfAnIdentifierInAQuotedString()
{
var parameterNames = SqlUtility.GetParameterNames(@"CREATE VIEW IF NOT EXISTS ListIDs_24h AS
SELECT
EPC,
substr( EPC, 15, 4 ) AS Type,
substr( EPC, 19 ) AS ID,
strftime( '%Y-%m-%d %H:%M:%S', Timestamp ) AS Timestamp
FROM LogEntries
WHERE Timestamp > datetime( 'now' ) - time( '24:00' )
ORDER BY Timestamp DESC");
Assert.Empty(parameterNames);
}
public IDbCommand BuildCommand(SqlQuery sqlQuery)
{
if (sqlQuery == null)
{
throw new ArgumentNullException("sqlQuery");
}
if (log.IsDebug)
{
log.Debug(LogMessages.DbDialect_BuildingCommand);
}
var parameterNames = SqlUtility.GetParameterNames(sqlQuery.CommandText);
if (parameterNames.Count == 0 && sqlQuery.Arguments.Count > 0)
{
parameterNames = Enumerable.Range(0, sqlQuery.Arguments.Count)
.Select(c => "Parameter" + c.ToString(CultureInfo.InvariantCulture))
.ToArray();
}
if (parameterNames.Count != sqlQuery.Arguments.Count)
{
throw new MicroLiteException(ExceptionMessages.DbDriver_ArgumentsCountMismatch.FormatWith(parameterNames.Count.ToString(CultureInfo.InvariantCulture), sqlQuery.Arguments.Count.ToString(CultureInfo.InvariantCulture)));
}
var command = this.DbProviderFactory.CreateCommand();
command.CommandText = this.GetCommandText(sqlQuery.CommandText);
command.CommandType = this.GetCommandType(sqlQuery.CommandText);
for (int i = 0; i < parameterNames.Count; i++)
{
var parameter = command.CreateParameter();
var parameterName = parameterNames[i];
var sqlArgument = sqlQuery.Arguments[i];
this.BuildParameter(parameter, parameterName, sqlArgument);
command.Parameters.Add(parameter);
}
if (this.SupportsCommandTimeout)
{
command.CommandTimeout = sqlQuery.Timeout;
}
return(command);
}
public void GetParameterNamesThrowsArgumentNullExceptionForNullCommandText()
{
Assert.Throws <ArgumentNullException>(() => SqlUtility.GetParameterNames(null));
}
public void GetParameterNamesShouldNotMatchColonColonIdentifier()
{
var parameterNames = SqlUtility.GetParameterNames("SELECT ::Foo");
Assert.Empty(parameterNames);
}
public void GetParameterNamesShouldNotMatchAtAtIdentifier()
{
var parameterNames = SqlUtility.GetParameterNames("SELECT @@IDENTITY");
Assert.Empty(parameterNames);
}
