Parsing SQL text

Question

Does anyone know how to parse SQL Text with VB.NET?

Ex: I got a sql file "CREATE TABLE..." i want to get an array of columns and an array of data types.

Answer 1

It may be the easiest approach to feed that statement to an SQL Server and actually create that table in a temp database.

After that, finding out about the table structure would be easy.

All you'd have to parse out of the statement string would be the name of the table. Even better, you could simply replace it and have a known table name from the start.

Additionally, you would get the info if the statement is even valid SQL.

Answer 2

Expanding on @Tomalak's post: once you have the table built you can use a DataReader to select just 1 line if you only need the schema or your actual data, then do something like this:

Dim myReader As DataReader
Dim myTable As DataTable

Dim myColumns As New Collection

myReader =  //' get your data

If myReader.HasRows Then
    myTable.Load(myReader)
    For Each col As DataColumn In myTable.Columns
        myColumns.Add(col.DataType.ToString, col.ColumnName)
    Next
End If

The collection myColumns will now have a Key of the column's name and the Value is the columns datatype. You can modify this to make 2 separate collections if you need.

Parsing a string on the other hand will involve significantly more debugging and offer lots of room for error.