ASP.NET Insert CSV 檔案資料 至 Microsoft SQL

參考網址
http://vectus.wordpress.com/2011/02/13/asp-net-insert-csv-%E6%AA%94%E6%A1%88%E8%B3%87%E6%96%99-%E8%87%B3-microsoft-sql/



Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim reader As System.IO.StreamReader = New System.IO.StreamReader(Me.FileUpload1.PostedFile.InputStream)
Dim csv As String = reader.ReadToEnd()
InsertCSVtoDataTable(csv)

End Sub

Public Sub InsertCSVtoDataTable(ByVal csvContent As String)

Dim sr As New System.IO.StringReader(csvContent)
Dim line As String = Nothing
Dim i As Integer = 0
Dim p As String()

line = sr.ReadLine()
p = line.Split(",")
i = 0

If i = 0 Then
For Each s As String In p
i += 1
Next
End If

i = 1
line = sr.ReadLine()

While line IsNot Nothing
p = line.Split(",")
Insert_Record(p, i)
line = sr.ReadLine()
i += 1
End While

End Sub

Private Sub Insert_Record(ByVal AAA As String(), ByVal BBB As Integer)
Dim connectionString1 As String = "Data Source=.\SqlExpress; Initial Catalog=aa; Integrated Security=SSPI"
Dim con1 As SqlConnection = New SqlConnection(connectionString1)
con1.Open()

Dim SqlString1 As String = "INSERT INTO TABLE2 (a, b, c, d, e) VALUES (" & _
BBB.ToString & ", '" & AAA(0).ToString.Trim & "', '" & _
AAA(1).ToString.Trim & "', '" & AAA(2).ToString.Trim & "', '" & _
AAA(3).ToString.Trim & "')"
Dim cmd1 As SqlCommand = New SqlCommand(SqlString1, con1)
cmd1.ExecuteNonQuery()
con1.Close()
End Sub