TSQL – Using Pivot Table

A Pivot table in TSQL is useful to spread row data as columns. Row values can be defined into multiple columns.

A row in SQL Server represents an entity and a column as an attribute of the entity.

To build a pivot table, you will need to define three columns.

  • Column 1 – Row grouping
  • Column 2 – Create new columns based on the values
  • Column 3 – Aggregated values for the row and columns

Steps to build a pivot table:

  1. Select based dataset
  2. Create temporary result set using Common Expression Table (CTE)
  3. Apply PIVOT operator

Example

This is the original table

Rate_IdDescR01R02R03R04
1Plan14.1025.2028.5040.50
2Plan21.5039.5041.8060.90

This will be the final result

PlanSilverGold
Plan-114.1021.50
Plan-225.2039.50
Plan-328.5041.80
Plan-440.5060.90

Step 1: Create the table

CREATE TABLE #Rate (
Rate_Id int,
Rate_Desc varchar(100),
R01 decimal(17,2),
R02 decimal(17,2),
R03 decimal(17,2),
R04 decimal(17,2)
);

Step 2: Insert records

INSERT INTO #Rate VALUES (1,'Plan',14.10,25.20,28.50,40.50);
INSERT INTO #Rate VALUES (2,'Plan',21.50,39.50,41.80,60.90);

Step 3: Use cross apply to create additional rows

SELECT * FROM #Rate
cross apply
(
   select 1,R01 union
   select 2,R02 union
   select 3,R03 union
   select 4,R04 
) r (RowNo, Rate)

Output

Rate_IdRate_DescR01R02R03R04RowNoRate
1Plan14.1025.2028.5040.50114.10
1Plan14.1025.2028.5040.50225.20
1Plan14.1025.2028.5040.50328.50
1Plan14.1025.2028.5040.50440.50
2Plan21.5039.5041.8060.90121.50
2Plan21.5039.5041.8060.90239.50
2Plan21.5039.5041.8060.90341.80
2Plan21.5039.5041.8060.90460.90

Step 4: Use PIVOT to change rows to columns

WITH CTE_Rates As (
SELECT * FROM #Rate
cross apply
(
   select 1,R01 union
   select 2,R02 union
   select 3,R03 union
   select 4,R04 
) r (RowNo, Rate))
SELECT [Plan], [1] as [Silver], [2] as [Gold]
FROM (
SELECT Rate_Id,CONCAT(Rate_Desc,'-',RowNo) as [Plan], Rate_Desc, RowNo,Rate
FROM CTE_Rates) r
PIVOT(
 Max(Rate)
  for Rate_Id IN ([1],[2])
) p
;

Final table result from Step 4

PlanSilverGold
Plan-114.1021.50
Plan-225.2039.50
Plan-328.5041.80
Plan-440.5060.90

In this example I have converted Rate_Id 1 and 2 as Silver and Gold. Also R01, R02, R03, R04 as Plan-1, Plan-2, Plan-3, Plan-4.

TSQL – Last Time Table was Updated

TSQL – Last Time Table was Updated

If you looking for a script to find out when was the last time a particular table was updated then here it is:

SELECT OBJECT_NAME(OBJECT_ID) AS DatabaseName, last_user_update,*
FROM sys.dm_db_index_usage_stats
WHERE database_id = DB_ID( 'DATABASENAME')
AND OBJECT_ID=OBJECT_ID('TABLENAME')
SELECT
tbl.name
,ius.last_user_update
,ius.user_updates
,ius.last_user_seek
,ius.last_user_scan
,ius.last_user_lookup
,ius.user_seeks
,ius.user_scans
,ius.user_lookups
FROM
sys.dm_db_index_usage_stats ius INNER JOIN
sys.tables tbl ON (tbl.OBJECT_ID = ius.OBJECT_ID)
WHERE ius.database_id = DB_ID()

This normally happens when you don’t have columns such as DateCreated and UpdatedDate in the table.

Live as if you were to die tomorrow. Learn as if you were to live forever.. -Mahatma Gandhi

Windows 10 – Adjusting Search Box in File Explorer

Windows 10 – Adjusting Search Box in File Explorer

In windows 10 the search box area in file explorer is too wide, well for me personally. The good thing about windows 10 is that you have the ability to adjust the search box according to your personal needs.

