C# NpgsqlCommand.ExecuteBlind方法代码示例

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

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

		//
		// RoleProvider.RemoveUsersFromRoles
		//

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

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


			NpgsqlConnection conn = new NpgsqlConnection(connectionString);
			NpgsqlCommand cmd =
				new NpgsqlCommand(
					"DELETE FROM " + usersInRolesTable + "" +
					" WHERE user_name = @user_name AND role_name = @role_name AND application_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, "RemoveUsersFromRoles");
				}
				else
				{
					throw e;
				}
			}
			finally
			{
				cmd.Dispose();
				conn.Close();
			}
		}

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

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

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

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

		/// <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>
		/// Deletes Profiles
		/// </summary>
		/// <param name="usernames"></param>
		/// <returns></returns>
		public override int DeleteProfiles(string[] usernames)
		{
			if (usernames == null || usernames.Length < 1)
			{
				return 0;
			}

			int numProfilesDeleted = 0;
			bool beginTranCalled = false;
			NpgsqlConnection conn = null;
			try
			{
				conn = new NpgsqlConnection(_NpgsqlConnectionString);
				conn.Open();

				NpgsqlCommand cmd;
				int numUsersRemaing = usernames.Length;
				while (numUsersRemaing > 0)
				{
					cmd = new NpgsqlCommand(String.Empty, conn);
					cmd.Parameters.Add("@UserName0", usernames[usernames.Length - numUsersRemaing]);
					StringBuilder allUsers = new StringBuilder("@UserName0");
					numUsersRemaing--;

					int userIndex = 1;
					for (int iter = usernames.Length - numUsersRemaing; iter < usernames.Length; iter++)
					{
						// REVIEW: Should we check length of command string instead of parameter lengths?
						if (allUsers.Length + usernames[iter].Length + 3 >= 4000)
						{
							break;
						}
						string userNameParam = "@UserName" + userIndex;
						allUsers.Append(",");
						allUsers.Append(userNameParam);
						cmd.Parameters.Add(userNameParam, usernames[iter]);
						numUsersRemaing--;
						++userIndex;
					}

					// We don't need to start a transaction if we can finish this in one sql command
					if (!beginTranCalled && numUsersRemaing > 0)
					{
						NpgsqlCommand beginCmd = new NpgsqlCommand("BEGIN TRANSACTION", conn);
						beginCmd.ExecuteBlind();
						beginTranCalled = true;
					}


					cmd.CommandText =
						string.Format(
							"DELETE FROM {0} WHERE UserId IN ( SELECT u.UserId FROM vw_aspnet_Users u WHERE u.ApplicationId = '{1}' AND u.UserName IN ({2}))",
							_table, AppId, allUsers.ToString());
					cmd.CommandTimeout = CommandTimeout;
					numProfilesDeleted += cmd.ExecuteNonQuery();
				}

				if (beginTranCalled)
				{
					cmd = new NpgsqlCommand("COMMIT TRANSACTION", conn);
					cmd.ExecuteBlind();
					beginTranCalled = false;
				}
			}
			catch
			{
				if (beginTranCalled)
				{
					NpgsqlCommand cmd = new NpgsqlCommand("ROLLBACK TRANSACTION", conn);
					cmd.ExecuteBlind();
					beginTranCalled = false;
				}
				throw;
			}
			finally
			{
				if (conn != null)
				{
					conn.Close();
					conn = null;
				}
			}
			return numProfilesDeleted;
		}

//.........这里部分代码省略.........
				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;
				bool IfExists = false;
				try
				{
					using (readerSelect = cmd.ExecuteReader(CommandBehavior.SingleRow))
					{
						if (readerSelect.HasRows)
						{
							// IF EXISTS (SELECT * FROM aspnet_users WHERE UserId = '') 
							IfExists = true;
						}
						else
						{
							IfExists = false;
						}
						readerSelect.Close();

		/// <summary>
		/// Gets Profiles for Query
		/// </summary>
		/// <param name="insertArgs"></param>
		/// <param name="pageIndex"></param>
		/// <param name="pageSize"></param>
		/// <param name="insertQuery"></param>
		/// <param name="totalRecords"></param>
		/// <returns></returns>
		private ProfileInfoCollection GetProfilesForQuery(NpgsqlParameter[] insertArgs, int pageIndex, int pageSize,
		                                                  StringBuilder insertQuery, out int totalRecords)
		{
			if (pageIndex < 0)
			{
				throw new ArgumentException("pageIndex");
			}
			if (pageSize < 1)
			{
				throw new ArgumentException("pageSize");
			}

			long lowerBound = (long) pageIndex*pageSize;
			long upperBound = lowerBound + pageSize - 1;
			if (upperBound > Int32.MaxValue)
			{
				throw new ArgumentException("pageIndex and pageSize");
			}

			NpgsqlConnection conn = null;
			NpgsqlDataReader reader = null;
			NpgsqlCommand cmd = null;
			try
			{
				conn = new NpgsqlConnection(_NpgsqlConnectionString);
				conn.Open();

				StringBuilder cmdStr = new StringBuilder(200);
				// Create a temp table TO store the select results
				cmd =
					new NpgsqlCommand(
						"CREATE TABLE #PageIndexForProfileUsers(IndexId int IDENTITY (0, 1) NOT NULL, UserId varchar(36))", conn);
				cmd.CommandTimeout = CommandTimeout;
				cmd.ExecuteBlind();
				cmd.Dispose();

				insertQuery.Append(" ORDER BY UserName");
				cmd = new NpgsqlCommand(insertQuery.ToString(), conn);
				cmd.CommandTimeout = CommandTimeout;
				if (insertArgs != null)
				{
					foreach (NpgsqlParameter arg in insertArgs)
					{
						cmd.Parameters.Add(arg);
					}
				}

				cmd.ExecuteBlind();
				cmd.Dispose();

				cmdStr = new StringBuilder(200);
				cmdStr.Append("SELECT u.UserName, u.IsAnonymous, u.LastActivityDate, p.LastUpdatedDate FROM vw_aspnet_Users u, ").
					Append(_table);
				cmdStr.Append(" p, #PageIndexForProfileUsers i WHERE u.UserId = p.UserId AND p.UserId = i.UserId AND i.IndexId >= ");
				cmdStr.Append(lowerBound).Append(" AND i.IndexId <= ").Append(upperBound);
				cmd = new NpgsqlCommand(cmdStr.ToString(), conn);
				cmd.CommandTimeout = CommandTimeout;

				reader = cmd.ExecuteReader(CommandBehavior.SequentialAccess);
				ProfileInfoCollection profiles = new ProfileInfoCollection();
				while (reader.Read())
				{
					string username;
					DateTime dtLastActivity, dtLastUpdated = DateTime.UtcNow;
					bool isAnon;

					username = reader.GetString(0);
					isAnon = reader.GetBoolean(1);
					dtLastActivity = DateTime.SpecifyKind(reader.GetDateTime(2), DateTimeKind.Utc);
					dtLastUpdated = DateTime.SpecifyKind(reader.GetDateTime(3), DateTimeKind.Utc);
					profiles.Add(new ProfileInfo(username, isAnon, dtLastActivity, dtLastUpdated, 0));
				}
				totalRecords = profiles.Count;

				if (reader != null)
				{
					reader.Close();
					reader = null;
				}

				cmd.Dispose();

				cmd = new NpgsqlCommand("DROP TABLE #PageIndexForProfileUsers", conn);
				cmd.ExecuteBlind();

				return profiles;
			}
			finally
			{
				if (reader != null)
				{
//.........这里部分代码省略.........