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#教程 目录