Use DMVs to determine based on past executions which indexes would be beneficial in that database.

The following SQL should be run in Production SQL server (off hours). It basically uses DMVs to determine based on past executions which indexes would be beneficial in that database. It recommends what indexes could be created on the specified tables.

SELECT
 migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) AS improvement_measure
 ,
 'CREATE INDEX [missing_index_' + CONVERT (varchar, mig.index_group_handle) + '_' + CONVERT (varchar, mid.index_handle)
 + '_' + LEFT (PARSENAME(mid.statement, 1), 32) + ']'
 + ' ON ' + mid.statement
 + ' (' + ISNULL (mid.equality_columns,'')
 + CASE WHEN mid.equality_columns IS NOT NULL AND mid.inequality_columns IS NOT NULL THEN ',' ELSE '' END
 + ISNULL (mid.inequality_columns, '')
 + ')'
 + ISNULL (' INCLUDE (' + mid.included_columns + ')', '') AS create_index_statement
--, migs.*, mid.database_id, mid.[object_id]
FROM sys.dm_db_missing_index_groups mig
INNER JOIN sys.dm_db_missing_index_group_stats migs ON migs.group_handle = mig.index_group_handle
INNER JOIN sys.dm_db_missing_index_details mid ON mig.index_handle = mid.index_handle
WHERE migs.avg_total_user_cost * (migs.avg_user_impact / 100.0) * (migs.user_seeks + migs.user_scans) > 10
ORDER BY migs.avg_total_user_cost * migs.avg_user_impact * (migs.user_seeks + migs.user_scans) DESC

 

IdentityServer3 Custom Views for login

So in my endeavors to support Facebook, Google, Outlook and etc login providers the middle ware IdentityServer3 on git has proven worth its weight in gold.

If you need to customize the views for a client, let us suppose a customer wants the login page to be branded for their company logo….What??… the nerve of them.. lol… Seriously, it is extremely easy… See this link: [ branding ]  Perhaps they want to use a bootstrap theme, different layout or various validation rules… See that link…

UP NEXT:

My next post will be about using this with an Angular2 application. Much easier than I even thought!

IdentityServer3 Login Form + Anonymous Auth on IIS

When you deploy to IIS be sure to edit the web sites Authentication settings

Anonymous Authentication: Enabled

Basic Authentication: Disabled

Windows Authentication: Disabled

Digest Authentication: Disabled

Forms Authentication: Disabled

ASP.NET Authentication: Disabled

With this setup, you will not automatically be logged in via your Domain Account, but instead prompted to login via the Middleware Login Page.

If you want your users to use their windows login, you can enable Windows Authentication.

 

DEV/OP: Execute Stored Procedure against database using Powershell and ODBC

I had a need to execute a stored procedure stored in a ASE IQ database version 12. (VERY OLD).

I could only use a ODBC command…

Here is an example powershell example


param(
 [parameter(Mandatory=$true)][string]$Environment= "DEV"
)

$SybaseLogin = ""
$SybasePassword = ""
$IQLogin = ""
$IQPassword = ""
$EmailRecipients = "email"

# Determine script location for PowerShell
$PowerShellDir = Split-Path $script:MyInvocation.MyCommand.Path
Write-Host "Current Powershell script directory is $PowerShellDir"

$DllFullPath = "C:\Program Files\DataDirect\Connect for ADO.NET 3.3\DDTek.Sybase.dll"#$PowerShellDir + "\DDTek.Sybase.dll"
$Global:IqConnectionString = ""
$Global:SybaseConnectionString = ""
$Global:PY = ""
$Global:ObsDate = ""
$Global:SybaseData = @()

#============================================================================
# Initialize-SqlModule
#
# This method will set up all db connections
#============================================================================
function Initialize-SqlModule {

 param(
 [Parameter(Mandatory=$true)][string] $DbEnv,
 [Parameter(Mandatory=$true)][string] $DbSybaseLogin,
 [Parameter(Mandatory=$true)][string] $DbSybasePassword,
 [Parameter(Mandatory=$true)][string] $DbIQLogin,
 [Parameter(Mandatory=$true)][string] $DbIQPassword,
 [Parameter(Mandatory=$true)][string] $PowerShellDir
 )

 try {
 [reflection.assembly]::loadfile($DllFullPath)
 }
 catch [Exception] {
 Write-Host "Error loading sybase dll. Check location on next line."
 Write-Host $DllFullPath
 Write-Host $_.Exception.Message
 return "Error"
 }

 switch ( $DbEnv)
 {
 "DEV"
 {
 $Global:IqConnectionString = ""
 $Global:SybaseConnectionString = ""
 }
 "TAC"
 {
 $Global:IqConnectionString = " "
 $Global:SybaseConnectionString = " "
 }
 "PROD"
 {
 $Global:IqConnectionString = " "
 $Global:SybaseConnectionString = " "
 }
 default
 {
 Write-Host "Environment $Environment is not a valid parameter"
 exit -1
 }
 }
 return "OK"
}


