public void BuildCommandForSqlQueryWithSqlTextWhichUsesSameParameterTwice()
{
var sqlQuery = new SqlQuery(
"SELECT * FROM [Table] WHERE [Table].[Id] = @p0 AND [Table].[Value1] = @p1 OR @p1 IS NULL",
100, "hello");
var mockDbProviderFactory = new Mock<DbProviderFactory>();
mockDbProviderFactory.Setup(x => x.CreateCommand()).Returns(new System.Data.SqlClient.SqlCommand());
var dbDriver = new MsSqlDbDriver();
dbDriver.DbProviderFactory = mockDbProviderFactory.Object;
var command = dbDriver.BuildCommand(sqlQuery);
Assert.Equal(sqlQuery.CommandText, command.CommandText);
Assert.Equal(CommandType.Text, command.CommandType);
Assert.Equal(2, command.Parameters.Count);
var parameter1 = (IDataParameter)command.Parameters[0];
Assert.Equal(ParameterDirection.Input, parameter1.Direction);
Assert.Equal("@p0", parameter1.ParameterName);
Assert.Equal(sqlQuery.Arguments[0], parameter1.Value);
var parameter2 = (IDataParameter)command.Parameters[1];
Assert.Equal(ParameterDirection.Input, parameter2.Direction);
Assert.Equal("@p1", parameter2.ParameterName);
Assert.Equal(sqlQuery.Arguments[1], parameter2.Value);
}
public void BuildCommandForSqlQueryWithStoredProcedureWithParameters()
{
var sqlQuery = new SqlQuery(
"EXEC GetTableContents @identifier, @Cust_Name",
100, "hello");
var mockDbProviderFactory = new Mock<DbProviderFactory>();
mockDbProviderFactory.Setup(x => x.CreateCommand()).Returns(new System.Data.SqlClient.SqlCommand());
var dbDriver = new MsSqlDbDriver();
dbDriver.DbProviderFactory = mockDbProviderFactory.Object;
var command = dbDriver.BuildCommand(sqlQuery);
// The command text should only contain the stored procedure name.
Assert.Equal("GetTableContents", command.CommandText);
Assert.Equal(CommandType.StoredProcedure, command.CommandType);
Assert.Equal(2, command.Parameters.Count);
var parameter1 = (IDataParameter)command.Parameters[0];
Assert.Equal("@identifier", parameter1.ParameterName);
Assert.Equal(sqlQuery.Arguments[0], parameter1.Value);
var parameter2 = (IDataParameter)command.Parameters[1];
Assert.Equal("@Cust_Name", parameter2.ParameterName);
Assert.Equal(sqlQuery.Arguments[1], parameter2.Value);
}
public void BuildCommandForSqlQueryWithStoredProcedureWithParameters()
{
var command = new SqlCommand();
var sqlQuery = new SqlQuery(
"EXEC GetTableContents @identifier, @Cust_Name",
100, "hello");
var dbDriver = new MsSqlDbDriver();
dbDriver.BuildCommand(command, sqlQuery);
// The command text should only contain the stored procedure name.
Assert.Equal("GetTableContents", command.CommandText);
Assert.Equal(CommandType.StoredProcedure, command.CommandType);
Assert.Equal(2, command.Parameters.Count);
var parameter1 = (IDataParameter)command.Parameters[0];
Assert.Equal(DbType.Int32, parameter1.DbType);
Assert.Equal(ParameterDirection.Input, parameter1.Direction);
Assert.Equal("@identifier", parameter1.ParameterName);
Assert.Equal(sqlQuery.Arguments[0].Value, parameter1.Value);
var parameter2 = (IDataParameter)command.Parameters[1];
Assert.Equal(DbType.String, parameter2.DbType);
Assert.Equal(ParameterDirection.Input, parameter2.Direction);
Assert.Equal("@Cust_Name", parameter2.ParameterName);
Assert.Equal(sqlQuery.Arguments[1].Value, parameter2.Value);
}
public void BuildCommandForSqlQueryWithSqlTextWhichUsesSameParameterTwice()
{
var command = new SqlCommand();
var sqlQuery = new SqlQuery(
"SELECT * FROM [Table] WHERE [Table].[Id] = @p0 AND [Table].[Value1] = @p1 OR @p1 IS NULL",
100, "hello");
var dbDriver = new MsSqlDbDriver();
dbDriver.BuildCommand(command, sqlQuery);
Assert.Equal(sqlQuery.CommandText, command.CommandText);
Assert.Equal(CommandType.Text, command.CommandType);
Assert.Equal(2, command.Parameters.Count);
var parameter1 = (IDataParameter)command.Parameters[0];
Assert.Equal(DbType.Int32, parameter1.DbType);
Assert.Equal(ParameterDirection.Input, parameter1.Direction);
Assert.Equal("@p0", parameter1.ParameterName);
Assert.Equal(sqlQuery.Arguments[0].Value, parameter1.Value);
var parameter2 = (IDataParameter)command.Parameters[1];
Assert.Equal(DbType.String, parameter2.DbType);
Assert.Equal(ParameterDirection.Input, parameter2.Direction);
Assert.Equal("@p1", parameter2.ParameterName);
Assert.Equal(sqlQuery.Arguments[1].Value, parameter2.Value);
}
public void BuildCommandForSqlQueryWithStoredProcedureWithoutParameters()
{
var command = new SqlCommand();
var sqlQuery = new SqlQuery("EXEC GetTableContents");
var dbDriver = new MsSqlDbDriver();
dbDriver.BuildCommand(command, sqlQuery);
// The command text should only contain the stored procedure name.
Assert.Equal("GetTableContents", command.CommandText);
Assert.Equal(CommandType.StoredProcedure, command.CommandType);
Assert.Equal(0, command.Parameters.Count);
}
public void BuildCommandForSqlQueryWithStoredProcedureWithoutParameters()
{
var sqlQuery = new SqlQuery("EXEC GetTableContents");
var mockDbProviderFactory = new Mock<DbProviderFactory>();
mockDbProviderFactory.Setup(x => x.CreateCommand()).Returns(new System.Data.SqlClient.SqlCommand());
var dbDriver = new MsSqlDbDriver();
dbDriver.DbProviderFactory = mockDbProviderFactory.Object;
var command = dbDriver.BuildCommand(sqlQuery);
// The command text should only contain the stored procedure name.
Assert.Equal("GetTableContents", command.CommandText);
Assert.Equal(CommandType.StoredProcedure, command.CommandType);
Assert.Equal(0, command.Parameters.Count);
}
public void BuildCommandThrowsMicroLiteExceptionForParameterCountMismatch()
{
var sqlQuery = new SqlQuery(
"SELECT * FROM [Table] WHERE [Table].[Id] = @p0 AND [Table].[Value] = @p1",
100);
var dbDriver = new MsSqlDbDriver();
var exception = Assert.Throws<MicroLiteException>(
() => dbDriver.BuildCommand(sqlQuery));
Assert.Equal(ExceptionMessages.DbDriver_ArgumentsCountMismatch.FormatWith("2", "1"), exception.Message);
}
public void DbTypeTimeIsIncorrectlyHandledAsDateTime()
{
var command = new SqlCommand();
var sqlQuery = new SqlQuery(
"SELECT * FROM [Table] WHERE [TimeStamp] > @p0",
new SqlArgument(new TimeSpan(5, 30, 1), DbType.Time));
var dbDriver = new MsSqlDbDriver();
dbDriver.BuildCommand(command, sqlQuery);
Assert.Equal(SqlDbType.Time, command.Parameters[0].SqlDbType);
}