Share via


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.