Ready to get started?
Learn more about the CData ADO.NET Provider for CSV or download a free trial:
Create Master-Detail Screens for CSV Data in LightSwitchUse the CData ADO.NET CSV data provider to create a master-detail screen.
LightSwitch provides support for using relationships between tables in a data source such as the master-detail, or one-to-many, relationship between Customer and Invoices. This article will walk you through the steps to set up a LightSwitch master-detail screen using simple queries.
Configure the CSV ADO.NET Data Source
Follow the steps below to use the Data Source Configuration Wizard to configure connection properties and import CSV tables.
- In a new LightSwitch project, click "Attach to data source".
- In the resulting wizard, select the Database data source type. The Connection Properties dialog is displayed.
Click Change and select the CData CSV Data Source. Enter the required connection properties.
The DataSource property must be set to a valid local folder name. Also, specify the IncludeFiles property to work with text files having extensions that differ from .csv, .tab, or .txt. Specify multiple file extensions in a comma-separated list. You can also set Extended Properties compatible with the Microsoft Jet OLE DB 4.0 driver. Alternatively, you can provide the format of text files in a Schema.ini file.
Below is a typical connection string:
Additionally, set the CacheLocation and CacheMetadata properties to improve performance. The performance benefits gained from caching metadata are especially relevant in LightSwitch projects, as creating the entity model requires data about every table and column. Cache the metadata before connecting to CSV so that LightSwitch can pull the table schemas from the cache instead of sending a query for every table.
See the "Caching Data" chapter in the help documentation for more information on the provider's caching features.
- Select two tables that have a master-detail relationship, such as Customer and Invoices.
DataBind CSV Tables to a Master-Detail Screen
Follow the steps below to use the designer to create the master-detail screen and provide QuickBooks data to it. In this example, you will make a simple screen that displays the grid for the master table, Customer, on top with the grid for the detail table, Invoices, on the bottom.
- In Solution Explorer, right-click the Invoices table under Data Sources and click Add Query.
- Add a parameter of type String to serve as a placeholder for the CustomerId field.
Add a filter condition to this query. In this example, you will search for all line items with a certain CustomerId. The parameter will serve as a placeholder for this value. Set the type of filter criteria to Parameter.
- In Solution Explorer, right-click the Screens folder and click Add Screen. In the resulting wizard, select the Editable Grid screen. In the Screen Data list, select the Customer table.
- In the designer for the new screen, click the Add Data Item button and enable the Query option. Select the query you created.
- In the list of data items, scroll to the Query Parameters node at the bottom of the list. In the Parameter properties, enter "Customer.SelectedItem.Id" in the Parameter Binding box. This will bind the parameter to the key for the currently selected Customer record.
Drag the query from the data item list onto the Screen Content Tree. In this example, you will make a simple screen that displays the Customer grid on top with the Invoices grid on the bottom. However, you can use layout items to customize the look of the screen however you need.
Now all you need to do is run the application: This simple screen displays the Customer grid above the Invoices grid. Every time you select a record from the Customer table, the grid will be populated with details from the Invoices table.
If an error occurs when trying to load the data from these tables, you may see a red "X" where the data should appear. To troubleshoot the issue and see a more descriptive error message, follow these steps:
- Open your project properties and change the Application Type to Web.
- In Solution Explorer, select your project node and change from Logical View to File View.
- Expand the Server node and open the Web.config. Change the Mircosoft.LightSwitch.Trace.Enabled key to "true".
- Run your application again to the point that you receive the error. Change the URL to http://URL:port number/trace.axd
- Check the trace for requests that have a status code other than 200. Errors with a 500 status code can correspond to an error coming from the CSV data ADO.NET Provider.
If you continue to experience problems with your LightSwitch project, please contact email@example.com.