Insert into Sql DB

[billboy]

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

[jmcilhinney]

For future reference, please post all code snippets inside formatting tags. After more then 1200 posts, we really shouldn't have to ask for that courtesy.

As for the question, you will want to populate the DataTable fully and then use a data adapter to save the whole lot to the database in one go. For an example of that, follow the CodeBank link in my signature and check out my thread on Retrieving & Saving Data.

[billboy]

Sorry about the code tags, As someone with 1200 posts I do know about placing code in tags, it was an obvious late night mis-step




This code form your code bank seems to be working fine. Not sure if its implemented correctly.

Is there a way to parse the text file and create the sql table on the fly or dynamically?

Thanks

[billboy]

This is what I have got now

 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"
        
        Using myReader As New Microsoft.VisualBasic.FileIO.TextFieldParser(filepath)
           
            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)
                Catch ex As Exception
                End Try
            End While

        End Using
        If Table.Columns.Contains("Column1") Then
            Table.Columns.Remove("Column1")
        End If

        Using connection As New SqlConnection(conn), bulkCopy As New SqlBulkCopy(connection)
            bulkCopy.DestinationTableName = "Table1"

            'Save the changes.
            connection.Open()
            bulkCopy.WriteToServer(Table)

            GridView1.DataSource = Table
            GridView1.AllowSorting = True
            GridView1.DataBind()

        End Using

[jmcilhinney]

You should be using |DataDirectory| in your connection string. In an ASP.NET app, it resolves to the App_Data folder at run time.

Using Bulk Copy is of no real benefit unless you have very large quantities of data and you had better be sure that it's clean because there's no rolling back.