webservice.cs

一个采用VS2008+Sql2000开发的任务管理系统

			Command.ExecuteNonQuery();

			Command.Connection.Close();
			Command.Dispose();
			cn.Dispose();
		}

		/// <summary>
		/// Retrieves a user object from the database that describes the user specified by the UserID argument
		/// </summary>
		public UserInformation GetUserItem(UserInformation CurrentUser, int UserID)
		{
			SqlConnection cn = new SqlConnection(this.ConnectionString);
			SqlCommand Command = new SqlCommand("TaskList_GetUserInformation", cn);
			UserInformation ui = new UserInformation();
																																							
			Command.CommandType = CommandType.StoredProcedure;

			Command.Connection.Open();

			Command.Parameters.Add("@Username", SqlDbType.VarChar, 20).Value = CurrentUser.Username;
			Command.Parameters.Add("@UserID", SqlDbType.BigInt).Value = UserID;
			
			SqlDataReader dr = Command.ExecuteReader(CommandBehavior.CloseConnection);

			while (dr.Read())
			{
				ui.Username					= dr["Username"].ToString();
				ui.PasswordHash			= dr["PasswordHash"].ToString();
				ui.UserID						= Convert.ToInt32(dr["UserID"]);
				ui.IsManager				= Convert.ToBoolean(dr["IsManager"]);
				ui.IsAdministrator	= Convert.ToBoolean(dr["IsAdministrator"]);
        ui.ShouldNotify     = Convert.ToBoolean(dr["ShouldNotify"]);
        ui.NotifyPeriod     = Convert.ToInt32(dr["NotifyPeriod"]);
			}

			return ui;
		}
		/// <summary>
		/// Adds a user to a task list
		/// </summary>
		public void AddUserToTaskList(UserInformation CurrentUser, UserInformation UserToModify, int TaskListID)
		{
			SqlConnection cn = new SqlConnection(this.ConnectionString);
			SqlCommand Command = new SqlCommand("TaskList_AddUserToTaskList", cn);
			Command.CommandType = CommandType.StoredProcedure;

			Command.Connection.Open();

			Command.Parameters.Add("@Username", SqlDbType.VarChar, 20).Value = CurrentUser.Username;
			Command.Parameters.Add("@PasswordHash", SqlDbType.VarChar, 50).Value = CurrentUser.PasswordHash;
			Command.Parameters.Add("@UserID", SqlDbType.VarChar, 20).Value = UserToModify.UserID;
			Command.Parameters.Add("@TaskListID", SqlDbType.BigInt).Value = TaskListID;

			Command.ExecuteNonQuery();

			Command.Connection.Close();
			Command.Dispose();
			cn.Dispose();
		}

		/// <summary>
		/// Removes a user from a task list
		/// </summary>
		public void RemoveUserFromTaskList(UserInformation CurrentUser, UserInformation UserToModify, int TaskListID)
		{
			SqlConnection cn = new SqlConnection(this.ConnectionString);
			SqlCommand Command = new SqlCommand("TaskList_RemoveUserFromTaskList", cn);
			Command.CommandType = CommandType.StoredProcedure;

			Command.Connection.Open();

			Command.Parameters.Add("@Username", SqlDbType.VarChar, 20).Value = CurrentUser.Username;
			Command.Parameters.Add("@PasswordHash", SqlDbType.VarChar, 50).Value = CurrentUser.PasswordHash;
			Command.Parameters.Add("@UserID", SqlDbType.VarChar, 20).Value = UserToModify.UserID;
			Command.Parameters.Add("@TaskListID", SqlDbType.BigInt).Value = TaskListID;

			Command.ExecuteNonQuery();

			Command.Connection.Close();
			Command.Dispose();
			cn.Dispose();
		}

		/// <summary>
		/// The Add method adds a task list item to this user's task list
		/// </summary>
		public void Add(UserInformation ui, TaskListItem ti)
		{
			this.Add(ui, ti, DateTime.Now);		
		}

		/// <summary>
		/// The Add method adds a task list to this user's task list.
		/// </summary>
		private void Add(UserInformation ui, TaskListItem ti, DateTime CreatedDate)
		{
			SqlConnection cn = new SqlConnection(this.ConnectionString);
			SqlCommand Command = new SqlCommand("TaskList_Add", cn);
			Command.CommandType = CommandType.StoredProcedure;
			
			Command.Connection.Open();

			Command.Parameters.Add("@Username", SqlDbType.VarChar, 20).Value = ui.Username;
			Command.Parameters.Add("@PasswordHash", SqlDbType.VarChar, 50).Value = ui.PasswordHash;
			Command.Parameters.Add("@TaskListID", SqlDbType.VarChar, 50).Value = ti.TaskListID;
			Command.Parameters.Add("@Subject", SqlDbType.VarChar, 255).Value = ti.Subject;
			Command.Parameters.Add("@Body", SqlDbType.VarChar, 5000).Value = ti.Body;
			Command.Parameters.Add("@CreatedDate", SqlDbType.DateTime).Value = CreatedDate;
			Command.Parameters.Add("@ModifiedDate", SqlDbType.DateTime).Value = DateTime.Now;
			Command.Parameters.Add("@Status", SqlDbType.Int).Value = ti.StatusValue;
			Command.Parameters.Add("@CategoryID", SqlDbType.BigInt).Value = ti.CategoryID;

			Command.ExecuteNonQuery();

			Command.Connection.Close();

			Command.Dispose();
			cn.Dispose();
		}

		/// <summary>
		/// The GetTaskList method retrieves a user's task list, in DataSet format
		/// </summary>
		public DataSet GetTaskList(UserInformation ui, int TaskListID)
		{
			SqlConnection cn = new SqlConnection(this.ConnectionString);
			SqlCommand Command = new SqlCommand("TaskList_GetTaskList", cn);
			Command.CommandType = CommandType.StoredProcedure;
			
			Command.Connection.Open();

			Command.Parameters.Add("@Username", SqlDbType.VarChar, 20).Value = ui.Username;
			Command.Parameters.Add("@PasswordHash", SqlDbType.VarChar, 50).Value = ui.PasswordHash;
			Command.Parameters.Add("@TaskListID", SqlDbType.BigInt).Value = TaskListID;

			SqlDataAdapter da = new SqlDataAdapter(Command);
			DataSet ds = new DataSet("TaskList");
			da.Fill(ds);

			Command.Dispose();
			cn.Dispose();
			da.Dispose();

			return ds;
		}

		/// <summary>
		/// The Delete method removes a task list item
		/// </summary>
		public void Delete(UserInformation ui, int TaskListID, int TaskListItemID)
		{
			SqlConnection cn = new SqlConnection(this.ConnectionString);
			SqlCommand Command = new SqlCommand("TaskList_Delete", cn);
			Command.CommandType = CommandType.StoredProcedure;

			Command.Connection.Open();

			Command.Parameters.Add("@Username", SqlDbType.VarChar, 20).Value = ui.Username;
			Command.Parameters.Add("@PasswordHash", SqlDbType.VarChar, 50).Value = ui.PasswordHash;
			Command.Parameters.Add("@TaskListID", SqlDbType.VarChar, 50).Value = TaskListID;
			Command.Parameters.Add("@ID", SqlDbType.BigInt).Value = TaskListItemID;

			Command.ExecuteNonQuery();

			Command.Connection.Close();
			Command.Dispose();
			cn.Dispose();
		}

		/// <summary>
		/// Modify method takes a task list item and updates it
		/// </summary>
		public void Modify(UserInformation ui, TaskListItem ti, int TaskListID)
		{
      TaskListItem OldItem = GetTaskListItem(ui, ti.ID, ti.TaskListID);
      
      if (OldItem.CategoryID != ti.CategoryID)
        ti = AppendCategoryChangedText(ui, ti, OldItem);

      if (OldItem.StatusValue != ti.StatusValue)
        ti = AppendStatusChangedText(ui, ti, OldItem);

      if (OldItem.TaskListID != ti.TaskListID)
        ti = AppendStatusChangedText(ui, ti, OldItem);

      this.Delete(ui, TaskListID,ti.ID);
			this.Add(ui, ti, ti.CreatedDate);
		}

		/// <summary>
		/// Retrieves a list of users from the database
		/// </summary>
		public SqlDataReader GetUserList(UserInformation CurrentUser)
		{
			SqlConnection cn = new SqlConnection(this.ConnectionString);
			SqlCommand Command = new SqlCommand("TaskList_GetUserList", cn);
			Command.CommandType = CommandType.StoredProcedure;

			Command.Parameters.Add("@Username", SqlDbType.VarChar, 20).Value = CurrentUser.Username;
			Command.Parameters.Add("@PasswordHash", SqlDbType.VarChar, 50).Value = CurrentUser.PasswordHash;

			Command.Connection.Open();

			SqlDataReader dr = Command.ExecuteReader(CommandBehavior.CloseConnection);

			return dr;
		}

		/// <summary>
		/// Retrieves a list of task lists from the database
		/// </summary>
		public SqlDataReader GetTaskListList(UserInformation CurrentUser)
		{
			SqlConnection cn = new SqlConnection(this.ConnectionString);
			SqlCommand Command = new SqlCommand("TaskList_GetTaskListList", cn);
			Command.CommandType = CommandType.StoredProcedure;

			Command.Parameters.Add("@Username", SqlDbType.VarChar, 20).Value = CurrentUser.Username;
			Command.Parameters.Add("@PasswordHash", SqlDbType.VarChar, 50).Value = CurrentUser.PasswordHash;

			Command.Connection.Open();

			SqlDataReader dr = Command.ExecuteReader(CommandBehavior.CloseConnection);

			return dr;	
		}

		/// <summary>
		/// Retrieves a list of users assigned to a particular task list
		/// </summary>
		public SqlDataReader GetUsersAssignedToTaskList(UserInformation CurrentUser, int TaskListID)
		{
			SqlConnection cn = new SqlConnection(this.ConnectionString);
			SqlCommand Command = new SqlCommand("TaskList_GetUsersAssignedToTaskList", cn);
			Command.CommandType = CommandType.StoredProcedure;

			Command.Connection.Open();

			Command.Parameters.Add("@Username", SqlDbType.VarChar, 20).Value = CurrentUser.Username;
			Command.Parameters.Add("@PasswordHash", SqlDbType.VarChar, 50).Value = CurrentUser.PasswordHash;
			Command.Parameters.Add("@TaskListID", SqlDbType.BigInt).Value = TaskListID;

			SqlDataReader dr = Command.ExecuteReader(CommandBehavior.CloseConnection);

			return dr;
		}

		/// <summary>
		/// Retrieves properties & statistics about a given task list
		/// </summary>
		public SqlDataReader GetTaskListProperties(UserInformation CurrentUser, int TaskListID)
		{
			SqlConnection cn = new SqlConnection(this.ConnectionString);
			SqlCommand Command = new SqlCommand("TaskList_GetTaskListProperties", cn);
			Command.CommandType = CommandType.StoredProcedure;

			Command.Connection.Open();

			Command.Parameters.Add("@Username", SqlDbType.VarChar, 20).Value = CurrentUser.Username;
			Command.Parameters.Add("@PasswordHash", SqlDbType.VarChar, 50).Value = CurrentUser.PasswordHash;
			Command.Parameters.Add("@TaskListID", SqlDbType.BigInt).Value = TaskListID;

			SqlDataReader dr = Command.ExecuteReader(CommandBehavior.CloseConnection);

			return dr;
		}

		/// <summary>
		/// Retrieves properties & statistics about a given user
		/// </summary>
		public SqlDataReader GetUserProperties(UserInformation CurrentUser, int UserID)
		{
			SqlConnection cn = new SqlConnection(this.ConnectionString);
			SqlCommand Command = new SqlCommand("TaskList_GetUserProperties", cn);
			Command.CommandType = CommandType.StoredProcedure;

			Command.Connection.Open();

			Command.Parameters.Add("@Username", SqlDbType.VarChar, 20).Value = CurrentUser.Username;
			Command.Parameters.Add("@PasswordHash", SqlDbType.VarChar, 50).Value = CurrentUser.PasswordHash;
			Command.Parameters.Add("@UserID", SqlDbType.BigInt).Value = UserID;

			SqlDataReader dr = Command.ExecuteReader(CommandBehavior.CloseConnection);

			return dr;
		}

		/// <summary>
		/// Retrieves a single task list item
		/// </summary>
		public TaskListItem GetTaskListItem(UserInformation CurrentUser, int TaskID, int TaskListID)
		{
			SqlConnection cn = new SqlConnection(this.ConnectionString);
			SqlCommand Command = new SqlCommand("TaskList_GetTaskListItem", cn);
			Command.CommandType = CommandType.StoredProcedure;

			Command.Connection.Open();

			Command.Parameters.Add("@Username", SqlDbType.VarChar, 20).Value = CurrentUser.Username;
			Command.Parameters.Add("@PasswordHash", SqlDbType.VarChar, 50).Value = CurrentUser.PasswordHash;
			Command.Parameters.Add("@TaskID", SqlDbType.BigInt).Value = TaskID;
			Command.Parameters.Add("@TaskListID", SqlDbType.BigInt).Value = TaskListID;

			SqlDataReader dr = Command.ExecuteReader(CommandBehavior.CloseConnection);
			TaskListItem ti = new TaskListItem();

			while (dr.Read())
			{
				ti.ID					  = Convert.ToInt32(dr["TaskID"]);
				ti.Body					= dr["TaskBody"].ToString();
				ti.Subject			= dr["TaskSubject"].ToString();
				ti.ModifiedDate = (DateTime)dr["ModifiedOn"];
				ti.CreatedDate  = (DateTime)dr["CreatedOn"];
				ti.OwnerUserID	= Convert.ToInt32(dr["UserID"]);
				ti.CreatedBy		= dr["Username"].ToString();
				ti.StatusValue	= Convert.ToInt32(dr["TaskStatusValue"]);
				ti.StatusName		= dr["TaskStatusName"].ToString();
				ti.TaskListID		= Convert.ToInt32(dr["TaskListID"]);
        ti.CategoryID   = Convert.ToInt32(dr["CategoryID"]);
			}

			Debug.WriteLine(DateTime.Now.ToString() + " >> Retrieved StatusName of " + ti.StatusName);
			return ti;
		}

		/// <summary>
		/// Retrieves a list of task status codes
		/// </summary>
		public SqlDataReader GetStatusList(UserInformation CurrentUser)
		{
			SqlConnection cn = new SqlConnection(this.ConnectionString);
			SqlCommand Command = new SqlCommand("TaskList_GetStatusList", cn);
			Command.CommandType = CommandType.StoredProcedure;

			Command.Connection.Open();

			Command.Parameters.Add("@Username", SqlDbType.VarChar, 20).Value = CurrentUser.Username;
			Command.Parameters.Add("@PasswordHash", SqlDbType.VarChar, 50).Value = CurrentUser.PasswordHash;

			SqlDataReader dr = Command.ExecuteReader(CommandBehavior.CloseConnection);

			return dr;
		}

		/// <summary>
		/// Retrieves a list of task list categories