#============================================================================
# Invoke-GetIqDataTables
#============================================================================
function Invoke-GetIqDataTables {
 param(
 [Parameter(Mandatory=$true)][string] $SqlToRun
 )

 $dataset = New-Object System.Data.DataSet

 $DbConn = New-Object System.Data.Odbc.OdbcConnection
 $DbConn.ConnectionString = $Global:IqConnectionString
 $DbConn.Open()
 $DBCmd= $DbConn.CreateCommand()
 $DbCmd.CommandTimeout = 3000
 $DBCmd.CommandText = $SqlToRun
 $adapter = New-Object System.Data.Odbc.OdbcDataAdapter $DBCmd
 $adapter.Fill($dataSet) | Out-Null
 $DBConn.Close()
 $DBConn.Dispose()

 return $dataset
}


#============================================================================
# Invoke-GetIqDataTables
#============================================================================
function Invoke-GetSybaseDataTables {
 param(
 [Parameter(Mandatory=$true)][string] $SqlToRun
 )

 $dataset = New-Object System.Data.DataSet

 $DbConn = New-Object DDTek.Sybase.SybaseConnection
 $DbConn.ConnectionString = $Global:SybaseConnectionString
 $DbConn.Open()
 $DBCmd= $DbConn.CreateCommand()
 $DbCmd.CommandTimeout = 3000
 $DBCmd.CommandText = $SqlToRun
 $adapter = New-Object DDTek.Sybase.SybaseDataAdapter $DBCmd
 $adapter.SelectCommand.Connection = $DbConn
 $adapter.Fill($dataSet) | Out-Null
 $DBConn.Close()
 $DBConn.Dispose()

 return $dataset
}


#============================================================================
# New-IqWeeklyOBS
#============================================================================
function New-IqWeeklyOBS {

 # Get todays date.
 # If todays date is less then July 1 then subtract 1 from current year and build PYXXXX
 # If todays date is july 1 or greater then use current year and build PYXXXX.
 $today =(Get-date).AddDays(-1).AddYears(-1).ToString("dd/mm/yyyy")
 $Global:PY = (Get-date).Year
 $july1 = (Get-Date -Day 1 -Month 7)
 if( $today -lt $july1) {
 $Global:PY = [int]$Global:PY - 1
 }

 # build SQL
 $SqlToRun = [string]::Format(“EXECUTE dbo.OBSWeekly ""{0}"",""{1}"",""PY{2}""”,"DEV",$today,$Global:PY)
 


 #$SqlToRun = " declare @obs_dt varchar(10) "
 #$SqlToRun += " select @obs_dt = '$Global:ObsDate' "

 #$SqlToRun += " select c.ctr_shname, 0 as avg_act_obs ,convert(numeric(10,2), (ISNULL(cdo.planned_male_res,0) + ISNULL(cdo.planned_male_non_res,0) + ISNULL(cdo.planned_female_res,0) + ISNULL(cdo.planned_female_non_res,0))) as plan_obs "
 #$SqlToRun += " from ctr_daily_obs cdo, center c "
 #$SqlToRun += " where c.ctr_id = cdo.ctr_id and cdo.obs_dt = Convert(DATE,'01/01/2016',101) "
 #$SqlToRun += " group by c.ctr_shname,cdo.planned_female_res, cdo.planned_female_non_res, cdo.planned_male_non_res, cdo.planned_male_res "
 #$SqlToRun += " order by c.ctr_shname "

 Write-Host $SqlToRun;

 $FoundTables = Invoke-GetIqDataTables $SqlToRun
 $IqData = @()

 ForEach ( $oneRow in $FoundTables[0].Tables.Rows)
 {
 $ObjectRow = New-Object System.Object
 $ObjectRow | Add-Member -type NoteProperty -Name ctr_shname -Value $oneRow.ctr_shname
 $ObjectRow | Add-Member -type NoteProperty -Name avg_act_obs -Value $oneRow.avg_Act_obs
 $ObjectRow | Add-Member -type NoteProperty -Name plan_obs -Value $oneRow.plan_obs
 $IqData += $ObjectRow
 }

 return $IqData
}


