C# NpgsqlCommand.Dispose方法代码示例

		//
		// MembershipProvider.UpdateUser
		//

		public override void UpdateUser(MembershipUser user)
		{
			NpgsqlConnection conn = new NpgsqlConnection(connectionString);
			NpgsqlCommand cmd =
				new NpgsqlCommand(
					"UPDATE " + tableName + "" + " SET Email = @Email, Comment = @Comment," + " is_approved = @is_approved" +
					" WHERE user_name = @user_name AND application_name = @application_name", conn);

			cmd.Parameters.Add("@Email", NpgsqlDbType.Text, 128).Value = user.Email;
			cmd.Parameters.Add("@Comment", NpgsqlDbType.Text, 255).Value = user.Comment;
			cmd.Parameters.Add("@is_approved", NpgsqlDbType.Boolean).Value = user.IsApproved;
			cmd.Parameters.Add("@user_name", NpgsqlDbType.Text, 255).Value = user.UserName;
			cmd.Parameters.Add("@application_name", NpgsqlDbType.Text, 255).Value = pApplicationName;


			try
			{
				conn.Open();

				cmd.ExecuteBlind();
			}
			catch (NpgsqlException e)
			{
				if (WriteExceptionsToEventLog)
				{
					WriteToEventLog(e, "UpdateUser");

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

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

//.........这里部分代码省略.........
					}

					columnData.Add(new ProfileColumnData(columnName, pp, value, datatype));
				}

				// Figure out userid, if we don't find a userid, go ahead and create a user in the aspnetUsers table
				Guid userId = Guid.Empty;
				string tmpUserId = null;
				cmd =
					new NpgsqlCommand(
						string.Format(
							"SELECT u.UserId FROM vw_aspnet_Users u WHERE u.ApplicationId = '{0}' AND u.LoweredUserName = LOWER(@Username)",
							AppId), conn);
				cmd.CommandType = CommandType.Text;
				cmd.Parameters.Add("@Username", NpgsqlDbType.Text, 255).Value = username;
				try
				{
					reader = cmd.ExecuteReader();
					if (reader.Read())
					{
						tmpUserId = reader.GetString(0);
						if (!string.IsNullOrEmpty(tmpUserId)) // != null && tmpUserId != "")
						{
							userId = new Guid(tmpUserId);
						}
						else
						{
							userId = Guid.NewGuid();
						}
					}
					else
					{
						reader.Close();
						cmd.Dispose();
						reader = null;

						cmd =
							new NpgsqlCommand(
								" INSERT INTO aspnet_Users (ApplicationId, UserId, UserName, LoweredUserName, IsAnonymous, LastActivityDate) " +
								" VALUES (@ApplicationId, @UserId, @UserName, LOWER(@UserName), @IsUserAnonymous, @LastActivityDate) ", conn);
						userId = Guid.NewGuid();
						cmd.CommandType = CommandType.Text;
						cmd.Parameters.Add("@UserId", NpgsqlDbType.Text, 36).Value = userId.ToString();
						cmd.Parameters.Add("@ApplicationId", NpgsqlDbType.Text, 36).Value = AppId.ToString();
						cmd.Parameters.Add("@UserName", NpgsqlDbType.Text, 255).Value = username;
						cmd.Parameters.Add("@IsUserAnonymous", NpgsqlDbType.Boolean).Value = !userIsAuthenticated;
						cmd.Parameters.Add("@LastActivityDate", NpgsqlDbType.Timestamp).Value = DateTime.UtcNow;

						cmd.ExecuteBlind();
					}
				}
				finally
				{
					if (reader != null)
					{
						reader.Close();
						reader = null;
					}
					cmd.Dispose();
				}

				// Figure out if the row already exists in the table and use appropriate SELECT/UPDATE
				cmd = new NpgsqlCommand("SELECT * FROM " + _table + " WHERE UserId = @UserId", conn);
				cmd.CommandType = CommandType.Text;
				cmd.Parameters.Add("@UserId", NpgsqlDbType.Text, 36).Value = userId.ToString();
				NpgsqlDataReader readerSelect = null;

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

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

		//
		// System.Web.Security.MembershipProvider methods.
		//

		//
		// MembershipProvider.ChangePassword
		//

		public override bool ChangePassword(string username, string oldPwd, string newPwd)
		{
			if (!ValidateUser(username, oldPwd))
			{
				return false;
			}


			ValidatePasswordEventArgs args = new ValidatePasswordEventArgs(username, newPwd, true);

			OnValidatingPassword(args);

			if (args.Cancel)
			{
				if (args.FailureInformation != null)
				{
					throw args.FailureInformation;
				}
				else
				{
					throw new MembershipPasswordException("Change password canceled due to new password validation failure.");
				}
			}


			NpgsqlConnection conn = new NpgsqlConnection(connectionString);
			NpgsqlCommand cmd =
				new NpgsqlCommand(
					string.Format("UPDATE {0} SET Password = @Password, last_password_changed_date = @last_password_changed_date  WHERE user_name = @user_name AND application_name = @application_name", tableName), conn);

			cmd.Parameters.Add("@Password", NpgsqlDbType.Text, 255).Value = EncodePassword(newPwd);
			cmd.Parameters.Add("@last_password_changed_date", NpgsqlDbType.Timestamp).Value = DateTime.Now;
			cmd.Parameters.Add("@user_name", NpgsqlDbType.Text, 255).Value = username;
			cmd.Parameters.Add("@application_name", NpgsqlDbType.Text, 255).Value = pApplicationName;


			int rowsAffected = 0;

			try
			{
				conn.Open();

				rowsAffected = cmd.ExecuteNonQuery();
			}
			catch (NpgsqlException e)
			{
				if (WriteExceptionsToEventLog)
				{
					WriteToEventLog(e, "ChangePassword");

                    // 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 (rowsAffected > 0);		}

		//
		// MembershipProvider.CreateUser
		//

		public override MembershipUser CreateUser(string username, string password, string email, string passwordQuestion,
		                                          string passwordAnswer, bool isApproved, object providerUserKey,
		                                          out MembershipCreateStatus status)
		{
			ValidatePasswordEventArgs args = new ValidatePasswordEventArgs(username, password, true);

			OnValidatingPassword(args);

			if (args.Cancel)
			{
				status = MembershipCreateStatus.InvalidPassword;
				return null;
			}


			if (RequiresUniqueEmail && !string.IsNullOrEmpty(GetUserNameByEmail(email)))
			{
				status = MembershipCreateStatus.DuplicateEmail;
				return null;
			}

			MembershipUser u = GetUser(username, false);

			if (u == null)
			{
				DateTime createDate = DateTime.Now;

				if (providerUserKey == null)
				{
					providerUserKey = Guid.NewGuid();
				}
				else
				{
					if (!(providerUserKey is Guid))
					{
						status = MembershipCreateStatus.InvalidProviderUserKey;
						return null;
					}
				}

				NpgsqlConnection conn = new NpgsqlConnection(connectionString);
				NpgsqlCommand cmd =
					new NpgsqlCommand(
						string.Format("INSERT INTO {0} (UserId, user_name, Password, Email, password_question,  password_answer, is_approved, Comment, creation_date, last_password_changed_date, last_activity_date, application_name, is_locked_out, last_locked_out_date, failed_password_attempt_count, failed_password_attempt_window_start,  failed_password_answer_attempt_count, failed_password_answer_attempt_window_start) Values(@UserId, @user_name, @Password, @Email, @password_question,  @password_answer, @is_approved, @Comment, @creation_date, @last_password_changed_date,  @last_activity_date, @application_name, @is_locked_out, @last_locked_out_date,  @failed_password_attempt_count, @failed_password_attempt_window_start,  @failed_password_answer_attempt_count, @failed_password_answer_attempt_window_start)", tableName), conn);

				cmd.Parameters.Add("@UserId", NpgsqlDbType.Text).Value = providerUserKey.ToString();
				cmd.Parameters.Add("@user_name", NpgsqlDbType.Text, 255).Value = username;
				cmd.Parameters.Add("@Password", NpgsqlDbType.Text, 255).Value = EncodePassword(password);
				cmd.Parameters.Add("@Email", NpgsqlDbType.Text, 128).Value = email;
				cmd.Parameters.Add("@password_question", NpgsqlDbType.Text, 255).Value = passwordQuestion;
				cmd.Parameters.Add("@password_answer", NpgsqlDbType.Text, 255).Value = passwordAnswer == null
				                                                                       	? null
				                                                                       	: EncodePassword(passwordAnswer);
				cmd.Parameters.Add("@is_approved", NpgsqlDbType.Boolean).Value = isApproved;
				cmd.Parameters.Add("@Comment", NpgsqlDbType.Text, 255).Value = "";
				cmd.Parameters.Add("@creation_date", NpgsqlDbType.Timestamp).Value = createDate;
				cmd.Parameters.Add("@last_password_changed_date", NpgsqlDbType.Timestamp).Value = createDate;
				cmd.Parameters.Add("@last_activity_date", NpgsqlDbType.Timestamp).Value = createDate;
				cmd.Parameters.Add("@application_name", NpgsqlDbType.Text, 255).Value = pApplicationName;
				cmd.Parameters.Add("@is_locked_out", NpgsqlDbType.Boolean).Value = false; //false
				cmd.Parameters.Add("@last_locked_out_date", NpgsqlDbType.Timestamp).Value = createDate;
				cmd.Parameters.Add("@failed_password_attempt_count", NpgsqlDbType.Integer).Value = 0;
				cmd.Parameters.Add("@failed_password_attempt_window_start", NpgsqlDbType.Timestamp).Value = createDate;
				cmd.Parameters.Add("@failed_password_answer_attempt_count", NpgsqlDbType.Integer).Value = 0;
				cmd.Parameters.Add("@failed_password_answer_attempt_window_start", NpgsqlDbType.Timestamp).Value = createDate;

				try
				{
					conn.Open();

					int recAdded = cmd.ExecuteNonQuery();

					if (recAdded > 0)
					{
						status = MembershipCreateStatus.Success;
					}
					else
					{
						status = MembershipCreateStatus.UserRejected;
					}
				}
				catch (NpgsqlException e)
				{
					if (WriteExceptionsToEventLog)
					{
						WriteToEventLog(e, "CreateUser");
					}

					status = MembershipCreateStatus.ProviderError;
				}
				finally
				{
					cmd.Dispose();
					conn.Close();
				}

//.........这里部分代码省略.........

		//
		// RoleProvider.DeleteRole
		//

		public override bool DeleteRole(string rolename, bool throwOnPopulatedRole)
		{
			if (!RoleExists(rolename))
			{
				throw new ProviderException("Role does not exist.");
			}

			if (throwOnPopulatedRole && GetUsersInRole(rolename).Length > 0)
			{
				throw new ProviderException("Cannot delete a populated role.");
			}

			NpgsqlConnection conn = new NpgsqlConnection(connectionString);
			NpgsqlCommand cmd =
				new NpgsqlCommand(
					"DELETE FROM " + rolesTable + "" + " WHERE role_name = @role_name AND application_name = @application_name", conn);

			cmd.Parameters.Add("@role_name", NpgsqlDbType.Text, 255).Value = rolename;
			cmd.Parameters.Add("@application_name", NpgsqlDbType.Text, 255).Value = ApplicationName;


			NpgsqlCommand cmd2 =
				new NpgsqlCommand(
					"DELETE FROM " + usersInRolesTable + "" + " WHERE role_name = @role_name AND application_name = @application_name",
					conn);

			cmd2.Parameters.Add("@role_name", NpgsqlDbType.Text, 255).Value = rolename;
			cmd2.Parameters.Add("@application_name", NpgsqlDbType.Text, 255).Value = ApplicationName;

			NpgsqlTransaction tran = null;

			try
			{
				conn.Open();
				tran = conn.BeginTransaction();
				cmd.Transaction = tran;
				cmd2.Transaction = tran;

				cmd2.ExecuteBlind();
				cmd.ExecuteBlind();

				tran.Commit();
			}
			catch (NpgsqlException e)
			{
				try
				{
					if (tran != null)
					{
						tran.Rollback();
					}
				}
				catch
				{
				}


				if (WriteExceptionsToEventLog)
				{
					WriteToEventLog(e, "DeleteRole");

					return false;
				}
				else
				{
					throw e;
				}
			}
			finally
			{
				cmd.Dispose();
				conn.Close();
			}

			return true;
		}

		//
		// RoleProvider.GetAllRoles
		//

		public override string[] GetAllRoles()
		{
			string tmpRoleNames = "";

			NpgsqlConnection conn = new NpgsqlConnection(connectionString);
			NpgsqlCommand cmd =
				new NpgsqlCommand("SELECT role_name FROM " + rolesTable + "" + " WHERE application_name = @application_name", conn);

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

			NpgsqlDataReader reader = null;

			try
			{
				conn.Open();

				using (reader = cmd.ExecuteReader())
				{
					while (reader.Read())
					{
						tmpRoleNames += reader.GetString(0) + ",";
					}
					reader.Close();
				}
			}
			catch (NpgsqlException e)
			{
				if (WriteExceptionsToEventLog)
				{
					WriteToEventLog(e, "GetAllRoles");
				}
				else
				{
					throw e;
				}
			}
			finally
			{
				if (reader != null)
				{
					reader.Close();
				}
				cmd.Dispose();
				conn.Close();
			}

			if (tmpRoleNames.Length > 0)
			{
				// Remove trailing comma.
				tmpRoleNames = tmpRoleNames.Substring(0, tmpRoleNames.Length - 1);
				return tmpRoleNames.Split(',');
			}

			return new string[0];
		}

		//
		// System.Web.Security.RoleProvider methods.
		//

		//
		// RoleProvider.AddUsersToRoles
		//

		public override void AddUsersToRoles(string[] usernames, string[] rolenames)
		{
			foreach (string rolename in rolenames)
			{
				if (!RoleExists(rolename))
				{
					throw new ProviderException("Role name not found.");
				}
			}

			foreach (string username in usernames)
			{
				if (username.IndexOf(',') > 0)
				{
					throw new ArgumentException("User names cannot contain commas.");
				}

				foreach (string rolename in rolenames)
				{
					if (IsUserInRole(username, rolename))
					{
						throw new ProviderException("User is already in role.");
					}
				}
			}


			NpgsqlConnection conn = new NpgsqlConnection(connectionString);
			NpgsqlCommand cmd =
				new NpgsqlCommand(
					"INSERT INTO " + usersInRolesTable + "" + " (user_name, role_name, application_name) " +
					" Values(@user_name, @role_name, @application_name)", conn);

			NpgsqlParameter userParm = cmd.Parameters.Add("@user_name", NpgsqlDbType.Text, 255);
			NpgsqlParameter roleParm = cmd.Parameters.Add("@role_name", NpgsqlDbType.Text, 255);
			cmd.Parameters.Add("@application_name", NpgsqlDbType.Text, 255).Value = ApplicationName;

			NpgsqlTransaction tran = null;

			try
			{
				conn.Open();
				tran = conn.BeginTransaction();
				cmd.Transaction = tran;

				foreach (string username in usernames)
				{
					foreach (string rolename in rolenames)
					{
						userParm.Value = username;
						roleParm.Value = rolename;
						cmd.ExecuteBlind();
					}
				}

				tran.Commit();
			}
			catch (NpgsqlException e)
			{
				try
				{
					if (tran != null)
					{
						tran.Rollback();
					}
				}
				catch
				{
				}


				if (WriteExceptionsToEventLog)
				{
					WriteToEventLog(e, "AddUsersToRoles");
				}
				else
				{
					throw e;
				}
			}
			finally
			{
				cmd.Dispose();
				conn.Close();
			}
		}

		//
		// RoleProvider.CreateRole
		//

		public override void CreateRole(string rolename)
		{
			if (rolename.IndexOf(',') > 0)
			{
				throw new ArgumentException("Role names cannot contain commas.");
			}

			if (RoleExists(rolename))
			{
				throw new ProviderException("Role name already exists.");
			}

			NpgsqlConnection conn = new NpgsqlConnection(connectionString);
			NpgsqlCommand cmd =
				new NpgsqlCommand(
					"INSERT INTO " + rolesTable + "" + " (role_name, application_name) " + " Values(@role_name, @application_name)",
					conn);

			cmd.Parameters.Add("@role_name", NpgsqlDbType.Text, 255).Value = rolename;
			cmd.Parameters.Add("@application_name", NpgsqlDbType.Text, 255).Value = ApplicationName;

			try
			{
				conn.Open();

				cmd.ExecuteBlind();
			}
			catch (NpgsqlException e)
			{
				if (WriteExceptionsToEventLog)
				{
					WriteToEventLog(e, "CreateRole");
				}
				else
				{
					throw e;
				}
			}
			finally
			{
				cmd.Dispose();
				conn.Close();
			}
		}

		/// <summary>
		/// Updates last activity date
		/// </summary>
		/// <param name="conn"></param>
		/// <param name="userId"></param>
		private static void UpdateLastActivityDate(NpgsqlConnection conn, Guid userId)
		{
			NpgsqlCommand cmd =
				new NpgsqlCommand("UPDATE aspnet_Users SET LastActivityDate = @LastUpdatedDate WHERE UserId = '" + userId + "'",
				                  conn);
			cmd.CommandType = CommandType.Text;
			cmd.Parameters.Add("@LastUpdatedDate", NpgsqlDbType.Timestamp).Value = DateTime.UtcNow;
			try
			{
				cmd.ExecuteBlind();
			}
			finally
			{
				cmd.Dispose();
			}
		}

		/// <summary>
		/// Gets number of inactive Profiles
		/// </summary>
		/// <param name="authenticationOption"></param>
		/// <param name="userInactiveSinceDate"></param>
		/// <returns></returns>
		public override int GetNumberOfInactiveProfiles(ProfileAuthenticationOption authenticationOption,
		                                                DateTime userInactiveSinceDate)
		{
			NpgsqlConnection conn = null;
			NpgsqlCommand cmd = null;
			try
			{
				conn = new NpgsqlConnection(_NpgsqlConnectionString);
				conn.Open();

				cmd = new NpgsqlCommand(GenerateQuery(false, authenticationOption), conn);
				cmd.CommandTimeout = CommandTimeout;
				cmd.Parameters.Add("@InactiveSinceDate", NpgsqlDbType.Timestamp).Value = userInactiveSinceDate.ToUniversalTime();

				object o = cmd.ExecuteScalar();
				if (o == null || !(o is int))
				{
					return 0;
				}
				return (int) o;
			}
			finally
			{
				if (cmd != null)
				{
					cmd.Dispose();
				}
				if (conn != null)
				{
					conn.Close();
					conn = null;
				}
			}
		}

		/// <summary>
		/// Deletes inactive Profiles
		/// </summary>
		/// <param name="authenticationOption"></param>
		/// <param name="userInactiveSinceDate"></param>
		/// <returns></returns>
		public override int DeleteInactiveProfiles(ProfileAuthenticationOption authenticationOption,
		                                           DateTime userInactiveSinceDate)
		{
			NpgsqlConnection conn = null;
			NpgsqlCommand cmd = null;
			try
			{
				conn = new NpgsqlConnection(_NpgsqlConnectionString);
				conn.Open();

				cmd = new NpgsqlCommand(GenerateQuery(true, authenticationOption), conn);
				cmd.CommandTimeout = CommandTimeout;
				cmd.Parameters.Add("@InactiveSinceDate", NpgsqlDbType.Timestamp).Value = userInactiveSinceDate.ToUniversalTime();

				return cmd.ExecuteNonQuery();
			}
			finally
			{
				if (cmd != null)
				{
					cmd.Dispose();
				}
				if (conn != null)
				{
					conn.Close();
					conn = null;
				}
			}
		}

		//
		// MembershipProvider.ValidateUser
		//

		public override bool ValidateUser(string username, string password)
		{
			bool isValid = false;

			NpgsqlConnection conn = new NpgsqlConnection(connectionString);
			NpgsqlCommand cmd =
				new NpgsqlCommand(
					"SELECT Password, is_approved FROM " + tableName + "" +
					" WHERE user_name = @user_name AND application_name = @application_name AND is_locked_out = false", conn);

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

			NpgsqlDataReader reader = null;
			bool isApproved = false;
			string pwd = "";

			try
			{
				conn.Open();

				using (reader = cmd.ExecuteReader(CommandBehavior.SingleRow))
				{
					if (reader.HasRows)
					{
						reader.Read();
						pwd = reader.GetString(0);
						isApproved = reader.GetBoolean(1);
					}
					else
					{
						return false;
					}
					reader.Close();
				}

				if (CheckPassword(password, pwd))
				{
					if (isApproved)
					{
						isValid = true;

						NpgsqlCommand updateCmd =
							new NpgsqlCommand(
								"UPDATE " + tableName + " SET last_login_date = @last_login_date, last_activity_date = @last_activity_date" +
								" WHERE user_name = @user_name AND application_name = @application_name", conn);

						updateCmd.Parameters.Add("@last_login_date", NpgsqlDbType.Timestamp).Value = DateTime.Now;
						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();
					}
				}
				else
				{
					cmd.Dispose();
					conn.Close();

					UpdateFailureCount(username, "password");
				}
			}
			catch (NpgsqlException e)
			{
				if (WriteExceptionsToEventLog)
				{
					WriteToEventLog(e, "ValidateUser");

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