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

標籤：c# winfrom 增刪改查

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);        }    }}

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