Creating CSV File From SqlReader .. [assumes MS-SQL] .. faster way?

[Chris Morse]

Hi,

Following is a function I wrote to export the rows of an arbitrary
table. It's not complete yet, in that it currently does not output to
a file (it uses Debug.WriteLine () at the moment) and it currently
assumes MS-SQL Server database types (int, bit, varchar, etc) and only
handles a few of the datatypes.

It works, but it's rather slow. The problem being that every field of
every row must be checked for the datatype, so that the CSV file can
be properly formatted. Eg, text fields are single-quoted and any
single quotes in the text fields must be replaced with two single
quotes (eg, the Replace("'","'' ") call).

Does anyone know of a faster and more efficient way of accomplishing
this?

Thanks,
// CHRIS

CODE:

<NOTE>
Output is with Debug.WriteLine (), no file created yet;
also, (String) m_SQL_Connectio n must be defined for the
sqlCmd.Connecti on property setting.
</NOTE>
Public Function ExportQuery(ByV al szSQL As String, ByVal
szFilename As String) As Boolean

'Execute SQL And Save Results To CSV File
Dim sqlCmd As SqlCommand
Dim sqlReader As SqlDataReader
Dim n As Integer
Dim k As Integer
Dim nRow As Integer = 0
Dim nCols As Integer
Dim DC As DataColumn = Nothing
Dim obDateTime As DateTime

Try
sqlCmd = New SqlCommand(szSQ L)
sqlCmd.Connecti on = m_SQL_Connectio n
sqlReader = sqlCmd.ExecuteR eader()
If sqlReader.HasRo ws() = True Then
Do While sqlReader.Read( ) = True
If nRow = 0 Then
nCols = sqlReader.Field Count()
For n = 0 To nCols - 1
If n > 0 Then Debug.Write("," )
Debug.Write(Str ing.Format("""{ 0}""",
sqlReader.GetNa me(n)))

Next
Debug.WriteLine ("")
End If
nRow = nRow + 1
For n = 0 To nCols - 1
If n > 0 Then Debug.Write("," )
Select Case sqlReader.GetDa taTypeName(n)
Case "int"
Debug.Write(Str ing.Format("{0} ",
sqlReader(n)))
Case "varchar"
Debug.Write(Str ing.Format("'{0 }'",
CType(sqlReader (n), String).Replace ("'", "''")))
Case "bit"
If CType(sqlReader (n), Boolean) = True
Then
Debug.Write("1" )
Else
Debug.Write("0" )
End If
Case "datetime"
obDateTime = CType(sqlReader (n),
DateTime)

Debug.Write(Str ing.Format("'{0 :D4}-{1:D2}-{2:D2}
{3:D2}:{4:D2}:{ 5:D2}.{6:D3}'", obDateTime.Year (), obDateTime.Mont h(),
obDateTime.Day( ), obDateTime.Hour (), obDateTime.Minu te(),
obDateTime.Seco nd(), obDateTime.Mill isecond()))
Case Else
MsgBox(String.F ormat("ExportQu ery:
Column Type '{0}' Not Currently Handled",
sqlReader.GetDa taTypeName(n)))
End Select

Next
Debug.WriteLine ("")
Loop
End If

sqlReader.Close ()
sqlCmd.Dispose( )

Catch ex As Exception
Me.m_szLastErro r = ex.Message()
Debug.WriteLine (ex.Message())
Return False
End Try

Return True
End Function