Introduction


This article describes how to create a Windows Desktop application using VB.NET programming language and SQL-Server database. The intended audience are developers with no direction for working with SQL-Server database operations or developers currently utilizing TableAdapter or DataAdapter methods to work with SQL-Server databases.

The code presented will provide all the functionality needed in most solutions while some of the functionality may not be robust for each developer reading this which was done intentionally to provide a base to work from rather than a monolithic solution that needs reverse engineering. If familiar with Entity Framework then the base is similar to Entity Framework but without some of the niceties e.g. Entity Framework core opens connections as needed, built in save methods and a good deal more. Learning the basics here can assist in taking the next step to Entity Framework.

Building the solution

  • Requires Visual Studio 2015 or higher.
  • Requires an instance of SQL-Server installed or SQL-Express edition installed.
  • Run script.sql in SplashScreenIteratorDemo under the folder DataClasses. Make sure to change the server name from KARENS-PC to the name of your SQL-Server instance or to  .\SQLEXPRESS.

Description

A modified Microsoft NorthWind database (specifically for customer information) is used to

demonstrate interacting with data in the sample application.


Overview of the application

Display customer information within a DataGridView with the ability to view, add, edit and remove customers and related tables by code or cascading rules within the database. All data retrieved from the backend database are stored in class objects at runtime were the classes are defined in a separate class project which allows a developer to reuse these classes in other solutions.



There are several bases classes housed in a separate class project responsible for handling runtime exceptions. The exception class is inherited in the second class which is responsible for providing a connection string to the database used within the application. Both the exception and connection classes have protected properties which can be modified in the class (DataOperations class in the application project) which accesses data but the caller of the data class may only read the properties which has been done do there is no possible way the caller can causes issues at the presentation level. All data operations are performed in the data layer with the presentation layer calling the data layer.

Custom components

Since the classes which present information within a DataGridView do not have the capability to sort as when a DataTable is the DataSource of a DataGridView a custom BindingList is used. Besides providing the ability to sort the component provides the ability to filter data. Filtering uses System.Linq.Dynamic functionality which presents an issue such as performing case insensitive filtering e.g. StringComparison.CurrentCultureIgnoreCase can not be resolved so a property was added to the Customer class which allows the dynamic filtering to work as the added property is compliable at runtime.

The second component, a custom BindingSource which inherits the native BindingSource and provides methods to work with the underlying Customer list.

