Search This Blog

Loading...

Tuesday, September 20, 2016

C# DataTable throwing ReadOnlyException - "Column X is read only."

While using typed DataSets you might encounter this exception.

    Column X is read only.

Its because the default value for ReadOnly property of DataColumn in Typed DataSets is True. You can set it False in the properties window, but this will take impact in all code segments wherever this DataTable is being used.

So selectively setting ReadOnly property to false is a better option if you need this in only some parts of the program. You can loop through all columns of the table and set its ReadOnly property to false:

 foreach (DataColumn column in table.Columns) 
 {
    column.ReadOnly = false; 
 }

Or you can set for single column at a time:

    table.Columns["MyColumnName"].ReadOnly = false; 

Sunday, August 7, 2016

Setting Up Angular 2 with Visual Studio 2015 - ASP.NET Core

In this post I will explain how to setup Angular2 with Visual Studio 2015 hosted inside ASP.NET Core project. Mostly the steps are taken from the Quick Start tutorial at https://angular.io.

We have to make sure that the following requisites are being installed.

  • Visual Studio 2015 - You can find community edition here. If you already have VS 2015 community edition installed, make sure that it has also installed Update3.
  • ASP.Net Core - You can find ASP.Net Core on the same link, it needs VS 2015 Update3.

Let start developing sample project. Create a new ASP.NET Core project.

Select empty template.

Open project.json file and add the static files support to serve an HTML page. For this, add following line in the dependencies node under the Kestrel dependency.

"Microsoft.AspNetCore.Server.Kestrel": "1.0.0-rc2-final"
"Microsoft.AspNetCore.StaticFiles": "1.0.0-rc2-final"

So the final project.json file should be similar to this:

{
  "dependencies": {
    "Microsoft.NETCore.App": {
      "version": "1.0.0-rc2-3002702",
      "type": "platform"
    },
    "Microsoft.AspNetCore.Server.IISIntegration": "1.0.0-rc2-final",
    "Microsoft.AspNetCore.Server.Kestrel": "1.0.0-rc2-final",
    "Microsoft.AspNetCore.StaticFiles": "1.0.0-rc2-final"
  },

  "tools": {
    "Microsoft.AspNetCore.Server.IISIntegration.Tools": {
      "version": "1.0.0-preview1-final",
      "imports": "portable-net45+win8+dnxcore50"
    }
  },

  "frameworks": {
    "netcoreapp1.0": {
      "imports": [
        "dotnet5.6",
        "dnxcore50",
        "portable-net45+win8"
      ]
    }
  },

  "buildOptions": {
    "emitEntryPoint": true,
    "preserveCompilationContext": true
  },

  "runtimeOptions": {
    "gcServer": true
  },

  "publishOptions": {
    "include": [
      "wwwroot",
      "web.config"
    ]
  },

  "scripts": {
    "postpublish": [ "dotnet publish-iis --publish-folder %publish:OutputPath% --framework %publish:FullTargetFramework%" ]
  }
}

To host our Angular app, we will add an html page index.html under wwwroot folder.

To enable serving static files, like this html page, we have to modify the startup code. Open up Startup.cs file and remove any code in Configure method, and place this line:

app.UseStaticFiles();

At this stage, lets pause and test the work done till now, to see our index.html page running in browser. Follow these steps:

Open up VS Developer Command Prompt and navigate to the project folder, where web.config or project.json files are located. Type "dotnet run". App will be compiled and Kestrel web server will started.

Open browser and navigate to http://localhost:5000/index.html. You will see index.html page.

Try make changes in index.html and refresh the page in browser to see the changes reflected.

Now our html page is running in without Angular support. Lets proceed to next phase.

Press Ctrl+C on same command prompt to shutdown server.

Type "npm init" to initialize package.json file.

It will prompt for some inputs, you can provide any reasonable input, or just leave blank and press ENTER. And finally it will ask for "yes/no" option, then type "yes" and press ENTER.

Here your package.json file is initiated.

In Visual Studio - Solution Explorer you can see that "dependencies" node got a sub-folder npm. Open package.json file. Add "dependencies" and "devDependencies" nodes as below:

