When you buy this book you support this site! - Thank You for your support!

Nov 3

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

clip_image018

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

clip_image001

Create a new LightSwitch application.

 

clip_image002

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

clip_image003

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.

 

clip_image004

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.

 

clip_image005

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

 

clip_image006

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

clip_image007

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

 

clip_image008

Select WCF RIA Service.

 

clip_image009

Click Add Reference.

 

clip_image010

Select the RIA Service project.

 

clip_image011

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

 

clip_image012

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

 

clip_image013

The Entity will show up.

 

clip_image014

Right-click on Screens to add a Screen.

 

clip_image015

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

 

clip_image016

The Screen will be created.

 

clip_image017

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.

 

clip_image018

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

14 comment(s) so far...


Gravatar

Re: Connecting To A .CSV or Excel File Directly With LightSwitch

This is great - but how would the code change if you wanted the zip codes to be a numeric field, for example?

By Chris on   11/5/2011 10:18 AM
Gravatar

Re: Connecting To A .CSV or Excel File Directly With LightSwitch

@Chris - You would have to retrieve the records then manually loop through them to cast the field to a integer.

By Michael Washington on   11/5/2011 10:48 AM
Gravatar

Re: Connecting To A .CSV or Excel File Directly With LightSwitch

Yes, well, um, I literally have no idea how to do that. :)

Alright, well, I guess I can play with it and figure it out. Thanks!

By Chris on   11/5/2011 11:01 AM
Gravatar

Re: Connecting To A .CSV or Excel File Directly With LightSwitch

if I want to read from different sheets ?

By Nicolas on   12/26/2011 6:52 AM
Gravatar

Re: Connecting To A .CSV or Excel File Directly With LightSwitch

@Nicolas - You will want to use Excel files, and alter the code to read the file as an Excel file rather than a .csv

By Michael Washington on   12/26/2011 6:54 AM
Gravatar

Re: Connecting To A .CSV or Excel File Directly With LightSwitch

Hello Michael

Excellent article .. but I am having one small problem when I try to activate the WCF RIA server as a data source. I have entered the code for the ZipCode class as you indicate but when I setup the data source I get the message in teh Select Data Source Objects screen .. The Selected WCF RIA Service does not contain any entity defintiion.

Is there a missing Attribute in the class definition?

Thanks for your help.

Regards
Don Shushack

By dshusha on   6/14/2012 6:25 AM
Gravatar

Re: Connecting To A .CSV or Excel File Directly With LightSwitch

@dshusha - Please try my code on the Download page. If that doesn't work please make a post in the foruim on this site. Thanks.

By Michael Washington on   6/14/2012 6:32 AM
Gravatar

Re: Connecting To A .CSV or Excel File Directly With LightSwitch


Thanks Micheal for this helpful article.
But I have few queries:

1) What if open your project in visual studio in 2012 version??
2) where should i put the Zipcodes folder in side the application ??

Regards,
Vishal

By Vishal on   1/31/2015 10:15 AM
Gravatar

Re: Connecting To A .CSV or Excel File Directly With LightSwitch

Hi Micheal,

despite my zipcodes.csv file is already present in the debug folder as mentioned in the article above, code is not able to find the file.
Please respond.

Regards,
Vishal

By Vishal on   1/31/2015 11:35 AM
Gravatar

Re: Connecting To A .CSV or Excel File Directly With LightSwitch

@Vishal - You may get help in the Official LightSwitch forums. I am sorry but I can only make sure the code sample work on the version they were written on. I can't guarantee they will work in other versions of LightSwitch.

By Michael Washington on   1/31/2015 12:33 PM
Gravatar

Re: Connecting To A .CSV or Excel File Directly With LightSwitch

Michael,

Thank you for the post. Very helpful. I am struggling to make it work for excel file. I changed the file name to zipcode.xlsx and placed the file in the root directory but no luck. could you help me out.

Thank you in advance.

By deepk on   12/30/2015 2:45 PM
Gravatar

Re: Connecting To A .CSV or Excel File Directly With LightSwitch

@deepk - Sorry I have not touched Silverlight in years :( You may get help in the Official LightSwitch forums at: https://social.msdn.microsoft.com/Forums/vstudio/en-US/home?forum=lightswitch

By Michael Washington on   12/30/2015 2:47 PM
Gravatar

Re: Connecting To A .CSV or Excel File Directly With LightSwitch

Hi,

Is this will work in MS VS 2015 Light switch? I used this method in 2015 version, bet getting 26 errors in WCF_RIA_Services.cs. Do you have a Code To Open The .CSV File for 2015?

By Sail on   4/22/2016 4:20 AM
Gravatar

Re: Connecting To A .CSV or Excel File Directly With LightSwitch

@Sail - I'm sorry but I do not know if this works with the VS2015 version. I have not touched Silverlight in years :( You may get help in the Official LightSwitch forums at: https://social.msdn.microsoft.com/Forums/vstudio/en-US/home?forum=lightswitch

By Michael Washington on   4/22/2016 4:21 AM

Your name:
Gravatar Preview
Your email:
(Optional) Email used only to show Gravatar.
Your website:
Title:
Comment:
Security Code
CAPTCHA image
Enter the code shown above in the box below
Add Comment   Cancel 
Microsoft Visual Studio is a registered trademark of Microsoft Corporation / LightSwitch is a registered trademark of Microsoft Corporation