using System;
using System.Collections.Generic;
using
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;

namespace UserManager
{
    public partial class Form1 : Form
    {
        string txtConn = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=ykjj.mdb";
        public Form1()
        {
            InitializeComponent();
        }

        private void LoadData(string name)
        {
            using (OleDbConnection conn = new OleDbConnection(txtConn))
            {
                string sql;
                if (name.Trim() != "")
                    sql = String.Format("select yhm as 用户名,mm as 密码,qx as 权限 from czyb where yhm like'%{0}%'", name);
                else
                    sql = "select yhm as 用户名,mm as 密码,qx as 权限 from czyb";
                DataSet ds = new DataSet();
                OleDbDataAdapter da= new OleDbDataAdapter(sql,conn);
                da.Fill(ds, "user");
                bdsUser.DataMember = "user";
                bdsUser.DataSource = ds;
                dgvUser.DataSource = bdsUser;
                bdnUser.BindingSource = bdsUser;
            }
            //dgvUser.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.Fill;
            
        }
        private void Form1_Load(object sender, EventArgs e)
        {
                     
            //下面的代码用于ComboBox数据绑定
            using (OleDbConnection conn = new OleDbConnection(txtConn))
            {
                string sql = "select distinct qx from czyb";
                DataSet ds = new DataSet();
                OleDbDataAdapter da = new OleDbDataAdapter(sql, conn);
                da.Fill(ds, "qxb");
                bdsQx.DataMember = "qxb";
                bdsQx.DataSource = ds;
                cmbPriority.ValueMember = "qx";
                cmbPriority.DisplayMember = "qx";
                cmbPriority.DataSource = bdsQx;
            }
            LoadData("");
            txtName.DataBindings.Add("Text", bdsUser, "用户名");
            txtPassword.DataBindings.Add("Text", bdsUser, "密码");
            cmbPriority.DataBindings.Add("SelectedValue", bdsUser, "权限");
        }

        private void btnSearch_Click(object sender, EventArgs e)
        {
            LoadData(txtSearch.Text);
            
        }
        private bool CheckTrim(string name,string password,string priority)
        {
            if (name == "" || password == "" ||priority == "")
            {               
                return true;
            }
            else
                return false;
        }
        private bool Check(string yhm)   //判断该用户是否已存在
        {
            int i=0;
            using (OleDbConnection conn = new OleDbConnection(txtConn))
            {
                conn.Open();
                string sql = String.Format("select count(*) from czyb where yhm='{0}'",yhm);
                OleDbCommand cmd = new OleDbCommand(sql, conn);
               
                try
                {
                    i=Convert.ToInt32(cmd.ExecuteScalar());
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
                conn.Close();
            }
            if (i > 0) return true;
            else return false;
        }

        private void bindingNavigatorAddNewItem_Click(object sender, EventArgs e)
        {
            string name = txtName.Text.Trim();
            string password = txtPassword.Text.Trim();
            string priority = cmbPriority.Text.Trim();
            if (CheckTrim(name, password, priority))
            {
                MessageBox.Show("用户信息不能为空");
                return;
            }
            if (Check(name))
            {
                MessageBox.Show("该用户名已存在!");
                return;
            }
            using (OleDbConnection conn = new OleDbConnection(txtConn))
            {
                conn.Open();
                string sql = String.Format("insert into czyb(yhm,mm,qx) values('{0}','{1}','{2}')", name, password, priority);
                OleDbCommand cmd = new OleDbCommand(sql, conn);
                try
                {
                    cmd.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
                conn.Close();
            }
            LoadData(txtSearch.Text);
        }

        private void btnChange_Click(object sender, EventArgs e)
        {
            string name = txtName.Text.Trim();
            string password = txtPassword.Text.Trim();
            string priority = cmbPriority.Text.Trim();
            if (CheckTrim(name, password, priority))
            {
                MessageBox.Show("用户信息不能为空");
                return;
            }
           
            using (OleDbConnection conn = new OleDbConnection(txtConn))
            {
                conn.Open();
                string sql = String.Format("update czyb set mm='{0}',qx='{1}' where yhm='{2}'", password, priority, name);
                OleDbCommand cmd = new OleDbCommand(sql, conn);
                try
                {
                    cmd.ExecuteNonQuery();
                    MessageBox.Show("修改记录成功！");
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
                conn.Close();
            }
            LoadData(txtSearch.Text);
        }

        private void bindingNavigatorDeleteItem_Click(object sender, EventArgs e)
        {
            string name = dgvUser.CurrentRow.Cells[0].Value.ToString();
            using (OleDbConnection conn = new OleDbConnection(txtConn))
            {
                conn.Open();
                string sql = String.Format("delete from czyb where yhm='{0}'", name);
        
                OleDbCommand cmd = new OleDbCommand(sql, conn);
                if(MessageBox.Show("确认要删除吗？","警告！",MessageBoxButtons.YesNo,MessageBoxIcon.Warning)==DialogResult.Yes)
                {
                    try
                    {
                        cmd.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show(ex.Message);
                    }
                    conn.Close();
                }
            }
            LoadData(txtSearch.Text);
        }

        private void dgvUser_CellClick(object sender, DataGridViewCellEventArgs e)
        {
            //txtName.Text = dgvUser.CurrentRow.Cells[0].Value.ToString();
            //txtPassword.Text = dgvUser.CurrentRow.Cells[1].Value.ToString();
            //cmbPriority.Text = dgvUser.CurrentRow.Cells[2].Value.ToString();
        }

        private void txtSearch_KeyDown(object sender, KeyEventArgs e)
        {
            if (e.KeyData == Keys.Enter)
                btnSearch.PerformClick();
        }

        private void dgvUser_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {

        }

    }
}