Database SQL Concepts & key concepts to learn #inprogress

1. Constraints

2. Data Types

3.Diff between where and having clause

1.	WHERE Clause is used to filter the records from the table based on the specified condition.	HAVING Clause is used to filter record from the groups based on the specified condition.
2.	WHERE Clause can be used without GROUP BY Clause	HAVING Clause cannot be used without GROUP BY Clause
3.	WHERE Clause implements in row operations	HAVING Clause implements in column operation
4.	WHERE Clause cannot contain aggregate function	HAVING Clause can contain aggregate function
5.	WHERE Clause can be used with SELECT, UPDATE, DELETE statement.	HAVING Clause can only be used with SELECT statement.
6.	WHERE Clause is used before GROUP BY Clause	HAVING Clause is used after GROUP BY Clause
7.	WHERE Clause is used with single row function like UPPER, LOWER etc. SELECT COUNT(CustomerID), Country FROM Customers where Country='Brazil' or Country!='France' GROUP BY Country HAVING COUNT(CustomerID) > 5;	HAVING Clause is used with multiple row function like SUM, COUNT etc. Below result first group by Country then filter from group data. SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country HAVING COUNT(CustomerID) > 5;

4.Temp table Vs CTE (common table expression) Vs Table variable

Temp tables: are created in tempdb, it contains two types #local & ##Global

These tables act as the normal table and also can have constraints, an index like normal tables.

#local temp tables available only for within session alone.

CREATE TABLE #LocalTemp
(
 UserID int,
 Name varchar(50), 
 Address varchar(150)
)
GO
insert into #LocalTemp values ( 1, 'Shailendra','Noida');

##Global temp tables can access to all sessions.

CREATE TABLE ##GlobalTemp
(
 UserID int,
 Name varchar(50), 
 Address varchar(150)
)
GO
insert into ##GlobalTemp values ( 1, 'Shailendra','Noida');
GO
Select * from ##GlobalTemp

CTE: CTE is a named temporary result set which is used to manipulate the complex sub-queries data. This exists for the scope of a statement. This is created in memory rather than the Tempdb database. we cannot create an index on CTE 

CTE improves readability and ease in maintenance of complex queries and sub-queries. Always begin CTE with a semicolon.

;With CTE1(Address, Name, Age)--Column names for CTE, which are optional
AS
(
SELECT Addr.Address, Emp.Name, Emp.Age from Address Addr
INNER JOIN EMP Emp ON Emp.EID = Addr.EID
)
SELECT * FROM CTE1 --Using CTE 
WHERE CTE1.Age > 50
ORDER BY CTE1.NAME

Table Variable: This acts like a variable and exists for a particular batch of query execution. It gets dropped once it comes out of the batch. This is also created in the tempdb database but not the memory. This also allows you to create a primary key, identity at the time of Table variable declaration but not non-clustered index.

GO
 DECLARE @TProduct TABLE
 (
 SNo INT IDENTITY(1,1),
 ProductID INT,
 Qty INT
 ) 
 --Insert data to Table variable @Product 
 INSERT INTO @TProduct(ProductID,Qty)
 SELECT DISTINCT ProductID, Qty FROM ProductsSales ORDER BY ProductID ASC 
 --Select data
 Select * from @TProduct
 
 --Next batch
 GO
 Select * from @TProduct --gives error in next batch

5.Indexes

Indexes are special lookup tables that the database search engine can use to speed up data retrieval. Simply put, an index is a pointer to data in a table. An index in a database is very similar to an index in the back of a book.

6.ACID: properties

A- Atomicity : fail or success it should apply for all transaction not partial.

7. Joins

    i. Inner Join : retrieve only matched rows between tables

    ii. Outer Join : retrieve only Not matched rows between tables

    iii. Left Join : Table A Left join Table B => Retrieve all from Table A and Only Matched rows                     From  Table B.

    iv. Right Join: Vice versa of Left join

8. Union(without duplicate rows) Vs Union All (include duplicate rows)

 

Global Exception Handler in c# .Net Core

 Default exception will handled by ExceptionFilter, incase we need to override this then we can derived from IExceptionFilter and we can customize it.

Predefined interface for Exception filter is below

///predefine interface for exception is below

namespace Microsoft.AspNetCore.Mvc.Filters

{

    //

    // Summary:

    //     A filter that runs after an action has thrown an System.Exception.

    public interface IExceptionFilter : IFilterMetadata

    {

        //

        // Summary:

        //     Called after an action has thrown an System.Exception.

        //

        // Parameters:

        //   context:

        //     The Microsoft.AspNetCore.Mvc.Filters.ExceptionContext.

        void OnException(ExceptionContext context);

    }

}

Let us create a class and inherit from IExceptionFilter file name like GlobalExceptionFilter.cs

Above interface contains  OnException  method so when we derived from tat interface we need to provide definition.

using Microsoft.AspNetCore.Http;

using Microsoft.AspNetCore.Mvc.Filters;

using System;

using System.Net;

namespace GlobalException

{

public class GlobalExceptionFilter : IExceptionFilter

{

    /// <summary>

    /// Global method to log the Exception thismethod will hit when exception happen

    /// </summary>

    /// <param name="context"></param>

    public void OnException(ExceptionContext context)

    {

        if (context != null)

        {

            /// Find the response status code if it's null then send InternalServerError

            HttpStatusCode statusCode = (context.Exception as WebException != null &&

                        ((HttpWebResponse)(context.Exception as WebException).Response) != null) ?

                        ((HttpWebResponse)(context.Exception as WebException).Response).StatusCode

                        : HttpStatusCode.InternalServerError;

            context.ExceptionHandled = true;

            HttpResponse response = context.HttpContext.Response;

            response.StatusCode = (int)statusCode;

            response.ContentType = "application/json";

            /// use own logger to store exception like below

            //logger.Error(context.Exception);

        }

    }            

}

}

Now we need create Startup.cs class and map in program.cs file like below.

 public class Program

    {

        protected Program()

        {

        }

        public static void Main(string[] args)

        {

            CreateWebHostBuilder(args).Build().Run();

        }

        public static IWebHostBuilder CreateWebHostBuilder(string[] args) =>

            WebHost.CreateDefaultBuilder(args)

                .UseStartup<Startup>();

    }

we mentioned above like all configuration based on Startup class.

Now startup class we need add like below.

using Microsoft.AspNetCore.Builder;

using Microsoft.AspNetCore.Hosting;

using Microsoft.AspNetCore.Http;

using Microsoft.AspNetCore.Mvc;

using Microsoft.AspNetCore.Server.IISIntegration;

using Microsoft.Extensions.Configuration;

using Microsoft.Extensions.DependencyInjection;

using Microsoft.Extensions.DependencyInjection.Extensions;

using NLog;

using NLog.Extensions.Logging;

using Newtonsoft.Json.Serialization;

using Microsoft.AspNetCore.Mvc.NewtonsoftJson;

using System;

namespace GlobalExceptionSample.API

{

    public class Startup

    {

        public Startup(IConfiguration configuration)

        {

            var config = new ConfigurationBuilder()

          .SetBasePath(System.IO.Directory.GetCurrentDirectory())

          .AddJsonFile("appsettings.json", optional: true, reloadOnChange: true).Build();

            LogManager.Configuration = new NLogLoggingConfiguration(config.GetSection("NLog"));

            Configuration = configuration;

           

        }

        public static IConfiguration Configuration { get; set; }

        // This method gets called by the runtime. Use this method to add services to the container.

        public void ConfigureServices(IServiceCollection services)

        {

            services.AddControllers();

            services.TryAddSingleton<IHttpContextAccessor, HttpContextAccessor>();

            ///any default implementation we can include as AddSingleton like below

            ///services.AddSingleton<ILog, Log>();

            /*----Here we are adding Global Exception filter as GlobalExceptionFilter*/

            services.AddMvc(config => {

                            config.Filters.Add(typeof(GlobalExceptionFilter));

                         });

            services.AddControllers()

                    .AddNewtonsoftJson(options =>

                    {

                        options.SerializerSettings.ContractResolver = new DefaultContractResolver();

                    });

            services.AddCors();

            services.AddAuthentication(IISDefaults.AuthenticationScheme);

           

        }

        // This method gets called by the runtime. Use this method to configure the HTTP request pipeline.

        public void Configure(IApplicationBuilder app, IWebHostEnvironment env)

        {

            app.UseAuthentication();

            app.UseRouting();

            app.UseCors(

              options => options.SetIsOriginAllowed(x => _ = true)

              .AllowAnyMethod()

              .AllowAnyHeader()

              .AllowCredentials());

            app.UseAuthorization();

           

        }

    }

}

so in startup class below highlighted code is called exception filter config.

services.AddMvc(config => {

                            config.Filters.Add(typeof(GlobalExceptionFilter));

                         });