#============================================================================
#
# Execution Starts
#
#============================================================================
$Result = Initialize-SqlModule $Environment $SybaseLogin $SybasePassword $IQLogin $IQPassword $PowerShellDir
if ($Result -eq "OK" )
{
 Write-Host "Enter the word: DEV"
 # get data
 New-IqWeeklyOBS

 
}

VS Code Bootstap intellisense

Great tool, just wanted to re-post about it here

https://marketplace.visualstudio.com/items?itemName=ecmel.vscode-html-css

This gives you css class support in vs code when working with bootstrap or whatever you have in your resource json file.

So in the image below you can see I am adding a bootstrap panel and have access to the various classes…  nice…

 

vscodebootstrap

 

 

 

 

 

 

How to migrate from ASP.NET MVC 4 to ASP.NET MVC 5

http://patrickdesjardins.com/blog/how-to-migrate-from-asp-net-mvc-4-to-asp-net-mvc-5


I plan to actually post the entire article here if the original author gives me permission…

 

 

 

 


Other conversion articles:

The official article by MS:

https://www.asp.net/mvc/overview/releases/how-to-upgrade-an-aspnet-mvc-4-and-web-api-project-to-aspnet-mvc-5-and-web-api-2

http://stvault.net/post/How-to-properly-upgrade-ASPNET-MVC-3-to-MVC-5-project

http://blog.jessehouwing.nl/2013/12/upgrade-your-existing-aspnet-mvc4.html

 

Angular 2 notes

First credit to Deborah Kurata for her excellent course @ plural sight.

All my code is located here: github repo which is a cloned version from Deborah’s course code…  It is great for trying out new concepts!

Angular Modules

Every angular application has at least one module class which is the root module.   Of course that module is bootstrapped (in main.ts)  in order to run (launch) the application.

  1. They must be decorated with @NgModule  a metadata class decorator.
  2. Should be exported (public) so other component templates can use them
  3. Often import other modules
  4. Could provide services to any application component
  5. Aid in organization of application, often referred to as feature modules.  These are then imported into the root module

Quick Start Angular Application folder structure

QuickStart Angular folder structure inside the app folder

The application root module – AppModule

This module is used to tell Angular how the application is pieced together.  As noted earlier, all Angular applications must has at least one Angular Module.

Following conventions, its name is AppModule.  For step a step by step guide on how to create this manually, see this link.

Here is a most basic example as created from the QUICK-START app.

import { NgModule } from '@angular/core';
import { BrowserModule } from '@angular/platform-browser';

import { AppComponent } from './app.component';

@NgModule({
 imports: [ BrowserModule ],
 declarations: [ AppComponent ],
 bootstrap: [ AppComponent ]
})
export class AppModule { }

Here you will notice the Import statements which pull in resources the application will need.  Since I work with browser based applications my app.module.ts will almost always use the BrowserModule (more notes on it below).

NgModue observations

  1. This sets up the metadata used in the module.
  2. All metadata items are functions.
  3. NgModule is an interface with many options like: providers, declarations, imports, export and etc…
  4. For details on the module and the metadata options see this link.

BrowserModule

  1. Provides services for launching and running your application in a browser
  2. Re-Exports the CommonModule which provides access to Angular Directives (NgIf, NgFor…)

The BrowserModule provides services that are vitally important to run your application in a modern browser like chrome of I.E. Edge.

Note:  Do not import BrowserModule in any other module. Also you cannot lazy load this module.

After wiring up what is required we will will want to begin importing our actual application components.  For example, we import a component known as app.component.ts as generated in the quick-start files.

import { Component } from '@angular/core';

@Component({
 selector: 'my-app',
 template: '

{{title}}

‘, }) export class AppComponent { title = ‘Minimal NgModule’; }

This simply shows a title using a form of data binding known as string interpolation .  The value for the title comes from the exported class property title.

@NgModule

  1. You should notice in the app.module.ts the declarations and bootstrap metadata properties for NgModule have AppComponent in them.
  2. The declarations property list identifies the application’s only component, the root component.
  3. The bootstrap property is used to identify the AppComponent as the bootstrap component.  It is what kicks things off.

