Performing Data-Related Tasks by Using Code

 

For the latest documentation on Visual Studio 2017, see Visual Studio 2017 Documentation.

You can use the designers and tool windows in Visual Studio LightSwitch to accomplish many data-related design tasks. However, certain tasks can only be accomplished by adding code to an application. For example, to validate a field by applying custom conditions, you must write code. This document shows how to accomplish data-related tasks by using the data runtime object model. For more information about where you can write code in an application, see the following topics:

• How to: Handle Data Events

• How to: Handle Silverlight Screen Events

• How to: Handle Query Events

Common Tasks

The following list shows common data-related tasks that you can accomplish by using the data runtime object model. The tasks are described later in this document.

• Reading Data

• Updating Data

• Deleting Data

• Adding Data

• Saving Data

• Validating Data

• Setting Permissions on Data

• Working with Change Sets

• Extending Queries

Reading Data

You can read individual data items or collections of data items from any data source in your application.

The following example retrieves the customer that is currently selected in a screen.

        partial void RetrieveCustomer_Execute()
        {
            Customer cust = this.Customers.SelectedItem;
            if (cust.ContactName == "Bob")
            {
                //Perform some task on the customer entity.
            }
        }

        Private Sub RetrieveCustomer_Execute()
            Dim cust As Customer = Me.Customers.SelectedItem
            If cust.ContactName = "Bob" Then
                'Perform some task on the customer entity.
            End If
        End Sub

The following example iterates over a collection of customers.

        partial void RetrieveCustomers_Execute()
        {
            foreach (Customer cust in this.DataWorkspace.NorthwindData.Customers)
            {
                if (cust.ContactName == "Bob")
                {
                    //Perform some task on the customer entity.
                }
            }
        }

        Private Sub RetrieveCustomers_Execute()
            For Each cust As Customer In Me.DataWorkspace.NorthwindData.Customers
                If cust.ContactName = "Bob" Then
                    'Perform some task on the customer entity.
                End If
            Next

        End Sub

Navigating Data Relationships

You can read data from related entities. For example, a Customer entity might have a one-to-many relationship with an Orders entity. You could iterate over all orders that have been placed by a customer by using the Orders property of the Customer entity.

The following example iterates over the collection of orders that are related to a customer.

        partial void RetrieveSalesOrders_Execute()
        {
            Customer cust = this.Customers.SelectedItem;

            foreach (Order order in cust.Orders)
            {
                if (order.OrderDate == DateTime.Today)
                {
                    //perform some task on the order entity.
                }
            }
        }

        Private Sub RetrieveSalesOrders_Execute()
            Dim cust As Customer = Me.Customers.SelectedItem
            For Each myOrder As Order In cust.Orders
                If myOrder.OrderDate = Today Then
                    'Perform some task on the order entity.
                End If
            Next
        End Sub

The following example gets the customer who placed a specific order.

        partial void RetrieveCustomer_Execute()
        {
            Order order = this.DataWorkspace.NorthwindData.Orders_Single
                (Orders.SelectedItem.OrderID);

            Customer cust = order.Customer;
            //Perform some task on the customer entity.

        }

        Private Sub RetrieveCustomer_Execute()
            Dim order As Order
            order = Me.DataWorkspace.NorthwindData.Orders_Single _
                (Orders.SelectedItem.OrderID)
            Dim cust As Customer
            cust = order.Customer
            'Perform some task on the order entity.
        End Sub

Reading Data by Executing a Query

You can retrieve queries from the model and then execute them in your code. To view an example, see How to: Retrieve Data from a Query by Using Code.

Updating Data

You can update data for any entity by using code. The following example shows code that runs when a user creates an order in the Order entity in a screen and then clicks the Save button. The code updates a field in the Products entity by using a field in the Order Details entity.

        partial void Orders_Inserting(Order entity)
        {
            foreach (Order_Detail detail in entity.Order_Details)
            {
                detail.Product.UnitsInStock = 
                    (short?)(detail.Product.UnitsInStock - detail.Quantity);
            }
        }

        Private Sub Orders_Inserting(entity As Order)
            For Each detail In entity.Order_Details
                detail.Product.UnitsInStock =
                    detail.Product.UnitsInStock - detail.Quantity
            Next
        End Sub

