Wiki

Case Status Kiln
Log In

Wiki

 
Procedures»Adding File Management to a sy…
  • RSS Feed

Last modified on 7/16/2015 6:27 PM by User.

Tags:

Adding File Management to a system

Create schema similar to one of these:

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
create table FileCollections(
	FileCollectionId int
		not null
		constraint FileCollectionsPk primary key
)
go

create table Files(
	FileId int
		not null
		constraint FilesPk primary key,
	FileCollectionId int
		not null
		constraint FilesFileCollectionIdFk references FileCollections,
	FileName varchar( 100 )
		not null,
	ContentType varchar( 100 )
		not null,
	Contents varbinary( max )
		not null,
	UploadedDateAndTime datetime2
		not null,
	constraint FilesFileCollectionIdAndFileNameUnique unique ( FileCollectionId, FileName )
)
go

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
CREATE TABLE FileCollections(
    FileCollectionId int NOT NULL CONSTRAINT FileCollectionsPk PRIMARY KEY( FileCollectionId )
)
GO

CREATE TABLE Files(
    FileId int NOT NULL CONSTRAINT FilesPk PRIMARY KEY( FileId ),
    FileRowGuid UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL UNIQUE DEFAULT NEWID(),
    FileCollectionId int NOT NULL CONSTRAINT FilesFkToFileCollections REFERENCES FileCollections( FileCollectionId ),
    -- Windows' maximum length for an entire path is 260 characters. http://msdn.microsoft.com/en-us/library/windows/desktop/aa365247.aspx
    FileName varchar( 256 ) NOT NULL,
    ContentType varchar( 100 ) NOT NULL,
    Contents varbinary( MAX ) FILESTREAM NOT NULL,
    UploadedDate datetime2 NOT NULL
)
GO

Then, add a class called BlobFileManagement to your Library/Configuration/Providers folder and implement SystemBlobFileManagementProvider. When you do this, you want to make sure you don't always return the contents of the file (except in the one method that asks for the contents). To do this, create a custom retrieval like this in Development.xml:

1:
2:
3:
4:
5:
6:
7:
<query name="Files">
	<selectFromClause>SELECT FileId, FileName, ContentType, UploadedDateAndTime FROM Files</selectFromClause>
	<postSelectFromClauses>
		<postSelectFromClause name="MatchingId">WHERE FileId = @fileId</postSelectFromClause>
		<postSelectFromClause name="LinkedToFileCollection">WHERE FileCollectionId = @fileCollectionId</postSelectFromClause>
	</postSelectFromClauses>
</query>

Finally, a sample implementation of BlobFileManagement looks like this:

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
internal class BlobFileManagement: SystemBlobFileManagementProvider {
	BlobFile SystemBlobFileManagementProvider.GetFile( int fileId ) {
		return getEwlFile( FilesRetrieval.GetRowsMatchingId( fileId ).Single() );
	}

	List<BlobFile> SystemBlobFileManagementProvider.GetFilesLinkedToFileCollection( int fileCollectionId ) {
		return FilesRetrieval.GetRowsLinkedToFileCollection( fileCollectionId ).Select( getEwlFile ).ToList();
	}

	private BlobFile getEwlFile( FilesRetrieval.Row file ) {
		return new BlobFile( file.FileId, file.FileName, file.ContentType, file.UploadedDateAndTime.ToLocalTime() );
	}

	int SystemBlobFileManagementProvider.InsertFileCollection() {
		var id = MainSequence.GetNextValue();
		FileCollectionsModification.InsertRow( id );
		return id;
	}

	int SystemBlobFileManagementProvider.InsertFile( int fileCollectionId, string fileName, byte[] contents, string contentType ) {
		var id = MainSequence.GetNextValue();
		FilesModification.InsertRow( id, fileCollectionId, fileName, contentType, contents, DateTime.UtcNow );
		return id;
	}

	void SystemBlobFileManagementProvider.UpdateFile( int fileId, string fileName, byte[] contents, string contentType ) {
		var update = FilesModification.CreateForUpdate( new FilesTableEqualityConditions.FileId( fileId ) );
		update.FileName = fileName;
		update.ContentType = contentType;
		update.Contents = contents;
		update.UploadedDateAndTime = DateTime.UtcNow;
		update.Execute();
	}

	void SystemBlobFileManagementProvider.DeleteFile( int fileId ) {
		FilesModification.DeleteRows( new FilesTableEqualityConditions.FileId( fileId ) );
	}

	void SystemBlobFileManagementProvider.DeleteFilesLinkedToFileCollection( int fileCollectionId ) {
		FilesModification.DeleteRows( new FilesTableEqualityConditions.FileCollectionId( fileCollectionId ) );
	}

	byte[] SystemBlobFileManagementProvider.GetFileContents( int fileId ) {
		return FilesTableRetrieval.GetRows( new FilesTableEqualityConditions.FileId( fileId ) ).Single().Contents;
	}
}