formscoreimport.cs

学生管理系统Csharp2005 学生管理系统Csharp2005 学生管理系统Csharp2005

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
using System.Data.SqlClient;

using StudentManager.DbLayer;

namespace StudentManager
{
    public partial class FormScoreImport : Form
    {
        public FormScoreImport()
        {
            InitializeComponent();
        }

        private void splitContainer1_Panel1_Paint(object sender, PaintEventArgs e)
        {

        }

        /// <summary>
        /// “打开文件”单击事件
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void buttonOpenFile_Click(object sender, EventArgs e)
        {
            openFileDialog1.Filter = "Excel文件(*.xls)|*.xls|All files (*.*)|*.*";
            openFileDialog1.FilterIndex = 1;

            if (openFileDialog1.ShowDialog() != DialogResult.OK)
                return;

            ImportFile(this.openFileDialog1.FileName);
        }

        /// <summary>
        /// 将Excel文件fileName中的数据导入到数据库表“Student_Course”中
        /// </summary>
        /// <param name="fileName"></param>
        void ImportFile(string fileName)
        {
            //从Excel中读取数据，放入DataSet对象中
            DataSet dataSet = new DataSet();
            try
            {
                OleDbConnection myConn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + ";Extended Properties=Excel 8.0;");
                myConn.Open();
                OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT * FROM [Sheet1$]", myConn);
                OleDbCommandBuilder builder = new OleDbCommandBuilder(adapter);
                adapter.Fill(dataSet);
                myConn.Close();
            }
            catch (OleDbException ex)
            {
                MessageBox.Show(ex.Message);
            }

            //根据课程名称，查询课程编号
            string sql = "";
            string cNo = "";
            Database db = new Database();

            sql = "select CNo from [Course] where CName = ";
            sql += SqlStringConstructor.GetQuotedString(comboBoxCourse.SelectedItem.ToString());
            DataRow row = db.GetDataRow(sql);
            cNo = GetSafeData.ValidateDataRow_S(row, "CNo");

            //逐行把DataSet中的数据导入到SQL Server中
            foreach (DataRow row2 in dataSet.Tables[0].Rows)
            {
                string sNo = GetSafeData.ValidateDataRow_S(row2, "学号");
                string score = GetSafeData.ValidateDataRow_S(row2, "成绩");

                sql = "";
                sql += "Insert into [Student_Course] values (";
                sql += SqlStringConstructor.GetQuotedString(sNo) + ",";
                sql += SqlStringConstructor.GetQuotedString(cNo) + ",";
                sql += score + ")";
                db.ExecuteSQL(sql);
            }

            LoadScore();
        }


        /// <summary>
        ///  把导入的成绩数据查询出来，绑定到DataGridView中
        /// </summary>
        void LoadScore()
        {
            string sql = "";
            sql += "select [Student].SNo,SName,Score from [Student],[Student_Course]";
            sql += " where [Student].SNo = [Student_Course].SNo";

            Database db = new Database();

            DataTable dt = db.GetDataTable(sql);
            dataGridViewPreView.DataSource = dt;
        }

        private void buttonClose_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        /// <summary>
        /// 读取课程表中的数据，并绑定导下拉框中
        /// </summary>
        void InitData()
        {
            string sql = "select * from [Course]";

            Database db = new Database();
            SqlDataReader dr = db.GetDataReader(sql);
            comboBoxCourse.Items.Clear();

            while (dr.Read())
            {
                comboBoxCourse.Items.Add(GetSafeData.ValidateDataReader_S(dr, "CName"));
            }

            if (comboBoxCourse.Items.Count > 0)
                comboBoxCourse.SelectedIndex = 0;
        }

        /// <summary>
        /// 窗体加载事件
        /// </summary>
        /// <param name="sender"></param>
        /// <param name="e"></param>
        private void FormScoreImport_Load(object sender, EventArgs e)
        {
            InitData();
        }
    }
}