Private Shared Function SaveAsCSV(ByVal selectCommand As SqlClient.SqlCommand, ByVal FileCSV As
System.IO.StreamWriter, ByVal ColumnsExcluded() As String) As Integer
Dim Reader As SqlClient.SqlDataReader
Dim dtSchema As DataTable
Dim rowSchema As DataRow
Dim i, j, IndexColumn As Short
Dim ArrayIndex As New ArrayList
Dim ArrayNames As New ArrayList
Dim FieldIndex() As Short
Dim nRecords As Integer
Dim ColumnName As String
Dim StrLine, StrValue, StrFormat As String
Dim utf8 As New System.Text.UTF8Encoding
Const FieldSeparator As String = ","
System.Threading.Thread.CurrentThread.CurrentCulture = CSVCulture()
For i = 0 To ColumnsExcluded.Length - 1
ColumnsExcluded(i) = Trim(ColumnsExcluded(i)).ToLower()
Next
' execute select command
Reader = selectCommand.ExecuteReader()
dtSchema = Reader.GetSchemaTable()
' included field indexes
For i = 0 To dtSchema.Rows.Count - 1
rowSchema = dtSchema.Rows(i)
ColumnName = rowSchema("ColumnName")
If [Array].IndexOf(ColumnsExcluded, ColumnName.ToLower()) < 0 Then
ArrayIndex.Add(i)
End If
Next
FieldIndex = ArrayIndex.ToArray(GetType(Short))
' ... Formato de exportacion por campo
Dim StrColumns() As String = [Array].CreateInstance(GetType(String), dtSchema.Rows.Count)
Dim CSVFormats() As String = [Array].CreateInstance(GetType(String), dtSchema.Rows.Count)
For j = 0 To FieldIndex.Length - 1
IndexColumn = FieldIndex(j)
rowSchema = dtSchema.Rows(IndexColumn)
ArrayNames.Add(rowSchema("ColumnName"))
Select Case System.Type.GetTypeCode(rowSchema("DataType"))
Case TypeCode.String, TypeCode.Char
StrFormat = """{0}"""
Case TypeCode.DateTime
StrFormat = "{0:d}"
Case TypeCode.Int16, TypeCode.Int32, TypeCode.Int64, TypeCode.Byte, TypeCode.UInt16, TypeCode.UInt32, TypeCode.UInt64
StrFormat = "{0}"
Case TypeCode.Decimal, TypeCode.Double, TypeCode.Single
StrFormat = "{0}"
Case TypeCode.Boolean
StrFormat = "{0}"
Case Else
StrFormat = "{0}"
End Select
CSVFormats(IndexColumn) = StrFormat
Next
' exportar nombres de columnas
FileCSV.WriteLine([String].Join(",", ArrayNames.ToArray(GetType(String))))
' exportar datos
While Reader.Read()
StrLine = ""
For j = 0 To FieldIndex.Length - 1
IndexColumn = FieldIndex(j)
If Not Reader.IsDBNull(IndexColumn) Then
StrValue = String.Format(CSVFormats(IndexColumn), Reader.GetValue(IndexColumn))
StrLine = StrLine & StrValue
End If
StrLine = StrLine & FieldSeparator
Next
FileCSV.WriteLine(StrLine)
nRecords += 1
End While
Reader.Close()
' exporttación finalizada
Return nRecords
End Function
'''
''' Retorna información cultural específica para exportación en formato CSV
'''
''' -----------------------------------------------------------------------------
Private Shared Function CSVCulture() As System.Globalization.CultureInfo
Dim CultureInfo As New System.Globalization.CultureInfo("es-CO")
CultureInfo.NumberFormat.CurrencySymbol = "$"
CultureInfo.NumberFormat.CurrencyDecimalSeparator = "."
CultureInfo.NumberFormat.CurrencyGroupSeparator = ","
CultureInfo.NumberFormat.CurrencyDecimalDigits = 2
CultureInfo.NumberFormat.NumberDecimalSeparator = "."
CultureInfo.NumberFormat.NumberGroupSeparator = ","
CultureInfo.NumberFormat.NumberDecimalDigits = 2
CultureInfo.NumberFormat.PercentDecimalSeparator = "."
CultureInfo.NumberFormat.PercentGroupSeparator = ","
Return CultureInfo
End Function
|