?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 ;


namespace 學籍管理系統
{
    public partial class jiaocai :DevComponents .DotNetBar .Office2007Form 
    {
        public jiaocai()
        {
            InitializeComponent();
           
        }

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

        private void bar1_ItemClick(object sender, EventArgs e)
        {

        }
        private OleDbDataAdapter da;
        private DataTable dt;
        //通用檢索函數
        private void constring(string command)
        {
            string cn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Application.StartupPath + "\\xjgl.mdb" + ";Persist Security Info=True";
            OleDbConnection cnn = new OleDbConnection(cn);
            string updatas = command;
            OleDbCommand com = new OleDbCommand(updatas, cnn);
            try
            {

                cnn.Open();
                com.ExecuteNonQuery();
                cnn.Close();
                jiaocai_Load(null, null);
                cls();
            }
            catch (Exception)
            {
                MessageBox.Show("輸入的不正確！");
            }
            finally
            {
                cnn.Close();
            }
        }
        private void jiaocai_Load(object sender, EventArgs e)
        {
            string cn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Application.StartupPath + "\\xjgl.mdb" + ";Persist Security Info=True";
            OleDbConnection cnn = new OleDbConnection(cn);
            string sel = "select Cou_id as 學科代號,Cou_name as 學科名,Tea_name as 任課教師 from courview";
            OleDbCommand com = new OleDbCommand(sel, cnn);
            da = new OleDbDataAdapter();
            dt = new DataTable();
            da.SelectCommand = com;
            OleDbCommandBuilder cb = new OleDbCommandBuilder(da );
            da.Fill(dt);
            dataGridView1.DataSource = dt;

            OleDbDataAdapter da1 = new OleDbDataAdapter();
            DataTable dt1 = new DataTable();
            string sel1 = "select *from Teacher";
            OleDbCommand com1 = new OleDbCommand(sel1, cnn);
            da1.SelectCommand = com1;
            OleDbCommandBuilder cb1 = new OleDbCommandBuilder(da1);
            da1.Fill(dt1);
            comboBox1.DataSource = dt1;
           comboBox1.DisplayMember = dt1.Columns["Tea_name"].ToString();
    


        }
        private void cls()
        {
            textBoxX1.Text = "";
            textBoxX2.Text = "";
            comboBox1.Text = "";
        }
        private void reasch()
        {
            string cn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Application.StartupPath + "\\xjgl.mdb" + ";Persist Security Info=True";
            OleDbConnection cnn = new OleDbConnection(cn);
            string sel = "select *from Teacher where Tea_name='" + comboBox1.Text.Trim() + "'";
            OleDbCommand com = new OleDbCommand(sel, cnn);
            try
            {
                cnn.Open();
                OleDbDataReader dr = com.ExecuteReader();
                if (dr.Read())
                {
                    teaid = dr["Tea_id"].ToString();
                }
                dr.Close();
                cnn.Close();
            }
            catch (Exception)
            {
                MessageBox.Show("輸入的不正確！");
            }
            finally
            {
               cnn.Close();
            }
        }
        private void buttonItem1_Click(object sender, EventArgs e)
        {
            // 添加
            reasch();

            string inserts = "insert into Course values(" + "'" + textBoxX1.Text.Trim() + "','" + textBoxX2.Text.Trim() + "','" + teaid + "')";
            constring(inserts);
        }
        private static string teaid;
        private void comboBox1_SelectionChangeCommitted(object sender, EventArgs e)
        {
      
        }
        private void buttonItem2_Click(object sender, EventArgs e)
        {
           
            //查詢
            reasch();

            string updates = "update Course set Cou_id='" + textBoxX1.Text.Trim() + "',Cou_name='" + textBoxX2.Text.Trim() + "',Tea_id='" + teaid + "' where Cou_id='"+textBoxX1 .Text .Trim ()+"'";
            constring(updates);
        }

        private void buttonItem3_Click(object sender, EventArgs e)
        {
          
            string del = "delete from Course where Cou_id='" + textBoxX1.Text.Trim()+"'";
            constring(del);
        }

        private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
           textBoxX1.Text  = dt.Rows[e.RowIndex][0].ToString();
           textBoxX2.Text = dt.Rows[e.RowIndex][1].ToString();
           comboBox1.Text = dt.Rows[e.RowIndex][2].ToString();
        }

        private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
        {

        }
    }
}