An ASP.NET Rating System

By Paul Apostolos

In Part 1 we examined the code to get a picture's ranking into the database. Now that we have a ranking from the user we'd like to display the picture's current ranking. Before we can do this, though, we need to grab the picture's current ranking from the database. That's what we'll examine how to do in this part!

- continued -

The Image

To display the image I am going to use the src attribute of an <img> tag set to an .aspx file that will generate the image and return it directly to the browser. Alternately, I could create the image, save it to the server and set the <img> tag's src attribute to that filename. The only problem with this approach is that I'd end up with a lot of temporary image files on my Web server that would need to be deleted every so often. (For more information on dynamic image creation see this sample chapter from ASP.NET: Tips, Tutorials, and Code.)

To specify the image to display we'll create a server-side img tag. Then, in the code we can specify its Src property programmatically:

 <img id="RatingBar" runat="server" /> 'In the server-side code, you can set the src property like so: RatingBar.Src = "photo_rating_image.aspx?Photo_ID=" & Photo_ID

In the above code I concatenate Photo_ID to the filename and querystring and then set that to the src attribute of my image control RatingBar.

Let's look at the code for the file photo_rating_image.aspx. First I will import the namespaces used in this file, open a server-side script block, and begin the Page_Load event handler:

<%@ Import Namespace = "System.Data" %> <%@ Import Namespace = "System.Data.SqlClient" %> <%@ Import Namespace = "System.Drawing" %> <%@ Import Namespace = "System.Drawing.Imaging" %> <Script runat="server"> Sub Page_Load( Sender As Object, e As EventArgs)

The file photo_rating_image.aspx will receive via the querystring the Photo_Id it will need in order to get the specified photo's current rating and generate a graphical representation of that rating. Therefore, in my Page_Load event handler I need to grab the querystring value like so:

'Start by getting our Photo_ID from the querystring Dim Photo_ID As Integer = Request.Querystring("Photo_ID")

In Part 1, when I needed to establish a database connection to write the user's vote into the database table. Now that I wish to display the overall ranking, I need to create another database connection and pull out the cumulative ranking. This is done in a similar fashion as before, only this time I am calling the sp_Muggle_PhotoRatingOutput stored procedure.

'Set up a connection to my database as well as a 'SqlCommand Object for my stored procedure Dim myConnection As New SqlConnection(ConfigurationSettings.AppSettings("MyDSN")) Dim myCommand As New SqlCommand("sp_Muggle_PhotoRatingOutput", myConnection) myCommand.CommandType = CommandType.StoredProcedure 'Add the Photo_ID as a parameter to the stored procedure Dim parameterPhoto_ID As New SqlParameter("@Photo_ID", SqlDbType.Int, 4) parameterPhoto_ID.Value = Photo_ID myCommand.Parameters.Add(parameterPhoto_ID)

This stored procedure differs from my previous example because it returns a value via an output parameter. SqlCommmand parameters are by default input parameters. So, I need to specify the parameter's direction as being output. Notice it is exactly as above except I cannot assign a value to an output parameter. Also, I must add a line to let the SqlCommand object know the parameter parameterRating is an output parameter. This is accomplished like so:

'Add the Rating as a parameter to the stored procedure 'and specify its direction as output Dim parameterRating As New SqlParameter("@Rating", SqlDbType.Float, 8) parameterRating.Direction = ParameterDirection.Output myCommand.Parameters.Add(parameterRating)

I can execute the SqlCommand and retrieve the value of the output parameter by using the following lines of code:

'Open the connection, execute the stored procedure and close the connection myConnection.Open() myCommand.ExecuteNonQuery() myConnection.Close() Dim Rating_Output as Double Rating_Output = parameterRating.Value

Now I have a variable Rating_Output of type Double that contains the average ranking for the photo specified by @PhotoID. (Note that this value will be between 1.0 and 5.0.) In order to show a graphical representation of that Rating I will need to convert it to a whole number. First I need to set up some constants for my image. Because of the layout of my site I want to limit image to a width of 168 pixels and a height of 15 pixels. Therefore, I should set those up to be constants.

'Set up the constants for our image Dim MaxImageLength As integer Dim MaxImageHeight As integer MaxImageHeight = 15 ' pixels MaxImageLength = 168 ' pixels

If the largest length I would want the image is MaxImageLength or 168, then (Rating_Output / 5) * MaxImageLength is an expression that I can use to ensure my graphical representation of the rating will never exceed the MaxImageLength of 168 pixels.

Dim Rating as Integer Rating = Cint(((Rating_Output) / 5) * MaxImageLength)

Now that we have the rating for the image we'll examine how we can dynamically create a JPEG showing a graphical representation of the picture's ranking in Part 3.

Read Part 3!