Streaming data to .csv not showing results C#

Question

I am attempting to stream data from a SQLServer DB to a .CSV file; However, after execution, the .csv is still blank. The first while loop is/was for testing purposes which executed correctly. I know I am doing something wrong in my Streaming portion.

Any help is greatly appreciated!

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ConsoleApp1
{
    class Program
    {
        static void Main(string[] args)
        {
            SqlConnection conn = new SqlConnection("Server=*******;Database=*********;Integrated Security=true");
            conn.Open();
            SqlCommand cmd = new SqlCommand("SELECT p.cFName, p.cLName, s.cSugTitle, s.cEcrno, g.cName, h.mNotes, u.UserID, u.NetworkID FROM people p INNER JOIN suggest s ON p.cidPeople = s.cidPeople_WhoEntered INNER JOIN Grp g on s.cidGrp = g.cidGrp INNER JOIN history h ON h.cidSuggest = s.cidSuggest INNER JOIN users u ON u.cidPeople = p.cidPeople", conn);
            SqlDataReader reader = cmd.ExecuteReader();

        while (reader.Read())
        {
            Console.WriteLine("{0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}", reader.GetString(0), reader.GetString(1), reader.GetString(2), reader.GetString(3), reader.GetString(4), reader.IsDBNull(5) ? null : reader.GetString(5), reader.GetString(6), reader.IsDBNull(7) ? null : reader.GetString(7));
        }


        String path = @"C:\Users\AEEVANS\Desktop\example.csv";

        using (StreamWriter sr = File.AppendText(path))
        {
            while (reader.Read())
            {
                sr.WriteLine("{0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}", reader.GetString(0), reader.GetString(1), reader.GetString(2), reader.GetString(3), reader.GetString(4), reader.IsDBNull(5) ? null : reader.GetString(5), reader.GetString(6), reader.IsDBNull(7) ? null : reader.GetString(7));
                sr.Close();
            }  
        }
        Console.ReadKey();

        reader.Close();
        conn.Close();

        if (Debugger.IsAttached)
        {
            Console.ReadLine();
        }
    }
  }
}

EDIT I have edited my original code to not include the first while loop per recommendation. I am now running into the following error:

System.ObjectDisposedException: 'Cannot write to a closed TextWriter.'

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Diagnostics;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ConsoleApp1
{
    class Program
    {
        static void Main(string[] args)
        {
            SqlConnection conn = new SqlConnection("Server=********;Database=*********;Integrated Security=true");
            conn.Open();
            SqlCommand cmd = new SqlCommand("SELECT p.cFName, p.cLName, s.cSugTitle, s.cEcrno, g.cName, h.mNotes, u.UserID, u.NetworkID FROM people p INNER JOIN suggest s ON p.cidPeople = s.cidPeople_WhoEntered INNER JOIN Grp g on s.cidGrp = g.cidGrp INNER JOIN history h ON h.cidSuggest = s.cidSuggest INNER JOIN users u ON u.cidPeople = p.cidPeople", conn);
            SqlDataReader reader = cmd.ExecuteReader();

            String path = @"C:\Users\AEEVANS\Desktop\example.csv";

            using (StreamWriter sr = File.AppendText(path))
            {
                while (reader.Read())
                {
                    sr.WriteLine("{0}, {1}, {2}, {3}, {4}, {5}, {6}, {7}", reader.GetString(0), reader.GetString(1), reader.GetString(2), reader.GetString(3), reader.GetString(4), reader.IsDBNull(5) ? null : reader.GetString(5), reader.GetString(6), reader.IsDBNull(7) ? null : reader.GetString(7));
                    sr.Close();
                }
            }

            reader.Close();
            conn.Close();

            if (Debugger.IsAttached)
            {
                Console.ReadLine();
            }

        }
        }
    }

After your first loop (reader.Read(), the reader is at EOF. That's why your second loop doesn't do anything. Get rid of the first loop. You also should wrap the connection, command and reader in using blocks. — Jon Vote, Dec 13 '18 at 19:14
Also closing the Streamwriter inside the loop will let you with just one record in your file. If you have more records you will never see them while an exception will be raised at the second loop when you try to write to a closed stream — Steve, Dec 13 '18 at 19:17

score 3 · Answer 1 · answered Dec 13 '18 at 19:16

3

while (reader.Read()) is a one-way trip through a data reader. If you do another while (reader.Read()), you'll get nothing because the pointer is already at the end of the reader's contents.

Looks like you should be able to combine your two while (reader.Read()) blocks into one.

answered Dec 13 '18 at 19:16

Tab Alleman

31,483
7
36
52

I removed my first while loop per recommendation. I am now receiving the following error: System.ObjectDisposedException: 'Cannot write to a closed TextWriter.' – Andrew Dec 13 '18 at 19:43
After my sr.WriteLine (); – Andrew Dec 13 '18 at 19:44
That's because of what Steve said in his comment. You need to move `sr.Close` AFTER the `while (reader.Read())` block. – Tab Alleman Dec 13 '18 at 19:45
Do not know how I missed that. Thanks! – Andrew Dec 13 '18 at 19:58

Streaming data to .csv not showing results C#

1 Answers1