The Most Perfect CRUD Operations!

As you know, HTTP is RESTful Protocol with the HTTP Verbs meant for CRUD mapping initially.

But I am seeing may Wrong Doings in Implementation like:

  • Wrong HTTP Method for Operation
  • Wrong Naming Conventions

Perfect Mapping

Here I would like to Map in the Perfect Way recommended by Microsoft.

  • CREATE operation can be mapped to POST
  • READ operation can be mapped to GET
  • UPDATE operation can be mapped to PUT
  • DELETE operation can be mapped to DELETE

Note

CREATE is essentially mapped to POST and Visual Studio Code Generation also recommends same.

Example

Here I am creating a CRUD Controller with the above Method Representations.

    [Route(“api/[controller]”)]
public class EmployeeController : Controller
{
private static IDictionary<int, string> _list = new Dictionary<int, string>();

public EmployeeController()
{
if (_list.Count == 0)
{
_list.Add(1, “Amar”);
_list.Add(2, “Akbar”);
_list.Add(3, “Antony”);
}
}

// GET: api/<controller>
         [HttpGet]
public IEnumerable<string> Get()
{
return _list.Values;
}

// GET api/<controller>/5
         [HttpGet(“{id}”)]
public string Get(int id)
{
string result = string.Empty;

_list.TryGetValue(id, out result);

return result;
}

// POST api/<controller>
         [HttpPost]
public void Post([FromBody]string value)
{
_list.Add(_list.Keys.Max() + 1, value);
}

// PUT api/<controller>/5
         [HttpPut(“{id}”)]
public void Put(int id, [FromBody]string value)
{
_list[id] = value;
}

// DELETE api/<controller>/5
         [HttpDelete(“{id}”)]
public void Delete(int id)
{
_list.Remove(id);
}
}

Execution

On execution we can play with Postman against the same Controller Name and changing the HTTP Verbs.

image

Following are the actual UI screenshots.

List

image

Insert

image

Update

image

Delete

image

Summary

The reason I call his the Most Perfect CRUD operations is because the same controller name is used for all the 4 operations.

Make Singleton Class Thread-Safe

Singleton Design Pattern ensures that Only One Instance is created.  But, It will impose challenges in the Multi-threaded scenarios.

  • Multiple Thread causes Issues If entering the GetInstance() method
  • We need to use Lock keyword to ensure only 1 Thread enters the Instance Creation
  • We need to add additional check on Instance is Null inside the lock statement

public class ConnectionPool

{
private static ConnectionPool instance;
private static object _lockObject = new object();

public static ConnectionPool GetInstance()
{
if (instance == null)
{
lock (_lockObject)
{
if (instance != null)
instance = new ConnectionPool();
}
}

return instance;
}

private ConnectionPool()
{
// doesn’t matter what’s here
}

}

Reference

I have written a book Design Patterns in C# few years back.

Cosmos DB

In this article we can explore about Cosmos Db database & its usage.

Cosmos DB

Azure Cosmos DB is a globally distributed, multi-model, planet-scale, low-latency, high-availability, No-SQL database stored as JSON format.

image

In the olden days Cosmos DB was called as Azure DocumentDB.

Planet Scale

Azure Cosmos DB will be replicated based on Geography region & hence low-latency. There will be Primary Write-database in one central location & Multiple Secondary Read-databases in multiple locations.

Due to the multiple geographical location availability low-latency & high-availability is possible.

Multi-API Support

Cosmos DB supports Multiple-API support:

· SQL API

· Mongo API

· Graph API

· Table API

· Cassandra API

JSON Format

JSON Format is used to store the data in Cosmos DB.

Consistency

Cosmos DB have 5 consistency levels to ensure consistency, availability and latency trade-offs.

Create Cosmos DB

We can create Cosmos DB from Azure Portal as shown below.

Open Azure Portal > Create new resource > Cosmos DB

image

image

Click the Ok button & Wait for few minutes for the Creation to be completed.

You will get the following screen after creation.

image

Start by Add Collection. Then Add a New Document.

image

You can add new properties in the document. The data is stored as JSON file. Ensure the ID is unique.

image

Now we can query the database using SQL. Choose the New SQL Query option > Enter SELECT statement & Execute the Query. You will get the following results as displayed.

image

Summary

In this article we have explored about Cosmos Db database & its usage.

Installing SSAS Template in Visual Studio 2017

If you have not Selected Data Processing templates during your Visual Studio Installation, then you need to Download the SSDT for VS 2017 from following URL:

https://docs.microsoft.com/en-us/sql/ssdt/download-sql-server-data-tools-ssdt?view=sql-server-2017

Installation

On running the downloaded executable file, you will get the following prompt.  Choose the First Option if you want to work with All Templates in current Visual Studio instance.  Also, Select the Analysis Services option.

image

Click the Next button to start Installation.

image

Once the Installation is completed, You will get the following message.

image

Inside Visual Studio

Open Visual Studio. If you can see following Templates then you are good!

image

Summary

This post is a Part of Starting with Azure Analysis Services series. Please check other Articles for the continuity.

