Rotate Images using ASP.NET and SQL Server

It's always important for your website visitors to see fresh content. One simple way to do this to have a set of images display on a page that change each time the page is visited or refreshed. Nearly any type of website web site could benefit from this type of dynamic content. You've probably seen animated .gif images before but those can be large files which will impact page-load performance.

This website is using this very feature in the ASP.NET Master Page to rotate the scenic picture in the upper right corner of the page. Our site is only rotating one picture at a time. However we've designed other sites to use this feature to show multiple pictures. This Lesson has the following steps:

Step 1. SQL Server table and stored procedure
Step 2. Create ASP.NET Image Control
Step 3. Create ASP.NET User Control
Step 4. Create ASP.NET Component

Step 1. SQL Server table and stored procedure

First we create a table called "gallery_images". Open SQL Server and open a new query window in your database. Cut and paste the following SQL statement below to create a table called "gallerly_images". This table will store our data about the images that we want to display. The "Img" field will store the url to the image on the web site. For example "/images/rotate/rotate1.jpg"

CREATE TABLE [dbo].[Gallery_Images](
	[ImageID] [int] IDENTITY(1,1) NOT NULL,	
	[Img] [varchar](50) NULL,
	[imgH] [int] NULL,
	[imgW] [int] NULL,
	[Dttm] [datetime] NULL,
 CONSTRAINT [PK_Gallery_Images] PRIMARY KEY CLUSTERED 
(
	[ImageID] ASC
)WITH (PAD_INDEX  = OFF, 
STATISTICS_NORECOMPUTE 
 = OFF, IGNORE_DUP_KEY = OFF, 
ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Now manually upload some images to a folder on your site. In our example we'll be using /images/rotate/ and then enter the urls to the images into the gallery_images table we created. Be sure to add 5-6 images.

After adding some data your table should look similar to this with different image names naturally.

SQL Table for storing images

Next keeping an eye on security and performance we'll create a SQL Server stored procedure to query the images from the table we just created. Be sure to assign execute permissions for this stored procedure to the sql login that your web site uses to query the database.

Create PROCEDURE [dbo].[sp_images_random]

 AS

select top 1 img as 'url' from gallery_images 
order by newid() desc

Being good programmers we always test our code on the database before hooking it into a web page so manually execute the store procedure we just created in a query window

SQL Server Stored Procedure to query random images

Next Lesson

In Step 2. ASP.NET Image Control we'll setup an ASP.NET page using the ASP.NET Image Control to store the results of our query.

Code-behind

Sending Email

ASP.NET Image Control

Rotating Images

ASP.NET User Control

Famous Quotes

Search Engine Submission

They Said It Best

So we went to Atari and said, 'Hey, we've got this amazing thing, even built with some of your parts, and what do you think about funding us? Or we'll give it to you. We just want to do it. Pay our salary, we'll come work for you.' And they said, 'No.' So then we went to Hewlett-Packard, and they said, 'Hey, we don't need you. You haven't got through college yet.'
Steve Jobs, cofounder of Apple Computer