Uploading a File to SQL Server via Silverlight, WCF and EF
The requirements:
ü Upload individual large files to SQL Server 2008
ü Use a standard Silverlight-enabled WCF service with no special plumbing
ü Respect the very small upload size limits in normal binary WCF transmissions
ü Use Entity Framework 4.0 against a model containing only tables
ü Do not use any special HTTP handlers
Here is what we're trying to accomplish: The requirement is to take a file submitted by the user and send it to the database regardless of how large it is. We must avoid timing out our WCF connection (keep the message size small) and we must have fail-over logic in place in case a single part of the fail fails. Once a file has been completely uploaded, we want to perform other server-based operations on the file.
Let's get started by creating a new Silverlight Application called TestFileUpload and allow the template to also create a TestFileUpload.Web project.
Continue by creating two tables on the server. The first will store queued file parts while the second stores the final file. Note that FileData and FileDataPart columns are VarBinary(Max).
In the solution add a new class libary project called DBContext. Kill the class1.cs file and add a new ADO.NET Entity Data Model. Call it FileUploadModel and allow the name of the connection and model to stay set at FileUploadTestEntities. Next, in the web site, open the web.config file and copy the connection string from the DBContext project over. Time to compile the solution.
The Web Project
Add a project reference to the DBContext project and rebuild the solution.
Add a new Silverlight-enabled WCF Service and call it Uploader.svc. In the new service, kill the DoWork() method. Note that at this stage your service should be configured for binary message encoding and the size of your uploads and downloads are very limited. We'll leave this exactly as is and assume that you can only upload 8192 bytes per message. We'll make it a bit easier to calculate in your head and cut it to 8000 bytes per message.
In the UploadFile method, start with this:
using (FileUploadTestEntities context = new FileUploadTestEntities()) { }
Note the squiggly under the the using keyword. This occurs because Visual Studio knows what a FileUploadTestEntities object is but not what it inherits from. Add a reference in your web project to System.Data.Entity and this should resolve.
In our web service we will have 3 methods, all returning integers:
UploadFile will take some basic file information and the first binary part of the file and return the new ID of the file record.
UploadFilePart is an iterative add method that takes the file ID, the position of the binary steam in the data and of course the binary part. It returns 0 if the insert fails, 1 if the data already exists and 2 if the record was successfully inserted or updated.
FinalizeFile will take all the parts of the data and process them into the header record then delete all the part data after a few simple checks. See notes at the end for a better method of accomplishing much of this logic. FinalizeFile will also be where you can call any additional processing methods.
The Silverlight Project
Next, we'll move on to the Silverlight application. Here is what we need.
· A method and an interface to select a file and gets the source stream of that file.
· Logic to divide the stream into byte arrays.
· A cyclic process to upload each element in the byte arrays.
We'll forego an MVVM pattern here with ICommand and/or RelayCommand implementations in order to keep it strictly about the problem space. It is advisable to take this entire logical process and put it into it's own component.
Open the MainPage.xaml file, add a button and wire up it's Click event to it. Add a service reference to the Silverlight application pointing to your WCF service. Set the service reference namespace to Upload.
In the code-behind for MainPage.xaml, we will add a few private fields to maintain the uploaded file's data while processing it. Critical here are the fileBuffer generic list of bytes, the fileParts generic list of byte-sized (sic) chunks and the completedSections list of type ObservableCollection. The completedSections object is nice because it allows us to wire up events when the collection changes. The code is oversimplified to call the Finalize method every time the object changes but for our purposes, it works.
In the constructor we will wire up the CollectionChanged event of completedSections. Every time this collection changes, this event will fire.
The button_click event will allow the user to select a file and then break the file into arbitrary chunks. Once this is done, we call StartFileUpload to kick off the process.
StartFileUpload gets the first value from fileParts and fires it asynchronously, passing in the first section of the file. Note that ClientProxy is a local property that returns an UploaderClient object that is prewired to fire all three of it's return events.
client_UploadFileCompleted gets the return value from the client. If successful, we fire every object in fileParts using SendSection.
SendSection fires a single entry from fileParts based on an index value. The return of this method fires the client_UploadFilePartCompleted event.
client_UploadFilePartCompleted will return a value indicating whether or not the insert/update succeeded. If it succeeded, we add the index to completedSections which, in turn, fires the FinalizeFile method.
FinalizeFile will check to see if all parts of the file were sent (and successful) and ensures that the web service' FinalizeFile method is called only once. This method will return 0 if successful, -1 if failure or the index of the offending record if a failure reason can be determined. If the FinalizeFile method fails, we try to send the failed section again.
Some notes for production use:
a) You will need to security trim all your methods.
b) Consider sending max file size, available file types and individual message size in an initialization call.
c) This code could be dangerous in that a virus-infected file can be uploaded to your database and then opened by an unsuspecting user. Consider adding attributes to your uploaded files table to allow them to be quaranteened and scanned.
d) The Finalize method used here should be modified to include calls that send SQL directly to the database. I did not do that specifically to show that everything could be done with no SQL. The result is that the binary data is passed multiple times when all that is required is a call to DataLength() on the SQL side. As an aside, you MUST use DataLength() when testing the size of a VarBinary(Max) column in SQL Server. A call to Len() will not be reliable on this data type.
e) When a particular part of a file fails to get into the database properly and we resend, consider adding logic to control the number of retries per section and total retries allowed.
So here is the code for the WCF service:
[OperationContract]
public int UploadFile(string fileName, string fileType,
string fileExtension, Int64 fileSize, byte[] firstFileData)
{
int ret = 0;
using (FileUploadTestEntities context = new FileUploadTestEntities())
{
UploadedFile file = new UploadedFile();
file.FileName = fileName;
file.FileType = fileType;
file.FileExtension = fileExtension;
file.FileSize = fileSize;
context.AddToUploadedFiles(file);
context.SaveChanges();
UploadedFilePart part = new UploadedFilePart();
part.FileID = file.FileID;
part.Ordinal = 0;
part.FileDataPart = firstFileData;
context.AddToUploadedFileParts(part);
context.SaveChanges();
ret = file.FileID;
}
return ret;
}
[OperationContract]
public int UploadFilePart(int fileID, int ordinal,
bool overwrite, byte[] fileData)
{
// return values:
// 0 = Not inserted
// 1 = Already exists
// 2 = Inserted or updated
int ret = 0;
// Upload a new file part to the database
using (FileUploadTestEntities context = new FileUploadTestEntities())
{
// Check to be sure this part does not already exist.
var foundPart = (from p in context.UploadedFileParts
where p.FileID == fileID
&& p.Ordinal == ordinal
select p).FirstOrDefault();
if (foundPart != null && overwrite)
{
foundPart.FileDataPart = fileData;
context.SaveChanges();
ret = 2;
}
else if (foundPart != null && !overwrite)
{
ret = 1;
}
else // foundPart == null so ignore overwrite
{
UploadedFilePart nextPart = new UploadedFilePart();
nextPart.FileID = fileID;
nextPart.Ordinal = ordinal;
nextPart.FileDataPart = fileData;
context.AddToUploadedFileParts(nextPart);
context.SaveChanges();
ret = 2;
}
}
return ret;
}
[OperationContract]
public int FinalizeFile(int fileID)
{
// File upload is complete, post all file data to the UploadedFiles
using (FileUploadTestEntities context = new FileUploadTestEntities())
{
var totalPartSizes = from allParts in context.UploadedFileParts
where allParts.FileID == fileID
select allParts.FileDataPart;
Int64 totalPartSize = 0;
foreach (var sizePart in totalPartSizes)
totalPartSize += sizePart.Length;
var totalAssignedSize = (from fileTest in context.UploadedFiles
where fileTest.FileID == fileID
select fileTest.FileSize).First();
if (totalAssignedSize > totalPartSize)
{
// The sizes do not match.
// Find the first part that does not match the assigned size.
var missingParts = from p in context.UploadedFileParts
where p.FileID == fileID
orderby p.Ordinal
select p;
int iTestOrdinal = 0;
foreach (var testPart in missingParts)
{
// Test for contiguous elements
if (testPart.Ordinal != iTestOrdinal)
return iTestOrdinal;
// Test for size of the element as long as
// it's not the last one.
if (iTestOrdinal != (missingParts.Count() - 1))
{
if (testPart.FileDataPart.Length != 8000)
return iTestOrdinal; //////////////
}
iTestOrdinal++;
}
// We didn't find the problem. Nothing to do but fail.
return -1; //////////////
}
else if (totalAssignedSize < totalPartSize)
{
// There are too many parts.
// Not much we can do here except fail.
return -1; //////////////////
}
// The total size of parts is the same as the
List<byte> allFileBytes = new List<byte>();
// Get the list of parts for this item
var parts = from p in context.UploadedFileParts
where p.FileID == fileID
orderby p.Ordinal
select p;
foreach (var part in parts)
allFileBytes.AddRange(part.FileDataPart.ToList<byte>());
var file = (from f in context.UploadedFiles
where f.FileID == fileID
select f).FirstOrDefault();
if (file != null)
file.FileData = allFileBytes.ToArray();
context.SaveChanges();
// Final test to be sure that the file updated.
var finalTest = (from f in context.UploadedFiles
where f.FileID == fileID
select new { f.FileSize, f.FileData }).First();
if (finalTest.FileSize != finalTest.FileData.Length)
return -1; ////////////
// Matching sizes detected. Go ahead and delete the parts.
foreach (var part in parts)
context.UploadedFileParts.DeleteObject(part);
context.SaveChanges();
// We return zero because the first file part
// was guaranteed by the fact that we received
// a fileID in the very first call to the service.
return 0;
}
}
Here is the code-behind for MainPage.Xaml.cs:
private List<byte> fileBuffer = null;
private FileInfo selectedFile = null;
private int fileID = 0;
private int sectionCount = 0;
private ObservableCollection<int> completedSections =
new ObservableCollection<int>();
private UploaderClient client = null;
private bool finalizedFile = false;
private Dictionary<int, List<byte>> fileParts;
public MainPage()
{
InitializeComponent();
completedSections.CollectionChanged +=
delegate(object sender, NotifyCollectionChangedEventArgs e)
{
FinalizeFile();
};
}
private void Button_Click(object sender, RoutedEventArgs e)
{
OpenFileDialog openFileDialog = new OpenFileDialog();
openFileDialog.Filter = "JPEG files|*.jpg";
openFileDialog.Multiselect = false;
if (openFileDialog.ShowDialog() == true)
{
try
{
using (FileStream strm = openFileDialog.File.OpenRead())
{
selectedFile = openFileDialog.File;
using (BinaryReader rdr = new BinaryReader(strm))
{
fileBuffer =
rdr.ReadBytes(
(int)strm.Length).ToList<byte>();
}
}
if (fileBuffer != null)
{
fileParts = new Dictionary<int, List<byte>>();
var fileSections = from idx in
Enumerable.Range(0, fileBuffer.Count())
group fileBuffer[idx] by idx / 8000;
sectionCount = fileSections.Count();
int ordinal = 0;
foreach (var section in fileSections)
{
List<byte> itm = new List<byte>();
foreach (var b in section)
itm.Add(b);
fileParts.Add(ordinal, itm);
ordinal++;
}
StartFileUpload();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
}
private void StartFileUpload()
{
byte[] msgBody = fileParts.First().Value.ToArray();
ClientProxy.UploadFileAsync(
selectedFile.Name,
"image",
selectedFile.Extension,
fileBuffer.Count(),
msgBody,
0);
}
private void client_UploadFileCompleted(
object sender, UploadFileCompletedEventArgs e)
{
if (e.Result == 0)
{
ResetAll();
throw new
NullReferenceException("The file insert failed.");
}
else
{
fileID = e.Result;
completedSections.Add(0);
for (int i = 1; i < sectionCount; i++)
SendSection(i, false);
}
}
private void SendSection(int sectionKey, bool overWrite)
{
List<byte> foundPart;
if (fileParts.TryGetValue(sectionKey, out foundPart))
{
byte[] msgBody = foundPart.ToArray();
ClientProxy.UploadFilePartAsync(
fileID,
sectionKey,
overWrite,
msgBody,
sectionKey);
}
}
private void client_UploadFilePartCompleted(
object sender,
UploadFilePartCompletedEventArgs e)
{
if (e.Result != 0)
{
completedSections.Add(
(int)e.UserState);
}
else
{
completedSections.Remove((int)e.UserState);
SendSection((int)e.UserState, true);
}
}
private void FinalizeFile()
{
if (completedSections.Count() ==
sectionCount && !finalizedFile)
{
finalizedFile = true;
ClientProxy.FinalizeFileAsync(fileID);
}
}
private void ResetAll()
{
fileBuffer = null;
selectedFile = null;
fileID = 0;
completedSections =
new ObservableCollection<int>();
finalizedFile = false;
fileParts = null;
}
private void client_FinalizeFileCompleted(
object sender,
FinalizeFileCompletedEventArgs e)
{
if (e.Result == 0)
{
MessageBox.Show("Finished uploading file.");
ResetAll();
}
else if (e.Result == -1)
{
MessageBox.Show(
"Upload failed. Contact the system admin.");
}
else
{
completedSections.Remove(e.Result);
finalizedFile = false;
MessageBox.Show(
"Failure detected is at ordinal position " +
e.Result.ToString() +
". Retrying this file section.");
SendSection(e.Result, true);
}
}
private UploaderClient ClientProxy
{
get
{
if (client == null)
{
client = new UploaderClient();
client.UploadFileCompleted += new
EventHandler<UploadFileCompletedEventArgs>(
client_UploadFileCompleted);
client.UploadFilePartCompleted += new
EventHandler<UploadFilePartCompletedEventArgs>(
client_UploadFilePartCompleted);
client.FinalizeFileCompleted += new
EventHandler<FinalizeFileCompletedEventArgs>(
client_FinalizeFileCompleted);
}
return client;
}
}
And the script to create the database tables:
CREATE TABLE [dbo].[UploadedFiles](
[FileID] [int] IDENTITY(1,1) NOT NULL,
[FileSize] [bigint] NOT NULL,
STATISTICS_NORECOMPUTE=OFF,
ON [PRIMARY]
(
PRIMARY KEY CLUSTERED
WITH