ASP.NET Upload & Import CSV to Database |
ASP.NET Upload & Import CSV to Database เป็นการอัพโหลดไฟล์ CSV และทำการ Insert ข้อมูลลงใน Database
Language Code :  VB.NET || C#
Framework : 1,2,3,4
AspNetUploadCsvToDatabase.aspx
<%@ Import Namespace="System.Data"%>
<%@ Import Namespace="System.Data.OleDb"%>
<%@ Page Language="VB" %>
<script runat="server">
Sub btnUpload_OnClick(sender As Object, e As EventArgs)
Dim strPath As String = "csv/"
Dim dt As DataTable
If Me.fiUpload.HasFile Then
Me.fiUpload.SaveAs(Server.MapPath(strPath & fiUpload.FileName))
'*** Read CSV to DataTable ***'
dt = CsvCreateDataTable(strPath,fiUpload.FileName)
'*** Insert to Database ***'
InsertToDatabase(dt)
End IF
End Sub
'*** Convert CSV to DataTable ***'
Function CsvCreateDataTable(ByVal strPath,ByVal strFilesName) As DataTable
Dim objConn As New OleDbConnection
Dim dtAdapter As OleDbDataAdapter
Dim dt As New DataTable
Dim strConnString As String
strConnString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source="&Server.MapPath(strPath) & _
";Extended Properties='TEXT;HDR=Yes;FMT=Delimited;Format=Delimited(,)'"
objConn = New OleDbConnection(strConnString)
objConn.Open()
Dim strSQL As String
strSQL = "SELECT * FROM " & strFilesName
dtAdapter = New OleDbDataAdapter(strSQL, objConn)
dtAdapter.Fill(dt)
dtAdapter = Nothing
objConn.Close()
objConn = Nothing
Return dt '*** Return DataTable ***'
End Function
Function InsertToDatabase(ByVal dt)
Dim objConn As System.Data.OleDb.OleDbConnection
Dim objCmd As System.Data.OleDb.OleDbCommand
Dim strConnString,strSQL As String
Dim i As Integer
strConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="&Server.MapPath("database/mydatabase.mdb")&";"
objConn = New System.Data.OleDb.OleDbConnection(strConnString)
objConn.Open()
'*** Loop Insert ***'
For i = 0 To dt.Rows.Count - 1
Try
strSQL = "INSERT INTO customer2 (CustomerID,Name,Email,CountryCode,Budget,Used) " & _
"VALUES ('"&dt.Rows(i)("CustomerID")&"','"&dt.Rows(i)("Name")&"','"&dt.Rows(i)("Email")&"'" & _
" ,'"&dt.Rows(i)("CountryCode")&"','"&dt.Rows(i)("Budget")&"','"&dt.Rows(i)("Used")&"')"
objCmd = New System.Data.OleDb.OleDbCommand()
With objCmd
.Connection = objConn
.CommandType = CommandType.Text
.CommandText = strSQL
End With
objCmd.ExecuteNonQuery()
Me.lblText.Text = Me.lblText.Text & "["&dt.Rows(i)("CustomerID")&"] Inserted <br>"
Catch err As Exception
Me.lblText.Text = Me.lblText.Text & "["&dt.Rows(i)("CustomerID")&"] Not Insert <br>"
End Try
Next
objCmd = Nothing
objConn.Close()
objConn = Nothing
End Function
</script>
<html>
<head>
<title>ThaiCreate.Com ASP.NET - CSV</title>
</head>
<body>
<form id="form1" runat="server">
<asp:FileUpload id="fiUpload" runat="server"></asp:FileUpload>
<input id="btnUpload" type="button" OnServerClick="btnUpload_OnClick" value="Upload" runat="server" />
<hr />
<asp:Label id="lblText" runat="server"></asp:Label>
</form>
</body>
</html>
Screenshot
|
ช่วยกันสนับสนุนรักษาเว็บไซต์ความรู้แห่งนี้ไว้ด้วยการสนับสนุน 
