sqldataaccesslayer.cs

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

using System;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using System.Collections.Generic;
using System.Collections.Specialized;
using System.Web.UI.WebControls;
using ASPNET.StarterKit.BusinessLogicLayer;

namespace ASPNET.StarterKit.DataAccessLayer {
  public class SQLDataAccess : DataAccess {
    /*** 泛型委托 ***/

    private delegate void TGenerateListFromReader<T>(SqlDataReader returnData, ref List<T> tempList);

    /*****************************  基本类实现 *****************************/

    /*** 项目分类***/
    //创建新项目分类存储过程
    private const string SP_CATEGORY_CREATE = "aspnet_starterkits_CreateNewCategory";
    //删除项目分类存储过程
    private const string SP_CATEGORY_DELETE = "aspnet_starterkits_DeleteCategory";
    //获取所有项目分类列表的存储过程
    private const string SP_CATEGORY_GETALLCATEGORIES = "aspnet_starterkits_GetAllCategories";
    //根据项目ID获取分类列表的存储过程
    private const string SP_CATEGORY_GETCATEGORYBYPROJECTID = "aspnet_starterkits_GetCategoriesByProjectId";
    //根据项目分类ID获取项目分类列表的存储过程
    private const string SP_CATEGORY_GETCATEGORYBYID = "aspnet_starterkits_GetCategoryById";
    //根据分类名称和项目ID号获取分类列表的存储过程
    private const string SP_CATEGORY_GETCATEGORYBYNAMEANDPROJECT = "aspnet_starterkits_GetCategoryByNameAndProjectId";
    //更新指定的项目分类
    private const string SP_CATEGORY_UPDATE = "aspnet_starterkits_UpdateCategories";
    /// <summary>
    /// 创建新的分类
    /// </summary>
    /// <param name="newCategory">新的分类名称</param>
    /// <returns></returns>
    public override int CreateNewCategory(Category newCategory) {
      if (newCategory == null)
        throw (new ArgumentNullException("newCategory"));
      SqlCommand sqlCmd = new SqlCommand();
      //调用Sql帮助方法的AddParamToSQLCmd为提定的SqlCommand对象添加参数。
      AddParamToSQLCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
      AddParamToSQLCmd(sqlCmd, "@CategoryAbbreviation", SqlDbType.NText, 255, ParameterDirection.Input, newCategory.Abbreviation);
      AddParamToSQLCmd(sqlCmd, "@CategoryEstimateDuration", SqlDbType.Decimal, 0, ParameterDirection.Input, newCategory.EstimateDuration);
      AddParamToSQLCmd(sqlCmd, "@CategoryName", SqlDbType.NText, 255, ParameterDirection.Input, newCategory.Name);
      AddParamToSQLCmd(sqlCmd, "@ProjectId", SqlDbType.Int, 0, ParameterDirection.Input, newCategory.ProjectId);
     //调用Sql帮助方法的SetCommandType方法设置SqlCommand的类型和所要执行的SQL语句或存储过程。
      SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_CATEGORY_CREATE);
        //调用Sql帮助方法的ExecuteScalarCmd执行Sqlcommand中的ExecuteScalar方法。
      ExecuteScalarCmd(sqlCmd);
      //获取返回的值信息。
      return ((int)sqlCmd.Parameters["@ReturnValue"].Value);
    }
    /// <summary>
    /// 删除指定分类ID的项目分类
    /// </summary>
    /// <param name="categoryId"></param>
    /// <returns></returns>
    public override bool DeleteCategory(int categoryId) {
      if (categoryId <= DefaultValues.GetCategoryIdMinValue())
        throw (new ArgumentOutOfRangeException("categoryId"));
      //初始化一个SqlCommand对象
      SqlCommand sqlCmd = new SqlCommand();
      //为SqlCommand对象添加参数列表
      AddParamToSQLCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
      AddParamToSQLCmd(sqlCmd, "@CategoryIdToDelete", SqlDbType.Int, 0, ParameterDirection.Input, categoryId);
      //为SqlCommand对象设置CommandType和CommandText
      SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_CATEGORY_DELETE);
      ExecuteScalarCmd(sqlCmd);
      //获取存储过程返回值
      int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;      
      return (returnValue == 0 ? true : false);
    }
     /// <summary>
    /// 获取所有的项目分类
     /// </summary>
     /// <returns></returns>
    public override List<Category> GetAllCategories() {
      SqlCommand sqlCmd = new SqlCommand();
      //为SqlCommand对象设置CommandType和CommandText
      SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_CATEGORY_GETALLCATEGORIES);
      //新建一个Category类型的泛型列表
      List<Category> categoryList = new List<Category>();
      //调用TExecuteReaderCmd<T>泛型方法，传递TGenerateCategoryListFromReader<Category>泛型方法作为委托类型参数，categoryList用于返回值
      TExecuteReaderCmd<Category>(sqlCmd, TGenerateCategoryListFromReader<Category>, ref categoryList);
      //返回泛型列表类
      return categoryList;
    }
    /// <summary>
    /// 根据指定的ID获取项目分类列表
    /// </summary>
    /// <param name="Id"></param>
    /// <returns></returns>
    public override Category GetCategoryByCategoryId(int Id) {
      if (Id <= DefaultValues.GetCategoryIdMinValue())
        throw (new ArgumentOutOfRangeException("Id"));
      //初始化一个SqlCommand对象
      SqlCommand sqlCmd = new SqlCommand();
      //为SqlCommand对象添加参数列表
      AddParamToSQLCmd(sqlCmd, "@CategoryId", SqlDbType.Int, 0, ParameterDirection.Input, Id);
      //为SqlCommand对象设置CommandType和CommandText
      SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_CATEGORY_GETCATEGORYBYID);
      //新建一个Category类型的泛型列表
      List<Category> categoryList = new List<Category>();
      //调用TExecuteReaderCmd<T>泛型方法，传递TGenerateCategoryListFromReader<Category>泛型方法作为委托类型参数，categoryList用于返回值
      TExecuteReaderCmd<Category>(sqlCmd, TGenerateCategoryListFromReader<Category>, ref categoryList);
      //如果列表项总数大于0.
      if (categoryList.Count > 0)
          //返回第一个列表项。
        return categoryList[0];
      else
        return null;
    }
    /// <summary>
    /// 根据分类名和项目ID获取项目分类
    /// </summary>
    /// <param name="categoryName"></param>
    /// <param name="projectId"></param>
    /// <returns></returns>
    public override Category GetCategoryByCategoryNameandProjectId(string categoryName, int projectId) {
      if (projectId <= DefaultValues.GetProjectIdMinValue())
        throw (new ArgumentOutOfRangeException("Id"));

      //初始化一个SqlCommand对象
      SqlCommand sqlCmd = new SqlCommand();
      //为SqlCommand对象添加参数列表
      AddParamToSQLCmd(sqlCmd, "@ProjectId", SqlDbType.Int, 0, ParameterDirection.Input, projectId);
      AddParamToSQLCmd(sqlCmd, "@CategoryName", SqlDbType.NText, 255, ParameterDirection.Input, categoryName);
      //为SqlCommand对象设置CommandType和CommandText
      SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_CATEGORY_GETCATEGORYBYNAMEANDPROJECT);
      //新建一个Category类型的泛型列表
      List<Category> categoryList = new List<Category>();
      //调用TExecuteReaderCmd<T>泛型方法，传递TGenerateCategoryListFromReader<Category>泛型方法作为委托类型参数，categoryList用于返回值
      TExecuteReaderCmd<Category>(sqlCmd, TGenerateCategoryListFromReader<Category>, ref categoryList);
      //如果列表项总数大于0.
      if (categoryList.Count > 0)
        return categoryList[0];
      else
        return null;

    }


