achievement.cs

学生成绩管理系统

using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Sql;
using System.Data.Common;
using TeachHelper.DataAccessLayer;

namespace TeachHelper.BusinessLogicLayer
{
    /// <summary>
    /// Summary description for Achievement
    /// </summary>
    public class Achievement
    {
        public Achievement()
        {
            //
            // TODO: Add constructor logic here
            //
        }

        public static bool Add(object userId, int point, int examId)
        {
            Database db = DatabaseFactory.CreateDatabase();

            string sqlCommand = "usp_InsertTeachHelper_Achievement";
            DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);

            db.AddInParameter(dbCommand, "UserId", DbType.String, userId.ToString());
            db.AddInParameter(dbCommand, "Point", DbType.Int32, point);
            db.AddInParameter(dbCommand, "ExamId", DbType.Int32, examId);

            int result;
            try
            {
                result = db.ExecuteNonQuery(dbCommand);
            }
            catch (Exception)
            {
                throw new Exception("该成绩已存在");
            }

            if (result == 1)
                return true;
            else
                return false;

        }


        public static bool Add(string username, int point, int examId)
        {
            object userId = Membership.GetUser(username).ProviderUserKey;
            return Add(userId, point, examId);
        }

        public static DataSet Get(int examId)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sqlCommand = "usp_SelectTeachHelper_AchievementsByExamId";
            DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);
            db.AddInParameter(dbCommand, "ExamId", DbType.Int32, examId);
            return db.ExecuteDataSet(dbCommand);
        }

        public static DataSet GetAchievemnet(string username)
        {
            object userId = Membership.GetUser(username).ProviderUserKey;
            return GetAchievemnet(userId);
        }        

        public static DataSet GetAllAchievemnet()
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sqlCommand = "usp_SelectTeachHelper_Achievements";
            DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);
            return db.ExecuteDataSet(dbCommand);
        }

        public static DataSet GetAchievemnetId(int id)
        {
            if (id == 0)
            {
                return GetMyAchievemnet();
            }

            Database db = DatabaseFactory.CreateDatabase();
            string sqlCommand = "usp_SelectTeachHelper_AchievementsById";
            DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);
            db.AddInParameter(dbCommand, "Id", DbType.Int32, id);
            return db.ExecuteDataSet(dbCommand);
        }

        public static DataSet GetMyAchievemnet()
        {
            object userId = Membership.GetUser().ProviderUserKey;
            return GetAchievemnet(userId);
        }

        public static DataSet GetAchievemnet(object userId)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sqlCommand = "usp_SelectTeachHelper_AchievementsByUserId";
            DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);
            db.AddInParameter(dbCommand, "UserId", DbType.String, userId.ToString());
            return db.ExecuteDataSet(dbCommand);
        }

        public static DataSet GetAchievemnetByUserIdAndSubjectId(object userId, int subjectId)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sqlCommand = "usp_SelectTeachHelper_AchievementsByUserIdAndSubjectId";
            DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);
            db.AddInParameter(dbCommand, "UserId", DbType.String, userId.ToString());
            db.AddInParameter(dbCommand, "SubjectId", DbType.Int32, subjectId);
            return db.ExecuteDataSet(dbCommand);
        }

        public static DataSet GetAchievemnetByUserNameAndSubjectId(string username, int subjectId)
        {
            object userId = Membership.GetUser(username).ProviderUserKey;
            return GetAchievemnetByUserIdAndSubjectId(userId, subjectId);
        }

        public static DataSet GetMyAchievemnetBySubjectId(int subjectId)
        {
            object userId = Membership.GetUser().ProviderUserKey;
            return GetAchievemnetByUserIdAndSubjectId(userId, subjectId);
        }

        public static void UpdatePoint(int id, int point)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sqlCommand = "usp_UpdateTeachHelper_AchievementForPoint";
            DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);
            db.AddInParameter(dbCommand, "Id", DbType.Int32, id);
            db.AddInParameter(dbCommand, "Point", DbType.Int32, point);
            db.ExecuteScalar(dbCommand);
        }

        /// <summary>
        /// 获取低于指定分数的人数
        /// </summary>
        /// <param name="examId"></param>
        /// <param name="point"></param>
        public static int GetNumberLowerPoint(int examId, int point)
        {
            string sqlCommand = string.Format("SELECT COUNT(*) FROM TeachHelper_Achievement WHERE (ExamId = {0}) AND (Point < {1})", examId,point);
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(sqlCommand);
            return (int)db.ExecuteScalar(dbCommand);
        }

        /// <summary>
        /// 获取高于指定分数的人数
        /// </summary>
        /// <param name="examId"></param>
        /// <param name="point"></param>
        public static int GetNumberHigherPoint(int examId, int point)
        {
            string sqlCommand = string.Format("SELECT COUNT(*) FROM TeachHelper_Achievement WHERE (ExamId = {0}) AND (Point >= {1})", examId, point);
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(sqlCommand);
            return (int)db.ExecuteScalar(dbCommand);
        }

        public static int GetPensonCount(int examId)
        {
            string sqlCommand = string.Format("SELECT COUNT(*) FROM TeachHelper_Achievement WHERE (ExamId = {0})", examId);
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(sqlCommand);
            return (int)db.ExecuteScalar(dbCommand);
        }

        public static int GetMinPoint(int examId)
        {
            string sqlCommand = string.Format("SELECT MIN(Point) FROM TeachHelper_Achievement WHERE (ExamId = {0})", examId);
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(sqlCommand);
            return (int)db.ExecuteScalar(dbCommand);
        }

        public static int GetMaxPoint(int examId)
        {
            string sqlCommand = string.Format("SELECT MAX(Point) FROM TeachHelper_Achievement WHERE (ExamId = {0})", examId);
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(sqlCommand);
            return (int)db.ExecuteScalar(dbCommand);
        }

        public static int GetAvgPoint(int examId)
        {
            string sqlCommand = string.Format("SELECT AVG(Point) FROM TeachHelper_Achievement WHERE (ExamId = {0})", examId);
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(sqlCommand);
            return (int)db.ExecuteScalar(dbCommand);
        }
    }
}