(NET) NET (2012)

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
Link to this page: http://www.vb-net.com/TableDumper/Index.htm
<SITEMAP>  <MVC>  <ASP>  <NET>  <DATA>  <KIOSK>  <FLEX>  <SQL>  <NOTES>  <LINUX>  <MONO>  <FREEWARE>  <DOCS>  <ENG>  <MAIL ME>  <ABOUT ME>  < THANKS ME>