Learn C#, .NET, SQL

SQL Constraints

2022-03-02T19:02:00.021+05:30

What is SQL Constraints ?

When you’re creating a relational database tables, you often want to impose certain restrictions or have rules on some columns to maintain the integrity and accuracy of that column’s data.. If there is any violation between the constraint and the data action, the action is aborted.
Constraints in SQL Server can be defined at the column level, where it is specified as part of the column definition and will be applied to that column only, or declared at the table level. At table level constraints, rules will be applied to more than one column in the specified table.

Why Use SQL Constraints?
SQL constraints ensures the following

Data is correct (appropriate to the column).
The applications that input the data must follow the rules set while creating constraints.
We save time spent checking that the data has the required correctness. Manual corrections can be reduced.
Constraints ensures if INSERTs or UPDATEs do not conform to the rules, they will not be added to our database tables.

Constraints are of two types :

Column level constraints: Limits constraint for one specific column only.
Table level constraints: we can define constraint for multiple columns.

1. Column constraint is one or more of the following:

UNIQUE [WITH constraint_with_clause]
PRIMARY KEY [WITH constraint_with_clause]
REFERENCES [schema.]table_name[(column_name)][WITH constraint_with_clause]

Example of column-level constraints:
CREATE TABLE mytable(name CHAR(10) NOT NULL,
id INTEGER REFERENCES idtable(id),
age INTEGER CHECK (age > 0));

2. Table constraint is one or more of the following:

UNIQUE (column_name {, column_name}) [WITH constraint_with_clause]
PRIMARY KEY (column_name {, column_name}) [WITH constraint_with_clause]
FOREIGN KEY (column_name {, column_name})
REFERENCES [schema.]table_name [(column_name {, column_name})][WITH constraint_with_clause]

Example of table-level constraints:
CREATE TABLE yourtable(firstname CHAR(20) NOT NULL,
lastname CHAR(20) NOT NULL,
UNIQUE(firstname, lastname));

Following are the six main constraints that are commonly used in SQL Server.

1. NOT NULL :

By default, the columns are able to hold NULL values.

A NOT NULL constraint is used to prevent inserting NULL values into the specified column.

Consider a table with two columns, ID and Name. In the ID column definition statement, the NOT NULL column-level constraint is enforced, considering the ID column as a mandatory column that should be provided with a valid NOT NULL value.

Example :

USE [Test_Db]
GO
CREATE TABLE NullConstraintDemo
(
ID INT NOT NULL,
[Name] VARCHAR(50) NULL
)

INSERT INTO NullConstraintDemo ([ID],[NAME]) VALUES (1,'TestName')
GO
INSERT INTO NullConstraintDemo ([ID],[NAME]) VALUES (null, 2)
GO

Results :

First Insert statement will execute successfully.
Second statement will throw the error, since it is enforcing the NOT NULL constraint rules.

2. UNIQUE Constraint :

The UNIQUE constraint in SQL is used to ensure that no duplicate values will be inserted into a specific column or combination of columns that are participating in the UNIQUE constraint and not part of the PRIMARY KEY.
UNIQUE constraint allows to insert only one unique NULL value to the column, if the column allows NULL.
You can create a UNIQUE constraint on one or more columns of a table

Consider an example of Unique constraint

USE [Test_Db]
GO
CREATE TABLE UniqueConstraintTbl
(
ID INT UNIQUE,
Name VARCHAR(50) NULL
)

Try to insert the below records

INSERT INTO UniqueConstraintTbl ([ID],[NAME]) VALUES (1,'Amit')
GO
INSERT INTO UniqueConstraintTbl ([ID],[NAME]) VALUES (2,'Amit')
GO
INSERT INTO UniqueConstraintTbl ([ID],[NAME]) VALUES (NULL,'Steve')
GO
INSERT INTO UniqueConstraintTbl ([ID],[NAME]) VALUES (1,'Ravi')
GO

Results :
Since the unique constraint is applied for the column ID,

first two records will be inserted successfully, as the ID is unique.
The third record will be inserted successfully too, as the unique ID column allows only one NULL value.
Fourth record will fail as 1 is also inserted and exist in the table. ID column doesn’t allow duplicate values

The INFORMATION_SCHEMA.TABLE_CONSTRAINTS system object can be easily used to retrieve information about all defined constraints in a specific table using the T-SQL script below:

If you want to drop the constraint which was added, that can be done using the statement.

We have got the constraint name from the above T-Sql script, Constraint_Name = "UQ__UniqueCo__3214EC2670E86903", using this name we can write the drop T-Sql statement below

ALTER TABLE UniqueConstraintTbl DROP CONSTRAINT [UQ__UniqueCo__3214EC2670E86903]

3. PRIMARY KEY Constraint

The PRIMARY KEY constraint consists of one column or multiple columns with values that uniquely identify each row in the table.
The PRIMARY KEY constraint combines between the UNIQUE and SQL NOT NULL constraints, where the column or set of columns that are participating in the PRIMARY KEY cannot accept a NULL value.
If the PRIMARY KEY is defined in multiple columns, the combination values of all PRIMARY KEY columns must be unique. However, you can insert duplicate values on each column individually but not on the combination.
A table can have only ONE primary key; and in the table, the primary key can consist of single or multiple columns

Take into consideration that you can define only one PRIMARY KEY per each table,

USE [Test_Db]
GO
CREATE TABLE [dbo].[PrimaryKeyConstraintTbl]
(
     ID INT PRIMARY KEY,
   Name VARCHAR(50) NULL
)

Try to insert the below records :

INSERT INTO [PrimaryKeyConstraintTbl] ([ID],[NAME]) VALUES (1,'Amit')
GO
INSERT INTO [PrimaryKeyConstraintTbl] ([NAME]) VALUES ('Varun')
GO
INSERT INTO [PrimaryKeyConstraintTbl] ([ID],[NAME]) VALUES (1,'Raj')
GO

Results :

First record will be inserted successfully as both the ID and Name values are valid
Second insert will fail, as the ID column is mandatory and cannot be NULL, as the ID column is the PRIMARY KEY
The last INSERT statement will fail too as the provided ID value already exists and the duplicate values are not allowed in the PRIMARY KEY

