Performing Data-Related Tasks by Using Code

  • Article

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:

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.

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

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

The following example iterates over a collection of customers.

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

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

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.

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

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

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

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

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.

}

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.

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

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

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.

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

End Sub

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

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

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.

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

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();


}

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.

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

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();

}

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.

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

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;


}

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 in a LightSwitch Application

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 in LightSwitch.

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

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

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.

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

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();
}

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.