C# StreamWriter stops before finishing?

Question

using System;
using System.Data.OleDb;
using System.Diagnostics;
using System.IO;

namespace PullData
{
    class Program
    {
        static void Main(string[] args)
        {
            OleDbConnection connLocal = new OleDbConnection("Provider=SAOLEDB.10;Data Source=demo;Persist Security Info=True;User ID=dba;PWD=sql;Location=1.2.3.4");
            OleDbCommand cmdLocal = new OleDbCommand("SELECT tran_num, provider_id, amount, tran_date, collections_go_to, impacts, type, 'TestClinic' AS Clinic FROM transactions WHERE tran_date > '2015-09-27'", connLocal);
            StreamWriter sqlWriter = new StreamWriter(@"C:\Users\Administrator\Desktop\Clinic.txt");


            try
            {
                connLocal.Open();
            }
            catch (Exception connerr) { Debug.WriteLine(connerr.Message); }

            OleDbDataReader readLocal = cmdLocal.ExecuteReader();

            while (readLocal.Read())
            {
                sqlWriter.WriteLine("{0}|{1}|{2}|{3}|{4}|{5}|{6}|{7}", readLocal.GetValue(0).ToString(), readLocal.GetValue(1).ToString(), readLocal.GetValue(2).ToString(), readLocal.GetValue(3).ToString(), readLocal.GetValue(4).ToString(), readLocal.GetValue(5).ToString(), readLocal.GetValue(6).ToString(), readLocal.GetValue(7).ToString());
            }
            readLocal.Close();
            connLocal.Close();
        }
    }
}

Hey all,

Above is the code that I'm currently testing out to write out query results into a txt file. All seems to work other than each time I attempt to run it, it seems to stop writing at random points before ending the program. The query running has 3370 rows ending with a tran_num of 227239 however the end of my txt file ends before getting there and is cutoff in the middle of a row like this:

227233|999|-5.00|11/3/2015 12:00:00 AM|999|C|A|TestClinic
227234|AK|0.00|11/3/2015 12:00:0

I've attempted to run this a few times with the same issue, I've ran the query to just the database (using the same application) and can see all the rows. Any thoughts as to why this could be happening? Thanks

Answer 1

You should use the using statement to be sure that your disposable objects are closed also in case of exceptions. Also, to ensure that everything is flushed to the disk after you have finished your writing call the StreamWriter Flush method

static void Main(string[] args)
{
    using(OleDbConnection connLocal = new OleDbConnection(...))         
    using(OleDbCommand cmdLocal = new OleDbCommand("SELECT tran_num, provider_id, amount, tran_date, collections_go_to, impacts, type, 'TestClinic' AS Clinic FROM transactions WHERE tran_date > '2015-09-27'", connLocal))
    using(StreamWriter sqlWriter = new StreamWriter(@"C:\Users\Administrator\Desktop\Clinic.txt"))

    {
        try
        {
            connLocal.Open();
            using(OleDbDataReader readLocal = cmdLocal.ExecuteReader())
            {
                 while (readLocal.Read())
                 {
                    sqlWriter.WriteLine("{0}|{1}|{2}|{3}|{4}|{5}|{6}|{7}", 
                    readLocal.GetValue(0).ToString(), 
                    readLocal.GetValue(1).ToString(),
                    readLocal.GetValue(2).ToString(), 
                    readLocal.GetValue(3).ToString(), 
                    readLocal.GetValue(4).ToString(), 
                    readLocal.GetValue(5).ToString(), 
                    readLocal.GetValue(6).ToString(), 
                    readLocal.GetValue(7).ToString());
                }
            }
            sqlWriter.Flush();
       }
       catch (Exception connerr) { Debug.WriteLine(connerr.Message); }
   }
}

Notice also that I have enclosed all the code inside a try/catch, not just the opening of the connection and removed the call to Close because it will be called automatically when you exit from the using blocks.