|
|
|
| Title | Make a DataSet at run time without using any design time controls in VB .NET |
|---|
| Description | This example shows how to make a DataSet at run time without using any design time controls in VB .NET. |
|---|
| Keywords | DataSet, runtime, database, ADO.NET |
|---|
| Categories | VB.NET, Database |
|---|
|
|
|
When the form loads, the program composes a connect string and uses it to build an OleDbDataAdapter. It adds a table mapping to map the default (stupid) name Table to the selected table's real name Students. It then creates a DataSet and uses the adapter's Fill method to load data into it. It binds the DataSet's Students table to a DataGrid control so you can work with the data.
|
|
Private Const SELECT_STRING As String = _
"SELECT * FROM Students"
' Load the DataSet.
Private Sub Form1_Load(ByVal sender As System.Object, ByVal _
e As System.EventArgs) Handles MyBase.Load
' Compose the connect string.
Dim db_name As String = Application.StartupPath
db_name = db_name.Substring(0, _
db_name.LastIndexOf("\")) & "\Students.mdb"
connect_string = _
"Data Source=" & db_name & ";" & _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Persist Security Info=False"
' Create the SqlDataAdapter.
Dim data_adapter As New OleDbDataAdapter(SELECT_STRING, _
CONNECT_STRING)
' Map "Table" to "Students."
data_adapter.TableMappings.Add("Table", "Students")
' Fill the DataSet.
m_DataSet = New DataSet
data_adapter.Fill(m_DataSet)
' Bind the DataGrid control to the Students DataTable.
DataGrid1.SetDataBinding(m_DataSet, "Students")
End Sub
|
|
|
When the form is closing, the program creates a new OleDbAdapter and adds the same table mapping as before. It then makes a CommandBuilder to automatically create INSERT, UPDATE, and DELETE commands for the data. It finishes by calling the adapter's Update method to save changes to the data into the database.
|
|
' Save the data.
Private Sub Form1_Closing(ByVal sender As Object, ByVal e _
As System.ComponentModel.CancelEventArgs) Handles _
MyBase.Closing
If m_DataSet.HasChanges() Then
' Create the DataAdapter.
Dim data_adapter As New _
OleDbDataAdapter(SELECT_STRING, CONNECT_STRING)
' Map Table to Students.
data_adapter.TableMappings.Add("Table", "Students")
' Make the CommandBuilder generate the
' insert, update, and delete commands.
Dim command_builder As New _
OleDbCommandBuilder(data_adapter)
' Uncomment this code to see the INSERT,
' UPDATE, and DELETE commands.
'Debug.WriteLine("*** INSERT ***")
'Debug.WriteLine(command_builder.GetInsertCommand.CommandText)
'Debug.WriteLine("*** UPDATE ***")
'Debug.WriteLine(command_builder.GetUpdateCommand.CommandText)
'Debug.WriteLine("*** DELETE ***")
'Debug.WriteLine(command_builder.GetDeleteCommand.CommandText)
' Save the changes.
data_adapter.Update(m_DataSet)
End If
End Sub
|
|
|
For more information on database programming in VB .NET, see my book Visual Basic .NET Database Programming.
|
| |
|
| |
|
|
