VB.NET Entity Framework 6 basic data operations Window Forms
Introduction
Many getting starting tutorials for Entity Framework are generally written with C# rather than VB.NET, by following the code sample here a Windows Form developer can learn basic read data, edit data, add data and remove data using Visual Basic, Entity Framework 6 presented in a Windows Form using a ListView and TabControl as shown in the tile screenshot.
Reverse engineering
Reverse engineering is a process of creating one class per table in a database along with setting up navigation properties for a relational database along with gluing everything together with a DbContext.
Before reverse engineering a database, make sure each table has a primary auto incrementing primary key and for relational data e.g. Orders to Order details there is a relationship setup between the two tables and if business rules require to delete a parent table if the child table doesn't have rows for the parent and reverse to setup cascading rules. This is best done when working with SQL-Server to use SSMS. Also, ensure proper indexing has been done for tables in the database which allows Entity Framework to form better queries.
For first time reverse engineering from Solution Explorer (and should be considered a throwaway project for learning)
- Right click, select add new item.
- Select Data tab.
- Enter Context for model name, click add.
- Select "Code First from database", click next
- Create a connection or use a prior connection, click next
- Select tables, click finish.
Once completed there will be a class for each table and a DbContext class. At this point the following picks up from the steps above except in the code provided classes for tables have been moved to a folder name Models in the project so keep things clean.
Microsoft Blog code sample
The following code is based off Entity Framework blog examples, main table represents a basic blog and the child table are post for blogs which are linked together by BlogId which is a primary key in the blog table and a foreign key in the post table/class. From here all code is non Microsoft, the idea is this will help to follow along with C# code samples on Microsoft using the blog database.
Note, before running the code sample first create the database and populate tables using the script provided.
Implementation notes
A DbContext when used in a Windows Form can be used with a using statement local scoped to a button Click event or declared privately scoped at form level. In the code sample the DbContext is declared at form level. This keeps things simple as the DbContext keeps track of changes while using locally scoped DbContext additional work is required to allow the local DbContext to know the state of a entity while form level scope the change tracker "knows" the state of each object. To see basic change tracker see the following GitHub repository which used the change tracker in custom DbContext class.
Walkthrough a CRUD form
Data will be presented in a ListView which is not data aware so to keep track of blogs and post the following class is used to remember both primary and foreign keys of the current ListViewItem.
Namespace Classes
''' <summary>
''' Used to store current post and parent blog id
''' in a ListView item.
''' </summary>
Public Class PostTag
Public Property BlogId As Integer
Public Property PostId As Integer
End Class
End Namespace
Reading
The following method loads all blogs and post on form Shown event and serves as a refresh for the ListView when a new blog is added rather than simply adding a new ListViewItem which is not part of the learning experience for working with Entity Framework.
''' <summary>
''' Load blogs and post into ListView
''' </summary>
Private Sub PopulateAllBlogsTab()
BlogListView.BeginUpdate()
Try
'
' Clear to refresh rather than appending
' which may result in duplicates.
'
BlogListView.Items.Clear()
Dim index As Integer = 0
Dim groupName As String = ""
Dim blogs = _context.Blogs.ToList()
For Each blog As Blog In blogs
index += 1
groupName = $"Group{index}"
Dim currentGroup = New ListViewGroup(blog.BlogId.ToString(),
HorizontalAlignment.Left) With {
.Header = blog.Url,
.Name = groupName
}
For Each post As Post In blog.Posts
Dim listViewBlogItem = New ListViewItem(
{
post.Title,
post.Content
}, -1)
listViewBlogItem.Group = currentGroup
listViewBlogItem.Tag = New PostTag With {
.BlogId = blog.BlogId,
.PostId = post.PostId
}
BlogListView.Items.Add(listViewBlogItem)
Next
BlogListView.Groups.Add(currentGroup)
Next
BlogListView.AutoResizeColumns(ColumnHeaderAutoResizeStyle.HeaderSize)
Finally
BlogListView.EndUpdate()
End Try
If BlogListView.Items.Count > 0 Then
BlogListView.FocusedItem = BlogListView.Items(0)
BlogListView.Items(0).Selected = True
ActiveControl = BlogListView
End If
End Sub
Key points for code block above. The following line takes care of loading all blogs and post where without the .ToList Entity Framework will not execute a query to return data.
Dim blogs = _context.Blogs.ToList()
The following stored primary and foreign keys for each ListViewItem
listViewBlogItem.Tag = New PostTag With {
.BlogId = blog.BlogId,
.PostId = post.PostId
}
When the current blog entity is needed the following returns the entity by primary key. The Find method seeks by the primary key, in this case in the Blog collection. CurrentUrlTextBox is a TextBox which had it's Tag property set to a instance of PostTag class.
Dim blog As Blog = _context.Blogs.Find(CType(CurrentUrlTextBox.Tag, PostTag).BlogId)
Each time the selected item changes in the ListView the following code sets up values in the edit tab to access the selected item in the ListView. This is a simple way to keep the TextBox in sync with the ListView other than on save/update which will be covered later.
''' <summary>
''' Setup TextBox for editing current blog url
''' </summary>
''' <param name="sender"></param>
''' <param name="e"></param>
Private Sub BlogListView_SelectedIndexChanged(sender As Object, e As EventArgs) _
Handles BlogListView.SelectedIndexChanged
If BlogListView.SelectedItems.Count > 0 Then
_blogListViewSelectedIndex = BlogListView.Items.IndexOf(BlogListView.SelectedItems(0))
Dim postTag = CType(BlogListView.SelectedItems(0).Tag, PostTag)
Dim blog As Blog = _context.Blogs.Find(postTag.BlogId)
CurrentUrlTextBox.Tag = postTag
CurrentUrlTextBox.Text = blog.Url
Dim post = _context.Posts.Find(postTag.PostId)
CurrentPostTitleTextBox.Tag = postTag
CurrentPostTitleTextBox.Text = post.Title
CurrentPostContentTextBox.Text = post.Content
End If
End Sub
Editing
To edit the current ListView item, the code block above sets up three TextBoxes. To save/update the following line retrieves the blog using the Tag property of a TextBox.
Dim blog As Blog = _context.Blogs.Find(CType(CurrentUrlTextBox.Tag, PostTag).BlogId)
Update the Url property from a TextBox.
blog.Url = CurrentUrlTextBox.Text
Push update to the database table.
_context.SaveChanges()
Caveat, SaveChanges returns an Integer which represents change count, in this case 1 is successful, -1 failure. The following updates the current ListView group header.
BlogListView.SelectedItems(0).Group.Header = CurrentUrlTextBox.Text
The following uses the same pattern, this time for updating a post selected in the ListView.
''' <summary>
''' Update current selected post in ListView
''' </summary>
''' <param name="sender"></param>
''' <param name="e"></param>
Private Sub UpdatePostButton_Click(sender As Object, e As EventArgs) _
Handles UpdatePostButton.Click
Dim postTag As PostTag = CType(CurrentPostTitleTextBox.Tag, PostTag)
Dim post As Post = _context.Posts.Find(postTag.PostId)
post.Title = CurrentPostTitleTextBox.Text
post.Content = CurrentPostContentTextBox.Text
_context.SaveChanges()
Dim listViewItem As ListViewItem = BlogListView.Items(_blogListViewSelectedIndex)
listViewItem.SubItems(0).Text = CurrentPostTitleTextBox.Text
listViewItem.SubItems(1).Text = CurrentPostContentTextBox.Text
End Sub
Adding
To add a new record simple create a new instance of the class, in this case a Blog and Post will be added.
Dim blog As New Blog With {
.Url = NewBlogUrlTextBox.Text
}
Dim post As New Post With {
.Title = FirstPostTitleTextBox.Text,
.Content = FirstPostContentTextBox.Text
}
blog.Posts.Add(post)
_context.Blogs.Add(blog)
_context.SaveChanges()
By adding the new Post to the list of Post for the blog Entity Framework will first perform an insert, get the new blog primary key and use it for the new Post during SaveChanges method.
Removing a blog and all post
Removing records is done using the Remove or RemoveRange method. In the following example the blog is removed using Remove while Post use RemoveAll rather than iterating a list of Post. Finally SaveChanges commits the hard deletes.
_blogListViewSelectedIndex = BlogListView.Items.IndexOf(BlogListView.SelectedItems(0))
Dim postTag = CType(BlogListView.SelectedItems(0).Tag, PostTag)
Dim blog As Blog = _context.Blogs.Find(postTag.BlogId)
'
' Get all post for current blog
'
Dim postList = _context.Posts.
Where(Function(post) post.BlogId.Value = postTag.BlogId).ToList()
'
' Mark for hard delete
'
_context.Blogs.Remove(blog)
'
' Mark all post for hard delete
'
_context.Posts.RemoveRange(postList)
_context.SaveChanges()
Summary
Logic and code has been presented in basic format to see how easy it is to start using Entity Framework in Window Forms projects.
See Also
VB.NET Entity Framework Portal
External references
Microsoft: Fluent API with VB.NET
Entity Framework 6 - Knowledge Base (KB)
Source code
Clone the following GitHub repository or download, unzip and open in Visual Studio.