(NET) NET (2021)

SQLServerTxtDump - my typical windows console utility in VB.NET.
Example of System.Data.SqlClient, GetSchemaTable, DataTable/DataRow, Action/Func, Linq, Tuple, IEnumerable, Iterator, Yield, Byte.ToString("x2"), DBNull, Array of Object.

I have uploaded this program to Github to one of the my accounts https://github.com/Alex-1347/SqlServerTxtDump.



I'm sorry, I have no time for details description. If you can interest to VB.NET please see my real code.


   1:  Imports System.Data
   2:  Imports System.Data.SqlClient
   3:  Imports System.IO
   4:  Imports System.Text
   5:   
   6:  Partial Public Class DBDumper
   7:      Public Shared Sub DumpTableToFile(ByVal CN1 As SqlConnection, ByVal TableName As String, ByVal DestinationFile As String, Optional WhereClause As String = "1=1", Optional ColumnList As String = "*")
   8:          CN1.Open()
   9:          Using OutFile = File.CreateText(DestinationFile)
  10:              Using CMD = New SqlCommand("select " & ColumnList & "  from " & TableName & " where " & WhereClause, CN1)
  11:                  Try
  12:                      Using RDR1 As SqlDataReader = CMD.ExecuteReader()
  13:                          Dim SchemaTable As Tuple(Of String, Type, Integer)() = GetColumnsProperty(RDR1).ToArray()
  14:                          Dim NumFields As Integer = SchemaTable.Length
  15:                          Dim RowCount As Integer
  16:                          RowCount = 0
  17:                          OutFile.WriteLine(String.Join(My.Settings.FieldDivider, SchemaTable.Select(Function(X) X.Item1)))
  18:                          If RDR1.HasRows Then
  19:                              While RDR1.Read()
  20:                                  RowCount += 1
  21:                                  Dim Arr1() As Object = New Object(NumFields) {}
  22:                                  Dim ColumnsNumber As Integer = RDR1.GetValues(Arr1)
  23:                                  Dim ColumnValues As String() = Enumerable.Range(0, NumFields).
  24:                                      Select(Function(i) SqlFieldToString(Arr1(i), SchemaTable(i).Item1, Arr1(i).GetType, SchemaTable(i).Item3)).
  25:                                      Select(Function(SqlStringResult) String.Concat("""", Trim(SqlStringResult).Replace("""", """"""), """")).ToArray()
  26:                                  OutFile.WriteLine(String.Join(My.Settings.FieldDivider, ColumnValues))
  27:                                  If (RowCount Mod 1000) = 0 Then Console.Write(".")
  28:                              End While
  29:                          End If
  30:   
  31:                      End Using
  32:                  Catch ex As Exception
  33:                      Console.WriteLine(ex.Message)
  34:                      OutFile.WriteLine(ex.Message)
  35:                  End Try
  36:              End Using
  37:          End Using
  38:          CN1.Close()
  39:          Console.WriteLine()
  40:      End Sub
  41:      Private Shared Iterator Function GetColumnsProperty(ByVal RDR As IDataReader) As IEnumerable(Of Tuple(Of String, Type, Integer))
  42:          For Each Row As DataRow In RDR.GetSchemaTable().Rows
  43:              Yield New Tuple(Of String, Type, Integer)(Row("ColumnName"), Row("DataType"), Row("ColumnSize"))
  44:          Next
  45:      End Function
  46:   
  47:  End Class
  ..   
 115:      Function SqlFieldToString(Value As Object, ColumnName As String, DataType As Type, Length As Integer) As String
 116:          Select Case DataType
 117:              Case GetType(DBNull)
 118:                  Return "NULL"
 119:              Case GetType(System.Boolean)
 120:                  Return Value.ToString
 121:              Case GetType(System.Byte)
 122:                  Return Value.ToString
 123:              Case GetType(System.Byte())
 124:                  If Length > 1000 Then
 125:                      Return $"HUGE BINARY DATA {Length} bytes start with {String.Join("", Enumerable.Range(0, 10).Select(Function(I) CByte(Value(I)).ToString("x2")).ToList)}..."
 126:                  Else
 127:                      Dim Str1 = New Text.StringBuilder()
 128:                      For Each One As Byte In Value
 129:                          Str1.Append(One.ToString("x2"))
 130:                      Next
 131:                      Return Str1.ToString()
 132:                  End If
 133:   
 134:              Case GetType(System.DateTime)
 135:                  Return Value.ToString
 136:              Case GetType(System.Double)
 137:                  Return Value.ToString
 138:              Case GetType(System.Guid)
 139:                  Return Value.ToString
 140:              Case GetType(System.Int16)
 141:                  Return Value.ToString
 142:              Case GetType(System.Int32)
 143:                  Return Value.ToString
 144:              Case GetType(System.Int64)
 145:                  Return Value.ToString
 146:              Case GetType(System.String)
 147:                  Return Value.ToString
 148:              Case GetType(System.Decimal)
 149:                  Return Value.ToString
 150:              Case Else
 151:                  Console.WriteLine($"New datatype faced {DataType.Name}")
 152:                  Return Value.ToString
 153:          End Select
 154:      End Function

Also see my typical desktop application with full description:


More VB.NET example.



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/SQLServerTxtDump/Index.htm
<SITEMAP>  <MVC>  <ASP>  <NET>  <DATA>  <KIOSK>  <FLEX>  <SQL>  <NOTES>  <LINUX>  <MONO>  <FREEWARE>  <DOCS>  <ENG>  <CHAT ME>  <ABOUT ME>  < THANKS ME>