VB.NET offers a streamlined approach to database operations, with a robust framework. Using its power, you can obtain relevant information quickly and with minimal effort.
Take a look at some practical examples that show how to use VB.NET to perform SQL queries, and see how you can ensure data retrieval is both effective and efficient.
Setting Up Your Local SQL Server
Start by setting up a SQL server to review everything step by step. In the examples below you will see a Windows environment, but if you are using a different operating system like Linux and have a different SQL server, don’t worry; the general logic will remain the same.
Due to its simplicity and zero configuration approach, SQLite is an excellent choice for beginners.
To set things up, create a new folder, then open a command prompt and navigate to it. Run the following command to create a new .NET project in which you can use the VB.NET language:
dotnet new console -lang VB -o MyVBApp
You now have a project called MyVBApp. Continue the setup by integrating the SQLite package into your VB.NET project using NuGet, a popular package manager for .NET. Run this command:
dotnet add package System.Data.SQLite
After you’ve added SQLite, you can set up a local database effortlessly.
You can find all the code for these examples in the project’s GitHub repository.
Download the InitializeDatabase.vb file from the project’s repository. This particular file will help you configure your database. As you can see in this file there are some users and users' countries. You can use this as a sample database.
The command you used to create the VB.NET project created a file named Program.vb. Open this file and update it as follows:
Module Program
Sub Main(args As String())
DatabaseInitializer.InitializeDb()
End Sub
End Module
Run this program and you should see it create a file named mydatabase.db. This is the simple database that you will use in the following examples.
Establishing a Database Connection With SQL in VB.NET
Establishing a connection using SQLite in VB.NET is straightforward. Continue editing the Program.vb file and remove the existing contents of the Main subroutine. This file serves as the project's core.
You can define a connection to the database file, mydatabase.db, with this line of code:
Dim connectionString As String = "Data Source=mydatabase.db;Version=3;"
Data Source specifies the database file name. If the file doesn't exist, SQLite will create a new database when it establishes a connection.
The next step is to use the SQLiteConnection class to create a connection instance. You should always use a Using block when working with database connections to avoid potential leaks or deadlocks:
Using conn As New SQLiteConnection(connectionString)
conn.Open()
' Database operations go here
End Using
The Using block ensures that the connection is automatically closed when it completes.
Your final Program.vb file should look something like this:
Imports System
Imports System.Data.SQLite
Module Program
Sub Main(args As String())
Dim connectionString As String = "Data Source=mydatabase.db;Version=3;"
Try
Using conn As New SQLiteConnection(connectionString)
conn.Open()
Console.WriteLine("Successfully connected to the database!")
'You can perform database operations here.
End Using
Catch ex As SQLiteException
Console.WriteLine("Error: " & ex.Message)
End Try
End Sub
End Module
This code will connect to the mydatabase.db database and print a confirmation message when it succeeds. If an error occurs, it will print details to the console.
How to Fetch Data and Load It Into an Array
The SELECT SQL command is the main way of fetching data from an SQL database. If you have a table named Users in your database and you want to get the Name field from every record in that table, use SELECT like this:
SELECT Name FROM Users
You can pull data from the database and load it into an array by adding this query to the Program.vb file:
Dim query As String = "SELECT Name FROM Users"
Dim names As New List(Of String)()
Using conn As New SQLiteConnection(connectionString)
conn.Open()
Using cmd As New SQLiteCommand(query, conn)
Using reader As SQLiteDataReader = cmd.ExecuteReader()
While reader.Read()
names.Add(reader("Name").ToString())
End While
End Using
End Using
End Using
' Now the 'names' list is full of users' names.
' You can convert this list to an array if you want:
Dim namesArray() As String = names.ToArray()
' Print array content for testing purposes
For Each name In namesArray
Console.WriteLine(name)
Next You’ll see a list of names on the console, corresponding to the contents of your database table:
This code loads the data into a List structure—which has a dynamic size—before converting it to an array on completion. This approach is very useful for situations where you do not know in advance the number of records you’ll retrieve.
How to Use INSERT to Add Data to a Database
You can use the INSERT INTO command to add new data to a database. For example, consider the Users table that has two columns named Name and Country.
The basic SQL query you can use to add a new user would be:
INSERT INTO Users (Name, Country) VALUES ('Fatih', 'TR')
To add a new user to the database using this query, update the Program.vb file as follows:
Dim query As String = $"INSERT INTO Users (Name, Country) VALUES ('{name}', '{country}')"
Using conn As New SQLiteConnection(connectionString)
conn.Open()
Using cmd As New SQLiteCommand(query, conn)
cmd.ExecuteNonQuery()
End Using
End Using
Console.WriteLine($"{name} has been successfully added to the database!") This simple example uses string interpolation to build the query, but you should avoid this in production code since it’s vulnerable to SQL injection. The alternative is parameterized queries which make database operations safer and more efficient.
Parameterized queries use placeholders, instead of direct string concatenation, to add values to SQL queries. This approach will help you avoid many security threats:
Dim query As String = "INSERT INTO Users (Name, Country) VALUES (@Name, @Country)"
'...
cmd.Parameters.AddWithValue("@Name", userName)
cmd.Parameters.AddWithValue("@Country", userCountry)
Any Other Tips for Working With a Database From Within VB.Net
Database operations in VB.NET might initially seem daunting, but with a few guidelines, you can easily master the basics.
- Use parametrized queries to guard against security vulnerabilities.
- Always close your database connection when you’ve finished fetching or updating data.
- Maintain your database to optimize its structure as your data model changes over time.
- Don't forget to make backup copies in case of emergencies.
As with any technology, databases evolve. Software gets updated, new tools emerge, and we discover better ways of doing things. It's a good idea to stay informed and updated. Some tools act as intermediaries, like Entity Framework, making it easier to write database-related code.
How to Take Your VB.NET Journey Further
VB.NET, with its deep-rooted connection to the Microsoft ecosystem, is both robust and user-friendly. To truly grasp its power, start with the official documentation provided by Microsoft. From there, explore online courses, forums, and communities, where experienced developers share their knowledge and insights.
Remember, every expert was once a beginner. With consistent effort, curiosity, and the right resources, you'll soon find yourself navigating VB.NET with confidence and ease. As you progress, don't hesitate to experiment, ask questions, and—most importantly—enjoy the process of discovery.