Move your mouse in between address location box and search box unless you see your mouse cursor changed to Horizontal Move. Slide left or right according to your needs.

The search function will perform correctly with the specified search text even though your specified text is not completely visible in the search box.

File Explorer with horizontal cursor

Happy Learning…..

ASP.NET MVC – Project Items

ASP.NET MVC – Project Items

/App_Data

This folder contains private data such as XML Files or databases if you are using SQL Server Express, SQLite, or other file based repositories.

IIS web server will not server any other the contains from this folder.

/App_Start

This folder has core configuration settings about the project, including route definitions, filters and content bundles.

/Areas

Areas is a way to partition large application into smaller pieces.

/bin

Bin consists of the compiled assembly of MVC application along with any referenced assemblies that are not in the GAC.

/Content

This folder holds the content for CSS files and images.

/Controllers

This folder contains all the controllers.

/Models

This folder has all the view model and domain model classes.

/Scripts

This directory holds the JavaScript libraries.

/Views

This directory holds the views and partial views, grouped by the controller name. The web.config file prevents the views to be served by IIS. It must be rendered by action methods in controller class.

/View/Shared

This directory holds layouts and views which are not specific to a single controller.

/Views/Web.config

It contains the configuration required to make views work with ASP.NET and prevents views from being served by IIS and the namespaces imported into views by default.

/Global.asax

This is the global ASP.NET application class. Its code-behind class (Global.asax.cs) is the place to register routing configuration, as well as set up any code to run on application initialization or shutdown, or when unhandled exceptions occur.

/Web.config

This is the configuration file for the application.

Happy Days!!

Live as if you were to die tomorrow. Learn as if you were to live forever.. -Mahatma Gandhi

ASP.NET MVC – Controllers

Controllers

The controller is responsible for the request made by the browser, each request is mapped to a particular controller.

The controller can return a view or redirect to another controller.

The controller is just a class derived from the base System.Web.Mvc.Controller class.

Controller Actions

Action is a method on a controller which gets called when you enter a URL in the browser address.

A controller action needs to be a public method of a controller class.

Controller action methods cannot be overloaded.

Action Results

A controller returns an action result in response to the browser request.

ViewResult – Represents HTML and markup.

EmptyResult – Represents no result.

RedirectResult – Represents a redirection to a new URL.

JsonResult – Represents a JavaScript Object Notation result that can be used in an AJAX application.

JavaScriptResult – Represents a JavaScript script.

ContentResult – Represents a text result.

FileContentResult – Represents a downloadable file (with the binary content).

FilePathResult – Represents a downloadable file (with a path).

FileStreamResult – Represents a downloadable file (with a file stream).

An action result is not passed directly instead the following is passed:

View – Returns a ViewResult action result.

namespace MvcApplication1.Controllers
{
    public class BookController : Controller
    {
        public ActionResult Index()
        {
            // Add action logic here
            return View();
        }
    }
}

Redirect – Returns a RedirectResult action result.

RedirectToAction – Returns a RedirectToRouteResult action result.

using System.Web.Mvc;

namespace MvcApplication1.Controllers
{
    public class CustomerController : Controller
    {
        public ActionResult Details(int? id)
        {
            if (!id.HasValue)
                return RedirectToAction("Index");

            return View();
        }
    }
}

RedirectToRoute – Returns a RedirectToRouteResult action result.

Json – Returns a JsonResult action result.

JavaScriptResult – Returns a JavaScriptResult.

Content – Returns a ContentResult action result.

using System.Web.Mvc;

namespace MvcApplication1.Controllers
{
    public class StatusController : Controller
    {
        public ActionResult Index()
        {
            return Content("Hello World!");
        }
    }
}

File – Returns a FileContentResult, FilePathResult, or FileStreamResult depending on the parameters passed to the method.

HttpUnauthorizedResult – forces the user to login.

 

Live as if you were to die tomorrow. Learn as if you were to live forever.. -Mahatma Gandhi

C# – Working with Excel

Working with Excel

Library

Include

using Excel = Microsoft.Office.Interop.Excel

Instantiating the excel application class

Excel.Application excelApp = new Excel.Application();

To make the object visible

excelApp.Visible = true; //false to turnoff visibility

Add a workbook

