sqldataaccesslayer.cs

V2005+SQL 2005 数据库与网络开发典型系统时间跟增系统

      SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_PROJECT_GETALLPROJECTS);
      List<Project> prjList = new List<Project>();
      TExecuteReaderCmd<Project>(sqlCmd, TGenerateProjectListFromReader<Project>, ref prjList);
      return prjList;
    }
      /// <summary>
      /// 根据指定的项目ID获取项目
      /// </summary>
      /// <param name="projectId"></param>
      /// <returns></returns>
    public override Project GetProjectById(int projectId) {
      if (projectId <= 0)
        throw (new ArgumentOutOfRangeException("projectId"));
      SqlCommand sqlCmd = new SqlCommand();
      AddParamToSQLCmd(sqlCmd, "@ProjectId", SqlDbType.Int, 0, ParameterDirection.Input, projectId);
      SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_PROJECT_GETAPROJECTBYID);
      List<Project> prjList = new List<Project>();
      TExecuteReaderCmd<Project>(sqlCmd, TGenerateProjectListFromReader<Project>, ref prjList);
      if (prjList.Count > 0)
        return prjList[0];
      else
        return null;
    }
      /// <summary>
      /// 根据指定的项目经理用户名项取所属的项目
      /// </summary>
      /// <param name="userName"></param>
      /// <returns></returns>
    public override List<Project> GetProjectsByManagerUserName(string userName) {
      if (userName == null || userName.Length == 0)
        throw (new ArgumentOutOfRangeException("userName"));
      SqlCommand sqlCmd = new SqlCommand();
      AddParamToSQLCmd(sqlCmd, "@ProjectManagerUserName", SqlDbType.NText, 256, ParameterDirection.Input, userName);
      SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_PROJECT_GETAPROJECTSBYMANAGERUSERNAME);
      List<Project> prjList = new List<Project>();
      TExecuteReaderCmd<Project>(sqlCmd, TGenerateProjectListFromReader<Project>, ref prjList);
      return prjList;
    }
/// <summary>
/// 获取项目成员列表
/// </summary>
/// <param name="Id"></param>
/// <returns></returns>
    public override List<string> GetProjectMembers(int Id) {
      if (Id <= 0)
        throw (new ArgumentOutOfRangeException("Id"));
      SqlCommand sqlCmd = new SqlCommand();
      AddParamToSQLCmd(sqlCmd, "@ProjectId", SqlDbType.Int, 0, ParameterDirection.Input, Id);
      SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_PROJECT_GETPROJECTMEMBERS);
      List<string> userList = new List<string>();
      TExecuteReaderCmd<string>(sqlCmd, TGenerateUsertListFromReader<string>, ref userList);
      return userList;
    }
      /// <summary>
      /// 根据指定的用户名获取该用户所属的项目
      /// </summary>
      /// <param name="userName"></param>
      /// <returns></returns>
    public override List<Project> GetProjectsByUserName(string userName) {
      if (userName == null || userName.Length == 0)
        throw (new ArgumentOutOfRangeException("userName"));
      SqlCommand sqlCmd = new SqlCommand();
      AddParamToSQLCmd(sqlCmd, "@UserName", SqlDbType.NText, 256, ParameterDirection.Input, userName);
      SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_PROJECT_GETPROJECTSBYYSERNAME);
      List<Project> prjList = new List<Project>();
      TExecuteReaderCmd<Project>(sqlCmd, TGenerateProjectListFromReader<Project>, ref prjList);
      return prjList;      
