多表操作是对数据库常用的一种操作，在.net开发中，对数据库操作常用的恐怕也就是dataGrid了，这里说说我的经验。
假设现在有两张表如下(为了方便说明采用access数据库)：
学生表：student(id,name,age,t_id)
班主任表：teacher(t_id,t_name,t_age)
学生表中的t_id表示这个学生对应的班主任(这里是多对一的关系)
首先创建一个oleDataAdapter，并且初始化如下：
this.oleDbDataAdapter1.DeleteCommand = this.oleDbCommand1;
this.oleDbDataAdapter1.InsertCommand = this.oleDbCommand2;
this.oleDbDataAdapter1.SelectCommand = this.oleDbSelectCommand1;
this.oleDbDataAdapter1.TableMappings.AddRange(new System.Data.Common.DataTableMapping[] {
new System.Data.Common.DataTableMapping("Table", "student", new System.Data.Common.DataColumnMapping[] {
new System.Data.Common.DataColumnMapping("age", "年龄"),
new System.Data.Common.DataColumnMapping("id", "学号"),
new System.Data.Common.DataColumnMapping("name", "姓名"),
new System.Data.Common.DataColumnMapping("t_name", "教师姓名")})});
this.oleDbDataAdapter1.UpdateCommand = this.oleDbCommand3;
创建的查询语句：
this.oleDbSelectCommand1.CommandText = "SELECT student.age, student.id, student.name, student.t_id, teacher.t_name FROM (" +
"student INNER JOIN teacher ON student.t_id = teacher.t_id)";
this.oleDbSelectCommand1.Connection = this.oleDbConnection1;
由于是多表查询必须手工写updata,delete,insert语句，以方便使用updata()方法
this.oleDbCommand1.CommandText = "DELETE FROM student WHERE (id = ?) OR (id = ?) OR (id = ?) OR (id = ?)";
this.oleDbCommand1.Connection = this.oleDbConnection1;
this.oleDbCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("id", System.Data.OleDb.OleDbType.VarWChar, 10, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "id", System.Data.DataRowVersion.Original, null));
this.oleDbCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("id1", System.Data.OleDb.OleDbType.VarWChar, 10, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "id", System.Data.DataRowVersion.Original, null));
this.oleDbCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("id2", System.Data.OleDb.OleDbType.VarWChar, 10, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "id", System.Data.DataRowVersion.Original, null));
this.oleDbCommand1.Parameters.Add(new System.Data.OleDb.OleDbParameter("id3", System.Data.OleDb.OleDbType.VarWChar, 10, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "id", System.Data.DataRowVersion.Original, null));
//
// oleDbCommand2
//
this.oleDbCommand2.CommandText = "INSERT INTO student (age, id, name, t_id) VALUES (?, ?, ?, /'2/')";
this.oleDbCommand2.Connection = this.oleDbConnection1;
this.oleDbCommand2.Parameters.Add(new System.Data.OleDb.OleDbParameter("age", System.Data.OleDb.OleDbType.Integer, 0, "age"));
this.oleDbCommand2.Parameters.Add(new System.Data.OleDb.OleDbParameter("id", System.Data.OleDb.OleDbType.VarWChar, 10, "id"));
this.oleDbCommand2.Parameters.Add(new System.Data.OleDb.OleDbParameter("name", System.Data.OleDb.OleDbType.VarWChar, 10, "name"));
//
// oleDbCommand3
//
this.oleDbCommand3.CommandText = @"UPDATE student SET id = ?, age = ?, name = ?, t_id = ? WHERE (id = ?) AND (age = ?) AND (name = ?) AND (t_id = ?) OR (id = ?) AND (age IS NULL) AND (name = ?) AND (t_id IS NULL) OR (id = ?) AND (age = ?) AND (name IS NULL) AND (t_id = ?) OR (id = ?) AND (age IS NULL) AND (name IS NULL) AND (t_id = ?)";
this.oleDbCommand3.Connection = this.oleDbConnection1;
this.oleDbCommand3.Parameters.Add(new System.Data.OleDb.OleDbParameter("id", System.Data.OleDb.OleDbType.VarWChar, 10, "id"));
this.oleDbCommand3.Parameters.Add(new System.Data.OleDb.OleDbParameter("age", System.Data.OleDb.OleDbType.Integer, 0, "age"));
this.oleDbCommand3.Parameters.Add(new System.Data.OleDb.OleDbParameter("name", System.Data.OleDb.OleDbType.VarWChar, 10, "name"));
this.oleDbCommand3.Parameters.Add(new System.Data.OleDb.OleDbParameter("t_id", System.Data.OleDb.OleDbType.Integer, 0, "t_id"));
this.oleDbCommand3.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_id", System.Data.OleDb.OleDbType.VarWChar, 10, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "id", System.Data.DataRowVersion.Original, null));
this.oleDbCommand3.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_age", System.Data.OleDb.OleDbType.Integer, 0, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "age", System.Data.DataRowVersion.Original, null));
this.oleDbCommand3.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_name", System.Data.OleDb.OleDbType.VarWChar, 10, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "name", System.Data.DataRowVersion.Original, null));
this.oleDbCommand3.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_t_id", System.Data.OleDb.OleDbType.Integer, 0, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "t_id", System.Data.DataRowVersion.Original, null));
this.oleDbCommand3.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_id1", System.Data.OleDb.OleDbType.VarWChar, 10, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "id", System.Data.DataRowVersion.Original, null));
this.oleDbCommand3.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_name1", System.Data.OleDb.OleDbType.VarWChar, 10, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "name", System.Data.DataRowVersion.Original, null));
this.oleDbCommand3.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_id2", System.Data.OleDb.OleDbType.VarWChar, 10, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "id", System.Data.DataRowVersion.Original, null));
this.oleDbCommand3.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_age1", System.Data.OleDb.OleDbType.Integer, 0, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "age", System.Data.DataRowVersion.Original, null));
this.oleDbCommand3.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_t_id1", System.Data.OleDb.OleDbType.Integer, 0, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "t_id", System.Data.DataRowVersion.Original, null));
this.oleDbCommand3.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_id3", System.Data.OleDb.OleDbType.VarWChar, 10, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "id", System.Data.DataRowVersion.Original, null));
this.oleDbCommand3.Parameters.Add(new System.Data.OleDb.OleDbParameter("Original_t_id2", System.Data.OleDb.OleDbType.Integer, 0, System.Data.ParameterDirection.Input, false, ((System.Byte)(0)), ((System.Byte)(0)), "t_id", System.Data.DataRowVersion.Original, null));
然后和dataGrid进行绑定：
//
// dataGrid1
//
this.dataGrid1.DataMember = "student";
this.dataGrid1.DataSource = this.dataSet11;
this.dataGrid1.HeaderForeColor = System.Drawing.SystemColors.ControlText;
this.dataGrid1.Location = new System.Drawing.Point(48, 64);
this.dataGrid1.Name = "dataGrid1";
this.dataGrid1.Size = new System.Drawing.Size(440, 184);
this.dataGrid1.TabIndex = 6;
this.dataGrid1.TableStyles.AddRange(new System.Windows.Forms.DataGridTableStyle[] {
this.dataGridTableStyle1});
this.dataGrid1.MouseDown += new System.Windows.Forms.MouseEventHandler(this.dataGrid1_Click);
this.dataGrid1.Navigate += new System.Windows.Forms.NavigateEventHandler(this.dataGrid1_Navigate_1);
//
// dataSet11
//
this.dataSet11.DataSetName = "DataSet1";
this.dataSet11.Locale = new System.Globalization.CultureInfo("zh-CN");
//
// dataGridTableStyle1
//
this.dataGridTableStyle1.DataGrid = this.dataGrid1;
this.dataGridTableStyle1.GridColumnStyles.AddRange(new System.Windows.Forms.DataGridColumnStyle[] {
this.dataGridTextBoxColumn1,
this.dataGridTextBoxColumn2,
this.dataGridTextBoxColumn3,
this.dataGridTextBoxColumn4,
this.dataGridTextBoxColumn5});
this.dataGridTableStyle1.HeaderForeColor = System.Drawing.SystemColors.ControlText;
this.dataGridTableStyle1.MappingName = "student";
//
// dataGridTextBoxColumn1
//
this.dataGridTextBoxColumn1.Format = "";
this.dataGridTextBoxColumn1.FormatInfo = null;
this.dataGridTextBoxColumn1.HeaderText = "学号";
this.dataGridTextBoxColumn1.MappingName = "学号";
this.dataGridTextBoxColumn1.Width = 75;
//
// dataGridTextBoxColumn2
//
this.dataGridTextBoxColumn2.Format = "";
this.dataGridTextBoxColumn2.FormatInfo = null;
this.dataGridTextBoxColumn2.HeaderText = "姓名";
this.dataGridTextBoxColumn2.MappingName = "姓名";
this.dataGridTextBoxColumn2.Width = 75;
//
// dataGridTextBoxColumn3
//
this.dataGridTextBoxColumn3.Format = "";
this.dataGridTextBoxColumn3.FormatInfo = null;
this.dataGridTextBoxColumn3.HeaderText = "教师姓名";
this.dataGridTextBoxColumn3.MappingName = "教师姓名";
this.dataGridTextBoxColumn3.Width = 75;
//
// dataGridTextBoxColumn4
// 这里一定要埋入t_id,因为修改学生的班主任相当于修改他的ID，只是
//不显示出来，显示的应该是teacher表中的姓名，而不是ID
this.dataGridTextBoxColumn4.Format = "";
this.dataGridTextBoxColumn4.FormatInfo = null;
this.dataGridTextBoxColumn4.HeaderText = "t_id";
this.dataGridTextBoxColumn4.MappingName = "t_id";
this.dataGridTextBoxColumn4.Width = 0;
//
// dataGridTextBoxColumn5
//
this.dataGridTextBoxColumn5.Format = "";
this.dataGridTextBoxColumn5.FormatInfo = null;
this.dataGridTextBoxColumn5.HeaderText = "年龄";
this.dataGridTextBoxColumn5.MappingName = "年龄";
this.dataGridTextBoxColumn5.ReadOnly = true;
this.dataGridTextBoxColumn5.Width = 75;
首先读取数据到dataGrid
dataSet11.Clear();
oleDbDataAdapter1.Fill(dataSet11);
插入，修改操作
然后可以直接在dataGrid上面对数据进行修改和插入后，在点保存按钮的click事件中调用
try
{
oleDbDataAdapter1.Update(dataSet11.student);
MessageBox.Show("数据更新成功");
}
catch {}
数据会自动调用相应的updata和insert方法
删除操作
删除操作比较麻烦一点，因为要获取当前删除的行号
在dataGrid_Click（）事件中加入
System.Drawing.Point pt = new Point(e.X, e.Y);
DataGrid.HitTestInfo hti = this.dataGrid1.HitTest(pt);
x=hti.Row;
这里的x是获取的行号，然后可以调用删除方法
try
{
this.dataSet11.student.Rows[x].Delete();//这里是删除方法
this.dataGrid1.DataSource=this.dataSet11;//重新写回数据集
oleDbDataAdapter1.Update(dataSet11.student);//调用Updata()方法
}
catch(Exception ex)
{
MessageBox.Show(ex.ToString());
}
值得说明的是，这里有另外一种方法
this.dataSetJHMX1.SPXX.Rows.RemoveAt(x);
这种方法可以删除dataGrid中的一行，但是无法删除数据库中，那是因为dataGrid从数据库中读取的数据是保存在内存中的，以上的这种方法只能在dataGrid中删除，也就是内存中删除，却无法删除数据库。