testprocedures.cs

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

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

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void GetQuestionInfo()
    {
        SqlMetaData[] mDatas = new SqlMetaData[] { new SqlMetaData("sQuestions1", SqlDbType.Int), new SqlMetaData("sQuestions2", SqlDbType.Int), new SqlMetaData("sQuestions3", SqlDbType.Int), new SqlMetaData("mQuestions1", SqlDbType.Int), new SqlMetaData("mQuestions2", SqlDbType.Int), new SqlMetaData("mQuestions3", SqlDbType.Int) };
        SqlDataRecord record1 = new SqlDataRecord(mDatas);
        using (SqlConnection conn1 = new SqlConnection())
        {
            conn1.ConnectionString = "context connection=true";
            SqlCommand cmd1 = conn1.CreateCommand();
            conn1.Open();
            for (int i = 1; i <= 3; i++)
            {
                cmd1.CommandText = "SELECT COUNT(*) FROM [SQuestion] WHERE [Difficulty] =" + i.ToString();
                record1.SetValue(i - 1, cmd1.ExecuteScalar());
                cmd1.CommandText = "SELECT COUNT(*) FROM [MQuestion] WHERE [Difficulty] =" + i.ToString();
                record1.SetValue(i + 2, cmd1.ExecuteScalar());
            }
            SqlContext.Pipe.SendResultsStart(record1);
            SqlContext.Pipe.SendResultsRow(record1);
            SqlContext.Pipe.SendResultsEnd();
        }
    }

    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void GetTestInfo()
    {
        DataTable table1 = new DataTable();
        using (SqlConnection conn1 = new SqlConnection())
        {
            conn1.ConnectionString = "context connection=true";
            SqlCommand cmd1 = conn1.CreateCommand();
            conn1.Open();
            cmd1.CommandText = "SELECT [Test].[ID], [Name], [CreateTime], COUNT([TestItem].[ID]) FROM [Test], [TestItem] WHERE [TestItem].[TestID]=[Test].[ID] GROUP BY [Test].[ID], [Name], [CreateTime] ORDER BY [CreateTime]";
            table1.Load(cmd1.ExecuteReader());
        }
        SqlMetaData[] mDatas = new SqlMetaData[] { new SqlMetaData("ID", SqlDbType.Int), new SqlMetaData("Information", SqlDbType.NVarChar, 1024) };
        SqlDataRecord record1 = new SqlDataRecord(mDatas);
        SqlContext.Pipe.SendResultsStart(record1);
        int id;
        float fDif;
        string sInfo;
        for (int i = 0; i < table1.Rows.Count; i++)
        {
            id = (int)(table1.Rows[i][0]);
            fDif = UserDefinedFunctions.EvalTestDifficulty(id);
            sInfo = string.Format("{0} 创建于{1} 题数{2} 难度{3}", table1.Rows[i][1], table1.Rows[i][2], table1.Rows[i][3], fDif);
            record1.SetValues(id, sInfo);
            SqlContext.Pipe.SendResultsRow(record1);
        }
        SqlContext.Pipe.SendResultsEnd();
    }

    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void GenQuestion()
    {
        Random rand1 = new Random();
        int iType = rand1.Next(0, 2);
        using (SqlConnection conn1 = new SqlConnection())
        {
            conn1.ConnectionString = "context connection=true";
            SqlCommand cmd1 = conn1.CreateCommand();
            if (iType == 0)
                cmd1.CommandText = "SELECT TOP 1 [Question], [ChoiceA], [ChoiceB], [ChoiceC], [ChoiceD], [Answer], 0 AS [Type] FROM [SQuestion] ORDER BY NEWID()";
            else
                cmd1.CommandText = "SELECT TOP 1 [Question], [ChoiceA], [ChoiceB], [ChoiceC], [ChoiceD], [Answer], 1 AS [Type] FROM [MQuestion] ORDER BY NEWID()";
            conn1.Open();
            SqlContext.Pipe.ExecuteAndSend(cmd1);
        }
    }

    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void GenTest(string name, int sQuestions1, int sQuestions2, int sQuestions3, int mQuestions1, int mQuestions2, int mQuestions3, out int testID)
    {
        using (SqlConnection conn1 = new SqlConnection())
        {
            conn1.ConnectionString = "context connection=true";
            SqlCommand cmd1 = conn1.CreateCommand();
            conn1.Open();
            //生成单选题
            DataTable table1 = new DataTable();
            cmd1.CommandText = string.Format("SELECT TOP {0} [Number] FROM [SQuestion] WHERE [Difficulty] = 1 ORDER BY NEWID()", sQuestions1);
            table1.Load(cmd1.ExecuteReader());
            cmd1.CommandText = string.Format("SELECT TOP {0} [Number] FROM [SQuestion] WHERE [Difficulty] = 2 ORDER BY NEWID()", sQuestions2);
            table1.Load(cmd1.ExecuteReader());
            cmd1.CommandText = string.Format("SELECT TOP {0} [Number] FROM [SQuestion] WHERE [Difficulty] = 3 ORDER BY NEWID()", sQuestions3);
            table1.Load(cmd1.ExecuteReader());
            //生成多选题
            DataTable table2 = new DataTable();
            cmd1.CommandText = string.Format("SELECT TOP {0} [Number] FROM [MQuestion] WHERE [Difficulty] = 1 ORDER BY NEWID()", mQuestions1);
            table2.Load(cmd1.ExecuteReader());
            cmd1.CommandText = string.Format("SELECT TOP {0} [Number] FROM [MQuestion] WHERE [Difficulty] = 2 ORDER BY NEWID()", mQuestions2);
            table2.Load(cmd1.ExecuteReader());
            cmd1.CommandText = string.Format("SELECT TOP {0} [Number] FROM [MQuestion] WHERE [Difficulty] = 3 ORDER BY NEWID()", mQuestions3);
            table2.Load(cmd1.ExecuteReader());
            //将生成的测试写入数据库
            cmd1.CommandText = string.Format("INSERT INTO [Test] ([Name], [CreateTime]) VALUES ('{0}', '{1}'); SELECT @@IDENTITY", name, DateTime.Now);
            testID = int.Parse(cmd1.ExecuteScalar().ToString());
            int i, j;
            for (i = 0; i < table1.Rows.Count; i++)
            {
                cmd1.CommandText = string.Format("INSERT INTO [TestItem] ([TestID], [Number], [QuestionType], [QuestionNumber]) VALUES ({0}, {1}, 0, {2})", testID, i + 1, table1.Rows[i][0]);
                cmd1.ExecuteNonQuery();
            }
            for (j = 0; j < table2.Rows.Count; j++)
            {
                cmd1.CommandText = string.Format("INSERT INTO [TestItem] ([TestID], [Number], [QuestionType], [QuestionNumber]) VALUES ({0}, {1}, 1, {2})", testID, i + j + 1, table2.Rows[j][0]);
                cmd1.ExecuteNonQuery();
            }
        }
    }

    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void GetTest(int testID)
    {
        SqlMetaData[] mDatas = new SqlMetaData[] { new SqlMetaData("Question", SqlDbType.NVarChar, 2048), new SqlMetaData("QuestionType", SqlDbType.Int), new SqlMetaData("ChoiceA", SqlDbType.NVarChar, 256), new SqlMetaData("ChoiceB", SqlDbType.NVarChar, 256), new SqlMetaData("ChoiceC", SqlDbType.NVarChar, 256), new SqlMetaData("ChoiceD", SqlDbType.NVarChar, 256) };
        SqlDataRecord record1 = new SqlDataRecord(mDatas);
        using (SqlConnection conn1 = new SqlConnection())
        {
            conn1.ConnectionString = "context connection=true";
            SqlCommand cmd1 = conn1.CreateCommand();
            conn1.Open();
            cmd1.CommandText = string.Format("SELECT [Question], [ChoiceA], [ChoiceB], [ChoiceC], [ChoiceD] 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();
            SqlContext.Pipe.SendResultsStart(record1);
            while (reader1.Read())
            {
                record1.SetValues(reader1[0], 0, reader1[1], reader1[2], reader1[3], reader1[4]);
                SqlContext.Pipe.SendResultsRow(record1);
            }
            reader1.Close();
            cmd1.CommandText = string.Format("SELECT [Question], [ChoiceA], [ChoiceB], [ChoiceC], [ChoiceD] 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())
            {
                record1.SetValues(reader1[0], 1, reader1[1], reader1[2], reader1[3], reader1[4]);
                SqlContext.Pipe.SendResultsRow(record1);
            }
            reader1.Close();
            SqlContext.Pipe.SendResultsEnd();
        }
    }

    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void OutputScore(int testID, string testeeNumber, int score1, int score2, out int score)
    {
        SqlMetaData[] mDatas = new SqlMetaData[] { new SqlMetaData("Number", SqlDbType.Int), new SqlMetaData("StandardAnswer", SqlDbType.NVarChar, 4), new SqlMetaData("Answer", SqlDbType.NVarChar, 4), new SqlMetaData("Score", SqlDbType.Int) };
        SqlDataRecord record1 = new SqlDataRecord(mDatas);
        score = 0;
        using (SqlConnection conn1 = new SqlConnection())
        {
            conn1.ConnectionString = "context connection=true";
            SqlCommand cmd1 = conn1.CreateCommand();
            cmd1.CommandText = string.Format("SELECT [Answers] FROM [TestPaper] WHERE [TestID]={0} AND [TesteeNumber]={1}", testID, testeeNumber);
            conn1.Open();
            object obj1 = cmd1.ExecuteScalar();
            if(obj1 == null)
                return;
            string[] sAnswers = ((string)obj1).Split(' ');
            SqlContext.Pipe.SendResultsStart(record1);
            //输出单选题得分
            cmd1.CommandText = string.Format("SELECT [TestItem].[Number], [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[1].ToString() == sAnswers[i])
                {
                    record1.SetValues((int)reader1[0], ((char)(64 + int.Parse(reader1[1].ToString()))).ToString(), ((char)(64 + int.Parse(sAnswers[i]))).ToString(), score1);
                    score += score1;
                }
                else
                {
                    record1.SetValues((int)reader1[0], ((char)(64 + int.Parse(reader1[1].ToString()))).ToString(), ((char)(64 + int.Parse(sAnswers[i]))).ToString(), 0);
                }
                SqlContext.Pipe.SendResultsRow(record1);
                i++;
            }
            reader1.Close();
            //输出多选题得分
            cmd1.CommandText = string.Format("SELECT [TestItem].[Number], [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();
            int x;
            MultiAnswer ans0, ans1;
            while (reader1.Read())
            {
                ans0 = MultiAnswer.Parse(reader1[1].ToString());
                ans1 = MultiAnswer.Parse(sAnswers[i]);
                x = score2 * MultiAnswer.Score(ans0, ans1);
                record1.SetValues((int)reader1[0], ans0.ToAnswerString(), ans1.ToAnswerString(), x);
                SqlContext.Pipe.SendResultsRow(record1);
                score += x;
                i++;
            }
            reader1.Close();
            SqlContext.Pipe.SendResultsEnd();
        }
    }

    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void OutputScores(int testID, int score1, int score2)
    {
        SqlMetaData[] mDatas = new SqlMetaData[] { new SqlMetaData("TesteeNumber", SqlDbType.Int), new SqlMetaData("Score", SqlDbType.Int) };
        SqlDataRecord record1 = new SqlDataRecord(mDatas);
        DataTable table1 = new DataTable();
        using (SqlConnection conn1 = new SqlConnection())
        {
            conn1.ConnectionString = "context connection=true";
            SqlCommand cmd1 = conn1.CreateCommand();
            cmd1.CommandText = string.Format("SELECT [TesteeNumber], dbo.ScoreTestPaper({0}, [TesteeNumber], {1}, {2}) AS [Score] FROM [TestPaper] WHERE [TestID]={0} ORDER BY [TesteeNumber]", testID, score1, score2);
            conn1.Open();
            SqlContext.Pipe.ExecuteAndSend(cmd1);
        }
    }
};