/// <summary> /// Imports the data from <paramref name="galleryData"/> to the SQL Server database. /// </summary> /// <param name="galleryData">An XML-formatted string containing the gallery data. The data must conform to the schema defined in the project for /// the data provider's implementation.</param> /// <param name="importMembershipData">if set to <c>true</c> import membership data.</param> /// <param name="importGalleryData">if set to <c>true</c> import gallery data.</param> internal static void ImportData(string galleryData, bool importMembershipData, bool importGalleryData) { if (String.IsNullOrEmpty(galleryData)) { throw new ArgumentNullException("galleryData"); } using (DataSet ds = GenerateDataSet(galleryData)) { SqlConnection cn = SqlDataProvider.GetDbConnection(); SqlTransaction tran = null; try { cn.Open(); using (tran = cn.BeginTransaction()) { ClearData(cn, tran, importMembershipData, importGalleryData); if (importMembershipData) { string[] aspnet_TableNames = new string[] { "aspnet_Applications", "aspnet_Membership", "aspnet_Profile", "aspnet_Roles", "aspnet_Users", "aspnet_UsersInRoles" }; // SqlBulkCopy requires SQL permissions equivalent to that provided in the db_ddladmin or db_owner roles. using (SqlBulkCopy bulkCopy = new SqlBulkCopy(cn, SqlBulkCopyOptions.KeepIdentity, tran)) { foreach (string tableName in aspnet_TableNames) { bulkCopy.DestinationTableName = string.Concat(Util.SqlServerSchema, tableName); // Write from the source to the destination. using (IDataReader dr = ds.Tables[tableName].CreateDataReader()) { try { bulkCopy.WriteToServer(dr); } catch (Exception ex) { // Add a little info to exception and re-throw. if (!ex.Data.Contains("SQL Bulk copy error")) { ex.Data.Add("SQL Bulk copy error", String.Format("Error occurred while importing table {0}.", tableName)); } throw; } } } } } if (importGalleryData) { string[] gs_TableNames = new string[] { "gs_Gallery", "gs_Album", "gs_Role_Album", "gs_MediaObject", "gs_MediaObjectMetadata", "gs_Role", "gs_AppError" }; // SqlBulkCopy requires SQL permissions equivalent to that provided in the db_ddladmin or db_owner roles. using (SqlBulkCopy bulkCopy = new SqlBulkCopy(cn, SqlBulkCopyOptions.KeepIdentity, tran)) { foreach (string tableName in gs_TableNames) { bulkCopy.DestinationTableName = string.Concat(Util.SqlServerSchema, tableName); // Write from the source to the destination. using (IDataReader dr = ds.Tables[tableName].CreateDataReader()) { try { bulkCopy.WriteToServer(dr); } catch (Exception ex) { // Add a little info to exception and re-throw. if (!ex.Data.Contains("SQL Bulk copy error")) { ex.Data.Add("SQL Bulk copy error", String.Format("Error occurred while importing table {0}.", tableName)); } throw; } } } } } tran.Commit(); } cn.Close(); } catch { if (tran != null) { tran.Rollback(); } throw; } finally { if (cn != null) { cn.Dispose(); } } } }
private static SqlCommand GetCommandMediaObjectSelectHashKeys() { SqlCommand cmd = new SqlCommand(Util.GetSqlName("gs_MediaObjectSelectHashKeys"), SqlDataProvider.GetDbConnection()); cmd.CommandType = CommandType.StoredProcedure; cmd.Connection.Open(); return(cmd); }
private static SqlCommand GetCommandMimeTypeGallerySelect() { SqlCommand cmd = new SqlCommand(Util.GetSqlName("gs_MimeTypeGallerySelect"), SqlDataProvider.GetDbConnection()); cmd.CommandType = CommandType.StoredProcedure; cmd.Connection.Open(); return(cmd); }
/// <summary> /// Persist each each metadata item that has HasChanges = true to the data store. If all items are marked for updating /// (mediaObject.RegenerateMetadataOnSave = true), then all metadata items are deleted from the data store and then inserted based /// on the current metadata items. If one or more items has HasChanges = false, then each item with HasChanges = true is /// processed according to the following rules: (1) If the metadata value is null or an empty string, it is deleted from the /// data store and removed from the MetadataItems collection. (2) If the item's MediaObjectMetadataId = int.MinValue, the /// item is assumed to be new and is inserted. (3) Any item not falling into the previous two categories, but HasChanges = true, /// is assumed to be pre-existing and an update stored procedure is executed. /// </summary> /// <param name="mediaObject">The media object for which to update metadata items in the data store.</param> private static void UpdateMetadataItems(IGalleryObject mediaObject) { if (mediaObject.ExtractMetadataOnSave) { // User wants to replace all metadata items. Delete them all from the data store, then insert the ones we have. DeleteMetadataItems(mediaObject); InsertMetadataItems(mediaObject); } else { IGalleryObjectMetadataItemCollection metadataItemsToSave = mediaObject.MetadataItems.GetItemsToSave(); if (metadataItemsToSave.Count == 0) { return; // Nothing to save } // There is at least one item to persist to the data store. using (SqlConnection cn = SqlDataProvider.GetDbConnection()) { SqlCommand cmdUpdate = null; SqlCommand cmdInsert = null; try { cmdUpdate = GetCommandMediaObjectMetadataUpdate(cn); cmdUpdate.Parameters["@FKMediaObjectId"].Value = mediaObject.Id; cmdInsert = GetCommandMediaObjectMetadataInsert(cn); cmdInsert.Parameters["@FKMediaObjectId"].Value = mediaObject.Id; cn.Open(); foreach (IGalleryObjectMetadataItem metaDataItem in metadataItemsToSave) { if (String.IsNullOrEmpty(metaDataItem.Value)) { // There is no value, so let's delete this item. DeleteMetadataItem(metaDataItem); // Remove it from the collection. mediaObject.MetadataItems.Remove(metaDataItem); } else if (metaDataItem.MediaObjectMetadataId == int.MinValue) { // Insert the item. cmdInsert.Parameters["@MetadataNameIdentifier"].Value = (int)metaDataItem.MetadataItemName; cmdInsert.Parameters["@Description"].Value = metaDataItem.Description; cmdInsert.Parameters["@Value"].Value = metaDataItem.Value; cmdInsert.ExecuteNonQuery(); // Assign newly assigned ID to the metadata ID property. metaDataItem.MediaObjectMetadataId = Convert.ToInt32(cmdInsert.Parameters["@Identity"].Value, NumberFormatInfo.InvariantInfo); } else { // Update the item. cmdUpdate.Parameters["@MetadataNameIdentifier"].Value = (int)metaDataItem.MetadataItemName; cmdUpdate.Parameters["@Description"].Value = metaDataItem.Description; cmdUpdate.Parameters["@Value"].Value = metaDataItem.Value; cmdUpdate.Parameters["@MediaObjectMetadataId"].Value = metaDataItem.MediaObjectMetadataId; cmdUpdate.ExecuteNonQuery(); } } } finally { if (cmdUpdate != null) { cmdUpdate.Dispose(); } if (cmdInsert != null) { cmdInsert.Dispose(); } } } } }
/// <summary> /// Exports the gallery data to an XML-formatted string. /// </summary> /// <param name="exportMembershipData">Specifies whether to include membership data in the output.</param> /// <param name="exportGalleryData">Specifies whether to include gallery data in the output.</param> /// <returns>Returns an XML-formatted string representation of the data in the database.</returns> internal static string ExportData(bool exportMembershipData, bool exportGalleryData) { using (DataSet ds = new DataSet("GalleryServerData")) { ds.Locale = CultureInfo.InvariantCulture; System.Reflection.Assembly asm = System.Reflection.Assembly.GetExecutingAssembly(); using (System.IO.Stream stream = asm.GetManifestResourceStream("GalleryServerPro.Data.SqlServer.GalleryServerProSchema.xml")) { ds.ReadXmlSchema(stream); } using (SqlConnection cn = SqlDataProvider.GetDbConnection()) { if (cn.State == ConnectionState.Closed) { cn.Open(); } if (exportMembershipData) { string[] aspnetTableNames = new string[] { "aspnet_Applications", "aspnet_Membership", "aspnet_Profile", "aspnet_Roles", "aspnet_Users", "aspnet_UsersInRoles" }; using (SqlCommand cmd = new SqlCommand(Util.GetSqlName("gs_ExportMembership"), cn)) { cmd.CommandType = CommandType.StoredProcedure; using (IDataReader dr = cmd.ExecuteReader()) { ds.Load(dr, LoadOption.OverwriteChanges, aspnetTableNames); } } } if (exportGalleryData) { string[] gs_TableNames = new string[] { "gs_Album", "gs_Gallery", "gs_MediaObject", "gs_MediaObjectMetadata", "gs_Role", "gs_Role_Album", "gs_AppError", "gs_AppSetting", "gs_GalleryControlSetting", "gs_GallerySetting", "gs_BrowserTemplate", "gs_MimeType", "gs_MimeTypeGallery", "gs_UserGalleryProfile" }; using (SqlCommand cmd = new SqlCommand(Util.GetSqlName("gs_ExportGalleryData"), cn)) { cmd.CommandType = CommandType.StoredProcedure; using (IDataReader dr = cmd.ExecuteReader()) { ds.Load(dr, LoadOption.OverwriteChanges, gs_TableNames); } } } // We always want to get the schema into the dataset, even when we're not getting the rest of the gallery data. DataTable dbSchema = ds.Tables["gs_SchemaVersion"]; DataRow row = dbSchema.NewRow(); row[0] = Util.GetDataSchemaVersionString(); dbSchema.Rows.Add(row); using (System.IO.StringWriter sw = new System.IO.StringWriter(CultureInfo.InvariantCulture)) { ds.WriteXml(sw, XmlWriteMode.WriteSchema); //ds.WriteXmlSchema(@"D:\GalleryServerProSchema.xml"); // Use to create new schema file after a database change return(sw.ToString()); } } } }