Public Class CustomerBindingSource
    Inherits BindingSource
  
    Public Sub New(container As System.ComponentModel.IContainer)
        MyBase.New(container)
    End Sub
  
    Public Sub New()
        MyBase.New()
    End Sub
  
    Public Sub New(datasource As Object, datamember As String)
        MyBase.New(datasource, datamember)
    End Sub
    ''' <summary>
    ''' Return the Customer list
    ''' </summary>
    ''' <returns></returns>
    Public Function Customers As List(Of Customer)
        return CType(DataSource, List(Of Customer))
    End Function
    ''' <summary>
    ''' Indicator that there is a current object.
    ''' </summary>
    ''' <returns></returns>
    Public Function CurrentIsValid As Boolean
        Return Current IsNot nothing
    End Function
    ''' <summary>
    ''' Returns the current customer object
    ''' </summary>
    ''' <returns></returns>
    Public Function CurrentCustomer() As Customer
        Return CType(Current, Customer)
    End Function
    Public sub UpdateCurrentCustomer(pCustomer as Customer)
        CurrentCustomer().CompanyName = pCustomer.CompanyName
        CurrentCustomer().ContactFirstName = pCustomer.ContactFirstName
        CurrentCustomer().ContactLastName = pCustomer.ContactLastName
        CurrentCustomer().ContactTitle = pCustomer.ContactTitle
        CurrentCustomer().ContactTypeIdentifier = pCustomer.ContactTypeIdentifier
        CurrentCustomer().ContactIdentifier = pCustomer.ContactIdentifier
        CurrentCustomer().PhoneNumber = pCustomer.PhoneNumber
        CurrentCustomer().PhoneTypeDescription = pCustomer.PhoneTypeDescription
        CurrentCustomer().PhoneTypeIdenitfier = pCustomer.PhoneTypeIdenitfier
        CurrentCustomer().DevicesIdentifier = pCustomer.DevicesIdentifier
        CurrentCustomer().Street = pCustomer.Street
        CurrentCustomer().City = pCustomer.City
        CurrentCustomer().PostalCode = pCustomer.PostalCode
        CurrentCustomer().CountryIdentifier = pCustomer.CountryIdentifier
        CurrentCustomer().CountryName = pCustomer.CountryName
        CurrentCustomer().Active = pCustomer.Active
    End sub
    ''' <summary>
    ''' Indicates if the current customer has a status of active
    ''' </summary>
    ''' <returns></returns>
    Public Function CurrentIsActive As Boolean
        Return CurrentCustomer().Active
    End Function
      
    ''' <summary>
    ''' Returns the current primary key for the customer
    ''' </summary>
    ''' <returns></returns>
    public Function CurrentIdentifier as Integer
        Return CurrentCustomer().CustomerIdentifier
    End Function
    ''' <summary>
    ''' Returns the current company name
    ''' </summary>
    ''' <returns></returns>
    public Function CurrentCompanyName as String
        Return CurrentCustomer().CompanyName
    End Function
    ''' <summary>
    ''' Returns the current contact name
    ''' </summary>
    ''' <returns></returns>
    public Function CurrentContactName as String
        Return CurrentCustomer().ContactName
    End Function
    Public Function ContactFirstName As String
        Return CurrentCustomer().ContactFirstName
    End Function
    Public  Function ContactLastName As string
        Return CurrentCustomer().ContactLastName
    End Function
    ''' <summary>
    ''' Returns the contact type key
    ''' </summary>
    ''' <returns></returns>
    public Function CurrentContactTypeIdentifier as Integer
        Return CurrentCustomer().ContactTypeIdentifier
    End Function
    ''' <summary>
    ''' Returns the phone type key
    ''' </summary>
    ''' <returns></returns>
    public Function CurrentPhoneTypeIdentifier as Integer
        Return CurrentCustomer().PhoneTypeIdenitfier
    End Function
End Class

Instead of the need to type cast the current property from object to customer we can use a method in the custom BindingSource.

_bsCustomers.CurrentCustomer()

Prior to accessing the current property it’s prudent to check if current object is valid so another method provides easy access.

_bsCustomers.CurrentIsValid()

To update the current property from changed data in a modal form.

_bsCustomers.UpdateCurrentCustomer(currentCustomer)

The alternates are type cast objects again and again or create language extension methods e.g. (which must resides in a code module).

<Runtime.CompilerServices.Extension> _
Public Function CurrentCustomer(pBindingSource As BindingSource) As Customer
    Return CType(pBindingSource.Current, Customer)
End Function

Retrieving data


Reference tables are read in using several SELECT statements setup as follows.

Dim selectStatements As String =
        <SQL>
            SELECT ContactTypeIdentifier,ContactTitle FROM dbo.ContactType;
            SELECT PhoneTypeIdenitfier,PhoneTypeDescription FROM dbo.PhoneType;
            SELECT id,CountryName FROM dbo.Countries
        </SQL>.Value

Note the use of XML literals and semi-colons to separate each SELECT statement. Each SELECT statement is read in via SqlClient DataReader using NextResult method to move to the next SELECT statement.

Customer data uses one SELECT statement which was created in SQL-Server Management Studio to ensure the proper data would be returned. If SQL-Server Management Studio is not available a developer can create a text file in their project, change the file extension from .txt to .sql and write SQL statements. Bonus if using Visual Studio 2017 which provides Intellisense for database operations.

The following SELECT statement uses XML literals as with the reference tables mentioned above. 

