baseoperate.cs

程序设计语言上机考试系统

                        sheet.Cells[i + 2, 1] = "" + row["department"].ToString();
                        //专业
                        sheet.Cells[i + 2, 2] = "" + row1["profession"].ToString();
                        //班级
                        sheet.Cells[i + 2, 3] = "" + row2["class"].ToString();
                        i++;
                    }
                }
            }
        }
        #endregion

       
        #region  创建SqlDataReader对象
        /// <summary>
        /// 创建一个SqlDataReader对象
        /// </summary>
        /// <param name="M_str_sqlstr">SQL语句</param>
        /// <returns>返回SqlDataReader对象</returns>
        public SqlDataReader getread(string M_str_sqlstr)
        {
            SqlConnection sqlcon = this.getcon();
            SqlCommand sqlcom = new SqlCommand(M_str_sqlstr, sqlcon);
            if (sqlcon.State == ConnectionState.Closed)
                sqlcon.Open();
            SqlDataReader sqlread = sqlcom.ExecuteReader(CommandBehavior.CloseConnection);
            return sqlread;
        }
        #endregion
        
        #region  取出字符串中的字符
        /// <summary>
        /// 取出字符串中的字符
        /// </summary>
        /// <param name="str">需要分割的字符串</param>
        /// <param name="split">分隔符</param>
        /// <returns>返回处理好的字符数组</returns>
        public string[] Split(string str,string split)
        {
            //存取分割后的字符
            string[] s ={ "" };
            if (str.Length > 1)
            {
                s = str.Split(split.ToCharArray(), str.Length);
            }
            else
                s[0] = str;
            return s;
        }
        #endregion
        
        
        #region  读取Excel文档
        /// <summary> 
        /// 读取Excel文档 
        /// </summary> 
        /// <param name="Path">文件名称</param> 
        /// <returns>返回一个数据集</returns> 
        public DataSet ExcelToDS(string Path)
        {
            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + Path + ";" + "Extended Properties=Excel 8.0;";
            //打开数据链接，得到一个数据集
            OleDbConnection conn = new OleDbConnection(strConn);
            if (conn.State == ConnectionState.Closed)
                conn.Open();
            string strExcel = "";
            OleDbDataAdapter myCommand = null;
            //创建一个 DataSet对象
            DataSet ds = null;
            strExcel = "select * from [sheet1$]";
            myCommand = new OleDbDataAdapter(strExcel, strConn);
            ds = new DataSet();
            myCommand.Fill(ds, "table1");
            if (conn.State == ConnectionState.Open)
                conn.Close();
            return ds;
        }
        #endregion

        #region 随机取出number个不重复的数组元素
        /// <summary>
        /// 随机取出number个不重复的数组元素
        /// </summary>
        /// <param name="number">需要取出的元素的个数</param>
        /// <param name="save">存放试题id的数组</param>
        /// <returns></returns>
        public string[] Rand(int number, string[] save)
        {
            // 声明一个数组，用来储存生成的随机数；
            string[] randnum = new string[number];
            int leng = 0;
            leng = save.Length;

            // 声明变量； // 在0～number之间取得数字
            for (int i = 0; i < number; i++)
            {
                // 注意这里是i+1；
                randnum[i] = (i + 1).ToString();
            }
            // 因为要提取number个，重复执行number次
            for (int k = 0; k < number; k++)
            {
                // 随机抽出save数组位置；
                Random r = new Random();
                string cur = r.Next(0, leng).ToString();
                // 将该位置的元素值反馈给randnum；

                randnum[k] = save[Convert.ToInt32(cur)];
                // 将该位置上的元素从 save数组中删除，避免下次再抽中。
                delete(Convert.ToInt32(cur), save);
                //删除元素后数组长度减一
                leng--;
            }
            return randnum;
        }
        #endregion

        #region 将数组中第i个元素删除
        /// <summary>
        /// 将数组中第i个元素删除
        /// </summary>
        /// <param name="i">要删除的元素下标</param>
        /// <param name="arr">要修改的数组</param>
        public void delete(int i,string[] arr)
        {
            for (int j = i; j < arr.Length - 1; j++)
            {
                arr[j] = arr[j + 1];
            }
            
        }
        #endregion

        #region 取出试题库中试题的id，并存储到数组中
        public string[] getid(string sql,string table)
        {
            //打开数据库链接
            if (cn.State == ConnectionState.Closed)
                cn.Open();
            //取出数据库中的数据表
            SqlDataAdapter adp = new SqlDataAdapter(sql, cn);
            DataSet ds = new DataSet();
            adp.Fill(ds, table);
            DataTable dt = ds.Tables[0];
            string[] id = new string[dt.Rows.Count];
            int i = 0;
            foreach (DataRow row in dt.Rows)
            {
                //存储id
                id[i] = row["id"].ToString();
                i++;
            }
            //关闭数据库连接
            if (cn.State == ConnectionState.Open)
               cn.Close();
            return id;
        }
        #endregion
        
        
        #region 发放试卷
        public void Send()
        {
            //取出shiti表中的信息
            DataSet myds = getds("select distinct idshijuan from shiti order  by idshijuan", "shiti");
            //存储试卷号
            DataTable dt = myds.Tables[0];
            //取出学号
            DataSet dstudent = getds("select s_number from student order by s_number", "student");
            DataTable dtstudent = dstudent.Tables[0];
            string[] snumber = new string[dtstudent.Rows.Count];
            int i = 0;
            foreach (DataRow row in dtstudent.Rows)
            {
                //将学号存入数组中
                snumber[i] = row["s_number"].ToString();
                i++;
            }
            int length = 0;
            while (length < snumber.Length)
            {
                //重复循环直到所有的考生都分配完毕

                foreach (DataRow row1 in dt.Rows)
                {   //试卷号

                    //重复循环直到所有的考生都分配完毕
                    if (length < snumber.Length)
                    {
                        getcom("insert into paper(idshijuan,s_number) values('" + row1["idshijuan"].ToString() + "','" + snumber[length].Trim() +"')");
                            i++;
                    }
                    length++;
                }
            }
            MessageBox.Show("试卷发放完毕！", "提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Information);
        }
        #endregion

        #region 删除datagridview中选中的多条记录
        public void delseldata(DataGridView dataGridView1,string sql)
        {
            if (MessageBox.Show("确实要删除选择信息吗？", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information) == DialogResult.OK)
            {
                //将选中的行的index存在数组selectindex中
                int[] selectindex = new int[dataGridView1.SelectedRows.Count];
                for (int i = 0, j = 0; i < dataGridView1.Rows.Count; i++)
                {
                    if (dataGridView1.Rows[i].Selected)
                        selectindex[j++] = Convert.ToInt32(dataGridView1.Rows[i].Index.ToString());
                }
                //从selectindex数组中index值最大的行开始，依次删除选中的行
                for (int i = dataGridView1.SelectedRows.Count - 1; i >= 0; i--)
                    getcom(sql+ "'" + Convert.ToString(dataGridView1.Rows[selectindex[i]].Cells[0].Value).Trim() + "'");
            }
        }
        #endregion
        
        #region 判断字符串是否超出数据库要求长度
        public bool IfLong(string s, int leng)
        {
            byte[] sarr = Encoding.Default.GetBytes(s);
            int len = sarr.Length;
            if (leng < len)
                return true;
            else
                return false;
        }
        #endregion

        #region 取出某一学号的试题,按照试题类型分别存储在id[][]中
        /// <summary>
        /// 
        /// </summary>
        /// <param name="s_number">要查看的试卷所属学生的学号</param>
        /// <returns>返回试卷中试题的id，id[0],id[1],id[2]分别存放的是选择题、判断题、程序设计题的id</returns>
        public string[][] Extract(string s_number)
        {
            string[][] id = new string[3][];
            //取出学生所对试卷号
            SqlCommand cmd = new SqlCommand("select idshijuan from paper where s_number ='" + s_number + "'", cn);
            if (cn.State == ConnectionState.Closed)
                cn.Open();
            string idshijuan = cmd.ExecuteScalar().ToString();
            DataSet myds = getds("select * from shiti where idshijuan ='" + idshijuan + "'", "shiti");
            foreach (DataRow row in myds.Tables[0].Rows)
            {
                //取出对应试卷的选择题id
                id[0] = Split(row["A"].ToString(), "；");
                //取出对应试卷的判断题id
                id[1] = Split(row["B"].ToString(), "；");
                //取出对应试卷的程序设计题id
                id[2] = Split(row["C"].ToString(), "；");
            }
            if (cn.State == ConnectionState.Open)
                cn.Close();
            return id;
        }
        #endregion


        #region 取出考生答案
        public string[][] ExtractAnswer(string s_number)
        {
            string[][] answer = new string[3][];
            if (cn.State == ConnectionState.Closed)
                cn.Open();
            DataSet myds = getds("select * from answer where snumber ='" + s_number + "'", "answer");
            foreach (DataRow row in myds.Tables[0].Rows)
            {
                //取出对应试卷的选择题答案
                answer[0] = Split(row["A"].ToString(), "；");
                //取出对应试卷的判断题答案
                answer[1] = Split(row["B"].ToString(), "；");
                //取出对应试卷的程序设计题答案id
                answer[2] = Split(row["C"].ToString(), "；");
            }
            if (cn.State == ConnectionState.Open)
                cn.Close();
            return answer;
        }
        #endregion
    }
}