Beginner's Guide to Angular 2 + WebApi

Hello Everyone,

This post is an initial guide for anyone who wants to use Angular 2 to access a .Net WebApi back end to access SQL server database.

This will guide you to do the basic CRUD operations using the WebApi REST service.

Now lets see our requirement.

We have Brands and Models for cars which is having a one to many relationship where one brand will have more than one model. We will be doing the CRUD operations on the models and brands will be inserted directly to database. The image below shows the EF model of our database.

There are two WebApi controllers built using the above entities one for brands and one for models.

Now lets move on to the Angular 2 front end, the image below shows the file structure in VS code.

As you can see we have five component classes each will be representing an user interface except the app.component.ts which will be used as the frame for navigation and other component content will be loaded into this component as this is single page application.

We have a carservice.ts which will be interacting with the WebApi.
There are two classes Car and Brand representing our entities in the backend these will be used to when we send and receive data to the service.

The other files are the default set of files in Angular 2 project, here is brief description of each file.

tsconfig.json	Specifies the root files and the compiler options required to compile the project
systemjsconfig.js	Allows to configure SystemJS to load modules compiled using the TypeScript compiler.
package.json	Used by NPM installer to install the packages
app.module.ts	Defines AppModule, the root module that tells Angular how to assemble the application.

Now lets move on to our user interface of listing the cars.

You can see the code for this component class in the below URL.
https://github.com/guruparan/BlogSample/blob/master/AngularSample1/CarApp/app/allcars.component.ts
If you can see the code here we request all the cars from the carservice class and display it.
Delete option is also added to the same view where a method is called when the delete button is clicked.

The next screen will be the add new car screen

Here we have a drop down of brands which will be loaded when the page loads and we will be redirecting to all cars page when the new car is saved.
You can see the code for this component in the below URL.
https://github.com/guruparan/BlogSample/blob/master/AngularSample1/CarApp/app/addcar.component.ts

The next screen will be edit item,

As you can see the url of the browser it bring in the parameter Id of the car which will be used by to retrieve the car of that id and load the values, other than that this screen is same as the add car screen.

You can see the code for this component in the below URL.
https://github.com/guruparan/BlogSample/blob/master/AngularSample1/CarApp/app/editcar.component.ts

The next screen will be view item.

Again this screen is simple and does not have any actions, it receives the data from the service just like the edit screen using the parameter that is passed via the URL.

You can see the code for this component in the below URL.
https://github.com/guruparan/BlogSample/blob/master/AngularSample1/CarApp/app/viewcar.component.ts

 The most important class of all is the service class and it handles all the requests to the service.
You can see the code for the service class in the below URL.
https://github.com/guruparan/BlogSample/blob/master/AngularSample1/CarApp/app/carservice.ts

Developer Note:
1. This is not 'THE' way to do this but this is also an approach to get started.
2. The templates can be moved to separate html files but kept in the component
3. Bootstrap is used for styling , style can be added separately per component .
4. You can use any other service technology to try this out.

The whole code including the WebApi service can be obtained in the below repository.
https://github.com/guruparan/BlogSample/tree/master/AngularSample1

Happy Coding
-Guru-
CodeProject

Syncing tables using Merge in SQL Server

Hi Readers,

In this post I'll explain how to use the 'Merge' statement in sql server to Sync two tables.
For this we'll need two tables, one will act as the source and the other one will act as the destination.

First create the required tables

CREATE TABLE SourceTable
(
 ID INT IDENTITY PRIMARY KEY,
 Name VARCHAR(100)
);

CREATE TABLE DestinationTable
(
 ID INT PRIMARY KEY,
 Name VARCHAR(100)
);

Then insert some data to the source table

INSERT INTO SourceTable VALUES('George');
INSERT INTO SourceTable VALUES('Saman');
INSERT INTO SourceTable VALUES('Kamal');
INSERT INTO SourceTable VALUES('Nimal');
INSERT INTO SourceTable VALUES('Sunil');
INSERT INTO SourceTable VALUES('Mark');
INSERT INTO SourceTable VALUES('Bill');
INSERT INTO SourceTable VALUES('Paul');
INSERT INTO SourceTable VALUES('Sean');
INSERT INTO SourceTable VALUES('Shane');
INSERT INTO SourceTable VALUES('Elvis');

Now we'll come to the Merge Statement

MERGE DestinationTable D
USING SourceTable S
ON D.ID=S.ID 
WHEN MATCHED AND D.Name!=S.Name THEN UPDATE SET D.Name=S.Name
WHEN NOT MATCHED BY SOURCE THEN DELETE
WHEN NOT MATCHED BY TARGET THEN INSERT(ID,Name) VALUES(S.ID,S.Name);

As this is the first time , all the rows will be inserted
So we'll do some changes to the table (insert, update , delete)

DELETE FROM SourceTable WHERE Name='Bill';
UPDATE SourceTable SET Name='Mark2' WHERE Name= 'Mark';
INSERT INTO SourceTable VALUES('Alex');

Now if we run the merge statement again we can see that both the tables are synced.
You can schedule it to a job so that the tables will be in sync.

Connecting Windows 8 Store Application with SQL Server using WCF - Part 2

This post is all about creating the application

First we need to add the service reference

Give the Namespace as DataProvider

Now we'll go to the coding Here i have added the coding for the buttons Both button click events will have to be async.

   private async void btnGetStudent_Click(object sender, RoutedEventArgs e)
    {
        //Create a client object to access the service
        DataProvider.DataProviderClient serviceClient = new DataProvider.DataProviderClient();

        //Get the student id and convert it to integer
        int ID = Convert.ToInt32(txtStudentID.Text);

        //Get the student via the service
        DataProvider.Student student = await serviceClient.getStudentAsync(ID);

        //The service will return null if the student is not available
        //If student is not found show a message
        if (student==null)
        {
            Windows.UI.Popups.MessageDialog message = new Windows.UI.Popups.MessageDialog("Student not found");
            message.ShowAsync();
            return;
        }

        //Set the values to the appropriate labels
        lblFirstName.Text = student.FirstName;
        lblLastName.Text = student.LastName;
    }

    private async void btnGetAllStudents_Click(object sender, RoutedEventArgs e)
    {
        //Create a client object to access the service
        DataProvider.DataProviderClient serviceClient = new DataProvider.DataProviderClient();

        //Get the student objects from the service
        ObservableCollection students = await serviceClient.getStudentsAsync();

        //Add the objects to the list view
        foreach (DataProvider.Student student in students)
        {
            lstStudents.Items.Add(student.FirstName+" "+student.LastName);
        }
    }

Download Code

Please Leave a comment about the post.