Excel.Workbook openExcel = excelApp.Workbooks.Add(Type.Missing);

or to add an existing excel file

var path = @"C:\\excel.xlsx";
Excel.Workbook openExcel = excelApp.Workbooks.Open(path);

To add blank sheets

Excel.Sheets sheets = openExcel.Worksheets;
Excel._Worksheet worksheet = (Excel.Worksheet)sheets.Add(Type.Missing);

To select active sheet

Excel._Worksheet workSheet = (Excel.Worksheet)excelApp.ActiveSheet;

or To select sheet by name

Excel._Worksheet workSheet = (Excel.Worksheet)excelApp.Worksheets[sheetname];

Writing to the cell

workSheet.Cells[1, "A"] = "Fund";

Set the column width

workSheet.Columns.ColumnWidth = 18;

Using get_Range and format header

Excel.Range headerColumnRange = workSheet.get_Range("A2", "G2");
headerColumnRange.Font.Bold = true;
headerColumnRange.Font.Color = 0xFF0000;
headerColumnRange.WrapText = true;
headerColumnRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;

Name of the worksheet

workSheet.Name = name.Substring(0, len);

Format cells

workSheet.Cells[startCell, strAlpha].NumberFormat = "0.00";

Autofit columns

workSheet.Columns.AutoFit();

Save

openExcel.Save();

SaveAs .xlsx

openExcel.SaveAs(strFullFilePathNoExt, Excel.XlFileFormat.xlOpenXMLWorkbook, Missing.Value,
    Missing.Value, false, false, Excel.XlSaveAsAccessMode.xlNoChange, 
    Excel.XlSaveConflictResolution.xlUserResolution, true, 
    Missing.Value, Missing.Value, Missing.Value);

Close excel

openExcel.Close();

Copy or Cloning

foreach(Worksheet sheet in workBook.Worksheets)
{
    var newbook = app.Workbooks.Add(1);
    sheet.Copy(newbook.Sheets[1]);

    newbook.SaveAs(FileDropLocation + "\\" + sheet.Name);
    newbook.Close();
}

workBook.Close();

Delete worksheet

workSheet.Worksheets[2].Delete();

Border

Microsoft.Office.Interop.Excel.Borders border = celLrangE.Borders;  
border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;  
border.Weight = 2d; 

C# – LINQ to read XML

LINQ to read XML

Loading the XML File

XElement and XDocument derive from XContainer.

1. XElement class

Reads the XML elements (attributes and children)

Implements IXmlSerializable.

XElement xelement = XElement.Load(file);
IEnumerable employees = xelement.Elements();
//Read the entire XML
foreach(var employee in employees)
{
	Console.WriteLine("Reading Employee: == " + employee);
}

2. XDocument class

Reads the entire XMl document.

Reads from the single root element.

XDocument xdocument = XDocument.Load(file);
IEnumerable employees = xdocument.Elements();
foreach(var employee in employees)
{
     Console.WriteLine("Reading Employee: == " + employee);
}

Reading the Single Element

foreach(var employee in employees)
{
     Console.WriteLine("Reading Employee: == " + employee.Element("Name").Value);
}

Reading Multiple Elements

foreach(var employee in employees)
{
Console.WriteLine("Reading Employee: {0} == {1}", employee.Element("Name").Value, employee.Element("EmpId").Value);
}

Filter Elements

XElement xelement = XElement.Load(file);
var name = from nm in xelement.Elements("Employee")
    where (string)nm.Element("Sex") == "Female"
    select nm;

    Console.WriteLine("Information about Female Employees");
    foreach(XElement x in name)
    {
	Console.WriteLine(x);
    }

Filter Element within another Element

XElement xelement = XElement.Load(file);
var addresses = from address in xelement.Elements("Employee")
	where (string)address.Element("Address").Element("City") == "Alta"
	select address;

Console.WriteLine("Information about Employees in Alta City");
foreach(XElement x in addresses)
{
	Console.WriteLine(x);
}

Find Nested Elements using Descendants

XElement xelement = XElement.Load(file);
Console.WriteLine("List of all Zip codes");
foreach(XElement x in xelement.Descendants("Zip"))
{
    Console.WriteLine((string)x);
}

Sorting Elements