4. FOREIGN KEY Constraint

A Foreign Key is a database key that is used to link two tables together.
A FOREIGN KEY is a columns (or set of columns) in one table, that refers to the PRIMARY KEY in another table.
The table with the foreign key is called the child table, and the table with the primary key is called the referenced or parent table.
You can create only one PRIMARY KEY per each table, with the ability to create multiple FOREIGN KEY constraints in each table by referencing multiple parent table.

The relationship between the child and the parent tables is maintained by checking the existence of the child table FOREIGN KEY values in the referenced parent table’s PRIMARY KEY before inserting these values into the child table. In this way, the FOREIGN KEY constraint, in the child table that references the PRIMARY KEY in the parent table, will enforce database referential integrity. Referential integrity ensures that the relationship between the database tables is preserved during the data insertion process.

The FOREIGN KEY constraint provides you also with the ability to control what action will be taken when the referenced value in the parent table is updated or deleted, using the ON UPDATE and ON DELETE clauses. The supported actions that can be taken when deleting or updating the parent table’s values include:

NO ACTION: When the ON UPDATE or ON DELETE clauses are set to NO ACTION, the performed update or delete operation in the parent table will fail with an error.
CASCADE: Setting the ON UPDATE or ON DELETE clauses to CASCADE, the same action performed on the referenced values of the parent table will be reflected to the related values in the child table. For example, if the referenced value is deleted in the parent table, all related rows in the child table are also deleted.
SET NULL: With this ON UPDATE and ON DELETE clauses option, if the referenced values in the parent table are deleted or modified, all related values in the child table are set to NULL value.
SET DEFAULT: Using the SET DEFAULT option of the ON UPDATE and ON DELETE clauses specifies that, if the referenced values in the parent table are updated or deleted, the related values in the child table with FOREIGN KEY columns will be set to its default value.

We will create two new tables to understand the FOREIGN KEY constraint functionality. The first table will act as the parent table with the ID column defined as a PRIMARY KEY column. The second table will act as the child table, with the ID column defined as the FOREIGN KEY column that references the ID column on the parent table. Following is the script for creating the tables:

USE [Test_Db]
GO
CREATE TABLE ConstraintParentTbl
(
       ID INT PRIMARY KEY,
	 Name VARCHAR(50) NULL
)
GO
CREATE TABLE ConstraintChildTbl
(
        CID INT PRIMARY KEY,
	 ID INT FOREIGN KEY REFERENCES ConstraintParentTbl(ID)
)

Insert the following records

INSERT INTO ConstraintParentTbl ([ID],[NAME]) VALUES (1,'Amit'),(2,'Sanya'),(3,'Sam')
GO
INSERT INTO ConstraintChildTbl (CID,ID) VALUES (1,1)
GO
INSERT INTO ConstraintChildTbl (CID,ID) VALUES (2,4)
GO

Results :

The first record that we tried to insert into the child table is inserted without any error as the ID value of 1 already exists in the parent table.
Second record into the child table will fail because the ID value of 4 doesn’t exist in the parent table, and due to the FOREIGN KEY constraint, you will not be able to insert an ID value to the child table that doesn’t exist in the parent table:

Select the values from parent and child table, only one value will be inserted into child table as below

5. CHECK Constraint

The CHECK constraint is used to limit the value range that can be placed in a column.
The CHECK constraint will be evaluated during insert/update values, where the value that satisfies the condition will be inserted into the table, otherwise, the insert operation will be discarded
It is allowed to specify multiple CHECK constraints for the same column.
The CHECK constraint is used mainly to enforce the domain integrity by limiting the inserted values to the ones that follow the defined values, range or format rules.

Let us create a new simple table that has three columns; the ID column that is considered as the PRIMARY KEY of that table, Name, and Salary. A CHECK constraint is defined on the Age column to make sure that above 18 values are inserted into that column. The CHECK constraint is defined within CREATE TABLE T-SQL statement below:

CREATE TABLE CheckConstraintTbl
(
     ID INT PRIMARY KEY,
     Name VARCHAR(50) NULL, 
     Age INT CHECK (Age>18)
)
GO

Insert the below records :

INSERT INTO CheckConstraintTbl ([ID],[NAME],Age) VALUES (1,'Amit',19)
GO
INSERT INTO CheckConstraintTbl ([ID],[NAME],Age) VALUES (2,'Sagar',18)
GO
INSERT INTO CheckConstraintTbl ([ID],[NAME],Age) VALUES (3,'Raj',Null)
GO

Results :

first record is inserted with no error as the Age value meets the checking condition.

The second and third INSERT statements will fail, as the provided Salary values do not meet the CHECK constraint condition as you can see in the following error message:

To check the constraints created for the table and name of the constraint, select table constraints from INFORMATION_SCHEMA. Based on the name we can drop the constraint, if required.

6. DEFAULT Constraint

The DEFAULT constraint is used to set a default value for a column.
The default value will be added to all new records, if no other value is specified.
The default value can be a constant value, a system function value or NULL.

In the below CREATE TABLE statement for a simple table with three columns, a DEFAULT constraint is defined on the ModifiedDate column, that assigns the GETDATE() system function value for that column in case we miss specifying it in the INSERT statement

CREATE TABLE DefaultConstraintTbl
(
        ID INT PRIMARY KEY,
	[Name] VARCHAR(50) NULL,
	ModifiedDate DATETIME NOT NULL DEFAULT GETDATE()
)
GO

INSERT INTO DefaultConstraintTbl ([ID],[NAME],ModifiedDate) VALUES (1,'Amit',

'2016/10/22')
GO
INSERT INTO DefaultConstraintTbl ([ID],[NAME]) VALUES (2,'Ajay')
GO

Check the values in the table

C# Reference types

2022-02-27T21:31:00.013+05:30

Reference type holds the reference of the value, that is when we declare a reference type, then the compiler stores the value in the managed heap. However, the address of the memory is stored in the stack.
C# also provides the following built-in reference types:

