locked
Paging Through Images Stored in Database RRS feed

  • Question

  • User878358631 posted

    Hello,

    I am trying to develop a ASP.NET "Web Pages" application (using Razor) with WebMatrix 3.0, using the built in Sql Server CE database.

    After following and adapting the "Mikesdotnetting" tutorial (http://www.mikesdotnetting.com/article/148/save-and-retrieve-files-from-a-sql-server-ce-database-with-webmatrix), I have successfully created the functionality to upload images to the database, and can display a single image on the web page, but I have been unsuccessful trying to adapt the code to 'page' through the images in the database.

    Essentially, I have a table where the binary image data is stored (I"mages" table), and would like to display the first image record on a webpage Gallery.cshtml, then include the functionality with a next button to load (page to) the next image record on the webpage, and so on (like an image gallery).

    I have the following two files.

    Gallery.cshtml

    <html lang="en">
    <head>
    </head>
    <body>
    <img id="image" src="image.cshtml">
    </body>
    </html>

    And,

    Image.cshtml

    @{

    int id = 10;
    var db = Database.Open("StarterSite");
    var sql = "Select * From Images Where Id = @0";
    var file = db.QuerySingle(sql, id);
    Response.AddHeader("content-disposition", "inline; filename=" + file.FileName);
    Response.ContentType = file.MimeType;
    Response.BinaryWrite((byte[])file.FileContent);

    }

    I have a sample of 10 images stored in the "Images" table, and what I would like to achieve is to provide a 'next' button on the Gallery.cshtml to load the next image record from the database table on the page Gallery.cshtml.

    I have searched online for weeks for some sample code to try to acheive this, but have finially have hit a brick wall !!

    I think what I need to do is count the number of 'Image' records in the "Images" database table, and create a @href link passing the record value Id as a querystring, but I am struggling to put this together.

    The table structure for the "Images" table is:

    ID as Int;
    FileName as Navchar
    MimeType as Navchar
    FileContent as image (datatype)

    I would be very grateful for any advice or guidance on how this may be achievable.  I hope this makes sense, but please let me know if you need any further explanation, etc.

    Many thanks,

    Kind regards, James

    Monday, July 20, 2015 5:50 PM

Answers

  • User-821857111 posted

    You need to use OFFSET and FETCH in your SQL query. And example of that can be found in this article: http://www.mikesdotnetting.com/article/150/web-pages-efficient-paging-without-the-webgrid

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, July 21, 2015 3:31 AM
  • User-821857111 posted

    You just need to amend the logic to output what you want. The current page is represented by the variable 'page'. If page is greater than 1, you are not on the first page so show a Previous link. If page is less than totalPages, you are not on the last page, so show a Next link.

    Now convert that to code:

    @if(page > 1){
        <a href="/Paging/@(page - 1)">Previous</a>
    }
    @if(page < totalPages){
        <a href="/Paging/@(page + 1)">Next</a>
    }
    



    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, July 22, 2015 9:27 AM

All replies

  • User-821857111 posted

    You need to use OFFSET and FETCH in your SQL query. And example of that can be found in this article: http://www.mikesdotnetting.com/article/150/web-pages-efficient-paging-without-the-webgrid

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, July 21, 2015 3:31 AM
  • User878358631 posted

    Hello Mike, 

    Thank you for your reply on this question.

    Yes, this the functionality I have been trying to accomplish.  I spent sometime yesterday working with this, and now have it working with my database and pages, etc.

    I was wondering if it is possible to modified this code to show a 'Next' button (or link) instead of the page numbers along the bottom (1 2 3 4 5,).  I have made an attempt at changing the code, but have been very unsuccessful !!!

    Wednesday, July 22, 2015 5:43 AM
  • User-821857111 posted

    You just need to amend the logic to output what you want. The current page is represented by the variable 'page'. If page is greater than 1, you are not on the first page so show a Previous link. If page is less than totalPages, you are not on the last page, so show a Next link.

    Now convert that to code:

    @if(page > 1){
        <a href="/Paging/@(page - 1)">Previous</a>
    }
    @if(page < totalPages){
        <a href="/Paging/@(page + 1)">Next</a>
    }
    



    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, July 22, 2015 9:27 AM
  • User878358631 posted

    Hi Mike, 

    Thank you for taking the time to respond to my post - the information you have provided has helped my resolve my issue.

    All the best, James

    Tuesday, July 28, 2015 3:50 PM