Export documents from Spider using LinqPad

[#1]

Spider enables the user to handle documents, including document versions in Spider. In some scenarios, the migration of documents to another apllication might be useful, for example if a document management system should be connected.

To export all documents out of Spider, a Linq script has been developed. This will be connected to a Spider application database and will store all available documents to a local (Windows) folder. 

The created structure will follow this schema

DatabaseName > Mandator > ObjectType > ObjectIdentifier > DocumentFolder > FileName

Warning: Use at your own risk. The script is free of use as is, but no warranty is given. No liability is assumed.

To execute the script, the tool LinqPad can be used.

LINQPad is not just for LINQ queries, but any C#/F#/VB expression, statement block or program. Put an end to those hundreds of Visual Studio Console projects cluttering your source folder and join the revolution of LINQPad scripters and incremental developers.  -   https://www.linqpad.net/

Consideration

Please consider and execute the following steps to export the documents:

1. Make sure you have enough local disk space. Depending on the number and the size of documents stored in the Spider database, the exported documents might need plenty of space.
2. Download and install LinqPad. In our test, we've used LinqPad 5, which is free.
   https://www.linqpad.net/Download.aspx
3. Open the script in LinqPad
4. Configure the connection and the script
5. Execute the script and monitor the progress. After hitting the green Play button, the progress will be shown in the results window (at the bottom of the application)
   

   

Configuration

After installing LinqPad and opening the script, please configure to application as follows:

1. Create a connection to the SQL server on which the Spider databases are located. The user should have read permission to the database, updates will not be performed. 
2. Set the Language to "C# program".
3. Select the database you'd like to export documents from. All Spider 6.4 application databases are supported. If you'd like to download documents from multiple databases, you have to execute the script for each database.
4. Type in the local root folder for the exported documents.

The Script

Download the script here: https://community.flexera.com/xtqzp85664/attachments/xtqzp85664/Spider-Knowledge/181.7/2/Export Spider documents.zip 

Or copy the source code to LinqPad from here:

void Main()
{
/*************************************************************************
This script exports all documents of a Spider database
*************************************************************************

This script will create a directory structure as follows per document:
Database > Mandator > ObjectType > ObjectIdentifier > DocumentFolder > FileName

The file name will be formed from the document name, followed by the 
version in square brackets. Illegal characters will be replaced.

/* SETTINGS
*************************************************************************/

// Folder where the files should be placed
string baseFolder = @"D:\_Temp\Documents Export";

// Illegal characters in document names will be replaced with this char
char replaceInvalidCharsWith = '_';

/*************************************************************************/

var context = this;

var bar = new Util.ProgressBar("").Dump();
var nOfNText = new DumpContainer().Dump();
var spacer = new DumpContainer().Dump();
var fileNameText = new DumpContainer().Dump();
var saveToText = new DumpContainer().Dump();

nOfNText.Content = "Determing meta data...";
Thread.Sleep(1000);

var metaQuery = 
	from doc in Document_base_M
	join mandator in Core_Mandator_01 on doc.MandatorID equals mandator.ID
	join sco in SCObjects on doc.SCObjectID equals sco.ID
	join docVersion in DocumentVersions on doc.ID equals docVersion.DocumentID
	join store in Storages on docVersion.StorageID equals store.ID
	join folder in Folders on doc.FolderID equals folder.ID
	select new MetaData() {
		BaseFolder = baseFolder,
		MandatorID = doc.MandatorID,
		Mandator = doc.Mandator,
		DocumentID = doc.ID,
		StorageID = store.ID,
		SCObjectName = sco.Name,
		Folder = doc.Folder.Replace("/", @"\"),
		ContractIdentifier = doc.ObjectIdentifier,
		FileName = doc.Name + " [v" + docVersion.Version + "]" + docVersion.FileType
	};

float i = 1;
float all = metaQuery.Count();

string invalid = new string(Path.GetInvalidFileNameChars()) + new string(Path.GetInvalidPathChars());
string dbName = this.Connection.Database;

foreach(var meta in metaQuery){
	
	foreach (char c in invalid)
	{
		meta.Mandator = meta.Mandator.Replace(c, replaceInvalidCharsWith);
		meta.SCObjectName = meta.SCObjectName.Replace(c, replaceInvalidCharsWith);
		if(!string.IsNullOrEmpty(meta.Folder) && c != '\\')
			meta.Folder = meta.Folder.Replace(c, replaceInvalidCharsWith);
		meta.ContractIdentifier = meta.ContractIdentifier.Replace(c, replaceInvalidCharsWith);
		meta.FileName = meta.FileName.Replace(c, replaceInvalidCharsWith);
		dbName = dbName.Replace(c, replaceInvalidCharsWith);
	}
	
	bar.Percent = (int)((i/all)*100);
	bar.Fraction = i/all;
	
	nOfNText.Content = i + " / " + all;
	fileNameText.Content = "File name: " + meta.FileName;
	saveToText.Content = "Save to: " + meta.BaseFolder + @"\" + dbName + @"\" + meta.Mandator + @"\" + meta.SCObjectName + @"\" + meta.ContractIdentifier + @"\" + meta.Folder;
		
	var filePath = meta.BaseFolder + @"\"  + dbName + @"\" + meta.Mandator + @"\" + meta.SCObjectName + @"\" + meta.ContractIdentifier + @"\" + meta.Folder + @"\" + meta.FileName;
	
	if(!File.Exists(filePath)){
		Directory.CreateDirectory(meta.BaseFolder);
		Directory.CreateDirectory(meta.BaseFolder + @"\" + dbName);
		Directory.CreateDirectory(meta.BaseFolder + @"\" + dbName + @"\" + meta.Mandator);
		Directory.CreateDirectory(meta.BaseFolder + @"\" + dbName + @"\" + meta.Mandator + @"\" + meta.SCObjectName);
		Directory.CreateDirectory(meta.BaseFolder + @"\" + dbName + @"\" + meta.Mandator + @"\" + meta.SCObjectName + @"\" + meta.ContractIdentifier);
		Directory.CreateDirectory(meta.BaseFolder + @"\" + dbName + @"\" + meta.Mandator + @"\" + meta.SCObjectName + @"\" + meta.ContractIdentifier + @"\" + meta.Folder);
		
		var dataQuery = 
			from store in Storages 
			where store.ID == meta.StorageID
			select store.Data;
		
		var result = dataQuery.Single();
		var bytes = result.ToArray();
	
		File.WriteAllBytes(filePath, bytes);
	}
	
	i++;
}

nOfNText.Content = "Finished";
fileNameText.Content = all + " documents have been downloaded to";
saveToText.Content = baseFolder;
}

class MetaData {
public string BaseFolder { get; set; }
public int MandatorID { get; set; }
public string Mandator { get; set; }
public int DocumentID { get; set; }
public int StorageID { get; set; }
public string SCObjectName { get; set; }
public string Folder { get; set; }
public string ContractIdentifier { get; set; }
public string FileName { get; set; }
}