Understanding Constructors in .NET Core

Constructors are essential for initializing objects in object-oriented programming. Constructors are unique methods called when a class instance is created in.NET Core. This article explores the types, concepts, and operations of constructors in.NET Core and offers real-world examples to help explain their use.

A constructor: what is it?
When an object is formed, its state is initialized using a method called a constructor, which shares the same name as the class. Constructors lack a return type, not even void, in contrast to ordinary methods.

Types of Constructors

1. Default Constructor
2. Parameterized Constructor
3. Static Constructor

1. Default Constructor

A default constructor is a constructor that takes no arguments. If no constructor is defined in a class, the compiler automatically provides a default constructor.

public class Person
{
    public string Name { get; set; }
    public int Age { get; set; }

    // Default Constructor
    public Person()
    {
        Name = "Unknown";
        Age = 0;
    }
}

2. Parameterized Constructor

A parameterized constructor allows you to initialize an object with specific values at the time of creation.

public class Person
{
    public string Name { get; set; }
    public int Age { get; set; }

    // Parameterized Constructor
    public Person(string name, int age)
    {
        Name = name;
        Age = age;
    }
}

3. Static Constructor

A static constructor is used to initialize static members of the class. It is called automatically before any static members are accessed or any instances are created.

public class Configuration
{
    public static string Setting { get; set; }

    // Static Constructor
    static Configuration()
    {
        Setting = "Default Setting";
    }
}

How Constructors Work?

When an object is instantiated using the new keyword, the constructor is called. This process involves.

1. Memory Allocation: Memory is allocated for the new object.
2. Constructor Invocation: The appropriate constructor is invoked to initialize the object's state.
3. Object Creation: The object reference is returned to the caller.

Example

Let's create a simple example to see constructors in action.

public class Car
{
    public string Make { get; set; }
    public string Model { get; set; }
    public int Year { get; set; }

    // Default Constructor
    public Car()
    {
        Make = "Unknown";
        Model = "Unknown";
        Year = 0;
    }

    // Parameterized Constructor
    public Car(string make, string model, int year)
    {
        Make = make;
        Model = model;
        Year = year;
    }

    // Static Constructor
    static Car()
    {
        Console.WriteLine("Static constructor called");
    }
}

class Program
{
    static void Main()
    {
        Car defaultCar = new Car();
        Console.WriteLine($"Default Car: {defaultCar.Make}, {defaultCar.Model}, {defaultCar.Year}");

        Car specificCar = new Car("Toyota", "Corolla", 2022);
        Console.WriteLine($"Specific Car: {specificCar.Make}, {specificCar.Model}, {specificCar.Year}");
    }
}

Dependency Injection and Constructors

In .NET Core, constructors are often used for dependency injection (DI). DI is a design pattern that allows a class to receive its dependencies from an external source rather than creating them itself. This is usually done through constructor injection.

public interface IGreetingService
{
    void Greet(string name);
}

public class GreetingService : IGreetingService
{
    public void Greet(string name)
    {
        Console.WriteLine($"Hello, {name}!");
    }
}

public class HomeController
{
    private readonly IGreetingService _greetingService;

    // Constructor Injection
    public HomeController(IGreetingService greetingService)
    {
        _greetingService = greetingService;
    }

    public void Welcome()
    {
        _greetingService.Greet("John Doe");
    }
}

class Program
{
    static void Main()
    {
        var serviceProvider = new ServiceCollection()
            .AddSingleton<IGreetingService, GreetingService>()
            .BuildServiceProvider();

        var controller = serviceProvider.GetService<HomeController>();
        controller.Welcome();
    }
}

Conclusion

In.NET Core, constructors are essential to object initialization. Using static initialization, specified parameters, or default values, they offer a way to build up an object's initial state. Knowing constructors and using them wisely—especially when combined with dependency injection—can significantly improve the code's maintainability and flexibility.

ASP.NET Core 9.0 Hosting Recommendation

One of the most important things when choosing a good ASP.NET Core 9.0 hosting is the feature and reliability. HostForLIFE is the leading provider of Windows hosting and affordable ASP.NET Core, their servers are optimized for PHP web applications. The performance and the uptime of the hosting service are excellent and the features of the web hosting plan are even greater than what many hosting providers ask you to pay for. 

