sqlserverpagesstorageprovider.cs

YetAnotherForum.Net+ScrewTurnWiki中文完美汉化增强版

			cmd.Parameters.Add(new SqlParameter("Page", page.Name));
			cmd.Parameters.Add(new SqlParameter("Revision", revision));
			cmd.Parameters.Add(new SqlParameter("Title", content.Title));
			cmd.Parameters.Add(new SqlParameter("DateTime", content.LastModified));
			cmd.Parameters.Add(new SqlParameter("Username", content.User));
			cmd.Parameters.Add(new SqlParameter("Content", content.Content));
			cmd.Parameters.Add(new SqlParameter("Comment", content.Comment));
			return ExecuteNonQuery(cmd) == 1;
		}

		public PageInfo AddPage(string name, DateTime creationDateTime) {
			SqlCommand cmd = GetCommand();
			cmd.CommandText = "INSERT INTO [Page] VALUES (@Name, 'N', @DateTime); INSERT INTO [PageContent] VALUES (@Name, -1, '-', @DateTime, '-', '-', '-')";
			cmd.Parameters.Add(new SqlParameter("Name", name));
			cmd.Parameters.Add(new SqlParameter("DateTime", creationDateTime));
			if(ExecuteNonQuery(cmd) == 2) {
				return new PageInfo(name, this, PageStatus.Normal, creationDateTime);
			}
			else return null;
		}

		public PageInfo RenamePage(PageInfo page, string newName) {
			SqlCommand cmd = GetCommand();
			cmd.CommandText = "UPDATE [Page] SET [Name] = @NewPage WHERE [Name] = @Page";
			cmd.Parameters.Add(new SqlParameter("NewPage", newName));
			cmd.Parameters.Add(new SqlParameter("Page", page.Name));
			if(ExecuteNonQuery(cmd) == 1) {
				return new PageInfo(newName, this, page.Status, page.CreationDateTime);
			}
			else return null;
		}

		public PageInfo SetStatus(PageInfo page, PageStatus status) {
			SqlCommand cmd = GetCommand();
			cmd.CommandText = "UPDATE [Page] SET [Status] = @Status WHERE [Name] = @Page";
			string s = "N";
			switch(status) {
				case PageStatus.Normal:
					s = "N";
					break;
				case PageStatus.Locked:
					s = "L";
					break;
				case PageStatus.Public:
					s = "P";
					break;
			}
			cmd.Parameters.Add(new SqlParameter("Status", s));
			cmd.Parameters.Add(new SqlParameter("Page", page.Name));
			if(ExecuteNonQuery(cmd) == 1) {
				return new PageInfo(page.Name, this, status, page.CreationDateTime);
			}
			else return null;
		}

		public bool ModifyPage(PageInfo page, string title, string username, DateTime dateTime, string comment, string content, bool backup) {
			if(backup) {
				if(!Backup(page)) return false;
			}

			PageContent c = new PageContent(page, title, username, dateTime, comment, content);
			return SetBackupContent(c, -1);
		}

		public bool RollbackPage(PageInfo page, int revision) {
			// Delete newer backups, update current revision
			PageContent content = GetBackupContent(page, revision);
			if(content == null) return false;
			SqlCommand cmd = GetCommand();
			cmd.CommandText = "UPDATE [PageContent] SET [Title] = @Title, [DateTime] = @DateTime, [Username] = @Username, [Content] = @Content, [Comment] = @Comment WHERE [Page] = @Page AND [Revision] = -1; DELETE FROM [PageContent] WHERE [Page] = @Page AND NOT [Revision] = -1 AND [Revision] >= @Revision";
			cmd.Parameters.Add(new SqlParameter("Title", content.Title));
			cmd.Parameters.Add(new SqlParameter("DateTime", content.LastModified));
			cmd.Parameters.Add(new SqlParameter("Username", content.User));
			cmd.Parameters.Add(new SqlParameter("Content", content.Content));
			cmd.Parameters.Add(new SqlParameter("Comment", content.Comment));
			cmd.Parameters.Add(new SqlParameter("Page", page.Name));
			cmd.Parameters.Add(new SqlParameter("Revision", revision));
			return ExecuteNonQuery(cmd) > 1;
		}

		public bool DeleteBackups(PageInfo page, int revision) {
			// Delete older backups, re-number remaining backups
			if(revision == -1) {
				List<int> backups = GetBackups(page);
				if(backups.Count > 0) revision = backups[backups.Count - 1];
			}
			SqlCommand cmd = GetCommand();
			cmd.CommandText = "DELETE FROM [PageContent] WHERE [Page] = @Page AND NOT [Revision] = -1 AND [Revision] <= @Revision; UPDATE [PageContent] SET [Revision] = [Revision] - @Revision - 1 WHERE [Page] = @Page AND NOT [Revision] = -1";
			cmd.Parameters.Add(new SqlParameter("Page", page.Name));
			cmd.Parameters.Add(new SqlParameter("Revision", revision));
			return ExecuteNonQuery(cmd) > 0;
		}

		public bool RemovePage(PageInfo page) {
			SqlCommand cmd = GetCommand();
			cmd.CommandText = "DELETE FROM [Page] WHERE [Name] = @Page";
			cmd.Parameters.Add(new SqlParameter("Page", page.Name));
			return ExecuteNonQuery(cmd) == 1;
		}

		public bool Rebind(PageInfo page, string[] categories) {
			// Delete old bindings, add new bindings
			SqlCommand cmd = GetCommand();
			StringBuilder sb = new StringBuilder();
			sb.Append("DELETE FROM [CategoryBinding] WHERE [Page] = @Page; ");
			cmd.Parameters.Add(new SqlParameter("Page", page.Name));
			for(int i = 0; i < categories.Length; i++) {
				sb.Append("INSERT INTO [CategoryBinding] VALUES (@Cat");
				sb.Append(i.ToString());
				sb.Append(", @Page); ");
				cmd.Parameters.Add(new SqlParameter("Cat" + i.ToString(), categories[i]));
			}
			cmd.CommandText = sb.ToString();
			return ExecuteNonQuery(cmd) > 0;
		}

		public Message[] GetMessages(PageInfo page) {
			// This method is implemented in a very raw way
			return GetReplies(page, -1).ToArray();
		}

		private List<Message> GetReplies(PageInfo page, int parent) {
			SqlCommand cmd = GetCommand();
			cmd.CommandText = "SELECT * FROM [Message] WHERE [Page] = @Page AND [Parent] = @Parent";
			cmd.Parameters.Add(new SqlParameter("Page", page.Name));
			cmd.Parameters.Add(new SqlParameter("Parent", parent));
			SqlDataReader reader = ExecuteReader(cmd);
			List<Message> result = new List<Message>();
			while(reader != null && reader.Read()) {
				Message msg = new Message(reader.GetInt32(0), reader.GetString(3), reader.GetString(5), reader.GetDateTime(4), reader.GetString(6));
				// Too many connections with recursion?
				msg.Replies = GetReplies(page, msg.ID);
				result.Add(msg);
			}
			Close(cmd);
			return result;
		}

		public int GetMessageCount(PageInfo page) {
			SqlCommand cmd = GetCommand();
			cmd.CommandText = "SELECT COUNT(*) FROM [Message] WHERE [Page] = @Page";
			cmd.Parameters.Add(new SqlParameter("Page", page.Name));
			return (int)ExecuteScalar(cmd);
		}

		public bool AddMessage(PageInfo page, string username, string subject, DateTime dateTime, string body, int parent) {
			SqlCommand cmd = GetCommand();
			cmd.CommandText = "INSERT INTO [Message] VALUES (@Page, @Parent, @Username, @DateTime, @Subject, @Body)";
			cmd.Parameters.Add(new SqlParameter("Page", page.Name));
			cmd.Parameters.Add(new SqlParameter("Parent", parent));
			cmd.Parameters.Add(new SqlParameter("Username", username));
			cmd.Parameters.Add(new SqlParameter("DateTime", dateTime));
			cmd.Parameters.Add(new SqlParameter("Subject", subject));
			cmd.Parameters.Add(new SqlParameter("Body", body));
			return ExecuteNonQuery(cmd) == 1;
		}

		public bool RemoveMessage(PageInfo page, int id, bool removeReplies) {
			SqlCommand cmd = GetCommand();
			if(removeReplies) {
				// Recursively remove all the replies
				cmd.CommandText = "SELECT [ID] FROM [Message] WHERE [Page] = @Page AND [Parent] = @ID";
				cmd.Parameters.Add(new SqlParameter("Page", page.Name));
				cmd.Parameters.Add(new SqlParameter("ID", id));
				SqlDataReader reader = ExecuteReader(cmd);
				while(reader != null && reader.Read()) {
					RemoveMessage(page, reader.GetInt32(0), true);
				}
				Close(cmd);
				// Delete the message
				cmd = GetCommand();
				cmd.CommandText = "DELETE FROM [Message] WHERE [Page] = @Page AND [ID] = @ID";
				cmd.Parameters.Add(new SqlParameter("Page", page.Name));
				cmd.Parameters.Add(new SqlParameter("ID", id));
				return ExecuteNonQuery(cmd) == 1;
			}
			else {
				// Find parent
				cmd.CommandText = "SELECT [Parent] FROM [Message] WHERE [Page] = @Page AND [ID] = @ID";
				cmd.Parameters.Add(new SqlParameter("Page", page.Name));
				cmd.Parameters.Add(new SqlParameter("ID", id));
				int pid = (int)ExecuteScalar(cmd); // Can be -1
				// Set new parent, delete message
				cmd = GetCommand();
				cmd.CommandText = "UPDATE [Message] SET [Parent] = @Parent WHERE [Page] = @Page AND [Parent] = @ID; DELETE FROM [Message] WHERE [Page] = @Page AND [ID] = @ID";
				cmd.Parameters.Add(new SqlParameter("Parent", pid));
				cmd.Parameters.Add(new SqlParameter("Page", page.Name));
				cmd.Parameters.Add(new SqlParameter("ID", id));
				return ExecuteNonQuery(cmd) > 0;
			}
		}

		public bool ModifyMessage(PageInfo page, int id, string username, string subject, DateTime dateTime, string body) {
			SqlCommand cmd = GetCommand();
			cmd.CommandText = "UPDATE [Message] SET [Username] = @Username, [Subject] = @Subject, [DateTime] = @DateTime, [Body] = @Body WHERE [Page] = @Page AND [ID] = @ID";
			cmd.Parameters.Add(new SqlParameter("Username", username));
			cmd.Parameters.Add(new SqlParameter("Subject", subject));
			cmd.Parameters.Add(new SqlParameter("DateTime", dateTime));
			cmd.Parameters.Add(new SqlParameter("Body", body));
			cmd.Parameters.Add(new SqlParameter("Page", page.Name));
			cmd.Parameters.Add(new SqlParameter("ID", id));
			return ExecuteNonQuery(cmd) == 1;
		}

		public NavigationPath[] AllNavigationPaths {
			get {
				SqlCommand cmd = GetCommand();
				cmd.CommandText = "SELECT * FROM [NavigationPath]";
				SqlDataReader reader = ExecuteReader(cmd);
				List<NavigationPath> result = new List<NavigationPath>();
				while(reader != null && reader.Read()) {
					NavigationPath n = new NavigationPath(reader.GetString(0), this);
					n.Pages.AddRange(GetPages(n.Name));
				}
				Close(cmd);
				return result.ToArray();
			}
		}

		private List<string> GetPages(string path) {
			SqlCommand cmd = GetCommand();
			cmd.CommandText = "SELECT [Page] FROM [NavigationPathBinding] WHERE [NavigationPath] = @Path ORDER BY [Number]";
			cmd.Parameters.Add(new SqlParameter("Path", path));
			SqlDataReader reader = ExecuteReader(cmd);
			List<string> result = new List<string>();
			while(reader != null && reader.Read()) {
				result.Add(reader.GetString(0));
			}
			Close(cmd);
			return result;
		}

		public NavigationPath AddNavigationPath(string name, PageInfo[] pages) {
			SqlCommand cmd = GetCommand();
			StringBuilder sb = new StringBuilder();
			sb.Append("INSERT INTO [NavigationPath] VALUES (@Path); ");
			cmd.Parameters.Add(new SqlParameter("Path", name));
			string[] pgs = new string[pages.Length];
			cmd.Parameters.Add(new SqlParameter("Path2", name));
			for(int i = 0; i < pages.Length; i++) {
				sb.Append("INSERT INTO [NavigationPathBinding] VALUES (@Path2, @Page");
				sb.Append(i.ToString());
				sb.Append(", @Number");
				sb.Append(i.ToString());
				sb.Append("); ");
				cmd.Parameters.Add(new SqlParameter("Page" + i.ToString(), pages[i].Name));
				cmd.Parameters.Add(new SqlParameter("Number" + i.ToString(), i));
				pgs[i] = pages[i].Name;
			}
			cmd.CommandText = sb.ToString();
			if(ExecuteNonQuery(cmd) == pages.Length + 1) {
				NavigationPath p = new NavigationPath(name, this);
				p.Pages.AddRange(pgs);
				return p;
			}
			else return null;
		}

		public NavigationPath ModifyNavigationPath(string name, PageInfo[] pages) {
			// Shortcut
			if(!RemoveNavigationPath(name)) return null;
			return AddNavigationPath(name, pages);
		}

		public bool RemoveNavigationPath(string name) {
			SqlCommand cmd = GetCommand();
			cmd.CommandText = "DELETE FROM [NavigationPath] WHERE [Name] = @Path";
			cmd.Parameters.Add(new SqlParameter("Path", name));
			return ExecuteNonQuery(cmd) == 1;
		}

		public Snippet[] AllSnippets {
			get {
				SqlCommand cmd = GetCommand();
				cmd.CommandText = "SELECT * FROM [Snippet]";
				SqlDataReader reader = ExecuteReader(cmd);
				List<Snippet> result = new List<Snippet>();
				while(reader != null && reader.Read()) {
					result.Add(new Snippet(reader.GetString(0), reader.GetString(1), this));
				}
				Close(cmd);
				return result.ToArray();
			}
		}

		public Snippet AddSnippet(string name, string content) {
			SqlCommand cmd = GetCommand();
			cmd.CommandText = "INSERT INTO [Snippet] VALUES (@Name, @Content)";
			cmd.Parameters.Add(new SqlParameter("Name", name));
			cmd.Parameters.Add(new SqlParameter("Content", content));
			if(ExecuteNonQuery(cmd) == 1) {
				return new Snippet(name, content, this);
			}
			else return null;
		}

		public Snippet ModifySnippet(string name, string content) {
			SqlCommand cmd = GetCommand();
			cmd.CommandText = "UPDATE [Snippet] SET [Content] = @Content WHERE [Name] = @Name";
			cmd.Parameters.Add(new SqlParameter("Content", content));
			cmd.Parameters.Add(new SqlParameter("Name", name));
			if(ExecuteNonQuery(cmd) == 1) {
				return new Snippet(name, content, this);
			}
			else return null;
		}

		public bool RemoveSnippet(string name) {
			SqlCommand cmd = GetCommand();
			cmd.CommandText = "DELETE FROM [Snippet] WHERE [Name] = @Name";
			cmd.Parameters.Add(new SqlParameter("Name", name));
			return ExecuteNonQuery(cmd) == 1;
		}
	}

}