Multi-table operation is an operation for databases. In .NET development, I am afraid it is DataGrid for database operations, and talking about my experience.
Suppose there are two tables now (for convenience of explaining the Access database):
Student Table: Student (ID, Name, Age, T_ID)
Class Director Table: Teacher (T_ID, T_Name, T_age)
The T_ID in the student table indicates the class teacher corresponding to this student (here is a multi-to-one relationship)
First create an OLEDATADAPTER and initialize the following:
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", "age", new system.data. Common.datacolumnmapping ("ID", "Learn"), new system.data.common.datacolumnmapping ("name", "name", new system.data.common.Datacolumnmapping ("t_name", "teacher name") })}); This.oledbdataadapter1.UpdateCommand = this.OleDbcommand3;
Created query statement:
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;
Since it is a multi-table query, you must handwritten Updata, DELETE, INSERT statement, to facilitate usage UPDATA () method
THISOLEDBCOMMAND1.COMMANDTEXT = "delete from student where (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) ((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)), "ID", System.Data.DataRowVersion.ORIGINAL, NULL); this.Oledbcommand1.Parameters.Add (New System. Data.oledb.oledb Parameter ("ID3", System.Data.Oledb.oledbtype.varwchar, 10, System.Data.ParameterDirection.Input, False, ((System.byte) ((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.OLDBTYPE.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 (agn =?) AND (Name =?) AND (T_ID =?) OR (ID =?) AND (Name =?) AND (T_ID is NULL) OR (ID =?) AND (agn =? ) AND (NAME IS NULL) AND (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, 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)) ((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) ((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) ((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) ((((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) ((System.byte) (0)), "ID", System.Data.DataRowVersion.ORIINAL, NULL); this.OleDbcomm And3.Parameters.Add (new system.data.oledb.oledbParameter ("Original_Name1", System.Data.Oledb.oledbtype.varwchar, 10, System.Data.ParameterDirection.Input, False, ((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) ((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) ((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) ((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) (((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) ((System.byte) (0)), "T_ID", System.Data.DataRowVersion.original, NULL); then bind to 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.dataGridtableS.Addrang e (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 = "stopnt"; /// DataGridTextBoxColumn1 // this.dataGridTextBoxColumn1.format = ""; this.DataGridTextBoxColumn1.formatinfo = null;
this.dataGridTextBoxColumn1.HeaderText = "Student ID"; this.dataGridTextBoxColumn1.MappingName = "Student ID"; this.dataGridTextBoxColumn1.Width = 75; // // dataGridTextBoxColumn2 // this.dataGridTextBoxColumn2.Format = ""; this.dataGridTextBoxColumn2. FormatInfo = null; this.dataGridTextBoxColumn2.HeaderText = "name"; this.dataGridTextBoxColumn2.MappingName = "name"; this.dataGridTextBoxColumn2.Width = 75; // // dataGridTextBoxColumn3 // this.dataGridTextBoxColumn3.Format = ""; this. dataGridTextBoxColumn3.FormatInfo = null; this.dataGridTextBoxColumn3.HeaderText = "teacher's name"; this.dataGridTextBoxColumn3.MappingName = "teacher's name"; this.dataGridTextBoxColumn3.Width = 75; // // dataGridTextBoxColumn4 // here must be buried t_id, Because of the modification of the class teacher, it is equivalent to modifying his ID, it is not displayed, the display should be the name in the Teacher table, not id this.DataGridtextBoxColumn4.format = ""; this.DataGridTextBoxColumn4.formatinfo = null; this .DataGridTextBoxColumn4.Headertext = "t_id"; this.dataGridtextBoxColumn4.mappingname = "t_id"; this.dat aGridTextBoxColumn4.Width = 0; // // dataGridTextBoxColumn5 // this.dataGridTextBoxColumn5.Format = ""; this.dataGridTextBoxColumn5.FormatInfo = null; this.dataGridTextBoxColumn5.HeaderText = "Age"; this.dataGridTextBoxColumn5.MappingName = "Age"; THIS.DATAGRIDTEXTBOXCOLUMN5.Readonly = true; this.DataGridTextBoxColumn5.width = 75;
First read data to DataGrid
DataSet11.clear (); OLEDBDataAdapter1.fill (DataSet11);
Insert, modify
Then you can modify and insert the data directly on the DataGrid, call the Click event of the point saved button.
Try {OLEDBDataAdapter1.Update (DataSet11.student); MessageBox.show ("Data Update Success");} Catch {} Data automatically calls the corresponding UPDATA and Insert method
Delete operation
The delete operation is more troublesome, because you want to get the currently deleted line number
Add to DataGrid_Click () event
System.drawing.point Pt = New Point (E.x, E.Y); DataGrid.hittetStinfo HTI = this.dataGrid1.hittetest (Pt); x = hti.row;
The X here is a acquired line number, then you can call the deletion method.
Try {this.dataset11.student.rows [x] .delete (); // Here is the delete method this.dataGrid1.datasource = this.dataset11; // Re-write data set oledbdataadapter1.Update (dataset11.student); / / Call UPDATA () method
} Catch (Exception EX) {MessageBox.show (EX.TOString ());
It is worth mentioning that there is another method here.
this.datasetjhmx1.spxx.rows.removeat (x);
This method can delete a line in the DataGrid, but cannot delete the database, because the data read from the database from the database is saved in memory, the above method can only be deleted in the DataGrid, that is, memory Delete, but you can't delete the database.
