Nov 3

Written by: Michael Washington
11/3/2011 8:23 PM 

Sometimes we are faced with the need to read data from a .csv (comma separated value) or Excel file. One option is to import the data, but this can turn out to be more trouble than any benefit if the file changes frequently.

Using WCF RIA Services allows the option to place the .csv or Excel file on the server hard drive and have LightSwitch access the data directly. When the file changes, simply place the new file on the server hard drive. While it wont be covered in this article, it is possible to edit the file from LightSwitch (see this article for an example of an updatable WCF RIA Service).

The disadvantage of this approach, is that it can use a lot of server memory. This is only useful if the data is accessed infrequently (for example, only a few times an hour by a small number of people).

If you need to import the .csv or Excel file into LightSwitch see this link:

http://blogs.msdn.com/b/lightswitch/archive/2010/10/08/how-do-i-import-and-export-data-to-from-a-csv-file-dan-seefeldt.aspx.

Note: You must have Visual Studio Professional (or higher) to create WCF RIA Services using the method described in this article.

 

Create The Application

Create a new LightSwitch application.

 

Add a Entity (table) to the application, and build the application (Build/Build Solution). It is important that we do this step, otherwise we will not have an ApplicationData.cs file to connect the WCF RIA Service to in the later step.

After the WCF RIA Project is completed, you can delete the Entity (table).

Download the WCF RIA Project from the Downloads page, and unzip it.

Click on the Solution in the Solution Explorer, and then select File, then Add, then Existing Project.

 

Navigate to the WCF_RIA_Project.csproj file and select it.

The project will be added to your solution.

Reference The LightSwitch Object Context

Now, we will add code from the LightSwitch project to our WCF RIA Service project. We will add a class that LightSwitch automatically creates, that connects to the database that LightSwitch uses.

We will use this class in our WCF RIA Service to communicate with the LightSwitch database.

 

Right-click on the WCF_RIA_Project and select Add then Existing Item.

 

Navigate to ..ServerGenerated\GeneratedArtifacts (in the LightSwitch project)and click on ApplicationData.cs and Add As Link.

 

Code To Open The .CSV File

Add the following code to the WCF_RIA_Service.cs file. Put it inside the Namespace, but not inside the WCF_RIA_Service class (otherwise you will get build errors):

 

    public class ZipCode
    {
        [Key]
        public string Zip { get; set; }
        public string City { get; set; }
        public string State { get; set; }
        public string Latitude { get; set; }
        public string Longitude { get; set; }
        public string Timezone { get; set; }
        public string Dst { get; set; }
    }

 

Add the following code to the class:

 

        [Query(IsDefault = true)]
        public IQueryable<ZipCode> GetAllZipCodes()
        {
            string fileName = HttpContext.Current.Server.MapPath("~/zipcode.csv");
            var colZipCodes = (from line in File.ReadAllLines(fileName, Encoding.Default)
                               let parts = line.Split(",".ToCharArray())
                               select new ZipCode()
                               {
                                   Zip = parts[0],
                                   City = parts[1],
                                   State = parts[2],
                                   Latitude = parts[3],
                                   Longitude = parts[4],
                                   Timezone = parts[5],
                                   Dst = parts[6]
                               });
            return colZipCodes.AsQueryable();
        }

 

This code will open a file called “zipcode.csv” that is in the root of the application, and provide it’s contents to the LightSwitch application.

You can get the Zipcode.csv file in the ZipCode.zip file from the Downloads page.

When you publish the application, you simply place this file in the root of the application and it will work. When you are debugging the application, you have to place the file in the bin folder.

In Visual Studio, Build the entire solution.

 

Add the WCF RIA Service

In the Solution Explorer, right-click on the Data Sources folder and select Add Data Source.

 

Select WCF RIA Service.

 

Click Add Reference.

 

Select the RIA Service project.

 

You have to wait for the service to show up in the selection box. Select it and click Next.

 

Check the box next to the Entity, and click Finish.

 

The Entity will show up.

 

Right-click on Screens to add a Screen.

 

Add a Editable Grid Screen that uses the ZipCodes data service.

 

The Screen will be created.

 

When you run the application, you will get an error if it can’t find the zipcode.csv file.

Simply place the file in the indicated location and run the application again.

 

The application will display the zip codes.

Search will work.

Paging will work.

Remember, When you publish the application, you simply place the ZipCode.csv file in the root of the application.

 

Download Code

The LightSwitch project is available at http://lightswitchhelpwebsite.com/Downloads.aspx