C# NpgsqlConnection类代码示例

    /// <summary>
    /// 执行 Transact-SQL 语句并返回受影响的行数。
    /// </summary>
    public int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText,
        params DbParameter[] cmdParms)
    {
        NpgsqlCommand cmd = new NpgsqlCommand();

        using (NpgsqlConnection conn = new NpgsqlConnection(connectionString))
        {
            PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
            int val = cmd.ExecuteNonQuery();
            cmd.Parameters.Clear();
            return val;
        }
    }

 List<AggregatedChampionStatistics> LoadAggregatedChampionStatistics(Summoner summoner, MapType map, GameModeType gameMode, NpgsqlConnection database)
 {
     const string query =
         "with source as " +
         "(select player.champion_id, player.won, player.kills, player.deaths, player.assists, player.gold, player.minion_kills from game_result, player where game_result.map = cast(:map as map_type) and game_result.game_mode = cast(:game_mode as game_mode_type) and (game_result.team1_id = player.team_id or game_result.team2_id = player.team_id) and player.summoner_id = :summoner_id) " +
         "select statistics.champion_id, coalesce(champion_wins.wins, 0) as wins, coalesce(champion_losses.losses, 0) as losses, statistics.kills, statistics.deaths, statistics.assists, statistics.gold, statistics.minion_kills from " +
         "(select source.champion_id, sum(source.kills) as kills, sum(source.deaths) as deaths, sum(source.assists) as assists, sum(source.gold) as gold, sum(source.minion_kills) as minion_kills from source group by source.champion_id) " +
         "as statistics " +
         "left outer join " +
         "(select champion_id, count(*) as wins from source where won = true group by champion_id) " +
         "as champion_wins " +
         "on statistics.champion_id = champion_wins.champion_id " +
         "left outer join " +
         "(select champion_id, count(*) as losses from source where won = false group by champion_id) " +
         "as champion_losses " +
         "on statistics.champion_id = champion_losses.champion_id;";
     DatabaseCommand select = GetCommand(query, database);
     select.SetEnum("map", map.ToEnumString());
     select.SetEnum("game_mode", gameMode.ToEnumString());
     select.Set("summoner_id", summoner.Id);
     using (NpgsqlDataReader reader = select.ExecuteReader())
     {
         List<AggregatedChampionStatistics> output = new List<AggregatedChampionStatistics>();
         while (reader.Read())
         {
             AggregatedChampionStatistics statistics = new AggregatedChampionStatistics(reader);
             statistics.ChampionName = GetChampionName(statistics.ChampionId);
             output.Add(statistics);
         }
         output.Sort();
         return output;
     }
 }

        public void UseAllConnectionsInPool()
        {
            // As this method uses a lot of connections, clear all connections from all pools before starting.
            // This is needed in order to not reach the max connections allowed and start to raise errors.

            NpgsqlConnection.ClearAllPools();
            try {
                var openedConnections = new List<NpgsqlConnection>();
                // repeat test to exersize pool
                for (var i = 0; i < 10; ++i) {
                    try {
                        // 18 since base class opens two and the default pool size is 20
                        for (var j = 0; j < 18; ++j) {
                            var connection = new NpgsqlConnection(ConnectionString);
                            connection.Open();
                            openedConnections.Add(connection);
                        }
                    } finally {
                        openedConnections.ForEach(delegate(NpgsqlConnection con) { con.Dispose(); });
                        openedConnections.Clear();
                    }
                }
            } finally {
                NpgsqlConnection.ClearAllPools();
            }
        }

 public void ResetOnClose()
 {
     var conn = new NpgsqlConnection(ConnectionString + ";SearchPath=public");
     conn.Open();
     ExecuteNonQuery("SET search_path=foo", conn);
     conn.Close();
     conn.Open();
     Assert.That(ExecuteScalar("SHOW search_path", conn), Is.EqualTo("public"));
     conn.Close();
 }

        public void MinPoolSize()
        {
            var conn = new NpgsqlConnection(ConnectionString + ";MinPoolSize=30;MaxPoolSize=30");
            conn.Open();
            conn.Close();

            conn = new NpgsqlConnection(ConnectionString + ";MaxPoolSize=30;MinPoolSize=30");
            conn.Open();
            conn.Close();
        }

 public void MinPoolSizeEqualsMaxPoolSize()
 {
     using (var conn = new NpgsqlConnection(new NpgsqlConnectionStringBuilder(ConnectionString) {
         MinPoolSize = 30,
         MaxPoolSize = 30
     }))
     {
         conn.Open();
     }
 }

 public static void MinimumPgVersion(NpgsqlConnection conn, string minVersion, string ignoreText=null)
 {
     var min = new Version(minVersion);
     if (conn.PostgreSqlVersion < min)
     {
         var msg = $"Postgresql backend version {conn.PostgreSqlVersion} is less than the required {min}";
         if (ignoreText != null)
             msg += ": " + ignoreText;
         Assert.Ignore(msg);
     }
 }

 public void MinPoolSizeLargeThanMaxPoolSize()
 {
     using (var conn = new NpgsqlConnection(new NpgsqlConnectionStringBuilder(ConnectionString)
     {
         MinPoolSize = 2,
         MaxPoolSize = 1
     }))
     {
         Assert.That(() => conn.Open(), Throws.Exception.TypeOf<ArgumentException>());
     }
 }

 /// <summary>
 /// 执行查询，返回DataSet
 /// </summary>
 public DataSet ExecuteQuery(string connectionString, CommandType cmdType, string cmdText,
     params DbParameter[] cmdParms)
 {
     using (NpgsqlConnection conn = new NpgsqlConnection(connectionString))
     {
         using (NpgsqlCommand cmd = new NpgsqlCommand())
         {
             PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
             using (NpgsqlDataAdapter da = new NpgsqlDataAdapter(cmd))
             {
                 DataSet ds = new DataSet();
                 da.Fill(ds, "ds");
                 cmd.Parameters.Clear();
                 return ds;
             }
         }
     }
 }

        public void RejectSelfSignedCertificate(bool useSslStream)
        {
            var csb = new NpgsqlConnectionStringBuilder(ConnectionString)
            {
                SslMode = SslMode.Require,
                UseSslStream = useSslStream
            };

            using (var conn = new NpgsqlConnection(csb))
            {
                // The following is necessary since a pooled connector may exist from a previous
                // SSL test
                NpgsqlConnection.ClearPool(conn);

                // TODO: Specific exception, align with SslStream
                Assert.That(() => conn.Open(), Throws.Exception);
            }
        }

    /// <summary>
    /// 执行查询，返回DataReader
    /// </summary>
    public DbDataReader ExecuteReader(string connectionString, CommandType cmdType, string cmdText,
        params DbParameter[] cmdParms)
    {
        NpgsqlCommand cmd = new NpgsqlCommand();
        NpgsqlConnection conn = new NpgsqlConnection(connectionString);

        try
        {
            PrepareCommand(cmd, conn, null, cmdType, cmdText, cmdParms);
            NpgsqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            cmd.Parameters.Clear();
            return rdr;
        }
        catch
        {
            conn.Close();
            throw;
        }
    }

 public void ResetOnClose()
 {
     var conn = new NpgsqlConnection(ConnectionString + ";SearchPath=public");
     conn.Open();
     ExecuteNonQuery("DROP SCHEMA IF EXISTS foo");
     ExecuteNonQuery("CREATE SCHEMA foo");
     try
     {
         ExecuteNonQuery("SET search_path=foo", conn);
         conn.Close();
         conn.Open();
         Assert.That(ExecuteScalar("SHOW search_path", conn), Is.EqualTo("public"));
         conn.Close();
     }
     finally
     {
         ExecuteNonQuery("DROP SCHEMA foo");
     }
 }

        public void MinPoolSize()
        {
            var connString = new NpgsqlConnectionStringBuilder(ConnectionString) { MinPoolSize = 2 };
            using (var conn = new NpgsqlConnection(connString))
            {
                connString = conn.Settings; // Shouldn't be necessary
                conn.Open();
                conn.Close();
            }

            var pool = PoolManager.Pools[connString];
            Assert.That(pool.Idle, Has.Count.EqualTo(2));

            // Now open 2 connections and make sure they're good
            using (var conn1 = OpenConnection(connString))
            using (var conn2 = OpenConnection(connString))
            {
                Assert.That(pool.Idle, Has.Count.Zero);
                Assert.That(conn1.ExecuteScalar("SELECT 1"), Is.EqualTo(1));
                Assert.That(conn2.ExecuteScalar("SELECT 1"), Is.EqualTo(1));
            }
        }

		//
		// MembershipProvider.GetAllUsers
		//

		public override MembershipUserCollection GetAllUsers(int pageIndex, int pageSize, out int totalRecords)
		{
			NpgsqlConnection conn = new NpgsqlConnection(connectionString);
			NpgsqlCommand cmd =
				new NpgsqlCommand(string.Format("SELECT Count(*) FROM {0} WHERE application_name = @application_name", tableName), conn);
			cmd.Parameters.Add("@application_name", NpgsqlDbType.Text, 255).Value = ApplicationName;
			MembershipUserCollection users = new MembershipUserCollection();

			NpgsqlDataReader reader = null;
			totalRecords = 0;
			try
			{
				conn.Open();
				totalRecords = Convert.ToInt32(cmd.ExecuteScalar());
				if (totalRecords <= 0)
				{
					return users;
				}

				cmd.CommandText = string.Format("SELECT UserId, user_name, Email, password_question, Comment, is_approved, is_locked_out, creation_date, last_login_date, last_activity_date, last_password_changed_date, last_locked_out_date  FROM {0}  WHERE application_name = @application_name  ORDER BY user_name Asc", tableName);

				using (reader = cmd.ExecuteReader())
				{
					int counter = 0;
					int startIndex = pageSize*pageIndex;
					int endIndex = startIndex + pageSize - 1;

					while (reader.Read())
					{
						if (counter >= startIndex)
						{
							MembershipUser u = GetUserFromReader(reader);
							users.Add(u);
						}

						if (counter >= endIndex)
						{
							cmd.Cancel();
						}

						counter++;
					}
					reader.Close();
				}
			}
			catch (NpgsqlException e)
			{
				if (WriteExceptionsToEventLog)
				{
					WriteToEventLog(e, "GetAllUsers");

                    // use fully qualified name so as not to conflict with System.Data.ProviderException
                    // in System.Data.Entity assembly
					throw new System.Configuration.Provider.ProviderException(exceptionMessage);
				}
				else
				{
					throw;// e;
				}
			}
			finally
			{
				if (reader != null)
				{
					reader.Close();
				}
				cmd.Dispose();
				conn.Close();
			}

			return users;
		}

 public void MinPoolSizeLargeThanPoolSizeLimit()
 {
     var conn = new NpgsqlConnection(ConnectionString + ";MinPoolSize=1025;");
     conn.Open();
     conn.Close();
 }