using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.OleDb;
namespace 数据集和数据适配器示例
{
public partial class Form1 : Form
{
private OleDbConnection conn;
private OleDbDataAdapter da;
private DataSet ds;
public Form1()
{
InitializeComponent();
}
private void ListBoxFill()
{
if (this.ds.Tables[0].Rows != null) this.ds.Tables[0].Rows.Clear();
this.listBoxEmployee.DataSource = null;
this.listBoxEmployee.Items.Clear();
this.da.Fill(ds, "雇员");
this.listBoxEmployee.DataSource = ds.Tables["雇员"];
this.listBoxEmployee.DisplayMember = "姓名";
this.listBoxEmployee.ValueMember = "雇员ID";
}
private void Form1_Load(object sender, EventArgs e)
{
string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Northwind.mdb";
string queryString = "SELECT 雇员ID,姓氏,名字,姓氏+名字 AS 姓名 FROM 雇员";
this.conn = new OleDbConnection(connectionString);
this.ds = new DataSet();
this.ds.Tables.Add("雇员");
this.da = new OleDbDataAdapter(queryString, conn);
//对数据适配器设置插入命令,INSERT语句中包含的问好表示查询参数
this.da.InsertCommand = new OleDbCommand("INSERT INTO 雇员(姓氏,名字) VALUES(?,?)", conn);
this.da.InsertCommand.Parameters.AddWithValue("姓氏", "");
this.da.InsertCommand.Parameters.AddWithValue("名字", "");
//对数据适配器设置更新命令,UPDATE语句中包含的问好表示查询参数
this.da.UpdateCommand = new OleDbCommand("UPDATE 雇员 SET 姓氏=?,名字=? WHERE 雇员ID=?", conn);
this.da.UpdateCommand.Parameters.AddWithValue("姓氏","");
this.da.UpdateCommand.Parameters.AddWithValue("名字","");
this.da.UpdateCommand.Parameters.AddWithValue("雇员ID", -1);
//对数据适配器设置删除命令
this.da.DeleteCommand = new OleDbCommand("DELETE * FROM 雇员 WHERE 雇员ID=?", conn);
this.da.DeleteCommand.Parameters.AddWithValue("雇员ID", -1);
//对雇员姓名信息填充列表框
this.ListBoxFill();
}
private void buttonAdd_Click(object sender, EventArgs e)
{
if (this.textBoxLastName.Text == "" || this.textBoxFirstName.Text == "")
{
this.labelMsg.Text = "姓氏和名字不能为空!!";
return;
}
this.da.InsertCommand.Parameters["姓氏"].Value = this.textBoxLastName.Text;
this.da.InsertCommand.Parameters["名字"].Value = this.textBoxFirstName.Text;
DataRow dr = this.ds.Tables[0].NewRow();
dr["姓氏"] = this.textBoxLastName.Text;
dr["名字"] = this.textBoxFirstName.Text;
this.ds.Tables[0].Rows.Add(dr);
this.da.Update(this.ds, "雇员");
this.ListBoxFill();
this.listBoxEmployee.SelectedIndex = this.listBoxEmployee.Items.Count - 1;
this.labelMsg.Text = "新纪录添加成功!";
this.textBoxLastName.Text = "";
this.textBoxFirstName.Text = "";
this.textBoxLastName.Focus();
}
private void buttonEdit_Click(object sender, EventArgs e)
{
int selectedIndex = this.listBoxEmployee.SelectedIndex;
if (this.textBoxLastName.Text == "" || this.textBoxFirstName.Text == "")
{
this.labelMsg.Text = "姓氏和名字不能为空!!"; return;
}
this.da.UpdateCommand.Parameters["雇员ID"].Value = this.listBoxEmployee.SelectedValue;
this.da.UpdateCommand.Parameters["姓氏"].Value=this.textBoxLastName.Text;
this.da.UpdateCommand.Parameters["名字"].Value=this.textBoxFirstName.Text;
//获取当前选择的数据行
DataRow dr = this.ds.Tables["雇员"].Select("雇员ID=" + this.listBoxEmployee.SelectedValue)[0];
dr["姓氏"] = this.textBoxLastName.Text;
dr["名字"] = this.textBoxFirstName.Text;
this.da.Update(this.ds, "雇员");
this.listBoxEmployee.SelectedIndex = selectedIndex;
this.labelMsg.Text = "选定的记录已被更新。";
this.ListBoxFill();
}
private void buttonDelete_Click(object sender, EventArgs e)
{
this.da.DeleteCommand.Parameters["雇员ID"].Value = this.listBoxEmployee.SelectedValue;
DataRow dr = this.ds.Tables[0].Select("雇员ID=" + this.listBoxEmployee.SelectedValue)[0];
dr.Delete();
this.da.Update(this.ds, "雇员");
this.ListBoxFill();
this.labelMsg.Text = "选定的记录已被删除";
}
private void buttonExit_Click(object sender, EventArgs e)
{
Application.Exit();
}
private void listBoxEmployee_Click(object sender, EventArgs e)
{
DataRow dr = null;
if (this.listBoxEmployee.SelectedIndex != -1)
{
dr = this.ds.Tables[0].Select("雇员ID=" + this.listBoxEmployee.SelectedValue)[0];
this.textBoxLastName.Text = dr["姓氏"].ToString();
this.textBoxFirstName.Text = dr["名字"].ToString();
}
}
}
}
数据集和数据适配器示例
最新推荐文章于 2017-12-28 14:48:17 发布