When an Angular application kicks off, it puts the HTML rendering of AppComponent in the DOM. In the quick start it is as defined in its @Component metadata selector.  For more info on the @Component decorator see this link.

The magic of Bootstrapping

So where does the actual bootstrapping occur?  Well, in most cases the index.html file will call the system.js.config.js file which in turn calls the main.ts (js) file when then kicks off the actual bootstrapping process.  If you are using a Angular Quickstart or CLI tool you will not need to worry about this for the vast majority of applications you will write in Angular.

Maybe an illustration will aid:

Angular Bootstrap process

Angular Bootstrap process

The above example is how the dynamic bootstrapping occurs. Which basically means the browser is shipped the Angular compiler  which then compiles our application in the browser.  For development or small applications this is fine.  But for performance you want to use the static bootstrapping method known as Ahead-Of-Time (AOT) Compilation.  Read more here about AOT.

Basic steps to adding a Component

First decide is this a stand alone component or will it be a nested component?

A stand alone will not have a selector property in the @Component metadata which a nested will need the selector defined.

I like to begin with an empty shell of a html file which will be our template.  I greatly prefer a html template over a inline template.

Naming convention for the file.

This will vary but I like to place my files in a folder that relates the purpose.  For example I am going to create a component that will list out a set of products.

So I create a folder called Products under the app folder.

Then I add a file called product-list.component.html

Then I add a product-list.component.ts file

In the product-list.component.ts file I will need a few basic things to kick the process off…

  1. create the class and besure to export it.
  2. Add the @Component decorator and them import it @angular/core
  3. Add the metadata needed.  Since the list is a root component it will not be nested, no selector is needed. But I will need a templateUrl and the moduleId decorator.
  4. Add an import for your data service if needed and include any services in the constructor.
  5. If you are going to respond to events you will need to include them from the core library.

Here is an example:

import { Component, OnInit } from '@angular/core';


import { IProduct } from './product';
import { ProductService } from './product.service';

@Component({
 // selector: 'pm-products', we are no longer nesting 
 moduleId: module.id,
 templateUrl: 'product-list.component.html',
 styleUrls: ['product-list.component.css']
})
export class ProductListComponent
 implements OnInit {
 //Image Properties
 imageWidth: number = 25;
 imageMargin: number = 2;
 showImage: boolean = false;

 pageTitle: string = 'Product Listing';
 listFilter: string;

 products: IProduct[];

 errorMessage: string;

 /** 
 * Long Contructor Method
 * private _productService: ProductService;
 *
 * constructor(productService: ProductService) {
 * this._productService = productService;
 * }
 */

 constructor(private _productService: ProductService) { }
 ngOnInit(): void {
 console.log('In OnInit');
 console.log('Module ID: ' + module.id);
 //this.products = this._productService.getProducts();
 this._productService.getProducts()
 .subscribe(products => this.products = products,
 error => this.errorMessage = error);
 }

 // Methods
 toggleImage(): void {
 this.showImage = !this.showImage;
 }
 onRatingClicked(message: string): void {
 this.pageTitle = 'Product List: ' + message;
 }
}

The Html Template

{{pageTitle}}
Filter by:

Filtered by: {{listFilter}}

ProductCodeAvailablePrice5 Star Rating
{{product.productName}}{{product.productCode}}{{product.releaseDate}}{{product.price | currency:’USD’:true:’1.2-2′}}

{{showImage ? ‘Hide’ : ‘Show’}} Image

Of course as your application grows you should use the angular module system to aid in organizing your code and help with lazy loading…

The product module code:

import { NgModule } from '@angular/core';
import { RouterModule } from '@angular/router';

import { SharedModule } from '../shared/shared.module';
import { ProductService } from './product.service';
import { ProductListComponent } from './product-list.component';
import { ProductFilterPipe } from './product-filter.pipe';
import { ProductDetailComponent } from './product-detail.component';
import { ProductDetailGuard } from './product-guard-service';

import { ProductRoutingModule } from './product-routing.module';

@NgModule({
 declarations: [
 ProductListComponent,
 ProductDetailComponent,
 ProductFilterPipe
 ],
 imports: [
 SharedModule,
 ProductRoutingModule
 ],
 providers: [
 ProductService,
 ProductDetailGuard]
})
export class ProductModule {

}

This all of course must be wired up in the app.module.ts code in the app root folder…

Here is an example:

import { NgModule } from '@angular/core';
import { BrowserModule } from '@angular/platform-browser';
//import { FormsModule } from '@angular/forms';
import { HttpModule } from '@angular/http';
import { RouterModule } from '@angular/router';

