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