Note

If your code modifies data from other data sources, you must commit those changes by calling the SaveChanges method of that data source.

Deleting Data

You can delete data by calling the Delete method of any entity. The following example deletes a customer from the NorthwindData data source.

        partial void DeleteCustomer_Execute()
        {
            Customer cust =
                this.Customers.SelectedItem;

            if (Customers.CanDelete)
            {
                cust.Delete();
            }
        }

        Private Sub DeleteCustomer_Execute()
            Dim cust As Customer
            cust = Me.Customers.SelectedItem
            If Customers.CanDelete Then
                cust.Delete()
            End If

        End Sub

Adding Data

The following example adds a new customer to the NorthwindData data source. This example populates the fields that describe the new customer by using information from a contact that was recently added to a SharePoint list. The example calls a query named NewCustomersInSharePoint to determine which contacts in the SharePoint list have not yet been imported to the NorthwindData data source.

        partial void ImportCustomers_Execute()
        {
            foreach (SharePointCustomer spCust in
        this.DataWorkspace.SharePointData.NewCustomersInSharePoint())
            {
                Customer newCust = new Customer();

                newCust.ContactName = spCust.FirstName + " " + spCust.LastName;
                newCust.Address = spCust.Address;
                newCust.City = spCust.City;
                newCust.PostalCode = spCust.PostalCode;
                newCust.Region = spCust.Region;

                //Set the CopiedToDatabase field of the item in SharePoint.
                spCust.CopiedToDatabase = "Yes";
            }
            this.DataWorkspace.SharePointData.SaveChanges();


        }

        Private Sub ImportCustomers_Execute()
            For Each spCust As SharePointCustomer In _
                Me.DataWorkspace.SharePointData.NewCustomersInSharePoint
                Dim newCust As Customer = New Customer()
                With newCust

                    .ContactName = spCust.FirstName & " " & spCust.LastName
                    .Address = spCust.Address
                    .City = spCust.City
                    .PostalCode = spCust.PostalCode
                    .Region = spCust.Region

                    'Set the CopiedToDatabase field of the item in SharePoint.
                    spCust.CopiedToDatabase = "Yes"
                End With

            Next
            Me.DataWorkspace.SharePointData.SaveChanges()



        End Sub

Saving Data

Typically, pending changes are committed to a data source when the user clicks the Save button in a screen. However, you can also commit pending changes by adding code that calls the SaveChanges method of a data source. You must add this code if you want to accomplish either of these tasks:

• Commit changes that you make to data that is located in other data sources.

• Override the Save event of a screen.

Committing Changes That You Make to Data That Is Located in Other Data Sources

The files in which you write custom code have a primary data source. If you add custom code that modifies data from another data source in your LightSwitch solution, you must commit those changes by calling the SaveChanges method of that data source.

The following example shows code that runs when a user creates an order in an Order entity in a screen and then clicks the Save button. The code updates a field in the Products entity by using a field in the Order Details entity. Because the Products entity is located in another data source, this code calls the SaveChanges method of that data source to commit the changes.

        partial void Orders_Inserting(Order1 entity)
        {
            foreach (Order_Detail1 detail in entity.Order_Details)
            {
                detail.Product.UnitsInStock = (short?)
                    (detail.Product.UnitsInStock - detail.Quantity);
            }
            this.DataWorkspace.ProductDataSource.SaveChanges();

        }

        Private Sub Orders_Inserting(entity As Order1)
            For Each detail In entity.Order_Details
                detail.Product.UnitsInStock = detail.Product.UnitsInStock - detail.Quantity
            Next
            Me.DataWorkspace.ProductDataSource.SaveChanges()

        End Sub

