Anders G. Nordby

Senior Systems Consultant at CGI

inRiver PIM: Deleting Orphaned Resources

When resource entities are deleted in inRiver PIM, the actual resource is not deleted with it. After a while, you can end up with lots of unused resources in your PIM database. You can safely get rid of these resource files, and I think you should. In the installation of inRiver PIM that I’m working with, we’re using three databases, one being the main inRiver PIM database, one is the resource database, and the last one is for logging. Splitting everything up this was recommended to me, and I’m happy with this setup.

First, I created a view in my resource database:

CREATE VIEW [dbo].[View_OrphanResourceFiles]
AS
SELECT Id
FROM [pim.resource].[dbo].ResourceFile
EXCEPT
SELECT Value AS Id
FROM [pim.inRiver].[dbo].Field
WHERE FieldTypeId = 'ResourceFileId'

Then, in a periodic cleanup job, I use the following C# code to delete the actual resources:

private static void DeleteOrphanedResources()
{
	var connectionString = ConfigurationManager.ConnectionStrings["pim.resource"].ToString();
	const string query = "SELECT Id FROM [View_OrphanResourceFiles]";

	var dataTable = new DataTable();

	using (var connection = new SqlConnection(connectionString))
	{
		connection.Open();
		var command = new SqlCommand(query, connection);

		var da = new SqlDataAdapter(command);
		da.Fill(dataTable);
	}

	if (dataTable.Rows != null && dataTable.Rows.Count > 0)
	{
		EnsurePimConnection(new PimCredentials());

		foreach (DataRow row in dataTable.Rows)
		{
			if (row[0] == null)
				continue;

			int id;
			int.TryParse(row[0].ToString(), out id);
			DeleteResourceFile(id);
		}
	}
}

private static void DeleteResourceFile(int id)
{
	try
	{
		var ok = RemoteManager.UtilityService.DeleteFile(id);
	}
	catch (Exception)
	{
		// There is a known bug in inRiver PIM: exception is always thrown here!
	}

}

private static void EnsurePimConnection(PimCredentials credentials)
{
	if (RemoteManager.Instance != null)
		return;

	RemoteManager.CreateInstance(credentials.Url, credentials.UserName, credentials.Password);
}

// PimCredentials is a simple class that holds the UserName, Password and Url to
// the inRiver PIM instance, and reads these from the Web.config file during the
// class instantiation.

Note the bug in the comment; this has been reported to inRiver.

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: