Introduction
This application gives you some basic photo-sharing capability similar to Ofoto or Yahoo-photos. The advantage to rolling your own application is that you gain full control over the content and layout of your site (no popup ads!) and you can completely customize it to fit your needs.
This article describes the application, which is actually 2 separate apps - a back-end Windows Forms C# application that scans your directories and files to build a database, and an ASP.NET application that presents the photos and allows the user to view them and edit them (to provide a caption and description).
Note that there is a C# Photo Album Viewer posted on CodeProject by Mark Nischalke, but that is Windows Forms only. I was looking for something enabled for use through a web browser.
Background
This article assume you have basic working knowledge of C#, Windows Forms, ASP.NET programming, and some SQL statement knowledge. You'll need to have either the full version of SQL Server, or you can get MSDE, the free version of SQL.
The application as it is currently built will scan for all *.jpg files in subdirectories of your pictures folder. Future enhancements may include recursively scanning all files in all subdirectories.
Using the code
Before reading the article you may want to get the code installed and running. To do this you should follow these steps:
- Download the Back-end Windows Forms app and unzip it somewhere on your hard drive.
- Edit the application config file to specify your SQL server and the root folder containing your pictures. This file is called App.Config, but note that VS.NET will automatically copy and rename this file to NPdbman.exe.config when you build the project.
- Run the NPdbman.exe application and select "Initialize" from the database menu. This will create the tables and constraints in a new SQL database called netpix.
- Select "Build" from the database menu. This will populate the tables with the information scanned from the folder specified in the configuration file.
- Download the Front-end ASP.NET app and unzip it in the IIS wwwroot folder. Run the IIS configuration tool, and right-click on the netpix folder. Select 'Properties' and in the 'Application Settings' pane, select 'Create'.
- Edit the web.config for the application and specify your SQL server connection.
- You should now be able to browse to http://localhost/netpix/default.aspx and browse your photos!
The database
It would have certainly been possible to build a simple application that did not use a database and simply scanned the folder and file information on the fly, but using a database will allow us to implement some advanced features which would have been awkward and difficult without the power of the RDBMS.
Schema
Here is a diagram of the database, which consists of just 2 small related tables:
The albums table is built from subdirectories of your Pictures folder. Each subdirectory maps to one album in the table. You can provide a description for an album, so that the user will see a name for the album which may or may not be the actual folder name in the file system.
The pics table is built from the *.jpg files found in each subdirectory (album). The pics table is related to the albums table by a foreign key constraint, because every picture must belong to an album. Most of the column names should be pretty self-explanatory except perhaps numviews. This column counts the number of times a user has viewed the full picture and is incremented by the ASP.NET code, every time that a user clicks on a picture. We will see this code shortly.
Stored procedures
There are just a couple stored procedures. The first one inserts a picture into the pics table (if it does not already exist there):
CREATE PROCEDURE CreatePic(@albumid int, @filename varchar(255),
@width int, @height int,
@imgdate datetime, @imgsize int) AS
IF NOT EXISTS(SELECT [id] FROM pics WHERE albumid=@albumid
AND [filename]=@filename)
INSERT INTO pics (albumid, [filename], width,
height, imgdate, imgsize)
values (@albumid, @filename, @width,
@height, @imgdate, @imgsize);
The second is similar, but it operates on the albums table and it returns the identity value of the new record (or the existing one):
CREATE PROCEDURE CreateAlbum(@rootpath varchar(1024),
@description varchar(255), @id int output) AS
SELECT @id = (SELECT [id] FROM albums WHERE rootpath=@rootpath);
IF @id IS NULL
BEGIN
INSERT INTO albums (rootpath, [description])
values (@rootpath, @description);
SET @id = SCOPE_IDENTITY();
END
The back-end
The back end is a Windows Forms application that you run on the server to build the database by scanning your pictures folder. There are 2 basic functions: to reset (delete) everything in the database, and then to scan and build all the entries. This article won't discuss how you build a forms application or hook up menu entries, etc., because that is covered in depth elsewhere.
Reset/ Initialize Code
Let's take a look at the reset/initialize code. First there is a generic routine which reads a .sql script file and executes it on the given connection:
private void ExecuteBatch(SqlConnection conn, string filename)
{
System.IO.StreamReader r = System.IO.File.OpenText(filename);
string sqlCmd = r.ReadToEnd();
r.Close();
SqlCommand cmd = new SqlCommand(sqlCmd, conn);
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
}
This is pretty basic SQL interaction. This code is executed for the scripts resetdb.sql, createalbum.sql and createpics.sql. These script file contains all the necessary SQL to drop and then CREATE TABLE and ALTER TABLE statements to setup the database schema and stored procedures. Any error that occurs here will be thrown from the application and handled by the generic popup handler.
Populating the database
The database is built by compiling the scanned information from the file system into a DataSet object which is then committed to the database. First we setup the objects we will use for creating the albums table:
DataSet ds = new DataSet();
insertAlbumCmd = new SqlCommand("CreateAlbum", conn);
insertAlbumCmd.CommandType = CommandType.StoredProcedure;
insertAlbumCmd.Parameters.Add("@rootpath",
SqlDbType.VarChar, 1024, "rootpath");
insertAlbumCmd.Parameters.Add("@description",
SqlDbType.VarChar, 256, "description");
insertAlbumCmd.Parameters.Add("@id", SqlDbType.Int, 0, "id");
insertAlbumCmd.Parameters["@id"].Direction = ParameterDirection.Output;
insertAlbumCmd.UpdatedRowSource = UpdateRowSource.OutputParameters;
We build the command which will invoke the stored procedure listed above. We tell ADO.NET that, after the insert, it should take the output parameter from the stored procedure and use this value (the identity value) to update the id column of the disconnected DataTable.
albumsAdapter = new SqlDataAdapter("SELECT * FROM albums", conn);
albumsAdapter.InsertCommand = insertAlbumCmd;
albumsAdapter.FillSchema(ds, SchemaType.Mapped, "albums");
DataTable albums = ds.Tables["albums"];
Here we attach the command to a SqlDataAdapter object and then pull the schema from the database into our table.
DataColumn dc = albums.Columns["id"];
dc.AutoIncrementSeed = -1;
dc.AutoIncrementStep = -1;
This part is important because it avoids any duplicate keys being generated during the batch update. If the SQL server returns an identity value which already exists in the DataTable, an exception would be thrown. Using negative identity values prevents this from ever happening.
Finally we can get about doing the actual work:
string[] dirs = System.IO.Directory.GetDirectories(rootPath);
foreach (string dir in dirs)
{
string dirname = System.IO.Path.GetFileName(dir);
DataRow dr = albums.NewRow();
dr["rootpath"] = dir;
dr["description"] = dirname;
albums.Rows.Add(dr);
}
albumsAdapter.Update(ds, "albums");
The Update will insert all pending rows into the data store.
Populating the pictures table follows the same logic, so I won't repeat it here. For each *.jpg file found, a row is added to the DataTable and then the SqlDataUdapter Update method is invoked in order to perform the necessary inserts. The main difference are the columns required; for each image found, this method is called to collect the necessary data into the DataRow:
protected void GetImageInfo(string imgpath, DataRow dr)
{
System.IO.FileStream fs = File.Open(imgpath,
FileMode.Open, FileAccess.Read, FileShare.Read);
Bitmap img = new Bitmap(fs);
dr["filename"] = System.IO.Path.GetFileName(imgpath);
dr["imgsize"] = (int)fs.Length;
dr["height"] = img.Height;
dr["width"] = img.Width;
dr["imgdate"] = File.GetCreationTime(imgpath);
dr["numviews"] = 0;
img.Dispose();
fs.Close();
}
Unfortunately this causes a major performance hit because each image must be loaded into memory in order to determine its dimensions. This is a one-time up-front operation, so this is an acceptable tradeoff.
The front-end
The front-end is the actual ASP.NET application which pulls our data out from the database and formats it nicely for the user.
The list of albums
The first thing the user sees is a list of albums, along with a little folder icon and a bit of information about the album itself (the number of pictures it contains). This is implemented with a DataList control. The control is defined here:
<asp:DataList id="dl" runat="server"
RepeatDirection="Horizontal" RepeatColumns="3">
<ItemTemplate>
<table><tr><td><img src="folder.png"></td>
<td><asp:HyperLink Runat="server" ID="hlItem"
NavigateUrl='<%# "viewalbum.aspx?id=" +
DataBinder.Eval(Container.DataItem, "id")%>'
Text='<%#DataBinder.Eval(Container.DataItem,
"description")%>'>
</asp:HyperLink><br>
<asp:Label Runat="server" ID="lbItem"
Text='<%# DataBinder.Eval(Container.DataItem,
"piccount") + " pictures" %>'>
</asp:Label></td>
</tr></table>
</ItemTemplate>
</asp:DataList>
The important thing to note here is that each item is comprised of a folder bitmap, a HyperLink control, and a Label control. The Hyperlink has its text bound to the description of the album, and its URL bound to the viewalbum.aspx page. It passes the album ID to the viewalbum.aspx in the URL.
The code behind for this file is all of two lines:
dl.DataSource = npdata.GetAlbums();
dl.DataBind();
The GetAlbums method is defined in a class named npdata. The npdata class contains static methods which encapsulate the data access adapters and commands to interface with the SQL database. The GetAlbums method does a basic select and fill and returns the DataSet. You may notice that the Label control references the piccount column, which does not exist in our schema. The piccount column is a calculated value which you can see in the query we use to bind to the list:
SqlDataAdpater adap = new SqlDataAdapter("SELECT *," +
"(SELECT COUNT(*) FROM pics WHERE pics.albumid=albums.id)
AS piccount " +
"FROM albums", conn);
So the piccount column is calculated by doing a sub-query on the pics table to determine how many pictures have a parent in the given album.
Viewing an album
When the user clicks on an album, the NavigateUrl property from the HyperLink control directs the browser to viewalbum.aspx and the album ID is passed along in the URL. This page generates a thumbnail for each image along with a basic description, and allows the user to click the image or edit the image properties. We once again utilize the DataList control for this functionality and it operates much the same way. The one point of note in this DataList is the actual URL for the thumbnail image:
<img border="0" src='<%# "genimage.ashx?thumbnail=y&id="
+ DataBinder.Eval(Container.DataItem, "id") %>'>
We can't link directly to the .jpg file because the server is not directly sharing the images folder. So we link to a page called genimage.ashx which implements a sort of proxy that accepts the picture ID and streams the actual image data back to the client. It also accepts a thumbnail parameter, which indicates that the image should be sized down to 150x150. Note the .ashx extension; these are special files containing directives that you easily implement your own IHttpHandler-derived class. These classes give you a low-level interface to send data back to the client without all the overhead of creating and managing the lifecycle of a Page object. Our .ashx file contains only one line:
<%@ WebHandler Language="C#" Class="netpix.ImageGenerator" %>
This directs the client request for handling by the ImageGenerator class which is discussed in the next section.
Generating the pictures
The ImageGenerator class implements the IHttpHandler interface, which is a very simple and low-level interface for send raw streams of data back to the client. We are just dumping the bytes of image, so it's perfect for our needs. Since this class is key to the operation of the application, we will examine all of the code for this class:
public class ImageGenerator : IHttpHandler
{
public bool IsReusable
{ get { return true; } }
public void ProcessRequest(HttpContext Context)
{
int numviews;
int picid = Convert.ToInt32(Context.Request["id"]);
string imgpath = npdata.GetPathToPicture(picid, out numviews);
Context.Response.ContentType = "image/jpg";
Here we retrieve the picture ID from the URL and invoke the GetPathToPicture method which wraps a SQL join statement that returns the full local path to the image, as well as the number of times the image has been viewed on the client. Then we set the content type to jpg because we are impersonating a jpg file.
if (Context.Request["thumbnail"] != null)
{
Bitmap bmp = new Bitmap(imgpath);
float scale = 150.0f / System.Math.Max(bmp.Height, bmp.Width);
System.Drawing.Image thumb = bmp.GetThumbnailImage(
(int)(bmp.Width * scale), (int)(bmp.Height * scale),
null, System.IntPtr.Zero);
thumb.Save(Context.Response.OutputStream,
System.Drawing.Imaging.ImageFormat.Jpeg);
bmp.Dispose();
thumb.Dispose();
}
In the case where the Request URL contains the thumbnail parameter, we first load the image file from disk and call GetThumbnailImage to scale it down. We scale it down by a constant factor to maintain the aspect ratio so as not to distort the image. We then save the resized image directly to the Response object's output stream. This puts a pretty heavy stress on the server CPU when a large number of thumbnails are requested (I will discuss this in the 'Future Items' section).
else
{
System.IO.FileStream fs = File.Open(imgpath,
FileMode.Open, FileAccess.Read, FileShare.Read);
const int byteLength = 16384;
byte[] bytes = new byte;
while( fs.Read(bytes, 0, byteLength ) != 0 )
{
Context.Response.BinaryWrite(bytes);
}
fs.Close();
npdata.SetNumViews(picid, numviews+1);
}
}
In this case, we are interested in streaming the image directly from the file contents. The current implementation reads the file contents in chunks and sends them to the response's output stream. We also need to increment the picture's view count in the database because the full-size image has been requested. The SetNumViews method just issues an SQL UPDATE statement to the pics table to set the numviews column for the given picture.
Viewing and editing a Picture
From the album view, the user can either view an image or edit the image information. There really isn't much of interest happening in viewpic.aspx or editpic.aspx. The user can supply title and description information in the editor, which will then be used by the viewer. The viewer will show the title for a picture if available, otherwise it will default to the filename. This is accomplished in the SQL statement:
getpicinfo = new SqlCommand("SELECT ISNULL(title, filename)
AS returntitle, " +
"ISNULL([description],'') AS returndesc "+
"FROM pics WHERE pics.id=@picid", conn);
getpicinfo.Parameters.Add("@picid", SqlDbType.Int);
Our schema design dictates that we will use the DB Null value to indicate no custom title is present. When this happens, we use the image filename as the text for the picture's caption.
Future/To-Do items
This application only represents the basic framework of a robust image cataloging and viewing system. Some ideas that can be implemented:
- Cache the thumbnail images in the application's Cache dictionary. Setup a file system dependency to automatically invalidate the cache item if the image changes on disk.
- Support for multiple users could be achieved by adding a user table to the database and adding another foreign key column to the albums table to identify the user it belongs to.
- More information could be stored in the
pics table: number of colors, etc. and more formats (i.e. GIF) could be supported
- Add functionality to invite users to view your pictures using a web form to specify the E-mail addresses.
- As noted, generating the thumbnails on the fly puts a heavy CPU load on the server. In a high-volume environment, it is preferable for the back-end generator to create the thumbnails and either store them in the database itself or at a known location in the file system.
- Many other ideas...
History
- Initial version: July 27, 2003
- Updated: June 29, 2003 - Implemented some suggestions from feedback.
