/// <summary>
/// Return a collection representing the metadata items for the specified <paramref name="mediaObjectId"/>. If no matching object
/// is found in the data store, an empty collection is returned.
/// </summary>
/// <param name="mediaObjectId">The ID that uniquely identifies the desired media object.</param>
/// <returns>
/// Returns a collection of all metadata items.
/// </returns>
public static IEnumerable <MediaObjectMetadataDto> GetMetadataItemsByMediaObjectId(int mediaObjectId)
{
using (SqlConnection cn = SqlDataProvider.GetDbConnection())
{
return(GetMetadataItemsByMediaObjectId(mediaObjectId, cn));
}
}
/// <summary>
/// Persist the specified media queue item to the data store. The ID of the new item is assigned to
/// <see cref="MediaQueueDto.MediaQueueId"/>.
/// </summary>
/// <param name="mediaQueue">The media queue item to persist to the data store.</param>
internal static void Save(MediaQueueDto mediaQueue)
{
using (SqlConnection cn = SqlDataProvider.GetDbConnection())
{
if (mediaQueue.MediaQueueId == int.MinValue)
{
using (SqlCommand cmd = GetCommandMediaQueueInsert(mediaQueue, cn))
{
cn.Open();
cmd.ExecuteNonQuery();
int id = Convert.ToInt32(cmd.Parameters["@Identity"].Value, System.Globalization.NumberFormatInfo.CurrentInfo);
if (mediaQueue.MediaQueueId != id)
{
mediaQueue.MediaQueueId = id;
}
}
}
else
{
using (SqlCommand cmd = GetCommandMediaQueueUpdate(mediaQueue, cn))
{
cn.Open();
cmd.ExecuteNonQuery();
}
}
}
}
/// <summary>
/// Insert all metadata items from the data store for the specified media object. Assumes no existing metadata record exists
/// that matches the MediaObjectMetadataId value of each metadata item. Each metadata item is inserted and the newly
/// assigned MediaObjectMetadataId value is assigned to the item's MediaObjectMetadataId property.
/// </summary>
/// <param name="mediaObject">The media object for which to insert all metadata items to the data store.</param>
private static void InsertMetadataItems(IGalleryObject mediaObject)
{
// Insert meta data items, if any, into MediaObjectMetadata table.
if (mediaObject.MetadataItems.Count > 0)
{
using (SqlConnection cn = SqlDataProvider.GetDbConnection())
{
using (SqlCommand cmd = GetCommandMediaObjectMetadataInsert(cn))
{
cmd.Parameters["@FKMediaObjectId"].Value = mediaObject.Id;
cn.Open();
foreach (IGalleryObjectMetadataItem metaDataItem in mediaObject.MetadataItems)
{
cmd.Parameters["@MetadataNameIdentifier"].Value = (int)metaDataItem.MetadataItemName;
cmd.Parameters["@Description"].Value = metaDataItem.Description;
cmd.Parameters["@Value"].Value = metaDataItem.Value;
cmd.ExecuteNonQuery();
// Assign newly assigned ID to the metadata ID property.
metaDataItem.MediaObjectMetadataId = Convert.ToInt32(cmd.Parameters["@Identity"].Value, NumberFormatInfo.InvariantInfo);
}
}
}
}
}
private static void PersistToDataStore(IAlbum album)
{
using (SqlConnection cn = SqlDataProvider.GetDbConnection())
{
if (album.IsNew)
{
using (SqlCommand cmd = GetCommandAlbumInsert(album, cn))
{
cn.Open();
cmd.ExecuteNonQuery();
int id = Convert.ToInt32(cmd.Parameters["@Identity"].Value, System.Globalization.NumberFormatInfo.CurrentInfo);
if (album.Id != id)
{
album.Id = id;
}
}
}
else
{
using (SqlCommand cmd = GetCommandAlbumUpdate(album, cn))
{
cn.Open();
cmd.ExecuteNonQuery();
}
}
}
}
/// <summary>
/// Persist the specified <paramref name="profile" /> to the data store.
/// </summary>
/// <param name="profile">The profile to persist to the data store.</param>
internal static void Save(IUserProfile profile)
{
using (SqlConnection cn = SqlDataProvider.GetDbConnection())
{
using (SqlCommand cmd = GetCommandUserGalleryProfileSave(cn))
{
cmd.Parameters["@UserName"].Value = profile.UserName;
cn.Open();
foreach (IUserGalleryProfile userGalleryProfile in profile.GalleryProfiles)
{
cmd.Parameters["@GalleryId"].Value = userGalleryProfile.GalleryId;
cmd.Parameters["@SettingName"].Value = ProfileNameShowMediaObjectMetadata;
cmd.Parameters["@SettingValue"].Value = userGalleryProfile.ShowMediaObjectMetadata;
cmd.ExecuteNonQuery();
cmd.Parameters["@SettingName"].Value = ProfileNameEnableUserAlbum;
cmd.Parameters["@SettingValue"].Value = userGalleryProfile.EnableUserAlbum;
cmd.ExecuteNonQuery();
cmd.Parameters["@SettingName"].Value = ProfileNameUserAlbumId;
cmd.Parameters["@SettingValue"].Value = userGalleryProfile.UserAlbumId;
cmd.ExecuteNonQuery();
}
}
}
}
private static void PersistToDataStore(IAppError appError)
{
if (appError.AppErrorId == int.MinValue)
{
using (SqlConnection cn = SqlDataProvider.GetDbConnection())
{
using (SqlCommand cmd = GetCommandErrorInsert(appError, cn))
{
cn.Open();
cmd.ExecuteNonQuery();
int id = Convert.ToInt32(cmd.Parameters["@Identity"].Value, System.Globalization.NumberFormatInfo.CurrentInfo);
if (appError.AppErrorId != id)
{
appError.AppErrorId = id;
}
}
}
}
else
{
throw new DataException("Cannot save a previously existing application error to the data store.");
}
}
/// <summary>
/// Return a collection representing the child media objects contained within the album specified by
/// <paramref name="albumId"/> parameter, optionally including the metadata for each item. If no matching
/// objects are found in the data store, an empty collection is returned.
/// </summary>
/// <param name="albumId">The ID that uniquely identifies the desired album.</param>
/// <param name="includeMetadata">When <c>true</c> include metadata items on the
/// <see cref="MediaObjectDto.MediaObjectMetadata" /> property. When <c>false</c>, the property is null.</param>
/// <returns>
/// Returns a collection of all media objects directly within the album represented by <paramref name="albumId"/>.
/// </returns>
internal static IEnumerable <MediaObjectDto> GetChildGalleryObjectsById(int albumId, bool includeMetadata)
{
List <MediaObjectDto> mediaObjects = new List <MediaObjectDto>();
using (SqlConnection cn = SqlDataProvider.GetDbConnection())
{
using (IDataReader dr = GetCommandChildMediaObjectsById(albumId, cn).ExecuteReader())
{
while (dr.Read())
{
// SQL:
//SELECT
// MediaObjectId, FKAlbumId, Title, HashKey, ThumbnailFilename, ThumbnailWidth, ThumbnailHeight,
// ThumbnailSizeKB, OptimizedFilename, OptimizedWidth, OptimizedHeight, OptimizedSizeKB,
// OriginalFilename, OriginalWidth, OriginalHeight, OriginalSizeKB, ExternalHtmlSource, ExternalType, Seq,
// CreatedBy, DateAdded, LastModifiedBy, DateLastModified, IsPrivate
//FROM [gs_MediaObject]
//WHERE FKAlbumId = @AlbumId
mediaObjects.Add(new MediaObjectDto
{
MediaObjectId = dr.GetInt32(0),
FKAlbumId = dr.GetInt32(1),
Title = dr.GetString(2),
HashKey = dr.GetString(3),
ThumbnailFilename = dr.GetString(4),
ThumbnailWidth = dr.GetInt32(5),
ThumbnailHeight = dr.GetInt32(6),
ThumbnailSizeKB = dr.GetInt32(7),
OptimizedFilename = dr.GetString(8),
OptimizedWidth = dr.GetInt32(9),
OptimizedHeight = dr.GetInt32(10),
OptimizedSizeKB = dr.GetInt32(11),
OriginalFilename = dr.GetString(12),
OriginalWidth = dr.GetInt32(13),
OriginalHeight = dr.GetInt32(14),
OriginalSizeKB = dr.GetInt32(15),
ExternalHtmlSource = dr.GetString(16),
ExternalType = dr.GetString(17),
Seq = dr.GetInt32(18),
CreatedBy = dr.GetString(19),
DateAdded = dr.GetDateTime(20),
LastModifiedBy = dr.GetString(21),
DateLastModified = dr.GetDateTime(22),
IsPrivate = dr.GetBoolean(23)
});
}
}
if (includeMetadata)
{
foreach (MediaObjectDto moDto in mediaObjects)
{
moDto.MediaObjectMetadata = new HashSet <MediaObjectMetadataDto>(MediaObject.GetMetadataItemsByMediaObjectId(moDto.MediaObjectId, cn));
}
}
}
return(mediaObjects);
}
/// <summary>
/// Persist the current application settings to the data store.
/// </summary>
/// <param name="appSetting">An instance of <see cref="IAppSetting" /> to persist to the data store.</param>
internal static void SaveAppSetting(IAppSetting appSetting)
{
using (SqlConnection cn = SqlDataProvider.GetDbConnection())
{
using (SqlCommand cmd = GetCommandAppSettingUpdate(cn))
{
Type asType = appSetting.GetType();
// Specify the list of properties we want to save.
string[] propertiesToSave = new[] { "MediaObjectDownloadBufferSize", "EncryptMediaObjectUrlOnClient", "EncryptionKey",
"JQueryScriptPath", "JQueryUiScriptPath", "MembershipProviderName", "RoleProviderName", "ProductKey", "EnableCache",
"AllowGalleryAdminToManageUsersAndRoles", "AllowGalleryAdminToViewAllUsersAndRoles", "MaxNumberErrorItems" };
string boolType = typeof(bool).ToString();
string intType = typeof(int).ToString();
string stringType = typeof(string).ToString();
cn.Open();
foreach (PropertyInfo prop in asType.GetProperties())
{
if (prop.PropertyType.FullName == null)
{
continue;
}
if (Array.IndexOf(propertiesToSave, prop.Name) >= 0)
{
// This is one of the properties we want to save.
string propValue;
if (prop.PropertyType.FullName.Equals(boolType))
{
propValue = Convert.ToBoolean(prop.GetValue(appSetting, null), CultureInfo.InvariantCulture).ToString(CultureInfo.InvariantCulture);
}
else if (prop.PropertyType.FullName.Equals(intType))
{
propValue = Convert.ToInt32(prop.GetValue(appSetting, null), CultureInfo.InvariantCulture).ToString(CultureInfo.InvariantCulture);
}
else if (prop.PropertyType.FullName.Equals(stringType))
{
propValue = Convert.ToString(prop.GetValue(appSetting, null), CultureInfo.InvariantCulture);
}
else
{
propValue = prop.GetValue(appSetting, null).ToString();
}
// Update the item.
cmd.Parameters["@SettingValue"].Value = propValue;
cmd.Parameters["@SettingName"].Value = prop.Name;
cmd.ExecuteNonQuery();
}
}
}
}
}
private static SqlCommand GetCommandMediaObjectUpdate(IGalleryObject mediaObject)
{
DataStore dataStoreConfig = ConfigManager.GetGalleryServerProConfigSection().DataStore;
SqlCommand cmd = new SqlCommand(Util.GetSqlName("gs_MediaObjectUpdate"), SqlDataProvider.GetDbConnection());
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@MediaObjectId", SqlDbType.Int, 0, "MediaObjectId"));
cmd.Parameters.Add(new SqlParameter("@HashKey", SqlDbType.Char, dataStoreConfig.MediaObjectHashKeyLength, "HashKey"));
cmd.Parameters.Add(new SqlParameter("@FKAlbumId", SqlDbType.Int, 0, "FKAlbumId"));
cmd.Parameters.Add(new SqlParameter("@ThumbnailFilename", SqlDbType.NVarChar, dataStoreConfig.MediaObjectFileNameLength, "ThumbnailFilename"));
cmd.Parameters.Add(new SqlParameter("@ThumbnailWidth", SqlDbType.Int, 0, "ThumbnailWidth"));
cmd.Parameters.Add(new SqlParameter("@ThumbnailHeight", SqlDbType.Int, 0, "ThumbnailHeight"));
cmd.Parameters.Add(new SqlParameter("@ThumbnailSizeKB", SqlDbType.Int, 0, "ThumbnailSizeKB"));
cmd.Parameters.Add(new SqlParameter("@OptimizedFilename", SqlDbType.NVarChar, dataStoreConfig.MediaObjectFileNameLength, "OptimizedFilename"));
cmd.Parameters.Add(new SqlParameter("@OptimizedWidth", SqlDbType.Int, 0, "OptimizedWidth"));
cmd.Parameters.Add(new SqlParameter("@OptimizedHeight", SqlDbType.Int, 0, "OptimizedHeight"));
cmd.Parameters.Add(new SqlParameter("@OptimizedSizeKB", SqlDbType.Int, 0, "OptimizedSizeKB"));
cmd.Parameters.Add(new SqlParameter("@OriginalFilename", SqlDbType.NVarChar, dataStoreConfig.MediaObjectFileNameLength, "OriginalFilename"));
cmd.Parameters.Add(new SqlParameter("@OriginalWidth", SqlDbType.Int, 0, "OriginalWidth"));
cmd.Parameters.Add(new SqlParameter("@OriginalHeight", SqlDbType.Int, 0, "OriginalHeight"));
cmd.Parameters.Add(new SqlParameter("@OriginalSizeKB", SqlDbType.Int, 0, "OriginalSizeKB"));
cmd.Parameters.Add(new SqlParameter("@ExternalHtmlSource", SqlDbType.NVarChar, dataStoreConfig.MediaObjectExternalHtmlSourceLength, "ExternalHtmlSource"));
cmd.Parameters.Add(new SqlParameter("@ExternalType", SqlDbType.NVarChar, dataStoreConfig.MediaObjectExternalTypeLength, "ExternalType"));
cmd.Parameters.Add(new SqlParameter("@Title", SqlDbType.NVarChar, dataStoreConfig.MediaObjectTitleLength, "Title"));
cmd.Parameters.Add(new SqlParameter("@Seq", SqlDbType.Int, 0, "Seq"));
cmd.Parameters.Add(new SqlParameter("@LastModifiedBy", SqlDbType.NVarChar, dataStoreConfig.LastModifiedByLength, "LastModifiedBy"));
cmd.Parameters.Add(new SqlParameter("@DateLastModified", SqlDbType.DateTime, 0, "DateLastModified"));
cmd.Parameters.Add(new SqlParameter("@IsPrivate", SqlDbType.Bit, 0, "IsPrivate"));
// Not specifying CreatedBy or DateAdded because those should only get populated during the INSERT.
cmd.Parameters["@MediaObjectId"].Value = mediaObject.Id;
cmd.Parameters["@HashKey"].Value = mediaObject.Hashkey;
cmd.Parameters["@FKAlbumId"].Value = mediaObject.Parent.Id;
cmd.Parameters["@ThumbnailFilename"].Value = mediaObject.Thumbnail.FileName;
cmd.Parameters["@ThumbnailWidth"].Value = mediaObject.Thumbnail.Width;
cmd.Parameters["@ThumbnailHeight"].Value = mediaObject.Thumbnail.Height;
cmd.Parameters["@ThumbnailSizeKB"].Value = mediaObject.Thumbnail.FileSizeKB;
cmd.Parameters["@OptimizedFilename"].Value = mediaObject.Optimized.FileName;
cmd.Parameters["@OptimizedWidth"].Value = mediaObject.Optimized.Width;
cmd.Parameters["@OptimizedHeight"].Value = mediaObject.Optimized.Height;
cmd.Parameters["@OptimizedSizeKB"].Value = mediaObject.Optimized.FileSizeKB;
cmd.Parameters["@OriginalFilename"].Value = mediaObject.Original.FileName;
cmd.Parameters["@OriginalWidth"].Value = mediaObject.Original.Width;
cmd.Parameters["@OriginalHeight"].Value = mediaObject.Original.Height;
cmd.Parameters["@OriginalSizeKB"].Value = mediaObject.Original.FileSizeKB;
cmd.Parameters["@ExternalHtmlSource"].Value = mediaObject.Original.ExternalHtmlSource;
cmd.Parameters["@ExternalType"].Value = mediaObject.Original.ExternalType;
cmd.Parameters["@Title"].Value = mediaObject.Title;
cmd.Parameters["@Seq"].Value = mediaObject.Sequence;
cmd.Parameters["@LastModifiedBy"].Value = mediaObject.LastModifiedByUserName;
cmd.Parameters["@DateLastModified"].Value = mediaObject.DateLastModified;
cmd.Parameters["@IsPrivate"].Value = mediaObject.IsPrivate;
return(cmd);
}
/// <summary>
/// Return the media object for the specified <paramref name="mediaObjectId" />, optionally including the metadata
/// for each item. Returns null if no matching object is found in the data store.
/// </summary>
/// <param name="mediaObjectId">The ID that uniquely identifies the desired media object.</param>
/// <param name="includeMetadata">When <c>true</c> include metadata items on the
/// <see cref="MediaObjectDto.MediaObjectMetadata" /> property. When <c>false</c>, the property is null.</param>
/// <returns>Returns an instance of <see cref="MediaObjectDto" />, or null if no matching object is found.</returns>
public static MediaObjectDto GetMediaObjectById(int mediaObjectId, bool includeMetadata)
{
MediaObjectDto moDto = null;
using (SqlConnection cn = SqlDataProvider.GetDbConnection())
{
using (IDataReader dr = GetCommandMediaObjectSelectById(mediaObjectId, cn).ExecuteReader(CommandBehavior.SingleRow))
{
while (dr.Read())
{
// SQL:
//SELECT
// MediaObjectId, FKAlbumId, Title, HashKey, ThumbnailFilename, ThumbnailWidth, ThumbnailHeight,
// ThumbnailSizeKB, OptimizedFilename, OptimizedWidth, OptimizedHeight, OptimizedSizeKB,
// OriginalFilename, OriginalWidth, OriginalHeight, OriginalSizeKB, ExternalHtmlSource, ExternalType, Seq,
// CreatedBy, DateAdded, LastModifiedBy, DateLastModified, IsPrivate
//FROM [gs_MediaObject]
//WHERE MediaObjectId = @MediaObjectId
moDto = new MediaObjectDto
{
MediaObjectId = dr.GetInt32(0),
FKAlbumId = dr.GetInt32(1),
Title = dr.GetString(2),
HashKey = dr.GetString(3),
ThumbnailFilename = dr.GetString(4),
ThumbnailWidth = dr.GetInt32(5),
ThumbnailHeight = dr.GetInt32(6),
ThumbnailSizeKB = dr.GetInt32(7),
OptimizedFilename = dr.GetString(8),
OptimizedWidth = dr.GetInt32(9),
OptimizedHeight = dr.GetInt32(10),
OptimizedSizeKB = dr.GetInt32(11),
OriginalFilename = dr.GetString(12),
OriginalWidth = dr.GetInt32(13),
OriginalHeight = dr.GetInt32(14),
OriginalSizeKB = dr.GetInt32(15),
ExternalHtmlSource = dr.GetString(16),
ExternalType = dr.GetString(17),
Seq = dr.GetInt32(18),
CreatedBy = dr.GetString(19),
DateAdded = dr.GetDateTime(20),
LastModifiedBy = dr.GetString(21),
DateLastModified = dr.GetDateTime(22),
IsPrivate = dr.GetBoolean(23)
};
}
}
if (includeMetadata && (moDto != null))
{
moDto.MediaObjectMetadata = new HashSet <MediaObjectMetadataDto>(MediaObject.GetMetadataItemsByMediaObjectId(moDto.MediaObjectId, cn));
}
}
return(moDto);
}
private static SqlCommand GetCommandAlbumUpdate(IAlbum album)
{
DataStore dataStoreConfig = ConfigManager.GetGalleryServerProConfigSection().DataStore;
SqlCommand cmd = new SqlCommand(Util.GetSqlName("gs_AlbumUpdate"), SqlDataProvider.GetDbConnection());
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@AlbumId", SqlDbType.Int, 0, "AlbumId"));
cmd.Parameters.Add(new SqlParameter("@AlbumParentId", SqlDbType.Int, 0, "AlbumParentId"));
cmd.Parameters.Add(new SqlParameter("@Title", SqlDbType.NVarChar, dataStoreConfig.AlbumTitleLength, "Title"));
cmd.Parameters.Add(new SqlParameter("@DirectoryName", SqlDbType.NVarChar, dataStoreConfig.AlbumDirectoryNameLength, "DirectoryName"));
cmd.Parameters.Add(new SqlParameter("@Summary", SqlDbType.NVarChar, dataStoreConfig.AlbumSummaryLength, "Summary"));
cmd.Parameters.Add(new SqlParameter("@ThumbnailMediaObjectId", SqlDbType.Int, 0, "ThumbnailMediaObjectId"));
cmd.Parameters.Add(new SqlParameter("@Seq", SqlDbType.Int, 0, "Seq"));
cmd.Parameters.Add(new SqlParameter("@DateStart", SqlDbType.DateTime, 0, "DateStart"));
cmd.Parameters.Add(new SqlParameter("@DateEnd", SqlDbType.DateTime, 0, "DateEnd"));
cmd.Parameters.Add(new SqlParameter("@LastModifiedBy", SqlDbType.NVarChar, dataStoreConfig.LastModifiedByLength, "LastModifiedBy"));
cmd.Parameters.Add(new SqlParameter("@DateLastModified", SqlDbType.DateTime, 0, "DateLastModified"));
cmd.Parameters.Add(new SqlParameter("@OwnedBy", SqlDbType.NVarChar, dataStoreConfig.OwnedByLength, "OwnedBy"));
cmd.Parameters.Add(new SqlParameter("@OwnerRoleName", SqlDbType.NVarChar, dataStoreConfig.OwnerRoleNameLength, "OwnerRoleName"));
cmd.Parameters.Add(new SqlParameter("@IsPrivate", SqlDbType.Bit, 0, "IsPrivate"));
cmd.Parameters["@AlbumId"].Value = album.Id;
cmd.Parameters["@AlbumParentId"].Value = album.Parent.Id;
cmd.Parameters["@Title"].Value = album.Title;
cmd.Parameters["@DirectoryName"].Value = album.DirectoryName;
cmd.Parameters["@Summary"].Value = album.Summary;
cmd.Parameters["@ThumbnailMediaObjectId"].Value = album.ThumbnailMediaObjectId;
cmd.Parameters["@Seq"].Value = album.Sequence;
if (album.DateStart > DateTime.MinValue)
{
cmd.Parameters["@DateStart"].Value = album.DateStart;
}
else
{
cmd.Parameters["@DateStart"].Value = DBNull.Value;
}
if (album.DateEnd > DateTime.MinValue)
{
cmd.Parameters["@DateEnd"].Value = album.DateEnd;
}
else
{
cmd.Parameters["@DateEnd"].Value = DBNull.Value;
}
cmd.Parameters["@LastModifiedBy"].Value = album.LastModifiedByUserName;
cmd.Parameters["@DateLastModified"].Value = album.DateLastModified;
cmd.Parameters["@OwnedBy"].Value = album.OwnerUserName;
cmd.Parameters["@OwnerRoleName"].Value = album.OwnerRoleName;
cmd.Parameters["@IsPrivate"].Value = album.IsPrivate;
return(cmd);
}
private static SqlCommand GetCommandGalleriesSelect()
{
SqlCommand cmd = new SqlCommand(Util.GetSqlName("gs_GallerySelect"), SqlDataProvider.GetDbConnection());
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection.Open();
return(cmd);
}
private static void DeleteFromDataStore(IAlbum album)
{
using (SqlConnection cn = SqlDataProvider.GetDbConnection())
{
using (SqlCommand cmd = GetCommandAlbumDelete(album.Id, cn))
{
cn.Open();
cmd.ExecuteNonQuery();
}
}
}
private static SqlCommand GetCommandErrorDeleteAll()
{
SqlCommand cmd = new SqlCommand(Util.GetSqlName("gs_AppErrorDeleteAll"), SqlDataProvider.GetDbConnection());
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@GalleryId", SqlDbType.Int, 0, "GalleryId"));
cmd.Parameters["@GalleryId"].Value = ConfigManager.GetGalleryServerProConfigSection().Core.GalleryId;
return(cmd);
}
/// <summary>
/// Permanently delete the specified gallery server role from the data store. The stored procedure deletes the record
/// in the gs_Role table corresponding to this role and also all records in the gs_Role_Album table that reference
/// this role.
/// </summary>
/// <param name="role">An instance of IGalleryServerRole to delete from the data store.</param>
private static void DeleteFromDataStore(IGalleryServerRole role)
{
using (SqlConnection cn = SqlDataProvider.GetDbConnection())
{
using (SqlCommand cmd = GetCommandGalleryServerRoleDelete(role, cn))
{
cn.Open();
cmd.ExecuteNonQuery();
}
}
}
/// <summary>
/// Permanently delete the specified gallery from the data store, including all related records. This action cannot
/// be undone.
/// </summary>
/// <param name="gallery">The <see cref="IGallery" /> to delete from the data store.</param>
internal static void DeleteGallery(IGallery gallery)
{
using (SqlConnection cn = SqlDataProvider.GetDbConnection())
{
using (SqlCommand cmd = GetCommandGalleryDelete(gallery.GalleryId, cn))
{
cn.Open();
cmd.ExecuteNonQuery();
}
}
}
/// <summary>
/// Delete the specified metadata item from the data store. No error occurs if the record does not exist in the data store.
/// </summary>
/// <param name="metaDataItem">The metadata item to delete from the data store.</param>
private static void DeleteMetadataItem(IGalleryObjectMetadataItem metaDataItem)
{
using (SqlConnection cn = SqlDataProvider.GetDbConnection())
{
using (SqlCommand cmd = GetCommandMediaObjectMetadataDelete(metaDataItem.MediaObjectMetadataId, cn))
{
cn.Open();
cmd.ExecuteNonQuery();
}
}
}
/// <summary>
/// Permanently delete the profile records for the specified <paramref name="userName" />.
/// </summary>
/// <param name="userName">The user name that uniquely identifies the user.</param>
internal static void DeleteProfileForUser(string userName)
{
using (SqlConnection cn = SqlDataProvider.GetDbConnection())
{
using (SqlCommand cmd = GetCommandUserGalleryProfileDeleteForUser(userName, cn))
{
cn.Open();
cmd.ExecuteNonQuery();
}
}
}
private static SqlCommand GetCommandErrorDelete(int appErrorId)
{
SqlCommand cmd = new SqlCommand(Util.GetSqlName("gs_AppErrorDelete"), SqlDataProvider.GetDbConnection());
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@AppErrorId", SqlDbType.Int, 0, "AppErrorId"));
cmd.Parameters["@AppErrorId"].Value = appErrorId;
return(cmd);
}
/// <summary>
/// Delete the media queue item from the data store.
/// </summary>
/// <param name="mediaQueue">The media queue item to delete from the data store.</param>
internal static void Delete(MediaQueueDto mediaQueue)
{
using (SqlConnection cn = SqlDataProvider.GetDbConnection())
{
using (SqlCommand cmd = GetCommandMediaQueueDelete(mediaQueue.MediaQueueId, cn))
{
cn.Open();
cmd.ExecuteNonQuery();
}
}
}
/// <summary>
/// Permanently delete the profile records associated with the specified <paramref name="galleryId" />.
/// </summary>
/// <param name="galleryId">The gallery ID.</param>
internal static void DeleteProfilesForGallery(int galleryId)
{
using (SqlConnection cn = SqlDataProvider.GetDbConnection())
{
using (SqlCommand cmd = GetCommandUserGalleryProfileDeleteForGallery(galleryId, cn))
{
cn.Open();
cmd.ExecuteNonQuery();
}
}
}
private static void DeleteFromDataStore(int appErrorId)
{
using (SqlConnection cn = SqlDataProvider.GetDbConnection())
{
using (SqlCommand cmd = GetCommandErrorDelete(appErrorId, cn))
{
cn.Open();
cmd.ExecuteNonQuery();
}
}
}
private static SqlCommand GetCommandMediaObjectDelete(int mediaObjectId)
{
SqlCommand cmd = new SqlCommand(Util.GetSqlName("gs_MediaObjectDelete"), SqlDataProvider.GetDbConnection());
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@MediaObjectId", SqlDbType.Int, 0, "MediaObjectId"));
cmd.Parameters["@MediaObjectId"].Value = mediaObjectId;
return(cmd);
}
/// <summary>
/// Save the list of root album IDs to the data store. The table gs_Role_Album contains one record for each role/album
/// relationship. This procedure adds and deletes records as needed.
/// </summary>
/// <param name="role">The gallery server role containing the list of root Album IDs to persist to the data store.</param>
private static void PersistRoleAlbumRelationshipsToDataStore(IGalleryServerRole role)
{
// Step 1: Copy the list of root album IDs to a new list. We'll be removing items from the list as we process them,
// so we don't want to mess with the actual list attached to the object.
List <int> roleAlbumRelationshipsToPersist = new List <int>();
foreach (int albumId in role.RootAlbumIds)
{
roleAlbumRelationshipsToPersist.Add(albumId);
}
// Step 2: Iterate through each role/album relationship that is stored in the data store. If it is in our list, then
// remove it from the list (see step 4 why). If not, the user must have unchecked it so add it to a list of
// relationships to be deleted.
List <int> roleAlbumRelationshipsToDelete = new List <int>();
foreach (int albumId in GetDataReaderRoleRootAlbums(role.RoleName))
{
if (roleAlbumRelationshipsToPersist.Contains(albumId))
{
roleAlbumRelationshipsToPersist.Remove(albumId);
}
else
{
roleAlbumRelationshipsToDelete.Add(albumId);
}
}
using (SqlConnection cn = SqlDataProvider.GetDbConnection())
{
cn.Open();
// Step 3: Delete the records we accumulated in our list.
using (SqlCommand cmd = GetCommandGalleryServerRoleAlbumDelete(role, cn))
{
foreach (int albumId in roleAlbumRelationshipsToDelete)
{
cmd.Parameters["@AlbumId"].Value = albumId;
cmd.ExecuteNonQuery();
}
}
using (SqlCommand cmd = GetCommandGalleryServerRoleAlbumInsert(role, cn))
{
// Step 4: Any items still left in the roleAlbumRelationshipsToPersist list must be new ones checked by the user. Add them.
foreach (int albumId in roleAlbumRelationshipsToPersist)
{
cmd.Parameters["@AlbumId"].Value = albumId;
cmd.ExecuteNonQuery();
}
}
}
}
private static SqlCommand GetCommandRootAlbum(int galleryId)
{
SqlCommand cmd = new SqlCommand(Util.GetSqlName("gs_SelectRootAlbum"), SqlDataProvider.GetDbConnection());
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@GalleryId", SqlDbType.Int));
cmd.Parameters["@GalleryId"].Value = galleryId;
cmd.Connection.Open();
return(cmd);
}
private static SqlCommand GetCommandAlbumDelete(int albumId)
{
SqlCommand cmd = new SqlCommand(Util.GetSqlName("gs_AlbumDelete"), SqlDataProvider.GetDbConnection());
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@AlbumId", SqlDbType.Int, 0, "AlbumId"));
cmd.Parameters["@AlbumId"].Value = albumId;
//SqlParameter prm = cmd.Parameters.Add("@RV", SqlDbType.Int);
//prm.Direction = ParameterDirection.ReturnValue;
return(cmd);
}
/// <summary>
/// Permanently delete the specified media object from the data store. This action cannot
/// be undone. This action also deletes the related metadata items.
/// </summary>
/// <param name="mediaObject">The <see cref="IGalleryObject" /> to delete from the data store.</param>
public static void Delete(IGalleryObject mediaObject)
{
// Related metadata items in the MediaObjectMetadataItem table are deleted
// via a cascade delete rule configured between this table and the MediaObject table.
using (SqlConnection cn = SqlDataProvider.GetDbConnection())
{
using (SqlCommand cmd = GetCommandMediaObjectDelete(mediaObject.Id, cn))
{
cn.Open();
cmd.ExecuteNonQuery();
}
}
}
private static SqlCommand GetCommandSynchronizeSelect(ISynchronizationStatus synchStatus)
{
SqlCommand cmd = new SqlCommand(Util.GetSqlName("gs_SynchronizeSelect"), SqlDataProvider.GetDbConnection());
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@GalleryId", SqlDbType.Int));
cmd.Parameters["@GalleryId"].Value = synchStatus.GalleryId;
cmd.Connection.Open();
return(cmd);
}
private static void PersistRoleToDataStore(IGalleryServerRole role)
{
// The update stored procedure will automatically call the insert stored procedure if it does not
// find a matching role to update.
using (SqlConnection cn = SqlDataProvider.GetDbConnection())
{
using (SqlCommand cmd = GetCommandGalleryServerRoleUpdate(role, cn))
{
cn.Open();
cmd.ExecuteNonQuery();
}
}
}
/// <summary>
/// Gets the version of SQL Server currently being used.
/// </summary>
/// <returns>Returns an enumeration value that indicates the version of SQL Server the web installer is connected to.</returns>
/// <remarks>This function is a nearly identical copy of the one used in the install wizard.</remarks>
internal static SqlVersion GetSqlVersion()
{
SqlVersion version = SqlVersion.Unknown;
using (SqlConnection cn = SqlDataProvider.GetDbConnection())
{
using (SqlCommand cmd = new SqlCommand("SELECT SERVERPROPERTY('productversion')", cn))
{
cn.Open();
using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
while (dr.Read())
{
string sqlVersion = dr.GetString(0);
if (!String.IsNullOrEmpty(sqlVersion))
{
int majorVersion;
if (Int32.TryParse(sqlVersion.Substring(0, sqlVersion.IndexOf(".")), out majorVersion))
{
if (majorVersion < 7)
{
version = SqlVersion.PreSql2000;
}
else if (majorVersion == 8)
{
version = SqlVersion.Sql2000;
}
else if (majorVersion == 9)
{
version = SqlVersion.Sql2005;
}
else if (majorVersion == 10)
{
version = SqlVersion.Sql2008;
}
else if (majorVersion > 10)
{
version = SqlVersion.PostSql2008;
}
}
}
break;
}
dr.Close();
}
}
}
return(version);
}
