Full Trust European Hosting

BLOG about Full Trust Hosting and Its Technology - Dedicated to European Windows Hosting Customer

Europe SQL Hosting - HostForLIFEASP.NET :: How To Update Tables With Joins In SQL?

clock June 30, 2021 08:53 by author Peter

In this blog, we will see how to update tables with joins in SQL.

When we are dealing with the data we need to store that data in the database like MySQL, Oracle, etc. In daily practice, we need to create tables, and account for alterations that may be lead us to update the table's data.

We can use iteration While loop or a Cursor for the same purpose but in this blog, we will be updating tables with joins in SQL.

Let's start.
First of all, we need a database (example: TestingDatabase) with two tables (example: Countries and second one States) schema as shown below,

Table: Countries 

Table: States

Now we have two tables with a common countries id column, we added a column named Countrysortname in the states table using an alter command,


We can apply inner join on country_id.
Table schema after adding a column.

Table: States

Now, we are ready to update the table States using INNER JOIN.

We need to write a select command first to verify what we are going to update as shown below,

SELECT [L.column_name],
FROM   table_name1 L
       JOIN table_name2 R
         ON L.column_name = R.column_name

SET    [L.Column_name] = [R.Column_name]
FROM   table_name1 L
       JOIN table_name2 R
                    ON L.column_name = R.column_name


SELECT ct.id,ct.name,ct.sortname,st.id,  st.countrysortname
FROM   states st
INNER JOIN countries ct
ON ct.id = st.country_id

Update Command

SET    st.countrysortname = ct.sortname
FROM   states st
INNER JOIN countries ct
ON ct.id = st.country_id

With Subquery

SELECT l.id,

FROM   states l
       INNER JOIN (SELECT st.id,
                   FROM   states st
                          INNER JOIN countries ct
                                  ON ct.id = st.country_id)r ON l.id = r.id

Update Command

SET    l.countrysortname = r.sortname
FROM   states l
       INNER JOIN (SELECT st.id,
                   FROM   states st
                          INNER JOIN countries ct
                                  ON ct.id = st.country_id)r
               ON l.id = r.id
WHERE  l.id = r.id

Hope this will help you.

AngularJS Hosting Europe - HostForLIFE.eu :: Commonly Used Angular Commands

clock June 29, 2021 07:51 by author Peter

In this article, we will see what are the commonly used commands in an Angular project with the explanation. These are very useful when it comes to Angular applications.

1) To get the npm version,
npm -v

2) To get the node version,
node -v

3) To get the Angular version,
ng v

4) To get the Jasmine version,
jasmine -v

5) To get the Karma version,
karma --version

6) To install Angular CLI,
npm install @angular/cli -g
npm install @angular/cli

7) To install the next version of Angular CLI,
npm install @angular/cli@next

8) To get help in the terminal,
ng help

9) To create a new project in Angular,
ng new project-name

10) To skip external dependencies while creating a new project,
ng new project-name --skip-install

11) To run the Angular project,
ng serve (or) npm start (or) ng serve --force

12) Dry Run,
ng new project-name --dry-run

13) To create a new component in the Angular Project,
ng generate component component-name
ng g c component-name

14) To avoid creating a new folder while creating a new component,
ng generate component component-name --flat

15) To create a build,
ng build

16) To create a build for a specific environment,
ng build --prod

17) To run test cases,
ng test

18) To run the end-to-end test,
ng e2e

19) For Angular Documentation,
ng doc

20) To change the angular-cli.json config file,
ng set

21) To create a directive in Angular,

ng generate directive directive-name
ng g d directive-name

22) To create a service in Angular,
ng generate service service-name
ng g s service-name

23) To create a class,
ng generate class class-name
ng g cl class-name

24) To create an interface,
ng generate interface interface-name
ng g i interface-name

25) To create a pipe,
ng generate pipe pipe-name
ng g p pipe-name

26) To create enum,
ng generate enum enum-name
ng g e enum-name

27) To create a module,
ng generate module module-name
ng g m module-name

28) To create a spec file for the module,
ng g m module-name --spec true -d

29) To create a module with routing,
ng g m module-name --routing

30) To create a guard to the route,
ng g guard guard-name

31) To remove node_modules,
rm -rf node_modules

32) To uninstall Angular CLI,
npm uninstall --save-dev @angular/cli
npm uninstall -g angular-cli @angular/cli

33) To install the latest version of Angular CLI,
npm install --save-dev @angular/cli@latest

34) To update Angular CLI,
ng update @angular/cli
ng update @angular/core

