ASP.NET MVC Areas to organize an Application

Introduction: Asp.net MVC allows us to split an application into multiple logical groups/modules which are called areas. In this demo, I’ll create two areas – Finance and Marketing. Each area will hold and display employee list of its own department.

Steps:

Create a MVC 5 project.

Add Area: In the solution explorer, right click the project | Add| Area | enter name (Finance in this example). Add another area and name it (Marketing in this example).

After adding the areas, the structure should look like as shown in screenshot below.

Check configuration: Two important files - FinanceAreaRegistration.cs and MarketingAreaRegistration.cs files are being added. These files are used to configure the routing system for the area. 

The configuration of the area is called from application_start method in global.aspx.

Implement Finance Module:

Add Finance class and financeRepository class under the “Models” folder for “Finance” area.

The classes are being implemented with the following code:

Add controller (FinanceController)  under controller folder. Write the following code in FinanceController.cs file.

Add view: Right click on Index action method of financeController and create a view called “index” by accepting default option. The structure of “Finance” area should look like this.

Write the following code in “index.cshtml” file under “view” folder. This view will implement “finance” model.

 Debug the project. The output shows “Finance” and “Marketing” tabs. Click on “Finance” tabs and here is the output.

Also, the "Marketing" area has been implemented.Here is the output:

SSIS Pivot Transformation with UI

Pivoting converts rows into columns.We can perform this task using SSIS Pivot Transformation component.

SQL server 2012 added an UI to Pivot transformation component and makes it simpler. We can configure the pivot operation by setting the options in pivot dialog box.

Sample data: It pulls Total number of items sold by product by month in 2013.

SQL:

SELECT DISTINCT DATENAME([month], SalesDate) AS [Month], productName, SUM(NumberSold) AS Total

FROM     Product

GROUP BY DATENAME([month], SalesDate), productName

ORDER BY productName

Output: 

We want to visualize data as shown below:

Steps:

Start| All Program| SQL Server 2012 |SQL Server Data Tools.

Create a new project – File |New Project |Integration Services Project.

Add “ Data Flow Task ”. Double click the “ Data Flow Task”. Add “ OLEDB Data source”. Double click the “OLEDB data source” and set connection and SQL command as shown below.

Add “Pivot Transform” from toolbox and connect “OLEDB data source” to “Pivot Transform”. Double click the “Pivot Transform” to open the “Pivot UI”. Set the “Pivot Key”, ”Set Key” and “Pivot Value”.

Under “Generate Pivot Output columns from Values”, enter the month name and then click the “Generate Columns Now” button. Click “OK”.

Add “OLEDB Destination” and connect it with “Pivot Transform”. Double click the “OLEDB Destination” and set connection and create a table to output data.

Run the Package.

The package ran successfully. Now open SSMS and query the table where you sent the data.

Output:

Data Quality Services (SQL Server 2012)

Introduction: SQL Server Data Quality Services enables you to cleanse, match, standardize with user generated or third party knowledge. Here I’ll show you how to standardize data. The file contains customer name and country name.The country field has values such as "us","usa","united states of america" and we'll standardize it to "u.s.a".

Steps:

Open DQS client and connect to Server.

Click “New Knowledge Base”.

Enter name, description and select “knowledge discovery”.

 Click next.

Select “Excel” as data source and browse your file. The file I’m using has customer full name, email and country.

Select country, email and custname as source column.

Create  domain “country”,”custname” and “email” and map the source columns to domains just created. 

Click next. Click Start.

The profiler statistics show the “country” column has five unique values. Click “next.

Select the domain “country” and see the values. Change the values to invalid if value is not “U.S.A” and correct the values to “U.S.A”.

Click “finish” and publish the KB.

We’ll use this KB to standardize country name in our next project.

Open DQS client and click on “New Data Quality Project” under create or maintain data quality project.

Enter name and select "data standardization" we just created as “knowledge base”. Under “activity”, select “cleansing” and click “next”.

Select Excel as data source and browse your data. Map Source column to domain name.Click “next”.Click “start”.

The profiler statistics shows it corrected two records of country domain.

 Click “next”. Select the “corrected” tab and note that “US” and “USA” have been corrected to “U.S.A” as per our KB. Select the “new” tab and note the values are not in our previously created KB.

 We need to add these new values to our KB and then we can run our data to standardize country name.

