ASP.NET Export Database To CSV |
ASP.NET Export Database To CSV ตัวอย่างการ Export ข้อมูลจาก Database เขียนไปไว้ในไฟล์ CSV โดยใช้ ADO.NET ผ่าน NameSpace ชื่อ System.Data.OleDb
Language Code :  VB.NET || C#
Framework : 1,2,3,4
AspNetExportDatabaseToCsv.aspx
<%@ Import Namespace="System.Data"%>
<%@ Import Namespace="System.Data.OleDb"%>
<%@ Import Namespace="System.IO"%>
<%@ Page Language="VB" %>
<script runat="server">
Sub Page_Load(sender As Object, e As EventArgs)
Dim dt As DataTable
'*** Read DataTable ***'
dt = CreateDataTable
'*** Export to CSV ***'
ExportToCSV(dt)
End Sub
'*** Create to DataTable ***'
Function CreateDataTable() 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("database/mydatabase.mdb")&";"
objConn = New OleDbConnection(strConnString)
objConn.Open()
Dim strSQL As String
strSQL = "SELECT * FROM customer "
dtAdapter = New OleDbDataAdapter(strSQL, objConn)
dtAdapter.Fill(dt)
dtAdapter = Nothing
objConn.Close()
objConn = Nothing
Return dt '*** Return DataTable ***'
End Function
Function ExportToCSV(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
Dim strPath As String = "csv/"
Dim strFileName As String = "customer.csv"
'*** Create Text Files (Columns Default ***' ***'
Dim StrWer As StreamWriter
StrWer = File.CreateText(Server.MapPath(strPath) & strFileName)
StrWer.Write("CustomerID,Name,Email,CountryCode,Budget,Used")
StrWer.Close()
'*** Connect to CSV ***'
strConnString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source="&Server.MapPath(strPath) & _
";Extended Properties='TEXT;HDR=Yes;FMT=Delimited;Format=Delimited(,)'"
objConn = New System.Data.OleDb.OleDbConnection(strConnString)
objConn.Open()
'*** Loop Write CSV ***'
For i = 0 To dt.Rows.Count - 1
strSQL = "INSERT INTO " & strFileName & " (CustomerID,Name,Email,CountryCode,Budget,Used) " & _
"VALUES ('"&dt.Rows(i)("CustomerID")&"','"&dt.Rows(i)("Name")&"','"&dt.Rows(i)("Email")&"'" & _
" ,'"&dt.Rows(i)("CountryCode")&"','"&FormatNumber(dt.Rows(i)("Budget"),2)&"' " & _
",'"&FormatNumber(dt.Rows(i)("Used"),2)&"')"
objCmd = New System.Data.OleDb.OleDbCommand()
With objCmd
.Connection = objConn
.CommandType = CommandType.Text
.CommandText = strSQL
End With
objCmd.ExecuteNonQuery()
Next
Me.lblText.Text = "Export Successfully <a href=" & strPath & strFileName & ">Click here</a> "
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:Label id="lblText" runat="server"></asp:Label>
</form>
</body>
</html>
Screenshot
|
ช่วยกันสนับสนุนรักษาเว็บไซต์ความรู้แห่งนี้ไว้ด้วยการสนับสนุน 
Load balance : Server 03