"dependencies": {
 "@angular/common": "2.0.0-rc.4",    
 "@angular/compiler": "2.0.0-rc.4",
 "@angular/core": "2.0.0-rc.4",
 "@angular/forms": "0.2.0",
 "@angular/http": "2.0.0-rc.4",
 "@angular/platform-browser": "2.0.0-rc.4",
 "@angular/platform-browser-dynamic": "2.0.0-rc.4",
 "@angular/router": "3.0.0-beta.1",
 "@angular/router-deprecated": "2.0.0-rc.2",
 "@angular/upgrade": "2.0.0-rc.4",
 "systemjs": "0.19.27",
 "core-js": "^2.4.0",
 "reflect-metadata": "^0.1.3",
 "rxjs": "5.0.0-beta.6",
 "zone.js": "^0.6.12",
 "angular2-in-memory-web-api": "0.0.14"  
},
  "devDependencies": {
    "typescript": "^1.8.10",
    "gulp": "^3.9.1",
    "path": "^0.12.7",
    "gulp-clean": "^0.3.2",
    "gulp-concat": "^2.6.0",
    "gulp-typescript": "^2.13.6",
    "typings": "^1.3.1",
    "gulp-tsc": "^1.2.0"
  },

So final package.json file should be similar to this:

{
  
"name": "webapplication11",
"version": "1.0.0",
"description": "",
"main": "index.js",

"dependencies": {
 "@angular/common": "2.0.0-rc.4",
 "@angular/compiler": "2.0.0-rc.4",
 "@angular/core": "2.0.0-rc.4",
 "@angular/forms": "0.2.0",
 "@angular/http": "2.0.0-rc.4",
 "@angular/platform-browser": "2.0.0-rc.4",
 "@angular/platform-browser-dynamic": "2.0.0-rc.4",
 "@angular/router": "3.0.0-beta.1",
 "@angular/router-deprecated": "2.0.0-rc.2",
 "@angular/upgrade": "2.0.0-rc.4",
 "systemjs": "0.19.27",
 "core-js": "^2.4.0",
 "reflect-metadata": "^0.1.3",
 "rxjs": "5.0.0-beta.6",
 "zone.js": "^0.6.12",
 "angular2-in-memory-web-api": "0.0.14"
},
  "devDependencies": {
    "typescript": "^1.8.10",
    "gulp": "^3.9.1",
    "path": "^0.12.7",
    "gulp-clean": "^0.3.2",
    "gulp-concat": "^2.6.0",
    "gulp-typescript": "^2.13.6",
    "typings": "^1.3.1",
    "gulp-tsc": "^1.2.0"
  },

  
"scripts": {
    "test": "echo \"Error: no test specified\" && exit 1"
  },
 
"author": "",
"license": "ISC"

}

Add new item, with TypeScript JSON Configuration File item template at root of the project, with default file name(tsconfig.json). Add following lines in "compilerOptins" node under "target" node.

    "suppressImplicitAnyIndexErrors": true,
    "emitDecoratorMetadata": true,
    "experimentalDecorators": true,
    "module": "system",
    "moduleResolution": "node"

And this line just above(outside) the compilerOptions node:

"compileOnSave": true,

Final tsconfig.json file should be similar to this:

{
  "compileOnSave": true,

  "compilerOptions": {
    "noImplicitAny": false,
    "noEmitOnError": true,
    "removeComments": false,
    "sourceMap": true,
    "target": "es5",
    "suppressImplicitAnyIndexErrors": true,
    "emitDecoratorMetadata": true,
    "experimentalDecorators": true,
    "module": "system",
    "moduleResolution": "node"

  },
  "exclude": [
    "node_modules",
    "wwwroot"
  ]
}

Add new item, with Gulp Configuration File item template at root of the project, with default file name(gulpfile.js). Update the file content with following:

var gulp = require('gulp');

gulp.task('copy', function () {
    gulp.src('./app/**/*.*')
        .pipe(gulp.dest('./wwwroot/app'));
});

