Writing a text file

Question

Using VB.Net and SQL Server 2005

I want to write a text file from the database like

Open a text file, read the data from the database then write into the text file.

Code.

cmd = New SqlCommand("SELECT ID, Name, Dept from table", con)
dr = cmd.ExecuteReader

            While dr.Read()

                Dim data As String
                data = File.ReadAllText(dr.Item("PersonID"))
                File.WriteAllText("D:\test.txt", data)


            End While

The above code is showing error, How to write a text file

vb.net Code Help

Answer 1

File.ReadAllText if for reading from the file.

File.WriteAllText will write everything to the file, overwriting what is in it.

You need to get the data like this:

data = dr.Item("PersonID").ToString()

When writing, you probably want to append the text to it:

File.AppendAllText("D:\test.txt", data);

What would be slightly more efficient would be to loop through you data, building the whole set of data in memory (adding line breaks at the end of each row) and then writing it all in one go with File.WriteAllText. I am assuming the amounts of data are not massive, as this approach will be using more memory but will cause less disk IO.

Answer 2

cmd = New SqlCommand("SELECT ID, Name, Dept from table", con)
dr = cmd.ExecuteReader

Dim sb as new StringBuilder

While dr.Read()

    ''Let's read line by line and Append it to our StringBuilder
    sb.AppendLine(
           String.Format("{0} | {1} | {2} | {3}", 
                   dr.item("ID"), dr.item("Name"), dr.item("Dept") ))

End While

''Now that we have all data in our StringBuilder, lets put into our file

File.WriteAllLines("D:\test.txt", sb.ToString())

P.S. from a C# guy ... please verify if the code it right :) (C# code below)

using (SqlConnection con = new SqlConnection("my Connection String"))
{
    using (SqlCommand cmd = new SqlCommand())
    {
        cmd.Connection = con;
        cmd.CommandType = System.Data.CommandType.Text;
        cmd.CommandText = "SELECT ID, Name, Dept from table";

        con.Open();
        SqlDataReader dr = cmd.ExecuteReader();
        StringBuilder sb = new StringBuilder();

        while (dr.Read())
            // Let's read line by line and Append it to our StringBuilder
            sb.AppendLine(
                String.Format("{0} | {1} | {2} | {3}",
                    dr["ID"], dr["Name"], dr["Dept"]));

        // Now that we have all data in our StringBuilder, lets put into our file
        File.WriteAllLines("D:\test.txt", sb.ToString());
    }
}

Answer 3

In your code you are not actually reading from the database. Try this:

Using writer = New StreamWriter("d:\test.txt")
    While dr.Read()
        Dim id = dr.GetInt32(0)
        Dim name = dr.GetString(1)
        Dim dept = dr.GetString(2)

        writer.WriteLine(String.Format("{0} {1} {2}", id, name, dept))
    End While
End Using