How can I export data from view to csv file with additional column?

Question

Besides exporting required fields from a view, I need to export an additional field, password. This field is not present in the view. It is a result of encrypting studentId field. Ex, if StudentId is ‘1234567’ then its password is 'ABCDEFG'. My codes as following.

    'assume the connection works 
    cmd1.CommandText = "select studentID from vwTEST"
    cmd1.ExecuteNonQuery()
    da.Fill(dt)
    For i As Integer = 0 To dt.Rows.Count - 1
        strStudentID = dt.Rows(i).Item(0)
        enc = encrypt(strStudentID)    ‘assume this function works
        updatePW(strStudentID, enc)
Next

Everything works. But the rule is I can’t put passwords into the students table. Also, I can’t use a UDF on SQL Server. So, I try to create a virtue table that I will put all data in there, and then I will try to put the password (that is created by the encrypt function) in that virtual table before exporting data to csv file. How can I do that? Can you please help? Thanks in advance.

Answer 1

Though the original post mentioned in the comments should have been enough help, this code should also do the trick

The ExportToCSV function takes a DataTable and a FileName as parameter, returns True on Success, False on failure (and prints the error to the console)

You could do the username encrypting at the places where i have put the remark OR you add them to the datatable before exporting it

The exported csv file works with MS Excel & SpreadSheets (KingSoft office)

Imports System.IO
Imports System.Text

Module Module1
    Function wrapValue(value As String, group As String, separator As String) As String
        If value.Contains(separator) Then
            If value.Contains(group) Then
                value = value.Replace(group, group + group)
            End If
            value = group & value & group
        End If
        Return value
    End Function

    Function ExportToCSV(dtable As DataTable, fileName As String) As Boolean
        Dim result As Boolean = True
        Try
            Dim sb As New StringBuilder()
            Dim separator As String = ";"
            Dim group As String = """"
            Dim newLine As String = Environment.NewLine

            For Each column As DataColumn In dtable.Columns
                sb.Append(wrapValue(column.ColumnName, group, separator) & separator)
            Next
            ' here you could add the column for the username
            sb.Append(newLine)

            For Each row As DataRow In dtable.Rows
                For Each col As DataColumn In dtable.Columns
                    sb.Append(wrapValue(row(col).ToString(), group, separator) & separator)
                Next
                ' here you could extract the password for the username
                sb.Append(newLine)
            Next
            Using fs As New StreamWriter(fileName)
                fs.Write(sb.ToString())
            End Using
        Catch ex As Exception
            Console.WriteLine(ex.Message & vbCrLf & ex.StackTrace)
            result = False
        End Try
        Return result
    End Function

    Sub Main()
        Dim dt As New DataTable
        dt.Columns.Add("String Column", GetType(String))
        dt.Columns.Add("Integer Column", GetType(Integer))
        dt.Columns.Add("Bool Column", GetType(Boolean))
        dt.Columns.Add("Double Column", GetType(Double))

        For i As Integer = 0 To 10
            dt.Rows.Add("string "";value " & i, i, IIf(i Mod 2, True, False), Math.Sqrt(i))
        Next

        If ExportToCSV(dt, Path.Combine(Environment.CurrentDirectory, "TestFile.csv")) Then
            Console.WriteLine("CSV File succesfully written")
        End If
        Console.ReadLine()
    End Sub

End Module