public async Task Scalar() { using (var conn = OpenConnection()) using (var cmd = new NpgsqlCommand("SELECT 1", conn)) { Assert.That(await cmd.ExecuteScalarAsync(), Is.EqualTo(1)); } }
/// <summary> /// Execute a function /// </summary> internal async Task <T> ExecuteFunction <T>(string function, bool async, params object[] arguments) { using (var command = new NpgsqlCommand(function, _connection)) { command.CommandType = CommandType.StoredProcedure; command.CommandText = function; foreach (var argument in arguments) { command.Parameters.Add(new NpgsqlParameter { Value = argument }); } return((T)(async ? await command.ExecuteScalarAsync() : command.ExecuteScalar())); } }
/// <summary> /// Execute a function /// </summary> internal async Task <T> ExecuteFunction <T>(string function, bool async, CancellationToken cancellationToken, params object[] arguments) { using var command = new NpgsqlCommand(function, Connection) { CommandType = CommandType.StoredProcedure, CommandText = function }; foreach (var argument in arguments) { command.Parameters.Add(new NpgsqlParameter { Value = argument }); } return((T)(async ? await command.ExecuteScalarAsync(cancellationToken) : command.ExecuteScalar()) !); }
private static async Task ExecuteDBAction(Npgsql.NpgsqlConnection connection, int threadId, int value) { using (DbCommand command = new Npgsql.NpgsqlCommand(selectquery, connection)) { command.Parameters.Add(new Npgsql.NpgsqlParameter("id", threadId)); command.Parameters.Add(new Npgsql.NpgsqlParameter("value", value)); var result = await command.ExecuteScalarAsync(); } Thread.Sleep(new Random().Next(50, 250)); using (DbCommand command = new Npgsql.NpgsqlCommand(updatequery, connection)) { command.Parameters.Add(new Npgsql.NpgsqlParameter("id", threadId)); command.Parameters.Add(new Npgsql.NpgsqlParameter("value", value)); var result = await command.ExecuteScalarAsync(); } }
/// <summary> /// 在【主库】执行 /// </summary> /// <param name="cmdType"></param> /// <param name="cmdText"></param> /// <param name="cmdParms"></param> /// <returns></returns> async public Task <object> ExecuteScalarAsync(CommandType cmdType, string cmdText, params NpgsqlParameter[] cmdParms) { DateTime dt = DateTime.Now; string logtxt = ""; Object <NpgsqlConnection> conn = null; NpgsqlCommand cmd = PrepareCommandAsync(cmdType, cmdText, cmdParms, ref logtxt); DateTime logtxt_dt = DateTime.Now; object val = null; Exception ex = null; try { if (cmd.Connection == null) { cmd.Connection = (conn = await this.MasterPool.GetAsync()).Value; } val = await cmd.ExecuteScalarAsync(); } catch (Exception ex2) { ex = ex2; } if (conn != null) { if (IsTracePerformance) { logtxt_dt = DateTime.Now; } this.MasterPool.Return(conn, ex); if (IsTracePerformance) { logtxt += $"ReleaseConnection: {DateTime.Now.Subtract(logtxt_dt).TotalMilliseconds}ms Total: {DateTime.Now.Subtract(dt).TotalMilliseconds}ms"; } } LoggerException(this.MasterPool, cmd, ex, dt, logtxt); cmd.Parameters.Clear(); return(val); }
protected async Task<object> ExecuteScalarAsync(string sql, NpgsqlConnection conn = null) { if (conn == null) conn = Conn; using (var cmd = new NpgsqlCommand(sql, conn)) return await cmd.ExecuteScalarAsync(); }
public async void Scalar() { using (var cmd = new NpgsqlCommand("SELECT 1", Conn)) { Assert.That(await cmd.ExecuteScalarAsync(), Is.EqualTo(1)); } }
public async Task<int> AddDriverAsync(Driver theDriver) { try { await Connection.OpenAsync().ConfigureAwait(false); var aCommand = new NpgsqlCommand( "Insert into driver (firstname, lastname, address, city, state, postalcode, country, licensenumber, licensestate, customerid) VALUES (:value1, :value2, :value3, :value4, :value5, :value6, :value7, :value8, :value9, :value10) RETURNING id", 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); // returns the id from the SELECT, RETURNING sql statement above return Convert.ToInt32(await aCommand.ExecuteScalarAsync().ConfigureAwait(false)); } catch (NpgsqlException) { return 0; } catch (InvalidOperationException) { return 0; } catch (SqlException) { return 0; } catch (ConfigurationErrorsException) { return 0; } finally { if (Connection.State == ConnectionState.Open) Connection.Close(); } }
public async Task<int> AddCustomerAsync(Customer theCustomer) { try { await Connection.OpenAsync().ConfigureAwait(false); var aCommand = new NpgsqlCommand( "Insert into customer (name, allowsadditionaldrivers, allowsadditions, hasmaxrentaldays, maxrentaldays) VALUES (:value1, :value2, :value3, :value4, :value5) RETURNING id", 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); // returns the id from the SELECT, RETURNING sql statement above return Convert.ToInt32(await aCommand.ExecuteScalarAsync().ConfigureAwait(false)); } catch (NpgsqlException) { return 0; } catch (InvalidOperationException) { return 0; } catch (SqlException) { return 0; } catch (ConfigurationErrorsException) { return 0; } finally { if (Connection.State == ConnectionState.Open) Connection.Close(); } }
/// <summary> /// Tests the connection asynchronously. /// </summary> /// <returns></returns> public override async Task TestConnectionAsync() { using (var con = await CreateConnectionAsync()) using (var cmd = new NpgsqlCommand("SELECT 1", con)) await cmd.ExecuteScalarAsync(); }
public async Task<int> AddLocationAsync(Location theLocation) { try { await Connection.OpenAsync().ConfigureAwait(false); var aCommand = new NpgsqlCommand( "Insert into location (customerid, name, address, city, state, postalcode, country, latitude, longitude) VALUES (:value1, :value2, :value3, :value4, :value5, :value6, :value7, :value8, :value9) RETURNING id", 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); // returns the id from the SELECT, RETURNING sql statement above return Convert.ToInt32(await aCommand.ExecuteScalarAsync().ConfigureAwait(false)); } catch (NpgsqlException) { return 0; } catch (InvalidOperationException) { return 0; } catch (SqlException) { return 0; } catch (ConfigurationErrorsException) { return 0; } finally { if (Connection.State == ConnectionState.Open) Connection.Close(); } }
public async Task<int> AddUserAsync(User theUser) { try { await Connection.OpenAsync().ConfigureAwait(false); var aCommand = new NpgsqlCommand( "Insert into appuser (firstname, lastname, email, customerid, isemployee) VALUES (:value1, :value2, :value3, :value4, :value5) RETURNING id", 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); // returns the id from the SELECT, RETURNING sql statement above return Convert.ToInt32(await aCommand.ExecuteScalarAsync().ConfigureAwait(false)); } catch (NpgsqlException) { return 0; } catch (InvalidOperationException) { return 0; } catch (SqlException) { return 0; } catch (ConfigurationErrorsException) { return 0; } finally { if (Connection.State == ConnectionState.Open) Connection.Close(); } }
public async Task<int> AddAutomobileAsync(Automobile theAutomobile) { try { await Connection.OpenAsync().ConfigureAwait(false); var aCommand = new NpgsqlCommand( "Insert into automobile (vin, vehiclenumber, name, class, style, color, manufacturer, model, code, locationid) VALUES (:value1, :value2, :value3, :value4, :value5, :value6, :value7, :value8, :value9, :value10) RETURNING id", 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); // returns the id from the SELECT, RETURNING sql statement above return Convert.ToInt32(await aCommand.ExecuteScalarAsync().ConfigureAwait(false)); } catch (NpgsqlException) { return 0; } catch (InvalidOperationException) { return 0; } catch (SqlException) { return 0; } catch (ConfigurationErrorsException) { return 0; } finally { if (Connection.State == ConnectionState.Open) Connection.Close(); } }