public void ConstructBasic()
{
var statement = new PostgresStatement("select * from frog");
Assert.Equal("select * from frog", statement.Text);
Assert.Empty(statement.Parameters);
Assert.Equal(CommandType.Text, statement.Type);
}
public void ConstructType()
{
var statement = new PostgresStatement("exec proc", CommandType.StoredProcedure);
Assert.Equal("exec proc", statement.Text);
Assert.Empty(statement.Parameters);
Assert.Equal(CommandType.StoredProcedure, statement.Type);
}
public void ConstructTypeParameters()
{
var statement = new PostgresStatement(
"exec getfrogs @species, @colour", CommandType.StoredProcedure,
new NpgsqlParameter("@species", "tree"), new NpgsqlParameter("@colour", "green"));
Assert.Equal("exec getfrogs @species, @colour", statement.Text);
Assert.Equal(CommandType.StoredProcedure, statement.Type);
Assert.Equal(2, statement.Parameters.Length);
Assert.Contains(statement.Parameters, p => p.ParameterName == "@species" && (string)p.Value == "tree");
Assert.Contains(statement.Parameters, p => p.ParameterName == "@colour" && (string)p.Value == "green");
}
public void ConstructParameters()
{
var statement = new PostgresStatement(
"select * from frog where species like @species and colour like @colour",
new NpgsqlParameter("@species", "tree"), new NpgsqlParameter("@colour", "green"));
Assert.Equal("select * from frog where species like @species and colour like @colour", statement.Text);
Assert.Equal(CommandType.Text, statement.Type);
Assert.Equal(2, statement.Parameters.Length);
Assert.Contains(statement.Parameters, p => p.ParameterName == "@species" && (string)p.Value == "tree");
Assert.Contains(statement.Parameters, p => p.ParameterName == "@colour" && (string)p.Value == "green");
}
public async Task General()
{
const int recordCount = 10;
using (var dbGenerator = new DbGenerator())
{
var dbInterface = new PostgresInterface(dbGenerator.CreateDb());
const string insertSql = "INSERT INTO frog (name, date_of_birth) VALUES (@Name, @DateOfBirth);" +
"SELECT CAST(lastval() AS INT);";
const string readSingleSql = "SELECT * FROM frog WHERE id = @Id";
const string readAllSql = "SELECT * FROM frog";
const string updateSql = "UPDATE frog SET name = @NewName WHERE id BETWEEN @FromId AND @ToId";
var ids = new List <int>();
for (var i = 1; i <= recordCount; i++)
{
var nameParam = new NpgsqlParameter("@Name", $"Kermit{i}");
var dateOfBirthParam = new NpgsqlParameter("@DateOfBirth", DateTime.Today.AddDays(-i));
var statement = new PostgresStatement(insertSql, nameParam, dateOfBirthParam);
var id = (int)(await dbInterface.ExecuteScalarAsync(statement).ConfigureAwait(false));
// it's a new DB so should be the case, depending on this for following tests
Assert.Equal(i, id);
ids.Add(id);
}
Assert.Equal(recordCount, ids.Count);
foreach (var id in ids)
{
var idParam = new NpgsqlParameter("@Id", id);
var statement = new PostgresStatement(readSingleSql, idParam);
using (var reader = await dbInterface.GetReaderAsync(statement).ConfigureAwait(false))
{
Assert.True(await reader.ReadAsync().ConfigureAwait(false));
Assert.Equal(id, reader["id"]);
Assert.Equal($"Kermit{id}", reader["name"]);
Assert.Equal(DateTime.Today.AddDays(-id), Convert.ToDateTime(reader["date_of_birth"]));
}
}
var updateStatement = new PostgresStatement(updateSql, new NpgsqlParameter("@NewName", "Frank"),
new NpgsqlParameter("@FromId", 5), new NpgsqlParameter("@ToId", 8));
var affected = await dbInterface.ExecuteNonQueryAsync(updateStatement).ConfigureAwait(false);
Assert.Equal(4, affected);
var readAllStatement = new PostgresStatement(readAllSql);
using (var reader = await dbInterface.GetReaderAsync(readAllStatement).ConfigureAwait(false))
{
var count = 0;
while (await reader.ReadAsync().ConfigureAwait(false))
{
count++;
var id = (int)reader["id"];
if (id >= 5 && id <= 8)
{
Assert.Equal("Frank", reader["name"]);
}
else
{
Assert.Equal($"Kermit{id}", reader["name"]);
}
}
Assert.Equal(recordCount, count);
}
}
}