At HostForLIFE.eu, customers can also experience fast ASP.NET Core hosting. The company invested a lot of money to ensure the best and fastest performance of the datacenters, servers, network and other facilities. Its datacenters are equipped with the top equipments like cooling system, fire detection, high speed Internet connection, and so on. That is why HostForLIFEASP.NET guarantees 99.9% uptime for ASP.NET Core. And the engineers do regular maintenance and monitoring works to assure its Orchard hosting are security and always up.

Read More...

How to Porting Libraries to .NET Standard?

Changing libraries to.NET Standard can be very helpful in making your code work with different.NET implementations, such Xamarin,.NET Framework, and.NET Core. This tutorial will walk you through the process of converting your current libraries to.NET Standard, emphasizing important factors to take into account and necessary actions.

Describe the.NET Standard
The official specification of.NET APIs known as ".NET Standard" is meant to be compatible with all.NET implementations. Its goal is to make the.NET ecosystem more standardized. Libraries can be utilized without change across various.NET platforms by adhering to the.NET Standard.

Why Port to .NET Standard?

• Cross-Platform Compatibility: Libraries targeting .NET Standard can run on any .NET implementation, including .NET Core, .NET Framework, Xamarin, and more.
• Future-Proofing: .NET Standard represents the future of. NET. Porting now ensures your libraries remain relevant and usable.
• Code Reusability: You can share the same codebase across multiple platforms, reducing redundancy and maintenance efforts.

Steps to Port Libraries to .NET Standard

1. Assess Compatibility

Before starting the porting process, check if your library's dependencies and APIs are available in .NET Standard. You can use tools like .NET Portability Analyzer to assess compatibility.

2. Choose the Appropriate .NET Standard Version

Select the highest .NET Standard version that your target platforms support. Higher versions include more APIs but have stricter platform requirements. Here is a summary:

• .NET Standard 1. x: Supports older platforms, fewer APIs.
• .NET Standard 2.0: Significant increase in available APIs, supported by .NET Core 2.0, .NET Framework 4.6.1, Xamarin, etc.
• .NET Standard 2.1: Includes more APIs, but not supported by .NET Framework.

3. Create a .NET Standard Project

In Visual Studio, create a new Class Library project targeting .NET Standard:

1. Open Visual Studio and create a new project.
2. Select “Class Library (.NET Standard)” as the project template.
3. Choose the appropriate .NET Standard version.

4. Migrate Code

Copy the code from your existing library to the new .NET Standard project. During this process, you might need to.

• Remove or replace unsupported APIs: Some APIs available in the .NET Framework might not be available in .NET Standard. Look for alternatives or conditional compilation.
• Refactor platform-specific code: Use conditional compilation symbols to separate platform-specific code, for example.

  #if NETSTANDARD
      // .NET Standard code
  #else
      // Platform-specific code
  #endif

5. Update Dependencies

Ensure all your dependencies are compatible with .NET Standard. If a dependency does not support the .NET Standard, look for an alternative or consider contributing to the original project to add support.

6. Test Thoroughly

Testing is crucial to ensure your library works correctly on all target platforms.

• Unit Tests: Write unit tests and run them on different .NET implementations.
• Integration Tests: Test the library in real-world scenarios to verify its behavior.

7. Package and Distribute

Once the porting is complete and thoroughly tested, package your library for distribution.

1. Update the csproj file to include necessary metadata like version, authors, and description.
2. Use NuGet to create and publish a package.

8. Documentation and Support

Update your library’s documentation to reflect the changes made during the porting process. Clearly mention the supported .NET Standard version and any breaking changes.

Summary

Porting your libraries to .NET Standard is a valuable investment, enabling greater compatibility, code reusability, and future-proofing your projects. By following these steps and thoroughly testing your code, you can successfully transition to .NET Standard and take advantage of its benefits across multiple platforms.

ASP.NET Core 9.0 Hosting Recommendation