dynamic
object
string

The following keywords are used to declare reference types:

class
interface
delegate
record

Consider the following examples to describe the reference in more detail :

Example 1 - Declare one object and its memory structure
Consider the simple program for reference type with Student object and has it's properties as Name and Age.

public class Student{ 
public string Name {get; set;} 
public int Age {get; set;} 
} 
public class program { 
static void Main(string[] args) { 
Student stu = new Student(); 
stu.Name = "Sam"; 
stu.Age =23;

Console.WriteLine("student1 : " + stu.Name.ToString());}
}

Output :

student1 : Sam

Storage

In this example , the variable "stu" is declared as a reference type,
hence the compiler allocates memory from heap and stores the values.
The memory address 0x000001 of the reference type is stored in the stack as explained in the diagram below

Example 2 - Declare 2 separate references
In example 2, we have declared two reference types stu and stu2. Both these reference types have been allocated memory in managed heap. The memory addresses of the reference types stu and stu2 have been stored in the stack as shown in the image below
Code :

 public class program { 
 static void Main(string[] args)
    {
        Student stu = new Student();
        stu.Name = "Sam";
        stu.Age = 22;
        Student stu2 = new Student();
        stu2.Name = "Niran";
        stu2.Age = 26;
        Console.WriteLine("Both stu & stu2 has separate location.");
        Console.WriteLine("student1 : " + stu.Name.ToString());
        Console.WriteLine("student2 : " + stu2.Name.ToString());
    }
}

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

Output :

Both stu & stu2 has separate location.
student1 : Sam
student2 : Niran

Storage

In example 2, we have declared two reference types stu and stu2. Both these reference types have been allocated memory in managed heap. The memory addresses of the reference types stu and stu2 have been stored in the stack as shown in the image above.

Example 3 - Variable Assignment
If we assign one reference type variable to another reference type then the memory address will be copied and both variables will point to same memory location. So any value change to any of the variable would affects to the other as well.
Code :

 public class program { 
 static void Main(string[] args)
    {
        Student stu = new Student();
        stu.Name = "Sam";
        stu.Age = 22;
        Student stu2 = new Student();
        stu2.Name = "Niran";
        stu2.Age = 26;
        Console.WriteLine("Before assigning, stu & stu2 has separate location.");
        Console.WriteLine("student1 : " + stu.Name.ToString());
        Console.WriteLine("student2 : " + stu2.Name.ToString());
        Console.WriteLine("**************************");
 
        //Assign stu to stu2
        stu2 = stu;
        Console.WriteLine("After assigning, stu & stu2 points to same location.");
        Console.WriteLine("student1 : " + stu.Name.ToString());
        Console.WriteLine("student2 : " + stu2.Name.ToString());
    }
	}

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

Output :

Before assigning, stu & stu2 has separate location.
student1 : Sam
student2 : Niran
**************************
After assigning, stu & stu2 points to same location.
student1 : Sam
student2 : Sam

Storage :
Lets assign reference type stu to stu2 as follows. stu2 = stu; As soon as compiler recognizes the code, it updates the memory address of emp2. (e.g. 0x000001 , which is memory address of stu as shown in the image). That means, both the reference types stu and stu2 point to same memory location. Any change to any of the variable affects the other, because they are both pointing to the same location. The output value of stu2.Name will be “Sam”. as below... If we change the variable name to "Amit", the output for both the object name will be Amit...

C# Value Types

2022-02-25T17:58:00.027+05:30

Value Types and Reference Types are two main data types in C#. This separation for value type and reference type are done based on the way how each type stores its value in memory. I will be covering Reference type in my next blog. We will discuss in detail about Value type here.

What is Value Types ?

Value types are stored generally(not always) in the stack. A data type is a value type if it holds a data value within its own memory space have their own copy of the data
With value types, each variable has its own copy of the data, and it is not possible for operations on one variable to affect the other (except in the case of in, ref and out parameter variables).

Type of value types in C#

Built-in value types

int, bool, byte, char, decimal, double, float, long, sbyte, short, uint, ulong, ushort

User-defined value types

struct
enum

Storage locations

The CLR stores objects in three types of storage locations -- the CPU registers, the stack or the managed heap. The short-lived objects are stored inside registers or stack, and the long-lived objects are stored in the heap. As defined, value types are generally stored in the stack. It's a general misconception that value types are always stored in the stack.
Value types are stored on the stack when the value is a local variable or lives inside the local memory pool. However, value type are stored on the heap when they are a part of other objects on the heap or are a static field. And they always can be stored inside CPU register as a part of evaluation stack processing. The actual location of a value type depends on the implementation of the JIT compiler.
Most of the primitive types, such as int, float, double, and char have a fixed size, and when you declare a variable as a value type, the compiler generates code that allocates a block of memory big enough to hold a corresponding value. For example, declaring an int variable causes the compiler to allocate 4 bytes of memory (32 bits) to hold the integer value. A statement that assigns a value (such as 42) to the int causes the value to be copied into this block of memory.

Note : Value type can be stored in a stack frame, in the CPU register or even in the heap memory if the value type is contained inside an object, i.e., if it is a part of a reference type.

Examples :

1. Value assignment

int x = 50; // declare and initialize x

