Outputting DataGridView As SQL Script

[Darkicon]

I'm working on a program that allows me to quickly write database entries. There are 123 columns, almost all of them I'm not using right now so I figured I'd write a program to speed up the process of adding entries to the database.

The problem I'm having is exporting every row in a DataGridView to a .sql file. I'm able to do it with a single row but I'm having trouble with doing it for all rows. Every time I get a index out of range exception. I can't figure out why. The problem is probably obvious, but I need a fresh set of eyes to figure it out.

In order for it to streamwrite each line it first collects all the data from the columns by using a loop then it writes it, clears the string and starts on the next row, looping for all the data, so on and so forth.

Private Sub StreamAll(ByVal table as String)
        Dim TotalRows As Integer = DataGridView1.Rows.GetRowCount(DataGridViewElementStates.Visible)
        Dim CurrentRow As Integer = 1
        Dim TotalCols As Integer = DataGridView1.Columns.GetColumnCount(DataGridViewElementStates.Visible)
        Dim CurrentCol As Integer = 0
        Dim DataInfo As String = "INSERT INTO " & table & " VALUES ('"
        Dim Failed As Boolean = False
        debugoutput.Text &= "Preparing to write..." & vbNewLine

        Try
            With FolderBrowserDialog1
                .RootFolder = Environment.SpecialFolder.Desktop
                .SelectedPath = "C:\"
                If .ShowDialog = DialogResult.OK Then
                    Try
                        Using sw As StreamWriter = New StreamWriter(.SelectedPath & "\" & "QuickSQL.sql")
                            Do
                                Do
                                    DataInfo &= DataGridView1.Item(CurrentCol, CurrentRow).Value & "', '"
                                    CurrentCol = CurrentCol + 1
                                Loop Until CurrentCol = TotalCols - 1
                                DataInfo &= DataGridView1.Item(CurrentCol, CurrentRow).Value & "';"
                                sw.WriteLine(DataInfo)
                                DataInfo = "INSERT INTO " & table & " VALUES ('"
                                CurrentRow = CurrentRow + 1
                            Loop Until CurrentRow = TotalRows - 1
                            debugoutput.Text &= "Writing to file..." & vbNewLine
                            sw.Close()
                        End Using
                    Catch ex As Exception
                        debugoutput.Text &= "Streamwrite failed (" & ex.Message & ")." & vbNewLine
                        Failed = True
                    End Try
                ElseIf .ShowDialog = DialogResult.Cancel Then
                    debugoutput.Text &= "Write aborted." & vbNewLine
                    Failed = True
                End If
            End With
        Catch ex As Exception
            debugoutput.Text &= "Streamwrite failed (" & ex.Message & ")." & vbNewLine
            Failed = True
        End Try
        Select Case Failed
            Case Is = False
                debugoutput.Text &= "Streamwrite successful." & vbNewLine
            Case Else
        End Select
    End Sub

Sorry for the mess of code. Couldn't figure out a more organized way.

[GeekByChoiCe]

Here is a working example:

Imports System.Text

Public Class Form1
	'Create the query
	Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
		Dim table As String = "testTable" 'whatever your table name is
		Dim columnCount As Integer = DataGridView1.Columns.Count - 1 'get the count of columns
		Dim sb As New StringBuilder
		sb.AppendFormat("INSERT INTO {0} VALUES ", table)
		For Each row As DataGridViewRow In DataGridView1.Rows
			For i As Integer = 0 To columnCount
				If i = 0 Then 'first value need to have "("
					sb.AppendFormat("('{0}',", row.Cells(i).Value)
					Continue For
				End If
				If i < columnCount Then	'all values split by "," excpet last value
					sb.AppendFormat("'{0}',", row.Cells(i).Value)
					Continue For
				End If
				'last value needs to have "),"
				sb.AppendFormat("'{0}'),", row.Cells(i).Value)
			Next
		Next

		Debug.WriteLine(sb.ToString.TrimEnd(","c)) 'print out the string without the last ","
	End Sub

	Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
		'Fill Grid
		For i As Integer = 0 To 2
			DataGridView1.Rows.Add(createRow(i))
		Next
	End Sub

	Private Function createRow(index As Integer) As String()
		Dim list As New List(Of String)
		For Each col As DataGridViewColumn In DataGridView1.Columns
			list.Add(col.Name & index)
		Next
		Return list.ToArray
	End Function
End Class

The above code prints out:
INSERT INTO testTable VALUES ('Column10','Column20','Column30','Column40','Column50','Column60','Column70','Column80','Column90','Column100'),('Column11','Column21','Column31','Column41','Column51','Column61','Column71','Column81','Column91','Column101'),('Column12','Column22','Column32','Column42','Column52','Column62','Column72','Column82','Column92','Column102')

[GeekByChoiCe]

To write the text to a file you can use:

IO.File.WriteAllText("QuickSQL.sql", sb.ToString.TrimEnd(","c))