35) To clean cache,
npm cache clean

36) To install TypeScript latest version,
npm install -g typescript@latest

37) To install Jasmine/Karma latest version,
npm install -g karma-jasmine@latest

38) To install TypeScript specific version,
npm install typescript@version-number

39) To install Jasmine specific version,
npm install -g jasmine@version-number

40) To install Karma specific version,
npm install -g karma@version-number
To update Angular versions

Steps to update particular Angular version on the current project,

Execute these commands,

ng update @angular/core@8 @angular/cli@8 --allow-dirty
npm install
git commit -a -m "Upgrade to the latest version of Angular 8"
ng update @angular/core @angular/cli --next
{ng update @angular/core@9 @angular/cli@9 --allow-dirty}
npm install
git commit -a  -m "Upgrade to Angular 9"
ng update @angular/material --next --force
npm i @angular/[email protected]

Steps to update the latest Angular version,

npm uninstall -g @angular-cli
npm cache clean
rm -rf node_modules
npm install
npm install -g @angular/cli@latest
ng update @angular/cli @angular/core
ng update --all --force
npm install --save @angular/animations@latest @angular/cdk@latest @angular/common@latest @angular/compiler@latest @angular/core@latest @angular/flex-layout@latest @angular/forms@latest @angular/http@latest @angular/material@latest @angular/platform-browser@latest @angular/platform-browser-dynamic@latest @angular/router@latest core-js@latest zone.js@latest rxjs@latest rxjs-compat@latest
npm install --save-dev @angular-devkit/build-angular@latest @angular/compiler-cli@latest @angular/language-service @types/jasmine@latest @types/node@latest codelyzer@latest karma@latest karma-chrome-launcher@latest karma-cli@latest karma-jasmine@latest karma-jasmine-html-reporter@latest jasmine-core@latest jasmine-spec-reporter@latest protractor@latest tslint@latest rxjs-tslint@latest webpack@latest

Europe mySQL Hosting - HostForLIFEASP.NET :: MySQL CASE Expression

clock June 22, 2021 08:25 by author Peter

In this tutorial, I am going to explain CASE expressions in MySQL with examples. This article will cover the following topics. Let’s see.
    Simple CASE Expression
    Searched CASE Expression
    Difference between SIMPLE and SEARCHED CASE Expressions

In MySQL, the case expression shows multiple conditions. An expression is the compatible aggregated type of all return values, but also depends on the context. If we have to use it in a numeric context, the result is returned as a decimal, real, or integer value. You can use CASE expression anywhere in a query like SELECT, WHERE, or ORDER BY clause.

Note that, MySQL CASE Expressions are different from MySQL CASE Statements. MySQL CASE Statements are only used in Stored Procedures, Functions, Events, and Triggers whereas MySQL CASE Expressions are used in SELECT Queries.
The CASE expression has two forms
    Simple CASE Statement
    Searched CASE Statement

