Understanding Connections and Data Readers

To establish a connection to a database, you need to create an instance of the SqlConnection class passing the connection string as an argument. Then you invoke the Open method to open the connection so that you can perform your data operations. Finally, you invoke Close to close the connection. The following code, which requires an Imports System.Data.SqlClient directive, demonstrates how you establish a connection to the Northwind database:

Utilizing a Using..End Using block ensures that the connection will be correctly released without the need of invoking Close.

Inserting Data

To perform an insert operation, you create an instance of the SqlCommand class passing the SQL instructions that perform the actual insertion. The constructor also requires the connection to be specified. The following code demonstrates this:

Notice how you can provide parameterized query strings specifying values with the SqlCommand.Parameters.AddWithValue method. The code adds a new customer to the Customers table in the database, specifying to fields, CompanyName, and CustomerID. The ExecuteNonQuery method allows executing a Transact-SQL operation instead of a simple query.

Updating Data

Updating data works similarly to inserting, in that you write the same code, changing the SQL instructions in the query string. The following code provides an example affecting the previously added customer:

So, you simply use an Update SQL instruction.

Deleting Data

Deletion works the same as other operations, differing only about the SQL code that uses a Delete statement. The following code demonstrates this:

Querying Data

Querying data is the last operation; it is important because it demonstrates a fundamental object: the SqlDataReader. The object allows retrieving a series of rows from the specified database object. The following code demonstrates how you can retrieve a series of customers:

The query string contains a projection statement that allows querying data. The ExecuteReader method sends the reading query string to the data source and retrieves the desired information. Now that you have recalled the ways for working with data in a connected mode, it is time to recall the disconnected mode and DataSets.