?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 Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void dataGridView1_CellClick(object sender, DataGridViewCellEventArgs e)
        {
            fillData();
        }

        private void showData()
        {
            myClass mC = new myClass();
            OleDbConnection con = mC.getConnect();
            using (con)//連接數據庫表，并取得表中的一個字段
            {
                DataTable dt = new DataTable();        //內存中數據的一個表
                OleDbDataAdapter da = new OleDbDataAdapter("select * from 學生 order by 學號", con);//數據適配器
                da.Fill(dt);                           //用數據適配器da去填寫內存中的那個表dt
                DataView dv = new DataView(dt);        //用已有的數據去填寫數據視圖
                this.dataGridView1.DataSource = dv;    //數據控件就等于dv

            }
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            showData();
        }

        private void fillData()
        {
            try
            {
                this.numberTextBox.Text = this.dataGridView1[0, this.dataGridView1.CurrentCell.RowIndex].Value.ToString();
                this.nameTextBox.Text = this.dataGridView1[1, this.dataGridView1.CurrentCell.RowIndex].Value.ToString();
                this.sexTextBox.Text = this.dataGridView1[2, this.dataGridView1.CurrentCell.RowIndex].Value.ToString();
                this.ageTextBox.Text = this.dataGridView1[3, this.dataGridView1.CurrentCell.RowIndex].Value.ToString();
                this.acaTextBox.Text = this.dataGridView1[4, this.dataGridView1.CurrentCell.RowIndex].Value.ToString();
                this.speTextBox.Text = this.dataGridView1[5, this.dataGridView1.CurrentCell.RowIndex].Value.ToString();
            }
            catch { }
        }

        private void add(object sender, EventArgs e)
        {
            if (IsSameRecord())
            {
                return;
            }
            else
            {
                try
                {
                    StringBuilder strOLE = new StringBuilder();
                    strOLE.Append("insert into 學生(學號,姓名,性別,年齡,學院,專業)");
                    strOLE.Append("values('" + this.numberTextBox.Text.Trim().ToString() + "','" + this.nameTextBox.Text.Trim().ToString() + "',");
                    strOLE.Append("'" + this.sexTextBox.Text.Trim().ToString() + "','" + this.ageTextBox.Text.Trim().ToString() + "',");
                    strOLE.Append("'" + this.acaTextBox.Text.Trim().ToString() + "','" + this.speTextBox.Text.Trim().ToString() + "')");
                    myClass mC = new myClass();
                    mC.getCommon(strOLE.ToString()); 
                    strOLE.Remove(0, strOLE.Length);
                    MessageBox.Show("添加成功");
                    showData();
                }
                catch (Exception ex)
                {
                    MessageBox.Show("錯誤：" + ex.Message, "錯誤提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Error);
                }
                finally
                {
                }
            }
        }

        private bool IsSameRecord()
        {
            string Str_condition = "";
            Str_condition = this.numberTextBox.Text.Trim();

            StringBuilder strOLE = new StringBuilder();
            strOLE.Append("select * from 學生 ");
            strOLE.Append("where 學號='" + Str_condition + "'");

            myClass mC = new myClass();
            DataSet myDS = mC.getDS(strOLE.ToString(), "學生");
            strOLE.Remove(0, strOLE.Length);

            if (myDS.Tables["學生"].Rows.Count > 0)
            {
                MessageBox.Show("已存在相同的員工號，請重新更改員工號！");
                return true;             
            }
            else
            {                
                return false;
            }
        }

        private void del(object sender, EventArgs e)
        { 

            if (this.numberTextBox.Text == "")
            {
                MessageBox.Show("請選擇你要刪除的學生");
                return;
            }
            else
            {
                try
                {
                    StringBuilder strOLE = new StringBuilder();
                    strOLE.Append("delete from 學生 where 學號='"+ this.numberTextBox.Text.Trim().ToString() +"'");
                    myClass mC = new myClass();
                    mC.getCommon(strOLE.ToString());
                    strOLE.Remove(0, strOLE.Length);
                    MessageBox.Show("刪除成功");
                    showData();
                }
                catch (Exception ex)
                {
                    MessageBox.Show("錯誤：" + ex.Message, "錯誤提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Error);
                }
                finally
                {
                }
            }
        }

        private void edit(object sender, EventArgs e)
        {
            if (this.nameTextBox.Text != "")
            {
                string currentDataStr = "";
                currentDataStr = this.dataGridView1[0, this.dataGridView1.CurrentCell.RowIndex].Value.ToString();

                StringBuilder sBuilder = new StringBuilder();
                sBuilder.Append("update 學生 set 學號='" + this.numberTextBox.Text + "',");
                sBuilder.Append("姓名='" + this.nameTextBox.Text + "', 性別='" + this.sexTextBox.Text + "',");
                sBuilder.Append("年齡='" + this.ageTextBox.Text + "', 學院='" + this.acaTextBox.Text + "',");
                sBuilder.Append("專業='" + this.speTextBox.Text + "' where 學號='" + currentDataStr + "'");

                try
                {
                    myClass mC = new myClass();
                    mC.getCommon(sBuilder.ToString());
                    MessageBox.Show("恭喜，修改成功！");
                }
                catch (Exception ex)
                {
                    MessageBox.Show("錯誤：" + ex.Message, "錯誤提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Error);
                }
                showData();
            }
            else
            {
                MessageBox.Show("請輸入學號！"); 
            }
        }

        private void clean(object sender, EventArgs e)
        {
            this.nameTextBox.Text = "";
            this.numberTextBox.Text = "";
            this.sexTextBox.Text = "";
            this.ageTextBox.Text = "";
            this.acaTextBox.Text = "";
            this.speTextBox.Text = "";
        }

        private void find(object sender, EventArgs e)
        {
            string findStr = this.searchTextBox.Text;
            if (findStr == "")
            {
                MessageBox.Show("請輸入查詢條件！");
            }
            else
            {
                StringBuilder sBuilder = new StringBuilder();
                sBuilder.Append("select * from 學生 where 學號='"+ this.searchTextBox.Text +"' ");
                sBuilder.Append("or 姓名='"+ this.searchTextBox.Text +"' or 學院='"+ this.searchTextBox.Text +"'");

                try
                {
                    myClass mC = new myClass();
                    OleDbDataReader dr = mC.getRead(sBuilder.ToString());
                    if (dr.Read())
                    {
                        this.numberTextBox.Text = dr.GetString(0);
                        this.nameTextBox.Text = dr.GetString(1);
                        this.sexTextBox.Text = dr.GetString(2);
                        this.ageTextBox.Text = dr.GetString(3);
                        this.acaTextBox.Text = dr.GetString(4);
                        this.speTextBox.Text = dr.GetString(5);
                        MessageBox.Show("查找成功！");
                    }
                    else
                    {
                        MessageBox.Show("不存在你要查找的結果!請重新輸入！");
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show("錯誤：" + ex.Message, "錯誤提示", MessageBoxButtons.OKCancel, MessageBoxIcon.Error);
                }
            }
        }
    }
}