VB.NET Writing better code Part 1
Introduction
This article is a general guideline to writing and maintaining code for both software developers and the occasional coder. Topics presented will provide solid guidelines at different levels for explanations. These guidelines are just that, guidelines, not set in stone but consideration should be given to each guideline rather than taking the easy path. Example of an easy path, writing all code for a program in a single web page or Windows Form and/or not properly typing variables.
By properly planning a solution out from the start rather than simply head-first writing code will save time later with less errors, change of path when something does not work as expected to maintenance of the program months or years later. This is true for small medium and large projects, more so for medium to large projects.
For database best practices SQL-Server will be the target while other databases follow the same practices with different syntax in regards to how a statement is created.
Suggested reading prior to reading this article but not mandatory, .NET: Defensive data programming (part 1).
Purpose
Before opening Visual Studio, decide what the intent for writing code. There are two paths, the first is to learn coding usually for something which a developer has no experience like working with interacting with databases while the second is to write code which will solve a problem.
In the first case, learning, this is best accomplished by researching the topic at two levels. The first level is searching the Internet e.g. “VB.NET SQL-Server reading records”, read at least ten pages to see what others are doing. From here, level 2, find Microsoft documentation for methods used in the research performed on the web.
Reasoning behind first searching then reading documentation is first “perspective” in regard to search the web as reading one method to perform a task may work yet may not be efficient and/or may teach poor programming practices. Reading Microsoft documentation provides important details to what a developer is learning. This also may extend to reading developer blogs with a bit of skepticism as what may be found in a blog may work yet may teach poor programming practices and be a one-sided view to the topic.
Design
Reasoning behind first searching then reading documentation is first “perspective” in regard to search the web as reading one method to perform a task may work yet may not be efficient and/or may teach poor programming practices. Reading Microsoft documentation provides important details to what a developer is learning. This also may extend to reading developer blogs with a bit of skepticism as what may be found in a blog may work yet may teach poor programming practices and be a one-sided view to the topic.
A common roadblock for novice developers is a method is created in a class that returns a specific type and then the need to determine if the method was performed successfully. In some cases the return type can indicate success or failure while in other cases this will not be possible. By simply hitting this specific roadblock may prove to challenging for some which causes code to be written that does not conform to code which can be maintained well in the future while researching the roadblock can provide insight into proven methods to resolve the roadblock.
Although the above is not a formal design it does force a path to follow rather than simply sitting down and writing code.
When developing an application to solve a problem, the first step will be to write down what is to be accomplished, business requirements, constraints e.g. storing information for access by twenty users with room to grow with security enforced so unauthorized users are denied access or may not even see the database or application.
Using a tried and proven method can be done using Gherkin which connects the human concept of cause and effect to the software concept of input/process/output. This can be written down in either a document or in a Azure repository which sits on a cloud server. These repositories reside on the web and a Microsoft product that provides version control, reporting, requirements management, project management, automated builds, lab management, testing and release management capabilities.
From here based on design and requirements create base projects where for a database centric solution (most applications interact with databases) there would be a class project for interacting with a database then another project for processing data as per business requirements where a main project utilizes these two projects to perform operations needed to solve a specific problem.
User interface selection considerations should be based on current requirements and the chance the user interface may change. For instance, today the requirement is to run solely on Windows 10 while down the road users want to run on other platforms e.g. Android, IOS, MAC etc. Consideration for today might move from Windows Forms to WPF (which requires object oriented programming skills unlike windows forms which can be coded loosely with or without user defined classes) or Xamarin which Visual Basic does not support directly which may change the programming language for a project which now means retooling.
The next step can be done by creating classes and methods, once done take a breathe and ask “do these classes met the requirements to resolve a problem?” If not now is the time to make adjustments unless the decision is made to perform agile joint application development where during the life cycle of the project requirements change and the development team or developer adjust to new requirements.
An alternate path is to write code with the base projects created using TDD (Test Driven Development) against requirements written down using Gherkin. Without nothing more than empty class projects and a main project, begin writing code to code that does not exists e.g. a person class is needed, with the following properties, identifier, first/last name and birth date. Write the following and ignore any errors.
Dim person As New Person With {.Identifier = 1, .FirstName = "Jim", .LastName = "Smith", .BirthDate = Now}
On the Person non-existing class press CTRL+. which presents options to create the class followed by the properties.
The following is created which most likely needs to be public so change the scope to Public. In this case the class is generated in the same class the code above was written in.
Friend Class Person
Public Property Identifier As Integer
Public Property FirstName As String
Public Property LastName As String
Public Property BirthDate As Date
End Class
If "Generate new type" was selected this presents a dialog which permits the class to be created in another existing project. For project location select from the dropdown the project then under File name option click the radio button for "create new file" which shows Person.vb. A rule of thumb is to not only place classes such as person into a class project but also into folders within the class project e.g. Classes for containers like Person while another folder named ExtensionMethods for extension methods.
For the person class enter Classes\person. Pressing the Okay button creates the Person class in a folder named Classes (which will be created if it does not exists). Usually a person requires more information such as address. Add the following code to the creation of a person.
Dim person As New Person With
{
.Identifier = 1,
.FirstName = "Jim",
.LastName = "Smith",
.BirthDate = Now,
.Address = New Address With
{
.Street = "101 Green Ave",
.City = "Portland",
.State = "OR"
}
}
Press CTRL+. to create the new Address class then again to add an Address property to the Person class.
By using "create new type" in tangent with requirements written from Gherkin requires more time, first in planning then in coding stages then simply have an idea and begin coding yet the latter tends to be harder to maintain and leaves one or more requirements not met.
Setting default options in Visual Studio
Before writing code, from the Tools menu in Visual Studio select “Projects and Solutions”, “VB Defaults” to setup default settings.
Option Strict
Setting this option to “On” restricts implicit data type conversions to only widening conversions, disallows late binding, and disallows implicit typing that results in an Object type. Simply put, VB.NET checks data types are specified for all elements while set to “Off” there is no type checking when writing code or when compiling a Visual Studio project within a Visual Studio solution.
When a coder wrote software with JavaScript or Visual Basic classic type checking of variable tends to be lacked, no assertion such as testing if the proper type has been passed to a procedure or function which in many cases carries over to writing VB.NET code A common remedy for the occasional passing of incorrect data or when nothing is passed in a On Error statement of Try-Catch statement resolve the issue with recovery as the coder sees fit.
Problems with this mentality, the runtime analysis of untyped variables takes time away from current processing of business logic along with wrong type being used for a task.
Example not setting Option Strict On. Rather than specify a type for WithOptionStrictOff, SomeDate parameter passing in a date as a string and converted to a date at runtime with a valid date the result is a date plus 1 day.
Module Module1
Sub Main()
WithOptionStrictOff("09/10/2019")
Console.ReadLine()
End Sub
Private Sub WithOptionStrictOff(SomeDate)
Dim NextDay = CType(SomeDate, DateTime).AddDays(1)
Console.WriteLine(NextDay)
End Sub
End Module
All it takes for this code to fail is by passing a invalid date which may of happened by a user entering a date in a TextBox for a Windows Form project or a Input element for a web application.
Module Module1
Sub Main()
WithOptionStrictOff("09/102019")
Console.ReadLine()
End Sub
Private Sub WithOptionStrictOff(SomeDate)
Dim NextDay = CType(SomeDate, DateTime).AddDays(1)
Console.WriteLine(NextDay)
End Sub
End Module
There is no separator between day and year which will throw a runtime exception System.InvalidCastException: 'Conversion from string "09/102019" to type 'Date' is not valid.'
Turn on Option Strict .AddDays will light up with "Option Strict On disallows late binding" and SomeDate.AddDays(1) will report "Cannot resolve symbol". Then changing the procedure signature from Private Sub WithOptionStrictOff(SomeDate) to Private Sub WithOptionStrictOn(SomeDate As DateTime) will report that this line of code WithOptionStrictOn("09/102019") "Option Strict On disallows implicit conversion from 'string' to 'Date'.
This means only a Date can be pasted to the method e.g.
WithOptionStrictOn(#09/10/2019#)
If a bad formed data is pasted
WithOptionStrictOn(#09/102019#)
The code will not compile which provides another layer of protection in that the code will not compile. But what if the date is entered in a TextBox? This is where assertion is required to determine if user input is a valid date.
The following example is one way to validate a data using date format of MM/DD/YYYY. First determine if the TextBox.Text property a) has a value b) can represent a date.
Private Sub ValidateBirthdayButton_Click(sender As Object, e As EventArgs) _
Handles ValidateBirthdayButton.Click
If Not String.IsNullOrWhiteSpace(BirthdayTextBox.Text) Then
Dim birthDay As Date
If Date.TryParse(BirthdayTextBox.Text, birthDay) Then
WithOptionStrictOn(birthDay)
End If
End If
End Sub
By setting Option Strict On and using assertion will reduce chances of a runtime exception being thrown.
When to use Option Strict Off? When dealing with situation were types are unknown, for instance working with Excel automation when dealing with different versions of Excel.
The professional programmer knows when to use Option Strict Off which comes from first understanding why Option Strict On is important then can Option Strict Off may be used and usually wrapped with a Try-Catch statement where the Catch part of the Try-Catch is not left empty.
Although the example above uses a Date type, the same applies to other types such as numeric variables. The example below demonstrates this with a conversion from string to Decimal.
Private Sub QuantityButton_Click(sender As Object, e As EventArgs) _
Handles QuantityButton.Click
If Not String.IsNullOrWhiteSpace(QuantityTextBox.Text) Then
Dim quantity As Decimal
If Decimal.TryParse(QuantityTextBox.Text, quantity) Then
' Text converts to a decimal
End If
End If
End Sub
To conclude this section on Option Strict On, always assert when converting from one type to another, use Option Strict On for a layer of protection at design time. And finally consider using other controls e.g. DateTimePicker for date input, NumericUpDown control for string to numeric, custom TextBox which knows how to handle unacceptable input or filtering out unacceptable input usually done in KeyDown event.
Option Infer
Option enables Infer the use of local type inference when working with local variable. For example, in the following code the first variable is explicitly typed which works with both Option Infer On or Off while the second variable is created explicitly, the compiler figures out the type.
Public Class OptionInferCodeSamples
Public Sub Example()
Dim firstInteger As Integer = 1
Dim secondInteger = 1
End Sub
End Class
Type inference used is very precise. The compiler will only allow the statement if the type is clear and unambiguous. Therefore, the outcome is not really a loss in precision ... it is merely saving the developer from stating the type more than once.
There are those who embrace and those who do not embrace type interference, similarly to the C# counterpart using var to infer the type. The decision which path to take should be, use explicit declarations when it is apparent by either looking at the right side of the assignment for the type or the variable name clearly states the type.
In the following example, both the variable name indicates the type along with the right side, the type is specified.
Public Sub Example()
Dim CustomerDataTable As New DataTable
End Sub
In the following code sample, the variable name does not tell the type if when using the variable many lines later the only way to tell the type is to actually type out the name and Visual Studio will show the type.
Public Sub Example()
Dim CustomerData = New DataTable
End Sub
Best practices is to have great variable names when using Option Infer On rather than cryptic variable names. The test is to present code using Option Infer On to another developer, allow them to read code with the result that everything is perfectly clear unlike the following code sample with a variable name telling nothing about the type, a DataTable.
Public Sub Example()
Dim D1 = New DataTable
End Sub
Another consideration would be, how much reside resides between declaring a variable and using the variable. For example, the following method is short, types of variables can easily be known.
Public Function GetProjectResourcesItems() As List(Of PropertyInfo)
Dim propertyInfoArray = GetType(My.Resources.Resources).
GetProperties(
BindingFlags.NonPublic Or
BindingFlags.Instance Or
BindingFlags.Static)
Dim propertyResourceInfoList =
(
From propertyInfo In propertyInfoArray
Where propertyInfo.PropertyType Is GetType(String)
).ToList
Return propertyResourceInfoList
End Function
Then there are coders using the following variables with a method several pages long. Using Option Infer On is acceptable while naming of variables is not as they don't describe their content.
Dim query1 = ""
Dim query2 = ""
Using clear variable names.
Dim SelectCustomersByCountry = ""
Dim SelectCustomersByNameStartsWith = ""
Naming/coding style conventions
Visual Studio 2017 and Visual Studio 2019 provides code style rules which are located from the IDE menu.
Tools -> Text Editor -> Basic -> Code Style.
Many of the rules under this option section should be consider adapting while they are not for everyone there is an option to create a local rule set as explained on the following Microsoft documentation page.
If some rules are not always acceptable then simply ignore the suggestion or create local rules.
What should not be done is to use naming conventions like naming a Windows Forms button cmdAddCustomerButton, instead consider addNewCustomerButton or AddNewCustomerButton. Another example, using a method name GetByType(Id As Integer) for returning a specific type from a database table such as contact titles for customers, instead provide a meaningful name; GetAllRecordsByContactTitle(contactTypeIdentifier).
When working with user created methods which are asynchronous , append Async to the method name e.g. reading very large files ReadVeryLargeFileAsync() this indicates to other developers that the method needs to be call differently from conventional synchronous methods.
Most important, variable names, class names, interface names should be easily recognizable to their intent to programmers other than the person who wrote the code.
When working with for-next do not use one letter variables e.g.
For i As Integer = 0 To CultureInfo.CurrentCulture.DateTimeFormat.AbbreviatedMonthNames.Count -1
Console.WriteLine(CultureInfo.CurrentCulture.DateTimeFormat.AbbreviatedMonthNames(i))
Next
In this context i is perfectly fine until a inner for-next is introduced.
For i As Integer = 0 To dt.Rows.Count -1
For j As Integer = 0 To dt.Columns.Count -1
Console.WriteLine(dt.Rows(i)(j))
Next
Next
Now imagine the code sample above has many more lines, i and j are not easy to figure out what they are nor is it easy to hover of a single character for debug purposes. By providing meaningful names it's easier to distinguish what a variable represents.
For rowIndex As Integer = 0 To dt.Rows.Count -1
For columnIndex As Integer = 0 To dt.Columns.Count -1
Console.WriteLine(dt.Rows(rowIndex)(columnIndex))
Next
Next
Compound this with a third for-next and maintainability goes way down with one character variables for indexers. Instead, use verbose names for indexer names as in the following example which iterates a list of orders then iterates order details followed by iterating products.
For orderIndex As Integer = 0 To orderList.Count -1
For detailsIndex As Integer = 0 To orderList(orderIndex).Details.Count -1
For productIndex As Integer = 0 To orderList(orderIndex).Details(productIndex).ProductList.Count -1
Next
Next
Next
Working with string
When developing applications a great deal of work is done with strings. Below are best practices for working with string
Concatenation
When concatenating strings for simple/small concatenation use the & operator or &= operator, avoid using the + operator. Another option is using string interpolation.
Using & operator.
Dim example1 As Integer = 10
Dim example2 As Integer = 12
Dim result As String = "Results:" & example1 & " " & example2
MessageBox.Show(result)
Using string interpolation.
Dim example1 As Integer = 10
Dim example2 As Integer = 12
Dim result As String = $"Results: {example1} {example2}"
MessageBox.Show(result)
For large string concatenation use the StringBuilder class. Reasoning for using the StringBuilder class as the StringBuilder modifies the string while using the & operator requires new allocation of space for the new object.
Become familiar with the following methods in the string class which return strings.
- String.Concat method which can concatenate one or more instances of a string to represent a single string.
- String.Join method which concatenates the elements of a specific array into a string.
- String.Insert method which returns a new string in which a specified string is inserted at a index position in the instance.
In some cases a task appears to need a one of the methods shown above, Join to join together a string array into a string.
Given
Dim FirstNames As String() = {"Karen", "Mary", "Lisa"}
Return
Karen, Mary and Lisa
From reading the documentation on String.Join only one separator can be used, to get around this one idea is to iterate the array, appending to another string using a StringBuilder Append method and then check for a comma at the end of the string, get the index and replace it with " and ", this is a good deal of work and unnecessary code to see in your business logic along with only useful in the method coded in.
To get around this, a language extension method.
Public Module StringExtensions
''' <summary>
''' Join string array with specified delimiter and last delimiter
''' </summary>
''' <param name="sender">String array to convert to delimited string</param>
''' <param name="delimiter">Delimiter to separate array items</param>
''' <param name="lastDelimiter">Token used for final delimiter</param>
''' <returns>Delimited string</returns>
<Runtime.CompilerServices.Extension>
Public Function JoinWithLastSeparator(sender() As String, delimiter As String, lastDelimiter As String) As String
Return String.Join(delimiter & " ",
sender.Take(sender.Length - 1)) &
(((If(sender.Length <= 1, "", lastDelimiter))) +
sender.LastOrDefault())
End Function
End Module
The first step to creating the method above is first doing it the hard way, get it to work then think about using an extension method and start writing code or go out on the web and find what is needed, something close and modify if needed.
Lesson here is to first know what is available, in this case for working with strings and work with these methods until they do not provide what is needed.
↑ Back to top
Design patterns
Design patterns are solutions to software design problems a developer encounters again and again in application development. Patterns are about reusable designs and interactions of objects.
The builder in VB.NET is a creational design pattern, which allows constructing complex objects step by step. Unlike other creational patterns, Builder doesn't require products to have a common interface. That makes it possible to produce different products using the same construction process.
When reviewing code with testers and business clients using a builder pattern allows a developer to write code which allows testers and clients reviewing code to understand the purpose of the code.
For instance, the following code utilizes the builder pattern to create and send an email message. Actual work is not exposed which means this might use System.Net.Mail classes, MailKit from GitHub, a paid library or classes created in house.
Dim mailer As New MailBuilder()
mailer.CreateMail(GmailConfiguration1).
WithRecipient("karen@comcast.net").
WithCarbonCopy("mary@gmail.com").
WithSubject("Test").
AsRichContent().
WithHtmlView("<p>Hello <strong>Bob</strong></p>").
WithPickupFolder().
WithTimeout(2000).
SendMessage()
To see several examples read TechNet article VB.NET Fluent Builder design pattern. For a list of design patterns see TechNet article Software Patterns and practices.
↑ Back to top
Structure of a Visual Basic Program
Structuring for a program is language agnostic meaning for Visual Basic or C# the same applies for both.
Dependent on program complexity a program may consist of one project or multiple projects. When developing multiple projects common code between projects may emerge which means the common code can be placed into a separate Visual Studio where other projects reference the compiled assemblies rather than actual projects.
Use classes to store information ranging from data read from a text file, information obtained from Active Directory or data moving to and from a database. Classes should be stored in a folder with the name Classes for common classes, a folder named Data Classes for classes which interact with databases. These classes may reside in the main project or a class project which resides in the same Visual Studio solution which the main project references the class project(s).
Interfaces should be stored in a folder named Interfaces.
Use code modules for language extensions placed into a folder named Extensions. Separate functionality by type, for instance string extensions in StringExtensions, Integer extensions in IntegerExtensions etc.
Future architecture
Consideration to future architecture means, today a program is written for Windows Forms while later a move to cross-platform is desired or required. In this case consideration should be considered now. Consider reviewing porting recommendations which can save time later down the road.
- For new Windows desktop solutions move from Windows Forms to WPF (Windows Presentation Foundation).
- Do not depend on the system registry as other platforms do not have a system registry.
- When using class method in a conventional .NET project review the "Applies to" at the bottom of the page for a class method which will indicate if the method is compatible with .NET Core or not.
- Microsoft have expressly said that not all .NET Core API’s will be available for VB.NET, especially those that give low level control to programmers. This means roll your own, look to open source or adapt by utilizing C# class projects by adding references to the Visual Basic project. Review Visual Basic .NET Language design for up to date information. Review Kathleen Dollard page Visual Basic in .NET Core.
Containers
Consider what is required for storing data rather than picking what is easy.
When implementing a design developer tend to think in terms of what seems tried and true, for example, working with data from a database table the first thought is DataSet and DataTable. Although both DataSet and DataTable work there are times when there are other options such as concrete class or a list of concrete class.
For example, a ComboBox for displaying contact types read from a database table. Conventional path is to read data into a DataTable, set the DataTable as the DataSource of the ComboBox and set DisplayMember to the field representing text for contact type. In some cases with a poor design the primary key is not even read which should be so when editing or adding a new record the key is available rather than the string value as the string value may change over time.
An alternate solution is to use a data reader, populate a List(Of ContactType) with a property for the primary key and a property for the contact type plus overriding ToString with the Name property. Set the DataSource to the List, the ComboBox uses the ToString override as the DisplayMember.
Public Class ContactType
Public Property Identifier() As Integer
Public Property Name() As String
Public Overrides Function ToString() As String
Return Name
End Function
End Class
No matter the container another consideration is consistency. Imagine there are classes for working with various entities e.g. Customer, Orders, Order Details were each need and get by primary key, an update, add method, delete method, save method and read all method. A team member is given Customers, another Orders and another Order Details. More likely than not each developer will call one or more of the methods different names rather than selecting the same name for each required method.
Here is where requiring an Interface provides consistency. The following Interface provides consistency for all method names and if events were needs the same goes for events.
Public Interface ITableEntity(Of T)
Function GetById(id As Decimal) As T
Function Update(pMessage As T) As T
Function Add(pMessage As T) As T
Function Delete(id As Integer) As T
Function Commit() As Integer
Function GetAll() As List(Of T)
End Interface
Working with Customer and Order the following are abbreviated classes.
Partial Public Class Customer
Public Property Id() As Decimal
Public Property Name As String
End Class
Public Class Order
Public Property Id() As Decimal
Public Property CustomerIdentifier() As Decimal
Public Property OrderDate() As DateTime
End Class
Next a class is needed for Customer operations which implements ITableEntity.
Public Class CustomerOperations
Implements ITableEntity(Of Customer)
Visual Studio will indicate there are missing implementation members, press CTRL+. to allow Visual Studio to create the members. The results ready for the developer to write code for each method.
Public Class CustomerOperations
Implements ITableEntity(Of Customer)
Public Function GetById(id As Decimal) As Customer _
Implements ITableEntity(Of Customer).GetById
Return Nothing
End Function
Public Function Update(pMessage As Customer) As Customer _
Implements ITableEntity(Of Customer).Update
Return Nothing
End Function
Public Function Add(pMessage As Customer) As Customer _
Implements ITableEntity(Of Customer).Add
Return Nothing
End Function
Public Function Delete(id As Integer) As Customer _
Implements ITableEntity(Of Customer).Delete
Return Nothing
End Function
Public Function Commit() As Integer Implements ITableEntity(Of Customer).Commit
Return 0
End Function
Public Function GetAll() As List(Of Customer) _
Implements ITableEntity(Of Customer).GetAll
Return Nothing
End Function
End Class
Repeat for Order operations
Public Class OrderOperations
Implements ITableEntity(Of Order)
Public Function GetById(id As Decimal) As Order _
Implements ITableEntity(Of Order).GetById
Return Nothing
End Function
Public Function Update(pMessage As Order) As Order _
Implements ITableEntity(Of Order).Update
Return Nothing
End Function
Public Function Add(pMessage As Order) As Order _
Implements ITableEntity(Of Order).Add
Return Nothing
End Function
Public Function Delete(id As Integer) As Order _
Implements ITableEntity(Of Order).Delete
Return Nothing
End Function
Public Function Commit() As Integer _
Implements ITableEntity(Of Order).Commit
Return 0
End Function
Public Function GetAll() As List(Of Order) _
Implements ITableEntity(Of Order).GetAll
Return Nothing
End Function
End Class
Repeat for Order Details.
At this point each type requires different code to interact with the backend database and there is consistency between each type at this point.
Database interaction
First write SQL statements in SSMS (SQL-Server Management Studio) rather than in code to validate statements work properly rather than writing SQL statements in code which may mask a poorly written statement. For those new to writing SQL, see the following page which provides generic assistance to write SQL along with examples to run and even modify.
If there is an issue with a operation failing to provide expected results when writing SQL in SSMS in a project chances are the results are not statement related but instead code related which narrows down the search for fixing the problem.
The following TechNet article provides assistance for writing SQL statements and also writing unit test against SQL statements.
Build a database schema from business requirements, avoid tables with a large set of fields, learn to breakout data into relational parts e.g. a customer table should not store physical address or contact information as a customer may have multiple addresses and multiple contacts such as office phone with multiple extensions and have a cell phone for work and home.
Look for opportunities to store repeating data e.g. contact title, categories etc. these items can be setup in child tables and linked to primary tables via foreign keys.
Learn to use SSMS Profiler to learn if indices may speed up data operations.
Avoid using SELECT * FROM SomeTable, only select the fields required for a specific task.
Do not use connection objects that are public, available to all classes and/or forms in a Visual Studio solution. Best practices, create a connection and if needed a command object in each method requiring these objects implemented with using statements. In the following code sample using statements are used for a connection and command object. Once executing the code both objects are disposed of.
Public Function Read() As DataTable
Dim dt As New DataTable
Dim selectStatement = "SELECT id,FirstName,LastName FROM dbo.Persons1 ORDER BY LastName"
Using cn As New SqlConnection() With {.ConnectionString = ConnectionString}
Using cmd As New SqlCommand() With {.Connection = cn, .CommandText = selectStatement}
cn.Open()
dt.Load(cmd.ExecuteReader)
End Using
End Using
Return dt
End Function
Database constraints
Learn how to assert work with constraints, for instance, adding a duplicate record is inserting directly into a table from SQL-Server Management Studio while another would be providing an administrator the ability to add new records without any form of assertion in an application. See .NET: Defensive data programming (Part 2) for how to deal with common constraint violations.
Security
Decide on which approach to take for securing databases.
- Windows authentication. Microsoft recommend using Windows authentication wherever possible. Windows authentication uses a series of encrypted messages to authenticate users in SQL Server. When SQL Server logins are used, SQL Server login names and encrypted passwords are passed across the network, which makes them less secure.
- Mix mode, windows and sql server authentication
Usually Windows authentication will fit most situations while in other cases SQL-Server authentication with a login form is best, review the following TechNet article on creating a secure login with user name and password and the following TechNet article on creating users and roles in a SQL-Server database.
Command parameters
Always use command parameters for WHERE conditions, never use string concatenation to build a query. Write the query in SSMS with DECLARE statements for parameters where the DECLARE statements in code are command parameters. See the following TechNet article for learning how to work with parameters.
The following code sample shows how to use parameters for updating an existing record.
Public Function Update(firstName As String, lastName As String, identifier As Integer) As Boolean
Using cn As New SqlConnection() With {.ConnectionString = "TODO"}
Dim statement =
"SELECT 1 FROM dbo.Persons1 AS p " &
"WHERE p.FirstName = @FirstName AND p.LastName = @LastName "
Using cmd As New SqlCommand() With {.Connection = cn, .CommandText = statement}
cmd.Parameters.AddWithValue("@FirstName", firstName)
cmd.Parameters.AddWithValue("@LastName", lastName)
cn.Open()
If cmd.ExecuteScalar() Is Nothing Then
cmd.Parameters.AddWithValue("@id", identifier)
cmd.ExecuteNonQuery()
Return True
Else
Return False
End If
End Using
End Using
End Function
Stored procedures
When considering options for injecting SQL statements into a program look at having these statements directly in code or in stored procedures. To learn about stored procedures used in Visual Basic code see SQL-Server Stored Procedures for VB.NET Windows Forms.
Accessing data considerations
When building a program, which interacts with, databases consider one of the following directions.
- For the occasion developer working with a single table in a database using a TableAdapter or DataAdapter are perfect for simple single table database solution while Entity Framework or Entity Framework Core would be an option for learning purposes.
- Building an application with a relational database, Entity Framework or Entity Framework Core would be best choice as Entity Framework is flexible, easy to use unlike TableAdapter, which is easy to use although not flexible.
A starter Windows Form application code sample using Entity Framework Core. ASP.NET Core Blazer Entity Framework code sample.
Entity Framework
Besides working with DataSet/DataTable containers for interacting with data, consider working with Entity Framework. A good place to start for Windows Forms is Entity Framework 6 which is a tried and tested object-relational mapper which reduces the impedance mismatch between the relational and object-oriented worlds, enabling developers to write applications that interact with data stored in relational databases using strongly-typed .NET objects that represent the application's domain, and eliminating the need for a large portion of the data access "plumbing" code that they usually need to write.
Going back to structuring a solution, the following is a recommended structure when working with multiple databases and tables.
To give Entity Framework 6 a spin see the following TechNet article Structuring an Entity Framework project for Windows Forms. For an overview of Entity Framework see the following Microsoft Entity Framework documentation.
Unit testing
Unit testing is a level of software testing where individual units/ components of a software are tested. The purpose is to validate that each unit of the software performs as designed. A unit is the smallest testable part of any software. It usually has one or a few inputs and usually a single output. In procedural programming, a unit may be an individual program, function, procedure, etc. In object-oriented programming, the smallest unit is a method, which may belong to a base/ super class, abstract class or derived/ child class.
All projects can benefit from unit testing as verifying units/ components validate the code works as expected. If and when there is a bug within the program unit test may find the problem without actually debugging the program.
Microsoft Visual Studio supports various flavors of unit testing which can be learned more about under Getting started with unit testing.
Visual Studio also supports "Live unit testing" which is a productivity feature, which provides real-time feedback directly in the editor on how code changes are impacting your unit tests and your code coverage. This will help you maintain quality by keeping the tests passing as you make changes.
Asynchronous programming
Asynchrony is essential for activities that are potentially blocking, such as when your application accesses the web or a file system. Access to a web resource, for example, is sometimes slow or delayed. If such an activity is blocked within a synchronous process, the entire application must wait. In an asynchronous process, the application can continue with other work that doesn’t depend on the web resource until the potentially blocking task finishes.
Understand when to implement Asynchronous task as in many cases there are alternate paths. Asynchronous operations done incorrectly may slow down processing making start to finish a task longer than a synchronous task.
In some cases, an alternate approach may resolve unresponsive user interface.
For example, reading millions of records from a database into a grid followed by triggering events to format data which will take minutes to finish loading followed by if a user resizes a column this will cause the grid to become unresponsive once more. A better solution is to determine exactly what data is needed by providing a filtering system to narrow down records to present.
Options to consider, Iterator-yield, BackgroundWorker, Async-await for keeping an application responsive.
For a comprehensive guide see Asynchronous programming with Async and Await (Visual Basic).
Windows Forms user input validation
When accepting information in a windows form consider using DataAnnotations to validate input rather than using inline validation in a form. Using DataAnnotations standard attributes or custom attributes designed for a project's specific needs keeps the input window code clean and provides reusability.
The following form will serve as a simple model for using Data annotations. Each input, including the password confirmation all require validation which will be done by declaring properties in a class.
To ensure all required fields are accounted for they setup with Required attribute.
<Required()>
Public Property UserName() As String
Optional, provide error text were {0} is the property name (.
<Required(ErrorMessage:="{0} is required")>
Public Property UserName() As String
Since UserName would not look nice to a user, instead User Name would so a language extension method will remedy this.
<Runtime.CompilerServices.Extension>
Public Function SplitCamelCase(sender As String) As String
Return Regex.Replace(
Regex.Replace(sender,
"(\P{Ll})(\P{Ll}\p{Ll})", "$1 $2"), "(\p{Ll})(\P{Ll})", "$1 $2")
End Function
If a business rule indicates min and max length of a property StringLengthAttribute will handle this.
When working with ranges e.g. a valid range of years for a credit card expiration. In the ErrorMessage, {0} is 2019, {1} is 2022. Note the change in ErrorMessage for Required does not use {0}, this permits user defined messages.
<Required(ErrorMessage:="Credit card expire year required")>
<Range(2019, 2022, ErrorMessage:="{0} is not valid {1} to {2} are valid")>
Public Property CreditCardExpireYear() As Integer
For validating two inputs which are required to match, like a password.
<Required(ErrorMessage:="{0} is required"), DataType(DataType.Text)>
<StringLength(20, MinimumLength:=6)>
<PasswordCheck(ErrorMessage:="Must include a number and symbol in {0}")>
Public Property Password() As String
<Compare("Password", ErrorMessage:="Passwords do not match, please try again"),
DataType(DataType.Text)>
<StringLength(20, MinimumLength:=6)>
Public Property PasswordConfirmation() As String
PasswordCheck is a class which inherits ValidationAttribute class which allows a developer to extend validation.
Imports System.ComponentModel.DataAnnotations
Imports System.Text.RegularExpressions
Namespace Rules
''' <summary>
''' Specialized class to validate a password
''' </summary>
Public Class PasswordCheck
Inherits ValidationAttribute
Public Overrides Function IsValid(value As Object) As Boolean
Dim validPassword = False
Dim reason = String.Empty
Dim password As String = If(value Is Nothing, String.Empty, value.ToString())
If String.IsNullOrWhiteSpace(password) OrElse password.Length < 6 Then
reason = "new password must be at least 6 characters long. "
Else
Dim pattern As New Regex("((?=.*\d)(?=.*[a-z])(?=.*[A-Z])(?=.*[@#$%]).{6,20})")
If Not pattern.IsMatch(password) Then
reason &= "Your new password must contain at least 1 symbol character and number."
Else
validPassword = True
End If
End If
If validPassword Then
Return True
Else
Return False
End If
End Function
End Class
'-------------------------------------------------------------------------------
' Pattern used above
'-------------------------------------------------------------------------------
' ( # Start of group
' (?=.*\d) # must contains one digit from 0-9
' (?=.*[a-z]) # must contains one lowercase characters
' (?=.*[A-Z]) # must contains one uppercase characters
' (?=.*[@#$%]) # must contains one special symbols in the list "@#$%"
' . # match anything with previous condition checking
' {6,20} # length at least 6 characters and maximum of 20
') # End of group
'-------------------------------------------------------------------------------
End Namespace
To validate the form shown above, a instance of CustomerLogin is created, properties are populated with input controls on the form then passed to a validator which validates against the attributes on the properties for CustomerLogin.
Namespace Validators
Public Class ValidationHelper
Public Shared Function ValidateEntity(Of T As Class)(entity As T) As EntityValidationResult
Return (New EntityValidator(Of T)()).Validate(entity)
End Function
End Class
End Namespace
In turn a check is done to see if there are any bad inputs from empty inputs, passwords not matching to invalid credit card numbers.
Dim login As New CustomerLogin With
{
.UserName = UserNameTextBox.Text,
.Password = PasswordTextBox.Text,
.PasswordConfirmation = PasswordConfirmTextBox.Text,
.EmailAddress = EmailTextBox.Text,
.FirstName = FirstNameTextBox.Text,
.LastName = LastNameTextBox.Text,
.CreditCardNumber = CreditCardTextBox.Text,
.CreditCardExpireMonth = ExpireMonthTextBox.AsInteger,
.CreditCardExpireYear = ExpireYearTextBox.AsInteger,
.CreditCardCode = CreditCardCode.Text
}
'
' Perform all required validation
'
Dim validationResult As EntityValidationResult = ValidationHelper.ValidateEntity(login)
If validationResult.HasError Then
Errors can be shown in a MessageBox or presented in a ErrorProvider.
See full source code in the following GitHub repository and the following GitHub repository for some unusual validation rules code samples.
Exception handling
A well-designed application handles exceptions and errors to prevent application crashes. This is accomplished first using assertion when there is a possibility of an exception being thrown.
For instance, reading lines from a file. In the following example, if the file does not exists an exception is thrown.
Imports System.IO
Namespace Classes
Public Class FileOperations
Public Function ReadFile(fileName As String) As List(Of String)
Return File.ReadAllLines(fileName).ToList()
End Function
End Class
End Namespace
To prevent this assert that the file exists, if the file exists read and return a list, if the file does not exists, return an empty list.
Imports System.IO
Namespace Classes
Public Class FileOperations
Public Function ReadFile(fileName As String) As List(Of String)
If File.Exists(fileName) Then
Return File.ReadAllLines(fileName).ToList()
Else
Return New List(Of String)
End If
End Function
End Class
End Namespace
A better option would be to check if the file exists, if the file exists attempt to read the file. Even if the file exists another process may have locked the file preventing a read operation which calls for a try-catch statement. Rather than having the caller check for a empty list, return an instance of a class as per below.
Namespace Classes
Public Class ReadResults
Public Property Lines() As List(Of String)
Public Property Success() As Boolean
Public ReadOnly Property HasErrors() As Boolean
Get
If Exception IsNot Nothing Then
Success = False
Return True
Else
Return False
End If
End Get
End Property
Public Property Exception() As Exception
End Class
End NameSpace
The revised read operation.
Imports System.IO
Namespace Classes
Public Class FileOperations
Public Function ReadFile(fileName As String) As ReadResults
Dim results = New ReadResults
If File.Exists(fileName) Then
Try
results.Lines = File.ReadAllLines(fileName).ToList()
Catch ex As Exception
results.Exception = ex
End Try
Else
results.Exception = New FileNotFoundException(fileName)
End If
Return results
End Function
End Class
End Namespace
Another example is to have a connection object setup as a private variable to a form of class for opening a database e.g.
If conn.State <> ConnectionState.Closed Then
conn.Close()
End IF
Since there may be issues or poorly constructed logic the above may throw an exception, for this reason a try-catch is in order.
Try
conn.Close()
Catch ex As InvalidOperationException
Console.WriteLine(ex.GetType().FullName)
Console.WriteLine(ex.Message)
End Try
In the last two code samples for connecting to a database (taken from Microsoft docs) this is a way of showing how to prevent an exception. The example unfortunately is not a recommended method to connect to databases, instead for each operation a local connection should be made.
In the following example the connection is locally scoped to the procedure rather than the class. Using the same concept as reading a file, return a class instance of ReadResults where there is a List of Person rather than a list of string.
Person class
Namespace DataOperations
Public Class Person
Public Property Identifier() As Integer
Public Property FirstName() As String
Public Property LastName() As String
End Class
End Namespace
Results class
Namespace DataOperations
Public Class ReadResults
Public Property List() As List(Of Person)
Public Property Success() As Boolean
Public ReadOnly Property HasErrors() As Boolean
Get
If Exception IsNot Nothing Then
Success = False
Return True
Else
Return False
End If
End Get
End Property
Public Property Exception() As Exception
End Class
End Namespace
Read operation
Public Function ConnectAndRead() As ReadResults
Dim results = New ReadResults With {
.List = New List(Of Person)()
}
Using cn As New SqlClient.SqlConnection With {.ConnectionString = ConnectionString}
Using cmd As New SqlClient.SqlCommand With {.Connection = cn}
cmd.CommandText = "SELECT Identifier, FirstName, LastName FROM People;"
Try
cn.Open()
Dim reader = cmd.ExecuteReader
If reader.HasRows Then
While reader.Read()
results.List.Add(New Person() With
{
.Identifier = reader.GetInt32(0),
.FirstName = reader.GetString(1),
.LastName = reader.GetString(2)
})
End While
results.Success = True
End If
Catch ex As Exception
results.Exception = ex
End Try
End Using
End Using
Return results
End Function
In the above examples a special class was needed which needs to be created for each each project tailored to what is being returned. Another path is to create special classes, publish to NuGet and when needed added to projects via NuGet Package Manager.
With this path assertion looks like the following.
Imports BaseConnectionLibrary.ConnectionClasses
Public Class Form1
Private Sub sqlServerExistsButton_Click(sender As Object, e As EventArgs) _
Handles sqlServerExistsButton.Click
Dim mockedConnectionString =
"Data Source=KARENS-PC;" &
"Initial Catalog=NorthWindAzure3;" &
"Integrated Security=True"
Dim ops As New ConnectionValidator
Dim result = ops.OpenTest(Providers.SQLClient, mockedConnectionString)
If result Then
MessageBox.Show("Connection opened successfully")
Else
If ops.HasException Then
MessageBox.Show(
$"Failed opening connection{Environment.NewLine}{ops.LastExceptionMessage}")
End If
End If
End Sub
End Class
Full source code for implementing the above assertion can be found in the following GitHub Repository.
When exceptions are thrown if a specific exception is encountered multiple catches can be used, for example, a constraint violation on a update or insert into a database table.
Try
' operation which may fail
Catch ed As SqlException When ed.ErrorCode = 42
' handle constraint violation
Catch ex As Exception
' handle all other exceptions
End Try
To handle all SqlExceptions
Try
' operation which may fail
Catch ed As SqlException
' handle any data exception
Catch ex As Exception
' handle all other exceptions
End Try
For a full list of exception handling refer to Handling and throwing exceptions in .NET.
See also: Best practices for exceptions.
Summary
This article has presented best practices and recommendation for writing more efficient and maintainable code which should be considered at base to work from but not all inclusive in regards to covering all topics to build superior applications. From here apply what has been learned to other topics such as working with LINQ, Lambda, for and if statements to name a few.
See also
VB.NET: Invoke Method to update UI from secondary threads
VB.NET Fluent Builder Design Pattern
VB.NET Type conversions (Part 1)
MS-Access with VB.NET: Best practices (Part 1)
VB.NET: Upgrading from MS-Access to SQL-Server (Part 1/3)
SQL-Server database login for Windows Forms (VB.NET)
How to Handle a Huge Collection of Strings in VB.Net
.NET: Defensive data programming (part 1)
VB.Net: SQL Injection Protection Using Parameterized Queries
VB.NET: Essential Tuples
Database selective connection strings (VB.NET)