database.cs

投票系统模块设计

using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using Common;

namespace Data
{
	/// <summary>
	/// Database 的摘要说明。
	/// </summary>
	public class Database
	{
		public Database()
		{
		}

		/// <summary>
		/// 新增用户
		/// </summary>
		/// <param name="user">新增的用户信息</param>
		/// <returns>存储过程的返回值</returns>
		public static int CreateUser(User user)
		{
			int iRet = 1;
			//数据连接
			SqlConnection conn = new SqlConnection(
				ConfigurationSettings.AppSettings["ConnStr"]);

			//为执行存储过程作准备，参数赋值
			SqlCommand cmd = new SqlCommand("CreateUser", conn);
			cmd.CommandType = CommandType.StoredProcedure;
			cmd.Parameters.Add("@username", user.username);
			cmd.Parameters.Add("@password", user.password);
			cmd.Parameters.Add("@realname", user.realname);
			cmd.Parameters.Add("@gender", user.gender);
			cmd.Parameters.Add("@teleno", user.teleno);
			cmd.Parameters.Add("@email", user.email);
			cmd.Parameters.Add("@address", user.address);

			//存储过程返回值
			SqlParameter paramOut = cmd.Parameters.Add("@RETURN_VALUE", "");
			paramOut.Direction = ParameterDirection.ReturnValue;

			try
			{
				//打开数据连接
				conn.Open();

				//执行存储过程，添加用户
				cmd.ExecuteNonQuery();

				//返回值
				iRet = (int)cmd.Parameters["@RETURN_VALUE"].Value;
			}
			catch (Exception ex)  //其他异常
			{
				throw ex;
			}
			finally
			{
				//最后关闭数据连接
				conn.Close();
			}

			return iRet;
		}

		/// <summary>
		/// 删除用户
		/// </summary>
		/// <param name="user">删除的用户信息</param>
		/// <returns>0:成功,1:失败</returns>
		public static int DeleteUser(User user)
		{
			return 0;
		}