//      return (new List<Project>());
    }

      /// <summary>
      /// 从指定的项目中删除用户
      /// </summary>
      /// <param name="projectId"></param>
      /// <param name="userName"></param>
      /// <returns></returns>
    public override bool RemoveUserFromProject(int projectId, string userName) {
      if (String.IsNullOrEmpty(userName))
        throw (new ArgumentOutOfRangeException("userName"));
      if (projectId <= 0)
        throw (new ArgumentOutOfRangeException("projectId"));
      SqlCommand sqlCmd = new SqlCommand();
      AddParamToSQLCmd(sqlCmd, "@ResultValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
      AddParamToSQLCmd(sqlCmd, "@userName", SqlDbType.NVarChar, 0, ParameterDirection.Input, userName);
      AddParamToSQLCmd(sqlCmd, "@ProjectId", SqlDbType.Int, 0, ParameterDirection.Input, projectId);
      SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_PROJECT_REMOVEUSERFROMPROJECT);
      ExecuteScalarCmd(sqlCmd);
      int resultValue = (int)sqlCmd.Parameters["@ResultValue"].Value;
      return (resultValue == 0 ? true : false);
    }
      /// <summary>
      /// 更新项目
      /// </summary>
      /// <param name="projectToUpdate"></param>
      /// <returns></returns>
    public override bool UpdateProject(Project projectToUpdate) {
      // validate input
      if (projectToUpdate == null)
        throw (new ArgumentNullException("projectToUpdate"));
      // validate input
      if (projectToUpdate.Id <= 0)
        throw (new ArgumentOutOfRangeException("projectToUpdate"));
      SqlCommand sqlCmd = new SqlCommand();
      // set the type of parameter to add a new project
      AddParamToSQLCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
      AddParamToSQLCmd(sqlCmd, "@ProjectId", SqlDbType.Int, 0, ParameterDirection.Input, projectToUpdate.Id);
      AddParamToSQLCmd(sqlCmd, "@ProjectCompletionDate", SqlDbType.DateTime, 0, ParameterDirection.Input, projectToUpdate.CompletionDate);
      AddParamToSQLCmd(sqlCmd, "@ProjectDescription", SqlDbType.NText, 1000, ParameterDirection.Input, projectToUpdate.Description);
      AddParamToSQLCmd(sqlCmd, "@ProjectEstimateDuration", SqlDbType.Decimal, 0, ParameterDirection.Input, projectToUpdate.EstimateDuration);
      AddParamToSQLCmd(sqlCmd, "@ProjectManagerUserName", SqlDbType.NText, 256, ParameterDirection.Input, projectToUpdate.ManagerUserName);
      AddParamToSQLCmd(sqlCmd, "@ProjectName", SqlDbType.NText, 256, ParameterDirection.Input, projectToUpdate.Name);
      SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_PROJECT_UPDATE);
      ExecuteScalarCmd(sqlCmd);
      int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
      return (returnValue == 0 ? true : false);
    }


    /*** 时间项 ***/
    private string SP_TIMEENTRY_CREATE = "aspnet_starterkits_CreateNewTimeEntry";
    private string SP_TIMEENTRY_DELETE = "aspnet_starterkits_DeleteTimeEntry";
    private string SP_TIMEENTRY_GETALLTIMEENTRIES = "aspnet_starterkits_GetAllTimeEntries";
    private string SP_TIMEENTRY_GETALLTIMEENTRIESBYPROJECTID_USER = "aspnet_starterkits_GetAllTimeEntriesByProjectIdandUser";
    private string SP_TIMEENTRY_GETALLTIMEENTRIESBYUSERNAMEANDDATE = "aspnet_starterkits_GetAllTimeEntriesByProjectIdandUserAndDate";
    private string SP_TIMEENTRY_UPDATE = "aspnet_starterkits_UpdateTimeEntry";
    private string SP_TIMEENTRY_GETTIMEENTRYBYID = "aspnet_starterkits_GetTimeEntryById";


/// <summary>
/// 创建一个新的时间项
/// </summary>
/// <param name="newTimeEntry"></param>
/// <returns></returns>
    public override int CreateNewTimeEntry(TimeEntry newTimeEntry) {

      if (newTimeEntry == null)
        throw (new ArgumentNullException("newTimeEntry"));


      SqlCommand sqlCmd = new SqlCommand();

      AddParamToSQLCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);

      AddParamToSQLCmd(sqlCmd, "@CategoryId", SqlDbType.Int, 0, ParameterDirection.Input, newTimeEntry.CategoryId);
      AddParamToSQLCmd(sqlCmd, "@TimeEntryCreatorUserName", SqlDbType.NText, 255, ParameterDirection.Input, newTimeEntry.CreatorUserName);
      AddParamToSQLCmd(sqlCmd, "@TimeEntryDescription", SqlDbType.NText, 1000, ParameterDirection.Input, newTimeEntry.Description);
      AddParamToSQLCmd(sqlCmd, "@TimeEntryEstimateDuration", SqlDbType.Decimal, 0, ParameterDirection.Input, newTimeEntry.Duration);
      AddParamToSQLCmd(sqlCmd, "@TimeEntryEnteredDate", SqlDbType.DateTime, 0, ParameterDirection.Input, newTimeEntry.ReportedDate);
      AddParamToSQLCmd(sqlCmd, "@TimeEntryUserName", SqlDbType.NText, 255, ParameterDirection.Input, newTimeEntry.UserName);

      SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TIMEENTRY_CREATE);

      ExecuteScalarCmd(sqlCmd);

      return ((int)sqlCmd.Parameters["@ReturnValue"].Value);
    }
