sqldataprovider.cs

来自「BugNET is an issue tracking and project 」· CS 代码 · 共 1,173 行 · 第 1/5 页

        private const string SP_APPLICATIONLOG_GETLOG = "BugNet_ApplicationLog_GetLog";
        private const string SP_APPLICATIONLOG_GETLOGCOUNT = "BugNet_ApplicationLog_GetLogCount";
        private const string SP_APPLICATIONLOG_CLEARLOG = "BugNet_ApplicationLog_ClearLog";

        private const string SP_CUSTOMFIELD_GETCUSTOMFIELDBYID = "BugNet_ProjectCustomField_GetCustomFieldById";
        private const string SP_CUSTOMFIELD_GETCUSTOMFIELDSBYPROJECTID = "BugNet_ProjectCustomField_GetCustomFieldsByProjectId";
        private const string SP_CUSTOMFIELD_GETCUSTOMFIELDSBYISSUEID = "BugNet_ProjectCustomField_GetCustomFieldsByIssueId";
        private const string SP_CUSTOMFIELD_CREATE = "BugNet_ProjectCustomField_CreateNewCustomField";
        private const string SP_CUSTOMFIELD_UPDATE = "BugNet_ProjectCustomField_UpdateCustomField";
        private const string SP_CUSTOMFIELD_DELETE = "BugNet_ProjectCustomField_DeleteCustomField";
        private const string SP_CUSTOMFIELD_SAVECUSTOMFIELDVALUE = "BugNet_ProjectCustomField_SaveCustomFieldValue";

        private const string SP_CUSTOMFIELDSELECTION_CREATE = "BugNet_ProjectCustomFieldSelection_CreateNewCustomFieldSelection";
        private const string SP_CUSTOMFIELDSELECTION_DELETE = "BugNet_ProjectCustomFieldSelection_DeleteCustomFieldSelection";
        private const string SP_CUSTOMFIELDSELECTION_GETCUSTOMFIELDSELECTIONSBYCUSTOMFIELDID = "BugNet_ProjectCustomFieldSelection_GetCustomFieldSelectionsByCustomFieldId";
        private const string SP_CUSTOMFIELDSELECTION_GETCUSTOMFIELDSELECTIONBYID = "BugNet_ProjectCustomFieldSelection_GetCustomFieldSelectionById";
        private const string SP_CUSTOMFIELDSELECTION_UPDATE = "BugNet_ProjectCustomFieldSelection_Update";
        private const string SP_CUSTOMFIELDSELECTION_GETCUSTOMFIELDSELECTION = "BugNet_ProjectCustomFieldSelection_GetCustomFieldSelection";

        private const string SP_REQUIREDFIELDS_GETFIELDLIST = "BugNet_RequiredField_GetRequiredFieldListForIssues";
        private const string SP_ISSUE_BYPROJECTIDANDCUSTOMFIELDVIEW = "BugNet_GetIssuesByProjectIdAndCustomFieldView";

        #endregion

        /*** INSTANCE PROPERTIES ***/

        /// <summary>
        /// Gets a value indicating whether [supports project cloning].
        /// </summary>
        /// <value>
        /// 	<c>true</c> if [supports project cloning]; otherwise, <c>false</c>.
        /// </value>
        public override bool SupportsProjectCloning
        {
            get { return true; }
        }

        /// <summary>
        /// Creates the new category.
        /// </summary>
        /// <param name="newCategory">The new category.</param>
        /// <returns></returns>
        public override int CreateNewCategory(BugNET.BusinessLogicLayer.Category newCategory)
        {
            if (newCategory == null)
                throw (new ArgumentNullException("newCategory"));

            SqlCommand sqlCmd = new SqlCommand();

            AddParamToSQLCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            AddParamToSQLCmd(sqlCmd, "@ProjectId", SqlDbType.Int, 0, ParameterDirection.Input, newCategory.ProjectId);
            AddParamToSQLCmd(sqlCmd, "@CategoryName", SqlDbType.NText, 255, ParameterDirection.Input, newCategory.Name);
            AddParamToSQLCmd(sqlCmd, "@ParentCategoryId", SqlDbType.Int, 0, ParameterDirection.Input, newCategory.ParentCategoryId);

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

            return ((int)sqlCmd.Parameters["@ReturnValue"].Value);
        }

        /// <summary>
        /// Deletes the category.
        /// </summary>
        /// <param name="categoryId">The category id.</param>
        /// <returns></returns>
        public override bool DeleteCategory(int categoryId)
        {
            // Validate Parameters
            if (categoryId <= Globals.NewId)
                throw (new ArgumentOutOfRangeException("categoryId"));

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();

            AddParamToSQLCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 4, ParameterDirection.ReturnValue, null);
            AddParamToSQLCmd(sqlCmd, "@CategoryId", SqlDbType.Int, 4, ParameterDirection.Input, categoryId);

            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_CATEGORY_DELETE);
            ExecuteScalarCmd(sqlCmd);
            int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
            return (returnValue == 0 ? true : false);
        }

        /// <summary>
        /// Updates the category.
        /// </summary>
        /// <param name="categoryToUpdate">The category to update.</param>
        /// <returns></returns>
        public override bool UpdateCategory(BugNET.BusinessLogicLayer.Category categoryToUpdate)
        {
            SqlCommand sqlCmd = new SqlCommand();

            AddParamToSQLCmd(sqlCmd, "@ReturnValue", SqlDbType.Int, 0, ParameterDirection.ReturnValue, null);
            AddParamToSQLCmd(sqlCmd, "@CategoryId", SqlDbType.Int, 0, ParameterDirection.Input, categoryToUpdate.Id);
            AddParamToSQLCmd(sqlCmd, "@ProjectId", SqlDbType.Int, 0, ParameterDirection.Input, categoryToUpdate.ProjectId);
            AddParamToSQLCmd(sqlCmd, "@CategoryName", SqlDbType.NText, 255, ParameterDirection.Input, categoryToUpdate.Name);
            AddParamToSQLCmd(sqlCmd, "@ParentCategoryId", SqlDbType.Int, 0, ParameterDirection.Input, categoryToUpdate.ParentCategoryId);

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

            int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
            return (returnValue == 0 ? true : false);
        }

        /// <summary>
        /// Gets the categories by project id.
        /// </summary>
        /// <param name="projectId">The project id.</param>
        /// <returns></returns>
        public override System.Collections.Generic.List<BugNET.BusinessLogicLayer.Category> GetCategoriesByProjectId(int projectId)
        {
            // Validate Parameters
            if (projectId <= Globals.NewId)
                throw (new ArgumentOutOfRangeException("projectId"));

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParamToSQLCmd(sqlCmd, "@ProjectId", SqlDbType.Int, 0, ParameterDirection.Input, projectId);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_CATEGORY_GETCATEGORIESBYPROJECTID);
            List<Category> categoryList = new List<Category>();
            TExecuteReaderCmd<Category>(sqlCmd, TGenerateCategoryListFromReader<Category>, ref categoryList);

            return categoryList;
        }

        /// <summary>
        /// Gets the root categories by project id.
        /// </summary>
        /// <param name="projectId">The project id.</param>
        /// <returns></returns>
        public override System.Collections.Generic.List<BugNET.BusinessLogicLayer.Category> GetRootCategoriesByProjectId(int projectId)
        {
            // Validate Parameters
            if (projectId <= Globals.NewId)
                throw (new ArgumentOutOfRangeException("projectId"));

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParamToSQLCmd(sqlCmd, "@ProjectId", SqlDbType.Int, 0, ParameterDirection.Input, projectId);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_CATEGORY_GETROOTCATEGORIESBYPROJECTID);
            List<Category> categoryList = new List<Category>();
            TExecuteReaderCmd<Category>(sqlCmd, TGenerateCategoryListFromReader<Category>, ref categoryList);

            return categoryList;
        }

        /// <summary>
        /// Gets the child categories by category id.
        /// </summary>
        /// <param name="categoryId">The category id.</param>
        /// <returns></returns>
        public override System.Collections.Generic.List<BugNET.BusinessLogicLayer.Category> GetChildCategoriesByCategoryId(int categoryId)
        {
            // Validate Parameters
            if (categoryId <= Globals.NewId)
                throw (new ArgumentOutOfRangeException("categoryId"));

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParamToSQLCmd(sqlCmd, "@CategoryId", SqlDbType.Int, 0, ParameterDirection.Input, categoryId);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_CATEGORY_GETCHILDCATEGORIESBYCATEGORYID);
            List<Category> categoryList = new List<Category>();
            TExecuteReaderCmd<Category>(sqlCmd, TGenerateCategoryListFromReader<Category>, ref categoryList);

            return categoryList;
        }

        /// <summary>
        /// Gets the category by id.
        /// </summary>
        /// <param name="categoryId">The category id.</param>
        /// <returns></returns>
        public override Category GetCategoryById(int categoryId)
        {
            // Validate Parameters
            if (categoryId <= Globals.NewId)
                throw (new ArgumentOutOfRangeException("categoryId"));

            // Execute SQL Command
            SqlCommand sqlCmd = new SqlCommand();
            AddParamToSQLCmd(sqlCmd, "@CategoryId", SqlDbType.Int, 0, ParameterDirection.Input, categoryId);
            SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_CATEGORY_GETCATEGORYBYID);
            List<Category> categoryList = new List<Category>();
            TExecuteReaderCmd<Category>(sqlCmd, TGenerateCategoryListFromReader<Category>, ref categoryList);

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

        /// <summary>
        /// Deletes the issue.
        /// </summary>
        /// <param name="issueId">The issue id.</param>
        /// <returns></returns>
        public override bool DeleteIssue(int issueId)
        {
            if (issueId <= Globals.NewId)
                throw (new ArgumentOutOfRangeException("ssueId"));
            try
            {
                // Execute SQL Command
                SqlCommand sqlCmd = new SqlCommand();

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

                SetCommandType(sqlCmd, CommandType.StoredProcedure, SP_ISSUE_DELETE);
                ExecuteScalarCmd(sqlCmd);
                int returnValue = (int)sqlCmd.Parameters["@ReturnValue"].Value;
                return (returnValue == 0 ? true : false);
            }
            catch (Exception ex)
            {
                throw ProcessException(ex);
            }
        }

        /// <summary>
        /// Gets the issues by project id.
        /// </summary>
        /// <param name="projectId">The project id.</param>
        /// <returns></returns>
        public override System.Collections.Generic.List<BugNET.BusinessLogicLayer.Issue> GetIssuesByProjectId(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_ISSUE_GETISSUESBYPROJECTID);