My DAX Days…

In this post I would like to Record my DAX Learnings during my SQL Server Analysis Services days.

SQL Server Analysis Services

It is 1000X faster than SQL Server since the Data is stored in Memory in Compressed Format.

DAX

DAX stands for Data Analysis Expressions.  It is widely used in Analysis Services, Power BI etc. 

DAX looks similar to Excel Formulas.

DAX Measure

DAX Measure is the actual Formula.

DAX Variables

DAX Variables will be executed once they are Invoked

EVALUATE

Evaluates the Expression.

Eg: EVALUATE ( ‘DimProducts’)

Eg: EVALUATE( ‘DimProducts’ ) ORDER BY ‘DimProducts'[Date] DESC  –Sorting Example

For Scalar Values, Use EVALUATE (ROW(“Column”, Variable)) syntax

Note: Add an EVALUATE in front of the Expression

ROW

Returns a Table with a Single Row.

Eg: EVALUATE ROW(“Total Sales”, SUM(FactSales[Price]), 
         “Total Quantity”, SUM(FactSales[Quantity]))

image

SUMMARY

Returns a summary table.

EVALUATE
SUMMARIZE(
        DimProducts 
      , DimProducts[Id] 
      , DimProducts[Class] 
      , DimProducts[SubClass] 
      , “Sales Total”, SUM(FactSales[Price]) 
      , “Quantity Total”,  SUM(FactSales[Quantity]) 
      ) 

image

CALCULATE

Evaluates an Expression when modified by a Filter.

FILTER

Returns a Subset of Table or Expression.

Eg: EVALUATE
FILTER(DimProducts,DimProducts[Class]=”Home Theatre”)

image

*Note the double quotes

More Filters: https://bit.ly/2L3iOdr

SUM

Sum is an Aggregator.

Eg: EVALUATE
SUMMARIZE (
DimProducts,
“Sum”, SUM ( DimProducts[Price] )
)

image

SUMX

SumX is an Iterator.  Can Add Multiplications into this.

Eg: EVALUATE
SUMMARIZE (
DimProducts,
“Sum”, SUMX (DimProducts, DimProducts[Price] * 100)
)

image

ADDCOLUMNS

Adds calculated columns to the given table

References

https://www.sqlbi.com/

https://msdn.microsoft.com/en-us/query-bi/dax

More DAX Days

In this post I would  like to Publish few more DAX scenarios I have encountered.

Join Tables

We can use CROSSJOIN function on tables to use All Columns required by the SUMMARIZE function.

DEFINE
    VAR JoinTable =
        FILTER (
            CROSSJOIN ( Products, FactSales ),
            DimProducts[Id] = SalesFactSales[ProductId]
        )

    VAR Result Table=
        SUMMARIZE (
            JoinTable ,
            DimProducts[Choice],
            DimProducts[Id],
            FactSales[StoreID],
            FactSales[WeekId],
            “Quantity”, SUMX (
                FILTER ( ‘FactSales’, ‘DimProducts'[Id] = ‘FactSales'[ProductId] ),
                FactSales[Quantity]
            )
        )

 

EVALUATE
ResultTable

SUMMARIZECOLUMNS

SUMMARIZECOLUMNS is recommended than SUMMARIZE because it is Faster & Optimized.

SUMMARIZE still exists for Backwordk Compatibility.

Let us see the Cross Join using SUMMARIZECOLUMNS.

Cross Join with Matching ProductID

DEFINE VAR Results =
SUMMARIZECOLUMNS (
    ‘DimProducts'[ProductID],
    “Sales Amount”,
    SUMX (        FILTER (
            CROSSJOIN ( DimProducts, FactSales ),
            DimProducts[ProductId] = FactSales[ProductId]
        ), FactSales[Quantity] * FactSales[Price] )

)

EVALUATE Results

image

Another way to see the results.

EVALUATE FILTER(CROSSJOIN (
                        DimProducts,
                        FactSales
                    ),
                    DimProducts[ProductId] = FactSales[ProductId]

)

— Returns as many rows as in Sales table

image

Working on Table Variables

We can work on Table Variables to do more Aggregations. Example is given below.

For example to find Average, Use AVERAGEX function.

VAR ResultsTable =
        ADDCOLUMNS (
            SUMMARIZE (
                ProductJoinSales,
                DimProducts[Choice],
                DimProducts[Id]
            ),
            “CalcValue”,  DIVIDE(
                DIVIDE (
                    SUM (
                        FactSales[Quantity]
                        ),
                        COUNT ( FactSales[StoreID] )
                )
            )
        )

VAR Result = AVERAGEX (ResultsTable, [CalcValue]) 

Note Ensure to display the value using ROW function.

EVALUATE ROW(“Average”, Result)

OR Condition in Filter

Or Conditions in Filters are pretty easy.  Use the || operator of C#.

FILTER ( FactSales, FactSales[Price] > 0 || FactSales[Quantity] > 0)

Reference

https://azure-arena.com/2018/08/03/my-dax-days/

https://azure-arena.com/2018/08/01/sql-tables-used-in-dax/