public async void Reader() { using (var cmd = new NpgsqlCommand("SELECT 1", Conn)) using (var reader = await cmd.ExecuteReaderAsync()) { await reader.ReadAsync(); Assert.That(reader[0], Is.EqualTo(1)); } }
public async void Columnar() { using (var cmd = new NpgsqlCommand("SELECT NULL, 2, 'Some Text'", Conn)) using (var reader = await cmd.ExecuteReaderAsync(CommandBehavior.SequentialAccess)) { await reader.ReadAsync(); Assert.That(await reader.IsDBNullAsync(0), Is.True); Assert.That(await reader.GetFieldValueAsync<string>(2), Is.EqualTo("Some Text")); } }
public async void Columnar() { ExecuteNonQuery("INSERT INTO DATA (field_int4, field_text) VALUES (2, 'Some Text')"); using (var cmd = new NpgsqlCommand("SELECT field_int2, field_int4, field_text FROM data", Conn)) using (var reader = await cmd.ExecuteReaderAsync(CommandBehavior.SequentialAccess)) { await reader.ReadAsync(); Assert.That(await reader.IsDBNullAsync(0), Is.True); Assert.That(await reader.GetFieldValueAsync<string>(2), Is.EqualTo("Some Text")); } }
/// <summary> /// Calculates /// </summary> /// <param name="cmd"></param> /// <param name="ds"></param> /// <param name="outSrid"></param> /// <returns></returns> public static async Task <(double l, double b, double r, double t)> CalculateBBox(Npgsql.NpgsqlCommand cmd, DataStore ds, int?outSrid = null) { cmd.CommandText = outSrid.HasValue ? GetGeomBBoxSql(ds, outSrid) : GetGeomBBoxSql(ds); var output = (0d, 0d, 0d, 0d); using (var rdr = await cmd.ExecuteReaderAsync()) { if (rdr.HasRows) { //if any rows, there should be exactly one await rdr.ReadAsync(); output = ((double)rdr[0], (double)rdr[1], (double)rdr[2], (double)rdr[3]); } } return(output); }
/// <summary> /// 若使用【读写分离】,查询【从库】条件cmdText.StartsWith("SELECT "),否则查询【主库】 /// </summary> /// <param name="readerHander"></param> /// <param name="cmdType"></param> /// <param name="cmdText"></param> /// <param name="cmdParms"></param> /// <returns></returns> async public Task ExecuteReaderAsync(Func <NpgsqlDataReader, Task> readerHander, CommandType cmdType, string cmdText, params NpgsqlParameter[] cmdParms) { DateTime dt = DateTime.Now; string logtxt = ""; DateTime logtxt_dt = DateTime.Now; var pool = this.MasterPool; bool isSlave = false; //读写分离规则 if (this.SlavePools.Any() && cmdText.StartsWith("SELECT ", StringComparison.CurrentCultureIgnoreCase)) { var availables = slaveUnavailables == 0 ? //查从库 this.SlavePools : ( //查主库 slaveUnavailables == this.SlavePools.Count ? new List <NpgsqlConnectionPool>() : //查从库可用 this.SlavePools.Where(sp => sp.IsAvailable).ToList()); if (availables.Any()) { isSlave = true; pool = availables.Count == 1 ? this.SlavePools[0] : availables[slaveRandom.Next(availables.Count)]; } } Object <NpgsqlConnection> conn = null; NpgsqlCommand cmd = PrepareCommandAsync(cmdType, cmdText, cmdParms, ref logtxt); if (IsTracePerformance) { logtxt += $"PrepareCommand: {DateTime.Now.Subtract(logtxt_dt).TotalMilliseconds}ms Total: {DateTime.Now.Subtract(dt).TotalMilliseconds}ms\r\n"; } Exception ex = null; try { if (IsTracePerformance) { logtxt_dt = DateTime.Now; } if (isSlave) { //从库查询切换,恢复 bool isSlaveFail = false; try { if (cmd.Connection == null) { cmd.Connection = (conn = await pool.GetAsync()).Value; } //if (slaveRandom.Next(100) % 2 == 0) throw new Exception("测试从库抛出异常"); } catch { isSlaveFail = true; } if (isSlaveFail) { if (conn != null) { if (IsTracePerformance) { logtxt_dt = DateTime.Now; } pool.Return(conn, ex); if (IsTracePerformance) { logtxt += $"ReleaseConnection: {DateTime.Now.Subtract(logtxt_dt).TotalMilliseconds}ms Total: {DateTime.Now.Subtract(dt).TotalMilliseconds}ms"; } } LoggerException(pool, cmd, new Exception($"连接失败,准备切换其他可用服务器"), dt, logtxt, false); cmd.Parameters.Clear(); await ExecuteReaderAsync(readerHander, cmdType, cmdText, cmdParms); return; } } else { //主库查询 if (cmd.Connection == null) { cmd.Connection = (conn = await pool.GetAsync()).Value; } } if (IsTracePerformance) { logtxt += $"Open: {DateTime.Now.Subtract(logtxt_dt).TotalMilliseconds}ms Total: {DateTime.Now.Subtract(dt).TotalMilliseconds}ms\r\n"; logtxt_dt = DateTime.Now; } using (NpgsqlDataReader dr = await cmd.ExecuteReaderAsync() as NpgsqlDataReader) { if (IsTracePerformance) { logtxt += $"ExecuteReader: {DateTime.Now.Subtract(logtxt_dt).TotalMilliseconds}ms Total: {DateTime.Now.Subtract(dt).TotalMilliseconds}ms\r\n"; } while (true) { if (IsTracePerformance) { logtxt_dt = DateTime.Now; } bool isread = await dr.ReadAsync(); if (IsTracePerformance) { logtxt += $" dr.Read: {DateTime.Now.Subtract(logtxt_dt).TotalMilliseconds}ms Total: {DateTime.Now.Subtract(dt).TotalMilliseconds}ms\r\n"; } if (isread == false) { break; } if (readerHander != null) { object[] values = null; if (IsTracePerformance) { logtxt_dt = DateTime.Now; values = new object[dr.FieldCount]; for (int a = 0; a < values.Length; a++) { if (!await dr.IsDBNullAsync(a)) { values[a] = await dr.GetFieldValueAsync <object>(a); } } logtxt += $" dr.GetValues: {DateTime.Now.Subtract(logtxt_dt).TotalMilliseconds}ms Total: {DateTime.Now.Subtract(dt).TotalMilliseconds}ms\r\n"; logtxt_dt = DateTime.Now; } await readerHander(dr); if (IsTracePerformance) { logtxt += $" readerHander: {DateTime.Now.Subtract(logtxt_dt).TotalMilliseconds}ms Total: {DateTime.Now.Subtract(dt).TotalMilliseconds}ms ({string.Join(",", values)})\r\n"; } } } if (IsTracePerformance) { logtxt_dt = DateTime.Now; } dr.Close(); } if (IsTracePerformance) { logtxt += $"ExecuteReader_dispose: {DateTime.Now.Subtract(logtxt_dt).TotalMilliseconds}ms Total: {DateTime.Now.Subtract(dt).TotalMilliseconds}ms\r\n"; } } catch (Exception ex2) { ex = ex2; } if (conn != null) { if (IsTracePerformance) { logtxt_dt = DateTime.Now; } pool.Return(conn, ex); if (IsTracePerformance) { logtxt += $"ReleaseConnection: {DateTime.Now.Subtract(logtxt_dt).TotalMilliseconds}ms Total: {DateTime.Now.Subtract(dt).TotalMilliseconds}ms"; } } LoggerException(pool, cmd, ex, dt, logtxt); cmd.Parameters.Clear(); }
internal async Task <List <PostgresType> > LoadBackendTypes(NpgsqlConnection conn, NpgsqlTimeout timeout, bool async) { var commandTimeout = 0; // Default to infinity if (timeout.IsSet) { commandTimeout = (int)timeout.TimeLeft.TotalSeconds; if (commandTimeout <= 0) { throw new TimeoutException(); } } var typeLoadingQuery = GenerateTypesQuery(SupportsRangeTypes, SupportsEnumTypes, conn.Settings.LoadTableComposites); using (var command = new NpgsqlCommand(typeLoadingQuery, conn)) { command.CommandTimeout = commandTimeout; command.AllResultTypesAreUnknown = true; using (var reader = async ? await command.ExecuteReaderAsync() : command.ExecuteReader()) { var byOID = new Dictionary <uint, PostgresType>(); // First load the types themselves while (async ? await reader.ReadAsync() : reader.Read()) { timeout.Check(); var ns = reader.GetString(reader.GetOrdinal("nspname")); var internalName = reader.GetString(reader.GetOrdinal("typname")); var oid = Convert.ToUInt32(reader[reader.GetOrdinal("oid")]); Debug.Assert(internalName != null); Debug.Assert(oid != 0); var typeChar = reader.GetString(reader.GetOrdinal("type"))[0]; switch (typeChar) { case 'b': // Normal base type var baseType = new PostgresBaseType(ns, internalName, oid); byOID[baseType.OID] = baseType; continue; case 'a': // Array { var elementOID = Convert.ToUInt32(reader[reader.GetOrdinal("elemoid")]); Debug.Assert(elementOID > 0); if (!byOID.TryGetValue(elementOID, out var elementPostgresType)) { Log.Trace($"Array type '{internalName}' refers to unknown element with OID {elementOID}, skipping", conn.ProcessID); continue; } var arrayType = new PostgresArrayType(ns, internalName, oid, elementPostgresType); byOID[arrayType.OID] = arrayType; continue; } case 'r': // Range { var elementOID = Convert.ToUInt32(reader[reader.GetOrdinal("elemoid")]); Debug.Assert(elementOID > 0); if (!byOID.TryGetValue(elementOID, out var subtypePostgresType)) { Log.Trace($"Range type '{internalName}' refers to unknown subtype with OID {elementOID}, skipping", conn.ProcessID); continue; } var rangeType = new PostgresRangeType(ns, internalName, oid, subtypePostgresType); byOID[rangeType.OID] = rangeType; continue; } case 'e': // Enum var enumType = new PostgresEnumType(ns, internalName, oid); byOID[enumType.OID] = enumType; continue; case 'c': // Composite // Unlike other types, we don't var compositeType = new PostgresCompositeType(ns, internalName, oid); byOID[compositeType.OID] = compositeType; continue; case 'd': // Domain var baseTypeOID = Convert.ToUInt32(reader[reader.GetOrdinal("typbasetype")]); Debug.Assert(baseTypeOID > 0); if (!byOID.TryGetValue(baseTypeOID, out var basePostgresType)) { Log.Trace($"Domain type '{internalName}' refers to unknown base type with OID {baseTypeOID}, skipping", conn.ProcessID); continue; } var domainType = new PostgresDomainType(ns, internalName, oid, basePostgresType); byOID[domainType.OID] = domainType; continue; case 'p': // pseudo-type (record, void) // Hack this as a base type goto case 'b'; default: throw new ArgumentOutOfRangeException($"Unknown typtype for type '{internalName}' in pg_type: {typeChar}"); } } if (async) { await reader.NextResultAsync(); } else { reader.NextResult(); } LoadCompositeFields(reader, byOID); if (SupportsEnumTypes) { if (async) { await reader.NextResultAsync(); } else { reader.NextResult(); } LoadEnumLabels(reader, byOID); } return(byOID.Values.ToList()); } } }
private async Task<EventPage> buildEventPage(long lastEncountered, NpgsqlCommand cmd) { IList<IEvent> events = null; IList<long> sequences = new List<long>(); using (var reader = await cmd.ExecuteReaderAsync(_token).ConfigureAwait(false)) { while (await reader.ReadAsync(_token).ConfigureAwait(false)) { var seq = await reader.GetFieldValueAsync<long>(0, _token).ConfigureAwait(false); sequences.Add(seq); } if (sequences.Any()) { await reader.NextResultAsync(_token).ConfigureAwait(false); events = await _selector.ReadAsync(reader, _map, _token).ConfigureAwait(false); } else { events = new List<IEvent>(); } } return new EventPage(lastEncountered, sequences, events) {Count = events.Count}; }
public async Task<User> GetUserByEmailAsync(string theEmail) { try { await Connection.OpenAsync().ConfigureAwait(false); var aPreparedCommand = new NpgsqlCommand( "SELECT id, firstname, lastname, email, customerid, isemployee from appuser where email = :value1", Connection); var aParam = new NpgsqlParameter("value1", NpgsqlDbType.Text) { Value = theEmail }; aPreparedCommand.Parameters.Add(aParam); var aReader = await aPreparedCommand.ExecuteReaderAsync().ConfigureAwait(false); if (!aReader.HasRows) return null; var aReturn = new User(); while (await aReader.ReadAsync().ConfigureAwait(false)) { aReturn = ReadUser(aReader); } return aReturn; } catch (NpgsqlException) { return null; } catch (InvalidOperationException) { return null; } catch (SqlException) { return null; } catch (ConfigurationErrorsException) { return null; } finally { if (Connection.State == ConnectionState.Open) Connection.Close(); } }
public async Task<IEnumerable<Automobile>> GetAutomobilesAsync(IEnumerable<int> theAutomobileIds) { try { await Connection.OpenAsync().ConfigureAwait(false); var aPreparedCommand = new NpgsqlCommand( "SELECT id, vin, vehiclenumber, name, class, style, color, manufacturer, model, code, locationid from automobile where id = :value1", Connection); aPreparedCommand.Parameters.AddWithValue("value1", string.Join(",", theAutomobileIds)); var aReader = await aPreparedCommand.ExecuteReaderAsync(); if (!aReader.HasRows) return Enumerable.Empty<Automobile>().ToList(); var aReturnList = new List<Automobile>(); while (await aReader.ReadAsync()) { aReturnList.Add(ReadAutomobile(aReader)); } return aReturnList; } catch (NpgsqlException) { return Enumerable.Empty<Automobile>().ToList(); } catch (InvalidOperationException) { return Enumerable.Empty<Automobile>().ToList(); } catch (SqlException) { return Enumerable.Empty<Automobile>().ToList(); } catch (ConfigurationErrorsException) { return Enumerable.Empty<Automobile>().ToList(); } finally { if (Connection.State == ConnectionState.Open) Connection.Close(); } }
public async Task<IEnumerable<Driver>> GetDriverByLastNameAsync(string theDriverName) { try { await Connection.OpenAsync().ConfigureAwait(false); var aPreparedCommand = new NpgsqlCommand( "SELECT id, firstname, lastname, address, city, state, postalcode, country, licensenumber, licensestate, customerid from driver where id = :value1", Connection); var aParam = new NpgsqlParameter("value1", NpgsqlDbType.Text) { Value = theDriverName }; aPreparedCommand.Parameters.Add(aParam); var aReader = await aPreparedCommand.ExecuteReaderAsync().ConfigureAwait(false); if (!aReader.HasRows) return null; var aReturn = new List<Driver>(); while (await aReader.ReadAsync().ConfigureAwait(false)) { aReturn.Add(ReadDriver(aReader)); } return aReturn; } catch (NpgsqlException) { return null; } catch (InvalidOperationException) { return null; } catch (SqlException) { return null; } catch (ConfigurationErrorsException) { return null; } finally { if (Connection.State == ConnectionState.Open) Connection.Close(); } }
public async void ExecuteReaderAsync(int rows) { for (var i = 0; i < rows; i++) ExecuteNonQuery("INSERT INTO DATA (field_int4) VALUES (10)"); using (var metrics = TestMetrics.Start(TestRunTime, true)) { while (!metrics.TimesUp) { using (var cmd = new NpgsqlCommand("SELECT field_int4 FROM data", Conn)) using (var reader = await cmd.ExecuteReaderAsync()) { while (await reader.ReadAsync()) { } } metrics.IncrementIterations(); } } }
public async Task<Automobile> GetAutomobileAsync(int theAutomobileId) { try { await Connection.OpenAsync().ConfigureAwait(false); var aPreparedCommand = new NpgsqlCommand( "SELECT id, vin, vehiclenumber, name, class, style, color, manufacturer, model, code, locationid from automobile where id = :value1", Connection); var aParam = new NpgsqlParameter("value1", NpgsqlDbType.Integer) { Value = theAutomobileId }; aPreparedCommand.Parameters.Add(aParam); var aReader = await aPreparedCommand.ExecuteReaderAsync().ConfigureAwait(false); if (!aReader.HasRows) return null; var aReturn = new Automobile(); while (await aReader.ReadAsync().ConfigureAwait(false)) { aReturn = ReadAutomobile(aReader); } return aReturn; } catch (NpgsqlException) { return null; } catch (InvalidOperationException) { return null; } catch (SqlException) { return null; } catch (ConfigurationErrorsException) { return null; } finally { if (Connection.State == ConnectionState.Open) Connection.Close(); } }
public async Task<Customer> GetCustomerByNameAsync(string theCustomerName) { try { await Connection.OpenAsync().ConfigureAwait(false); var aPreparedCommand = new NpgsqlCommand( "SELECT id, name, allowsadditionaldrivers, allowsadditions, hasmaxrentaldays, maxrentaldays from customer where name = :value1", Connection); var aParam = new NpgsqlParameter("value1", NpgsqlDbType.Text) { Value = theCustomerName }; aPreparedCommand.Parameters.Add(aParam); var aReader = await aPreparedCommand.ExecuteReaderAsync().ConfigureAwait(false); if (!aReader.HasRows) return null; var aReturn = new Customer(); while (await aReader.ReadAsync().ConfigureAwait(false)) { aReturn = ReadCustomer(aReader); } return aReturn; } catch (NpgsqlException) { return null; } catch (InvalidOperationException) { return null; } catch (SqlException) { return null; } catch (ConfigurationErrorsException) { return null; } finally { if (Connection.State == ConnectionState.Open) Connection.Close(); } }
private async Task<EventPage> buildEventPage(long @from, NpgsqlCommand cmd) { IList<IEvent> events = null; IList<long> sequences = new List<long>(); long nextKnown = 0; long lastKnown = 0; using (var reader = await cmd.ExecuteReaderAsync(_token).ConfigureAwait(false)) { while (await reader.ReadAsync(_token).ConfigureAwait(false)) { var seq = await reader.GetFieldValueAsync<long>(0, _token).ConfigureAwait(false); sequences.Add(seq); } if (sequences.Any()) { await reader.NextResultAsync(_token).ConfigureAwait(false); events = await _selector.ReadAsync(reader, _map, null, _token).ConfigureAwait(false); } else { events = new List<IEvent>(); } nextKnown = await getLong(reader).ConfigureAwait(false); lastKnown = await getLong(reader).ConfigureAwait(false); } return new EventPage(@from, sequences, events) { Count = events.Count, NextKnownSequence = nextKnown, LastKnownSequence = lastKnown }; }
public async Task<IEnumerable<Location>> GetLocationsForCustomerAsync(int theCustomerId) { try { await Connection.OpenAsync().ConfigureAwait(false); var aPreparedCommand = new NpgsqlCommand("SELECT id, customerid, name, address, city, state, postalcode, country, latitude, longitude from location where customerid=:value1", Connection); aPreparedCommand.Parameters.AddWithValue("value1", theCustomerId); var aReader = await aPreparedCommand.ExecuteReaderAsync(); if (!aReader.HasRows) return Enumerable.Empty<Location>(); var aReturn = new List<Location>(); while (await aReader.ReadAsync().ConfigureAwait(false)) { aReturn.Add(ReadLocation(aReader)); } return aReturn; } catch (NpgsqlException) { return Enumerable.Empty<Location>(); } catch (InvalidOperationException ex) { return Enumerable.Empty<Location>(); } catch (SqlException) { return Enumerable.Empty<Location>(); } catch (ConfigurationErrorsException) { return Enumerable.Empty<Location>(); } finally { if (Connection.State == ConnectionState.Open) Connection.Close(); } }
public async Task<Location> GetLocationAsync(int theLocationId) { try { await Connection.OpenAsync().ConfigureAwait(false); var aPreparedCommand = new NpgsqlCommand( "SELECT id, customerid, name, address, city, state, postalcode, country, latitude, longitude from location where id = :value1", Connection); var aParam = new NpgsqlParameter("value1", NpgsqlDbType.Integer) { Value = theLocationId }; aPreparedCommand.Parameters.Add(aParam); var aReader = await aPreparedCommand.ExecuteReaderAsync().ConfigureAwait(false); if (!aReader.HasRows) return null; var aReturn = new Location(); while (await aReader.ReadAsync().ConfigureAwait(false)) { aReturn = ReadLocation(aReader); } return aReturn; } catch (NpgsqlException) { return null; } catch (InvalidOperationException) { return null; } catch (SqlException) { return null; } catch (ConfigurationErrorsException) { return null; } finally { if (Connection.State == ConnectionState.Open) Connection.Close(); } }
private static async Task executeCallbacksAsync(NpgsqlCommand cmd, CancellationToken tkn, BatchCommand batch, List<Exception> list) { using (var reader = await cmd.ExecuteReaderAsync(tkn).ConfigureAwait(false)) { if (batch.Callbacks.Any()) { if (batch.Callbacks[0] != null) { await batch.Callbacks[0].PostprocessAsync(reader, list, tkn).ConfigureAwait(false); } for (int i = 1; i < batch.Callbacks.Count; i++) { await reader.NextResultAsync(tkn).ConfigureAwait(false); if (batch.Callbacks[i] != null) { await batch.Callbacks[i].PostprocessAsync(reader, list, tkn).ConfigureAwait(false); } } } } }
public static async IAsyncEnumerable <PersistedEvent> GetAsync(this NpgsqlConnection conn, IEventFilter eventFilter, [EnumeratorCancellation] CancellationToken cancellationToken = default) { var parameters = new List <(string, object)>(); var whereClauses = new List <string>(); if (eventFilter.AggregateId.HasValue) { var name = "a"; whereClauses.Add($"\"AggregateId\" = @{name}"); parameters.Add((name, eventFilter.AggregateId.Value)); } if (eventFilter.Checkpoint.HasValue) { var name = "b"; whereClauses.Add($"\"Id\" > @{name}"); parameters.Add((name, eventFilter.Checkpoint.Value)); } var eventTypes = eventFilter.EventTypes.ToArray(); if (eventTypes.Any()) { var stringBuilder = new StringBuilder("\"EventType\" in ("); var whereClauseParts = new string[eventTypes.Length]; for (var i = 0; i < eventTypes.Length; i++) { var eventType = eventTypes[i]; var name = "c" + i; parameters.Add((name, eventType)); whereClauseParts[i] = "@" + name; } var partsString = string.Join(", ", whereClauseParts); stringBuilder.Append(partsString); stringBuilder.Append(")"); whereClauses.Add(stringBuilder.ToString()); } var whereClause = whereClauses.Any() ? "WHERE " + string.Join(" AND ", whereClauses) : ""; const string Id = "\"Id\""; var query = $"{Select} {s_queryColumns}, {Id} {From} {Table} {whereClause} order by {Id}"; await using var command = new NpgsqlCommand(query, conn); foreach (var(parameterName, value) in parameters) { command.Parameters.AddWithValue(parameterName, value); } await using var reader = await command.ExecuteReaderAsync(cancellationToken); while (await reader.ReadAsync(cancellationToken)) { var timestamp = reader.GetFieldValue <Instant>(0); var aggregateId = reader.GetFieldValue <Guid>(1); var version = reader.GetFieldValue <int>(2); var eventType = reader.GetFieldValue <string>(3); var metadata = reader.GetFieldValue <byte[]>(4); var payload = reader.GetFieldValue <byte[]>(5); var eventId = reader.GetFieldValue <long>(6); var result = new PersistedEvent() { AggregateId = aggregateId, EventType = eventType, Id = eventId, Metadata = metadata, Payload = payload, Timestamp = timestamp, Version = version }; yield return(result); } }