š VB.NET Phonebook Example Using Disconnected Mode (with MS Access Database)
Welcome back! If you've already explored our Connected Mode tutorial, you're ready to level up with Disconnected Mode in ADO.NET. Unlike Connected Mode, where we constantly interact with the database, Disconnected Mode lets us load data into memory, work on it offline, and update the database only when needed. This is perfect for apps like our phonebook, especially in scenarios with limited database access.
In this tutorial, we'll adapt our Phonebook project to use Disconnected Mode, leveraging key ADO.NET components:
OleDbDataAdapter: Transfers data between the database and our in-memoryDataSet.DataSet: Stores data offline, acting like an in-memory database.CommandBuilder: Automatically generates SQL commands for inserting, updating, and deleting records.
Ready to dive in? Let's get started!
š Project Setup
We're using the same setup as our Connected Mode tutorial, so if you're familiar with that, you're good to go. Here's a quick recap:
Database: We have an MS Access database named PhoneB.accdb with a table called Phone1. The table includes columns for:
Fname,Lname,Country,City,Phone,Mobile,Lemail(all Short Text)Lphoto(OLE Object for storing image byte arrays)
Form: Our form, MainForm, includes:
- TextBoxes:
TxtFname,TxtLname,TxtCountry,TxtCity,TxtPhone,TxtMobile,TxtLemail - PictureBox:
P_Lphotofor displaying photos - Buttons:
BtnNew,BtnSave,BtnEdit,BtnDelete,BtnSearch,BtnLoad,BtnExit - DataGridView:
DG1to display all records
š” Step 1: Setting Up Global Variables and Form Load
First, we need to define our global variables and initialize the form. This sets up the connection to our database and prepares the DataSet for offline work.
Imports System.Data.OleDb
Imports System.IO
Public Class MainForm
' Connection string for the MS Access database
Dim connStr As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=PhoneB.accdb"
' Connection object
Dim conn As New OleDbConnection(connStr)
' DataAdapter for data transfer
Dim da As OleDbDataAdapter
' DataSet to hold data in memory
Dim ds As New DataSet()
' CommandBuilder for generating SQL commands
Dim cb As OleDbCommandBuilder
' Form Load event to initialize the form
Private Sub MainForm_Load(sender As Object, e As EventArgs) Handles MyBase.Load
' Set default image for PictureBox
P_Lphoto.InitialImage = My.Resources.Image_no
' Load data into DataSet
LoadDataIntoDataSet()
End Sub
š” Step 2: Loading Data into DataGridView
Next, we load data from the database into our DataSet and display it in the DataGridView. This is done once, and all operations happen in memory until we update the database.
Sub LoadDataIntoDataSet()
Try
' Create DataAdapter to fetch data from Phone1 table
da = New OleDbDataAdapter("SELECT * FROM Phone1", conn)
' Generate SQL commands automatically
cb = New OleDbCommandBuilder(da)
' Clear existing data in DataSet
ds.Clear()
' Fill DataSet with data
da.Fill(ds, "Phone1")
' Bind DataTable to DataGridView
DG1.DataSource = ds.Tables("Phone1")
' Customize DataGridView headers and image column
RenameDG1Headers()
ResizeImageColumn()
Catch ex As Exception
MsgBox("Error loading data: " & ex.Message)
End Try
End Sub
š” Step 3: Displaying Data on Row Click
When a user clicks a row in the DataGridView, we populate the text boxes and PictureBox with the selected record's data, all from the in-memory DataSet.
Private Sub DG1_CellClick(sender As Object, e As DataGridViewCellEventArgs) Handles DG1.CellClick
If e.RowIndex >= 0 Then
Dim row As DataGridViewRow = DG1.Rows(e.RowIndex)
' Populate text boxes with row data
TxtFname.Text = row.Cells("Fname").Value?.ToString()
TxtLname.Text = row.Cells("Lname").Value?.ToString()
TxtCountry.Text = row.Cells("Country").Value?.ToString()
TxtCity.Text = row.Cells("City").Value?.ToString()
TxtPhone.Text = row.Cells("Phone").Value?.ToString()
TxtMobile.Text = row.Cells("Mobile").Value?.ToString()
TxtLemail.Text = row.Cells("Lemail").Value?.ToString()
' Load photo if available
If row.Cells("Lphoto").Value IsNot DBNull.Value Then
Dim imgData As Byte() = CType(row.Cells("Lphoto").Value, Byte())
Using ms As New MemoryStream(imgData)
P_Lphoto.Image = Image.FromStream(ms)
End Using
Else
P_Lphoto.Image = Nothing
End If
End If
End Sub
š” Step 4: Saving a New Record
To add a new contact, we create a new row in the DataTable, fill it with data from the text boxes, and update the database using the DataAdapter.
Private Sub BtnSave_Click(sender As Object, e As EventArgs) Handles BtnSave.Click
If String.IsNullOrEmpty(TxtFname.Text) OrElse String.IsNullOrEmpty(TxtLname.Text) Then
MsgBox("First and Last name are required.")
Exit Sub
End If
Try
' Create new row in DataTable
Dim newRow As DataRow = ds.Tables("Phone1").NewRow()
' Fill row with text box data
newRow("Fname") = TxtFname.Text
newRow("Lname") = TxtLname.Text
newRow("Country") = TxtCountry.Text
newRow("City") = TxtCity.Text
newRow("Phone") = TxtPhone.Text
newRow("Mobile") = TxtMobile.Text
newRow("Lemail") = TxtLemail.Text
' Handle photo if present
If P_Lphoto.Image IsNot Nothing Then
Using ms As New MemoryStream()
P_Lphoto.Image.Save(ms, Imaging.ImageFormat.Jpeg)
newRow("Lphoto") = ms.ToArray()
End Using
Else
newRow("Lphoto") = DBNull.Value
End If
' Add row to DataTable and update database
ds.Tables("Phone1").Rows.Add(newRow)
da.Update(ds, "Phone1")
LoadDataIntoDataSet()
ClearAll()
MsgBox("Saved successfully.")
Catch ex As Exception
MsgBox("Error saving data: " & ex.Message)
End Try
End Sub
š” Step 5: Editing a Record
To edit a contact, we find the row in the DataTable using the first name, update its values, and save changes to the database.
Private Sub BtnEdit_Click(sender As Object, e As EventArgs) Handles BtnEdit.Click
Try
Dim fnameKey As String = TxtFname.Text
Dim rows() As DataRow = ds.Tables("Phone1").Select("Fname = '" & fnameKey.Replace("'", "''") & "'")
If rows.Length > 0 Then
With rows(0)
' Update row with new values
.Item("Lname") = TxtLname.Text
.Item("Country") = TxtCountry.Text
.Item("City") = TxtCity.Text
.Item("Phone") = TxtPhone.Text
.Item("Mobile") = TxtMobile.Text
.Item("Lemail") = TxtLemail.Text
' Update photo if present
If P_Lphoto.Image IsNot Nothing Then
Using ms As New MemoryStream()
P_Lphoto.Image.Save(ms, Imaging.ImageFormat.Jpeg)
.Item("Lphoto") = ms.ToArray()
End Using
Else
.Item("Lphoto") = DBNull.Value
End If
End With
' Save changes to database
da.Update(ds, "Phone1")
LoadDataIntoDataSet()
ClearAll()
MsgBox("Record updated.")
Else
MsgBox("Record not found for update.")
End If
Catch ex As Exception
MsgBox("Error editing record: " & ex.Message)
End Try
End Sub
š” Step 6: Deleting a Record
To delete a contact, we find the row by first name, mark it for deletion, and update the database.
Private Sub BtnDel_Click(sender As Object, e As EventArgs) Handles BtnDel.Click
Try
Dim fnameKey As String = TxtFname.Text
Dim rows() As DataRow = ds.Tables("Phone1").Select("Fname = '" & fnameKey.Replace("'", "''") & "'")
If rows.Length > 0 Then
' Mark row for deletion
rows(0).Delete()
' Update database
da.Update(ds, "Phone1")
LoadDataIntoDataSet()
ClearAll()
MsgBox("Record deleted.")
Else
MsgBox("Record not found for deletion.")
End If
Catch ex As Exception
MsgBox("Error deleting record: " & ex.Message)
End Try
End Sub
š” Step 7: Searching by First Name
Searching is fast in Disconnected Mode since all data is in memory. We use the Select method to find a record by first name and display it.
Private Sub BtnSearch_Click(sender As Object, e As EventArgs) Handles BtnSearch.Click
Dim fnameKey As String = InputBox("Enter First name.")
Dim rows() As DataRow = ds.Tables("Phone1").Select("Fname = '" & fnameKey.Replace("'", "''") & "'")
If rows.Length > 0 Then
With rows(0)
' Populate text boxes with found record
TxtFname.Text = .Item("Fname").ToString()
TxtLname.Text = .Item("Lname").ToString()
TxtCountry.Text = .Item("Country").ToString()
TxtCity.Text = .Item("City").ToString()
TxtPhone.Text = .Item("Phone").ToString()
TxtMobile.Text = .Item("Mobile").ToString()
TxtLemail.Text = .Item("Lemail").ToString()
' Load photo if available
If Not IsDBNull(.Item("Lphoto")) Then
Dim imgBytes() As Byte = CType(.Item("Lphoto"), Byte())
Using ms As New MemoryStream(imgBytes)
P_Lphoto.Image = Image.FromStream(ms)
End Using
Else
P_Lphoto.Image = Nothing
End If
End With
Else
MsgBox("Record not found.")
End If
End Sub
š Final Notes
- Photo Handling: We use
FileStreamand byte arrays to store images in theLphotocolumn. - Error Handling: Always use Try/Catch blocks to catch and display errors gracefully.
- Resource Management: While this example uses a class-level connection, consider using
Usingblocks for connections to ensure proper disposal. - Scalability: Disconnected Mode is great for small datasets like this phonebook but may consume more memory for larger datasets.
That's it! You've built a fully functional phonebook app using VB.NET and ADO.NET in Disconnected Mode. This approach makes your app efficient and robust, especially in low-connectivity scenarios. Try out the code, download the project, and let us know in the comments if you have questions or ideas to enhance it. Happy coding!
No comments:
Post a Comment