Data Sheet

Starting with release 7.0.7.0, web applications created with Premium or Unlimited edition include a preview of the second generation data sheet view. The new implementation supports horizontal and vertical scrolling, column resizing, and freezing. In addition, applications based on Microsoft SQL Server or Oracle also support hierarchical presentation of data.

The preview does not yet support all capabilities available in the original data sheet. The missing features will be released as soon as they become production-ready. Therefore, the second generation data sheet is disabled by default. If you would like to take it for a spin, make sure to regenerate your web app and follow the instructions below.

Start the web application generator. Create a sample Northwind web app. When generation is complete, click on the project name in the start page of the generator. Then, click Develop to open the project in Visual Studio.

In the Solution Explorer, right-click on ~\App_Code folder, and press Add | Add New Item…

Select Class from the list, and assign a name. Press Add to create the file.

Replace the existing code base with the following:

C#:

namespace MyCompany.Data
{
    public partial class ControllerUtilities
    {
        public override bool SupportsScrollingInDataSheet
        {
            get { return true; }
        }
    }
}

Visual Basic:

Namespace MyCompany.Data
    Partial Public Class ControllerUtilities
        Public Overrides ReadOnly Property SupportsScrollingInDataSheet As Boolean
            Get
                Return True
            End Get
        End Property
    End Class
End Namespace

Save the file and run the web app. Navigate to the Customers page. On the action bar, press Action | Show in Data Sheet.

The grid view will render the second generation data sheet.

The second generation data sheet will be enabled by default when the feature set of the previous implementation is replicated.

To use the original data sheet view implementation, make sure that the SupportsScrollingInDataSheet property of class ControllerUtilities returns false.

Database views are composed of a result set for a query accessible as a virtual table. This query is computed from other tables in the database. Code On Time web application generator creates read-only data controllers from views. It is possible to add update and insert capabilities to these controllers.

Let’s create a new database view in the Northwind database based on Employees table, add this view to a project, and enable editing.

Creating the View

Start SQL Server Management Studio. In the Object Explorer, right-click on Databases / Northwind and press New Query.

Paste in the following script:

create view EmployeeList as
select 
    Employees.EmployeeID, 
    Employees.LastName + ', ' + Employees.FirstName FullName,
    Employees.Title, 
    Employees.TitleOfCourtesy,
    (select COUNT(*) 
     from Orders 
     where EmployeeID = Employees.EmployeeID
    ) NumberOfOrders,
    Employees.BirthDate,
    Employees.HireDate,
    Employees."Address",
    Employees.City,
    Employees.Region,
    Employees.PostalCode,
    Employees.Country,
    Employees.HomePhone,
    Employees.Extension
from Employees

The script will create a view composed of several columns from the Employees table. In addition, the FirstName and LastName columns will be concatenated into the FullName column, and a count of orders will be displayed in NumberOfOrders column.

On the toolbar, press Execute to run the script.

Adding View to Project

Start the web application generator. Click on the project name, and press Refresh. Check the box next to the view dbo.EmployeeList, and confirm the refresh.

On the Summary page, press Design to activate the Project Designer.

Defining Primary Key

The new controller is based on a database view and does not have a primary key explicitly defined. This means that individual rows cannot be selected or edited. Specifying a primary key from within the Project Designer will enable this functionality.

In the Project Explorer, switch to the Controllers tab. Double-click on EmployeeList / Fields / EmployeeID* (Int32, read-only) node.

Mark the field as a primary key.

Press OK to save the field.

Defining Base Table Name

In order to ensure that the Employees table will be updated when a user changes a record in the EmployeeList controller, the command must have the Table Name property explicitly specified.

Double-click on EmployeeList / Commands / command1 (Text) node.

Specify a table.

Press OK to save changes.

Next, right-click on EmployeeList / Commands / command1 (Text) and select Reset to Baseline. This will ensure that any changes to the database view will be reflected in the command text when the data controller is refreshed.

Viewing the Results

On the toolbar, press Browse. Navigate to Reports | Employee List page.

Click on Actions | Show in Data Sheet to activate data sheet mode. Double-click a cell and change the value.

Press Enter key to save.

Navigate to the Employees page. Note that the corresponding row of Employees database table has been updated.