Private ReadOnly Property CustomerSelectStatement As String  
    get
        Return
            <SQL>
            SELECT  Cust.CustomerIdentifier ,
                    Cust.CompanyName ,
                    Contact.FirstName + ' ' + Contact.LastName AS ContactName ,
                    Contact.FirstName ,
                    Contact.LastName ,
                    Cust.ContactTypeIdentifier,
                    ContactType.ContactTitle ,
                    Contact.ContactIdentifier ,
                    Devices.PhoneNumber ,
                    PhoneType.PhoneTypeDescription ,
                    Devices.PhoneTypeIdenitfier,
                    Cust.Street ,
                    Cust.City ,
                    Cust.PostalCode ,
                    Countries.CountryName ,  
                    Countries.id,
                    Cust.ModifiedDate,
                    Devices.Active ,
                    Devices.Identifier 
            FROM    Customers AS Cust
                    INNER JOIN ContactType ON Cust.ContactTypeIdentifier = ContactType.ContactTypeIdentifier
                    INNER JOIN Countries ON Cust.CountryIdentfier = Countries.id
                    INNER JOIN Contact ON Cust.ContactIdentifier = Contact.ContactIdentifier
                    INNER JOIN ContactContactDevices AS Devices ON Contact.ContactIdentifier = Devices.ContactIdentifier
                    INNER JOIN PhoneType ON Devices.PhoneTypeIdenitfier = PhoneType.PhoneTypeIdenitfier
            WHERE   (Devices.Active = 1 AND PhoneType.PhoneTypeIdenitfier = 3
                    )
            ORDER BY Cust.CompanyName;
        </SQL>.Value
    End get
End Property

The query is executed in a function utilizing an Iterator which returns IEnumerable(Of Customer) using a DataReader coupled with Yield statement. This occurs when the application starts and displays results in a splash screen. There is a short delay in the read operation as there are 102 records and without the delay everything would happen too fast to notice. The idea here is the user of the application sees progress as company names are displayed in the splash screen. The proper use is not for one hundred or several hundred records but many thousands of records or perhaps a slow connection to the backend database.

Special care is needed if an exception is thrown while in the iterator method since the splash screen runs in a separate thread a delegate is required to close the splash screen in this case.

DataGridView setup

For each customer field/property which is displayed in the DataGridView a column needs to be created and the DataPropertyName set. The default behavior of generating columns is not performed the following line stops auto generation of columns in form load event.

Modifying data



Both adding new customers and editing customers is performed in a modal dialog rather than directly in the DataGridView. Validation is performed in the modal dialog for ensuring required fields are entered. If there are exceptions thrown during add or update operations raised from code or the backend database the insert or update are rolled back via a transaction object and by using a base exception class exceptions can be displayed to the user without halting the application.

Note that when viewing adding and update methods for the novice/beginner developer may think there is a lot of code vs working with Table or Data adapters yet there is freedom to change how a operation performs rather than rely on what goes behind a Table or Data adapter.

For both add and updates several tables are affected as the version of NorthWind uses related tables for contact details along with the base customer table.

Inserting


For inserts, one reference table is inserted, the new primary key is returned and used for the next insert then used in the last insert.

