RIA Services is a relatively new addition to Microsoft’s repertoire of frameworks and services for Application Development; and the understanding of Domain services is the core concept behind it all.
In this article I will explain the architecture that combines a Silverlight Client Application to a RIA services solution. The solution will connect to a SQL Database back-end that uses SQL Server FileStream types for file storage.
Specifically I will explain how you can transfer files in a Silverlight + RIA Services architecture to and from a SQL Server Database using FileStream Types…
Prerequisites
- How to enable SQL FileStream Types
- Using SQL FileStream Types in your Databases
- MaxRequestLength – Packet sizes: Size isn’t everything
The Basics
To begin with let me explain some of the basic components:
- You have a Silverlight Client Application with XAML pages
- You have Domain Services exposed to the Client Application with servicing methods
- The mid-tier traverses through a communications layer to reach Repository Classes
- Repository Classes are used to connect to the Database to set and retrieve information
With this in mind, the diagram below represents a Request from the Client to the Database Server:
RIA Services – Client to Server Request:
Downloading
Before we consider File Uploading via RIA Services lets consider its easier counter-part: File Downloading via RIA Services! Please take 5 minutes to read my article on File Buffering and Streaming because the concepts used here are exactly the same…
Below is the initial Download Request from the Client Application. The Request passes through a File Handler. This File Handler is the key component for Streaming data between two systems. The Request is pretty straight forward, the File Handler pipes the Request to the Domain Service – which in turn calls our Manager Class and Repository Classes.
The Repository Class is responsible for querying the SQL Database to get the File Stream data from the SQL Table.
Later in this article there is a code-snippet that explains how I achieved this.
File Download – Client to Server Request:
Once the Repository has a reference to the File entity. The Repository passes the Byte[] array data through all of the channels back via the Domain Service to the File Handler. The diagram below is a representation of this process:
note: The RED arrows indicate that Streaming is happening and shows the direction that the streamed data is moving.
File Download (Streamed) – Server to Client Response:
Uploading
Now lets discuss the File Upload counter-part. When I first designed the solution I ran into performance issues. To give you a better understanding I will describe my original design below:
- The XAML page loads a BackgroundWorker to upload packets of data to the File Handler.
- On the ProcessRequest() method of the File Handler I piped the data through the Domain Service
- Which in turn piped the data to the Repository
- The Repository opened a data stream to the Database and flushed the data into this steam.
- The Repository then closes the database connection.
- This repeats for as many upload Packets as necessary.
The performance hit that got me was that the BackgroundWorker’s processing method needed to wait until the File Handler completed its entire process; and this happens after all the steps above have finished – including open and closing the database connection and writing the contents to the FileStream table column.
So an alternate approch was devised…
File uploading via RIA Service is split into 2 parts:
- Transferring the file from the Client Application to the Web Server via the File Handler
- Transferring the file from the Web Server to the Database Server as a SQL FileStream Type
1. File Transfer (Client Application to Web Server)
This first process is identical to the steps explained in the article: File Buffering and Streaming
File Upload (Streamed) – Client to Web Server:
2. File Transfer (Web Server to DB Server)
This diagram is the representation on how the File Handler transfers data packets to the SQL Database via Domain Services:
File Upload (Streamed) – Web Server to DB Server:
We extend what we previously had in the File Handler ProcessRequest() method by adding extra functionality that executes when the last file packet is retrieved and appended to the Web Server file. Below is the entire ProcessRequest() method – you will notice an additional call is made on detection of the “completed” flag to a private method TransferFileToDB().
public void ProcessRequest(HttpContext context) { if (context.Request.QueryString.AllKeys.Contains("attachmentid") && context.Request.QueryString.AllKeys.Contains("filename") && context.Request.QueryString.AllKeys.Contains("uniquefilename")) { // Write to the Upload Folder System.IO.FileInfo fi = new System.IO.FileInfo(@"C:\MyUploads" + context.Request.QueryString["uniquefilename"]); System.IO.FileStream fs; try { if (fi.Exists) { fs = new System.IO.FileStream(fi.FullName , System.IO.FileMode.Append); } else { fs = new System.IO.FileStream(fi.FullName , System.IO.FileMode.OpenOrCreate , System.IO.FileAccess.Write); } // Insert the data chunk into the file byte[] b = new byte[context.Request.InputStream.Length]; context.Request.InputStream.Read(b, 0, b.Length); fs.Write(b, 0, b.Length); fs.Close(); // Check if this is the last data packet. // If so, call TransferFileToDB() to upload to the DB if (context.Request.QueryString.AllKeys.Contains("completed")) { TransferFileToDB( System.Guid.Parse( context.Request.QueryString["attachmentid"] ) , fi.FullName , context.Request.QueryString["uniquefilename"] , context.Request.QueryString["filename"]); } } catch (Exception ex) { //Handle Error - Remove the temporary file etc } } }
The TransferFileToDB method is responsible for streamlining the local Web Server file contents to a Domain Service method that has been coded to accept data packets. So we continuously call the UploadStreamed() method of the Domain Service until we reach the end of the file.
When we transfer the final packet to the SQL Server we can safely clean up by deleting the temporary file on the Web Server – as we are now sure that the entire contents have been uploaded to the SQL Server Database.
But if any exception is thrown you need to clean up the partially uploaded file on the SQL Database. Plus remove the temporary file on the Web Server, and alert the User accordingly.
private void TransferFileToDB( System.Guid attachmentID , string filePath , string newFileName , string originalFileName) { AttachmentService svc = new AttachmentService(); System.IO.FileStream fs; // Get the file from the Upload Folder System.IO.FileInfo fi = new System.IO.FileInfo(filePath); if (fi.Exists) { try { byte[] buffer; int position = 0; int packetSize = 10 * 1000 * 1024; // Open the file for reading fs = new System.IO.FileStream(filePath, System.IO.FileMode.Open); long length = fs.Length; // Stream the data packets to the Domain Service for storage while (position < length) { if (position + packetSize > length) { buffer = new byte[length - position]; } else { buffer = new byte[packetSize]; } fs.Read(buffer, 0, buffer.Length); svc.UploadStreamed(attachmentID , newFileName , originalFileName , buffer , position); position += buffer.Length; } fs.Close(); // Once all data packets are uploaded, clean up the file. fi.Delete(); } catch (Exception ex) { //Handle error here } } }
Next up…. the Domain Service implementation!
Enjoy!
August 8th, 2012 on 11:27 PM
Great article! Do you an example of what the domain service might look like that’s coded to receive the incoming stream?
Mark
August 9th, 2012 on 7:09 AM
Hi Mark, thanks for the feedback! I knew I forgot something… lol
Here’s the link to the Domain Service code that will process the incoming data packets (freshly written..):
— http://nullablecode.com/2012/08/ria-services-file-uploading-service-method/
Let me know what you think 🙂
September 15th, 2012 on 8:12 PM
Terrific! Thanks for posting that – I’m reading up on it now.
Next question: suppose I want to implement the domain service on a different server than the one the SL client is hosted in – is there a way for the SL client to have RIA services connections to multiple web servers? Maybe with SL Class Libraries? I can’t quite figure out how the pieces would fit.
Mark
October 18th, 2012 on 9:50 AM
Hi,
Thank you for sharing you knowledge with us. It has helped me very much.
I have managed to follow your instructions with no issues up until Visual Studio
“Add Connection”.
Steps:-
1. Updated my instance as a FILESTREAM.
2. Ran:-
EXEC sp_configure filestream_access_level, 2
RECONFIGURE
3. Created database:-
Use Master
GO
IF EXISTS (SELECT name FROM sys.databases WHERE name = N’FileStreamDB_Babak’)
DROP DATABASE FileStreamDB_Babak
GO
USE master
GO
— Create FileStreamDB_Babak Database
CREATE DATABASE [FileStreamDB_Babak] ON PRIMARY
( NAME = N’FileStreamDB_Babak’,
FILENAME = N’C:TutorialsSilverlightLearn26_Uploading_ImagesVersion_17_BobbiesFileStreamDB_BabakFileStreamDB_Babak.mdf’ ,
SIZE = 10MB , MAXSIZE = UNLIMITED, FILEGROWTH = 10%
)
LOG ON
( NAME = N’FileStreamDB_Babak_log’, FILENAME = N’C:TutorialsSilverlightLearn26_Uploading_ImagesVersion_17_BobbiesFileStreamDB_BabakFileStreamDB_Babak_log.ldf’ ,
SIZE = 10MB , MAXSIZE = UNLIMITED , FILEGROWTH = 10%
)
GO
ALTER DATABASE [FileStreamDB_Babak]
ADD FILEGROUP [FileStreamGroup_Babak] CONTAINS FILESTREAM
GO
ALTER DATABASE [FileStreamDB_Babak]
ADD FILE
( NAME = N’FileStreamDB_FSData’,
FILENAME = N’C:TutorialsSilverlightLearn26_Uploading_ImagesVersion_17_BobbiesFileStreamDB_BabakFileStreamData’
)
TO FILEGROUP FileStreamGroup_Babak
GO
4. Created table:-
CREATE TABLE [dbo].[File](
[FileID] [int] IDENTITY(1,1) NOT NULL,
[DocumentFileId] [uniqueidentifier] ROWGUIDCOL NOT NULL UNIQUE DEFAULT NEWSEQUENTIALID(),
[DocumentFile] [varbinary](max) FILESTREAM NULL,
[Date_Time] DATETIME DEFAULT GETDATE()
CONSTRAINT [File_PK] PRIMARY KEY CLUSTERED
(
[FileID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
FILESTREAM_ON [FileStreamGroup_Babak],
UNIQUE NONCLUSTERED
(
[DocumentFileId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
FILESTREAM_ON [FileStreamGroup_Babak]
5. I inserted a row with an image:-
INSERT INTO [FileStreamDB_Babak].[dbo].[File]
([DocumentFile])
SELECT * FROM
OPENROWSET(BULK N’C:TutorialsSilverlightLearn26_Uploading_ImagesVersion_17_BobbiesSampleFilesDSC00101.JPG’ ,SINGLE_BLOB) AS Document
GO
6. Queried this row with that image:-
SELECT [FileID]
,[DocumentFileId]
,[DocumentFile]
,[Date_Time]
FROM [FileStreamDB_Babak].[dbo].[File]
GO
7. Checked that this image was created and viewed it in Microsoft Photo Viewer successfully.
8. Created a new Silverlight project with WCF Ria check box as true.
9. But when I go to Visual Studio to Add connection to this database (.mdf file) I get a long error:-
FILESTREAM feature is disabled.
A file activation error occurred. The physical file name ‘C:TutorialsSilverlightLearn26_Uploading_ImagesVersion_17_BobbiesFileStreamDB_BabakFileStreamData’ May be incorrect. Disgnose and correct additional errors, and retry the operation.
Could not open new database ‘C:TutorialsSilverlightLearn26_Uploading_ImagesVersion_17_Bobbies FileStreamDB_Babak FileStreamDB_Babak.MDF’.
CREATE DATABASE is aborted.
An attempt to attach an auto-named database for file ‘C:TutorialsSilverlightLearn26_Uploading_ImagesVersion_17_Bobbies FileStreamDB_Babak FileStreamDB_Babak.MDF’ failed. A database with the same name exits, or specified file can not be opened, or it is located on UNC share.
I have other projects working with Silverlight 4 without any issues. I am able to add connections to other instances/databases.
Details:-
Visual Studio 2010 ultimate
SQL Server 2008
Silverlight 4
Windows 7 32bit
Please help, I can not find anything on the web. I have been trying to solve this issue for several days now with no success.
Best Regards
Ravie