19.4.8 数据库综合运用

版权声明：本文为博主原创文章，转载请在显著位置标明本文出处以及作者网名，未经作者允许不得用于商业目的。

需要北风数据库的请留言自己的信箱。

本节中将通过实例综合展示对数据表的查询、增加、修改和删除。

【例 19.16】【项目：code19-016】雇员信息管理。

本例演示了对北风数据库中雇员表中的雇员信息进行管理，实现对雇员信息（由于原表信息太多，有省略）的增加、修改和删除，其中修改提供了两种方式。

窗体设计如下图所示：

图19-36 窗体设计

详细代码如下：

        OleDbConnection conn;

        string currentName;

        private void Form1_Load(object sender, EventArgs e)

        {

            //建立OleDbConnection对象实例

            conn = new OleDbConnection();

            //32位程序使用此语句

            conn.ConnectionString = "Provider = Microsoft.Jet.OLEDB.4.0;data source=C:\\lessons\\Northwind.mdb;";

            //64位程序使用此语句

            //conn.ConnectionString = "Provider = Microsoft.ACE.OLEDB.12.0;data source=C:\\lessons\\Northwind.mdb;";

            conn.Open();

            listEmployee();

        }

 

        //将所有雇员全名加入到cbEmployee中

        private void listEmployee()

        {

            cbEmployee.Items.Clear();

            OleDbCommand command = new OleDbCommand();

            command.CommandText = "select 雇员ID,(姓氏+名字) as 姓名 from 雇员";

            command.Connection = conn;

            OleDbDataReader reader;

            reader = command.ExecuteReader(CommandBehavior.SingleResult);

 

            if( reader.HasRows)

            {

                while (reader.Read())

                    cbEmployee.Items.Add(reader["姓名"]);

                cbEmployee.SelectedIndex = 0;

            }

        }

 

 

        private void Form1_FormClosing(object sender, FormClosingEventArgs e)

        {

            conn.Close();

        }

 

        //增加雇员信息

        private void btnAdd_Click(object sender, EventArgs e)

        {

            btnOk.Visible = true;

            btnCancel.Visible = true;

            Panel1.Visible = true;

            Panel1.Left = cbEmployee.Left;

            Panel1.Top = 10;

            cbEmployee.Visible = false;

 

            //清空文本框内的所有数据

            txtDuties.Text = "";

            txtAppellation.Text = "";

            dtpBirthday.Value = DateTime.Now;

            dtpEmployDay.Value = DateTime.Now;

            txtCountry.Text = "";

            txtCity.Text = "";

            txtArea.Text = "";

            txtAddress.Text = "";

            txtZipcode.Text = "";

            txtPhone.Text = "";

        }

 

        //增加雇员信息实际是btnOk按钮完成

        private void btnOk_Click(object sender, EventArgs e)

        {

            OleDbCommand command = new OleDbCommand();

            command.CommandText = "insert into 雇员(姓氏,名字,职务,尊称,出生日期,雇用日期,地址,城市,地区,邮政编码,国家,家庭电话)" +

                                   "values('" + txtSurname.Text + "','" +

                                        txtName.Text + "','" +

                                        txtDuties.Text + "','" +

                                        txtAppellation.Text + "','" +

                                        dtpBirthday.Value + "','" +

                                        dtpEmployDay.Value + "','" +

                                        txtAddress.Text + "','" +

                                        txtCity.Text + "','" +

                                        txtArea.Text + "','" +

                                        txtZipcode.Text + "','" +

                                        txtCountry.Text + "','" +

                                        txtPhone.Text + "')";

            command.Connection = conn;

            try

            {

                command.ExecuteNonQuery();

                MessageBox.Show("新增成功！");

                string fullname = txtSurname.Text + txtName.Text;

                cbEmployee.Items.Add(fullname);

                currentName = fullname;

            }          

            catch (Exception ex)

            {

                MessageBox.Show("新增失败：" + ex.Message);

            }

 

            btnOk.Visible = false;

            btnCancel.Visible = false;

            Panel1.Visible = false;

            cbEmployee.Visible = true;

            //显示雇员信息

            getEmployeeInfo(currentName);

        }

 

        //取消新增雇员

        private void btnCancel_Click(object sender, EventArgs e)

        {

            btnOk.Visible = false;

            btnCancel.Visible = false;

            Panel1.Visible = false;

            cbEmployee.Visible = true;

            //显示雇员信息

            getEmployeeInfo(currentName);

        }

 

        //修改雇员信息方式一

        private void btnEdit1_Click(object sender, EventArgs e)

        {

            OleDbCommand command = new OleDbCommand();

            command.CommandText = "update 雇员 set 职务='" + txtDuties.Text +

                                    "',尊称='" + txtAppellation.Text +

                                    "',出生日期='" + dtpBirthday.Value +

                                    "',雇用日期='" + dtpEmployDay.Value +

                                    "',国家='" + txtCountry.Text +

                                    "',城市='" + txtCity.Text +

                                    "',地区='" + txtArea.Text +

                                    "',地址='" + txtAddress.Text +

                                    "',邮政编码='" + txtZipcode.Text +

                                    "',家庭电话='" + txtPhone.Text +

                                    "' where 姓氏+名字='" + cbEmployee.Text + "'";

            command.Connection = conn;

            try

            {

                command.ExecuteNonQuery();

                MessageBox.Show("修改成功！");

            }

           

            catch ( Exception ex)

            {

                MessageBox.Show("修改失败：" + ex.Message);

            }

        }

 

        //修改雇员信息方式二

        private void btnEdit2_Click(object sender, EventArgs e)

        {

            OleDbCommand command = new OleDbCommand();

            command.CommandText = "update 雇员 set 职务=?,尊称=?,出生日期=?," +

                                    "雇用日期=?," +

                                    "国家=?," +

                                    "城市=?," +

                                    "地区=?," +

                                    "地址=?," +

                                    "邮政编码=?," +

                                    "家庭电话=?" +

                                    " where 姓氏+名字=?";

            command.Parameters.Add("@Duties", OleDbType.VarChar);

            command.Parameters["@Duties"].Value = txtDuties.Text;

            command.Parameters.Add("@Appellation", OleDbType.VarChar);

            command.Parameters["@Appellation"].Value = txtAppellation.Text;

            command.Parameters.Add("@Birthday", OleDbType.Date);

            command.Parameters["@Birthday"].Value = dtpBirthday.Value;

            command.Parameters.Add("@EmployDay", OleDbType.Date);

            command.Parameters["@EmployDay"].Value = dtpEmployDay.Value;

            command.Parameters.Add("@Country", OleDbType.VarChar);

            command.Parameters["@Country"].Value = txtCountry.Text;

            command.Parameters.Add("@City", OleDbType.VarChar);

            command.Parameters["@City"].Value = txtCity.Text;

            command.Parameters.Add("@Area", OleDbType.VarChar);

            command.Parameters["@Area"].Value = txtArea.Text;

            command.Parameters.Add("@Address", OleDbType.VarChar);

            command.Parameters["@Address"].Value = txtAddress.Text;

            command.Parameters.Add("@Zipcode", OleDbType.VarChar);

            command.Parameters["@Zipcode"].Value = txtZipcode.Text;

            command.Parameters.Add("@Phone", OleDbType.VarChar);

            command.Parameters["@Phone"].Value = txtPhone.Text;

            command.Parameters.Add("@FullName", OleDbType.VarChar);

            command.Parameters["@FullName"].Value = cbEmployee.Text;

            command.Connection = conn;

            try

            {

                command.ExecuteNonQuery();

                MessageBox.Show("修改成功！");

            }

            catch (Exception ex)

            {

                MessageBox.Show("修改失败：" + ex.Message);

            }

        }

 

        //删除雇员信息

        private void btnDel_Click(object sender, EventArgs e)

        {

            OleDbCommand command = new OleDbCommand();

            command.CommandText = "delete * from 雇员 where 姓氏+名字='" + cbEmployee.Text + "'";

            command.Connection = conn;

            try

            {

                command.ExecuteNonQuery();

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

                //删除后重新列出雇员

                listEmployee();

            }

 

            catch (Exception ex)

            {

                MessageBox.Show("删除失败：" + ex.Message);

            }

        }

 

        private void btnExit_Click(object sender, EventArgs e)

        {

            this.Close();

        }

 

        private void cbEmployee_SelectedIndexChanged(object sender, EventArgs e)

        {

            getEmployeeInfo(cbEmployee.Text);

        }

 

        //显示当前在cbEmployee中的雇员全名对应的雇员信息

        private void getEmployeeInfo(string fullname)

        {

            OleDbCommand odcommand = new OleDbCommand();

            odcommand.CommandText = "select * from 雇员 where 姓氏+名字='" + cbEmployee.Text + "'";

            odcommand.Connection = conn;

 

            OleDbDataReader reader ;

            reader = odcommand.ExecuteReader(CommandBehavior.SingleResult | CommandBehavior.SingleRow);

            if (reader.HasRows)

            {

                reader.Read();

                txtDuties.Text = (string)reader["职务"];

                txtAppellation.Text = (string)reader["尊称"];

                dtpBirthday.Value =(DateTime) reader["出生日期"];

                dtpEmployDay.Value = (DateTime)reader["雇用日期"];

                txtCountry.Text = (string)reader["国家"];

                txtCity.Text = (string)reader["城市"];

                txtArea.Text = (string)reader["地区"];

                txtAddress.Text = (string)reader["地址"];

                txtZipcode.Text = (string)reader["邮政编码"];

                txtPhone.Text = (string)reader["家庭电话"];

            }

            else

                MessageBox.Show("错误的编号");

            reader.Close();

            currentName = cbEmployee.Text;

        }

运行结果如下图所示：

图19-37 新增雇员信息

学习更多vb.net知识，请参看vb.net 教程 目录

学习更多C#知识，请参看C#教程 目录