C# NpgsqlConnection.OpenAsync方法代码示例

        private async Task<string> Create()
        {
            string sql;
            var assembly = typeof(TestDatabase).GetTypeInfo().Assembly;
            using (var reader = new StreamReader(assembly.GetManifestResourceStream(_sqlFile)))
            {
                sql = await reader.ReadToEndAsync();
            }

            var dbName = await CreateDatabase();
            var connectionString = $"Server=localhost;Port=5432;Database={_databaseName};User Id=postgres;Password=s1mpl3;";

            using (var connection = new NpgsqlConnection(connectionString))
            {
                await connection.OpenAsync();

                using (var command = connection.CreateCommand())
                {
                    foreach (
                        var statement in
                        Regex.Split(sql, @"^\s*GO\s*$", RegexOptions.Multiline)
                            .Where(s => !string.IsNullOrWhiteSpace(s)))
                    {
                        //_logger.LogDebug(sql);
                        command.CommandText = statement;
                        await command.ExecuteNonQueryAsync();
                    }
                }
            }
            return _connectionString = connectionString;
        }

 public static IServiceCollection AddSqlConnectionBuilder(this IServiceCollection collection,
     string configuration)
 {
     var connection = new NpgsqlConnection(configuration);
     connection.OpenAsync();
     collection.AddTransient(provider => connection);
     return collection;
 }

        /// <summary>
        /// Gets a fresh, open and ready-to-use connection wrapper
        /// </summary>
        public async Task<PostgresConnection> GetConnection()
        {
            var connection = new NpgsqlConnection(_connectionString);
            
            await connection.OpenAsync();

            var currentTransaction = connection.BeginTransaction(IsolationLevel.ReadCommitted);

            return new PostgresConnection(connection, currentTransaction);
        }

      public static async Task<bool> CheckConnection(string connectionString)
      {
         if (string.IsNullOrWhiteSpace(connectionString))
            throw new ArgumentException("The argument may not be null or empty", nameof(connectionString));

         using (var connection = new NpgsqlConnection(connectionString))
         {
            await connection.OpenAsync();
            return connection.State == ConnectionState.Open;
         }
      }

        public async Task<DbConnection> OpenConnectionAsync()
        {
            if (string.IsNullOrWhiteSpace(_connectionString))
            {
                throw new InvalidOperationException("Connection to PostgreSQL MicroGarden Settings storage is not configured yet. Consider using UseMicroGardenSettingsPostgreSQLStore in Configure");
            }

            var connection = new NpgsqlConnection(_connectionString);
            await connection.OpenAsync();

            return connection;
        }

        public static async Task<EventStream> GetLatestStreamCreated()
        {
            using (var connection = new NpgsqlConnection(GetConnectionStringBuilder()))
            {
                await connection
                    .OpenAsync()
                    .ConfigureAwait(false);

                var query = await connection
                    .QueryAsync<EventStream>(SelectLatestStreamSql)
                    .ConfigureAwait(false);

                return query.Any()
                    ? query.Single()
                    : default(EventStream);
            }
        }

        // For more information on how to configure your application, visit http://go.microsoft.com/fwlink/?LinkID=398940
        public void ConfigureServices(IServiceCollection services)
        {
            services.AddMvc(options =>
            {
                var logger = Factory.CreateLogger("Web.Exception");
                options.InputFormatters.Clear();
                options.OutputFormatters.Clear();
                options.Filters.Add(new ExceptionFilter(logger, new JsonExceptionPage()));
                options.InputFormatters.Add(new JsonInputFormatter());

                options.OutputFormatters.Add(new JsonStandardMediaTypeFormatter());
                
            });

            services.AddTransient<IEmailService, EmailService>(
                provider => new EmailService(Configuration["Email:Host"], Configuration["Email:UserName"],
                    Configuration["Email:Password"], Configuration["Email:From"],
                    Int32.Parse(Configuration["Email:Port"])));

            var connection = new NpgsqlConnection(Configuration["Data:Npgsql:ConnectionString"]);
            connection.OpenAsync();

            services.AddTransient<DbConnection>(provider => connection);

            services.AddBasicAuthentication();


            var repositoryConfig = new DefaultDapperRepositoryOptions<SigmaUser>(connection,
                new SigmaPropertyStore(connection));
            services
                .StartAuthenticationConfiguration<SigmaUser>()
                .AddRepositories(repositoryConfig)
                .AddEmailProvider(Configuration["Email:Host"], Configuration["Email:UserName"],
                    Configuration["Email:Password"], Configuration["Email:From"],
                    int.Parse(Configuration["Email:Port"]))
                .AddLoggers(Factory)
                .ConfigureLoginOptions(new LoginManagerOptions<SigmaUser>
                {
                    ClaimsProvider =
                        new ClaimsProvider<SigmaUser>(repositoryConfig.UserStore, repositoryConfig.UserRoleStore,
                            new ClaimTypesOptions())
                })
                .BuildManagers();
        }

        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);
        }

 private async Task<string> CreateDatabase()
 {
     _databaseName = $"simple_{Guid.NewGuid():N}".ToLowerInvariant();
     var connectionString = $"Server=localhost;Port=5432;Database=postgres;User Id=postgres;Password=s1mpl3;";
     using (var connection = new NpgsqlConnection(connectionString))
     {
         await connection.OpenAsync();
         using (var cmd = connection.CreateCommand())
         {
             cmd.CommandText = $"CREATE DATABASE {_databaseName} WITH OWNER = postgres ENCODING = 'UTF8' CONNECTION LIMIT = -1;";
             _logger.LogDebug(cmd.CommandText);
             try
             {
                 await cmd.ExecuteNonQueryAsync();
             }
             catch (Exception ex)
             {
                 _logger.LogError(42999, ex, "Failed to CREATE DATABASE");
             }
         }
     }
     return _databaseName;
 }

 public void ConnectionRefusedAsync(bool pooled)
 {
     var csb = new NpgsqlConnectionStringBuilder(ConnectionString) { Port = 44444, Pooling = pooled };
     using (var conn = new NpgsqlConnection(csb))
     {
         Assert.That(async () => await conn.OpenAsync(), Throws.Exception
             .TypeOf<SocketException>()
             .With.Property(nameof(SocketException.SocketErrorCode)).EqualTo(SocketError.ConnectionRefused)
         );
         Assert.That(conn.FullState, Is.EqualTo(ConnectionState.Closed));
     }
 }

        public static async Task CreateEventStream(Guid streamId, string streamName)
        {
            using (var connection = new NpgsqlConnection(GetConnectionStringBuilder()))
            {
                await connection
                    .OpenAsync()
                    .ConfigureAwait(false);

                using (var command = connection.CreateCommand())
                {
                    command.CommandText = CreateEventStreamSql;
                    command.Parameters.AddWithValue("@streamId", streamId);
                    command.Parameters.AddWithValue("@streamName", streamName);
                    command.Parameters.AddWithValue("@createdOn", DateTime.UtcNow);

                    await command
                        .ExecuteNonQueryAsync()
                        .ConfigureAwait(false);
                }
            }
        }

        public static async Task<IEnumerable<StoredEvent>> GetStreamEvents(Guid streamId)
        {
            using (var connection = new NpgsqlConnection(GetConnectionStringBuilder()))
            {
                await connection
                    .OpenAsync()
                    .ConfigureAwait(false);

                var query = await connection
                    .QueryAsync<StoredEvent>(SelectStreamEventsSql)
                    .ConfigureAwait(false);

                return query.ToList();
            }
        }

        private async Task<NpgsqlConnection> CreateConnectionAsync(CancellationToken cancellationToken = default(CancellationToken))
        {
            var con = new NpgsqlConnection(ConnectionString);
            await con.OpenAsync(cancellationToken).ConfigureAwait(false);

            //TODO: Research server settings.

            return con;
        }

 private async Task<NpgsqlConnection> OpenConnection()
 {
     var conn = new NpgsqlConnection(_connectionStringBuilder);
     await conn
         .OpenAsync()
         .ConfigureAwait(false);
     return conn;
 }

        public void ConnectTimeoutAsync()
        {
            var unknownIp = Environment.GetEnvironmentVariable("NPGSQL_UNKNOWN_IP");
            if (unknownIp == null)
                TestUtil.IgnoreExceptOnBuildServer("NPGSQL_UNKNOWN_IP isn't defined and is required for connection timeout tests");

            var csb = new NpgsqlConnectionStringBuilder(ConnectionString)
            {
                Host = unknownIp,
                Pooling = false,
                Timeout = 2
            };
            using (var conn = new NpgsqlConnection(csb))
            {
                Assert.That(async () => await conn.OpenAsync(), Throws.Exception.TypeOf<TimeoutException>());
                Assert.That(conn.State, Is.EqualTo(ConnectionState.Closed));
            }
        }