Whilst rebuilding one of our Lanzarote sites today I needed to pick two random images from the database and display them inside a Master Page wrapped in a Light Box and access the database through LINQ-to-SQL.

I found some useful stuff which other people might find equally useful (not sure where it came from but I've modified it heavily and converted it to VB.NET).

First of all create a view and a function in SQL:

CREATE VIEW [dbo].[RandomView]
AS
SELECT NEWID() As ID
GO

CREATE FUNCTION [dbo].[GetNewId]()
RETURNS uniqueidentifier
AS
BEGIN
RETURN (SELECT ID FROM RandomView)
END
GO

These are the page controls:

<div class="adleft">
    <asp:HyperLink ID="hyperlinkLeft" runat="server" title="Scots and Wry Costa Teguise" rel='lightbox[gallery]'>
    <asp:Image ID="imageLeft" runat="server" Width="105" Height="105" title="Scots & Wry Costa Teguise" Alt="Photo at Scots and Wry Lanzarote"/>
</asp:HyperLink>
</div>
<div class="adright">
    <asp:HyperLink ID="hyperlinkRight" runat="server" title="Scots and Wry Costa Teguise" rel='lightbox[gallery]'>
    <asp:Image ID="imageRight" runat="server" Width="105" Height="105" title="Scots & Wry Costa Teguise" Alt="Photo at Scots and Wry Lanzarote"/>
</asp:HyperLink></div>

Add the function to the DBML panel in Visual Studio and then add this to the code where you need the pictures (note that I am wrapping the two images in a hyperlink on the ASPX page to get the lightbox):

        Using db As New LadybirdFoundationClassesDataContext
            Dim leftPhoto = (From p In db.Photographs Where p.ClientUserName.Contains("MYUSERNAME")).OrderBy(Function(t) db.GetNewId()).First()
            Dim rightPhoto = (From p In db.Photographs Where p.ClientUserName.Contains("MYUSERNAME")).OrderBy(Function(t) db.GetNewId()).First()
            imageLeft.ImageUrl = "thumbnail.aspx?id=" & leftPhoto.fldID & "&maxwidth=105&maxheight=105"
            hyperlinkLeft.NavigateUrl = "thumbnail.aspx?id=" & leftPhoto.fldID & "&maxwidth=500&maxheight=500"
            imageRight.ImageUrl = "thumbnail.aspx?id=" & rightPhoto.fldID & "&maxwidth=105&maxheight=105"
            hyperlinkRight.NavigateUrl = "thumbnail.aspx?id=" & rightPhoto.fldID & "&maxwidth=500&maxheight=500"
        End Using

I suspect that people like Steve (see http://stevescodingblog.co.uk/ ) have a whole library of functions like this but these Lambda expressions are all new (and hard!) for me.  At some point I'll probably turn the image displayer into a server control and then just drag it onto the page as needed and put the thumbnailer into my base class somewhere because a project I am currently working on needs random ads which are different sizes and in different places.  Anyway, it works and that's the main thing!