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

 

   Default.aspx (必要部分)
 
   <form id="form1" runat="server">
   <div>
      <asp:FileUpload ID="FileUpload1" runat="server" />
      <br /><br />
      <asp:Button ID="Button1" runat="server" Text="Insert" />
      <br /><br />
   </div>
   </form>
 

 

   Default.aspx.vb (必要部分)
 
 
   Imports System.Data
   Imports System.IO
   Imports System.Web
   Imports System.Configuration
   Imports System.Web.UI
   Imports System.Web.Security
   Imports System.Web.UI.HtmlControls
   Imports System.Web.UI.WebControls
   Imports System.Web.UI.WebControls.WebParts
   Imports System.Data.SqlClient
 
   Partial Class _Default
      Inherits System.Web.UI.Page
 
      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
 
   End Class