EPiServer – Get all Media Files filtered by Media Class Type and Name

While working in a project for a client, we had to develop a media importer process which had to save several Pdfs and Excel files to the CMS. The files were inside a zip file in conjunction with a json configuration file which details the properties of the files. We could have used the media service EPiServer provides, but unfortunately it does not support content area types so we had to implement our own. I will discuss about this media importer in another post. Going back to the point, while developing this component, we found a problem by trying to avoid duplicated documents (files with the same name). To verify if the file already exists in the CMS we were querying the EPiServer Find index. However, if the json configuration file had the same file twice, it will generate a duplicated document because EPiServer Find takes 4 to 5 seconds to index a new document, and this will cause that the second file with the same name will not be found, even though it was already created in the CMS by the first one. Another scenario was when the importer process took to long to finish and another one started to run at the same time causing race conditions which lead to duplicated documents.

We decided then to query the database using an API provided by EPiServer but unfortunately you cannot use the SearchPageByCriteria API (Only pages) nor the ContentModelUsage API because several files are not even used in the any of the pages or blocks in the CMS, they are displayed as part of a find query. At the end, we decided to use a direct query to the database to get the media files borrowing some code from the 404 Handler for EPiServer. This post, will explain how we did it and how to use it. Just as a reminder, we do not recommend to use queries directly to the database due to performance (EPiServer APIs have their own cache implementation) and maintenance (Database structure may change) issues.

epi_server_logo_detail

So, lets begin !!!!

First, we will create the media type for the files we are going to save.


namespace Data.Models.Media
{
    [ContentType(GUID = "3014e7d1-53f3-49ca-94ff-29d3da5101a4")]
    [MediaDescriptor(ExtensionString = "pdf,xlsx,xls,csv,zip")]
    public class DocumentMetadata : MediaData
    {
        [CultureSpecific]
        [Display(GroupName = SystemTabNames.Content, Order = 100)]
        public virtual string DocumentTitle { get; set; }

        [CultureSpecific]
        [AllowedTypes(typeof(AuthorBlock))]
        [Display(GroupName = SystemTabNames.Content, Order = 200)]
        public virtual ContentArea Author { get; set; }
        
        [CultureSpecific]
        [UIHint(UIHint.Textarea)]
        [Display(GroupName = SystemTabNames.Content, Order = 300)]
        public virtual string Teaser { get; set; }

        [CultureSpecific]
        [UIHint(UIHint.Textarea)]
        [Display(GroupName = SystemTabNames.Content, Order = 400)]
        public virtual string Description { get; set; }

        [CultureSpecific]
        [Display(GroupName = SystemTabNames.Content, Order = 500)]
        public virtual string FileName { get; set; }
        
        [CultureSpecific]
        [Display(GroupName = SystemTabNames.Content, Order = 700)]
        public virtual bool ShowInRecentDocs { get; set; }
    }
}

We will also create an author block which is part of the properties we defined in the DocumentMetadata class


namespace Data.Models.Block
{
    [ContentType(GUID = "c1a7bff1-5951-abc2-ba6b-b55aca1f4a7b")]
    public class AuthorBlock : BlockData
    {
        [CultureSpecific]
        [Required]
        [Display(GroupName = SystemTabNames.Content, Order = 200)]
        public virtual string BioName { get; set; }
        
        [CultureSpecific]
        [Display(GroupName = SystemTabNames.Content, Order = 400)]
        public virtual string BioPosition { get; set; }

        [CultureSpecific]
        [Display(GroupName = SystemTabNames.Content, Order = 500)]
        public virtual XhtmlString BioDescription { get; set; }
    }
}

Then, we will add the code that query the database directly and returns a content reference id. Part of this code was borrowed from the 404 handler as mentioned before.


namespace Business.Util
{
    public class CustomDataAccessBaseEx : EPiServer.DataAccess.DataAccessBase
    {
        private Injected<IContentRepository> ContentRepository;

        public CustomDataAccessBaseEx(IDatabaseExecutor handler)
            : base(handler)
        {
            Executor = handler;
        }

        public static CustomDataAccessBaseEx GetWorker()
        {
            return EPiServer.ServiceLocation.ServiceLocator.Current.GetInstance();
        }

        public DataSet ExecuteSql(string sqlCommand, List parameters)
        {
            return Executor.Execute(delegate
            {
                using (var ds = new DataSet())
                {
                    try
                    {
                        var command = CreateCommand(sqlCommand);
                        if (parameters != null)
                        {
                            foreach (var dbDataParameter in parameters)
                            {
                                var parameter = (SqlParameter)dbDataParameter;
                                command.Parameters.Add(parameter);
                            }
                        }
                        command.CommandType = CommandType.Text;
                        CreateDataAdapter(command).Fill(ds);
                    }
                    catch (Exception ex)
                    {
                        Console.WriteLine("An error occured in the ExecuteSQL method. Exception:{ex}", ex.Message);
                    }

                    return ds;
                }
            });

        }

        public int ExecuteScalar(string sqlCommand)
        {
            return Executor.Execute(delegate
            {
                int result;
                try
                {
                    IDbCommand dbCommand = CreateCommand(sqlCommand);
                    dbCommand.CommandType = CommandType.Text;
                    var resultObj = dbCommand.ExecuteScalar();

                    if (resultObj != null)
                    {
                        return (int)resultObj;
                    }

                    return 0;
                }
                catch (Exception ex)
                {
                    result = 0;
                    Console.WriteLine("An error occured in the ExecuteScalar method. Exception:{ex}", ex.Message);
                }
                return result;
            });
        }

        public int GetMediaByTypeAndName(string contentType, string name)
        {
            var sqlCommand = "SELECT DISTINCT(tbw.fkContentID) FROM[dbo].[tblWorkContent] tbw " +
            "INNER JOIN[dbo].[tblContent] tc ON tc.pkID = tbw.fkContentID " +
            "INNER JOIN[dbo].[tblContentType] tct ON tct.pkID = tc.fkContentTypeID " +
            $"WHERE tct.Name = '{contentType}' AND tbw.Name = '{name}'";

            return ExecuteScalar(sqlCommand);
        }
    }
}

The important part of this code is the GetMediaByTypeAndName function, which gets the document based on the document type and its name. It uses inner joins to improve a little bit the performance cost of making the call to the database. Now, with this code, we can execute a query using the example below. Pay special attention to the comments


 // Get access to database
var dataAccess = CustomDataAccessBaseEx.GetWorker();
DocumentMetadata newFile;

// Check if is an old file
var oldFileId = dataAccess.GetMediaByTypeAndName("DocumentMetadata", "DocumentNameTest");

if (oldFileId != 0)
{
    var oldFileReference = new ContentReference(oldFileId);
    newFile = contentRepository.Get(oldFileReference).CreateWritableClone() as DocumentMetadata;
}
else
{
    newFile = contentRepository.GetDefault(SiteDefinition.Current.GlobalAssetsRoot);
}
                            
// Set metadata for new file
newFile.DocumentTitle = "NewDocumentTitle";

And that is all. Now, if you want to find a media file by type (class name) and the name of the file, you can use this method. It is not the most efficient way to do it, but helped to solve our needs and performance wise it is not too expensive. I hope it will help someone and as always keep learning !!!

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s