?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 Microsoft.VisualBasic;

namespace 學(xué)籍管理系統(tǒng)
{
    public partial class chengji : DevComponents .DotNetBar .Office2007Form 
    {
        public chengji()
        {
            InitializeComponent();
        }
        private DataTable dt;
        private OleDbDataAdapter da;
        private void chengji_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 mysel = "Select Stu_id as 學(xué)號(hào),Stu_name as 姓名,Stu_major as 專業(yè),Stu_department as 學(xué)院,Cou_name as 學(xué)科,Grade as 學(xué)分,Tea_name as 任課教師 from grade2";
            OleDbCommand com = new OleDbCommand(mysel, cnn);
            dt = new DataTable();
            da = new OleDbDataAdapter();
            da.SelectCommand = com;
            OleDbCommandBuilder cb = new OleDbCommandBuilder(da);
            da.Fill(dt);
            dataGridView1.DataSource = dt;


            DataTable dt1 = new DataTable();
            OleDbDataAdapter da1 = new OleDbDataAdapter();
            string mysel1 = "select *from Course";
            OleDbCommand com1 = new OleDbCommand(mysel1, cnn);
            da1.SelectCommand = com1;
            OleDbCommandBuilder cb1 = new OleDbCommandBuilder(da1);
            da1.Fill(dt1);
            cbokc.DataSource = dt1;
            cbokc.DisplayMember = dt1.Columns["Cou_name"].ToString();
        

            DataTable dt2 = new DataTable();
            OleDbDataAdapter da2 = new OleDbDataAdapter();
            string mysel2 = "select *from Students";
            OleDbCommand com2 = new OleDbCommand(mysel2, cnn);
            da2.SelectCommand = com2;
            OleDbCommandBuilder cb2 = new OleDbCommandBuilder(da2);
            da2.Fill(dt2);
            txxh.DataSource = dt2;
            txxh.DisplayMember = dt2.Columns["Stu_id"].ToString();

         
        }

        private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
            int x = e.RowIndex;
            txxh.Text = dt.Rows[x][0].ToString();
            txname.Text = dt.Rows[x][1].ToString();
            txmajor.Text = dt.Rows[x][2].ToString();
            txxb.Text = dt.Rows[x][3].ToString();
            cbokc.Text = dt.Rows[x][4].ToString();
            cj.Text = dt.Rows[x][5].ToString();
            string cn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Application.StartupPath + "\\xjgl.mdb" + ";Persist Security Info=True";
            OleDbConnection cnn = new OleDbConnection(cn);
            string find = "Select * from Course where Cou_name=" + "'" + cbokc.Text + "'";
            try
            {
                cnn.Open();
                OleDbCommand com = new OleDbCommand(find, cnn);
                OleDbDataReader dr = com.ExecuteReader();

                if (dr.Read())
                {
                    cobknn = dr["Cou_id"].ToString();
                }
                cnn.Close();
                dr.Close();
            }
            catch (Exception)
            {
                MessageBox.Show("輸入的不正確！");
            }
            finally
            {
                cnn.Close();
            }
        }
        public static string cobk;
        private void reascher()
        {
            string cn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Application.StartupPath + "\\xjgl.mdb" + ";Persist Security Info=True";
            OleDbConnection cnn = new OleDbConnection(cn);
            string find = "Select * from Course where Cou_name=" + "'" + cbokc.Text + "'";
            try
            {
                cnn.Open();
                OleDbCommand com = new OleDbCommand(find, cnn);
                OleDbDataReader dr = com.ExecuteReader();

                if (dr.Read())
                {
                    cobk = dr["Cou_id"].ToString();
                }
                dr.Close();
                cnn.Close();
            }
            catch (Exception)
            {
                MessageBox.Show("輸入的不正確！");
            }
            finally 
            {
                cnn.Close();
            }
        }
         
        //通用檢索函數(shù)
        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();
                chengji_Load(null, null);
                cls();
            }
            catch (Exception)
            {
                MessageBox.Show("輸入的不正確！");
            }
            finally
            {
                cnn.Close();
            }
 
        }
        private void buttonItem1_Click(object sender, EventArgs e)
        {
            
            reascher();

            string mycoms = "insert into Grades (Stu_id,Cou_id,Grade) Values(" + "'" + txxh.Text + "'" + "," + "'" + cobk + "'," + "'" + cj.Text.Trim() + "')";

            constring(mycoms);
        }
        private void txname_SelectedIndexChanged(object sender, EventArgs e)
        {
   
        }

        private void txname_SelectionChangeCommitted(object sender, EventArgs e)
        {
            txname.Text = "";
            txxb.Text = "";
            txmajor.Text  = "";

            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 Students where Stu_id=" + "'" + txxh .Text .Trim () + "'";
            OleDbCommand com = new OleDbCommand(sel, cnn);
            try
            {
                cnn.Open();
                OleDbDataReader dr = com.ExecuteReader();
                if (dr.Read())
                {
                    txname.Text = dr["Stu_name"].ToString();
                    txmajor.Text = dr["Stu_major"].ToString();
                    txxb.Text = dr["Stu_department"].ToString();

                }
                dr.Close();
                cnn.Close();
            }
            catch (Exception)
            {
                MessageBox.Show("輸入的不正確！");
                
            }
            finally
            {
              
                cnn.Close();
            }

        }
        private void buttonItem4_Click(object sender, EventArgs e)
        {
            this.Close();
        }
        private string cobknn = null;
        private void del_Click(object sender, EventArgs e)
        {
           
            string del = "delete from Grades where Stu_id=" + "'" + txxh.Text.Trim() + "'and Cou_id=" + "'" + cobknn + "'";
            constring(del); 
        }

        private void cls()
        {
            txxh.Text = "";
            txxh.Text = "";
            txname.Text = "";
            txmajor.Text = "";
            txxb.Text = "";
            cj.Text = "";
        }
        private void buttonItem2_Click(object sender, EventArgs e)
        {
           
            string updatas = "update Grades set Grade=" + "'" + cj.Text.Trim() + "' where Stu_id=" + "'" + txxh.Text.Trim() + "' and Cou_id=" + "'" + cobknn + "'";
            constring(updatas);

        }



        private void find_Click(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 mysel = "Select Stu_id as 學(xué)號(hào),Stu_name as 姓名,Stu_major as 專業(yè),Stu_department as 學(xué)院,Cou_name as 學(xué)科,Grade as 學(xué)分,Tea_name as 任課教師 from grade2 where Stu_id='"+txxh.Text .Trim ()+"'";
            OleDbCommand com = new OleDbCommand(mysel, cnn);
            da = new OleDbDataAdapter();
            dt=new DataTable ();
            da.SelectCommand = com;
            da.Fill(dt);
            dataGridView1.DataSource = dt;
            cls();

        }
        
        private void buttonItem3_Click(object sender, EventArgs e)
        {
          
        }
    }
}