Angular2 is an updated framework for dynamic Web apps, built upon and expanding principles of Angular JS. The CData API Server lets you generate a REST API for your databases, both on-premises and cloud-based. This article will walk through setting up the CData API Server to create a REST API for a SQL Server database and creating a simple single-page application (SPA) that has live access to database data. The SPA will dynamically build and populate an HTML table based on the database data. While the article steps through most of the code, you can download the sample Angular2 project from CData Software to see the full source code and test the functionality for yourself.
Setting Up the API Server
If you have not already done so, you will need to download the CData API Server. Once you have installed the API Server, you will need to run the application, configure the driver to connect to your data (the instructions in this article are for the included sample database), and then configure the driver to create a REST API for any tables you wish to access in your SPA.
Enable CORS
- Click the checkbox to "Enable cross-origin resource sharing (CORS)".
- Either click the checkbox to "Allow all domains without '*'" or specify the domain(s) that are allowed to connect in Access-Control-Allow-Origin.
- Set Access-Control-Allow-Methods to "GET,PUT,POST,OPTIONS".
- Set Access-Control-Allow-Headers to "authorization".
- Click Save Changes.
Configure Your Connection to a SQL Server Database
To configure the API Server to connect to your SQL Server data, you will need to navigate to the Connections tab on the SETTINGS page. Once there, click Add Connection. At this point, you can name your connection, select SQL Server as the database, and fill in the Database field with the full path to your SQL Server database.
Configure a User
Next, create a user to access your SQL Server data through the API Server. You can add and configure users on the Users tab of the SETTINGS page. Since we are only creating a simple SPA for viewing data, we will create a user that has read-only access. Click Add, give the user a name, select GET for the Privileges, and click Save Changes.
As you can see in the screenshots, we already had a user configured with read and write access. For this article, we will access the API Server with the read-only user, using the associated authtoken.
Accessing Tables
Having created a user, we are ready to enable access to SQL Server tables. To enable tables, click the Add Resources button on the Resources tab of the SETTINGS page. Select the data connection you wish to access and click Next. With the connection selected, you can begin enabling resources by clicking on a table name and clicking Next. You will need to add resources one table at a time. In this example, we enabled the dbo.albums table, renaming it to albums.
Sample URLs for the REST API
Having configured a connection to the SQL Server database, created a user, and added resources to the API Server, we now have an easily-accessible REST API based on the OData protocol for those resources. Below, you will see a list of tables and the URLs to access them. For information on accessing the tables, you can navigate to the API page for the API Server. For the URLs, you will need the address and port of the API Server. Since we are working with Angular2, we will append the @json parameter to the end of URLs that do not return JSON data by default.
| Table | URL |
|---|---|
| Entity (table) List | http://address:port/api.rsc/ |
| Metadata for table albums | http://address:port/api.rsc/albums/$metadata?@json |
| albums data | http://address:port/api.rsc/albums |
As with standard OData feeds, if you wish to limit the fields returned, you can add a $select parameter to the query, along with other standard URL parameters, such as $filter, $orderby, $skip, and $top.
Building a Single Page Application
With the API Server setup completed, we are ready to build our SPA. We will walk through the source files for the SPA contained in the .zip file, making note of any relevant sections of code as we go along. Several of the source files are based loosely on the Angular2 tutorial from angular.io.
index.html
This is the home page of our SPA and the source code mainly consists of script elements to import the necessary Angular2 libraries.
app/main.ts
This TypeScript file is used to bootstrap the App.
app/rxjs-extensions.ts
This TypeScript file is used to import the necessary Observable extensions and operators.
app/app.module.ts
This TypeScript file is used to create a class that can be used in other files to import the necessary modules to create and run our SPA.
app/app.component.css
This file creates CSS rulesets to modify the h1, h2, th, and td elements in our HTML.
app/app.component.html
This file is the template for our SPA. The template consists of a title, a drop-down to select an available table, a drop-down to (multi) select columns in the table to be displayed, a button to retrieve the data, and a table for the data. Different sections are enabled/disabled based on criteria in *ngIf directives and the menus and table are built dynamically based on the results of calls to the API Server, using the *ngFor directive to loop through the returned data.
All of the calls to the API Server and assignment of values to variables are made in the AppComponent and AppService classes.
<h1>{{title}}</h1>
<br>
<label>Select a Table</label>
<br>
<select [(ngModel)]="selectedTable" (change)="tableChanged()">
<option *ngFor="let sel_table of availableTables" [value]="sel_table">{{sel_table}}</option>
</select>
<br>
<br>
<label>Select Columns</label>
<br>
<select *ngIf="selectedTable" [(ngModel)]="selectedColumns" (change)="columnsChanged()" multiple>
<option *ngFor="let sel_column of availableColumns" [value]="sel_column">{{sel_column}}</option>
</select>
<br>
<br>
<button *ngIf="selectedTable && selectedColumns" (click)="dataButtonClicked()">Get [{{selectedTable}}] Data</button>
<br>
<br>
<table *ngIf="selectedTable && selectedColumns">
<tr>
<th *ngFor="let column of selectedColumns">{{ column }}</th>
</tr>
<tr *ngFor="let row of tableData">
<td *ngFor="let column of selectedColumns">{{ row[column] }}</td>
</tr>
</table>
app/app.service.ts
This TypeScript file builds the service for retrieving data from the API Server. In it, we have functions for retrieving the list of tables, retrieving the list of columns for a specific table, and retrieving data from a table. We also have a class that represents the metadata of a table as returned by the API Server.
API_Table
The metadata returned by the API Server for a table includes the table's name, kind, and URL. We only use the name field, but pass the entire object in the event that we need the other information if we decide to build upon our SPA.
export class API_Table {
name: string;
kind: string;
url: string;
}
constructor()
In the constructor, we create a private instance of the Http class and set the Authorization HTTP header based on the user/authtoken credentials for the user we created earlier. We then include this header in our HTTP requests.
constructor(private http: Http) {
this.headers.append('Authorization', 'Basic ' + btoa(this.userName+":"+this.authToken));
}
getTables()
This function returns a list of the tables. The list is retrieved from the API Server by making an HTTP GET request, including the Authorization header, to the base URL for the API Server: http://localhost:8153/api.rsc
getTables(): Promise<API_Table[]> {
return this.http.get(this.baseUrl, {headers: this.headers})
.toPromise()
.then(response => response.json().value )
.catch(this.handleError);
}
getColumns()
This function returns a list of columns for the table specified by tableName. Since the $metadata endpoint returns XML formatted data by default, we pass the @json parameter in the URL to ensure that we get JSON data back from the API Server. Once we have the JSON data, we can drill down to retrieve the list of column names.
getColumns(tableName: string): Promise<string[]> {
return this.http.get(`${this.baseUrl}/${tableName}/$metadata?@json`, {headers: this.headers})
.toPromise()
.then(response => response = response.json().items[0]["odata:cname"] )
.catch(this.handleError);
}
getTableData()
This function returns the rows of data for the specified table and columns. We pass the tableName in the URL and then pass the list of columns (a comma-separated string) as the value of the $select URL parameter.
getTableData(tableName:string, columnList: string): Promise<Object[]> {
return this.http.get(`${this.baseUrl}/${tableName}/?$select=${columnList}`, {headers: this.headers})
.toPromise()
.then(response => response = response.json().value )
.catch(this.handleError);
}
app/app.component.ts
In this TypeScript file, we have defined the functions that react to the events in the SPA; within these functions, we call the functions from the AppService and use the results to populate the various elements of the SPA. These functions are fairly straightforward, assigning values to the different variables as necessary.
ngOnInit()
In this function, we call the getTables function from our AppService. Since getTables returns the raw data objects from our API Server table query, we need to push only the name field from each result into the array of available tables and not push the entire object.
ngOnInit(): void {
this.appService
.getTables()
.then( tables => {
for (let tableObj of tables) {
this.availableTables.push( tableObj.name )
}
});
}
tableChanged()
This function is called whenever the user selects a different table from the drop-down menu in the SPA. The function makes a call to the API Server to retrieve the list of columns for the given table, which populates another drop-down menu.
tableChanged(): void {
this.appService
.getColumns(this.selectedTable)
.then( columns => this.availableColumns = columns );
this.selectedColumns = [];
}
columnsChanged()
This function is called whenever the user changes which columns are selected from the drop-down menu. It simply clears the table data so that we do not display an empty table if the columns selected after the button is clicked are different from those originally selected.
columnsChanged(): void {
this.tableData = [];
}
dataButtonClicked()
This function serves to join the array of selected columns into a comma-separated string, as required by the $select parameter in an OData query, and pass the table name and list to the getTableData function in the AppService. The resulting data is then used to populate the HTML table.
dataButtonClicked(columnList: string): void {
columnList = this.selectedColumns.join(',');
this.appService
.getTableData( this.selectedTable, columnList )
.then( data => this.tableData = data );
}
Running the Single Page Application
With our connection to data configured and the source files for the SPA reviewed, we are now ready to run the Single Page Application. You will need to have node.js and npm installed on your machine in order to run the SPA. Included in the sample download is an instance of http-server, which will create a light-weight server on your machine to run the SPA. To start the server, you simply need to run the start.sh script in the root directory of the SPA:
> bash .\start.sh
When the SPA launches, you will see the title and a drop down menu to select a table. The list of tables is retrieved from the API Server and includes all of the tables you added as resources when configuring the API Server.
With a table selected, the drop-down, multiselect menu for columns appears, allowing you to select the columns you wish to see in your table. You can see that as you select columns, the table headers appear.
Once the table and columns are selected, you can click the Get