/// <summary>
/// 根据项目ID号获取项目分类
/// </summary>
/// <param name="projectId"></param>
/// <returns></returns>
    public override List<Category> GetCategoriesByProjectId(int projectId) {
      if (projectId <= DefaultValues.GetProjectIdMinValue())
        throw (new ArgumentOutOfRangeException("projectId"));
      //初始化一个SqlCommand对象
      SqlCommand sqlCmd = new SqlCommand();
      //为SqlCommand对象添加参数列表
      AddParamToSQLCmd(sqlCmd, "@ProjectId", SqlDbType.Int, 0, ParameterDirection.Input, projectId);
      //为SqlCommand对象设置CommandType和CommandText
      SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_CATEGORY_GETCATEGORYBYPROJECTID);
      //新建一个Category类型的泛型列表
      List<Category> categoryList = new List<Category>();
      //调用TExecuteReaderCmd<T>泛型方法，传递TGenerateCategoryListFromReader<Category>泛型方法作为委托类型参数，categoryList用于返回值
      TExecuteReaderCmd<Category>(sqlCmd, TGenerateCategoryListFromReader<Category>, ref categoryList);
      return categoryList;
    }

      /// <summary>
      /// 更新一个项目分类
      /// </summary>
      /// <param name="newCategory"></param>
      /// <returns></returns>
    public override bool UpdateCategory(Category newCategory) {
      if (newCategory == null)
        throw (new ArgumentNullException("newCategory"));
        //DefaultValues是一个静态类，定义了多个静态方法指定项目中默认值信息。
      if (newCategory.Id <= DefaultValues.GetCategoryIdMinValue())
        throw (new ArgumentOutOfRangeException("newCategory.Id"));
      //初始化一个SqlCommand对象
      SqlCommand sqlCmd = new SqlCommand();
      //为SqlCommand对象添加参数列表
      AddParamToSQLCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
      AddParamToSQLCmd(sqlCmd, "@CategoryId", SqlDbType.Int, 0, ParameterDirection.Input, newCategory.Id);
      AddParamToSQLCmd(sqlCmd, "@CategoryAbbreviation", SqlDbType.NText, 255, ParameterDirection.Input, newCategory.Abbreviation);
      AddParamToSQLCmd(sqlCmd, "@CategoryEstimateDuration", SqlDbType.Decimal, 0, ParameterDirection.Input, newCategory.EstimateDuration);
      AddParamToSQLCmd(sqlCmd, "@CategoryName", SqlDbType.NText, 255, ParameterDirection.Input, newCategory.Name);
      AddParamToSQLCmd(sqlCmd, "@ProjectId", SqlDbType.Int, 0, ParameterDirection.Input, newCategory.ProjectId);
      //为SqlCommand对象设置CommandType和CommandText
      SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_CATEGORY_UPDATE);
      ExecuteScalarCmd(sqlCmd);
      //判断是否更新成功
      int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
      return (returnValue == 0 ? true : false);
    }


    /*** 项目 ***/
    private const string SP_PROJECT_ADDUSERTOPROJECT = "aspnet_starterkits_AddUserToProject";
    private const string SP_PROJECT_CREATE = "aspnet_starterkits_CreateNewProject";
    private const string SP_PROJECT_DELETE = "aspnet_starterkits_DeleteProject";
    private const string SP_PROJECT_GETALLPROJECTS = "aspnet_starterkits_GetAllProjects";
    private const string SP_PROJECT_GETAPROJECTBYID = "aspnet_starterkits_GetProjectById";
    private const string SP_PROJECT_GETAPROJECTSBYMANAGERUSERNAME = "aspnet_starterkits_GetProjectByManagerUserName";
    private const string SP_PROJECT_GETPROJECTSBYYSERNAME = "aspnet_starterkits_GetProjectByUserName";
    private const string SP_PROJECT_GETPROJECTMEMBERS = "aspnet_starterkits_GetProjectMember";
    private const string SP_PROJECT_REMOVEUSERFROMPROJECT = "aspnet_starterkits_RemoveUserFromProject";
    private const string SP_PROJECT_UPDATE = "aspnet_starterkits_UpdateProject";
    /// <summary>
    /// 为指定的项目ID添加用户
    /// </summary>
    /// <param name="projectId">项目ID</param>
    /// <param name="userName">用户名称</param>
    /// <returns></returns>
    public override bool AddUserToProject(int projectId, string userName) {
        //判断userName参数是否有值
      if (userName == null || userName.Length == 0)
        throw (new ArgumentOutOfRangeException("userName"));
      //判断是否指定了项目ID
      if (projectId <= 0)
        throw (new ArgumentOutOfRangeException("projectId"));
      //声明一个SqlCommand对象
      SqlCommand sqlCmd = new SqlCommand();
      //为SqlCommand对象添加参数列表
      AddParamToSQLCmd(sqlCmd, "@ResultValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
      AddParamToSQLCmd(sqlCmd, "@MemberUserName", SqlDbType.NText, 255, ParameterDirection.Input, userName);
      AddParamToSQLCmd(sqlCmd, "@ProjectId", SqlDbType.Int, 0, ParameterDirection.Input, projectId);
      //为SqlCommand对象设置CommandType和CommandText
      SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_PROJECT_ADDUSERTOPROJECT);
      ExecuteScalarCmd(sqlCmd);
      //根据返回值判断是否执行成功
      int resultValue = (int)sqlCmd.Parameters["@ResultValue"].Value;
      return (resultValue == 0 ? true : false);
    }

      /// <summary>
      /// 创建新项目
      /// </summary>
      /// <param name="newProject"></param>
      /// <returns></returns>
    public override int CreateNewProject(Project newProject) {
      if (newProject == null)
        throw (new ArgumentNullException("newProject"));
      SqlCommand sqlCmd = new SqlCommand();
      AddParamToSQLCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
      AddParamToSQLCmd(sqlCmd, "@ProjectCreatorUserName", SqlDbType.NText, 255, ParameterDirection.Input, newProject.CreatorUserName);
      AddParamToSQLCmd(sqlCmd, "@ProjectCompletionDate", SqlDbType.DateTime, 0, ParameterDirection.Input, newProject.CompletionDate);
      AddParamToSQLCmd(sqlCmd, "@ProjectDescription", SqlDbType.NText, 1000, ParameterDirection.Input, newProject.Description);
      AddParamToSQLCmd(sqlCmd, "@ProjectEstimateDuration", SqlDbType.Decimal, 0, ParameterDirection.Input, newProject.EstimateDuration);
      AddParamToSQLCmd(sqlCmd, "@ProjectManagerUserName", SqlDbType.NText, 255, ParameterDirection.Input, newProject.ManagerUserName);
      AddParamToSQLCmd(sqlCmd, "@ProjectName", SqlDbType.NText, 255, ParameterDirection.Input, newProject.Name);
      SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_PROJECT_CREATE);
      ExecuteScalarCmd(sqlCmd);
      return ((int)sqlCmd.Parameters["@ReturnValue"].Value);
    }
    /// <summary>
    /// 删除指定项目ID的项目
    /// </summary>
    /// <param name="projectID"></param>
    /// <returns></returns>
    public override bool DeleteProject(int projectID) {
      if (projectID <= 0)
        throw (new ArgumentOutOfRangeException("projectID"));
      SqlCommand sqlCmd = new SqlCommand();
      AddParamToSQLCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
      AddParamToSQLCmd(sqlCmd, "@ProjectIdToDelete", SqlDbType.Int, 0, ParameterDirection.Input, projectID);
      SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_PROJECT_DELETE);
      ExecuteScalarCmd(sqlCmd);
      int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
      return (returnValue == 0 ? true : false);
    }
    /// <summary>
    /// 获取所有的项目列表
    /// </summary>
    /// <returns></returns>
    public override List<Project> GetAllProjects() {
      SqlCommand sqlCmd = new SqlCommand();