Serialize Table to CSV with Iterator and Yeld
1: Imports System.Data
2: Imports System.Data.SqlClient
3: Imports System.IO
4:
5: Public Class DBDumper
6: Public Shared Sub DumpTableToFile(ByVal Connection As SqlConnection, ByVal TableName As String, ByVal DestinationFile As String)
7: Using CMD = New SqlCommand("select * from " & TableName, Connection)
8: Using RDR As SqlDataReader = CMD.ExecuteReader()
9: Using OutFile = File.CreateText(DestinationFile)
10: Dim ColumnNames As String() = GetColumnNames(RDR).ToArray()
11: Dim NumFields As Integer = ColumnNames.Length
12: OutFile.WriteLine(String.Join(",", ColumnNames))
13: If RDR.HasRows Then
14: While RDR.Read()
15: Dim ColumnValues As String() = Enumerable.Range(0, NumFields).
16: Select(Function(i) DumpOneColumn(i, RDR)).
17: Select(Function(field) String.Concat("""", field.Replace("""", """"""), """")).ToArray()
18: OutFile.WriteLine(String.Join(",", columnValues))
19: End While
20: End If
21: End Using
22: End Using
23: End Using
24: End Sub
25:
26: Private Shared Function DumpOneColumn(I As Integer, RDR As SqlDataReader) As String
27: If RDR.GetValue(I).GetType.FullName = "System.Byte[]" Then
28: Dim Str1 = New StringBuilder()
29: For Each One As Byte In RDR.GetValue(I)
30: Str1.Append(One.ToString("x2"))
31: Next
32: Return Str1.ToString()
33: Else
34: Return RDR.GetValue(I).ToString()
35: End If
36: End Function
37:
38: Private Shared Iterator Function GetColumnNames(ByVal RDR As IDataReader) As IEnumerable(Of String)
39: For Each Row As DataRow In RDR.GetSchemaTable().Rows
40: Yield CStr(Row("ColumnName"))
41: Next
42: End Function
43: End Class
Comments (
)
<00>
<01>
<02>
<03>
<04>
<05>
<06>
<07>
<08>
<09>
<10>
<11>
<12>
<13>
<14>
<15>
<16>
<17>
<18>
<19>
<20>
<21>
<22>
<23>
Link to this page:
//www.vb-net.com/TableDumper/Index.htm
<SITEMAP> <MVC> <ASP> <NET> <DATA> <KIOSK> <FLEX> <SQL> <NOTES> <LINUX> <MONO> <FREEWARE> <DOCS> <ENG> <CHAT ME> <ABOUT ME> < THANKS ME> |