C# NpgsqlConnection.Open方法代码示例

 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 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("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 MinPoolSizeEqualsMaxPoolSize()
 {
     using (var conn = new NpgsqlConnection(new NpgsqlConnectionStringBuilder(ConnectionString) {
         MinPoolSize = 30,
         MaxPoolSize = 30
     }))
     {
         conn.Open();
     }
 }

        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 MinPoolSizeLargeThanMaxPoolSize()
 {
     using (var conn = new NpgsqlConnection(new NpgsqlConnectionStringBuilder(ConnectionString)
     {
         MinPoolSize = 2,
         MaxPoolSize = 1
     }))
     {
         Assert.That(() => conn.Open(), Throws.Exception.TypeOf<ArgumentException>());
     }
 }

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

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

        public void IntegratedSecurityWithUsername()
        {
            var username = Environment.GetEnvironmentVariable("USERNAME") ??
                           Environment.GetEnvironmentVariable("USER");
            if (username == null)
                throw new Exception("Could find username");

            var csb = new NpgsqlConnectionStringBuilder(ConnectionString) {
                IntegratedSecurity = true,
                Username = username,
                Password = null,
            };
            using (var conn = new NpgsqlConnection(csb))
            {
                try
                {
                    conn.Open();
                }
                catch (Exception e)
                {
                    if (TestUtil.IsOnBuildServer)
                        throw;
                    Console.WriteLine(e);
                    Assert.Ignore("Integrated security (GSS/SSPI) doesn't seem to be set up");
                }
            }
        }

 public void IntegratedSecurityWithoutUsername()
 {
     var csb = new NpgsqlConnectionStringBuilder(ConnectionString)
     {
         IntegratedSecurity = true,
         Username = null,
         Password = null,
     };
     using (var conn = new NpgsqlConnection(csb))
     {
         try
         {
             conn.Open();
         }
         catch (Exception e)
         {
             if (TestUtil.IsOnBuildServer)
                 throw;
             Console.WriteLine(e);
             Assert.Ignore("Integrated security (GSS/SSPI) doesn't seem to be set up");
         }
     }
 }

		//
		// MembershipProvider.GetUserNameByEmail
		//

		public override string GetUserNameByEmail(string email)
		{
			NpgsqlConnection conn = new NpgsqlConnection(connectionString);
			NpgsqlCommand cmd =
				new NpgsqlCommand(
					"SELECT user_name" + " FROM " + tableName + " WHERE Email = @Email AND application_name = @application_name", conn);

			cmd.Parameters.Add("@Email", NpgsqlDbType.Text, 128).Value = email;
			cmd.Parameters.Add("@application_name", NpgsqlDbType.Text, 255).Value = pApplicationName;

			string username = "";

			try
			{
				conn.Open();

				username = (string) cmd.ExecuteScalar();
			}
			catch (NpgsqlException e)
			{
				if (WriteExceptionsToEventLog)
				{
					WriteToEventLog(e, "GetUserNameByEmail");

                    // 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
			{
				cmd.Dispose();
				conn.Close();
			}

			if (username == null)
			{
				username = "";
			}

			return username;
		}

		/// <summary>
		/// 
		/// </summary>
		/// <param name="username"></param>
		/// <returns></returns>
		public MembershipUser GetCustomUser(string username)
		{
			NpgsqlMembershipProvider _provider = null;
			ProviderCollection _providers = null;

			// Get a reference to the <imageService> section
			MembershipSection section = (MembershipSection) WebConfigurationManager.GetSection("system.web/membership");

			// Load registered providers and point _provider
			// to the default provider
			_providers = new ProviderCollection();
			ProvidersHelper.InstantiateProviders(section.Providers, _providers, typeof (NpgsqlMembershipProvider));
			_provider = (NpgsqlMembershipProvider) _providers[section.DefaultProvider];

			NpgsqlConnection conn = new NpgsqlConnection(_provider.connectionString);
			NpgsqlCommand cmd =
				new NpgsqlCommand(
					"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 " + tableName +
					" WHERE user_name = @user_name AND application_name = @application_name", conn);

			cmd.Parameters.Add("@user_name", NpgsqlDbType.Text, 255).Value = username;
			cmd.Parameters.Add("@application_name", NpgsqlDbType.Text, 255).Value = _provider.ApplicationName;

			MembershipUser u = null;
			NpgsqlDataReader reader = null;

			try
			{
				conn.Open();

				using (reader = cmd.ExecuteReader())
				{
					if (reader.HasRows)
					{
						reader.Read();
						u = GetUserFromReader(reader);
						reader.Close();
					}
					reader.Close();
				}
			}
			catch (NpgsqlException e)
			{
				if (WriteExceptionsToEventLog)
				{
					WriteToEventLog(e, "GetUser(String, Boolean)");

                    // 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 u;
		}

		/// <summary>
		/// MembershipProvider.GetUser(string, bool)
		/// </summary>
		/// <param name="username"></param>
		/// <param name="userIsOnline"></param>
		/// <returns></returns>
		public override MembershipUser GetUser(string username, bool userIsOnline)
		{
			NpgsqlConnection conn = new NpgsqlConnection(connectionString);
			NpgsqlCommand cmd =
				new NpgsqlCommand(
					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 user_name = @user_name AND application_name = @application_name", tableName), conn);

			cmd.Parameters.Add("@user_name", NpgsqlDbType.Text, 255).Value = username;
			cmd.Parameters.Add("@application_name", NpgsqlDbType.Text, 255).Value = pApplicationName;

			MembershipUser u = null;
			NpgsqlDataReader reader = null;

			try
			{
				conn.Open();

				using (reader = cmd.ExecuteReader())
				{
					if (reader.HasRows)
					{
						reader.Read();
						u = GetUserFromReader(reader);
						reader.Close();

						if (userIsOnline)
						{
							NpgsqlCommand updateCmd =
								new NpgsqlCommand(
									string.Format("UPDATE {0} SET last_activity_date = @last_activity_date WHERE user_name = @user_name AND application_name = @application_name", tableName), conn);

							updateCmd.Parameters.Add("@last_activity_date", NpgsqlDbType.Timestamp).Value = DateTime.Now;
								// fixed by Alex .ToString("yyyy/MM/dd HH:mm:ss");
							updateCmd.Parameters.Add("@user_name", NpgsqlDbType.Text, 255).Value = username;
							updateCmd.Parameters.Add("@application_name", NpgsqlDbType.Text, 255).Value = pApplicationName;

							updateCmd.ExecuteBlind();
						}
					}
					reader.Close();
				}
			}
			catch (NpgsqlException e)
			{
				if (WriteExceptionsToEventLog)
				{
					WriteToEventLog(e, "GetUser(String, Boolean)");

                    // 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 u;
		}

		//
		// MembershipProvider.GetPassword
		//

		public override string GetPassword(string username, string answer)
		{
			if (!EnablePasswordRetrieval)
            {
                // use fully qualified name so as not to conflict with System.Data.ProviderException
                // in System.Data.Entity assembly
				throw new System.Configuration.Provider.ProviderException("Password Retrieval Not Enabled.");
			}

			if (PasswordFormat == MembershipPasswordFormat.Hashed)
            {
                // use fully qualified name so as not to conflict with System.Data.ProviderException
                // in System.Data.Entity assembly
				throw new System.Configuration.Provider.ProviderException("Cannot retrieve Hashed passwords.");
			}

			NpgsqlConnection conn = new NpgsqlConnection(connectionString);
			NpgsqlCommand cmd =
				new NpgsqlCommand(
					string.Format("SELECT Password, password_answer, is_locked_out FROM {0} WHERE user_name = @user_name AND application_name = @application_name", tableName), conn);

			cmd.Parameters.Add("@user_name", NpgsqlDbType.Text, 255).Value = username;
			cmd.Parameters.Add("@application_name", NpgsqlDbType.Text, 255).Value = pApplicationName;

			string password = "";
			string passwordAnswer = "";
			NpgsqlDataReader reader = null;

			try
			{
				conn.Open();

				using (reader = cmd.ExecuteReader(CommandBehavior.SingleRow))
				{
					if (reader.HasRows)
					{
						reader.Read();

						if (reader.GetBoolean(2))
						{
							throw new MembershipPasswordException("The supplied user is locked out.");
						}

						password = reader.GetString(0);
						passwordAnswer = reader.GetString(1);
					}
					else
					{
						throw new MembershipPasswordException("The supplied user name is not found.");
					}
					reader.Close();
				}
			}
			catch (NpgsqlException e)
			{
				if (WriteExceptionsToEventLog)
				{
                    WriteToEventLog(e, "GetPassword");
                    // 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();
			}


			if (RequiresQuestionAndAnswer && !CheckPassword(answer, passwordAnswer))
			{
				UpdateFailureCount(username, "passwordAnswer");

				throw new MembershipPasswordException("Incorrect password answer.");
			}


			if (PasswordFormat == MembershipPasswordFormat.Encrypted)
			{
				password = UnEncodePassword(password);
			}

			return password;
		}

		/// <summary>
		/// 
		/// </summary>
		/// <returns></returns>
		public string GetUserId()
		{
			NpgsqlMembershipProvider _provider = null;
			ProviderCollection _providers = null;

			// Get a reference to the <imageService> section
			MembershipSection section = (MembershipSection) WebConfigurationManager.GetSection("system.web/membership");

			// Load registered providers and point _provider
			// to the default provider
			_providers = new ProviderCollection();
			ProvidersHelper.InstantiateProviders(section.Providers, _providers, typeof (NpgsqlMembershipProvider));
			_provider = (NpgsqlMembershipProvider) _providers[section.DefaultProvider];

			HttpContext currentContext = HttpContext.Current;

			NpgsqlConnection conn = new NpgsqlConnection(_provider.connectionString);
			NpgsqlCommand cmd =
				new NpgsqlCommand(
					"SELECT UserId FROM " + tableName + " WHERE user_name = @user_name AND application_name = @application_name", conn);

			cmd.Parameters.Add("@user_name", NpgsqlDbType.Text, 255).Value = currentContext.User.Identity.Name;
			cmd.Parameters.Add("@application_name", NpgsqlDbType.Text, 255).Value = _provider.ApplicationName;

			string UserId = "";
			try
			{
				conn.Open();
				UserId = cmd.ExecuteScalar().ToString();
			}
			catch (NpgsqlException e)
			{
				if (WriteExceptionsToEventLog)
				{
					WriteToEventLog(e, "GetUserId()");

                    // 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
			{
				cmd.Dispose();
				conn.Close();
			}

			return UserId;
		}