/// <summary>
/// 删除指定时间项ID的时间项
/// </summary>
/// <param name="timeEntryId"></param>
/// <returns></returns>
    public override bool DeleteTimeEntry(int timeEntryId) {
      if (timeEntryId <= DefaultValues.GetTimeEntryIdMinValue())
        throw (new ArgumentOutOfRangeException("timeEntryId"));

      SqlCommand sqlCmd = new SqlCommand();

      AddParamToSQLCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
      AddParamToSQLCmd(sqlCmd, "@TimeEntryIdToDelete", SqlDbType.Int, 0, ParameterDirection.Input, timeEntryId);

      SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TIMEENTRY_DELETE);
      ExecuteScalarCmd(sqlCmd);

      int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;

      return (returnValue == 0 ? true : false);

    }
      /// <summary>
      /// 获取所有的时间项
      /// </summary>
      /// <returns></returns>
    public override List<TimeEntry> GetAllTimeEntries() {

      SqlCommand sqlCmd = new SqlCommand();

      SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TIMEENTRY_GETALLTIMEENTRIES);

      List<TimeEntry> timeEntryList = new List<TimeEntry>();

      TExecuteReaderCmd<TimeEntry>(sqlCmd, TGenerateTimeEntryListFromReader<TimeEntry>, ref timeEntryList);

      return timeEntryList;
    }
      /// <summary>
      /// 获取指定项目ID和用户名的时间项
      /// </summary>
      /// <param name="projectId"></param>
      /// <param name="userName"></param>
      /// <returns></returns>
    public override List<TimeEntry> GetTimeEntries(int projectId, string userName) {
      if (projectId <= DefaultValues.GetTimeEntryIdMinValue())
        throw (new ArgumentOutOfRangeException("projectId"));

      SqlCommand sqlCmd = new SqlCommand();

      AddParamToSQLCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
      AddParamToSQLCmd(sqlCmd, "@ProjectId", SqlDbType.Int, 0, ParameterDirection.Input, projectId);
      AddParamToSQLCmd(sqlCmd, "@TimeEntryUserName", SqlDbType.NText, 255, ParameterDirection.Input, userName);


      SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TIMEENTRY_GETALLTIMEENTRIESBYPROJECTID_USER);

      List<TimeEntry> timeEntryList = new List<TimeEntry>();

      TExecuteReaderCmd<TimeEntry>(sqlCmd, TGenerateTimeEntryListFromReader<TimeEntry>, ref timeEntryList);

      return timeEntryList;
    }
   /// <summary>
   /// 获取指定时间项ID的时间项
   /// </summary>
   /// <param name="timeEntryId"></param>
   /// <returns></returns>
    public override TimeEntry GetTimeEntryById(int timeEntryId) {
      if (timeEntryId <= 0)
        throw (new ArgumentOutOfRangeException("timeEntryId"));

      SqlCommand sqlCmd = new SqlCommand();

      AddParamToSQLCmd(sqlCmd, "@TimeEntryId", SqlDbType.Int, 0, ParameterDirection.Input, timeEntryId);

      SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TIMEENTRY_GETTIMEENTRYBYID);

      List<TimeEntry> timeEntryList = new List<TimeEntry>();


      TExecuteReaderCmd<TimeEntry>(sqlCmd, TGenerateTimeEntryListFromReader<TimeEntry>, ref timeEntryList);

      if (timeEntryList.Count > 0)
        return timeEntryList[0];
      else
        return null;

    }
      /// <summary>
      /// 根据指定的起始日期和用户名获取时间项
      /// </summary>
      /// <param name="userName"></param>
      /// <param name="startingDate"></param>
      /// <param name="endDate"></param>
      /// <returns></returns>
    public override List<TimeEntry> GetTimeEntriesByUserNameAndDates(string userName,
                                                                      DateTime startingDate, DateTime endDate) {
      SqlCommand sqlCmd = new SqlCommand();

      AddParamToSQLCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
      AddParamToSQLCmd(sqlCmd, "@EndDate", SqlDbType.DateTime, 0, ParameterDirection.Input, endDate);
      AddParamToSQLCmd(sqlCmd, "@StartingDate", SqlDbType.DateTime, 0, ParameterDirection.Input, startingDate);
      AddParamToSQLCmd(sqlCmd, "@TimeEntryUserName", SqlDbType.NText, 255, ParameterDirection.Input, userName);


      SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TIMEENTRY_GETALLTIMEENTRIESBYUSERNAMEANDDATE);

      List<TimeEntry> timeEntryList = new List<TimeEntry>();

      TExecuteReaderCmd<TimeEntry>(sqlCmd, TGenerateTimeEntryListFromReader<TimeEntry>, ref timeEntryList);

      return timeEntryList;

    }
      /// <summary>
      /// 更新时间项
      /// </summary>
      /// <param name="timeEntry"></param>