public void GenerateInsertQuery_CustomColumnNames_Valid()
{
// Arrange
var generator = new SqlQueryGenerator <CustomColumnNamesEntity>("dbo", "Orders");
// Act
var insertQuery = generator.GenerateInsertQuery(new CustomColumnNamesEntity());
// Assert
Assert.Equal($"INSERT INTO [dbo].[Orders] ([DateCreated]) OUTPUT [inserted].[OrderId] AS [Id], [inserted].[DateCreated] AS [Date] VALUES (@Date);", insertQuery);
}
public void GenerateInsertQuery_NoPrimaryKey_Valid()
{
// Arrange
var generator = new SqlQueryGenerator <HeapEntity>("dbo", "Users");
// Act
var insertQuery = generator.GenerateInsertQuery(new HeapEntity());
// Assert
Assert.Equal($"INSERT INTO [dbo].[Users] ([Username], [Password]) OUTPUT [inserted].[Username], [inserted].[Password] VALUES (@Username, @Password);", insertQuery);
}
public void GenerateInsertQuery_MissingColumnValue_ContainsColumn()
{
// Arrange
var generator = new SqlQueryGenerator <CompositePrimaryKeyEntity>("dbo", "Users");
// Act
var insertQuery = generator.GenerateInsertQuery(new CompositePrimaryKeyEntity());
// Assert
Assert.Equal($"INSERT INTO [dbo].[Users] ([Username], [Password], [DateCreated]) OUTPUT [inserted].[Username], [inserted].[Password], [inserted].[DateCreated] VALUES (@Username, @Password, @DateCreated);", insertQuery);
}
public void GenerateInsertQuery_ColumnHasDefaultConstraintAndDefaultValue_Valid()
{
// Arrange
var generator = new SqlQueryGenerator <HasDefaultConstraintEntity>("dbo", "Users");
// Actj
var query = generator.GenerateInsertQuery(new HasDefaultConstraintEntity());
// Assert
Assert.Equal("INSERT INTO [dbo].[Users] ([Id]) OUTPUT [inserted].[Id], [inserted].[DateCreated] VALUES (@Id);", query);
}
public void GenerateInsertQuery_CustomSchema_Valid()
{
// Arrange
var generator = new SqlQueryGenerator <SinglePrimaryKeyEntity>("account", "Users");
// Act
var query = generator.GenerateInsertQuery(new SinglePrimaryKeyEntity());
// Assert
Assert.Equal("INSERT INTO [account].[Users] ([Username], [Password]) OUTPUT [inserted].[Id], [inserted].[Username], [inserted].[Password] VALUES (@Username, @Password);", query);
}
public void GenerateInsertQuery_ColumnHasDefaultConstraintAndNonDefaultValue_Valid()
{
// Arrange
var generator = new SqlQueryGenerator <HasDefaultConstraintEntity>("dbo", "Users");
var record = new HasDefaultConstraintEntity
{
Id = 42,
DateCreated = DateTime.Now
};
// Act
var query = generator.GenerateInsertQuery(record);
// Assert
Assert.Equal("INSERT INTO [dbo].[Users] ([Id], [DateCreated]) OUTPUT [inserted].[Id], [inserted].[DateCreated] VALUES (@Id, @DateCreated);", query);
}
public void GenerateSkierInsertQueryTest(
string fn,
string ln,
DateTime dob,
string url,
int countryId,
int sexId,
int id,
bool isRemoved)
{
string expectedQuery = "INSERT INTO [Hurace].[Skier] " +
"([FirstName], [LastName], [DateOfBirth], [ImageUrl], [CountryId], [SexId], [IsRemoved]) VALUES " +
"(@FirstName0, @LastName0, @DateOfBirth0, @ImageUrl0, @CountryId0, @SexId0, @IsRemoved0)";
var queryGenerator = new SqlQueryGenerator <Entities.Skier>();
(var generatedQuery, var queryParameters) = queryGenerator.GenerateInsertQuery(
new Entities.Skier
{
FirstName = fn,
LastName = ln,
DateOfBirth = dob,
ImageUrl = url,
CountryId = countryId,
SexId = sexId,
Id = id,
IsRemoved = isRemoved
});
Assert.Equal(expectedQuery, generatedQuery);
Assert.Equal(fn, queryParameters.FirstOrDefault(qp => qp.ParameterName == "FirstName0").Value);
Assert.Equal(ln, queryParameters.FirstOrDefault(qp => qp.ParameterName == "LastName0").Value);
Assert.Equal(dob.ToString("s"), queryParameters.FirstOrDefault(qp => qp.ParameterName == "DateOfBirth0").Value);
Assert.Equal(url, queryParameters.FirstOrDefault(qp => qp.ParameterName == "ImageUrl0").Value);
Assert.Equal(countryId, queryParameters.FirstOrDefault(qp => qp.ParameterName == "CountryId0").Value);
Assert.Equal(sexId, queryParameters.FirstOrDefault(qp => qp.ParameterName == "SexId0").Value);
Assert.Equal(
isRemoved ? "TRUE" : "FALSE",
queryParameters.FirstOrDefault(qp => qp.ParameterName == "IsRemoved0").Value);
}
public void GenerateInsertQueryTest()
{
string expectedParameterName = "Name0";
string expectedParameterValue = "AUS";
string expectedQuery = $"INSERT INTO [Hurace].[Country] ([Name]) VALUES (@{expectedParameterName})";
var queryGenerator = new SqlQueryGenerator <Entities.Country>();
(var generatedQuery, var queryParameters) =
queryGenerator.GenerateInsertQuery(
new Entities.Country
{
Name = expectedParameterValue
});
Assert.Equal(expectedQuery, generatedQuery);
Assert.Single(queryParameters);
Assert.Equal(expectedParameterName, queryParameters[0].ParameterName);
Assert.Equal(expectedParameterValue, queryParameters[0].Value);
}
public async Task <int> CreateAsync(T newInstance)
{
if (newInstance is null)
{
throw new ArgumentNullException(nameof(newInstance));
}
(string createQuery, QueryParameter[] parameters) = SqlQueryGenerator.GenerateInsertQuery(newInstance);
int affectedRowCount = await template.ExecuteAsync(createQuery, parameters)
.ConfigureAwait(false);
if (affectedRowCount != 1)
{
throw new InvalidOperationException($"The INSERT Query affected {affectedRowCount} rows -> should only affect 1");
}
string getLastGivenIdentityQuery = SqlQueryGenerator.GenerateGetLastIdentityQuery();
return(await template.QuerySingleInt32Async(getLastGivenIdentityQuery)
.ConfigureAwait(false));
}
public static void GenerateInsertQueryWithInvalidParametersTest()
{
var queryGenerator = new SqlQueryGenerator <Entities.Sex>();
Assert.Throws <ArgumentNullException>(() => queryGenerator.GenerateInsertQuery(null));
}