Public  Function AddNewCustomer(pCustomer As customer) As Boolean
    mHasException = False
  
    dim contactInsertStatement = 
            <SQL>
            INSERT INTO dbo.Contact
                        (FirstName
                        ,LastName)
                    VALUES
                        (@FirstName
                        ,@LastName); 
            SELECT CAST(scope_identity() AS int);
            </SQL>.Value
  
  
    Dim contactDevicesInsertStatement = 
            <SQL>
            INSERT INTO dbo.ContactContactDevices
                        (ContactIdentifier
                        ,PhoneTypeIdenitfier
                        ,PhoneNumber
                        ,Active)
                    VALUES
                        (@ContactIdentifier
                        ,@PhoneTypeIdenitfier
                        ,@PhoneNumber
                        ,@ACTIVE);
                SELECT CAST(scope_identity() AS int);
            </SQL>.Value
  
    Dim customerInsertStatement = 
            <SQL>
            INSERT INTO dbo.Customers
                        (CompanyName
                        ,ContactName
                        ,ContactIdentifier
                        ,ContactTypeIdentifier
                        ,Street
                        ,City
                        ,PostalCode
                        ,CountryIdentfier
                        ,Phone
                        ,ModifiedDate)
                    VALUES
                        (@CompanyName 
                        ,@ContactName
                        ,@ContactIdentifier
                        ,@ContactTypeIdentifier
                        ,@Street
                        ,@City
                        ,@PostalCode
                        ,@CountryIdentfier
                        ,@Phone
                        ,@ModifiedDate);
                SELECT CAST(scope_identity() AS int);
            </SQL>.Value
  
    Dim contactIdentifier As Integer = 0
    Dim contactDeviceIdentifier as Integer = 0
  
    Using cn As New SqlConnection With{.ConnectionString = ConnectionString}
  
  
        cn.Open()
  
        Dim sqlTran As SqlTransaction = cn.BeginTransaction()
              
        Using cmd As New SqlCommand With 
            {
                .Connection = cn, 
                .CommandText = contactInsertStatement, 
                .Transaction = sqlTran
            }
  
  
            Try
                cmd.Parameters.AddWithValue("@FirstName",pCustomer.ContactFirstName)
                cmd.Parameters.AddWithValue("@LastName",pCustomer.ContactLastName)
  
                contactIdentifier = CInt(cmd.ExecuteScalar())
  
                cmd.CommandText = contactDevicesInsertStatement
                cmd.Parameters.Clear()
  
                cmd.Parameters.AddWithValue("@ContactIdentifier",contactIdentifier)
                cmd.Parameters.AddWithValue("@PhoneTypeIdenitfier",pCustomer.PhoneTypeIdenitfier)
                cmd.Parameters.AddWithValue("@PhoneNumber",pCustomer.PhoneNumber)
                cmd.Parameters.AddWithValue("@Active",True)
  
                contactDeviceIdentifier = CInt(cmd.ExecuteScalar())
  
                cmd.CommandText = customerInsertStatement
                cmd.Parameters.Clear()
  
                cmd.Parameters.AddWithValue("@CompanyName",pCustomer.CompanyName)
                cmd.Parameters.AddWithValue("@ContactName",pCustomer.ContactName)
                cmd.Parameters.AddWithValue("@ContactIdentifier",contactIdentifier)
                cmd.Parameters.AddWithValue("@ContactTypeIdentifier",pCustomer.ContactTypeIdentifier)
                cmd.Parameters.AddWithValue("@Street",pCustomer.Street)
                cmd.Parameters.AddWithValue("@City",pCustomer.City)
                cmd.Parameters.AddWithValue("@PostalCode",pCustomer.PostalCode)
                cmd.Parameters.AddWithValue("@Phone",pCustomer.PhoneNumber)
                cmd.Parameters.AddWithValue("@CountryIdentfier",pCustomer.CountryIdentifier)
                cmd.Parameters.AddWithValue("@ModifiedDate",Now)
  
                pCustomer.CustomerIdentifier = CInt(cmd.ExecuteScalar())
                pCustomer.ContactIdentifier = contactIdentifier
                pCustomer.DevicesIdentifier = contactDeviceIdentifier
                sqlTran.Commit()
  
                return True
  
            Catch sex As SqlException
  
                sqlTran.Rollback()
                mHasException = True
  
                Return False
  
            Catch ex As Exception
                mHasException = True
                mLastException = ex
  
                Return False
  
            End Try
        End Using
    End Using
End Function

Updates