Click “next”. It shows the cleansing result. Select export destination and export data.

We can use the KB to SSIS to integrate DQS with SSIS.

Test Driven Development (TDD) in Visual Studio

Introduction: Visual studio 2012/2013 supports Test Driven Development (TDD) through its unit testing framework.This demonstrates how to build test method in c# using unit testing framework and viewing results in test explorer.

Steps:

 Create a new project as shown below:

The solution explorer should look like this:

Create a new Class: Right click on TDD| Add New Item| Add Class and name it Calculator.

Write a basic test method: Double click UnitTest1.cs under TDD.Tests folder.

Generate the method from test:

Place the cursor on sum and then on the short cut menu choose Generate, Method Stub.

This will create a sum method in Calculator.cs.

Run the unit Test: On the Test menu,choose Unit Test,All Tests. The Test Explorer opens and displays the result.

The test fails because sum() is not implemented.

Let's implement the sum() in Calculator.cs file.

Now run the unit test again.

It fails because the expected result and actual result is not same.

Lets modify the sum() as follows:

Run the unit test again. The method passes this time.

Storage and Management of unstructured data using File Table and File Stream

Introduction: SQL Server provides a special table of files, also referred to as a File Table, which lets an application to integrate its storage and data management components to allow non-transactional access. A File Table is a specialized user table with a pre-defined schema that stores FILESTREAM data, as well as file and directory hierarchy information and file attributes. Each row in file table represents a file or directory.  It supports full-text search and semantic search over files and documents. FILESTREAM enables SQL Server-based applications to store unstructured data, such as documents and images, on the file system. Transact-SQL statements can insert, update, query, search, and back up FILESTREAM data. FILESTREAM integrates the SQL Server Database Engine with an NTFS file system.

Steps:

Enable Filestream:

1. On the Start menu, point to All Programs, point to Microsoft SQL Server 2012, point to Configuration Tools, and then click SQL Server Configuration Manager.

2. In the list of services, right-click SQL Server Services, and then click Open.
3. In the SQL Server Configuration Manager snap-in, locate the instance of SQL Server on which you want to enable FILESTREAM.
4. Right-click the instance, and then click Properties.
5. In the SQL Server Properties dialog box, click the FILESTREAM tab.
6. Select the Enable FILESTREAM for Transact-SQL access check box.
7. If you want to read and write FILESTREAM data from Windows, click Enable FILESTREAM for file I/O streaming access. Enter the name of the Windows share in the Windows Share Name box.
8. If remote clients must access the FILESTREAM data that is stored on this share, select  Allow remote clients to have streaming access to FILESTREAM data.
9. Click Apply.

In Sql server Management studio,click new query to display the query editor and enter the following sql statement:

Create File Stream Enabled Database:

CREATE DATABASE Demo

 ON

 PRIMARY (NAME = Book, FILENAME = 'C:\demo\Book.mdf'),

  FILEGROUP BookGroup CONTAINS FILESTREAM (NAME = BookFS, FILENAME = 'C:\demo\BookFS')

  LOG ON (NAME = 'BookLog', FILENAME = 'C:\demo\BookLog.ldf');       

GO

After execution of the statements, you’ll see the folder as seen in below pic.

Enable Non-Transactional Access at database level

ALTER DATABASE Demo

SET FILESTREAM (NON_TRANSACTED_ACCESS = FULL, DIRECTORY_NAME = N'BookFS')

Create a File Table:

Use Demo

CREATE TABLE BookDocument AS FileTable

GO

I created two text files – collegeText.txt and KidsBook.txt. The text files contain Title,Author,Publish Date and Price. Now, right click on “BookDocument” in the object explorer and select “Explore File Table Directory”, a new window will open. Drop the collegeText.txt and KidsBook.txt files here.

Run the select statement:

SELECT *

FROM dbo.bookdocument

Enable full text search

--Create Full-Text Catalog

Create FullText Catalog demoFulltextSearch as Default

GO

--Create FullText Index

Create FullText Index on dbo.bookDocument

(name Language 1033, File_stream type column file_type Language 1033)

key Index PK__BookDocu__5A5B77D5A5D5A685

on demoFullTextSearch

with Change_Tracking Auto, StopList=system

Go

Execute the following sql:

SELECT *

FROM dbo.bookdocument

WHERE CONTAINS (file_stream, 'c#');

This returns the record of collegeText.txt.