import {AppRoutingModule} from './app-routing.module';
import { AppComponent } from './app.component';
import { WelcomeComponent } from './home/welcome.component';
import { ProductModule } from './products/product.module';

@NgModule({
 imports: [BrowserModule,
 HttpModule,
 ProductModule,
 AppRoutingModule], // If you want to use # in your routes, set the following .forRoot([], {useHash: true}) - I think this is default for IIS7
 // directives, components and pipes by Angular, External or third party 
 declarations: [AppComponent, WelcomeComponent], // Our directives, components and pipes
 bootstrap: [AppComponent]
})
export class AppModule { }

StructureMap, IoC -MVC Controller parameterless constructors

Example VS 2015 Solution [ link ] for this article

StructureMap (SM) is one of the most popular IoC tools for MVC out there.  But, one of the biggest issues for first timers is the web has tons of post on it and often they are not clear on the version and etc…

For this Article I am using:

  • .Net 4.5.2
  • MVC 5
  • StructureMap  3.1.1.134
  • StructureMap MVC5

Official documentation:  http://structuremap.github.io/

As you may already know, the MVC default controller factory defaults to parameterless constructors.  When you change a Controller it accept a parameter, the .net mvc compiler will complain about it.  Often you will see an error that reads: No parameterless constructor define for this object. To solve this issue you will need to implement your own ControllerFactory and get it to pass request for controllers through structuremap (SM) or you can use the SM MVC 5 Nuget package in Visual Studio (later in this article). SM, will then inspect each request and attempt to resolve those dependencies with a concrete implementation.

A mvc controller class with a parameter based constructor as the default.

public class ContactController : Controller
{
  private readonly IContactRepository iRepository;

  public ContactController(IContactRepository repository)
  {
    iRepository = repository;
  }

  public ActionResult Index()
  {
    return View(iRepository.GetContacts());
  }
}

In the above code you will see I have a constructor that is expecting some kind of repository for contacts.  So as I was saying above, you will need to create a Default Controller Factory.   This grunt work can be avoided if you use any of the nuget packages for MVC and Structure Map.  But for if you want to do it manually you will need to wire things up yourself.

A bit more succinctly, since MVC framework has its own default implementation of the IControllerFactory interface which is DefalutControllerFactory and since it is not a sealed class so you can extend it.  So you override the virtual method GetControllerInstance and change the implementation to whatever we need and then register this in the global.asax.cs file start up.  (I suggest you not do this…)

Instead, you can make this much easier by leveraging a nuget package to do most of the grunt work for you.

An example of implementing the DefaultControllerFactory:

using System;
using System.Web.Mvc;
using StructureMap;

namespace ContactManager.IoC
{
  public class DependencyControllerFactory : DefaultControllerFactory
  {
    protected override IController GetControllerInstance(Type controllerType)
    {
        return ObjectFactory.GetInstance(controllerType) as Controller;
    }
  }
}

 

Leveraging your IDE’s tools…

In visual studio 2015 I created a simple mvc application. See this [ link ] for the code accompanying this blog post…

Then I went into Nuget and added StructureMap MVC5. ( https://www.nuget.org/packages/StructureMap.MVC5/ )

 

What caught me off guard initially was that I did not see any code in the application_start method referencing the StructureMap Dependency resolver or such.  After digging about I figured out it was using an assembly trick to wire up SM before the application start.  See this link for a quick explanation of PreApplicationStartMethod.

In App_Start folder you will see a file called StructuremapMvc.cs.  Open it and look for these two entries..

[assembly: PreApplicationStartMethod(typeof(StructuremapMvc), “Start”)]
[assembly: ApplicationShutdownMethod(typeof(StructuremapMvc), “End”)]

This is what wires up your mappings of concrete implementations of interfaces located in the DefaultRegistry.cs file in the DependencyResolution folder in your solution.

So all you have to do now is update the DefaultRegistry.cs file with you specific needs using the For<>.Use<>(); syntax.

Code-ON…

A few misc reads and link:

Look here for more details on the mvc 5 nuget package for MVC  https://github.com/webadvanced/Structuremap.MVC5

For specific integratinos: http://structuremap.github.io/integrations/

General Nuget package:  https://www.nuget.org/packages/structuremap/

Structuremap and entity framework can be a bitch, read this:

Entity Framework – The best example is this blog post from Jimmy Bogard.

So some quick unstructured notes…