testfunctions.cs

计算机考试管理系统 包括模拟测试

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read)]
    public static float EvalTestDifficulty(int testID)
    {
        int iDif1 = 0, iDif2 = 0, iNum1, iNum2;
        using (SqlConnection conn1 = new SqlConnection())
        {
            conn1.ConnectionString = "context connection=true";
            SqlCommand cmd1 = conn1.CreateCommand();
            conn1.Open();
            cmd1.CommandText = "SELECT SUM([Difficulty]) FROM [SQuestion], [TestItem] WHERE [TestItem].[QuestionType]=0 AND [SQuestion].[Number]=[TestItem].[QuestionNumber] AND [TestItem].[TestID]=" + testID;
            if(cmd1.ExecuteScalar() != null)
                iDif1 = (int)cmd1.ExecuteScalar();
            cmd1.CommandText = "SELECT COUNT(*) FROM [SQuestion], [TestItem] WHERE [TestItem].[QuestionType]=0 AND [SQuestion].[Number]=[TestItem].[QuestionNumber] AND [TestItem].[TestID]=" + testID;
            iNum1 = (int)cmd1.ExecuteScalar();
            cmd1.CommandText = "SELECT SUM([Difficulty]) FROM [MQuestion], [TestItem] WHERE [TestItem].[QuestionType]=1 AND [MQuestion].[Number]=[TestItem].[QuestionNumber] AND [TestItem].[TestID]=" + testID;
            if (cmd1.ExecuteScalar() != null)
                iDif2 = (int)cmd1.ExecuteScalar();
            cmd1.CommandText = "SELECT COUNT(*) FROM [MQuestion], [TestItem] WHERE [TestItem].[QuestionType]=1 AND [MQuestion].[Number]=[TestItem].[QuestionNumber] AND [TestItem].[TestID]=" + testID;
            iNum2 = (int)cmd1.ExecuteScalar();
        }
        return (float)(iDif1 + 2 * iDif2) / (iNum1 + iNum2);
    }

    [Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read)]
    public static int ScoreTestPaper(int testID, int testeeNumber, int score1, int score2)
    {
        int iScore = 0;
        using (SqlConnection conn1 = new SqlConnection())
        {
            conn1.ConnectionString = "context connection=true";
            SqlCommand cmd1 = conn1.CreateCommand();
            conn1.Open();
            cmd1.CommandText = string.Format("SELECT [Answers] FROM [TestPaper] WHERE [TestID]={0} AND [TesteeNumber]={1}", testID, testeeNumber);
            string[] sAnswers = ((string)cmd1.ExecuteScalar()).Split(' ');
            //计算单选题得分
            cmd1.CommandText = string.Format("SELECT [Answer] FROM [SQuestion], [TestItem] WHERE [TestItem].[QuestionType]=0 AND [SQuestion].[Number]=[TestItem].[QuestionNumber] AND [TestItem].[TestID]={0} ORDER BY [TestItem].[Number]", testID);
            SqlDataReader reader1 = cmd1.ExecuteReader();
            int i = 0;
            while (reader1.Read())
            {
                if (reader1[0].ToString() == sAnswers[i++])
                    iScore += score1;
            }
            reader1.Close();
            //计算多选题得分
            cmd1.CommandText = string.Format("SELECT [Answer] FROM [MQuestion], [TestItem] WHERE [TestItem].[QuestionType]=1 AND [MQuestion].[Number]=[TestItem].[QuestionNumber] AND [TestItem].[TestID]={0} ORDER BY [TestItem].[Number]", testID);
            reader1 = cmd1.ExecuteReader();
            while (reader1.Read())
            {
                iScore += score2 * MultiAnswer.Score(MultiAnswer.Parse(reader1[0].ToString()), MultiAnswer.Parse(sAnswers[i++]));
            }
            reader1.Close();
        }
        return iScore;
    }
};