Overriding the Save Event of a Screen

You can change the behavior of the Save button on a screen by overriding the Save event. Because you are replacing the behavior of the Save button, your code must call the SaveChanges method when you want to commit pending changes.

The following example overrides the Save event of a customer screen to catch and handle a specific exception that might be thrown if the save operation fails.

        partial void CustomersListDetail_Saving(ref bool handled)
        {
            try
            {
                this.DataWorkspace.SharePointData.SaveChanges();
            }
            catch (DataServiceOperationException ex)
            {
                if (ex.ErrorInfo == "DTSException")
                {
                    this.ShowMessageBox(ex.Message);
                }
                else
                {
                    throw ex;
                }
            }
            handled = true;


        }

        Private Sub CustomersListDetail_Saving(ByRef handled As Boolean)
            Try
                Me.DataWorkspace.SharePointData.SaveChanges()

            Catch ex As DataServiceOperationException

                If ex.ErrorInfo = "DTSException" Then
                    Me.ShowMessageBox(ex.Message)
                Else
                    Throw ex

                End If

            End Try

            handled = True


        End Sub

Validating Data

You can apply custom validation rules to the fields of an entity. You can add custom error messages that appear when users modify the value of properties in ways that do not conform to your validation rules. For more information, see How to: Validate Data

Setting Permissions on Data

By default, all users can view, insert, delete, or update data that appears in a screen. However, you can restrict these permissions by adding code to one of the following methods:

• CanRead

• CanInsert

• CanDelete

• CanUpdate

If you restrict an operation by using these methods, LightSwitch makes the operation unavailable to users who do not have unrestricted permissions. For more information, see How to: Handle Data Events.

The following example enables a user to update customer information if the user has update permission. This code example requires a permissions group named RoleUpdate. For more information about how to add a permissions group to your application, see Enabling Authorization and Creating Permissions.

        partial void Customers_CanUpdate(ref bool result)
        {
            result = this.Application.User.HasPermission(Permissions.RoleUpdate);
        }

        Private Sub Customers_CanUpdate(ByRef result As Boolean)
            result = Me.Application.User.HasPermission(Permissions.RoleUpdate)
        End Sub

By default, LightSwitch calls these methods when a user attempts to view, insert, delete, or update information. You can also call these methods in your custom code before data is read or modified.

Working with Change Sets

You can identify and discard pending changes before they are committed to a data source. The following example shows three user methods that identify and discard pending changes. The UndoAllCustomerUpdates method discards all changes made to all customers. The UndoAllUpdates method discards all changes made to the data source. The UndoCustomerEdit method discards changes made to the currently selected row of data in a customer screen.

        partial void UndoAllCustomerUpdates_Execute()
        {
            foreach (Customer cust in 
                this.DataWorkspace.NorthwindData.Details.
                GetChanges().OfType<Customer>())
            {
                cust.Details.DiscardChanges();
            }
        }

        partial void UndoAllUpdates_Execute()
        {
            this.DataWorkspace.NorthwindData.Details.DiscardChanges();
        }

        partial void UndoCustomerEdit_Execute()
        {
            Customers.SelectedItem.Details.DiscardChanges();
        }

        Private Sub UndoAllCustomerUpdates_Execute()
            For Each Cust As Customer In _
                Me.DataWorkspace.NorthwindData.Details. _
                GetChanges().OfType(Of Customer)()

                Cust.Details.DiscardChanges()

            Next
        End Sub

        Private Sub UndoAllUpdates_Execute()
            Me.DataWorkspace.NorthwindData.Details.DiscardChanges()
        End Sub

        Private Sub UnduCustomerEdit_Execute()
            Customers.SelectedItem.Details.DiscardChanges()
        End Sub

Extending Modeled Queries

If you want to modify a query beyond the capabilities of the Query Designer, you can extend the query by adding code to the PreProcessQuery method of the query. For more information, see How to: Extend a Query by Using Code.