多表操作是对数据库常用的一种操作,在.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("id