		/// <summary>
		/// 修改用户信息
		/// </summary>
		/// <param name="user">修改后的用户信息</param>
		/// <returns>是否成功</returns>
		public static bool ModifyUser(User user)
		{
			//数据连接
			SqlConnection conn = new SqlConnection(
				ConfigurationSettings.AppSettings["ConnStr"]);
			conn.Open();

			SqlCommand cmd = conn.CreateCommand();
			cmd.CommandType = CommandType.StoredProcedure;
			cmd.CommandText = "ModifyUser";
			cmd.Parameters.Add("@role_id", user.role_id);
			cmd.Parameters.Add("@username", user.username);

			int iRet = -1;
			try
			{
				iRet = cmd.ExecuteNonQuery();
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			catch (Exception ex)
			{
				throw ex;
			}

			return (iRet > 0);
		}

		/// <summary>
		/// 校验用户信息
		/// </summary>
		/// <param name="user">待校验的用户信息</param>
		/// <returns>0:成功,1失败</returns>
		public static int VerifyUser(User user)
		{
			return 0;
		}


		/// <summary>
		/// 获取用户的角色信息
		/// </summary>
		/// <param name="strUsername">用户名</param>
		/// <returns>角色信息</returns>
		public static Role GetRoleOfUser(string strUsername)
		{
			Role role = null;

			//数据连接
			SqlConnection conn = new SqlConnection(
				ConfigurationSettings.AppSettings["ConnStr"]);

			//存储过程GetRoleOfUser
			SqlCommand cmd = conn.CreateCommand();
			cmd.CommandType = CommandType.StoredProcedure;
			cmd.CommandText = "GetRoleOfUser";
			cmd.Parameters.Add("@username", strUsername);

			try
			{
				conn.Open();
				using (SqlDataReader reader = cmd.ExecuteReader())
				{
					if (reader.Read())
					{
						role = new Role();
						role.id = reader.GetInt32(0);
						role.name = reader.GetString(1);
						role.caption = reader.GetString(2);
						role.permission = reader.GetInt32(3);
					}
				}
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			finally
			{
				conn.Close();
			}

			return role;
		}

		/// <summary>
		/// 设置用户的角色
		/// </summary>
		/// <param name="user">用户信息</param>
		/// <param name="role">角色信息</param>
		/// <returns>0:成功,1:失败</returns>
		public static int SetRoleOfUser(User user, Role role)
		{
			return 0;
		}

		/// <summary>
		/// 新增角色
		/// </summary>
		/// <param name="role">角色信息</param>
		/// <returns>0:成功,1:失败</returns>
		public static int AddRole(Role role)
		{
			return 0;
		}

		/// <summary>
		/// 删除角色
		/// </summary>
		/// <param name="role">角色信息</param>
		/// <returns>0:成功,1:失败</returns>
		public static int DeleteRole(Role role)
		{
			return 0;
		}

		/// <summary>
		/// 修改角色信息
		/// </summary>
		/// <param name="role">角色信息</param>
		/// <returns>0:成功,1:失败</returns>
		public static int ModifyRole(Role role)
		{
			return 0;
		}

		/// <summary>
		/// 检查角色是否具有相关权限
		/// </summary>
		/// <param name="role">角色信息</param>
		/// <param name="per">检查的权限</param>
		/// <returns>是否具有权限</returns>
		public static bool CheckPermission(Role role, Permission per)
		{
			int iper = (int)per;
			return ( (iper & role.permission) == iper );
		}

		/// <summary>
		/// 发表文章
		/// </summary>
		/// <param name="art">文章信息</param>
		/// <param name="strUsername">作者</param>
		/// <returns>新文章id</returns>
		public static int PostArticle(Article art, string strUsername)
		{
			//数据连接
			SqlConnection conn = new SqlConnection(
				ConfigurationSettings.AppSettings["ConnStr"]);
			conn.Open();

			int iRet = -1;

			SqlCommand cmd = conn.CreateCommand();
			cmd.CommandType = CommandType.StoredProcedure;
			cmd.CommandText = "PostArticle";
			cmd.Parameters.Add("@title", art.title);
			cmd.Parameters.Add("@content", art.content);
			cmd.Parameters.Add("@father_id", art.father_id);
			cmd.Parameters.Add("@username", strUsername);
			SqlParameter paramOut = cmd.Parameters.Add("@RETURN_VALUE", SqlDbType.Int);
			paramOut.Direction = ParameterDirection.ReturnValue;


			try
			{
				cmd.ExecuteNonQuery();
				iRet = (int)cmd.Parameters["@RETURN_VALUE"].Value;
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			catch (Exception ex)
			{
				throw ex;
			}

			return iRet;

		}

		/// <summary>
		/// 修改文章
		/// </summary>
		/// <param name="art">文章信息</param>
		/// <returns>是否成功</returns>
		public static bool ModifyArticle(Article art)
		{
			//数据连接
			SqlConnection conn = new SqlConnection(
				ConfigurationSettings.AppSettings["ConnStr"]);
			conn.Open();

			SqlCommand cmd = conn.CreateCommand();
			cmd.CommandType = CommandType.StoredProcedure;
			cmd.CommandText = "ModifyArticle";
			cmd.Parameters.Add("@id", art.id);
			cmd.Parameters.Add("@title", art.title);
			cmd.Parameters.Add("@content", art.content);

			int iRet = -1;
			try
			{
				iRet = cmd.ExecuteNonQuery();
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			catch (Exception ex)
			{
				throw ex;
			}

			return (iRet > 0);
		}

		/// <summary>
		/// 删除文章
		/// </summary>
		/// <param name="id">文章id</param>
		/// <returns>是否成功</returns>
		public static bool DeleteArticle(int id)
		{
			//数据连接
			SqlConnection conn = new SqlConnection(
				ConfigurationSettings.AppSettings["ConnStr"]);
			conn.Open();

			SqlCommand cmd = conn.CreateCommand();
			cmd.CommandType = CommandType.StoredProcedure;
			cmd.CommandText = "DeleteArticle";
			cmd.Parameters.Add("@id", id);

			int iRet = -1;
			try
			{
				iRet = cmd.ExecuteNonQuery();
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			catch (Exception ex)
			{
				throw ex;
			}

			return (iRet > 0);
		}

		/// <summary>
		/// 列出全部文章信息
		/// </summary>
		/// <returns>文章信息的数据集</returns>
		public static DataSet ListArticles()
		{
			//数据连接
			SqlConnection conn = new SqlConnection(
				ConfigurationSettings.AppSettings["ConnStr"]);
			conn.Open();

			SqlCommand cmd = conn.CreateCommand();
			cmd.CommandType = CommandType.StoredProcedure;
			cmd.CommandText = "ListArticles";

			SqlDataAdapter da = new SqlDataAdapter(cmd);
			DataSet ds = null;


			try
			{
				ds = new DataSet();
				da.Fill(ds);
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			catch (Exception ex)
			{
				throw ex;
			}

			return ds;
		}

		/// <summary>
		/// 获取文章及其回复信息
		/// </summary>
		/// <param name="id">文章id</param>
		/// <returns>数据集</returns>
		public static DataSet GetArticle(int id)
		{
			//数据连接
			SqlConnection conn = new SqlConnection(
				ConfigurationSettings.AppSettings["ConnStr"]);
			conn.Open();

			SqlCommand cmd = conn.CreateCommand();
			cmd.CommandType = CommandType.StoredProcedure;
			cmd.CommandText = "GetArticle";
			cmd.Parameters.Add("@id", id);

			SqlDataAdapter da = new SqlDataAdapter(cmd);
			DataSet ds = null;

			try
			{
				ds = new DataSet();
				da.Fill(ds);
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			catch (Exception ex)
			{
				throw ex;
			}

			return ds;
		}

		/// <summary>
		/// 获取单篇文章信息
		/// </summary>
		/// <param name="id">文章id</param>
		/// <returns>文章信息数据集</returns>
		public static DataSet GetOneArticle(int id)
		{
			//数据连接
			SqlConnection conn = new SqlConnection(
				ConfigurationSettings.AppSettings["ConnStr"]);
			conn.Open();

			SqlCommand cmd = conn.CreateCommand();
			cmd.CommandType = CommandType.StoredProcedure;
			cmd.CommandText = "GetOneArticle";
			cmd.Parameters.Add("@id", id);

			SqlDataAdapter da = new SqlDataAdapter(cmd);
			DataSet ds = null;

			try
			{
				ds = new DataSet();
				da.Fill(ds);
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			catch (Exception ex)
			{
				throw ex;
			}

			return ds;
		}

		/// <summary>
		/// 获取最上级文章的id
		/// </summary>
		/// <param name="id">文章id</param>
		/// <returns>最上级文章的id</returns>
		public static int GetTopID(int id)
		{
			//数据连接
			SqlConnection conn = new SqlConnection(
				ConfigurationSettings.AppSettings["ConnStr"]);
			conn.Open();

			SqlCommand cmd = conn.CreateCommand();
			cmd.CommandText = string.Format("SELECT dbo.GetTopID({0})", id);


			int iRet = -1;

			try
			{
				iRet = (int)cmd.ExecuteScalar();
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			catch (Exception ex)
			{
				throw ex;
			}

			return iRet;
		}

		/// <summary>
		/// 根据用户id获取用户名
		/// </summary>
		/// <param name="id">用户id</param>
		/// <returns>用户名</returns>
		public static string GetUsernameByID(int id)
		{
			//数据连接
			SqlConnection conn = new SqlConnection(
				ConfigurationSettings.AppSettings["ConnStr"]);
			conn.Open();

			SqlCommand cmd = conn.CreateCommand();
			cmd.CommandText = string.Format(
                "SELECT username FROM [User] WHERE id = {0}", id);


			string str = string.Empty;

			try
			{
				str = (string)cmd.ExecuteScalar();
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			catch (Exception ex)
			{
				throw ex;
			}

			return str;
		}

		/// <summary>
		/// 列出全部用户信息
		/// </summary>
		/// <returns>用户信息的数据集</returns>
		public static DataSet GetAllUsers()
		{
			//数据连接
			SqlConnection conn = new SqlConnection(
				ConfigurationSettings.AppSettings["ConnStr"]);
			conn.Open();

			SqlCommand cmd = conn.CreateCommand();
			cmd.CommandType = CommandType.StoredProcedure;
			cmd.CommandText = "GetAllUsers";

			SqlDataAdapter da = new SqlDataAdapter(cmd);
			DataSet ds = null;

			try
			{
				ds = new DataSet();
				da.Fill(ds);
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			catch (Exception ex)
			{
				throw ex;
			}

			return ds;
		}

		/// <summary>
		/// 列出全部角色信息
		/// </summary>
		/// <returns>角色信息的数据集</returns>
		public static DataSet GetAllRoles()
		{
			//数据连接
			SqlConnection conn = new SqlConnection(
				ConfigurationSettings.AppSettings["ConnStr"]);
			conn.Open();

			SqlCommand cmd = conn.CreateCommand();
			cmd.CommandType = CommandType.StoredProcedure;
			cmd.CommandText = "GetAllRoles";

			SqlDataAdapter da = new SqlDataAdapter(cmd);
			DataSet ds = null;

			try
			{
				ds = new DataSet();
				da.Fill(ds);
			}
			catch (SqlException ex)
			{
				throw ex;
			}
			catch (Exception ex)
			{
				throw ex;
			}

			return ds;
		}
	}

	public enum Permission
	{
		//0:阅读文章, 1:发表文章, 2:修改文章, 3:删除文章, 4:管理用户, 5:管理角色
		Read = 0x1,
		Post = 0x2,
		Modify = 0x4,
		Delete = 0x8,
		UserManage = 0x10,
	}
}