Reading sql server image data types using linq to sql

Question

Hi

I am uploading an image to sql server 2005 table that has a field of type "image" using linq to sql with the following code that works great

                // convert the fileupload.postedfile to system.data.linq.binary
                 byte[] fileArray = new byte[photoFileUpload.PostedFile.ContentLength + 1];                         
                 photoFileUpload.PostedFile.InputStream.Read(fileArray, 0, photoFileUpload.PostedFile.ContentLength);           
                 System.Data.Linq.Binary image = new System.Data.Linq.Binary(fileArray);
                 dc.mydatacontextPhoto_update(3, image);

I am using a sproc "mydatacontextPhoto_update" to insert the image.

My question is how to read the image back into a listview control?  How would I do this?
Thanks
:-)

Answer 1

If you're asking how to select the image data back as a byte array:

 

var bytes = from b in MyPhotoTable // the table name from your data context holding the image data

               where b.PhotoID = 489 // arbitrary of course since I don't know how to filter your data

               select b.First() // you may want a collection of these, but I'm just getting the first for this example

 

Then you can use the results of the query like:

 

byte[] photo = bytes.ToArray<byte>();

 

I'm not sure how you need to use that data in your listview, so I'll leave it there. Let me know if that helped!

 

Answer 2

 

Hi

 

Thanks a lot for your comment.  Very useful  :-)

 

I need to display a set of products incluing an image and additional text in a listview.

For example in Scott Guthrie's blog on the listview control he uses an example like this using hard coded jpegs in the asp.net app

 

 <asp:ListView ID="itemPlaceholder" runat="server">
        <LayoutTemplate>
            <ul class="productlist">
                <asplaceHolder ID="itemPlaceHolder" runat="server" />
            </ul>
        </LayoutTemplate>
        <ItemTemplate>
            <li>
                <img src="Photos/<%#Eval("ProductID") %>.jpg" />
                <br />
                <%#Eval("ProductName") %></li>
        </ItemTemplate>
        <EmptyDataTemplate>
            <h4>
                Sorry - no products found for specified catalog
            </h4>
        </EmptyDataTemplate>
    </asp:ListView>

 

 

I want to to the same thing, so to answer your previous question it would be for a whole collection also just one one item

 

Thanks very much for your comments they are very informative :-)

 

 

Answer 3

In Scott's example, his image source is an actual .jpg file. In order to accomplish what he's doing you would have to get each photo back and write the bytes out to a file. If your purpose is to display the photos in a listview and your data is in SQL Server, then I would not recommend this approach since there is no point in writing the data to disk just to bring it back into memory again. 

Since you're in a web application, the source for your image in this case would have to a file (not ideal) or a url. I don't know if you have any experience creating ashx handlers, but that would be your best bet. There are plenty of tutorial showing how to create these handlers, so I won't go into that. A handler will be great since it will allow you to write your images out to a response stream without having to live the entire asp .net page life cycle.

What you will need is to make your ashx handler accept a parameter that is some kind of ID and it will query your image data for that ID and generate a byte[] like I showed in my last post. Then, your handler will write that byte[] out to the response stream.

In your listview, the src for your image will be the url of your handler and it will pass in an id to the url just like Scott was doing in his example you showed. This way, your images can live in memory and you can request them on a per image basis from your handler.

I hope that helped.

Answer 4

 

Wow  thanks for your excellent reply

 

I have had a look at ashx handlers and am a bit confused.  Would it be possible to post a simple example please?   This would give me a good starting point to implement my problem.

 

Thanks very much for your reply

 

 

 

 

Answer 5

Quick update

 

Yep, I managed to get it working using generic handler. 

 

Thanks very much for all your help

 

Jules  :-)

 

Answer 6

You're welcome!