Transferring data from DB to Text file through VB.Net

[ninelgorb]

I have a table in SQL Server that gets populated with data on a daily basis. Is there any way through vb.net I can populate a text file with that data other than executing a dts package?

 

[chrissie1]

sure, straight from msdn

The following code example shows a simple way to write text to a text file.
[Visual Basic]
Imports System
Imports System.IO

Class Test
    Public Shared Sub Main()
        ' Create an instance of StreamWriter to write text to a file.
        Dim sw As StreamWriter = New StreamWriter("TestFile.txt")
        ' Add some text to the file.
        sw.Write("This is the ")
        sw.WriteLine("header for the file.")
        sw.WriteLine("-------------------")
        ' Arbitrary objects can also be written to the file.
        sw.Write("The date is: ")
        sw.WriteLine(DateTime.Now)
        sw.Close()
    End Sub
End Class
[C#]
using System;
using System.IO;

class Test 
{
    public static void Main() 
    {
        // Create an instance of StreamWriter to write text to a file.
        // The using statement also closes the StreamWriter.
        using (StreamWriter sw = new StreamWriter("TestFile.txt")) 
        {
            // Add some text to the file.
            sw.Write("This is the ");
            sw.WriteLine("header for the file.");
            sw.WriteLine("-------------------");
            // Arbitrary objects can also be written to the file.
            sw.Write("The date is: ");
            sw.WriteLine(DateTime.Now);
        }
    }
}
The following code example creates a new text file and writes a string to it.
[Visual Basic]
Option Explicit On 
Option Strict On
Imports System
Imports System.IO
Public Class TextToFile
    Private Const FILE_NAME As String = "MyFile.txt"
    Public Shared Sub Main()
        If File.Exists(FILE_NAME) Then
            Console.WriteLine("{0} already exists.", FILE_NAME)
            Return
        End If
        Dim sr As StreamWriter = File.CreateText(FILE_NAME)
        sr.WriteLine("This is my file.")
        sr.WriteLine("I can write ints {0} or floats {1}, and so on.", 1, 4.2)
        sr.Close()
    End Sub
End Class

Christiaan Baes
Belgium

I just like this --> [Wiggle] [Wiggle]

 

[ninelgorb]

But I need the data to come from a sql server table.
How do I do that?

 

[chrissie1]

you will have to put the data in a dataset or read it via a datareader and then iterate through it and write it to the textfile. Writing it to XML is even easier since the datatable has a xmlwrite and xmlread method.

Christiaan Baes
Belgium

I just like this --> [Wiggle] [Wiggle]

 

[hneal98]

You can also create a stored procedure in SQL server and execute that from VB.NET.

 

[ninelgorb]

I got as far as getting data into my dataset. Do you happen to know how I then transfer from the dataset to the text file?

Something like this?

'I already executed a stored proc here to get data

Dim da As New SqlDataAdapter
Dim ds As DataSet = New DataSet
da.SelectCommand = objCommand
da.Fill(ds)

Dim FILE_NAME As String = ("C:\textFile_20050819.txt")
Dim sr As StreamWriter = File.CreateText(FILE_NAME)
sr.WriteLine(da)

This isn't working for me. I can't seem to figure out how to loop through the dataset.

Thanks,
Ninel

 

[chrissie1]

I suppose you only have one table with result and I only will use the first and second field to put in the text file.

dim strtemp as string
for inttemp as integer = 0 to ds.tables(0).rows.count-1
  strtemp = ds.tables(0).rows(inttemp).item(0) & ";" & ds.tables(0).rows(inttemp).item(1)
 sr.WriteLine(strtemp)
 sr.close
next

Christiaan Baes
Belgium

I just like this --> [Wiggle] [Wiggle]

 

[ninelgorb]

Thank you so much. I actually figured it out before you responded and I did it the exact saem way.

The only difference was that I closed the sr object outside the loop. What is the difference?

 

[chrissie1]

that your right and that I made a misstake. I'm not perfect (99.9%)

Christiaan Baes
Belgium

I just like this --> [Wiggle] [Wiggle]

 

[ninelgorb]

But you are close to it...that's what counts!!!