gulp.task('watch', function () {
    gulp.watch('./app/**/*.*', ['copy']);
});

gulp.task('nodemodules', function () {
    gulp.src('./node_modules/core-js/**/*.js')
        .pipe(gulp.dest('./wwwroot/node_modules/core-js'));
    gulp.src('./node_modules/@angular/**/*.js')
        .pipe(gulp.dest('./wwwroot/node_modules/@angular'));
    gulp.src('./node_modules/zone.js/**/*.js')
        .pipe(gulp.dest('./wwwroot/node_modules/zone.js'));
    gulp.src('./node_modules/systemjs/**/*.js')
        .pipe(gulp.dest('./wwwroot/node_modules/systemjs'));
    gulp.src('./node_modules/reflect-metadata/**/*.js')
        .pipe(gulp.dest('./wwwroot/node_modules/reflect-metadata'));
    gulp.src('./node_modules/rxjs/**/*.js')
        .pipe(gulp.dest('./wwwroot/node_modules/rxjs'));

});

gulp.task('default', ['nodemodules', 'copy', 'watch']);

That's all things setup for Angular, now lets come the the real Angular part.

Create new folder called "app" under project root. Add new item main.ts to app folder, using TypeScript file template.

Place the following code in main.ts

import { bootstrap }    from '@angular/platform-browser-dynamic';
import { AppComponent } from './app.component';
bootstrap(AppComponent);

Add new item app.component.ts to app folder, using TypeScript file template. Place the following code in app.component.ts.

import { Component } from '@angular/core';
@Component({
  selector: 'my-app',
  template: '<h1>My First Angular 2 App</h1>'
})
export class AppComponent { }

Create new JavaScript file startup.js under wwwroot folder, with following content.

/**
 * System configuration for Angular 2 samples
 * Adjust as necessary for your application needs.
 */
(function (global) {
    // map tells the System loader where to look for things
    var map = {
        'app': 'app', // 'dist',
        '@angular': 'node_modules/@angular',
        'angular2-in-memory-web-api': 'node_modules/angular2-in-memory-web-api',
        'rxjs': 'node_modules/rxjs'
    };
    // packages tells the System loader how to load when no filename and/or no extension
    var packages = {
        'app': { main: 'main.js', defaultExtension: 'js' },
        'rxjs': { defaultExtension: 'js' },
        'angular2-in-memory-web-api': { main: 'index.js', defaultExtension: 'js' },
    };
    var ngPackageNames = [
      'common',
      'compiler',
      'core',
      'http',
      'platform-browser',
      'platform-browser-dynamic',
      'router',
      'router-deprecated',
      'upgrade',
    ];
    // Individual files (~300 requests):
    function packIndex(pkgName) {
        packages['@angular/' + pkgName] = { main: 'index.js', defaultExtension: 'js' };
    }
    // Bundled (~40 requests):
    function packUmd(pkgName) {
        packages['@angular/' + pkgName] = { main: '/bundles/' + pkgName + '.umd.js', defaultExtension: 'js' };
    }
    // Most environments should use UMD; some (Karma) need the individual index files
    var setPackageConfig = System.packageWithIndex ? packIndex : packUmd;
    // Add package entries for angular packages
    ngPackageNames.forEach(setPackageConfig);
    var config = {
        map: map,
        packages: packages
    };
    System.config(config);
})(this);

Now, update index.html with the following content:

<html>
<head>
    <title>Angular 2 QuickStart</title>
    <meta charset="UTF-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <link rel="stylesheet" href="styles.css">
    <!-- 1. Load libraries -->
    <!-- Polyfill(s) for older browsers -->
    <script src="node_modules/core-js/client/shim.min.js"></script>
    <script src="node_modules/zone.js/dist/zone.js"></script>
    <script src="node_modules/reflect-metadata/Reflect.js"></script>
    <script src="node_modules/systemjs/dist/system.src.js"></script>
    <!-- 2. Configure SystemJS -->
    <script src="startup.js"></script>
    <script>
      System.import('app').catch(function(err){ console.error(err); });
    </script>