The update is structured similar adding a new customer while in the update we already have primary keys to find the proper rows and update these records. Since an exception may happen the operation is wrapped in a transaction and committed on success or rolled back on failure.
public Function UpdateCustomer(pCustomer as Customer) As Boolean
    Dim successCount as Integer = 0
    dim contactUpdateStatement = 
            <SQL>
            UPDATE dbo.Contact
                SET FirstName = @FirstName
                    ,@LastName = @LastName
                WHERE ContactIdentifier = @Id
            </SQL>.Value
  
  
    Dim contactDevicesUpdateStatement = 
            <SQL>
            UPDATE dbo.ContactContactDevices
                SET ContactIdentifier = @ContactIdentifier
                    ,PhoneTypeIdenitfier = @PhoneTypeIdenitfier
                    ,PhoneNumber = @PhoneNumber
                    ,Active = @Active
                WHERE Identifier = @Id
            </SQL>.Value
  
    Dim customerUpdateStatement = 
            <SQL>
            UPDATE dbo.Customers
                SET CompanyName = @CompanyName
                    ,ContactName = @ContactName
                    ,ContactIdentifier = @ContactIdentifier
                    ,ContactTypeIdentifier = @ContactTypeIdentifier
                    ,Street = @Street
                    ,City = @City
                    ,PostalCode = @PostalCode
                    ,CountryIdentfier = @CountryIdentfier
                    ,Phone = @Phone
                    ,ModifiedDate = @ModifiedDate
                WHERE CustomerIdentifier = @Id
            </SQL>.Value
  
  
    Using cn As New SqlConnection With{.ConnectionString = ConnectionString}
        cn.Open()
  
        Dim sqlTran As SqlTransaction = cn.BeginTransaction()
        Using cmd As New SqlCommand With {.Connection = cn, .CommandText = contactUpdateStatement, .Transaction = sqlTran}
  
            Try
                cmd.Parameters.AddWithValue("@FirstName", pCustomer.ContactFirstName)
                cmd.Parameters.AddWithValue("@LastName", pCustomer.ContactLastName)
                cmd.Parameters.AddWithValue("@Id", pCustomer.ContactIdentifier)
  
                successCount = cmd.ExecuteNonQuery()
                cmd.Parameters.Clear()
  
                cmd.CommandText = contactDevicesUpdateStatement
                cmd.Parameters.AddWithValue("@ContactIdentifier", pCustomer.ContactIdentifier)
                cmd.Parameters.AddWithValue("@PhoneTypeIdenitfier", pCustomer.PhoneTypeIdenitfier)
                cmd.Parameters.AddWithValue("@PhoneNumber", pCustomer.PhoneNumber)
                cmd.Parameters.AddWithValue("@Active", True)
                cmd.Parameters.AddWithValue("@Id", pCustomer.DevicesIdentifier)
  
                successCount += cmd.ExecuteNonQuery()
  
                cmd.Parameters.Clear()
  
                cmd.CommandText = customerUpdateStatement
                cmd.Parameters.Clear()
  
                cmd.Parameters.AddWithValue("@CompanyName", pCustomer.CompanyName)
                cmd.Parameters.AddWithValue("@ContactName", pCustomer.ContactName)
                cmd.Parameters.AddWithValue("@ContactIdentifier", pCustomer.ContactIdentifier)
                cmd.Parameters.AddWithValue("@ContactTypeIdentifier", pCustomer.ContactTypeIdentifier)
                cmd.Parameters.AddWithValue("@Street", pCustomer.Street)
                cmd.Parameters.AddWithValue("@City", pCustomer.City)
                cmd.Parameters.AddWithValue("@PostalCode", pCustomer.PostalCode)
                cmd.Parameters.AddWithValue("@Phone", pCustomer.PhoneNumber)
                cmd.Parameters.AddWithValue("@CountryIdentfier", pCustomer.CountryIdentifier)
                cmd.Parameters.AddWithValue("@ModifiedDate", Now)
                cmd.Parameters.AddWithValue("@Id",pCustomer.CustomerIdentifier)
  
                successCount += cmd.ExecuteNonQuery()
  
                sqlTran.Commit()
  
                Return successCount = 3
            Catch sex As SqlException
                sqlTran.Rollback()
                mHasException = True
  
                Return False
  
            Catch ex As Exception
                mHasException = True
                mLastException = ex
  
                Return False
            End Try
  
        End Using
  
    End Using
  
