Working with Databases: ADO.NET Basics

August 18, 2024

Working with Databases: ADO.NET Basics

In this post, we will explore how to connect and interact with databases using ADO.NET in Visual Basic. ADO.NET is a powerful framework for data access that allows you to retrieve, manipulate, and update data in a variety of database systems. Whether you’re working with SQL Server, Oracle, or another database, ADO.NET provides a consistent way to interact with your data.

What is ADO.NET?

ADO.NET is a set of classes in the .NET Framework that allows you to connect to databases, execute commands, and retrieve results. It consists of two main components:

• Connected Model: This model maintains a continuous connection to the database, allowing for real-time data updates.
• Disconnected Model: This model retrieves data into a dataset and works with it offline, which is more efficient for certain applications.

Setting Up Your Database Connection

Before you can interact with a database, you need to establish a connection. Here’s how to do that using ADO.NET:

Imports System.Data.SqlClient

Dim connectionString As String = "Data Source=your_server;Initial Catalog=your_database;User ID=your_username;Password=your_password;"
Dim connection As New SqlConnection(connectionString)

In the code above, replace your_server, your_database, your_username, and your_password with your actual database connection details.

Opening the Connection

Once you have your connection object set up, you can open the connection to the database:

Try
    connection.Open()
    Console.WriteLine("Connection opened successfully.")
Catch ex As Exception
    Console.WriteLine("Error: " & ex.Message)
End Try

Executing Commands

After establishing a connection, you can execute SQL commands. Here’s how to perform a simple SELECT query:

Dim command As New SqlCommand("SELECT * FROM your_table", connection)
Dim reader As SqlDataReader = command.ExecuteReader()

While reader.Read()
    Console.WriteLine(reader("column_name"))
End While
reader.Close()

In this example, replace your_table and column_name with the actual table and column names from your database.

Updating Data

To update data in your database, you can use an UPDATE statement. Here’s an example:

Dim updateCommand As New SqlCommand("UPDATE your_table SET column_name = @value WHERE condition", connection)
updateCommand.Parameters.AddWithValue("@value", newValue)

Try
    Dim rowsAffected As Integer = updateCommand.ExecuteNonQuery()
    Console.WriteLine(rowsAffected & " rows updated.")
Catch ex As Exception
    Console.WriteLine("Error: " & ex.Message)
End Try

This code updates a specific column in your table based on a condition. Remember to replace your_table, column_name, and condition with your actual values.

Closing the Connection

Finally, always ensure that you close your database connection once you are done:

connection.Close()
Console.WriteLine("Connection closed.")

Conclusion

In this post, we covered the basics of working with databases using ADO.NET in Visual Basic. You learned how to establish a connection, execute commands for data retrieval and updates, and manage your database connections effectively. With these foundational skills, you can start building more complex applications that interact with databases.

Stay tuned for our next post, where we will dive deeper into advanced ADO.NET features!