well.. ada dua alternatif yang bisa saya sarankan
1. convert datatable ke csv
functionnya adalah sebagai berikut:
Sub DT2CSV(ByVal dtable As DataTable, ByVal path_filename As String, _
ByVal sep_char As String)
Dim writer As System.IO.StreamWriter = New System.IO.StreamWriter(path_filename)
Try
' writer = New System.IO.StreamWriter(path_filename)
Dim _sep As String = ""
Dim builder As New System.Text.StringBuilder
For Each col As DataColumn In dtable.Columns
builder.Append(_sep).Append(col.ColumnName)
_sep = sep_char
Next
writer.WriteLine(builder.ToString())
For Each row As DataRow In dtable.Rows
_sep = ""
builder = New System.Text.StringBuilder
For Each col As DataColumn In dtable.Columns
builder.Append(_sep).Append(row(col.ColumnName))
_sep = sep_char
Next
writer.WriteLine(builder.ToString)
Next
Catch ex As Exception
Finally
If Not writer Is Nothing Then writer.Close()
End Try
End Sub
cara menggunakannya mudah, Misal anda memiliki query sebagai berikut:
Dim ConnStr As String = "Data Source=.;Initial Catalog=DB;Persist Security Info=True; " & _
"User ID=sa;Password=XXX;Connect Timeout=45"
Dim fn As String = "C:\TEST.csv"
Using cn As New SqlClient.SqlConnection
cn.ConnectionString = ConnStr
cn.Open()
Using cmd As New SqlClient.SqlCommand
Dim da As New SqlClient.SqlDataAdapter
Dim dt As New DataTable
cmd.Connection = cn
SQL = "SELECT Outlet.ID AS OutletID, Master.ID AS ProductID, Master.QtyOnHand, " & _
"CONVERT(VARCHAR(19), GETDATE(), 120) AS LastUpdate FROM Master CROSS JOIN Outlet"
cmd.CommandText = SQL
da.SelectCommand = cmd
da.Fill(dt) ' datatable di isi
'cara pakainya: DT2CSV([nama data tablenya],[File Name],[Separatornya])"
DT2CSV(dt, fn, ",")
End Using
End Using
setelah subrutin diatas dijalankan, anda akan mendapatkan sebuah file c:\TEST.csv yang berisi hasil query yg anda jalankan
sumber:
http://ishwara.us/data-table-to-csv-file-menggunakan-vb-net/
2. convert datatable ke xml
source nya nanti saya paste ke sini :), ada di laptop.
baik csv ataupun xml, bisa di baca di excel
