Lightswitch export all rows to CSV

Question

I am using c# and VS2012 on a lightswitch web-application,

I wish to export my data to CSV (on a search screen!), but can't reach any POC,

As i understand there are 2 main problems - a savefiledialog must be caused directly from a user button and in it must happened in the main dispatcher,

I used this code :

        partial void mySearchScreen_Created()
        {
            var CSVButton = this.FindControl("ExportToCSV");
            CSVButton.ControlAvailable += ExportCSV_ControlAvailable;

        }
        private void ExportCSV_ControlAvailable(object sender, ControlAvailableEventArgs e)
        {
            this.FindControl("ExportToCSV").ControlAvailable -= ExportCSV_ControlAvailable;
            Button Button = (Button)e.Control;
            Button.Click += ExportCSV_Click;
        }

        private void ExportCSV_Click(object sender, System.Windows.RoutedEventArgs e)
        {
            Microsoft.LightSwitch.Details.Client.IScreenCollectionProperty collectionProperty = this.Details.Properties.mySearch;
            var intPageSize = collectionProperty.PageSize;
            //Get the Current PageSize and store to variable
            collectionProperty.PageSize = 0;

            var dialog = new SaveFileDialog();
            dialog.Filter = "CSV (*.csv)|*.csv";
            if (dialog.ShowDialog() == true) {

                using (StreamWriter stream = new StreamWriter(dialog.OpenFile())) {
                    string csv = GetCSV();
                    stream.Write(csv);
                    stream.Close();
                    this.ShowMessageBox("Excel File Created Successfully. NOTE: When you open excel file and if you receive prompt about invalid format then just click yes to continue.", "Excel Export", MessageBoxOption.Ok);
                }
            }
            collectionProperty.PageSize = intPageSize;
            //Reset the Current PageSize
        }

        private string GetCSV()
        {
            StringBuilder csv = new StringBuilder();

            int i = 0;

            foreach (var orderRow_loopVariable in mySearch) {
                var orderRow = orderRow_loopVariable;
                ////HEADER
                if (i == 0) {
                    int c = 0;
                    foreach (var prop_loopVariable in orderRow.Details.Properties.All().OfType<Microsoft.LightSwitch.Details.IEntityStorageProperty>()) {
                        var prop = prop_loopVariable;
                        if (c > 0) {
                            csv.Append(",");//Constants.vbTab
                        }
                        c = c + 1;
                        csv.Append(prop.DisplayName);
                    }
                }
                csv.AppendLine("");

                ////DATA ROWS

                int c1 = 0;
                foreach (var prop_loopVariable in orderRow.Details.Properties.All().OfType<Microsoft.LightSwitch.Details.IEntityStorageProperty>()) {
                    var prop = prop_loopVariable;
                    if (c1 > 0) {
                        csv.Append(",");//Constants.vbTab
                    }
                    c1 = c1 + 1;
                    csv.Append(prop.Value);
                }
                i = i + 1;
            }

            if (csv.Length > 0) {
                return csv.ToString(0, csv.Length - 1);
            } else {
                return "";
            }
        }

This works, but it only get's me the first page items, On another thing i had to do i solved that problem by using this code :

this.DataWorkspace.myDataContextData.MySearch(...).Execute();

Yet trying that instead of just using 'MySearch' gives me the following error :

t is not valid to call Execute() on a different Dispatcher than the ExecutableObject's Logic Dispatcher.

Why is it so difficult to do such a basic thing related to data (export to csv/excel) on a system build for handling data ?

Any ideas ?

score 2 · Answer 1 · answered Jan 29 '14 at 17:56

The simplest workaround if this is the only use of the search screen would be to turn off paging. To do this go to the screen designer, highlight the query on the left, and in properties uncheck 'support paging.'

I'm not sure what the limitations are, but you can run some code in a different dispatcher using:

                        this.Details.Dispatcher.BeginInvoke(() =>
                        {
                            //This runs on main dispatcher
                        });

score 0 · Answer 2 · answered Mar 12 '15 at 12:52

I don't think there's anything wrong with your code, but I've noticed that it takes a while to reset the page size on a large collection, in which time the rest of your code continues to execute. I think that's why you only get the first page. The only solution I've found is to wait.

When the "File Download - Security Warning" dialog pops up, keep an eye on the 'busy' indicator on the screen's tab and also the 'Page x of y' status at the bottom of the grid if you can see it. Only when the busy indicator has gone and the status just says 'Page' should you click OK to continue.

I haven't figured out a way of doing this programmatically so it's not a very helpful feature unless you have a very tightly controlled user population. But if it's just you and a couple of power users, it is workable. I'm also not sure if this has been improved on in versions after VS2012.

There can be a downside to the other answer of taking the paging off the query entirely. I've tried that workaround when the grid collection was being displayed in a modal window and the window became uncloseable if there were too many rows in the grid.

Phil

Lightswitch export all rows to CSV

2 Answers2