End Function

Removing records


To remove a record pressing DELETE key on a record prompts for confirmation. If confirmed three tables are removed with the addition of orders and order details as cascade deletes are set to true. As with inserts and updates since there are multiple tables a transaction wraps around the removal operation.

Public Function RemoveCustomer(pCustomer As Customer ) As Boolean
    mHasException = False
  
    Dim removeContactStatement = "DELETE FROM dbo.Contact  WHERE ContactIdentifier = @Id"
    Dim removeCustomerStatement = "DELETE FROM dbo.Customers WHERE CustomerIdentifier = @Id"
    Dim removeDevicesStatement = "DELETE FROM dbo.ContactContactDevices WHERE ContactIdentifier = @Id"
  
  
    Using cn As New SqlConnection With{.ConnectionString = ConnectionString}
  
        cn.Open()
  
        Dim sqlTran As SqlTransaction = cn.BeginTransaction()
  
        Using cmd As New SqlCommand With {.Connection = cn, .CommandText = removeCustomerStatement, .Transaction = sqlTran}
            cmd.Parameters.AddWithValue("@Id", pCustomer.CustomerIdentifier)
  
            Try
  
                cmd.ExecuteNonQuery()
                cmd.Parameters.Clear()
  
                cmd.CommandText = removeDevicesStatement
                cmd.Parameters.AddWithValue("@Id", pCustomer.ContactIdentifier)
                cmd.ExecuteNonQuery()
  
                cmd.Parameters.Clear()
                cmd.CommandText = removeContactStatement
                cmd.Parameters.AddWithValue("@Id", pCustomer.ContactIdentifier)
                cmd.ExecuteNonQuery()
  
  
                sqlTran.Commit()
  
                Return True
            Catch sex As SqlException
                sqlTran.Rollback()
                mHasException = True
                mLastException = sex
                Return False
  
            Catch ex As Exception
                mHasException = True
                mLastException = ex
                Return False
            End Try
        End Using
    End Using
End Function

A downside to creating classes as done in this code sample in tangent with usage in SQL statements is you the developer must be diligent to update the queries e.g. if a new column is added then in the read operation make sure to add the column which usually means adding the column to the add, edit methods along any place this data is needed when working with the data in the presentation layer also and run unit test methods against the changed code.

Organization of projects

Both custom components mentioned reside in their own class projects which permits usage in other projects were the only dependencies are on the customer class which resides in a class project for all classes required for this application. One can take both class projects and use in another solution which works with the same database. Both exception handling class and database connection class reside in a separate class project which can be used in another Visual Studio solution.

Classes specific to the application reside in folders within the application project. There are two custom My.Namespace classes which must be in the application as how the .NET Framework is setup these classes cannot be on another project. One is a wrapper for showing message boxes while the other is a stub for sending error emails.

Take a look at ControlsExtensions in the main project which provides an extension method Descendants which is a good method to target all controls of a specific type on a form in in any containers on the form. It’s really useful in this code sample for edit/add validation along with exception handling if an exception is thrown in form load of the main form.

<Runtime.CompilerServices.Extension> _
Public Iterator Function Descendants(Of T As Class)(ByVal control As Control) As IEnumerable(Of T)
    For Each child As Control In control.Controls
  
        Dim currentChild = TryCast(child, T)
        If currentChild IsNot Nothing Then
            Yield currentChild
        End If
  
        If child.HasChildren Then
            For Each descendant As T In child.Descendants(Of T)()
                Yield descendant
            Next
        End If
    Next
End Function

Moving your code to this level

