sqldataaccesslayer.cs

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

      /// <returns></returns>
    public override bool UpdateTimeEntry(TimeEntry timeEntry) {
      if (timeEntry == null)
        throw (new ArgumentNullException("timeEntry"));


      SqlCommand sqlCmd = new SqlCommand();

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


      AddParamToSQLCmd(sqlCmd, "@TimeEntryId", SqlDbType.Int, 0, ParameterDirection.Input, timeEntry.Id);
      AddParamToSQLCmd(sqlCmd, "@CategoryId", SqlDbType.Int, 0, ParameterDirection.Input, timeEntry.CategoryId);
      AddParamToSQLCmd(sqlCmd, "@TimeEntryDescription", SqlDbType.NText, 1000, ParameterDirection.Input, timeEntry.Description);
      AddParamToSQLCmd(sqlCmd, "@TimeEntryEstimateDuration", SqlDbType.Decimal, 0, ParameterDirection.Input, timeEntry.Duration);
      AddParamToSQLCmd(sqlCmd, "@TimeEntryEnteredDate", SqlDbType.DateTime, 0, ParameterDirection.Input, timeEntry.ReportedDate);
      AddParamToSQLCmd(sqlCmd, "@TimeEntryUserName", SqlDbType.NText, 1000, ParameterDirection.Input, timeEntry.UserName);


      SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TIMEENTRY_UPDATE);
      ExecuteScalarCmd(sqlCmd);
      int resultValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;

      return (resultValue == 0 ? true : false);
    }

    /*** 用户报表***/
    private string SP_TIMEENTRY_GETUSERREPORT = "aspnet_starterkits_GetTimeEntryUserReport";
    private string SP_TIMEENTRY_GETUSERREPORTBYCATEGORY = "aspnet_starterkits_GetTimeEntryUserReportByCategoryId";
      /// <summary>
      /// 获取指定项目ID的用户报表
      /// </summary>
      /// <param name="projectId"></param>
      /// <returns></returns>
    public override List<UserReport> GetUserReportsByProjectId(int projectId) {

      SqlCommand sqlCmd = new SqlCommand();

      AddParamToSQLCmd(sqlCmd, "@ProjectId", SqlDbType.Int, 0, ParameterDirection.Input, projectId);

      SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TIMEENTRY_GETUSERREPORT);

      List<UserReport> userReport = new List<UserReport>();

      TExecuteReaderCmd<UserReport>(sqlCmd, TGenerateUserReportListFromReader<UserReport>, ref userReport);

      return userReport;
    }
      /// <summary>
      /// 获取指定分类ID的用户报表
      /// </summary>
      /// <param name="categoryId"></param>
      /// <returns></returns>
    public override List<UserReport> GetUserReportsByCategoryId(int categoryId) {

      SqlCommand sqlCmd = new SqlCommand();

      AddParamToSQLCmd(sqlCmd, "@CategoryId", SqlDbType.Int, 0, ParameterDirection.Input, categoryId);

      SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TIMEENTRY_GETUSERREPORTBYCATEGORY);

      List<UserReport> userReport = new List<UserReport>();

      TExecuteReaderCmd<UserReport>(sqlCmd, TGenerateUserReportListFromReader<UserReport>, ref userReport);

      return userReport;
    }

    /***  项目顾问所使用的时间长度汇总 ***/
    private string SP_TIMEENTRY_GETUSERREPORTBYUSER = "aspnet_starterkits_GetTimeEntryUserReportByUser";
      /// <summary>
      /// 获通过指定的用户名获取用户报表
      /// </summary>
      /// <param name="userName"></param>
      /// <returns></returns>
    public override List<UserTotalDurationReport> GetUserReportsByUserName(string userName) {

      SqlCommand sqlCmd = new SqlCommand();

      AddParamToSQLCmd(sqlCmd, "@UserName", SqlDbType.NText, 256, ParameterDirection.Input, userName);

      SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_TIMEENTRY_GETUSERREPORTBYUSER);

      List<UserTotalDurationReport> userReport = new List<UserTotalDurationReport>();

      TExecuteReaderCmd<UserTotalDurationReport>(sqlCmd, TGenerateUserReportListFromReader<UserTotalDurationReport>, ref userReport);
      return userReport;
    }


    /*****************************  SQL帮助方法 *****************************/
      /// <summary>
    /// 为SqlCommand对象添加参数
      /// </summary>
      /// <param name="sqlCmd">SqlCommand对象</param>
      /// <param name="paramId">参数ID</param>
      /// <param name="sqlType">Sql类型</param>
      /// <param name="paramSize">参数大小</param>
      /// <param name="paramDirection">参数方向</param>
      /// <param name="paramvalue">参数值</param>
    private void AddParamToSQLCmd(SqlCommand sqlCmd,
                                  string paramId,
                                  SqlDbType sqlType,
                                  int paramSize,
                                  ParameterDirection paramDirection,
                                  object paramvalue) {

      if (sqlCmd == null)
        throw (new ArgumentNullException("sqlCmd"));
      if (paramId == string.Empty)
        throw (new ArgumentOutOfRangeException("paramId"));

      SqlParameter newSqlParam = new SqlParameter();
      newSqlParam.ParameterName = paramId;
      newSqlParam.SqlDbType = sqlType;
      newSqlParam.Direction = paramDirection;

      if (paramSize > 0)
        newSqlParam.Size = paramSize;

      if (paramvalue != null)
        newSqlParam.Value = paramvalue;

      sqlCmd.Parameters.Add(newSqlParam);
    }
    //-------------------------------------------------------------------------
    /// <summary>
    /// 调用指定SQLCommand对象的ExecuteScalar方法，返回指定查询的第一行的第一列
    /// </summary>
    /// <param name="sqlCmd"></param>
    private void ExecuteScalarCmd(SqlCommand sqlCmd) {
      if (ConnectionString == string.Empty)
        throw (new ArgumentOutOfRangeException("ConnectionString"));

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

      using (SqlConnection cn = new SqlConnection(this.ConnectionString)) {
        sqlCmd.Connection = cn;
        cn.Open();
        sqlCmd.ExecuteScalar();
      }
    }
    //-------------------------------------------------------------------------
    /// <summary>
    /// 设置SqlCommand对象的参数    
    /// </summary>
    /// <param name="sqlCmd"></param>
    /// <param name="cmdType"></param>
    /// <param name="cmdText"></param>
    private void SetCommandType(SqlCommand sqlCmd, CommandType cmdType, string cmdText) {
      sqlCmd.CommandType = cmdType;
      sqlCmd.CommandText = cmdText;
    }
    //-------------------------------------------------------------------------
      /// <summary>
      /// 执行指定的SqlCommand.ExecuteReader方法，将将返回的SqlDataReader对象转换成指定类型的泛型列表
      /// </summary>
      /// <typeparam name="T">类型参数</typeparam>
      /// <param name="sqlCmd">SqlCommand对象</param>
      /// <param name="gcfr">委托</param>
      /// <param name="List">泛型列表对象</param>
    private void TExecuteReaderCmd<T>(SqlCommand sqlCmd, TGenerateListFromReader<T> gcfr, ref List<T> List) {
      if (ConnectionString == string.Empty)
        throw (new ArgumentOutOfRangeException("ConnectionString"));

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

      using (SqlConnection cn = new SqlConnection(this.ConnectionString)) {
        sqlCmd.Connection = cn;
        cn.Open();
       //========================================
        gcfr(sqlCmd.ExecuteReader(), ref List);
       //=======================================
      }
    }


    /*****************************  泛型列表帮助方法 *****************************/

      /// <summary>
      /// 从数据库中获取的项目信息，转换成List项目泛型列表对象
      /// </summary>
      /// <typeparam name="T">指定的类型</typeparam>
      /// <param name="returnData">返回数据的SqlDataReader对象</param>
      /// <param name="prjList">列表集合对象</param>
    private void TGenerateProjectListFromReader<T>(SqlDataReader returnData, ref List<Project> prjList) {
        //遍历SqlDataReader对象，将使用SqlDataReader对象中的数据构造相应的项目对象，并添加到泛型列表对象内部
      while (returnData.Read()) {
        decimal actualDuration = 0;
        if (returnData["ProjectActualDuration"] != DBNull.Value)
          actualDuration = Convert.ToDecimal(returnData["ProjectActualDuration"]);

        Project project = new Project(actualDuration, (string)returnData["ProjectCreatorDisplayName"], (DateTime)returnData["ProjectCompletionDate"], (DateTime)returnData["ProjectCreationDate"], (string)returnData["ProjectDescription"],
                                 (Decimal)returnData["ProjectEstimateDuration"], (int)returnData["ProjectId"], (string)returnData["ProjectManagerDisplayName"], (string)returnData["ProjectName"]);
        prjList.Add(project);
      }
    }
    //-------------------------------------------------------------------------
    /// <summary>
    /// 从数据库中获取的项目分类信息，转换成List项目分类泛型列表对象
    /// </summary>
    /// <typeparam name="T">指定的类型</typeparam>
    /// <param name="returnData">返回数据的SqlDataReader对象</param>
    /// <param name="prjList">列表集合对象</param>
    private void TGenerateCategoryListFromReader<T>(SqlDataReader returnData, ref List<Category> categoryList) {
      while (returnData.Read()) {
        decimal actualDuration = 0;
        if (returnData["CategoryActualDuration"] != DBNull.Value)
          actualDuration = Convert.ToDecimal(returnData["CategoryActualDuration"]);

        Category category = new Category((string)returnData["CategoryAbbreviation"], actualDuration, (int)returnData["CategoryId"], (decimal)returnData["CategoryEstimateDuration"], (string)returnData["CategoryName"], (int)returnData["ProjectId"]);
        categoryList.Add(category);
      }
    }
    //-------------------------------------------------------------------------
    /// <summary>
    /// 从数据库中获取的时间项信息，转换成List时间项泛型列表对象
    /// </summary>
    /// <typeparam name="T">指定的类型</typeparam>
    /// <param name="returnData">返回数据的SqlDataReader对象</param>
    /// <param name="prjList">列表集合对象</param>
    private void TGenerateTimeEntryListFromReader<T>(SqlDataReader returnData, ref List<TimeEntry> timeEntryList) {
      while (returnData.Read()) {
        TimeEntry timeEntry = new TimeEntry((string)returnData["TimeEntryCreatorDisplayName"], (int)returnData["CategoryId"], (DateTime)returnData["TimeEntryCreated"], (string)returnData["TimeEntryDescription"],
                                        (Decimal)returnData["TimeEntryDuration"], (int)returnData["TimeEntryId"], (DateTime)returnData["TimeEntryDate"], (string)returnData["TimeEntryUserName"]);
        timeEntryList.Add(timeEntry);
      }
    }
    //-------------------------------------------------------------------------
    /// <summary>
    /// 从数据库中获取用户信息，转换成List用户泛型列表对象
    /// </summary>
    /// <typeparam name="T">指定的类型</typeparam>
    /// <param name="returnData">返回数据的SqlDataReader对象</param>
    /// <param name="prjList">列表集合对象</param>
    private void TGenerateUsertListFromReader<T>(SqlDataReader returnData, ref List<string> userList) {
      while (returnData.Read()) {
        string userName = (string)returnData["UserName"];
        userList.Add(userName);
      }
    }
    //-------------------------------------------------------------------------
    /// <summary>
    /// 从数据库中获取表报列表信息，转换成List报表列表泛型列表对象
    /// </summary>
    /// <typeparam name="T">指定的类型</typeparam>
    /// <param name="returnData">返回数据的SqlDataReader对象</param>
    /// <param name="prjList">列表集合对象</param>
    private void TGenerateUserReportListFromReader<T>(SqlDataReader returnData, ref List<UserReport> userReportList) {
      while (returnData.Read()) {
        UserReport userReport = new UserReport((decimal)returnData["duration"], (int)returnData["CategoryId"], (string)returnData["UserName"]);
        userReportList.Add(userReport);
      }
    }
    //-------------------------------------------------------------------------
    /// <summary>
    /// 从数据库中获取用户列表信息，转换成List用户列表泛型列表对象
    /// </summary>
    /// <typeparam name="T">指定的类型</typeparam>
    /// <param name="returnData">返回数据的SqlDataReader对象</param>
    /// <param name="prjList">列表集合对象</param>
    private void TGenerateUserReportListFromReader<T>(SqlDataReader returnData, ref List<UserTotalDurationReport> userReportList) {
      while (returnData.Read()) {
        decimal totalDuration = 0;
        if (returnData["TotalDuration"] != DBNull.Value)
          totalDuration = (decimal)returnData["TotalDuration"];
        UserTotalDurationReport userReport = new UserTotalDurationReport(totalDuration, (string)returnData["UserName"]);
        userReportList.Add(userReport);
      }
    }
  }
}