First, let's create a database with a few tables containing some dummy data. Here, I am providing the database with the tables containing the records, on which I am showing you the various examples. Let's see.
    USE MySQL_CASEExpressions;  
    CREATE TABLE Bookorder(  
      BookNumber INT NOT NULL,  
      orderDate datetime NOT NULL,  
      shippedDate DATE DEFAULT NULL,  
      Status VARCHAR(50),  
      OrderAcknowledgeDate datetime,  
      PRIMARY KEY(BookNumber)  

SIMPLE CASE Expressions
In the Simple Case Expressions, when “case_value” matches with its respective “value”, then it returns the corresponding “result” value. But, if the “case_value” does not match with any “value” then, it returns the “result_value”.
CASE <case_value>
WHEN <value1> THEN <result1>
WHEN <value2> THEN <result2>...
ELSE <result_value> END;

Example 1

    SELECT CASE 10 * 2 WHEN 25 THEN 'Incorrect'  
    WHEN 40 THEN '40 Incorrect'  
    ELSE 20 END AS CASE_Result;  

Example 2
    SELECT CASE 5 * 5 - 5 / 5 + 6 WHEN 30 THEN "TRUE"  
    ELSE "FALSE"  
    END AS "Results";

Example 3
SELECT CASE 10 * 2  
WHEN 20 THEN '20 correct'  
WHEN 30 THEN '30 correct'  
WHEN 40 THEN '40 correct'  
END AS Result; 

Example 4
    SELECT BookNumber, orderDate,  
    CASE Status  
    WHEN "Done"  
    THEN "Product_Shipped"  
    WHEN "In Progress"  
    THEN "Product_Not_Shipped"  
    END AS ProductStatus  
    FROM mysql_caseexpressions.bookorder; 

Example 5 – With Aggregate Function
    SELECT SUM(CASE Status WHEN 'Done'  
        THEN 1 ELSE 0 END) AS 'Product_Shipped',  
      SUM(CASE Status WHEN 'In Progress'  
        THEN 1 ELSE 0 END) AS 'Product_Not_Shipped',  
      COUNT( * ) AS TotalOrders  
    FROM Bookorder;

In the Searched Case Expressions, CASE evaluates the “expressions” values that are specified in the WHEN clause and returns the corresponding “result_set” value. But, if any value is not satisfied by the corresponding conditions then, it returns the “result_value” that is specified in the ELSE clause.
WHEN <expression1> THEN <result_set1>
WHEN <expression2> THEN <result_set2> …
ELSE <result_value>

Example 1
    SELECT CASE WHEN 10 * 2 = 25 THEN 'Incorrect'  
    WHEN 10 * 2 = 40 THEN '40 Incorrect'  
    ELSE "Should be 10*2=20"  

Example 2
    SELECT CASE WHEN 5 * 5 - 5 / 5 + 6 = 30 THEN "TRUE"  
    ELSE "FALSE"  

Example 3
    SELECT CASE WHEN 10 * 2 = 20 THEN '20 correct'  
    WHEN 10 * 2 = 30 THEN '30 correct'  
    WHEN 10 * 2 = 40 THEN '40 correct'  

Example 4
    SELECT BookNumber, orderDate,  
    CASE WHEN Status = "Done"  
    THEN "Product_Shipped"  
    WHEN Status = "In Progress"  
    THEN "Product_Not_Shipped"  
    END AS ProductStatus  
    FROM mysql_caseexpressions.bookorder; 

Example 5 – With Aggregate Function
    SELECT SUM(CASE WHEN Status = 'Done'  
        THEN 1 ELSE 0 END) AS 'Product_Shipped',  
      SUM(CASE WHEN Status = 'In Progress'  
        THEN 1 ELSE 0 END) AS 'Product_Not_Shipped',  
      COUNT( * ) AS TotalOrders  
    FROM Bookorder; 

Difference between SIMPLE and SEARCHED CASE Expressions
Example 1
    SELECT CASE WHEN 10 * 2 = 20 THEN '20 correct'  
    WHEN 10 * 2 = 30 THEN '30 correct'  
    WHEN 10 * 2 = 40 THEN '40 correct'  
    CASE 10 * 2  
    WHEN 20 THEN '20 correct'  
    WHEN 30 THEN '30 correct'  
    WHEN 40 THEN '40 correct'  

Example 2
    SELECT BookNumber, orderDate,  
    CASE Status  
    WHEN "Done"  
    THEN "Product_Shipped"  
    WHEN "In Progress"  
    THEN "Product_Not_Shipped"  
    END AS ProductStatus  
    FROM mysql_caseexpressions.bookorder;  
    SELECT BookNumber, orderDate,  
    CASE WHEN Status = "Done"  
    THEN "Product_Shipped"  
    WHEN Status = "In Progress"  
    THEN "Product_Not_Shipped"  
    END AS ProductStatus  
    FROM mysql_caseexpressions.bookorder; 


AngularJS Hosting Europe - HostForLIFE.eu :: Mocking A REST API For Your Front-End Application

clock June 18, 2021 08:47 by author Peter

In this article, we'll talk about how to mock a REST API for your front-end application which you can easily consume in Angular, React, and Vue Js.

We're going to use open source packages, JSON-server, and faker.js.

In this article, we are setting up mocking a REST API for Angular applications.

Here we assume that you are already aware of how to set up the Angular project.

After setting up the Angular project, a question arises: Why we are mocking a back-end for our front-end application, or in other words, what's the need for that?

Let's talk about that in detail.

Nowadays modern web development applications involve multiple developers working separately in front-end and back-end applications. A few challenges that might arise are coordination between back-end developers and front-end development teams, there is also a chance that the backend is not ready and frontend developers get stuck. Here, JSON-Server plays an important role that this tool allows you to spin up a REST API server with fully-working API with zero codings.

Let's now mock up a REST API using JSON-server.

Step 1

Install JSON-Server inside your Angular application
npm install json-server  --save

After Installing JSON-server set up data objects.

Step 2

Create a data.json file inside a database folder.

Step 3

Inside the data.json file create an object.
  "products" : []


Now we need data for the object that you created that will return from endpoints.

Here we are going to use Faker.js.

Faker.js is the tool for generating a large amount of realistic data in no time.

Install Faker inside your angular application.

npm install faker --save

Create a separate file fakedata.js.

Add the following script inside fakedata.js,

var faker = require('faker');

var database = { products: []};

for (var i = 1; i<= 500; i++) {
    id: i,
    name: faker.commerce.productName(),
    description: faker.lorem.sentences(),
    price: faker.commerce.price(),
    imageUrl: "https://source.unsplash.com/1600x900/?product",
    quantity: faker.random.number()


Now we need to run this so that we can use this data as an endpoint.

Let's add the script for data and JSON-server into the package.json file,
  "name": "angularapp",
  "version": "0.0.0",
  "scripts": {
    "ng": "ng",
    "start": "ng serve",
    "build": "ng build",
    "watch": "ng build --watch --configuration development",
    "generate": "node ./fakedata.js > ./database/data.json",
    "server": "json-server --watch ./database/data.json",
    "test": "ng test"

Now, open your terminal, and let’s create some data for our REST API by running the below command,

 npm run generate

In the above screenshot, you will see the data.json file contains data.

Final step, let's run the REST API using the following command into your terminal,

npm run server

We will see the output in the terminal, Now the REST API can access using the URL which shows in the terminal.


and Get Resources on,


You can access all API Endpoints like GET, POST, PUT, PATCH, DETELE with angular HttpClient, and you can also use page and limit parameters to get data by pages, by setting up the header of the HTTP response.

Europe mySQL Hosting - HostForLIFEASP.NET :: Create A Linked MySQL To SQL Server

clock June 14, 2021 08:06 by author Peter
While working on one of my projects, I came across a scenario where I had a database of the client's that was provided by some other provider through their application. I had to get data from that database, and using that data I had to do some calculations on that and store the results in my database. There were two scenarios in this case:

  1. I could fetch all the required tables and data from that database and create my own database and dump those details into mine and then use it.

  2. I could create a link between my database and the existing database, so that I could fire queries directly on that database.

    Using the first approach will not give me updated records at any point of time but my scenario was real time data. So I thought of using a second approach. This approach gave me real time data as I was querying the existing data and using it. But there is one drawback: It might be somewhat slow. In this article I will explain how to create a linked server
    in SQL Server. Here I will be using ODBC Driver to fetch data from MYSQL database.

    The following are the steps to connect a MySQL database to a SQL Server:

    First of all we need to install the appropriate MySQL ODBC Driver based on the operating system from the below link. After the driver has been installed go to Control Panel, Administrative Tools, then Data Sources(ODBC). After that click System DSN. Now Press Add Button .

    Select MYSQL Driver Listed(MYSQL(ODBC) 5.3 ANSI Driver) and click finish. This will open up MySQL Configuration window.

    Fill Data Source Name as MYSQL (this can be anything). TCP/IP Server as localhost. Port as 3306(default port for mysql), User Name-root, Password -your database password and click test. This will show a success message. Now select database and click ok.

We are done with MYSQL System DSN. Now we will set Linked Server to MYSQL in SQL Server. Open SQL Server, Server Objects, then Linked Server. Right Click on Linked Servers, then Add New Linked Server.

This will Open up a Linked Server Properties Dialog. Fill Linked Server as MYSQL_LINKED, select Provider as Microsoft OLEDB Provider For ODBC Drivers. Product Name as MySQl, DataSource as MySQL_Linked (whatever name is given while creating DSN). Provider String as,

DRIVER=(MySQL ODBC 5.2 ANSI Driver);SERVER=localhost;PORT=3306;DATABASE=databasename; USER=username;PASSWORD=password;OPTION=3;  

Leave location as blank and Catalog as database name (in mysql). 

Drill down to Server Object, then Linked Servers, Providers, right-click MSDASQL, and select “Properties”. The Provider Options for Microsoft OLE DB Provider for ODBC Drivers dialog box will open allowing you to configure several options. Ensure the following four options are checked:
Nested queries

Level zero only

Allow inprocess

Supports ‘Like’ Operator

All other options should be unchecked. When done, click “OK”.

In addition to this, you can enable provider options on the SQL OLEDB, In my case I select the Dynamic Parameter and Allow in process.
We are done with setting up a linked server. Now we have to test it by firing some basic queries. There are three ways by which we can query a linked server.

    Open Query
    Select using 4 part notation.
    Execute Function

Open Query function requires 2 parameters: 1)Linked Server Name, 2)Query
    select * from openquery (MYSQL_LINKED, 'select * from test.user_details');  
    INSERT OPENQUERY (MYSQL_LINKED, 'select name,address from test.user_details') VALUES ('Peter','Amsterdam');  
    UPDATE OPENQUERY (MYSQL_LINKED, 'select name from test.user_details WHERE user_id = 100006413534648') SET name = 'Scott';  
    DELETE OPENQUERY (MYSQL_LINKED, 'select name from test.user_details WHERE user_id = 100006413534648')  


For Update/Delete on Linked Server we need to set RPC and RPC OUT properties of Linked Server as true (Right click Linked Server, Properties, Server Option Tab, RPC-True, then set RPC OUT -True.
Part Notation: We can also execute queries on linked server using four-part notations like:

SELECT * FROM linkedserver...tablename but for this we need to change MSDASQL Provider property. Check the box that says “level zero only” in providers.
    select * from MYSQL_LINKED...user_details  
    INSERT into MYSQL_LINKED...user_details(name,address) VALUES ('Rajeev','Bangalore');  
    UPDATE MYSQL_LINKED...user_details set name='Akash' where USER_ID='100006413534649';  
    DELETE from MYSQL_LINKED...user_details where USER_ID='100006413534649';  

Execute Function can also be used for querying linked server.
    EXECUTE('delete from test.user_details WHERE user_id = 100006413534647') AT MYSQL_LINKED 

Europe SQL Hosting - HostForLIFEASP.NET :: What Is Dynamic SQL?

clock June 8, 2021 06:53 by author Peter

Dynamic SQL is about creating and running SQL Statements at run-time. We can store a SQL statement inside a variable and execute that statement.

It is for the most part used to compose broadly useful and adaptable projects where the SQL Statements will be made and executed at run-time in light of the prerequisite.
Why do we need Dynamic SQL?

Dynamic SQL is very helpful to dynamically set the filters, columns, and table names.
Downsides of Dynamic SQL

It's riskier because the SQL statements aren't parsed until runtime, so it's more difficult to catch simple syntax errors. Also, many attempts at dynamic SQL run into performance problems, and the complexity of simply writing dynamic SQL gives a negative impression on dynamic SQL.
How we Achieve Dynamic SQL?

We will achieve the Dynamic SQL by String concatenation and exec statement in SQL.

String concatenation means appending different strings together in strings that are the varchar data type.

Declare a varchar variable and append the string checking conditions and use EXEC Statement to execute that varchar variable.

Simple Dynamic SQL
Declare @sqlQry varchar(4000)
SET @sqlQry='Select empid,empname,age,salary,dob from tblEmployee'

Dynamic SQL Using Table Name as Dynamic

Declare @sqlQry varchar(4000)
Declare @tblName varchar(20)='tblEmployee'
SET @sqlQry='Select empid,empname,age,salary,dob from '+ @tblName


Dynamic SQL Using Column Name as Dynamic

Declare @sqlQry varchar(4000)
Declare @tblcol varchar(50)='empid,empname,age,salary,dob'
SET @sqlQry='Select '+ @tblcol +' from tblEmployee '


Dynamic SQL Using Table Using Filters

Declare @sqlQry varchar(4000)
Declare @age int=0
Declare @Salary decimal=0.0
Declare @designation varchar(50)=null
SET @sqlQry='Select empid,empname,age,salary,dob,designation from tblEmployee where 1=1'
IF @designation isnotnull
SET @sqlQry=@sqlQry+' AND designation ='''+@designation+''''
IF @age <> 0
SET @sqlQry=@sqlQry+' AND age ='+Convert(Varchar(2),@age)
IF @Salary <> 0.0
SET @sqlQry=@sqlQry+' AND Salary ='+Convert(Varchar(6),@Salary)


In the above examples, we are appending values in the varchar variable so another type of variable needs to be converted as varchar. For varchar variables mention that variable in Triple Quotes like this '''+@designation+''' because in SQL we give string in between quotes.

Select * from tblemployee where designation=’Software engineer’

To execute the Dynamic SQL use EXEC(@sqlQry) and Print(@sqlQry) to view how the dynamically generated query looks like.

In this article, we have learned the basics of Dynamic SQL.

About HostForLIFE

HostForLIFE is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.

We have offered the latest Windows 2019 Hosting, ASP.NET 5 Hosting, ASP.NET MVC 6 Hosting and SQL 2019 Hosting.

Tag cloud

Sign in