ASP.NET(vb.net) & Export Database To CSV

Author: admin | Posted: 19-06-2010 | Category: ASP.NET(vb.net) & CSV

ASP.NET(vb.net) & Export Database To CSV - In this article we will example scripts ASP.NET how to imports CSV file into Database.

ShotDev Focus:
- ASP.NET(vb.net) & Export Database To CSV

Example

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>ShotDev.Com Tutorial</title>
</head>
<body>
<form id="form1" runat="server">
<asp:Label id="lblText" runat="server"></asp:Label>
</form>
</body>
</html>

Screenshot

.
.
.
Download this script.