Exporting PDF From Database back to PDF Format

Question

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.

1. Filename
2. Date (From SQL Table for the Original Creation Date)
3. Case Number (internal Identifier for the 3rd party program to link to)
4. 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;
            byte[] outbyte = new byte[buffersize];
            long retval;
            long startIndex = 0;

            int j = 1;

            while (reader.Read())
            {
                strFileName = reader.GetString(0) + "-" + i + "-" + j;
                strDock_no = reader.GetString(0);
                dtFiledate = reader.GetDateTime(2);
                strDescription = reader.GetString(4);   
                fs = new FileStream("c:\\FolderName\\" + strFileName + ".pdf", FileMode.OpenOrCreate, FileAccess.Write);
                bw = new BinaryWriter(fs);
                startIndex = 0;
                retval = reader.GetBytes(1,startIndex,outbyte,0,buffersize);
                while (retval == buffersize)
                {
                    bw.Write(outbyte);
                    bw.Flush();
                    startIndex += buffersize;
                    retval = reader.GetBytes(1,startIndex,outbyte,0,buffersize);
                }
                //write the remaining buffer.
                bw.Write(outbyte,0,(int)retval);
                ByteCount = ByteCount + Convert.ToUInt64(fs.Length);
                bw.Flush();
                //close the output file
                bw.Close();
                fs.Close();
                //need to write to the Text file here.
                TextWriter tw = new StreamWriter(path,true);
                tw.WriteLine(strDock_no + "~" + dtFiledate.ToString() + "~" + "c:\\FolderName\\" + strFileName + ".pdf" + "~" + strDescription);
                tw.Close();
                // increment the J variable for the Next FileName
                j++;
                RecordCount++;
            }
         //close the reader and the connection
            reader.Close();
            i++;
        }
    Console.WriteLine("Number of Records Processed:  " + RecordCount.ToString());
        Console.WriteLine("for a Total of : " + ByteCount + " Bytes");

        Decimal MByteCount = new Decimal(2);
        MByteCount = Convert.ToDecimal(ByteCount) / 1024 / 1024;
        Decimal GByteCount = new Decimal(2);
        GByteCount = MByteCount / 1024;

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

this Code was enclosed in a foreach statement that went day by day, from a starting date to an end date. inside that foreach statement the Application called a stored procedure that was given the specified day to call the records that were entered that day.

variables i and j were created because I needed to have a unique Filename even if I had the same Case Number. i represented the day (because I went day by day in my select statement) and j represented the record number for that day from the select statement.

the foreach and the while loops were enclosed in a using(conn) so that no matter what the connection would be closed finally.

at the end of the while loop I wrote to the Text File. the Text file was created outside of all the loops so that I could just append the file rather than overwrite it. that code is:

string path = @"c:\\FolderName\\TextFile.txt";
    if (!File.Exists(path))
    {
        TextWriter tw = new StreamWriter(path, false);
        tw.WriteLine("Dock_No~Date~FileName(Location)~Description");
        tw.Close();
    }

I left out all the Console.Writeline and Console.ReadLine code that wasn't necessary to the functionality I was looking for. I had added some code also that would count the bytes that were written and some code to count the records processed. this is just fun stuff to know, I need to clean up the Fun stuff at the end.

these are the Guts of what it took to accomplish a mass Extract of PDFs from a Blob Field in SQL Server, minus some Connection Mumbo Jumbo

Foreach Day Set up

this is the Code that I used to make the foreach work the way that I wanted it to.

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

Answer 1

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<DateTime> EachDay(DateTime startDate, DateTime endDate)
{
    for (var day = startDate.Date; day.Date <= endDate.Date; day = day.AddDays(1))
    {
        yield return day;
    }
}