Hi all!

I hope someone can help me out with this annoying problem. Please see this image for reference: http://www.flickr.com/photos/mattsmith321/2689459346/sizes/o/

I have a functioning application that is responsible for creating PDF Letters by combining data with a template (think mail merge). The PDF image/blob is stored in the database in a varbinary(max) field (called LetterPDF). On the DataTable that field maps to a byte[] (also called LetterPDF). The data access is built via DataTables on top of stored procedures. Everything (all CRUD functions) have worked great for about a year now.

Unfortunately, one of our list methods (GetLettersByStatus) is starting to blow up now because there are certain statuses that return several thousand records/letters. Given that each PDF image/blob is ~100K, returning that many records with that much data causes memory issues (understatement).

What I would like to do is modify the GetLettersByStatus sproc to dummy out the value for the PDF image/blob and continue using the existing DataTable structure. However, no matter how I try to return an empty/null value for that field, I keep running into errors with the DataTable mapping (not surprised really). Unfortunately, I can't passed it no matter how hard I try.

The error I am getting is this:

System.InvalidOperationException: Inconvertible type mismatch between SourceColumn 'LetterPDF' of Int32 and the DataColumn 'LetterPDF' of Byte[]..

Here is the select statement from the sproc:

    SELECT  [LetterID],
            [LetterStatus],
            [LetterTemplate],
            [LetterDate],
            [EntityID],
            [EntityDate],
            [ClientID],
            [ClientFirstName],
            [ClientLastName],
            [ProviderID],
            [ProviderName],
            [LetterData],
            [LetterPDF], -- <<<< HERE IS WHAT I WANT TO CHANGE
            [PrintToPrintCenter],
            [CustomPrinterName],
            [NumPrintAttempts],
            [LastStatusMessage],
            [LastModifiedDate]
    FROM    [Letters] WITH ( NOLOCK )
    WHERE   [LetterStatus] = @LetterStatus

I have tried modifying the noted line above with the following with no success:

NULL AS [LetterPDF],

'' AS [LetterPDF],

0 AS [LetterPDF],

It chokes on the this.Adapter.Fill(dataTable) in the following generated code:

        [global::System.ComponentModel.Design.HelpKeywordAttribute("vs.data.TableAdapter")]
        [global::System.ComponentModel.DataObjectMethodAttribute(global::System.ComponentModel.DataObjectMethodType.Select, false)]
        public virtual LetterDS.LetterDataTable GetLettersByStatus(global::System.Nullable<byte> LetterStatus) {
            this.Adapter.SelectCommand = this.CommandCollection[4];
            if ((LetterStatus.HasValue == true)) {
                this.Adapter.SelectCommand.Parameters[1].Value = ((byte)(LetterStatus.Value));
            }
            else {
                this.Adapter.SelectCommand.Parameters[1].Value = global::System.DBNull.Value;
            }
            LetterDS.LetterDataTable dataTable = new LetterDS.LetterDataTable();
            this.Adapter.Fill(dataTable);
            return dataTable;
        }

I know the easy way around this is to just create a new DataTable that doesn't have the LetterPDF field, put my one method on it and call it a day. However, that seems like a cop-out. Given that everything works and is in production, I only want to affect this one method/sproc (GetLettersByStatus) and can't go mucking around to much.

Any help is appreciated!

Thanks, Matt

Of course, after fighting with this for quite a while, I immediately stumble on something that gets me past that hurdle within two minutes of submitting this post

I changed the line in the sproc to:

convert(varbinary(max), 0) AS [LetterPDF],

Now I can preview the results in the DataTable without blowing up and I can pass my MS Test case.

However, a little later on, I am mapping the DataTable row to a custom DTO Letter object (which is returned by a web service). Ideally I would want the LetterPDF to be NULL or something easy to check but it is coming back with a length of 4. See this image for debug image: http://www.flickr.com/photos/mattsmith321/2689601798/sizes/o/

What should I check to determine if I can plug null into my custom object? Do I check to see if the length is = 4?

letter.PdfDocument = row.LetterPDF.Length = 4 ? null : row.LetterPDF;

Is there a way to get the sproc to return 0 length (to make it a little cleaner)?

FYI - In case you haven't guessed, this has nothing to do with LLBL but I know there are some smart people here that can probably help me out.

I think I got it!

I changed the sproc to the following and the length was 1 which was better.

convert(varbinary(1), 0) AS [LetterPDF],

Then I tried:

convert(varbinary(1), null) AS [LetterPDF],

This allowed the sproc to return null in the correct format that didn't choke the DataTable mappings.

In the end, the code to map the custom object gets to stay the same because it can use the IsLetterPDFNull() check.

letter.PdfDocument = row.IsLetterPDFNull() ? null : row.LetterPDF;

Thanks for listening!