C#Winfrom資料庫增删改查執行個體--SQL操作版

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
using System.Configuration;
namespace TestDBOper
{
    public partial class fmMain : Form
    {
        static string connstr = ConfigurationManager.ConnectionStrings["connstr"].ConnectionString;
        static DBState dbs;
        enum DBState { sAdd, sMod, sDel, sBro }
        public fmMain()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            this.dataGridView1.AutoGenerateColumns = false; //不要自動生成列
              btnQuery_Click(sender, e);
        }



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

        //查詢
        private void btnQuery_Click(object sender, EventArgs e)
        {
            int p = 0;
            if (this.dataGridView1.CurrentCell != null)
            {
                p = this.dataGridView1.CurrentCell.RowIndex;

            }

            OleDbConnection conn = new OleDbConnection(connstr);
            OleDbDataAdapter da = new OleDbDataAdapter("select * from users where username like '%" + this.textBox1.Text + "%'", conn);
            DataTable dt = new DataTable();

            conn.Open();
            da.Fill(dt);
            this.dataGridView1.DataSource = dt;

            if (p < dataGridView1.Rows.Count) { this.dataGridView1.CurrentCell = dataGridView1.Rows[p].Cells[0]; }

            toolStripStatusLabel1.Text = "共查詢到 " + dt.Rows.Count.ToString() + " 條資料";

            dbs = DBState.sBro;
            SetBtn();
        }

        //增加
        private void btnAdd_Click(object sender, EventArgs e)
        {
            dbs = DBState.sAdd;
            SetBtn();
            UserTxtClear();
        }

        //修改
        private void btnMod_Click(object sender, EventArgs e)
        {
            dbs = DBState.sMod;
            SetBtn();
            UserRefresh();
        }

        //删除 
        private void btnDel_Click(object sender, EventArgs e)
        {
            if (MessageBox.Show("您确定要删除嗎？", "提示", MessageBoxButtons.YesNo) != DialogResult.Yes) { return; }

            string sql = "delete from users where id [email protected]";
            OleDbConnection conn = new OleDbConnection(connstr);
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbParameter[] param = { new OleDbParameter("@id", this.dataGridView1.Rows[this.dataGridView1.CurrentCell.RowIndex].Cells["id"].Value.ToString()) };
            cmd.Parameters.AddRange(param);

            conn.Open();
            int n = 0;
            n = cmd.ExecuteNonQuery();
            conn.Close();
            if (n != 0)
            {

                MessageBox.Show("删除成功");

            }
            else
            {
                MessageBox.Show("删除失敗");

            }

            btnQuery_Click(sender, e);

        }

        //儲存
        private void btnSave_Click(object sender, EventArgs e)
        {
            if (txtUserName.Text.Trim()=="")
            {
                MessageBox.Show("姓名 不能為空");
                return;
                
            }
            if (txtUserSex.Text.Trim()=="")
            {
                MessageBox.Show("性别 不能為空");
                return; 
            }
            if (txtUserAge.Text.Trim()=="")
            {
                MessageBox.Show("年齡不能為空");
                return;
            }

            if (dbs == DBState.sAdd)
            {

                string sql = "insert into Users (username,userage,usersex) values  (@username,@userage,@usersex)";

                OleDbConnection conn = new OleDbConnection(connstr);
                OleDbCommand cmd = new OleDbCommand(sql, conn);
                OleDbParameter[] param ={ new OleDbParameter("@username",txtUserName.Text),
                                      new OleDbParameter("@userage",txtUserAge.Text),
                                      new OleDbParameter("@usersex",txtUserSex.Text)
                                    };
                cmd.Parameters.AddRange(param);
                conn.Open();
                int n = 0;
                n = cmd.ExecuteNonQuery();
                conn.Close();

                if (n != 0)
                {
                    MessageBox.Show("增加成功");
                }
                else
                {
                    MessageBox.Show("增加失敗");
                }
            }
            else if (dbs == DBState.sMod)
            {
                string sql = "update users set [email protected],[email protected],[email protected] where id= @id";

                OleDbConnection conn = new OleDbConnection(connstr);
                OleDbCommand cmd = new OleDbCommand(sql, conn);
                OleDbParameter[] param ={ new OleDbParameter("@username",txtUserName.Text),
                                          new OleDbParameter("@userage",txtUserAge.Text),
                                          new OleDbParameter("@usersex",txtUserSex.Text),
                                          new OleDbParameter("@id",this.dataGridView1.Rows[dataGridView1.CurrentCell.RowIndex].Cells["id"].Value.ToString())
                                        };
                cmd.Parameters.AddRange(param);
                conn.Open();
                int n = 0;
                n = cmd.ExecuteNonQuery();
                conn.Close();
                if (n != 0)
                {
                    MessageBox.Show("修改成功");
                }
                else
                {
                    MessageBox.Show("修改失敗");
                }

            }

            dbs = DBState.sBro;
            SetBtn();
            btnQuery_Click(sender, e);
        }

        //取消
        private void btnCancel_Click(object sender, EventArgs e)
        {
            dbs = DBState.sBro;
            SetBtn();
        }


        //---------------------------------------------- 功能函數 ------------------------------------------------
        //清空輸入框
        private void UserTxtClear()
        {
            txtUserName.Text = "";
            txtUserSex.Text = "";
            txtUserAge.Text = "";
        }
        //重新整理使用者
        private void UserRefresh()
        {
            txtUserName.Text = this.dataGridView1.Rows[dataGridView1.CurrentCell.RowIndex].Cells["username"].Value.ToString();
            txtUserSex.Text = this.dataGridView1.Rows[dataGridView1.CurrentCell.RowIndex].Cells["usersex"].Value.ToString();
            txtUserAge.Text = this.dataGridView1.Rows[dataGridView1.CurrentCell.RowIndex].Cells["userage"].Value.ToString();
        }

        //設定按鈕狀态 
        private void SetBtn()
        {
            btnAdd.Enabled = dbs == DBState.sBro;
            btnMod.Enabled = dbs == DBState.sBro;
            btnDel.Enabled = dbs == DBState.sBro;
            btnQuery.Enabled = dbs == DBState.sBro;
            btnSave.Enabled = dbs != DBState.sBro;
            btnCancel.Enabled = dbs != DBState.sBro;
        }

        private void dataGridView1_DoubleClick(object sender, EventArgs e)
        {
            btnMod_Click(sender, e);
        }
    }
}