CODEWITHSUNDEEP
vb.netsqldatareader
Bryan Esteban
Bryan Esteban

Reputation: 9

VB.Net SQL data to text file

I am trying to write the data that is retrieved from the SQL query into a text file in Visual Basic 2012. Here is some code - I just need a push in the right direction:

Imports System.Data.SqlClient
Imports System.IO

Public Class frmResults
    Dim cn As New SqlConnection("Data Source=(LocalDB)\v11.0;AttachDbFilename=C:\Users\bryan\Documents\TeamGFinalProject\BookCollection.MDF;Integrated Security=True;Connect Timeout=30")
    Dim cmd As New SqlCommand
    Dim dr As SqlDataReader
    Dim table As New DataTable("sales")

Private Sub frmResults_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    cmd.Connection = cn
End Sub

Private Sub BtnReport_Click(sender As Object, e As EventArgs) Handles BtnReport.Click
    cn.Open()
    cmd.CommandText = "select * from sales  where ord_date BETWEEN '" & dtpStart.Text & "' AND '" & dtpEnd.Text & "' Order BY ord_date;"
   
    Using sw As StreamWriter = New StreamWriter("Dates.txt")
        Dim reader As SqlDataReader = cmd.ExecuteReader()

        sw.WriteLine(reader)
        reader.Close()
    End Using

    cn.Close()
End Sub

End Class

Upvotes: 0

Views: 5484

Answers (3)

guest
guest

Reputation: 1

You need to loop over your result set and write to the file.

Here is a quick example.

How to stream data from sqldatareader to a text file in VB.Net

Upvotes: 0

vbnet3d
vbnet3d

Reputation: 1151

You are very close.

The SqlDataReader object is somewhat similar to a StreamReader or other reader objects. What you need to do is actually iterate through your data with the reader object. As you read, the fields are populated into the reader object. You can then write that data out. Here is a simple working example:

While (reader.Read)
   'Get data row by row
   Dim row_data(reader.FieldCount - 1) As String
   Dim i As Integer
   For i = 0 To reader.FieldCount - 1
       row_data(i) = reader(i).ToString
   Next

   'Build a single string (simplistic CSV style)
   String.Join(",",row_data)

   'Write to file
   sw.WriteLine(reader)
End While

This may not be the most efficient way of handling this, but it should give you a good idea of what is going on.

Upvotes: 0

Saragis
Saragis

Reputation: 1792

When reading from a SqlDataReader, you need to read a row of the result (SqlDataReader.Read), do something with it, and then move on to the next record until you've got what you need. You can use the SqlDataReader.GetValues method to get all column-values from the row at once.

Suggestion: work with parameters to build your commandtext (see here).

Private Sub BtnReport_Click(sender As Object, e As EventArgs) Handles BtnReport.Click       
    cn.Open()
    cmd.CommandText = "select * from sales  where ord_date BETWEEN '" & dtpStart.Text & "' AND '" & dtpEnd.Text & "' Order BY ord_date;"

    Using sw As StreamWriter = New StreamWriter("Dates.txt")
        Using reader As SqlDataReader = cmd.ExecuteReader()
            Dim values(reader.FieldCount - 1) As Object

            While reader.Read
                reader.GetValues(values)

                sw.WriteLine(String.Join(vbTab, values))
            End While
        End Using
    End Using

    cn.Close()

Upvotes: 1

Related Questions