Better way to print out rows from a datatable in vb.net

Question

I am new to vb.net and I am trying to query a database and print out the records in the row to the console window. I got it to work, but I have a feeling that there is a more concise way to do this. One thing that I am sure is wrong is that I had to convert the dataset to a datatable to be able to retrieve the values. Is that correct? Could you take a look at the code below (especially the for loop) and let me know what I can improve upon?

Thanks!

Module Module1

Sub Main()

    Dim constring As String = "Data Source=C:\Users\test\Desktop\MyDatabase1.sdf"
    Dim conn As New SqlCeConnection(constring)
    Dim cmd As New SqlCeCommand("SELECT * FROM ACCOUNT")
    Dim adapter As New SqlCeDataAdapter
    Dim ds As New DataSet()

    Try
        conn.Open()
        cmd.Connection = conn
        adapter.SelectCommand = cmd
        adapter.Fill(ds, "testds")
        cmd.Dispose()
        adapter.Dispose()
        conn.Close()

        Dim dt As DataTable = ds.Tables.Item("testds")
        Dim row As DataRow
        Dim count As Integer = dt.Columns.Count()

        For Each row In dt.Rows
            Dim i As Integer = 0
            While i <= count - 1
                Console.Write(row(i))
                i += 1
            End While
            Console.WriteLine(Environment.NewLine())
        Next

    Catch ex As Exception
        Console.WriteLine("There was an error")
        Console.WriteLine(ex)
    End Try

    Console.ReadLine()

End Sub

End Module

Solution

Here is how I would rewrite this for a few reasons:

1) You should always use Using statements with disposable objects to ensure they are correctly cleaned up. You had a good start with the dispose commands, but this way is safer.

2) It is more efficient to use ExecuteReader than loading everything into a dataset.

3) Your try/catch statement should include object creation as well as execution.

Finally, in response to your question about datasets and datatables, that code was absolutely correct: a dataset consists of zero or more datatables, so you were just extracting the existing datatable from the dataset.

    Try
        Dim constring As String = "Data Source=C:\Users\test\Desktop\MyDatabase1.sdf"

        Using conn As New SqlCeConnection(constring)
            conn.Open()
            Using cmd As New SqlCeCommand("SELECT * FROM ACCOUNT", conn)
                Dim reader As SqlCeDataReader

                reader = cmd.ExecuteReader()
                Do While reader.Read
                    For i As Integer = 0 To reader.FieldCount - 1
                        Console.Write(reader.GetString(i))
                    Next
                    Console.WriteLine(Environment.NewLine())
                Loop
            End Using
        End Using
    Catch ex As Exception
        Console.WriteLine("There was an error")
        Console.WriteLine(ex)
    End Try

    Console.ReadLine()
End Sub

One last note: since you are just printing to the console, it doesn't matter as much, but whenever you deal with a lot of strings, especially those that are to be concatenated, you should always consider using System.Text.StringBuilder.

Here is an example rewrite of the loop that prints to the console using stringbuilder (builds the string in memory, then dumps it to the console; I have also added the field name for good measure):

 Dim sbOutput As New System.Text.StringBuilder(500)
 For i As Integer = 0 To reader.FieldCount - 1
     If sbOutput.Length <> 0 Then
         sbOutput.Append("; ")
     End If
     sbOutput.Append(reader.GetName(i)).Append("=").Append(reader.GetString(i))
 Next
 sbOutput.AppendLine()
 Console.Write(sbOutput.ToString)