It's called a starter kit, so it's safe to assume most people that use the Personal Website Starter Kit are not professional web developers. It's probably also safe to assume that most student or hobbyist developers who would use the kit do not admin their own server rack, and must rely on purchased hosting plans to publish their personal websites to the Interweb. So then why would Microsoft create a starter kit that stored all of the website's pictures (times 4 different sizes) in a SQL server instance?? Do they realize how much hosted database space costs?!? With my hosting plan, I receive a whoppin' 20GB of webspace along side a puny 200MB of SQL Server space. It's easy to see my motivation to modify the website starter kit so that it stores its pictures on the filesystem instead of the database.
Disclaimer: I am fresh out of college, and by no means an expert. With that said, I offer no warranties on the modifications. Like most others, I am using the starter kit as a learning tool. If you notice any errors in my code or conventions, please feel free to post a comment or send an email so that I may correct the error and learn from my mistakes. But please no nitpicking. Now on with the show...
All updated files involved in the modification can be downloaded here.
After completing this modification, your website will store all pictures aqcuired via the manage albums/photos pages on the server's filesystem at '~/Images/Albums/'. The PhotoManager will not only create the image files on the server when a picture is uploaded, but delete the files as well when albums/pictures are removed via the manage albums/pictures pages. Because the web app handles all image file related tasks, you should not manual add/delete files in '~/Images/Albums' or risk throwing the database and associated files out of sync.
The database schema must be altered to store file locations instead of the actual pictures. Below is a diagram depicting the new schema:
After changing the schema, be sure to alter the stored procedures, 'GetFirstPhoto' and 'GetPhoto', so that they return the location column instead of the actual picture.
The Microsoft dev that put the starter kit together definately had the concept of abstraction figured out, thus making my life a whole lot easier. Besides alterations to a few stored procedures and adding another property to the Photo class, all modifications were contained to the PhotoManager class. Listed below are the PhotoManager methods that were either added or modified, along with the alterations on the stored procedure:
Photo.cs
public
class Photo
{
private int _id;
private int _albumid;
private string _caption;
private string _location;
public int PhotoID { get { return _id; } }
public int AlbumID { get { return _albumid; } }
public string Caption { get { return _caption; } }
public string Location { get { return _location; } }
public Photo(int id, int albumid, string caption, string location)
{
_id = id;
_albumid = albumid;
_caption = caption;
_location = location;
}
}
PhotoManager.cs
public static Stream GetPhoto(int photoid, PhotoSize size)
{
using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Personal"].ConnectionString))
{
using (SqlCommand command = new SqlCommand("GetPhoto", connection))
{
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(new SqlParameter("@PhotoID", photoid));
bool filter = !(HttpContext.Current.User.IsInRole("Friends") || HttpContext.Current.User.IsInRole("Administrators"));
command.Parameters.Add(new SqlParameter("@IsPublic", filter));
connection.Open();
string result = (string) command.ExecuteScalar();
try
{
//-Get image location on server
string path = HttpContext.Current.Server.MapPath("~/Images/");
path += result;
Stream pic = new FileStream(path, FileMode.Open, FileAccess.Read, FileShare.Read);
return ResizeImageFile(pic, size);
}
catch
{
return null;
}
}
}
}
public static Stream GetFirstPhoto(int albumid, PhotoSize size)
{
using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Personal"].ConnectionString))
{
using (SqlCommand command = new SqlCommand("GetFirstPhoto", connection))
{
command.CommandType =
CommandType.StoredProcedure;
command.Parameters.Add(
new SqlParameter("@AlbumID", albumid));
bool filter = !(HttpContext.Current.User.IsInRole("Friends") || HttpContext.Current.User.IsInRole("Administrators"));
command.Parameters.Add(
new SqlParameter("@IsPublic", filter));
connection.Open();
string result = (string) command.ExecuteScalar();
try
{
//-Get image location on server
string path = HttpContext.Current.Server.MapPath("~/Images/");
path += result;
Stream pic = new FileStream(path, FileMode.Open, FileAccess.Read, FileShare.Read);
return ResizeImageFile(pic, size);
}
catch
{
return null;
}
}
}
}
public static List<Photo> GetPhotos(int AlbumID)
{
using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Personal"].ConnectionString))
{
using (SqlCommand command = new SqlCommand("GetPhotos", connection))
{
command.CommandType =
CommandType.StoredProcedure;
command.Parameters.Add(
new SqlParameter("@AlbumID", AlbumID));
bool filter = !(HttpContext.Current.User.IsInRole("Friends") || HttpContext.Current.User.IsInRole("Administrators"));
command.Parameters.Add(
new SqlParameter("@IsPublic", filter));
connection.Open();
List<Photo> list = new List<Photo>();
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
Photo temp = new Photo(
(
int)reader["PhotoID"],
(
int)reader["AlbumID"],
(
string)reader["Caption"],
(
string)reader["Location"]);
list.Add(temp);
}
}
return list;
}
}
}
public static void AddPhoto(int AlbumID, string Caption, byte[] BytesOriginal)
{
int photoID;
string location;
//-Add photo to DB
using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Personal"].ConnectionString))
{
using (SqlCommand command = new SqlCommand("AddPhoto", connection))
{
command.CommandType =
CommandType.StoredProcedure;
command.Parameters.Add(
new SqlParameter("@AlbumID", AlbumID));
command.Parameters.Add(
new SqlParameter("@Caption", Caption));
connection.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
reader.Read();
photoID = reader.GetInt32(0);
//- in case photo has to removed from DB later
location = reader.GetString(1);
}
}
}
//-Save photo to filesystem
string path = System.Web.HttpContext.Current.Server.MapPath("~/Images/");
path += location;
try
{
WriteToFile(path,
ref BytesOriginal);
}
catch
{
//-If picture fails to save on filesystem, remove picture's record from DB
using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Personal"].ConnectionString))
{
using (SqlCommand command = new SqlCommand("RemovePhoto", connection))
{
command.CommandType =
CommandType.StoredProcedure;
command.Parameters.Add(
new SqlParameter("@PhotoID", photoID));
connection.Open();
command.ExecuteNonQuery();
}
}
}
}
public static void RemovePhoto(int PhotoID)
{
string location;
//-Remove photo from DB
using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Personal"].ConnectionString))
{
using (SqlCommand command = new SqlCommand("RemovePhoto", connection))
{
command.CommandType =
CommandType.StoredProcedure;
command.Parameters.Add(
new SqlParameter("@PhotoID", PhotoID));
connection.Open();
location = (
string) command.ExecuteScalar();
}
}
//-Remove photo from filesystem
string path = HttpContext.Current.Server.MapPath("~/Images/");
path += location;
if (File.Exists(path))
File.Delete(path);
}
public static void RemoveAlbum(int AlbumID)
{
string location;
using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["Personal"].ConnectionString))
{
using (SqlCommand command = new SqlCommand("RemoveAlbum", connection))
{
command.CommandType =
CommandType.StoredProcedure;
command.Parameters.Add(
new SqlParameter("@AlbumID", AlbumID));
connection.Open();
location = (
string) command.ExecuteScalar();
}
}
string path = HttpContext.Current.Server.MapPath("~/Images/");
path += location;
if (Directory.Exists(path))
Directory.Delete(path, true);
}
private static Stream ResizeImageFile(Stream imageFile, PhotoSize targetSize)
{
if (targetSize == PhotoSize.Original)
{
//-Retun pic without resizing
return imageFile;
}
else
{
//-Convert pic to byte[]
byte[] picBuff = StreamToBytes(imageFile);
//-Resize pic
if (targetSize == PhotoSize.Large)
{
picBuff = ResizeImageFile(picBuff, 600);
}
else if (targetSize == PhotoSize.Medium)
{
picBuff = ResizeImageFile(picBuff, 198);
}
else if (targetSize == PhotoSize.Small)
{
picBuff = ResizeImageFile(picBuff, 100);
}
//-Convert buffer back to stream and return
return new MemoryStream((byte[])picBuff);
}
}
private static byte[] StreamToBytes(Stream stream)
{
//-Create buffer
int byteCount = (int)stream.Length;
byte[] buffer = new byte[byteCount];
//-Read stream into buffer
using (stream)
{
stream.Seek(0,
SeekOrigin.Begin);
stream.Read(buffer, 0, byteCount);
}
//-Return buffer
return buffer;
}
private static void WriteToFile(string path, ref byte[] buffer)
{
//-Ensure directory is present
string dir = Path.GetDirectoryName(path);
if (!Directory.Exists(dir))
Directory.CreateDirectory(dir);
//-Create picture file
using (FileStream newFile = new FileStream(path, FileMode.Create))
{
newFile.Write(buffer, 0, buffer.Length);
}
}
Stored Procedures:
ALTER PROCEDURE
AddPhoto
@AlbumID
int,
@Caption
nvarchar(50)
AS
INSERT INTO [Photos] (
[AlbumID],
[Caption],
[Location])
VALUES (
@AlbumID,
@Caption,
'tmpLocation') /* tmpLocation needed because app broke when Location column set to Allow NULLs */
/* Retrieve generated PhotoID */
DECLARE @PhotoID int
SET @PhotoID = SCOPE_IDENTITY()
/* Build unique location path from album and photo ID */
DECLARE @Location nvarchar(50)
SET @Location = 'Albums\' + CONVERT(nvarchar(10), @AlbumID) + '\' + CONVERT(nvarchar(10),@PhotoID) + '.jpg'
/* Update photo with new location path */
UPDATE [Photos]
SET
[Location] = @Location
WHERE
[PhotoID] = @PhotoID
/* Return PhotoID and Location */
SELECT @PhotoID, @Location
RETURN
ALTER PROCEDURE
RemovePhoto
@PhotoID
int
AS
/* Retrive photo's location */
DECLARE @Location nvarchar(50)
SET @Location = (
SELECT [Location]
FROM [Photos]
WHERE [PhotoID] = @PhotoID)
/* Delete photo record */
DELETE FROM [Photos]
WHERE [PhotoID] = @PhotoID
/* Return deleted photo's file location */
SELECT @Location
RETURN
ALTER PROCEDURE
RemoveAlbum
@AlbumID
int
AS
DELETE FROM [Albums] WHERE [AlbumID] = @AlbumID
/* Build deleted album's directory location */
DECLARE @Location nvarchar(50)
SET @Location = 'Albums\' + CONVERT(nvarchar(10), @AlbumID) + '\'
/* Return deleted album's directory location */
SELECT @Location
RETURN