Visual Basic and SQL Server – Selecting Data

When performing an operation on an SQL Server database, such as selecting, inserting, updating and deleting data, the first thing that needs to be done is to connect to the database, as previously described.

In order to retrieve data, as well as insert, update and delete data, from an SQL Server database, SQL, or Structured Query Language needs to be used, more details of which can be found here. Retrieving data is done via the ‘Select’ statement.

The following table of data, called ‘person’, will be used in the example below for selecting data.

id firstname lastname title dob
1 Bob Smith Mr 1980-01-20
2 George Jones Mr 1997-12-15
3 Fred Bloggs Mr 1975-05-07
4 Alan White Mr 1989-03-20

Note that by default, dates in an SQL Server database are stored in the format YYYY-MM-DD (four digit year, two digit month and two digit day).

The example below selects four items of data from the ‘person’ table, in last name, first name and date of birth order. The resulting data is stored in a ‘reader’ object, which is then used in a ‘Do-While‘ loop to output details of each record to the console in the format: “id: lastname, firstname (dob)”. Note that the second ‘Try-Catch’ block has a ‘Finally’ section that closes the database connection, regardless of whether the data retrieval is successful or not.

' Database connection variable.
Dim connect = New SqlConnection(
    "Server=localhost\MSSQLSERVERDEMO; Database=Demo;" &
    "User Id=DemoUN; Password=DemoPW")

Try

    ' Connect to database.
    connect.Open()

Catch ex As Exception

    ' Confirm unsuccessful connection and stop program execution.
    Console.WriteLine("Database connection unsuccessful.")
    End

End Try

Try

    ' Query text.
    Dim sqlText As String =
        "SELECT id, firstname, lastname, dob " &
        "FROM dbo.person " &
        "ORDER BY lastname, firstname, dob"

    ' Query text incorporated into SQL command.
    Dim sqlSelect As New SqlCommand(sqlText, connect)

    ' Execute SQL and place data in a reader object.
    Dim reader As SqlDataReader = sqlSelect.ExecuteReader()

    ' Display person information in the console.
    Do While reader.Read()

        Console.WriteLine("{0}: {1}, {2} ({3})",
                  reader(0), reader(2), reader(1),
                  Format(reader(3), "dd/MM/yyyy"))

    Loop

    ' Close the reader.
    reader.Close()

Catch ex As Exception

    ' Confirm error retrieving person information and exit.
    Console.WriteLine("Error retrieving person information.")
    End

Finally

    ' Close the database connection.
    connect.Close()

End Try

The resulting output to the console is as follows.

3: Bloggs, Fred (07/05/1975)
2: Jones, George (15/12/1997)
1: Smith, Bob (20/01/1980)
4: White, Alan (20/03/1989)

Often it isn’t necessary to return all records from a database table. Where this is the case, parameters need to be introduced into the query. In the following example, the records returned are limited to those with a date of birth between 1 January 1980 and 31 December 1989. Within the SQL, text preceded by ‘@’ symbols are used to signify that parameters need to be incorporated. The parameter values are then bound into the SQL statement before it is executed. Binding the parameters in this way helps prevent SQL injection, where hackers try to insert malicious code to either do damage to the database or access more data than should be allowed.

' Database connection variable.
Dim connect = New SqlConnection(
    "Server=localhost\MSSQLSERVERDEMO; Database=Demo;" &
    "User Id=DemoUN; Password=DemoPW")

Try

    ' Connect to database.
    connect.Open()

Catch ex As Exception

    ' Confirm unsuccessful connection and stop program execution.
    Console.WriteLine("Database connection unsuccessful.")
    End

End Try

Try

    ' Query parameters.
    Dim dobLower As Date = "1980-01-01"
    Dim dobUpper As Date = "1989-12-31"

    ' Query text.
    Dim sqlText As String =
        "SELECT id, firstname, lastname, dob " &
        "FROM dbo.person " &
        "WHERE dob BETWEEN @dobLower AND @dobUpper " &
        "ORDER BY lastname, firstname, dob"

    ' Query text incorporated into SQL command.
    Dim sqlSelect As New SqlCommand(sqlText, connect)

    ' Bind the parameters to the query.
    sqlSelect.Parameters.AddWithValue("@dobLower", dobLower)
    sqlSelect.Parameters.AddWithValue("@dobUpper", dobUpper)

    ' Execute SQL and place data in a reader object.
    Dim reader As SqlDataReader = sqlSelect.ExecuteReader()

    ' Display person information in the console.
    Do While reader.Read()

        Console.WriteLine("{0}: {1}, {2} ({3})",
                          reader(0), reader(2), reader(1),
                          Format(reader(3), "dd/MM/yyyy"))

    Loop

    ' Close the reader.
    reader.Close()

Catch ex As Exception

    ' Confirm error retrieving person information and exit.
    Console.WriteLine("Error retrieving person information.")
    End

Finally

    ' Close the database connection.
    connect.Close()

End Try

The resulting output to the console is as follows.

1: Smith, Bob (20/01/1980)
4: White, Alan (20/03/1989)