/// <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); }