I am trying to insert rows of data as they are being read by TextFieldParser into a Sql Db Table and I a not quite sure how to go about it. The code I have posted is my thus far failed attempt

I have succesfully used the TextFieldParser to create a DataTable in memory but have NO experience with Sql and not even sure if what I am attemting is doable

I have googled and read about bulk transact but I want to try and understand my ill logic in how I am goiing about this it its present.

I really wanted to be able to create the table dynamically and inset it into sql, in otherwords create the columns and rows just like the Table in memory


Using myReader As New Microsoft.VisualBasic.FileIO.TextFieldParser(filepath)
Dim conn As String = "Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\Bill\Documents\Visual Studio 2008\WebSites\Emailer\App_Data\Database.mdf;Integrated Security=True;User Instance=True"
Dim connection As New SqlConnection(conn)
Dim command As SqlCommand
myReader.SetDelimiters(vbTab)
Dim currentRow As String()
currentRow = myReader.ReadFields()
Dim colNameList As New List(Of String)
Dim colName As String = String.Empty
For i As Integer = 0 To currentRow.GetUpperBound(0)
colName = currentRow(i)
Dim suffix As Integer = 1
While colNameList.Contains(colName)
colName = currentRow(i) & suffix.ToString
suffix += 1
End While
colNameList.Add(colName)
Next
For Each currentField As String In colNameList
Table.Columns.Add(currentField, GetType(System.String))

Next
While Not myReader.EndOfData
Try
currentRow = myReader.ReadFields()
Table.Rows.Add(currentRow)
command = New SqlCommand("INSERT INTO Table1 VALUES (" & currentRow & "), connection")
connection.Open()
command.ExecuteNonQuery()

Catch ex As Exception
End Try
End While
connection.Close()
End Using
If Table.Columns.Contains("Column1") Then
Table.Columns.Remove("Column1")
End If
GridView1.DataSource = Table
GridView1.AllowSorting = True
GridView1.DataBind()


Any guidance in the right direction would be much appreciated