webservice.cs

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

		/// </summary>
		public SqlDataReader GetCategoryList(UserInformation CurrentUser)
		{
			SqlCommand Command = new SqlCommand("TaskList_GetCategoryList", new SqlConnection(this.ConnectionString));
			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>
		/// Removes all show items from the database, effectively filtering all tasks from this
		/// user's task list.  Add tasks back with AddStatusShowPreference()
		/// </summary>
		public void ClearStatusShowPreferences(UserInformation CurrentUser)
		{
			SqlConnection cn = new SqlConnection(this.ConnectionString);
			SqlCommand Command = new SqlCommand("TaskList_ClearStatusShowPreferences", 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.ExecuteNonQuery();

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

		/// <summary>
		/// Adds a StatusID to the list of status types to show for this user.
		/// </summary>
		public void AddStatusShowPreference(UserInformation CurrentUser, int StatusID)
		{
			SqlConnection cn = new SqlConnection(this.ConnectionString);
			SqlCommand Command = new SqlCommand("TaskList_AddStatusShowPreference", 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("@StatusID", SqlDbType.BigInt).Value = StatusID;

			Command.ExecuteNonQuery();

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

		/// <summary>
		/// Changes the order in which we sort the given StatusID, 0 = first, n = last.
		/// </summary>
		public void UpdateSortOrderPreferences(UserInformation CurrentUser, int StatusID, int SortOrder)
		{
			SqlConnection cn = new SqlConnection(this.ConnectionString);
			SqlCommand Command = new SqlCommand("TaskList_UpdateSortOrderPreferences", 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("@StatusID", SqlDbType.BigInt).Value = StatusID;
			Command.Parameters.Add("@SortOrder", SqlDbType.Int).Value = SortOrder;

			Command.ExecuteNonQuery();

			Command.Connection.Close();
			Command.Dispose();
			cn.Dispose();
		}
		/// <summary>
		/// Retrieves the status id's to show for this user
		/// </summary>
		public SqlDataReader GetUserStatusShowPreferences(UserInformation CurrentUser)
		{
			SqlConnection cn = new SqlConnection(this.ConnectionString);
			SqlCommand Command = new SqlCommand("TaskList_GetUserStatusShowPreferences", 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 the current task sort order for this user
		/// </summary>
		public SqlDataReader GetUserSortOrderPreferences(UserInformation CurrentUser)
		{
			SqlConnection cn = new SqlConnection(this.ConnectionString);
			SqlCommand Command = new SqlCommand("TaskList_GetUserSortOrderPreferences", 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>
		/// Sets what method we use to determine which task list to show when the user first logs in
		/// </summary>
		public void ChangeStartupViewOption(UserInformation CurrentUser, StartupViewOptionEnum ViewOption)
		{
			SqlConnection cn = new SqlConnection(this.ConnectionString);
			SqlCommand Command = new SqlCommand("TaskList_ChangeStartupViewOption", 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("@StartupViewOption", SqlDbType.VarChar, 20).Value = ViewOption.ToString();

			Command.ExecuteNonQuery();

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

		/// <summary>
		/// Sets the Task List ID to show when the user first logs in
		/// </summary>
		public void ChangeStartupTaskListID(UserInformation CurrentUser, int TaskListID)
		{
			SqlConnection cn = new SqlConnection(this.ConnectionString);
			SqlCommand Command = new SqlCommand("TaskList_ChangeStartupTaskListID", 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;

			Command.ExecuteNonQuery();

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

		/// <summary>
		/// Retrieves the task list that this user should see when they first log in.
		/// </summary>
		public int GetStartupTaskListID(UserInformation CurrentUser)
		{
			SqlConnection cn = new SqlConnection(this.ConnectionString);
			SqlCommand Command = new SqlCommand("TaskList_GetStartupTaskListID", 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);

			while (dr.Read())
			{
				return Convert.ToInt32(dr["TaskListID"]);
			}
			return 0;
		}

		/// <summary>
		/// Retreives the way that this user wants the first task list shown to be determined
		/// </summary>
		public StartupViewOptionEnum GetStartupViewOption(UserInformation CurrentUser)
		{
			SqlConnection cn = new SqlConnection(this.ConnectionString);
			SqlCommand Command = new SqlCommand("TaskList_GetStartupViewOption", 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);

			while (dr.Read())
			{
				if (dr["StartupViewOption"].ToString() == "LastTaskList")
					return StartupViewOptionEnum.LastTaskList;
				else
					return StartupViewOptionEnum.SpecificTaskList;
			}
			return StartupViewOptionEnum.LastTaskList;
		}

		/// <summary>
		/// Retrieves the number of items that each task list page should display.  Can
		/// be "All" or any integer.
		public string GetNumberOfItemsToDisplayPerPage(UserInformation CurrentUser)
		{
			SqlConnection cn = new SqlConnection(this.ConnectionString);
			SqlCommand Command = new SqlCommand("TaskList_GetNumberOfItemsToDisplayPerPage", 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);

			while (dr.Read())
			{
				return dr["NumberOfItems"].ToString();
			}
			return "25"; // Default to 25
		}

		/// <summary>
		/// Sets the number of items that should be displayed on each task list page.  Can be
		/// the string "All" or any number.
		/// </summary>
		public void SetNumberOfItemsToDisplayPerPage(UserInformation CurrentUser, string NumberOfItems)
		{
			SqlConnection cn = new SqlConnection(this.ConnectionString);
			SqlCommand Command = new SqlCommand("TaskList_SetNumberOfItemsToDisplayPerPage", 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("@NumberOfItems", SqlDbType.VarChar, 20).Value = NumberOfItems;

			Command.ExecuteNonQuery();

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

    public SqlDataReader GetNotificationEmail(UserInformation CurrentUser)
    {
      SqlCommand Command = new SqlCommand("TaskList_GetNotificationEmail", new SqlConnection(this.ConnectionString));
      Command.CommandType = CommandType.StoredProcedure;

      Command.Connection.Open();
      return Command.ExecuteReader(CommandBehavior.CloseConnection);
    }

    public void UpdateNotificationEmail(UserInformation CurrentUser, string NotificationEmail)
    {
      SqlCommand Command = new SqlCommand("TaskList_UpdateNotificationEmail", new SqlConnection(this.ConnectionString));
      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("@NotificationEmail", SqlDbType.VarChar, 5000).Value = NotificationEmail;

      Command.ExecuteNonQuery();
      Command.Connection.Close();
    }

    public void UpdateUserNotification(UserInformation CurrentUser, bool ShouldNotify, int NotifyPeriod)
    {
      SqlCommand Command = new SqlCommand("TaskList_UpdateUserNotification", new SqlConnection(this.ConnectionString));
      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("@ShouldNotify", SqlDbType.Bit).Value = ShouldNotify;
      Command.Parameters.Add("@NotifyPeriod", SqlDbType.BigInt).Value = NotifyPeriod;

      Command.ExecuteNonQuery();
      Command.Connection.Close();
    }

    private TaskListItem AppendCategoryChangedText(UserInformation CurrentUser, TaskListItem NewItem, TaskListItem OldItem)
    {
      string OldCategoryDescription = String.Empty;
      string NewCategoryDescription = String.Empty;

      SqlDataReader dr = GetCategoryList(CurrentUser);
      while( dr.Read() )
      {
        if( Convert.ToInt32(dr["CategoryID"]) == OldItem.CategoryID )
          OldCategoryDescription = Convert.ToString(dr["Description"]);

        if( Convert.ToInt32(dr["CategoryID"]) == NewItem.CategoryID )
          NewCategoryDescription = Convert.ToString(dr["Description"]);
      }

      NewItem.Body += ("\n[" + DateTime.Now.ToString("MM/dd/yy hh:mm:ss") + " Category changed from " + OldCategoryDescription + " to " + NewCategoryDescription + "]");

      return NewItem;
    }

    private TaskListItem AppendStatusChangedText(UserInformation CurrentUser, TaskListItem NewItem, TaskListItem OldItem)
    {
      NewItem.Body += ("\n[" + DateTime.Now.ToString("MM/dd/yy hh:mm:ss") + " Status changed from " + OldItem.StatusName + " to " + NewItem.StatusName + "]");

      return NewItem;
    }

    private TaskListItem AppendTaskListChangedText(UserInformation CurrentUser, TaskListItem NewItem, TaskListItem OldItem)
    {
      string OldTaskListName = String.Empty;
      string NewTaskListName = String.Empty;

      SqlDataReader dr = this.GetTaskListList(CurrentUser);
      while( dr.Read() )
      {
        if( Convert.ToInt32(dr["TaskListID"]) == OldItem.TaskListID)
          OldTaskListName = Convert.ToString(dr["TaskListName"]);

        if( Convert.ToInt32(dr["TaskListID"]) == NewItem.TaskListID)
          NewTaskListName = Convert.ToString(dr["TaskListName"]);
      }

      NewItem.Body += ("\n[" + DateTime.Now.ToString("MM/dd/yy hh:mm:ss") + " Task List changed from " + OldTaskListName + " to " + NewTaskListName + "]");

      return NewItem;
    }
	}
}