DBCC CheckDb - get sql messages in VB.NET

Question

I am using this code to check my database for errors :

Dim cmd As New SqlCommand("DBCC CHECKDB (offpoDb) WITH TABLERESULTS", con)
cmd.ExecuteNonQuery()

But, u see, this command only generates SQL messages.

Is there any way to retrieve the messages in .net ?
Can i show the messages in a MessageBox ?

I've studied InfoMessage but i still fail to understand how to apply it/work with it.

Answer 1

Use a SqlDataReader instead of ExecuteNonQuery to get the recordset returned by TABLERESULTS:

Dim strBuilder As New System.Text.StringBuilder
Using cmd As New SqlClient.SqlCommand("DBCC CHECKDB (offpoDb) WITH TABLERESULTS", con)
    Dim reader As SqlClient.SqlDataReader
    reader = cmd.ExecuteReader

    While reader.Read
        strBuilder.AppendLine(CStr(reader("MessageText")))
    End While

    reader.Close()
End Using

MessageBox.Show(strBuilder.ToString)

To see all columns which are returned, execute the query in SQL Server Management Studio.

If you prefer to use the InfoMessage-event then add a handler and use it like following:

Sub MyMethod()
    Using con As New SqlClient.SqlConnection("<yourConnectionString>")
        con.Open()

        AddHandler con.InfoMessage, AddressOf InfoMessage

        Using cmd As New SqlClient.SqlCommand("DBCC CHECKDB (offpoDb)", con)
            cmd.ExecuteNonQuery()
        End Using

        con.Close()
    End Using
End Sub

Private Sub InfoMessage(sender As Object, e As SqlClient.SqlInfoMessageEventArgs)
    MessageBox.Show(e.Message)
End Sub