.NET: DataBinding
This example was written as a complete guide to Databinding in C# + VB.Net. I wrote it in VS2008 for wider version compatibility + it will compile + run as intended in all versions from 2008 to 2013 (+ beyond I hope...).
This example addresses many questions that are frequently asked in the forums I regularly use.
Download
You can download the example project: here
Project
It uses an Access 2007 Database with 2 Tables, but I've included (unused) code for SQL Server + MySQL Databases too, + it will also work, with minimal editing, with either an SQL or MySQL Database with the same structure, Tables, + Fields.
The 2 Database Tables I've read in their entirety into 2 DataTables, which I've then added to a DataSet + set up a DataRelation between the PrimaryKey column in the institutes Table + the corresponding ForeignKey column in the students Table. By using 2 BindingSources, with the DataSource of the first being the DataSet + the DataMember being the institutes DataTable, then the second BindingSource having the first BindingSource as its DataSource + the DataMember being the DataRelation, changing the SelectedItem in the ComboBox which is bound to the first BindingSource, causes the DataGridView to display only those students that are studying at the selected institute.
I also bound 2 TextBoxes to the second BindingSource (which is what the DataGridView is bound to), with the DataMember of the first TextBox being the student_name field + the DataMember of the second TextBox being the student_semester field. Changing the CurrentRow in the DataGridView causes the values in the TextBoxes to change to those of the CurrentRow, + they are fully editable.
There is also a second Form for editing the institutes Table. There you can add, remove, or rename institutes, + any changes are reflected when you return to the main Form.
Finally I added code in the Form_Closing event of Form1 which saves any changes back to the 2 Tables in the DataBase.
Main form
This is the code for the main form.
In Form_Load, It reads all of the data from the 2 tables. Then sets up the AutoIncrements for the AutoNumber field in both DataTables, then adds the DataRelation. Lastly it binds the ComboBox, DataGridView, + Textboxes.
In Form_FormClosing it saves the data back to the Database.
Button1_Click shows the Edit Institutes Form, then updates the ComboBox when the Form is closed.
The TextBoxes share a _Leave event, which ensures the DataGridView is immediately updated. :
Imports System.Data.SqlClient
Imports MySql.Data.MySqlClient
Imports System.Data.OleDb
Public Class Form1
Dim dtStudents As DataTable
Dim dtInstitutes As DataTable
Dim daStudents As OleDbDataAdapter
Dim daInstitutes As OleDbDataAdapter
'Dim daStudents As SqlDataAdapter
'Dim daInstitutes As SqlDataAdapter
'Dim daStudents As MySqlDataAdapter
'Dim daInstitutes As MySqlDataAdapter
Dim bs1 As BindingSource
Dim bs2 As BindingSource
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'sql server
'@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
'Dim con As New SqlConnection("connection string")
'daStudents = New SqlDataAdapter("SELECT * FROM students", con)
'Dim ds As New DataSet
'dtStudents = New DataTable("students")
'daStudents.Fill(dtStudents)
'ds.Tables.Add(dtStudents)
'Dim cb As New SqlCommandBuilder(daStudents)
'daInstitutes = New SqlDataAdapter("SELECT * FROM institutes", con)
'dtInstitutes = New DataTable("institutes")
'daInstitutes.Fill(dtInstitutes)
'ds.Tables.Add(dtInstitutes)
'cb = New SqlCommandBuilder(daInstitutes)
'@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
'mysql
'@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
'Dim con As New MySqlConnection("connection string")
'daStudents = New MySqlDataAdapter("SELECT * FROM students", con)
'Dim ds As New DataSet
'dtStudents = New DataTable("students")
'daStudents.Fill(dtStudents)
'ds.Tables.Add(dtStudents)
'Dim cb As New MySqlCommandBuilder(daStudents)
'daInstitutes = New MySqlDataAdapter("SELECT * FROM institutes", con)
'dtInstitutes = New DataTable("institutes")
'daInstitutes.Fill(dtInstitutes)
'ds.Tables.Add(dtInstitutes)
'cb = New MySqlCommandBuilder(daInstitutes)
'@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
'oledb
'@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
Dim con As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=testDB2.accdb;Persist Security Info=False;")
daStudents = New OleDbDataAdapter("SELECT * FROM students", con)
Dim ds As New DataSet
dtStudents = New DataTable("students")
daStudents.Fill(dtStudents)
Dim cb As New OleDbCommandBuilder(daStudents)
ds.Tables.Add(dtStudents)
daInstitutes = New OleDbDataAdapter("SELECT * FROM institutes", con)
dtInstitutes = New DataTable("institutes")
daInstitutes.Fill(dtInstitutes)
cb = New OleDbCommandBuilder(daInstitutes)
ds.Tables.Add(dtInstitutes)
'@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
dtStudents.Columns("student_id").AutoIncrement = True
dtInstitutes.Columns("institute_id").AutoIncrement = True
dtStudents.Columns(0).AutoIncrementSeed = dtStudents.Rows.Cast(Of DataRow).Max(Function(dr As DataRow) CInt(dr.Item(0))) + 1
dtStudents.Columns(0).AutoIncrementStep = 1
dtInstitutes.Columns(0).AutoIncrementSeed = dtInstitutes.Rows.Cast(Of DataRow).Max(Function(dr) CInt(dr.Item(0))) + 1
dtInstitutes.Columns(0).AutoIncrementStep = 1
ds.Relations.Add(New DataRelation("relation", ds.Tables("institutes").Columns("institute_id"), ds.Tables("students").Columns("institute_id")))
bs1 = New BindingSource(ds, "institutes")
ComboBox1.DisplayMember = "institute_name"
ComboBox1.ValueMember = "institute_id"
ComboBox1.DataSource = bs1
bs2 = New BindingSource(bs1, "relation")
DataGridView1.DataSource = bs2
DataGridView1.Columns(0).ReadOnly = True 'autonumber primary key
DataGridView1.Columns(3).Visible = False 'institute_id field
TextBox1.DataBindings.Add("Text", bs2, "student_name")
TextBox2.DataBindings.Add("Text", bs2, "student_semester")
End Sub
Private Sub Form1_FormClosing(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
Button1.Select() 'move focus from dgv + textboxes
DataGridView1.EndEdit()
daStudents.Update(dtStudents)
daInstitutes.Update(dtInstitutes)
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Form2.DataGridView1.DataSource = dtInstitutes
Form2.DataGridView1.Columns(0).ReadOnly = True 'autonumber primary key
Form2.ShowDialog()
ComboBox1.ResetBindings()
End Sub
Private Sub TextBoxes_Leave(ByVal sender As Object, ByVal e As System.EventArgs) Handles TextBox1.Leave, TextBox2.Leave
bs2.EndEdit() 'update dgv current row values immediately
End Sub
End Class
Dialog
This is the code for the Dialog Form.
It just handles the _FormClosing event to ensure any changes are saved:
Public Class Form2
Private Sub Form2_FormClosing(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosingEventArgs) Handles Me.FormClosing
Button2.Select() 'move focus from dgv
DataGridView1.EndEdit()
End Sub
End Class