</head>
<!-- 3. Display the application -->
<body>
    <my-app>Loading...</my-app>
</body>
</html>

And add new css file named styles.css in wwwroot folder with the following content taken from angular 5 min quickstart tutorial:

h1 {
  color: #369;
  font-family: Arial, Helvetica, sans-serif;
  font-size: 250%;
}
body {
  margin: 2em;
}
 /*
  * See https://github.com/angular/angular.io/blob/master/public/docs/_examples/styles.css
  * for the full set of master styles used by the documentation samples
  */

That's finished, try run your project and you will see output message:

My First Angular 2 App

You can download the sample project from my github repo Angular2-GettingStarted-With-VS2015. You may need to restore all packages, and npm(upgrade to latest version) and .NET Core to run it.

References:

Thursday, July 21, 2016

MS SQL Server - Returning the Inserted Rows

In this post, we will understand how to return the inserted rows. For example, we are inserting records in a table from Stored Procedure, then we want to return the resulting inserted values to the calling application.

For this we use the OUTPUT clause. OUTPUT clause adds a result set (in a virtual table INSERTED) to the INSERT statement, which contains specified set of columns and the set of rows that were inserted. Let see an example to understand this concept.

Let create a sample table and insert some records.

declare @tblCustomerOrders table (
 CustomerCode varchar(50),
 CustomerName varchar(50),
 OrderAmount decimal(18, 2)
)

insert into @tblCustomerOrders (CustomerCode, CustomerName, OrderAmount)
values 
('C001', 'Imran', 100),
('C002', 'Salman', 40),
('C003', 'Kamran', 500)

Here is nothing special, we just created a table variable and inserted 3 rows. But it will only display a message showing 3 row(s) affected as below, but do not return any data to the calling application.

Now, rewrite the above insert statement and add the OUTPUT clause to return inserted rows. In the following example, the OUTPUT clause will return CustomerCode, CustomerName and OrderAmount.

insert into @tblCustomerOrders (CustomerCode, CustomerName, OrderAmount)
OUTPUT INSERTED.CustomerCode, INSERTED.CustomerName, INSERTED.OrderAmount
values 
('C001', 'Imran', 100),
('C002', 'Salman', 40),
('C003', 'Kamran', 500)

It will output the following result set:

You can also output only selected columns, like you specify in regular SELECT clause. In the following exmaple, the OUTPUT clause is only returning CustomerCode and OrderAmount, CustomerName column will not be returned in the resulting table.

insert into @tblCustomerOrders (CustomerCode, CustomerName, OrderAmount)
OUTPUT INSERTED.CustomerCode, INSERTED.OrderAmount
values 
('C001', 'Imran', 100),
('C002', 'Salman', 40),
('C003', 'Kamran', 500)

The output will be:

Also for simplicity, you can use * to return all columns. In the following exmaple, the OUTPUT clause will return all the columns that will be used in insert statement.

insert into @tblCustomerOrders (CustomerCode, CustomerName, OrderAmount)
OUTPUT INSERTED.*
values 
('C001', 'Imran', 100),
('C002', 'Salman', 40),
('C003', 'Kamran', 500)

The output will return all columns:


Thursday, June 23, 2016

MS SQL Server - Window Functions - Aggregate Functions

Aggregate functions perform a calculation on a set of values and return a single value, frequently used with the GROUP BY clause of the SELECT statement.

In this post, we will try using aggregate functions with and without OVER clause. I recommend you to read the previous post on Window Ranking Functions

Let create a sample table and insert some records.

declare @tblCustomerOrders table (
 CustomerName varchar(50),
 OrderAmount decimal(18, 2)
)

insert into @tblCustomerOrders (CustomerName, OrderAmount)
values 
('Imran', 100),
('Imran', 40),
('Kamran', 500),
('Salman', 350),
('Kamran', 430),
('Salman', 210),
('Imran', 70),
('Salman', 105),
('Imran', 750)

In this table, if we want to see the sum of OrderAmount for each customer, we use the following query.