To move to this level a developer that is interested in this must take time to study how the code flows along with having a decent understanding why code has been setup as presented. The wrong approach is to copy and paste parts of code without having a understanding why. Couple code with a sound database schema which reflects your business requirements.

Where to go from here

Once there is a firm understanding of what has been presented consider working with Entity Framework. Entity Framework does require a significant amount of code to get rolling yet once into normal database operations Entity Framework is more elegant than TableAdapter, DataAdapter and even methods presented here. Why not skip right from TableAdapter or DataAdapter to Entity Framework? Because you will miss out on learning how things work which with Entity Framework is less transparent yet once learned is easy to understand.

Entity Framework Read example



In this example the classes which represent the same tables as in the above were created using “Design from database”. To keep things simple, the following loads Customers data.

Keeping simple, the following loads customers and displays several fields. In a real application data would be loaded into a DataGridView using the same custom BindingList as in the non-Entity Framework example above. 

Imports System.Data.Entity
Public Class Form1
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        Dim customerList as new List(Of Customer)
        Dim customerIdentifiers = Enumerable.Range(1, 10).ToArray()
  
        using cn As New NorthWindEntities()
            cn.Configuration.ProxyCreationEnabled = False
            cn.Configuration.LazyLoadingEnabled = true
  
            customerList = cn.Customers.
                Include(Function(cust As Customer) cust.Contact).
                Include("Country").Include("ContactType").
                Where(Function(cust) customerIdentifiers.Contains(cust.CustomerIdentifier)).
                ToList()
              
        End Using
  
        For Each cust As customer In customerList
            Console.WriteLine(
            $"{cust.CompanyName,-35} {cust.Contact.FirstName,-12} {cust.Contact.LastName, -12} {cust.ContactType.ContactTitle}")
        Next
    End Sub
End Class


Important notes

cn.Configuration.ProxyCreationEnabled = False
cn.Configuration.LazyLoadingEnabled = true

The two lines above tell Entity Framework not to load related tables e.g. Orders in this case is excluded. Since related tables are not loaded we need to use Include which is shown two different ways, one specifying the table name in a string which is fragile since table names change over time this can break while the second method uses lambda to strongly type the expression where condition which is the preferred method. Include is shown for one level but also supports multiple levels also.

This short example provides a quick glance for reading data. Modifying data is very easy too e.g. create a new customer, populate it’s properties, mark the Entry state as modified followed by calling the save method on the context.

Public Sub UpdateCustomer(pCustomer As Customer)
  
    Try
  
        Using cn As New NorthWindEntities
            cn.Entry(pCustomer).State = EntityState.Modified
            cn.SaveChanges()
        End Using
  
    Catch ex As DbUpdateConcurrencyException
        ' handle Optimistic Concurrency exception
    End Try
  
End Sub

Adding a new customer unlike using a managed data provider to insert and get the new primary key Entity Framework returns the new key upon a successful save.

As mentioned above, Entity Framework is a great option yet not always the best option for desktop application. If you are building desktop applications currently use the methods talk about above, when moving to web applications or service solutions look at Entity Framework current version.

Public Function AddCustomer(pCustomer As Customer) As Integer
    Using cn As New NorthWindEntities
  
        cn.Entry(pCustomer).State = EntityState.Added
  
        '
        ' The save will bring back the new primary key
        '
        cn.SaveChanges()
        '
        ' After saving the new primary key is contained in Customer
        ' The caller in this case creates a new record in the DataGridView
        ' DataSource, a BindingSource.
        Return pCustomer.CustomerIdentifier
  
    End Using
End Function

Screen shots

While data is loaded in the data class the function responsible is an Iterator using Yield to pass the current customer name via a delegate to the splash screen which runs in it's own thread.

Splash screen




Once finishing loading of data





Editing screen





Add screen




Remove current customer




Dynamic filter




Source code


Found on MSDN https://code.msdn.microsoft.com/SQL-Server-application-1d1368bc

See also

Objects and classes in VB.NET 
Entity Framwork Core