One of the most important things when choosing a good ASP.NET Core 9.0 hosting is the feature and reliability. HostForLIFE is the leading provider of Windows hosting and affordable ASP.NET Core, their servers are optimized for PHP web applications. The performance and the uptime of the hosting service are excellent and the features of the web hosting plan are even greater than what many hosting providers ask you to pay for. 

At HostForLIFE.eu, customers can also experience fast ASP.NET Core hosting. The company invested a lot of money to ensure the best and fastest performance of the datacenters, servers, network and other facilities. Its datacenters are equipped with the top equipments like cooling system, fire detection, high speed Internet connection, and so on. That is why HostForLIFEASP.NET guarantees 99.9% uptime for ASP.NET Core. And the engineers do regular maintenance and monitoring works to assure its Orchard hosting are security and always up.

 

Read More...

How Language Interoperability Is Attained by.NET (C#, VB.NET) ?

The power of.NET resides in its ability to offer language compatibility, which lets programmers easily design applications utilizing a variety of programming languages. This is made feasible via the Common Type System (CTS) and the Common Language Runtime (CLR), which guarantee error-free interoperability of code written in various languages.



We'll examine how.NET facilitates language interoperability in this blog article and offer a real-world example using C# and VB.NET.

Key Components

1. Common Language Runtime (CLR): The CLR is the execution engine for .NET applications, offering services like memory management, security enforcement, and exception handling. All .NET languages compile to an Intermediate Language (IL), which the CLR executes.
2. Common Type System (CTS): The CTS defines a set of data types and programming constructs common across all .NET languages. This ensures that objects written in one language can be understood in another language.
3. Common Language Specification (CLS): The CLS is a subset of the CTS that defines a set of rules and standards. Languages must adhere to these rules to ensure compatibility with other .NET languages, thereby enabling language interoperability.

Useful Example
Let's look at an example where we wish to utilize a C# class in a VB.NET application.

First, make a C# class
Initially, we build a basic C# class that offers a two-number addition method.

// File: Calculator.cs
using System;
namespace InteropExample
{
    public class Calculator
    {
        public int Add(int a, int b)
        {
            return a + b;
        }
    }
}

Step 2. Create the VB.NET Program
Next, we create a VB.NET program that uses the Calculator class to perform an addition operation.

' File: Program.vb
Imports InteropExample
Module Program
    Sub Main()
        Dim calc As New Calculator()
        Dim result As Integer = calc.Add(5, 10)
        Console.WriteLine("Result of addition: " & result)
    End Sub
End Module 

Step 3. Compile and Run the Code
To see the interoperability in action, follow these steps to compile and run the code.

Compile the C# Class: Use the C# compiler to compile the Calculator.cs file into a DLL.
csc /target:library /out:InteropExample.dll Calculator.cs

Compile the VB.NET Program: Use the VB.NET compiler to compile the Program.vb file, referencing the InteropExample.dll.
vbc /reference:InteropExample.dll Program.vb

Run the Program: Execute the resulting program.
Program.exe

Explanation

1. Compilation to IL: Both the C# and VB.NET code compile to Intermediate Language (IL), which the CLR can understand and execute.
2. Reference: The VB.NET program references the compiled C# DLL (InteropExample.dll), allowing it to create instances of the Calculator class and call its methods.
3. Execution: The CLR executes the IL code, providing seamless interaction between the two languages.

Conclusion

.NET's language interoperability is a powerful feature that allows developers to leverage the strengths of multiple programming languages within a single application. By understanding and utilizing the CLR, CTS, and CLS, you can create flexible and robust applications that transcend language barriers.

Best SQL 2022 Hosting Recommendation

One of the most important things when choosing a good SQL 2019 hosting is the feature and reliability. HostForLIFE is the leading provider of Windows hosting and affordable SQL 2019, their servers are optimized for PHP web applications. The performance and the uptime of the hosting service are excellent and the features of the web hosting plan are even greater than what many hosting providers ask you to pay for. 

At HostForLIFEASP.NET, customers can also experience fast SQL 2019 hosting. The company invested a lot of money to ensure the best and fastest performance of the datacenters, servers, network and other facilities. Its datacenters are equipped with the top equipments like cooling system, fire detection, high speed Internet connection, and so on. That is why HostForLIFEASP.NET guarantees 99.9% uptime for SQL 2019. And the engineers do regular maintenance and monitoring works to assure its Orchard hosting are security and always up.

Read More...

Using Stored Procedures in SQL Server to Create a User Login System

One essential component is user authentication. The usage of SQL Server stored procedures to implement a safe and effective login system guarantees that user credentials are checked against a database. The setup of a UsersDetails table and a LoginUser stored procedure for managing user logins are described below.

Make the UsersDetails Table in Step 1

Create a table first in order to store user credentials. Username and Password columns are shown in this example. As necessary, changes can be made to incorporate more user data.

USE DBname;
GO
-- Create the UsersDetails table if not exists
CREATE TABLE IF NOT EXISTS UsersDetails (
   UserID INT PRIMARY KEY IDENTITY(1,1),
   Username NVARCHAR(50) NOT NULL,
   Password NVARCHAR(50) NOT NULL
   -- Add additional columns as needed
);
GO

Step 2. Develop the LoginUser Stored Procedure

Next, create a stored procedure (LoginUser) that verifies user credentials against the UsersDetails table. This procedure accepts @Username and @Password parameters and outputs a @Status indicating the success or failure of the login attempt.

USE [DBname];
GO
/****** Object:  StoredProcedure [dbo].[LoginUser]    Script Date: 7/1/2024 3:29:21 PM ******/
SET ANSI_NULLS ON;
GO
SET QUOTED_IDENTIFIER ON;
GO
ALTER PROCEDURE [dbo].[LoginUser]
    @Username NVARCHAR(50),
    @Password NVARCHAR(50),
    @Status INT OUTPUT
AS
BEGIN
    SET @Status = 0; -- Default status to success
    IF EXISTS (SELECT 1 FROM UsersDetails WHERE Username = @Username AND Password = @Password)
        SELECT 'Login successful' AS [Message], @Status AS [Status];
    ELSE
    BEGIN
        SET @Status = 1; -- Set status to fail
        SELECT 'Invalid username or password' AS [Message], @Status AS [Status];
    END
END;

Step 3. Implementing the Login process

In your application, call the LoginUser stored procedure with the provided username and password. The stored procedure will return a message indicating whether the login was successful (@Status = 0) or unsuccessful (@Status = 1).

DECLARE @Username NVARCHAR(50) = 'example_user';
DECLARE @Password NVARCHAR(50) = 'example_password';
DECLARE @Status INT;
EXEC [dbo].[LoginUser]
   @Username,
   @Password,
   @Status OUTPUT;

IF @Status = 0
   PRINT 'Login successful';
ELSE
   PRINT 'Login failed: Invalid username or password';

Conclusion

Implementing user authentication using SQL Server stored procedures provides a robust way to manage login functionality within your applications. Ensure to secure passwords properly using hashing techniques and validate user inputs to enhance security further.

Best ASP.NET Core 9.0 Hosting Recommendation

One of the most important things when choosing a good ASP.NET Core 9.0 hosting is the feature and reliability. HostForLIFE is the leading provider of Windows hosting and affordable ASP.NET Core, their servers are optimized for PHP web applications. The performance and the uptime of the hosting service are excellent and the features of the web hosting plan are even greater than what many hosting providers ask you to pay for. 

At HostForLIFE.eu, customers can also experience fast ASP.NET Core hosting. The company invested a lot of money to ensure the best and fastest performance of the datacenters, servers, network and other facilities. Its datacenters are equipped with the top equipments like cooling system, fire detection, high speed Internet connection, and so on. That is why HostForLIFEASP.NET guarantees 99.9% uptime for ASP.NET Core. And the engineers do regular maintenance and monitoring works to assure its Orchard hosting are security and always up.

 

Read More...

Best and Cheap Joomla 5.1 Hosting in UK

To choose the Joomla 5.1 Hosting in UK for your site, we recommend you going with the following Best & Cheap Joomla 5.1 Hosting company that are proved reliable and sure by our editors. Meet Joomla 5.1, a powerful new suite of tools, and the strongest link in your new content supply chain. Interact with countless applications, thanks to REST-first native web services. Use progressive decoupling to break free from back-end restrictions without sacrificing security and accessibility. Deliver faster, with enhanced entity caching and better integration with CDNs and reverse proxies. With Joomla 5.1, you can build almost any integrated experience you can imagine.

 

Best and Cheap Joomla 5.1 Hosting in UK

UKWindowsHostASP.NET review is based on their industry reputation, web hosting features, performance, reliability, customer service and price, coming from our real hosting experience with them and the approximately 100 reviews from their real customers.UKWindowsHostASP.NET offers a variety of cheap and affordable UK Joomla 5.1 Hosting Plans with unlimited disk space for your website hosting needs.

UKWindowsHostASP.NET revolutionized hosting with Plesk Control Panel, a Web-based interface that provides customers with 24x7 access to their server and site configuration tools. Some other hosting providers manually execute configuration requests, which can take days. Plesk completes requests in seconds. It is included free with each hosting account. Renowned for its comprehensive functionality - beyond other hosting control panels - and ease of use, Plesk Control Panel is available only to UKWindowsHostASP.NET's customers.

UKWindowsHostASP.NET is the best UK Windows Hosting provider that offers the most affordable world class windows hosting solutions for their customers. They provide shared, reseller, cloud, and dedicated web hosting. They currently operate servers in four prestiguous cities in Europe, namely: London (UK), Amsterdam (Netherlands), Frankfurt (Germany), Washington DC (US), Paris (France), Singapore and Chennai (India). Their target is to provide a versatile and dependable one-stop online hosting and marketing shop for the small business entrepreneur, and eliminate the need for you to deal with a host of different online vendors. They offer high quality web hosting, dedicated servers, web design, domain name registration, and online marketing to help lead your business to online success.

Leveraging a strong market position within the domain name registration industry, UKWindowsHostASP.NET has carefully nurtured relationships with its customer base and built a feature-rich line of value-added services around its core domain name product offering. By bundling services and providing one-stop shopping, UKWindowsHostASP.NET has successfully grown and enjoyed increased breadth and loyalty of its customer base. 

The Reason Why Choosing UKWindowsHostASP.NET?

• 24/7-based Support -They never fall asleep and they run a service that is operating 24/7 a year. Even everyone is on holiday during Easter or Christmas/New Year, they are always behind their desk serving their customers.
• Excellent Uptime Rate - Their key strength in delivering the service to you is to maintain their server uptime rate. They never ever happy to see your site goes down and they truly understand that it will hurt your onlines business.
• High Performance and Reliable Server - They never ever overload their server with tons of clients. They always load balance their server to make sure they can deliver an excellent service, coupling with the high performance and reliable server.
• Experts in Web Hosting - Given the scale of their environment, they have recruited and developed some of the best talent in the hosting technology that you are using.
• Daily Backup Service - They realise that your website is very important to your business and hence, they never ever forget to create a daily backup. Your database and website are backup every night into a permanent remote tape drive.
• Easy Site Administration - With their powerful control panel, you can always administer most of your site features easily without even needing to contact for their Support Team.

Read More...

Which European Hosting Provides the Best DotNetNuke 9.13.3 Experience?

After reviewed dozens of DotNetNuke web hosting Europe, we had come out a list of the best DotNetNuke 9.13.3 Hosting Europe rated based on DotNetNuke installation convenience; included DotNetNuke modules and themes. Windows server reliability, performance and security; affordability; etc. To choose the best cheap DNN hosting for yourself, we recommend you going with the following best DotNetNuke 9.13.3 Hosting which have been truly verified by our DotNetNuke experts.

DotNetNuke 9.13.3 Hosting

Which European Hosting Provides the Best DotNetNuke 9.13.3 Experience?

HostForLIFEASP.NET is one of the Microsoft recommended hosting partners that provides most stable and reliable web hosting platform. Its DotNetNuke 9.13.3 Hosting solution is recognized as Best Web Hosting by many hosting reviews. Services include shared hosting, reseller hosting, and SharePoint hosting, with specialty in ASP.NET, SQL Server, and architecting highly scalable solutions. The shared DotNetNuke 9.13.3 Hosting start from Є2.97/mo only. 

The company offers a 1-click installer in the Web Application Gallery to automate the installation of the DotNetNuke community edition. HostForLIFEASP.NET shared hosting packaged support the newest ASP.NET Core, ASP.NET MVC 6  and the latest MySQL, with Windows 2022 Server. The most highlighted part of the hosting service from HostForLIFEASP.NET, however, is the super fast speed guaranteed by the first-rate facilities, advanced switching system and complete power and network redundancy.

Besides the checkpoints on reviewing other generic Windows web hosting, the DotNetNuke web hosting are rated based on these three.

• DotNetNuke Modules and Themes - the more free modules and themes provided by the web host, the more opportunity that your websites can be different to others.
• DotNetNuke Auto Installer is the prerequisite of a common Windows web hosting called as DotNetNuke web hosting in our directory. But with the auto installer integrated with the control panel (e.g. WebSitePanel or Plesk), you can easily setup by clicks in seconds. You're worry free about whether they support DotNetNuke or how complicated to setup DotNetNuke.
• There are some misunderstanding on the poor reliability of Windows server. The most complaints are just from the non-professional configuration and deployment. We only promote you with the reliable shared web hosts which are truly tested by ourselves. The best DotNetNuke web hosting Europe below all have more than 99.8% uptime based on our monitoring.

DotNetNuke Hosting Performance

Eliminate slow loading websites and invest in something better with their DNN hosting options. With their high-performance DotNetNuke 9.13.3 Hosting HostForLIFEASP.NET can ensure that their clients are available online 24 hours a day, 7 days a week. Our specialist webservers are designed to give your DNN website a boost, making sure it performs well and can handle the demand from customer usage.

More About HostForLIFEASP.NET DotNetNuke Web Hosting Features

• HostForLIFEASP.NET DotNetNuke 9.13.3 Hosting plan supports automated DotNetNuke installation.
• Every website is hosted using IIS Isolated Application Pool in order to meet maximum security standard and reliability.
• Ultra Fast and dedicated customer service second to none in the ASP.NET and DotNetNuke web hosting industry.
• 24/7/365 Technical and Email support from the DotNetNuke Expert of the DotNetNuke 9.13.3 Hosting provider.
• Managed Backup / Recovery of customer data - the DotNetNuke 9.13.3 Hosting company backs up all data daily and be available for recovery at anytime at your request.
• Fast and secure MSSQL 2012/2014 Database and Web servers give you maximum reliability and performance.
• Have wide-ranging experience with DotNetNuke 9.13.3 Hosting and troubleshooting.
• Whether it is compatible with the HostForLIFEASP.NET DotNetNuke 9.13.3 Hosting management and collaboration application.

Friendly Technical Support

DotNetNuke 9.13.3 Hosting quality often goes along with customer service. HostForLIFEASP.NET holds on to resolve technical or non-technical problems for customers, allowing them to seek help via email and tickets. The professional representatives shoulder full responsibility to help settle issues raised by customers patiently to ensure customers coming all over the world to get help 24 hours a day and 7 days a week. 

Read More...

Understanding Joins in SQL Server

As we know SQL Server has been created based on two mathematical concepts, they are Set Theory and Predicate Logic. In set theory, the cartesian product is the basic operation. Joins in SQL Server also works in the same way as the Cartesian product.

In mathematics, the Cartesian Product of sets A and B is defined as the set of all ordered pairs (x, y) such that x belongs to A and y belongs to B.

For example, if A = {1, 2} and B = {3, 4, 5}, then the Cartesian Product of A and B is {(1, 3), (1, 4), (1, 5), (2, 3), (2, 4), (2, 5)}.

When we apply joins between tables, the same cartesian product will happen first.

Joins are required to return the data from multiple tables. These tables should have common functionally similar columns to have a join condition between tables.

We will understand the joins after taking a look at cross-join (cartesian product) first.

CROSS Join

When we apply cross join between two tables(TableA and TableB), every row in TableA will have a combination with every row in TableB.

Let's take an example and look at it.

IF OBJECT_ID('dbo.ProductCategory') IS NOT NULL
    DROP TABLE dbo.ProductCategory;
CREATE TABLE dbo.ProductCategory (
    ProductCategoryId INT PRIMARY KEY IDENTITY,
    CategoryName VARCHAR(500)
);
GO
INSERT INTO dbo.ProductCategory (CategoryName) VALUES ('Fruits');
INSERT INTO dbo.ProductCategory (CategoryName) VALUES ('Vegetables');
INSERT INTO dbo.ProductCategory (CategoryName) VALUES ('Water');
INSERT INTO dbo.ProductCategory (CategoryName) VALUES ('Dairy Based Food');
INSERT INTO dbo.ProductCategory (CategoryName) VALUES ('Meat');
GO
SELECT * FROM dbo.ProductCategory;
GO
IF OBJECT_ID('dbo.Product') IS NOT NULL
    DROP TABLE dbo.Product;
CREATE TABLE dbo.Product (
    ProductId INT PRIMARY KEY IDENTITY,
    ProductName VARCHAR(500),
    ProductCategoryId INT NOT NULL
);
GO
INSERT INTO dbo.Product (ProductName, ProductCategoryId) VALUES ('Apple', 1);
INSERT INTO dbo.Product (ProductName, ProductCategoryId) VALUES ('Mango', 1);
INSERT INTO dbo.Product (ProductName, ProductCategoryId) VALUES ('Capsicum', 2);
INSERT INTO dbo.Product (ProductName, ProductCategoryId) VALUES ('Tomato', 2);
INSERT INTO dbo.Product (ProductName, ProductCategoryId) VALUES ('Milk', 4);
INSERT INTO dbo.Product (ProductName, ProductCategoryId) VALUES ('Curd', 4);
INSERT INTO dbo.Product (ProductName, ProductCategoryId) VALUES ('Chicken', 5);
INSERT INTO dbo.Product (ProductName, ProductCategoryId) VALUES ('Mutton', 5);
INSERT INTO dbo.Product (ProductName, ProductCategoryId) VALUES ('Pasta', 50);
INSERT INTO dbo.Product (ProductName, ProductCategoryId) VALUES ('Brown Rice', 65);
GO
SELECT * FROM dbo.Product;
GO
SELECT * FROM dbo.ProductCategory
CROSS JOIN dbo.Product;
GO

In general, we refer to the left table as the ProductCategory table and the right table as the Product table. Every row from the ProductCategory table will combine each row with each row from the product table.

Below is the one-part result of the cross-join. Please run the above script to check the full resultset.

Inner Join

when we apply INNER JOIN between tables, the result is going to be only the rows which are satisfying the given condition. Non-matching rows will be ignored.

SELECT
    PC.ProductCategoryId,
    PC.CategoryName,
    P.ProductId,
    P.ProductName
FROM dbo.ProductCategory PC
INNER JOIN dbo.Product P ON PC.ProductCategoryId = P.ProductCategoryId;

LEFT OUTER Join/ LEFT Join

When we apply LEFT JOIN between tables, the result is going to be only the rows which are satisfying the given condition plus Non-matching rows from left side table, null values will be returned for the corresponding rows from the right side table.

SELECT
    PC.ProductCategoryId,
    PC.CategoryName,
    P.ProductId,
    P.ProductName
FROM dbo.ProductCategory PC

RIGHT OUTER Join / RIGHT Join

when we apply RIGHT JOIN between tables, the result is going to be only the rows which are satisfying the given condition plus Non-matching rows from the RIGHT side table, null values will be returned for the corresponding rows from the LEFT side table.

SELECT
    PC.ProductCategoryId,
    PC.CategoryName,
    P.ProductId,
    P.ProductName
FROM dbo.ProductCategory PC
RIGHT JOIN dbo.Product P ON PC.ProductCategoryId = P.ProductCategoryId;

FULL OUTER Join / FULL Join

when we apply FULL JOIN between tables, the result is going to be only the rows which are satisfying the given condition plus Non-matching rows from left side table, plus non-matching rows from right table, null values will be returned for the corresponding rows from the both side tables.

SELECT
    PC.ProductCategoryId,
    PC.CategoryName,
    P.ProductId,
    P.ProductName
FROM dbo.ProductCategory PC
FULL JOIN dbo.Product P ON PC.ProductCategoryId = P.ProductCategoryId;

SELF JOIN in SQL Server

When we join a table with itself, it is called a "self join". It is essentially an inner join where both the left and right tables are the same table. We use a self join when a logical relationship exists between two columns within the same table.

Example
List out all the employees along with their managers.

In the same way, we can join more than two tables as well. The only thing we need to find out is the common table column between tables and the columns to be returned.

There is another important difference to understand between the join condition and the where condition.

what is the above difference/how does the above difference behave in the case of inner join and left join?

Two important points here.

1. Matching Predicate
2. Filtering Predicate

Let's use the below query to understand the above two points.

SELECT
    PC.ProductCategoryId,
    PC.CategoryName,
    P.ProductId,
    P.ProductName
FROM
    dbo.ProductCategory PC
INNER JOIN
    dbo.Product P ON PC.ProductCategoryId = P.ProductCategoryId
WHERE
    PC.CategoryName = 'Meat';

When we write any condition in the ON clause it becomes a Matching Predicate. The condition we write in where clause is Filtering Predicate.

Let's modify the above query and try to understand what will happen.

SELECT
    PC.ProductCategoryId,
    PC.CategoryName,
    P.ProductId,
    P.ProductName
FROM
    dbo.ProductCategory PC
INNER JOIN
    dbo.Product P ON PC.ProductCategoryId = P.ProductCategoryId AND PC.CategoryName = 'Meat';

In the case of inner join, it does not make any difference.

SELECT
    PC.ProductCategoryId,
    PC.CategoryName,
    P.ProductId,
    P.ProductName
FROM
    dbo.ProductCategory PC
INNER JOIN
    dbo.Product P ON PC.ProductCategoryId = P.ProductCategoryId
WHERE
    PC.CategoryName = 'Meat';

Now move the filter condition from where clause to on as below.

SELECT
    PC.ProductCategoryId,
    PC.CategoryName,
    P.ProductId,
    P.ProductName
FROM
    dbo.ProductCategory PC
INNER JOIN
    dbo.Product P ON PC.ProductCategoryId = P.ProductCategoryId AND PC.CategoryName = 'Meat';

Now we understand that there is no difference in the case of writing conditions in where clause or on clause when we use inner join. But in the case of left join, there is an important difference.

SELECT
    PC.ProductCategoryId,
    PC.CategoryName,
    P.ProductId,
    P.ProductName
FROM
    dbo.ProductCategory PC
LEFT JOIN
    dbo.Product P ON PC.ProductCategoryId = P.ProductCategoryId
WHERE
    PC.CategoryName = 'Meat';

Now move the condition from where clause to on clause.

See the surprising result.

So, please note that the condition we write in on clause is matching predicate, it won't filter the data, if there is no match from the right side table, it will give a null value. You have to write your filter conditions always in the where clause to filter the data. Joins can be used to update statements and delete statements as well. When you write update/delete statements based on left join be careful. Please test your query to understand whether it is giving the correct result or not.

Best SQL 2022 Hosting Recommendation

One of the most important things when choosing a good SQL 2019 hosting is the feature and reliability. HostForLIFE is the leading provider of Windows hosting and affordable SQL 2019, their servers are optimized for PHP web applications. The performance and the uptime of the hosting service are excellent and the features of the web hosting plan are even greater than what many hosting providers ask you to pay for. 

At HostForLIFEASP.NET, customers can also experience fast SQL 2019 hosting. The company invested a lot of money to ensure the best and fastest performance of the datacenters, servers, network and other facilities. Its datacenters are equipped with the top equipments like cooling system, fire detection, high speed Internet connection, and so on. That is why HostForLIFEASP.NET guarantees 99.9% uptime for SQL 2019. And the engineers do regular maintenance and monitoring works to assure its Orchard hosting are security and always up.

Read More...