SELECT 
  CustomerName
 ,SUM(OrderAmount) AS 'Sum'
FROM @tblCustomerOrders

But when you run this query you will get error:

Column '@tblCustomerOrders.CustomerName' is invalid in the select list 
because it is not contained in either an aggregate function or the 
GROUP BY clause.

Since we are missing the GROUP BY clause, we got this error. To work it as expected, we have to add GROUP BY clause with CustomerName field.

SELECT
  CustomerName
 ,SUM(OrderAmount) AS 'Sum'
FROM @tblCustomerOrders
GROUP BY CustomerName

Now you will get the expected result.

Lets see another scenario, if we want to list all customers with their SUM() of OrderAmounts, but we also need to list individual order amounts in separate column. Try this query:

SELECT
  CustomerName
 ,OrderAmount -- (individual OrderAmount)
 ,SUM(OrderAmount) AS 'Sum'
FROM @tblCustomerOrders
GROUP BY CustomerName

Since, OrderAmount field is not mentioned in GROUP BY clause, so you will get error message display:

Column '@tblCustomerOrders.OrderAmount' is invalid in the select list 
because it is not contained in either an aggregate function or the 
GROUP BY clause.

Now add OrderAmount field in GROUP BY clause along-with CustomerName field.

SELECT 
  CustomerName
 ,OrderAmount -- (individual OrderAmount)
 ,SUM(OrderAmount) AS 'Sum'
FROM @tblCustomerOrders
GROUP BY CustomerName, OrderAmount

This query will not generate error, but you get the following output, which is not the desired one.

Because each row is also grouped by OrderAmount, so the SUM(OrderAmount) is same as of OrderAmount.

How to fix this, there comes the OVER clause. Although there may be some other workarounds you can use to get the desired output, but the simpler one is using the OVER clause. You can put your aggregate functions in conjunction with OVER clause and there is no need to add GROUP BY clause.

Try getting SUM with OVER clause:

SELECT
  CustomerName
 ,SUM(OrderAmount) OVER() AS 'Sum'
FROM @tblCustomerOrders

This will generate the output:

But this is not the expected result, it is calculating the SUM of all records and not calculating SUM for individual Customers. As I also mentioned in my last post on Window Functions, add PARTITION BY clause in OVER clause.

The output will be:

As you might noticed, unlike the working query with GROUP BY clause discussed above, this query is displaying the list of all records along-with SUM, i.e. CustomerNames are repeating with no of occurrences exists in the table. So you can also add OrderAmount field to display along-with SUM filed.

SELECT
  CustomerName
 ,OrderAmount
 ,SUM(OrderAmount) OVER(PARTITION BY CustomerName) AS 'Sum'

FROM @tblCustomerOrders

Generates the output:

This gives us the solution that we can list down all customer records with SUM of OrderAmount and also showing individual OrderAmounts.

Similarly we can use other aggregate functions like COUNT(), AVG(), MIN(), MAX()

SELECT
  CustomerName
 ,OrderAmount
 ,COUNT(OrderAmount) OVER(PARTITION BY CustomerName) AS 'Count'   
 ,SUM(OrderAmount) OVER(PARTITION BY CustomerName) AS 'Sum'
 ,AVG(OrderAmount) OVER(PARTITION BY CustomerName) AS 'Avg'
 ,MIN(OrderAmount) OVER(PARTITION BY CustomerName) AS 'Min'
 ,MAX(OrderAmount) OVER(PARTITION BY CustomerName) AS 'Max'
 
FROM @tblCustomerOrders

Will generate the output as expected:

References:

Thursday, May 26, 2016

MS SQL Server - Window Functions - Ranking Functions

A window function is one that can be applied to a set of rows, The word ‘window’ is used to refer to the set of rows that the function works on. It provides a way to work with data grouped into logical windows.

The OVER clause used to define a window or set of rows within a query result set. Then we use a window function to computes a value for each row in the subset. OVER clause can be used to compute aggregated values such as average, sum, min, max, or a top N per group results.

The window function can be of two types:

  • Ranking function
  • Aggregate function.