int y = x; /* y will have a separate storage in memory with same data as variable x.

x++; /* Even if we increment the variable x, it will not impact y;

Output : x now has a value of 51, but y still contains 50

Memory allocation is explained below for the above program

2. Passing the Value Type Variables

The system creates a separate copy of a variable in another method when you pass a value-type variable from one method to another. If value got changed in the one method, it wouldn't affect the variable in another method.

Code :

static void ChangeValue(int y){
   y = 200;
   Console.WriteLine($"second output {y} ");
}
static void Main(string[] args){
    int x = 50;
    Console.WriteLine($"first output {x} ");
    ChangeValue(x);
    Console.WriteLine($"Third output {x} ");
}

Output

first output 50
second output 200
Third output 50

.NET History - from .NET Framework 1.0 till .NET 6

2022-02-22T13:32:00.044+05:30

Microsoft started working on the .NET framework in the late 90s. The plan was to create a platform based on managed code, code that can be executed under a runtime environment. The objective was to improve the development experience and reduce the effort for the engineers from handling security operations, active memory management, and other low-level efforts that C/C++ developers had to bother with. .NET is a framework given by Microsoft for building desktop, mobile and web-based applications. It provides runtime, libraries, tooling and structures that allow you to develop any applications by using .NET framework.

.NET Framework History

In around 2002, Microsoft released version 1 of the .NET Framework, a language for writing managed code that had a design similar to C++.The framework was specifically aimed for Windows OS and servers.
The initial release was called .NET framework 1.0. The framework has progressed significantly from that point forward, and the current version is 4.8. Refer the version history in detail of .NET framework.
It had WinForms, a GUI library for desktop applications, ASP.NET, a framework for Web; and ADO.NET for data access. All these components were driven by Common Language Runtime (CLR) to compile and execute managed code. Each version of .NET Framework contains the common language runtime (CLR), the base class libraries, and other managed libraries. Refer .NET Framework Introduction for CLR architecture
.NET framework has undergone multiple iterations crossing runtime updates, new desktop graphical systems (WPF), APIs for service-oriented applications (WCF), and more.
Developers went on using .NET, especially after the introduction of Silverlight and Xamarin. However, they ran into portability issues when expanding the compatibility of .NET to other platforms. Despite all these improvements to the framework, achieving a true cross-platform environment was far away.
The problem of cross-platform led to the development of .NET Core.
After .NET 4.8 was released in the year 2019, Microsoft planned moving towards open source technology and introduced .NET Core which is a platform-independent technology to develop web, desktop and mobile applications.

.NET CORE

Microsoft announced a dramatic shift in the way .NET exists by presenting .NET Core in 2016, a new cross-platform, cloud-friendly, and open-source version of the framework.
The new framework has a modular design. Also, it comes with lighter versions of the core development components. As a result, a developer has the option choose to add modules.
.NET Core made it to a release in 2016, becoming the main technology to consider for new .NET projects. Gradually, Microsoft started porting existing services to work with Core. Some that didn’t receive official ports, like Windows Communication Foundation (WCF), were substituted by alternatives sourced from the community.
In 2016, Microsoft acquired Xamarin, previously a proprietary technology for cross-platform mobile development, making it open source as well.
Microsoft continued moving towards "transparency between the product team and the community," and released open-sourced Windows Presentation Foundation (WPF), Windows Forms, and WinUI frameworks in December 2018.

.NET Ecosystem Before .NET Standard

.NET Standard

Different runtimes use different class libraries. For example, .NET Framework uses the .NET Framework class library, while .NET Core contains its own class library, as well as Xamarin with its own class library. In this way, it's hard to share code between different runtimes, as they don’t use the same APIs.
Microsoft's solution to this is the .NET Standard library, released in 2016. It represent a set of (formal) specifications that say which APIs you can use and its implemented by all runtimes. It is an evolution of Portable Class Libraries (PCL). Specific runtimes implement specific version of .NET Standard (implementing a specific set of APIs). For example, .NET Framework 4.6.1 implements .NET Standard 2.0 and .NET 5 implements .NET Standard 2.1 (link).

NOTE :

.NET 5 and later versions adopt a different approach to establishing uniformity that eliminates the need for .NET Standard in most of the scenarios. However, if you want to share code between .NET Framework and any other .NET implementation, such as .NET Core, your library should target .NET Standard 2.0.
No new versions of .NET Standard will be released, but .NET 5, .NET 6, and all future versions will continue to support .NET Standard 2.1 and earlier

.NET Ecosystem after .NET Standard

.NET 5 Era :

In May 2019, the company announced the big release that would tie the ecosystem together:
All .NET elements were supposed to be bundled in the .NET 5 development platform. While changes were made to the schedule because of COVID-19, the .NET 5 unified development platform was finally introduced in November 2020.
.NET 5, Microsoft merged .NET Core and .NET Standard, which means that we now have a shared code base for all .NET workloads, whether it’s desktop apps, cloud services, or mobile apps. However, .NET 5 does not replace .NET Standard. .NET 5 may run on phones and Windows desktop apps. The new TFM net5.0 will represent the API set that's available on all platforms.
.NET 5 will be a single product with a uniform set of capabilities and APIs that can be used for Windows desktop apps, cross-platform mobile apps, console apps, cloud services, and websites.
The successor to .NET Core 3.1 and .NET Framework 4.8, .NET 5 puts order into the fragmentation of the .NET world and provides a lot of features to build applications on Windows, Linux, macOS, iOS, watchOS, Android, tvOS, or using WebAssembly.
The platform comes with new APIs, language features, and runtime capabilities. Also, .NET 5 includes ASP.NET Core, Xamarin, Entity Framework Core, WPF, WinForms, and ML.NET.

.NET 6 Era :

.NET 6 delivers the final parts of the .NET unification plan that started with .NET 5. .NET 6 unifies the SDK, base libraries, and runtime across mobile, desktop, IoT, and cloud apps. In addition to this unification, the .NET 6 ecosystem offers:

Simplified development: Getting started is easy. New language features in C# 10 reduce the amount of code you need to write. And investments in the web stack and minimal APIs make it easy to quickly write smaller, faster microservices.
Better performance: .NET 6 is the fastest full stack web framework, which reduces compute costs if you're running in the cloud.
High productivity: .NET 6 and Visual Studio 2022 provide hot reload, new git tooling, intelligent code editing, robust diagnostics and testing tools, and better team collaboration.

References :

https://docs.microsoft.com/en-us/dotnet/standard/net-standard?tabs=net-standard-1-0
https://docs.microsoft.com/en-us/dotnet/core/whats-new/dotnet-6
https://docs.microsoft.com/en-us/dotnet/core/whats-new/dotnet-5
https://docs.microsoft.com/en-us/aspnet/core/introduction-to-aspnet-core?view=aspnetcore-

ASP.NET Core Introduction

2022-02-19T00:59:00.018+05:30

Content :

What is .NET Core ?

The Microsoft .NET Core Framework is a free, open-source, and managed framework for Windows, Linux, macOS and can also be used to build devices, cloud, and IoT applications.
NET Core is a runtime. It can execute applications that are built for it.
ASP.NET Core 3 and higher now depend on .NET Core

What is ASP.NET Core ?

ASP.NET Core is a cross-platform, high-performance, open-source framework for building modern, cloud-enabled, Internet-connected apps. With ASP.NET Core, you can:

Build web apps and services, Internet of Things (IoT) apps, and mobile backends.
Use your favorite development tools on Windows, macOS, and Linux.
Deploy to the cloud or on-premises.
Run on .NET Core.

ASP.NET Core provides the following benefits:

A unified story for building web UI and web APIs.
Architected for testability.
Razor Pages makes coding page-focused scenarios easier and more productive.
Blazor lets you use C# in the browser alongside JavaScript. Share server-side and client-side app logic all written with .NET.
Ability to develop and run on Windows, macOS, and Linux.
Open-source and community-focused.
Integration of modern, client-side frameworks and development workflows.
Support for hosting Remote Procedure Call (RPC) services using gRPC.
A cloud-ready, environment-based configuration system.
Built-in dependency injection.
A lightweight, high-performance, and modular HTTP request pipeline.
Ability to host on the following:

Kestrel
IIS
HTTP.sys
Nginx
Apache
Docker

Side-by-side versioning.
Tooling that simplifies modern web development.

ASP.NET Core Vs. ASP.NET 4.x

ASP.NET Core	ASP.NET 4.x
Build for Windows, macOS, or Linux	Build for Windows
Razor Pages is the recommended approach to create a Web UI as of ASP.NET Core 2.x. See also MVC, Web API, and SignalR.	Use Web Forms, SignalR, MVC, Web API, WebHooks, or Web Pages
Multiple versions per machine	One version per machine
Develop with Visual Studio, Visual Studio for Mac, or Visual Studio Code using C# or F#	Develop with Visual Studio using C#, VB, or F#
Higher performance than ASP.NET 4.x	Good performance
Use .NET Core runtime	Use .NET Framework runtime

.NET Framework Version History

2022-02-18T22:58:00.021+05:30

Significance of the symbol:

The check mark icon ✔️ denotes OS versions on which .NET Framework is installed by default.
The plus sign icon ➕ denotes OS versions on which .NET Framework doesn't come installed but can be installed.
The asterisk * denotes OS versions on which .NET Framework (whether preinstalled or not) must be enabled in Control Panel or, for Windows Server, through the Server Manager

Framework Version	CLR Version	Windows	Windows Server	Visual Studio	Released Date
.NET Framework 1.0	1	N/A	N/A	Visual Studio .NET (2002)	2002-04-15
.NET Framework 1.1	1.1	N/A	✔️ 2003	Visual Studio 2003	2003-07-10
.NET Framework 2.0	2.0	N/A	✔️ 2008 R2 SP1 ✔️ 2008 SP2 ✔️ 2003	Visual Studio 2005	2006-02-17
.NET Framework 3.0	2.0	✔️ Vista	✔️ 2008 R2 SP1* ✔️ 2008 SP2* ➕ 2003	Visual Studio 2008	2006-11-21
.NET Framework 3.5	2.0	✔️ 10* ✔️ 8.1* ✔️ 8* ✔️ 7 ➕ Vista	➕ Windows Server, version 1803* ➕ Windows Server, version 1709* ➕ 2016* ➕ 2012 R2* ➕ 2012* ✔️2008 R2 SP1* ➕ 2008 SP2 ➕ 2003	Visual Studio 2008	2007-11-19
.NET Framework 4	4.0	➕ 7 ➕ Vista	➕ 2008 R2 SP1 ➕ 2008 SP2 ➕ 2003	Visual Studio 2010	2010-04-12
.NET Framework 4.5	4.0	✔️ 8 ➕ 7 ➕ Vista	✔️ 2012 ➕ 2008 R2 SP1 ➕ 2008 SP2	Visual Studio 2012	2012-10-09
.NET Framework 4.5.1	4.0	✔️ 8.1 ➕ 8 ➕ 7 ➕ Vista	✔️ 2012 R2 ➕ 2012 ➕ 2008 R2 SP1 ➕ 2008 SP2	Visual Studio 2013	2014-01-15
.NET Framework 4.5.2	4.0	➕ 8.1 ➕ 8 ➕ 7 ➕ Vista	➕ 2012 R2 ➕ 2012 ➕ 2008 R2 SP1 ➕ 2008 SP2	Visual Studio 2015	2014-05-05
.NET Framework 4.6	4.0	✔️ 10 ➕ 8.1 ➕ 8 ➕ 7 ➕ Vista	➕ 2012 R2 ➕ 2012 ➕ 2008 R2 SP1 ➕ 2008 SP2	Visual Studio 2015	2015-07-29
.NET Framework 4.6.1	4	✔️ 10 November Update (Version 1511) ➕ 10 ➕ 8.1 ➕ 8 ➕ 7	➕ 2012 R2 ➕ 2012 ➕ 2008 R2 SP1	Visual Studio 2015 Update 1	2015-11-30
.NET Framework 4.6.2	4	✔️ 10 Anniversary Update (Version 1607) ➕ 10 November Update (Version 1511) ➕ 10 ➕ 8.1 ➕ 7	✔️ 2016 ➕ 2012 R2 ➕ 2012 ➕ 2008 R2 SP1	Visual Studio 2017 15.0	2016-08-02
.NET Framework 4.7	4	✔️ 10 Creators Update (Version 1703) ➕ 10 Anniversary Update (Version 1607) ➕ 8.1 ➕7	➕ 2016 ➕ 2012 R2 ➕ 2012 ➕ 2008 R2 SP1	Visual Studio 2017 15.1	2017-04-11
.NET Framework 4.7.1	4	✔️ 10 Fall Creators Update (Version 1709) ➕ 10 Creators Update (Version 1703) ➕ 10 Anniversary Update (Version 1607) ➕ 8.1 ➕7	➕ Windows Server, version 1803 ✔️ Windows Server, version 1709 ➕ 2016 ➕ 2012 R2 ➕ 2012 ➕ 2008 R2 SP1	Visual Studio 2017 15.5	2017-10-17
.NET Framework 4.7.2	4	✔️ 10 October 2018 Update (Version 1809) ✔️ 10 April 2018 Update (Version 1803) ➕ 10 Fall Creators Update (Version 1709) ➕ 10 Creators Update (Version 1703) ➕ 10 Anniversary Update (Version 1607) ➕ 8.1 ➕7	✔️ Windows Server 2019 ✔️ Windows Server, version 1809 ✔️ Windows Server, version 1803 ➕ Windows Server, version 1709 ➕ 2016 ➕ 2012 R2 ➕ 2012 ➕ 2008 R2 SP1	Visual Studio 2019	2018-04-30
.NET Framework 4.8	4	✔️ 11 ✔️ 10 May 2021 Update ✔️ 10 October 2020 Update ✔️ 10 May 2020 Update ✔️ 10 November 2019 Update ✔️ 10 May 2019 Update ➕ 10 October 2018 Update (Version 1809) ➕ 10 April 2018 Update (Version 1803) ➕ 10 Fall Creators Update (Version 1709) ➕ 10 Creators Update (Version 1703) ➕ 10 Anniversary Update (Version 1607) ➕ 8.1 ➕7	✔️ Windows Server 2022 ➕ Windows Server 2019 ➕ Windows Server, version 1809 ➕ Windows Server, version 1803 ➕ 2016 ➕ 2012 R2 ➕ 2012 ➕ 2008 R2 SP1	Visual Studio 2019 16.3	2019-04-18

NOTE:

.NET Framework 4.8 is the last version of .NET Framework. .NET Framework is serviced monthly with security and reliability bug fixes. .NET Framework will continue to be included with Windows, with no plans to remove it. You don't need to migrate your .NET Framework apps, but for new development, use .NET 5 or later.

.NET Core Version history

2022-02-16T13:41:00.001+05:30

Version	Release date	Released with	Latest update	Latest updated	Support ends
.NET Core 1.0	2016-06-27	Visual Studio 2015 Update 3	1.0.16	May 14, 2019	June 27, 2019
.NET Core 1.1	2016-11-16	Visual Studio 2017 Version 15.0	1.1.13	May 14, 2019	June 27, 2019
.NET Core 2.0	2017-08-14	Visual Studio 2017 Version 15.3	2.0.9	July 10, 2018	October 1, 2018
.NET Core 2.1	2018-05-30	Visual Studio 2017 Version 15.7	2.1.30 (LTS)	August 19, 2021	August 21, 2021
.NET Core 2.2	2018-12-04	Visual Studio 2019 Version 16.0	2.2.8	November 19, 2019	December 23, 2019
.NET Core 3.0	2019-09-23	Visual Studio 2019 Version 16.3	3.0.3	February 18, 2020	March 3, 2020
.NET Core 3.1	2019-12-03	Visual Studio 2019 Version 16.4	3.1.22 (LTS)	December 14, 2021	December 3, 2022
.NET 5	2020-11-10	Visual Studio 2019 Version 16.8	5.0.14	February 8, 2022	May 8, 2022
.NET 6	2021-11-08	Visual Studio 2022 Version 17.0	6.0.2 (LTS)	February 8, 2022	November 8, 2024

NOTE:

The .NET 5 is one of the biggest releases in the .NET platform history, and from this release onward there will be only one .NET no more core or .NET Framework, just one and only one .NET, and the developers will be able to use this framework to target Windows, Linux, macOS, iOS, Android, Web Assembly and many more. The important highlight of this release is a single .NET runtime and framework that can be used on any platform. The framework will have uniform runtime behaviors across various platforms. The .NET 5+ does not replace .NET Framework, however, Microsoft will focus on .NET 5+ as its main implementation of the .NET framework.

C# Version History

2022-02-14T23:24:00.048+05:30

Version	.Net Framework	Major Features
C# 1.0	.NET Framework 1.0/1.1	Classes Structs Interfaces Events Properties Delegates Operators and expressions Statements Attributes
C# 2.0	.NET Framework 2.0	Generics Partial types Anonymous methods Nullable value types Iterators Covariance and contravariance
C# 3.0	.NET Framework version 3.5	Auto-implemented properties Anonymous types Query expressions Lambda expressions Expression trees Extension methods Implicitly typed local variables Partial methods Object and collection initializers
C# 4.0	.NET Framework 4.0	Dynamic binding Named/optional arguments Generic covariant and contravariant Embedded interop types
C# 5.0	.NET Framework 4.5	Asynchronous members Caller info attributes
C# 6.0	.NET Framework 4.6	Static imports Exception filters Auto-property initializers Expression bodied members Null propagator String interpolation nameof operator Index initializers Await in catch/finally blocks Default values for getter-only properties
C# 7.0	.NET Core 2.0	Out variables Tuples and deconstruction Pattern matching Local functions Expanded expression bodied members Ref locals and returns Discards Binary Literals and Digit Separators Throw expressions
C# 8.0	.NET Core 3.0	Readonly members Default interface methods Pattern matching enhancements: Switch expressions Property patterns Tuple patterns Positional patterns Using declarations Static local functions Disposable ref structs Nullable reference types Asynchronous streams Indices and ranges Null-coalescing assignment Unmanaged constructed types Stackalloc in nested expressions Enhancement of interpolated verbatim strings
C# 9.0	.NET 5.0	Records Init only setters Top-level statements Pattern matching enhancements Performance and interop Native sized integers Function pointers Suppress emitting localsinit flag Fit and finish features Target-typed new expressions static anonymous functions Target-typed conditional expressions Covariant return types Extension GetEnumerator support for foreach loops Lambda discard parameters Attributes on local functions Support for code generators Module initializers
C#10.0	.NET 6.0	Record structs Improvements of structure types Interpolated string handlers global using directives File-scoped namespace declaration Extended property patterns Improvements on lambda expressions Allow const interpolated strings Record types can seal ToString() Improved definite assignment Allow both assignment and declaration in the same deconstruction Allow AsyncMethodBuilder attribute on methods CallerArgumentExpression attribute Enhanced #line pragma

SQL Joins

2022-02-12T20:54:00.064+05:30

What is SQL Join ?

A SQL Join is used to fetch or combine data (rows or columns) from two or more tables based on the defined conditions

Types of SQL Join

Following are the different types of joins used in SQL:

Venn diagram for Joins :

To explain the examples we will consider the below tables as our input for all the joins except for Cross/Self Join :

Student

Roll_No

Name

Address

Age

1

Dheeraj

Delhi

18

2

Rakesh

Karnataka

19

3

Naveen

Andhra Pradesh

20

4

Sandhya

Tamil Nadu

21

5

Rohit

Punjab

22

6

Pratik

Maharashtra

23

7

Niranjan

Kerala

24

8

Harsh

MP

25

StudentCourse

Course_ID

Roll_No

1

1

2

2

2

3

3

4

1

5

4

9

5

10

4

11

1. Inner Join

The INNER JOIN keyword selects all rows from both the tables as long as the condition satisfies
This keyword will create the result-set by combining all rows from both the tables where the condition satisfies i.e. value of the common field will be same

Example :

Refer Input tables : tables

In this example we are selecting and inner joining the table StudentCourse and Student based on the common field that is Roll_No.

Inner Join Query:

Select StudentCourse.Course_Id, Student.Name, Student.Age
   from Student INNER JOIN StudentCourse 
   ON Student.Roll_No = StudentCourse.Roll_No;

Output :

Course_Id

Name

Age

1

Dheeraj

18

2

Rakesh

19

2

Naveen

20

3

Sandhya

21

1

Rohit

22

2. Left Outer Join/Left Join

This join returns all the rows of the table on the left side of the join and matching rows for the table on the right side of join
The rows for which there is no matching row on right side, the result-set will contain null value for right side table

Example :

Refer Input tables : tables

In the example query below, Left table : Student and Right table : StudentCourse

Left Join Query:

Select Student.Name, Student.Age, StudentCourse.Course_Id 
from Student LEFT JOIN StudentCourse 
ON StudentCourse.Roll_No = Student.Roll_No;

Output :

Name, Age - Left table(Student) - All rows are returned
Course_Id - Right table(StudentCourse) - only matching rows based on Roll_No has the value for the column Course_Id, the non-matching rows are set as null values.

Name

Age

Course_Id

Dheeraj

18

1

Rakesh

19

2

Naveen

20

2

Sandhya

21

3

Rohit

22

1

Pratik

23

Null

Niranjan

24

Null

Harsh

25

Null

3. Right Outer Join/Right Join

Right Join is similar to Left Join
This join returns all the rows of the table on the right side of the join and matching rows for the table on the left side of join
The rows for which there is no matching row on left side, the result-set will contain null
Example :
Refer Input tables : tables
In the e.g. query below, Left table : Student and Right table : StudentCourse
Right Join Query :
SELECT Student.Name, Student.Age, StudentCourse.Course_Id
FROM Student RIGHT JOIN StudentCourse 
ON StudentCourse.Roll_No = Student.Roll_No;
Output :
Name, Age - Left table(Student) - Returned the values for the matching rows on the left side of join, non-matching rows are returned as null
Course_Id - Right table(StudentCourse) - Returned all the rows
Name

Age

Course_Id

Dheeraj

18

1

Rakesh

19

2

Naveen

20

2

Sandhya

21

3

Rohit

22

1

Null

Null

4

Null

Null

5

Null

Null

4

4. Full Outer Join/Full Join

FULL JOIN creates the result-set by combining result of both LEFT JOIN and RIGHT JOIN.
The result-set will contain all the rows from both the tables.
The rows for which there is no matching, the result-set will contain NULL values
Output :
Refer Input tables : tables
Full Join is the combination of left and right join. It returns all the rows from both left and right join, matching rows will have values, non-matching rows will have the value set as null
Full Join Query :
SELECT Student.Name,Student.Age, StudentCourse.Course_Id
FROM Student FULL JOIN StudentCourse 
ON StudentCourse.Roll_No = Student.Roll_No;
Output :

Name

Age

Course_Id

Dheeraj

18

1

Rakesh

19

2

Naveen

20

2

Sandhya

21

3

Rohit

22

1

Pratik

23

Null

Niranjan

24

Null

Harsh

25

Null

Null

Null

4

Null

Null

5

Null

Null

4

5. Cross Join

The CROSS JOIN is used to generate a paired combination of each row of the first table with each row of the second table.
This join type is also known as cartesian join
The CROSS JOIN joined every row from the first table (T1) with every row from the second table (T2)
Example :
Consider 2 input tables

CROSS JOIN Query :

    SELECT *
    FROM table1 CROSS JOIN  table2

Output :

Number

Alphabet

1

A

2

A

3

A

1

B

2

B

3

B

1

C

2

C

3

C

6. Self Join

A self join allows you to join a table to itself
It helps query hierarchical data or compare rows within the same table
A self join uses the inner join or left join clause
Because the query that uses the self join references the same table, the table alias is used to assign different names to the same table within the query
Example :
Consider the input table
Staffs table

Staff_Id

FirstName

LastName

Manager_Id

1

Ramesh

Kumar

NULL

2

Rohit

M

1

3

Aarav

K

2

4

Aditya

Sharma

2

5

Dhruv

Patil

3

Self Join Query using INNER JOIN :
SELECT
    e.[FirstName] + ' ' + e.[LastName] employee,
    m.[FirstName] + ' ' + m.[LastName] manager
FROM
   [Staffs] e
INNER JOIN [Staffs] m ON m.staff_id = e.manager_id
ORDER BY  manager;
Output :

employee

manager

Dhruv Patil

Aarav K

Rohit M

Ramesh Kumar

Aarav K

Rohit M

Aditya Sharma

Rohit M

Using LEFT JOIN Query:
SELECT
    e.[FirstName] + ' ' + e.[LastName] employee,
    m.[FirstName] + ' ' + m.[LastName] manager
FROM
   [Staffs] e
LEFT JOIN [Staffs] m ON m.staff_id = e.manager_id
ORDER BY  manager;
Using LEFT JOIN output:

employee

manager

Ramesh Kumar

NULL

Dhruv Patil

Aarav K

Rohit M

Ramesh Kumar

Aarav K

Rohit M

Aditya Sharma

Rohit M

SQL Introduction

2022-02-12T20:25:00.012+05:30

What is SQL ?

SQL is an acronym for Structured Query Language
SQL is used to communicate with relational databases
Some common relational database management systems that use SQL include Microsoft SQL Server, MySQL, PostgreSQL, MariaDB, and Oracle
SQL became a standard of the American National Standards Institute (ANSI) in 1986, and of the International Organization for Standardization (ISO) in 1987

What Can SQL do?

Standard SQL commands such as "Select", "Insert", "Update", "Delete", "Create", and "Drop" can be used to accomplish almost everything that one needs to do with a database.
SQL can execute queries against a database using the above commands
SQL can create new tables, stored procedures, views, and set permissions on tables, procedures, and views in a database

What is RDBMS ?

RDBMS stands for Relational Database Management System.
RDBMS data is structured in database tables, fields and records.
Each RDBMS table consists of database table rows. Each database table row consists of one or more database table fields.
RDBMS store the data into collection of tables, which might be related by common fields (table columns).
Most RDBMS use SQL as database query language.
The most popular RDBMS are MS SQL Server, DB2, Oracle and MySQL

C# Introduction

2022-02-12T19:43:00.014+05:30

What is C# ?

C# is a modern, object-oriented, and type-safe programming language. C# enables developers to build many types of secure and robust applications that run in .NET

Several C# features help create robust and durable applications

Garbage collection automatically reclaims memory occupied by unreachable unused objects
Nullable types : guard against variables that don't refer to allocated objects
Exception handling : provides a structured and extensible approach to error detection and recovery
Lambda expressions : support functional programming techniques.
Language Integrated Query (LINQ) : syntax creates a common pattern for working with data from any source
Asynchronous operations : provides syntax for building distributed systems
C# has a unified type system. All C# types, including primitive types such as int and double, inherit from a single root object type.
C# supports both user-defined reference types and value types
C# supports generic methods and types, which provide increased type safety and performance
C# provides iterators, which enable implementers of collection classes to define custom behaviors for client code
C# emphasizes versioning to ensure programs and libraries can evolve over time in a compatible manner

C# code sample

using System;

class Hello
{
    static void Main()
    {
        Console.WriteLine("Hello, World");
    }
}

How C# code interacts with .NET framework ?

Explained in blog .NET framework

.NET Framework Introduction

2022-02-06T23:27:00.042+05:30

What is .NET Framework?

.NET Framework is a managed execution environment for Windows that provides a variety of services to its running apps.
.NET Framework is used to create and run software applications

Two major components of .NET framework

Common language runtime (CLR)

It is the execution engine that handles running apps. It provides services like memory management, type safety, exception handling, garbage collection, security and thread management

.NET Framework Class Library

It provides a library of tested, reusable code that developers can call from their own apps. The Class Library includes APIs for reading and writing files, connecting to databases, drawing, and more

.NET applications are written in the C#, F#, or Visual Basic programming language. Code is compiled into a language-agnostic Common Intermediate Language (CIL). Compiled code is stored in assemblies—files with a .dll or .exe file extension.

When an app runs, the CLR takes the assembly and uses a just-in-time compiler (JIT) to turn it into machine code that can execute on the specific architecture of the computer it is running on.

What is .NET Framework used for?

.NET Framework is used to create and run software applications. .NET apps can run on many operating systems, using different implementations of .NET. .NET Framework is used for running .NET apps on Windows.

Who uses .NET Framework?

Software developers and the users of their applications both use .NET Framework:

Users of applications built with the .NET Framework need to have .NET Framework installed. In most cases, .NET Framework is already installed with Windows. If needed, you can download .NET Framework.
Software developers use .NET Framework to build many different types of applications—websites, services, desktop apps, and more with Visual Studio. Visual Studio is an integrated development environment (IDE) that provides development productivity tools and debugging capabilities.

Architecture of .NET framework :

Roll_No	Name	Address	Age
1	Dheeraj	Delhi	18
2	Rakesh	Karnataka	19
3	Naveen	Andhra Pradesh	20
4	Sandhya	Tamil Nadu	21
5	Rohit	Punjab	22
6	Pratik	Maharashtra	23
7	Niranjan	Kerala	24
8	Harsh	MP	25

Course_Id	Name	Age
1	Dheeraj	18
2	Rakesh	19
2	Naveen	20
3	Sandhya	21
1	Rohit	22

Staff_Id	FirstName	LastName	Manager_Id
1	Ramesh	Kumar	NULL
2	Rohit	M	1
3	Aarav	K	2
4	Aditya	Sharma	2
5	Dhruv	Patil	3

employee	manager
Dhruv Patil	Aarav K
Rohit M	Ramesh Kumar
Aarav K	Rohit M
Aditya Sharma	Rohit M

Learn C#, .NET, SQL

SQL Constraints

C# Reference types

C# Value Types

.NET History - from .NET Framework 1.0 till .NET 6

ASP.NET Core Introduction

.NET Framework Version History

.NET Framework 1.1

.NET Framework 2.0

.NET Framework 3.0

.NET Framework 3.5

.NET Framework 4

.NET Framework 4.5

.NET Framework 4.5.1

.NET Framework 4.5.2

.NET Framework 4.6

.NET Framework 4.6.1

.NET Framework 4.6.2

.NET Framework 4.7

.NET Framework 4.7.1

.NET Framework 4.7.2

.NET Framework 4.8

.NET Core Version history

C# Version History

SQL Joins

SQL Introduction

What is SQL ?

What Can SQL do?

What is RDBMS ?

C# Introduction

.NET Framework Introduction