Exporting PDF From Database back to PDF Format

Answer to Exporting PDF's from SQL Server DB and writing a Map to a Text File

this was a Question that I asked in StackOverflow and then answered it when I was finished with the project.

I would like it reviewed so the next time that I get to write something like this I will have better executing code.

here is the post

Answer From Link

I created Variables for the information that I was extracting that I wanted inside the Text File.

• Filename

• Date (From SQL Table for the Original Creation Date)

• Case Number (internal Identifier for the 3rd party program to link to)

• Description (Taken from the SQL Table to describe the document)

Then I put the application to work Writing the Code to PDF one at a time

```
using (SqlConnection Conn = new SqlConnection(strSQLConn))
{
//open the connection
Conn.Open();
Console.WriteLine("the connection is open");

//Variables needed for looping
DateTime Today = System.DateTime.Now;
DateTime StartDate = Convert.ToDateTime("2008-06-11 00:00:00");
//DateTime StartDate = Today.AddDays(-10);
Console.WriteLine("Converting the Documents from " + StartDate.ToString() + " - TO - " + Today.ToString());
Console.WriteLine("Press Any Key to continue.");
Console.ReadLine();
int RecordCount = 0;
ulong ByteCount = 0;
int i = 1;
foreach (DateTime day in EachDay(StartDate, Today))
{
String strDay = day.ToString();
// Create a SQLCommand to retrieve Data
SqlCommand getRecords = new SqlCommand("spRecapturePDF", Conn);
getRecords.CommandType = CommandType.StoredProcedure;
getRecords.Parameters.Add(new SqlParameter("@OneDay", strDay));
SqlDataReader reader = getRecords.ExecuteReader();
//stuff exporting the binary code to the PDF format
FileStream fs;
BinaryWriter bw;
int buffersize = 100;

One of the biggest issues I see in the code is the large number of IDisposable objects created not in using statements. I've put in some minor changes (idiomatic variable naming, etc.) below, but the important one is to make sure those resources get deterministically disposed.

using (var conn = new SqlConnection(strSQLConn))
    {
        // open the connection
        conn.Open();
        Console.WriteLine("the connection is open");

        // Variables needed for looping
        var today = DateTime.Now;
        var startDate = Convert.ToDateTime("2008-06-11 00:00:00");
        ////var startDate = Today.AddDays(-10);

        Console.WriteLine("Converting the Documents from " + startDate + " - TO - " + today);
        Console.WriteLine("Press Any Key to continue.");
        Console.ReadLine();

        var recordCount = 0;
        ulong byteCount = 0;
        var i = 1;

        foreach (var day in EachDay(startDate, today))
        {
            // Create a SQLCommand to retrieve Data
            using (var getRecords = new SqlCommand("spRecapturePDF", conn) { CommandType = CommandType.StoredProcedure })
            {
                getRecords.Parameters.Add(new SqlParameter("@OneDay", day.ToString()));
                using (var reader = getRecords.ExecuteReader())
                {
                    // stuff exporting the binary code to the PDF format
                    const int BufferSize = 100;
                    var buffer = new byte[BufferSize];
                    var j = 1;

                    while (reader.Read())
                    {
                        strFileName = reader.GetString(0) + "-" + i + "-" + j;
                        strDock_no = reader.GetString(0);
                        dtFiledate = reader.GetDateTime(2);
                        strDescription = reader.GetString(4);
                        using (var fs = new FileStream("c:\\FolderName\\" + strFileName + ".pdf", FileMode.OpenOrCreate, FileAccess.Write))
                        using (var bw = new BinaryWriter(fs))
                        {
                            long startIndex = 0;
                            var bytesRead = reader.GetBytes(1, startIndex, buffer, 0, BufferSize);

                            while (bytesRead == BufferSize)
                            {
                                bw.Write(buffer);
                                bw.Flush();
                                startIndex += BufferSize;
                                bytesRead = reader.GetBytes(1, startIndex, buffer, 0, BufferSize);
                            }

                            // write the remaining buffer.
                            bw.Write(buffer, 0, (int)bytesRead);
                            byteCount += Convert.ToUInt64(fs.Length);
                        }

                        //need to write to the Text file here.
                        using (var tw = new StreamWriter(path, true))
                        {
                            tw.WriteLine(strDock_no + "~" + dtFiledate.ToString() + "~" + "c:\\FolderName\\"
                                + strFileName + ".pdf" + "~" + strDescription);
                        }

                        // increment the J variable for the Next FileName
                        j++;
                        recordCount++;
                    }
                }
            }

            i++;
        }

        Console.WriteLine("Number of Records Processed:  " + recordCount.ToString());
        Console.WriteLine("for a Total of : " + byteCount + " Bytes");

        var megabyteCount = Convert.ToDecimal(byteCount) / 1024 / 1024;
        var gigabyteCount = megabyteCount / 1024;

        Console.WriteLine("Total MBs : " + megabyteCount + " MB");
        Console.WriteLine("Total GBs : " + gigabyteCount + " GB");
        Console.WriteLine("Press Enter to Continue ...");
        Console.ReadLine();
    }
}

and

const string Path = @"c:\\FolderName\\TextFile.txt";

if (File.Exists(Path))
{
    return;
}

using (var tw = new StreamWriter(Path, false))
{
    tw.WriteLine("Dock_No~Date~FileName(Location)~Description");
}

and

public static IEnumerable EachDay(DateTime startDate, DateTime endDate)
{
    for (var day = startDate.Date; day.Date <= endDate.Date; day = day.AddDays(1))
    {
        yield return day;
    }
}

StackExchange Code Review Q#33087, answer score: 4