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)); } }
/// <summary> /// executes an insert batch sql against a datastore /// </summary> /// <param name="cmd"></param> /// <param name="ds"></param> /// <param name="insertData"></param> /// <returns></returns> protected internal static async Task ExecuteInsertBatch(Npgsql.NpgsqlCommand cmd, DataStore ds, List <string> insertData) { cmd.CommandText = $@"INSERT INTO {ds.DataSource.Schema}.{ds.DataSource.Table} ({string.Join(",", ds.DataSource.Columns.Select(c => c.Name))}) {string.Join($"{Environment.NewLine} UNION ALL ", insertData)} ;"; await cmd.ExecuteNonQueryAsync(); cmd.Parameters.Clear(); insertData.Clear(); }
/// <summary> /// creates an index on a location column if it is present in the data store and of string data type /// </summary> /// <param name="cmd"></param> /// <param name="ds"></param> /// <returns></returns> protected static async Task CreateLocationIndex(Npgsql.NpgsqlCommand cmd, DataStore ds) { //location col indexing as required var locationCol = ds.DataSource.Columns.FirstOrDefault(c => c.Name == "location"); if (locationCol != null && locationCol.Type == ColumnDataType.String) { cmd.CommandText = GetCreateIdxSql(ds, "location"); await cmd.ExecuteNonQueryAsync(); } }
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); }
/// <summary> /// executes an upsert sql against a data store /// </summary> /// <param name="cmd"></param> /// <param name="ds"></param> /// <param name="insertData"></param> /// <param name="key"></param> /// <param name="skipCols"></param> /// <param name="colCnt"></param> /// <returns></returns> protected internal static async Task ExecuteUpsertBatch(Npgsql.NpgsqlCommand cmd, DataStore ds, List <string> insertData, int colCnt, IEnumerable <string> key, IEnumerable <string> skipCols) { var colNameRow = string.Empty; var discardColNameRowData = string.Empty; if (key?.Any() == true) { var colsData = new string[colCnt]; var keyCols = key.ToArray(); for (var colIdx = 0; colIdx < colCnt; colIdx++) { colsData[colIdx] = colIdx < keyCols.Length ? $"NULL as {keyCols[colIdx]}" : "NULL"; } colNameRow = $"SELECT {string.Join(",", colsData)} UNION ALL"; discardColNameRowData = $"WHERE {string.Join(" IS NOT NULL AND ", key)} IS NOT NULL"; } cmd.CommandText = $@"INSERT INTO {ds.DataSource.Schema}.{ds.DataSource.Table} ({string.Join(",", ds.DataSource.Columns.Where(c => skipCols == null || !skipCols.Contains(c.Name)).Select(c => c.Name))}) {(key?.Any() == true ? $"SELECT DISTINCT ON ({string.Join(",", key)}) * FROM (" : string.Empty)} {colNameRow} {string.Join($"{Environment.NewLine} UNION ALL ", insertData)} {(key?.Any() == true ? ") as data" : string.Empty)} {discardColNameRowData} ON CONFLICT({string.Join(",", key)}) DO UPDATE SET {GetDoUpdateCols(ds, key, skipCols)} ;"; await cmd.ExecuteNonQueryAsync(); cmd.Parameters.Clear(); insertData.Clear(); }
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(); } }