Jim Jackson

Character Counts.
Do the right thing.

Sponsors

June 2010 - Posts

Uploading a File to SQL Server via Silverlight, WCF and EF
Note: Some of the code presented here does not conform to standard security practices. The goal is to show the relevant methods to accomplish the requirements, not to present a production-ready solution.

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).

Data Model

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<intList<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<intList<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,
    [FileName] [varchar](250) NOT NULL,
    [FileType] [varchar](50) NOT NULL,
    [FileData] [varbinary](max) NULL,
    [FileExtension] [varchar](50) NOT NULL,
    [FileSize] [bigint] NOT NULL,
 CONSTRAINT [PK_UploadedFilesPRIMARY 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]
)
ON [PRIMARY]
GO
CREATE TABLE [dbo].[UploadedFileParts]
(
    [FileID] [int] NOT NULL,
    [Ordinal] [int] NOT NULL,
    [FileDataPart] [varbinary](max) NOT NULL,
 CONSTRAINT [PK_UploadedFileParts]
 PRIMARY KEY CLUSTERED
  ([FileID] ASC, [Ordinal] ASC)
 
WITH (
 
PAD_INDEX=OFF,
  STATISTICS_NORECOMPUTE=OFF,
  IGNORE_DUP_KEY=OFF,
  ALLOW_ROW_LOCKS=ON,
  ALLOW_PAGE_LOCKS=ON
 
) ON [PRIMARY]
) ON [PRIMARY]
GO 

 

SQL 2008 Geography - Combine Data Points into a Geography Line

I have encountered many instances where having the decimal values for coordinates in a line is more useful than the having the Geography value for that same line. Likewise, in some instances a Geography value is more appropriate. Here is what I mean:

I need all points in a polygon on a map. For my purposes, it is much (MUCH) faster to determine the overall rectangle that surrounds the polygon, add a bit of latitude and longitude to all axis and search against the individual points. With an index on the decimal values this is fast and guarantees that I get everything in the polygon. I don't mind too much that I also get a bunch of stuff not in that polygon. I use this to find what is visible to the user, not to determine actual spatial relationships among points or between points and the polygon.

There are also times when I need to find distances and spatial relationship between points in a line. This is where the Geography type comes in and is both fast and accurate.

In the end, the Geography type seems best suited to calculations and the decimal values are better off used for searching.

So, once you have a bunch of coordinates in a table (PointsTable) and have them ordered properly and referenced by a header value, here is how you can quickly turn the line into a Geography instance. I do this when I am adding records to the PointsTable and store the output in the header table. (HeaderID is the inbound parameter.) I'm not really fond of the SubString statement in here but I haven't worked out how to append to LineList conditionally.

Declare @HeaderID Int
Select @HeaderID = 123

Declare @LineList VarChar(Max)
Declare @GeoVal Geography 
Select @LineList = @LineList +
      Convert(VarChar(100), Longitude)
      + ' '
      + Convert(VarChar(100), Latitude) + ', '
From dbo.PointsTable
Where HeaderID = @HeaderID
Order By Ordinal
 
 
Select @LineList =
      SubString(@LineList, 1, (Len(@LineList) - 2))
Select @LineList =
      'LINESTRING(' + @LineList + ')' 
Select @GeoVal = 
      Geography::STLineFromText(@LineList, 4326);

 

More Posts