public void Cancel()
{
var cancellationSource = new CancellationTokenSource();
using (var cmd = new NpgsqlCommand("SELECT pg_sleep(5)", Conn))
{
Task.Factory.StartNew(() =>
{
Thread.Sleep(300);
cancellationSource.Cancel();
});
var t = cmd.ExecuteNonQueryAsync(cancellationSource.Token);
Task.WaitAny(t);
Assert.That(t.IsCanceled);
}
}
public async void NonQuery()
{
using (var cmd = new NpgsqlCommand("INSERT INTO data (field_int4) VALUES (4)", Conn))
{
await cmd.ExecuteNonQueryAsync();
}
Assert.That(ExecuteScalar("SELECT field_int4 FROM data"), Is.EqualTo(4));
}
public async void NonQuery()
{
ExecuteNonQuery("CREATE TEMP TABLE data (int INTEGER)");
using (var cmd = new NpgsqlCommand("INSERT INTO data (int) VALUES (4)", Conn))
{
await cmd.ExecuteNonQueryAsync();
}
Assert.That(ExecuteScalar("SELECT int FROM data"), Is.EqualTo(4));
}
public async Task NonQuery()
{
using (var conn = OpenConnection())
{
conn.ExecuteNonQuery("CREATE TEMP TABLE data (int INTEGER)");
using (var cmd = new NpgsqlCommand("INSERT INTO data (int) VALUES (4)", conn))
await cmd.ExecuteNonQueryAsync();
Assert.That(conn.ExecuteScalar("SELECT int FROM data"), Is.EqualTo(4));
}
}
private static async Task TestPostgres()
{
try
{
using (var cn = new NpgsqlConnection("Host=localhost;Username=postgres;Password=secretsquirrel"))
{
await cn.OpenAsync();
using (var cmd = new NpgsqlCommand("CREATE DATABASE simple", cn))
{
await cmd.ExecuteNonQueryAsync();
}
System.Console.WriteLine("Created database");
}
} catch {}
try
{
using (var cn = new NpgsqlConnection("Host=localhost;Username=postgres;Password=secretsquirrel;Database=simple"))
{
await cn.OpenAsync();
using (var cmd = new NpgsqlCommand("CREATE TABLE Starships (Id integer not null, Name varchar(100) not null)", cn))
{
await cmd.ExecuteNonQueryAsync();
}
using (var cmd = new NpgsqlCommand("INSERT INTO Starships VALUES (42, 'Heart of Gold')", cn))
{
await cmd.ExecuteNonQueryAsync();
}
System.Console.WriteLine("Created table");
}
} catch {}
var db = new SimpleData().Open(@"Host=localhost;Username=postgres;Password=secretsquirrel;Database=simple",
typeof(PostgresAdapter));
var starship = await db.Starships.GetById(42);
Console.WriteLine(starship.Name);
}
protected async Task<int> ExecuteNonQueryAsync(string sql, NpgsqlConnection conn = null)
{
if (conn == null)
conn = Conn;
using (var cmd = new NpgsqlCommand(sql, conn))
return await cmd.ExecuteNonQueryAsync();
}
public async Task DeleteDriverAsync(Driver theDriver)
{
try
{
await Connection.OpenAsync().ConfigureAwait(false);
var aCommand = new NpgsqlCommand("DELETE from driver where id=:value1", Connection);
aCommand.Parameters.AddWithValue("value1", theDriver.Id);
await aCommand.ExecuteNonQueryAsync().ConfigureAwait(false);
}
// no catch here, this is a reference project
// TODO: add catch and actions here
finally
{
if (Connection.State == ConnectionState.Open)
Connection.Close();
}
}
public async Task UpdateDriverAsync(Driver theDriver)
{
try
{
await Connection.OpenAsync().ConfigureAwait(false);
var aCommand = new NpgsqlCommand(
"UPDATE driver SET firstname = :value1, lastname = :value2, address = :value3, city = :value4, state = :value5, postalcode = :value6, country = :value7, licensenumber = :value8, licensestate = :value9, customerid = :value10 where id=:value11;", Connection);
aCommand.Parameters.AddWithValue("value1", theDriver.FirstName);
aCommand.Parameters.AddWithValue("value2", theDriver.LastName);
aCommand.Parameters.AddWithValue("value3", theDriver.Address);
aCommand.Parameters.AddWithValue("value4", theDriver.City);
aCommand.Parameters.AddWithValue("value5", theDriver.State);
aCommand.Parameters.AddWithValue("value6", theDriver.PostalCode);
aCommand.Parameters.AddWithValue("value7", theDriver.Country);
aCommand.Parameters.AddWithValue("value8", theDriver.LicenseNumber);
aCommand.Parameters.AddWithValue("value9", theDriver.LicenseState);
aCommand.Parameters.AddWithValue("value10", theDriver.CustomerId);
aCommand.Parameters.AddWithValue("value11", theDriver.Id);
await aCommand.ExecuteNonQueryAsync().ConfigureAwait(false);
}
// no catch here, this is a reference project
// TODO: add catch and actions here
finally
{
if (Connection.State == ConnectionState.Open)
Connection.Close();
}
}
public async Task UpdateCustomerAsync(Customer theCustomer)
{
try
{
await Connection.OpenAsync().ConfigureAwait(false);
var aCommand = new NpgsqlCommand(
"UPDATE customer SET name=:value1, allowsadditionaldrivers=:value2, allowsadditions=:value3, hasmaxrentaldays=:value4, maxrentaldays=:value5 where id=:value6;", Connection);
aCommand.Parameters.AddWithValue("value1", theCustomer.Name);
aCommand.Parameters.AddWithValue("value2", theCustomer.AllowsAdditionalDrivers);
aCommand.Parameters.AddWithValue("value3", theCustomer.AllowsAdditions);
aCommand.Parameters.AddWithValue("value4", theCustomer.HasMaxRentalDays);
aCommand.Parameters.AddWithValue("value5", theCustomer.MaxRentalDays);
aCommand.Parameters.AddWithValue("value6", theCustomer.Id);
await aCommand.ExecuteNonQueryAsync().ConfigureAwait(false);
}
// no catch here, this is a reference project
// TODO: add catch and actions here
finally
{
if (Connection.State == ConnectionState.Open)
Connection.Close();
}
}
public async Task UpdateLocationAsync(Location theLocation)
{
try
{
await Connection.OpenAsync().ConfigureAwait(false);
var aCommand = new NpgsqlCommand(
"UPDATE location SET customerid = :value1, name = :value2, address = :value3, city = :value4, state = :value5, postalcode = :value6, country = :value7, latitude = :value8, longitude = :value9 where id=:value10;", Connection);
aCommand.Parameters.AddWithValue("value1", theLocation.CustomerId);
aCommand.Parameters.AddWithValue("value2", theLocation.Name);
aCommand.Parameters.AddWithValue("value3", theLocation.Address);
aCommand.Parameters.AddWithValue("value4", theLocation.City);
aCommand.Parameters.AddWithValue("value5", theLocation.State);
aCommand.Parameters.AddWithValue("value6", theLocation.PostalCode);
aCommand.Parameters.AddWithValue("value7", theLocation.Country);
aCommand.Parameters.AddWithValue("value8", theLocation.Latitude);
aCommand.Parameters.AddWithValue("value9", theLocation.Longitude);
aCommand.Parameters.AddWithValue("value10", theLocation.Id);
await aCommand.ExecuteNonQueryAsync().ConfigureAwait(false);
}
// no catch here, this is a reference project
// TODO: add catch and actions here
finally
{
if (Connection.State == ConnectionState.Open)
Connection.Close();
}
}
public async Task UpdateUserAsync(User theUser)
{
try
{
await Connection.OpenAsync().ConfigureAwait(false);
var aCommand = new NpgsqlCommand(
"UPDATE appuser SET firstname = :value1, lastname = :value2, email = :value3, customerid = :value4, isemployee = :value5 where id=:value6;", Connection);
aCommand.Parameters.AddWithValue("value1", theUser.FirstName);
aCommand.Parameters.AddWithValue("value2", theUser.LastName);
aCommand.Parameters.AddWithValue("value3", theUser.Email);
aCommand.Parameters.AddWithValue("value4", theUser.CustomerId);
aCommand.Parameters.AddWithValue("value5", theUser.IsEmployee);
aCommand.Parameters.AddWithValue("value6", theUser.Id);
await aCommand.ExecuteNonQueryAsync().ConfigureAwait(false);
}
// no catch here, this is a reference project
// TODO: add catch and actions here
finally
{
if (Connection.State == ConnectionState.Open)
Connection.Close();
}
}
public async Task UpdateAutomobileAsync(Automobile theAutomobile)
{
try
{
await Connection.OpenAsync().ConfigureAwait(false);
var aCommand = new NpgsqlCommand(
"UPDATE automobile SET vin=:value1, vehiclenumber=:value2, name=:value3, class=:value4, style=:value5, color=:value6, manufacturer=:value7, model=:value8, code=:value9, locationid=:value10 where id=:value11;", Connection);
aCommand.Parameters.AddWithValue("value1", theAutomobile.VIN);
aCommand.Parameters.AddWithValue("value2", theAutomobile.VehicleNumber);
aCommand.Parameters.AddWithValue("value3", theAutomobile.Name);
aCommand.Parameters.AddWithValue("value4", theAutomobile.Class);
aCommand.Parameters.AddWithValue("value5", theAutomobile.Style);
aCommand.Parameters.AddWithValue("value6", theAutomobile.Color);
aCommand.Parameters.AddWithValue("value7", theAutomobile.Manufacturer);
aCommand.Parameters.AddWithValue("value8", theAutomobile.Model);
aCommand.Parameters.AddWithValue("value9", theAutomobile.Code);
aCommand.Parameters.AddWithValue("value10", theAutomobile.LocationId);
aCommand.Parameters.AddWithValue("value11", theAutomobile.Id);
await aCommand.ExecuteNonQueryAsync().ConfigureAwait(false);
}
// no catch here, this is a reference project
// TODO: add catch and actions here
finally
{
if (Connection.State == ConnectionState.Open)
Connection.Close();
}
}