XElement xelement = XElement.Load(file);
IEnumerable codes = from code in xelement.Elements("Employee")
	let zip = (string)code.Element("Address").Element("Zip")
        orderby zip
	select zip;
Console.WriteLine("List and Sort all Zip Codes");
			 
foreach (string zp in codes)
	Console.WriteLine(zp);

Measuring Execution Times

Measuring Execution Times

The SQL Server Management Studio shows query measurement time in seconds. If we are concern about performance then in-depth measurement is required such as milliseconds.

Using Statistics

set statistics time on

-- your query

set statistics time off

Messages Tab
SQL Server parse and compile time:
CPU time = 67 ms, elapsed time = 67ms

Setting the timing as default for every query
Query -> query options -> advanced -> Execution
check the “set statistics time” checkbox
Check “set statistics IO” checkbox

Using Client Statistics

Ways to turn on the Client Statistics

  • Menu: Query -> Include client Statistics
  • Toolbar: Click button Include Statistics
  • Keyboard: Shift+Alt+S

Properties

Client Execution Time Time the trial was started.
Query Profile Statistics
Number of INSERT, DELETE and UPDATE statements The number of Insert, Delete or Update statements that were executed in that particular trail.
Rows affected by INSERT, DELETE or UPDATE statements Number of rows that were affected by Insert, Delete or update statement part of your trial.
Number of SELECT statements Number of select statement that were executed under that particular trial execution. It includes fetch statements to retrieve rows from cursors.
Rows returned by SELECT statements Rows selected as part of that trail execution.
Number of transactions User transactions used in a trail execution.
Network Statistics How much traffic is moving from client to the server and back.
Number of server roundtrips Number of times request sent to server and number of time reply received from server in a trail execution.
TDS packets sent from client Number of TDS packets client has sent to the database server under a trial execution.
TDS packets received from server The number of TDS packets received by client from database server under a trial execution.
Bytes sent from client The number of bytes that the client has sent to database server under a trial execution. Includes spaces selected after query as well.
Time Statistics How much time was spent processing on the client versus how much time was spent waiting for the server in milliseconds.
Client processing time The cumulative amount of time that the client spent in executing code while the query was executed
Total execution time The cumulative amount of time (in milliseconds) that the client spent processing while the query was executed, including the time that the client spent waiting for replies from the server as well as the time spent executing code.
Wait time on server replies The cumulative amount of time (in milliseconds) that the client spent while it waited for the server to reply.

By default Client Statistics shows up to 10 trails. After 10 trails, every trail position get decreased by 1 and trail 0 get removed from list.

Reset Client Statistics
Query Menu -> Reset Client Statistics

Using Execution Time as Variable

DECLARE @StartTime datetime
DECLARE @EndTime datetime
SELECT @StartTime=GETDATE()

--Query goes here

SELECT @EndTime=GETDATE()
SELECT DATEDIFF(ms, @StartTime, @EndTime) AS [Duration in millisecs]

C# – LinkedList

LinkedList

LinkedList<T> is a generic doubly linked list.

Doubly linked list is a chain of nodes in which each references the node before, the node after and the actual element.

Benefit – element can always be inserted efficiently anywhere in the list, since it just involves creating a new node and updating a few references.

Implements IEnumerable<T> and ICollection<T>.

LinkedList<T> supports copying to an array for indexed processing and obtaining an enumerator to support foreach statement.

public sealed class LinkedListNode
{
	public LinkedList List { get; }
	public LinkedListNode Next { get; }
	public LinkedListNode Previous { get; }
	public T Value { get; set; }
}
var tune = new LinkedList();
tune.AddFirst ("do");                           tune.Dump(); // do
tune.AddLast ("so");                            tune.Dump(); // do - so

tune.AddAfter (tune.First, "re");               tune.Dump(); // do - re- so
tune.AddAfter (tune.First.Next, "mi");          tune.Dump(); // do - re - mi- so
tune.AddBefore (tune.Last, "fa");               tune.Dump(); // do - re - mi - fa- so

tune.RemoveFirst();                             tune.Dump(); // re - mi - fa - so
tune.RemoveLast();                              tune.Dump(); // re - mi - fa

LinkedListNode miNode = tune.Find ("mi");
tune.Remove (miNode);                           tune.Dump(); // re - fa
tune.AddFirst (miNode);                         tune.Dump(); // mi- re - fa