In this post, I will discuss Ranking Window Functions.

Depending of on whether you’re using a ranking window function or aggregate window function, the OVER clause will need to be implemented accordingly. If we use the OVER clause with a ranking function, we can specify an optional PARTITION BY clause and a required ORDER BY clause. However, if we are working with with an aggregate function, then a PARTITION BY clause must be used, and should not have an ORDER BY clause.

Working with Ranking Window Functions

Ranking functions return an integer ranking value for each row or group in a partition/window.

Transact-SQL provides the following ranking functions:

  • ROW_NUMBER
  • RANK
  • DENSE_RANK
  • NTILE

Let see each function in action with an example.

  • ROW_NUMBER:

    Returns a sequential integer number for each row in the window.

    Example: Create a sample table and insert some records to query.

    declare @tblItems table (
     ItemCode varchar(5),
     ItemCategory varchar(50),
     StockQty int
    )
    
    insert into @tblItems(ItemCode, ItemCategory, StockQty)
    values 
    ('I001', 'Books', 10),
    ('I002', 'Books', 20),
    ('I003', 'Laptops', 25),
    ('I004', 'Laptops', 30),
    ('I005', 'Laptops', 20),
    ('I006', 'SmartPhones', 50),
    ('I007', 'SmartPhones', 30),
    ('I008', 'Books', 10)
    

    The following example calculates a row number for @tblTest based on ItemCode.

    SELECT 
     Itemcode
    ,ROW_NUMBER() OVER(ORDER BY itemcode) AS 'ROW_NUMBER()'
    FROM @tblItems
    

    Here is the output:

  • RANK:

    Define an integral rank value for each row in the window. It returns a same rank value for repeated column values, however, the next number in the ranking sequence is skipped by the number of repeating rows (also known as gap). It does not always return consecutive integers.

    The following example ranks the @tblItems with sort by ItemCategory. Since the ItemCodes I001, I002, I008 have same ItemCategory, these are tied, these are ranked one, and the next rank for item I003 is returned as 4, i.e. skipped the values 2 and 3 from the previous tied items.

    SELECT 
     ItemCode
    ,ItemCategory
    ,RANK() OVER (ORDER BY ItemCategory) AS 'RANK()'
    FROM @tblItems
    

    Here is the output:

  • DENSE_RANK:

    Define an integral rank value for each row in the window, without any gaps in the ranking. It also returns a same rank value for repeated column values. If values in the ranking column are the same, they receive the same rank. The next number in the ranking sequence is the next number in the sequence. The numbers returned by the this function do not have gaps and always have consecutive ranks.

    The following example ranks the @tblItems with sort by ItemCategory. Since the ItemCodes I001, I002, I008 have same ItemCategory, these are tied, these are ranked one, and the next rank for item I003 is returned as 2, i.e. it does not skipped the gap values (2 and 3) from the previous tied items.

    SELECT 
     ItemCode
    ,ItemCategory
    ,DENSE_RANK() OVER (ORDER BY ItemCategory) AS 'DENSE_RANK()'
    FROM @tblItems
    

    Here is the output:

  • NTILE:

    Divides the result set into the number of groups specified as an argument to the function. The groups are numbered, starting at one. For each row, NTILE returns the number of the group to which the row belongs.

    The following example divides rows into 3 groups of items based on ItemCategory. Because the total number of rows is not divisible by the number of groups, the first two groups have 3 rows and the remaining groups have 2 rows each.

    SELECT 
     ItemCode
    ,ItemCategory
    ,NTILE(3) OVER(ORDER BY ItemCategory) AS 'NTILE(3)' 
    FROM @tblItems
    

    Here is the output:

    Let see another example of NTILE with group argument as 4. The following example divides rows into 4 groups of items based on ItemCategory. Now the total number of rows is divisible by the number of groups, so each group has equal number of rows (2 rows in each group).

    SELECT 
     ItemCode
    ,ItemCategory
    ,NTILE(4) OVER(ORDER BY ItemCategory) AS 'NTILE(4)' 
    FROM @tblItems
    

    Here is the output:

References: