public async Task FirstQueryThrowsIfEmptyTestAsync()
{
var queryDispatcher =
new QueryDispatcher(
new QueryExecutor(
new CommandPreparer(),
new ConnectionFactory(ConnectionString.Default)));
var setupQueries = new String[]
{
"DROP TABLE IF EXISTS first_query_throws_if_empty_test;",
"CREATE TABLE first_query_throws_if_empty_test (id INT NOT NULL, value TEXT NOT NULL);"
};
foreach (var setupQuery in setupQueries)
{
await queryDispatcher.ExecuteAsync(new Query(setupQuery));
}
var listQuery = @"
SELECT id, value
FROM first_query_throws_if_empty_test;" ;
await Assert.ThrowsAsync <InvalidOperationException>(async() =>
{
await queryDispatcher.FirstAsync(new Query(listQuery), row =>
{
return(new
{
id = row.ReadInt32("id"),
value = row.ReadString("value")
});
});
});
}
public async Task ParameterizedQueryTestAsync()
{
var queryDispatcher =
new QueryDispatcher(
new QueryExecutor(
new CommandPreparer(),
new ConnectionFactory(ConnectionString.Default)));
var setupQueries = new String[]
{
"DROP TABLE IF EXISTS parameterized_query_test;",
"CREATE TABLE parameterized_query_test (id INT NOT NULL, value TEXT NOT NULL);",
"INSERT INTO parameterized_query_test VALUES (1, 'This');",
"INSERT INTO parameterized_query_test VALUES (2, 'is');",
"INSERT INTO parameterized_query_test VALUES (3, 'a');",
"INSERT INTO parameterized_query_test VALUES (4, 'test');"
};
foreach (var setupQuery in setupQueries)
{
await queryDispatcher.ExecuteAsync(new Query(setupQuery));
}
var query = @"
SELECT id, value
FROM parameterized_query_test
WHERE id > :id
ORDER BY id ASC;" ;
var parameters = new NpgsqlParameter[]
{
new NpgsqlParameter("id", 1)
};
var item = await queryDispatcher.FirstAsync(new Query(query, new Dictionary <String, String>(), parameters), row =>
{
return(new
{
id = row.ReadInt32("id"),
value = row.ReadString("value")
});
});
Assert.NotNull(item);
Assert.Equal(item.id, 2);
Assert.Equal(item.value, "is");
}
public async Task QueryBuilderTestAsync()
{
var queryDispatcher =
new QueryDispatcher(
new QueryExecutor(
new CommandPreparer(),
new ConnectionFactory(ConnectionString.Default)));
var setupQueries = new String[]
{
"DROP TABLE IF EXISTS query_builder_query_test;",
"CREATE TABLE query_builder_query_test (id INT NOT NULL, value TEXT NOT NULL);",
"INSERT INTO query_builder_query_test VALUES (1, 'This');",
"INSERT INTO query_builder_query_test VALUES (2, 'is');",
"INSERT INTO query_builder_query_test VALUES (3, 'a');",
"INSERT INTO query_builder_query_test VALUES (4, 'test');"
};
foreach (var setupQuery in setupQueries)
{
await queryDispatcher.ExecuteAsync(new Query(setupQuery));
}
var query =
new QueryBuilder()
.SetQuery(@"
SELECT id, value
FROM query_builder_query_test
WHERE id > :id
ORDER BY id ASC;" )
.SetParameter("id", 3)
.Build();
var item = await queryDispatcher.FirstAsync(query, row =>
{
return(new
{
id = row.ReadInt32("id"),
value = row.ReadString("value")
});
});
Assert.NotNull(item);
Assert.Equal(item.id, 4);
Assert.Equal(item.value, "test");
}
public async Task LiteralQueryTestAsync()
{
var queryDispatcher =
new QueryDispatcher(
new QueryExecutor(
new CommandPreparer(),
new ConnectionFactory(ConnectionString.Default)));
var setupQueries = new String[]
{
"DROP TABLE IF EXISTS literal_query_test;",
"CREATE TABLE literal_query_test (id INT NOT NULL);",
"INSERT INTO literal_query_test VALUES (1);",
"INSERT INTO literal_query_test VALUES (10);",
"INSERT INTO literal_query_test VALUES (5);",
};
foreach (var setupQuery in setupQueries)
{
await queryDispatcher.ExecuteAsync(new Query(setupQuery));
}
var query = @"
SELECT id
FROM literal_query_test
ORDER BY id :order" ;
var literals = new Dictionary <String, String>()
{
{ "order", "DESC" }
};
var item = await queryDispatcher.FirstAsync(new Query(query, literals, new NpgsqlParameter[0]), row =>
{
return(new
{
id = row.ReadInt32("id")
});
});
Assert.NotNull(item);
Assert.Equal(item.id, 10);
}
public async Task FirstQueryTestAsync()
{
var queryDispatcher =
new QueryDispatcher(
new QueryExecutor(
new CommandPreparer(),
new ConnectionFactory(ConnectionString.Default)));
var setupQueries = new String[]
{
"DROP TABLE IF EXISTS first_query_test;",
"CREATE TABLE first_query_test (id INT NOT NULL, value TEXT NOT NULL);",
"INSERT INTO first_query_test VALUES (1, 'This');",
"INSERT INTO first_query_test VALUES (2, 'is');",
"INSERT INTO first_query_test VALUES (3, 'a');",
"INSERT INTO first_query_test VALUES (4, 'test');"
};
foreach (var setupQuery in setupQueries)
{
await queryDispatcher.ExecuteAsync(new Query(setupQuery));
}
var listQuery = @"
SELECT id, value
FROM first_query_test
WHERE id > 2
ORDER BY id ASC;" ;
var item = await queryDispatcher.FirstAsync(new Query(listQuery), row =>
{
return(new
{
id = row.ReadInt32("id"),
value = row.ReadString("value")
});
});
Assert.NotNull(item);
Assert.Equal(item.id, 3);
Assert.Equal(item.value, "a");
}
