Friday, November 27, 2015

Export DataTable to Excel with Open XML in c#

This code sample will illustrate how we can create Excel file from a data table in c#.
You will need to add DocumentFormat.OpenXml and WindowsBase dll references to make it work.
private static void WriteExcelFile(string outputPath, DataTable table)
{
 using (SpreadsheetDocument document = SpreadsheetDocument.Create(fileToGenerate, SpreadsheetDocumentType.Workbook))
 {
  WorkbookPart workbookPart = document.AddWorkbookPart();
  workbookPart.Workbook = new Workbook();

  WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
  var sheetData = new SheetData();
  worksheetPart.Worksheet = new Worksheet(sheetData);

  Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());
  Sheet sheet = new Sheet() { Id = workbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "Sheet1" };

  sheets.Append(sheet);

  Row headerRow = new Row();

  List<String> columns = new List<string>();
  foreach (System.Data.DataColumn column in table.Columns)
  {
   columns.Add(column.ColumnName);

   Cell cell = new Cell();
   cell.DataType = CellValues.String;
   cell.CellValue = new CellValue(column.ColumnName);
   headerRow.AppendChild(cell);
  }

  sheetData.AppendChild(headerRow);

  foreach (DataRow dsrow in table.Rows)
  {
   Row newRow = new Row();
   foreach (String col in columns)
   {
    Cell cell = new Cell();
    cell.DataType = CellValues.String;
    cell.CellValue = new CellValue(dsrow[col].ToString());
    newRow.AppendChild(cell);
   }

   sheetData.AppendChild(newRow);
  }

  workbookPart.Workbook.Save();
 }
}

Wednesday, June 10, 2015

Configure StyleCop with MSBuild to treat Warnings as Errors

We can easily configure StyleCop with MSBuild to make warnings appear as errors with the help of StyleCop.MSBuild NuGet package.

First go to Package Manager Console and install it with following command.


Install-Package StyleCop.MSBuild
Then unload your project and open the project file to modify it.



Now you have to add "StyleCopTreatErrorsAsWarnings" property inside "PropertyGroup" tag as below.
<Target Name="EnsureNuGetPackageBuildImports" BeforeTargets="PrepareForBuild">
<PropertyGroup>
  <ErrorText>This project references NuGet package(s) that are missing on this computer. Enable NuGet Package Restore to download them.  For more information, see http://go.microsoft.com/fwlink/?LinkID=322105. The missing file is {0}.</ErrorText>
  <StyleCopTreatErrorsAsWarnings>false</StyleCopTreatErrorsAsWarnings>
</PropertyGroup>
<Error Condition="!Exists('..\packages\StyleCop.MSBuild.4.7.49.1\build\StyleCop.MSBuild.Targets')" Text="$([System.String]::Format('$(ErrorText)', '..\packages\StyleCop.MSBuild.4.7.49.1\build\StyleCop.MSBuild.Targets'))" />
</Target>
Almost done, but sometimes auto generated files gives errors when we build the project, to overcome that we need to modify "Settings.StyleCop" file.

Go to project folder and inside that you can find folder called "packages". It contains StyleCop files and inside "tools" folder you can find that setting file. You can open it with a test editor.


Now you can add new value to GeneratedFileFilters as bellow to ignore those generated files.
<Parser ParserId="StyleCop.CSharp.CsParser">
  <ParserSettings>
 <CollectionProperty Name="GeneratedFileFilters">
   <Value>\.g\.cs$</Value>
   <Value>\.generated\.cs$</Value>
   <Value>\.g\.i\.cs$</Value>
   <Value>TemporaryGeneratedFile_.*\.cs$</Value>
 </CollectionProperty>
  </ParserSettings>
</Parser>
Now reload your project and try to build your project. You will be able to see that all the warnings appear as errors.

Wednesday, April 22, 2015

MVC 5 with Unity for Dependency Injection

Dependency Injection allows us to inject objects into a class, instead of constructing them internally. Unity is a dependency injection container that we can use for constructor, property, and method call injections.

You can get good idea about dependency injection by reading answers to this stack-overflow question.

This tutorial is an step-by-step guide with screen-shots where you can learn how to use Unity with Asp.Net MVC5.

I'm going to create sample web application for academic course listing website. There I have Institution and Course as main entities.

I will not connect this sample application with a database and will illustrate with hard-coded data. Let's start...

1. Create Sample Web Application

In this example I'm using Visual Studio 2013 and let's start by creating sample MVC web application.

Check whether you can run the created web application and if there are no issues it should give following output.

2. Install Unity.Mvc5 Library

To install Unity.Mvc5 Library using nuget, we have to go to Package Manager Console and execute the following command.

Install-Package Unity.Mvc5

Wait until it completes the installation.

After completion if you check the Solution Explore, you can see newly added UnityConfig class inside App_Start folder. That's where we register all our components with the container.

3. Update Sample Application

Let's update our sample application now, first I'll add two another Class Library projects called Entities and Services. There Entities project holds all the entity (model) classes we are going to use and Services project will be used to put our service classes.

Then I'll add following classes to created Entity project.

Institution Class

namespace Entities
{
    public class Institution
    {
        public long InstitutionID { get; set; }
        public string Name { get; set; }
        public string Telephone { get; set; }
        public string Address { get; set; }
    }
}

Course Class

namespace Entities
{
    public class Course
    {
        public long CourseID { get; set; }
        public long InstitutionID { get; set; }
        public string Title { get; set; }
        public string Description { get; set; }
    }
}

Next I will create the Services Class Library project and add reference to Entities project from Services project.

Now I will create two service classes for institutions and courses. And also I will add relevant interfaces for those classes.

IInstitutionService Interface

using Entities;

namespace Services
{
    public interface IInstitutionService
    {
        Institution GetInstitutionByID(long institutionID);
    }
}

InstitutionService Class

using Entities;

namespace Services
{
    public class InstitutionService : IInstitutionService
    {
        public Institution GetInstitutionByID(long institutionID)
        {
            return new Institution { InstitutionID = institutionID, Name = "Sample Institution", Address = "Sample Address", Telephone = "0123456789" };
        }
    }
}

ICourseService Interface

using Entities;

namespace Services
{
    public interface ICourseService
    {
        Course GetCourseByID(long courseID);
    }
}

CourseService Class

using Entities;

namespace Services
{
    public class CourseService : ICourseService
    {
        public Course GetCourseByID(long courseID)
        {
            return new Course { CourseID = courseID, Description = "Sample course description", InstitutionID = 1, Title = "Sample Course Title" };
        }
    }
}

Your solution explore should looks like below now with all those projects and classes.

We need to add references to Entities and Service projects within Unity Web Application before we continue.

4. Configure Unity

Let's register our components with the unity container. There first we have to update our UnityConfig class as below.

using Microsoft.Practices.Unity;
using Services;
using System.Web.Mvc;
using Unity.Mvc5;

namespace UnityWebApplication
{
    public static class UnityConfig
    {
        public static void RegisterComponents()
        {
            var container = new UnityContainer();
            
            // register all your components with the container here
            // it is NOT necessary to register your controllers
            
            // e.g. container.RegisterType<ITestService, TestService>();

            container.RegisterType<ICourseService, CourseService>();
            container.RegisterType<IInstitutionService, InstitutionService>();
            
            DependencyResolver.SetResolver(new UnityDependencyResolver(container));
        }
    }
}

This RegisterComponents method can be called within the Application_Start method in Global.asax. We can update our Global.asax as below.

using System.Web.Mvc;
using System.Web.Optimization;
using System.Web.Routing;

namespace UnityWebApplication
{
    public class MvcApplication : System.Web.HttpApplication
    {
        protected void Application_Start()
        {
            UnityConfig.RegisterComponents();

            AreaRegistration.RegisterAllAreas();
            FilterConfig.RegisterGlobalFilters(GlobalFilters.Filters);
            RouteConfig.RegisterRoutes(RouteTable.Routes);
            BundleConfig.RegisterBundles(BundleTable.Bundles);
        }
    }
}

5. Continue with Sample Application

Now we are going to display these institution and course information in our web application. I'll add new Empty Controller called Institution to our MVC application by right clicking Controllers folder.

Normally if we want to use our InstitutionService within the InstitutionController, we need to create instance of InstitutionService inside InstitutionController. Dependency injection is basically providing the objects that an object needs (here the InstitutionService) instead of having it construct them itself. I'm going to use constructor injection in this application and let the unity to create new instance of InstitutionService for InstitutionController. There we need to add constructor to our InstitutionController with parameter as below.

using Services;
using System.Web.Mvc;

namespace UnityWebApplication.Controllers
{
    public class InstitutionController : Controller
    {
        private IInstitutionService institutionService;

        public InstitutionController(IInstitutionService institutionService)
        {
            this.institutionService = institutionService;
        }

        // GET: Institution
        public ActionResult Index()
        {
            return View(this.institutionService.GetInstitutionByID(1));
        }
    }
}

Let's add view to display our information, you can find folder called Institution inside Views folder and we will create new view called Index there.

Now modify your view code as below to display institution information.

@using Entities;
@model Institution

@{
    ViewBag.Title = "Institution";
}

<hr />

Institution ID : @(Model.InstitutionID)

<hr />

Institution Name : @(Model.Name)

<hr />

Institution Address : @(Model.Address)

<hr />

Institution Telephone : @(Model.Telephone)

It's done, you can run your application and try /Institution/Index. You should get following output.

6. Move Unity Configuration to Separate File

We can move unity configuration to separate file which enable us to do modifications without recompiling existing application. We can do this by adding config section to web.config file as below.

<configuration>
  <configSections>
    <section name="unity" type="Microsoft.Practices.Unity.Configuration.UnityConfigurationSection, Microsoft.Practices.Unity.Configuration" />
    <!-- For more information on Entity Framework configuration, visit http://go.microsoft.com/fwlink/?LinkID=237468 -->
    <section name="entityFramework" type="System.Data.Entity.Internal.ConfigFile.EntityFrameworkSection, EntityFramework, Version=6.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" requirePermission="false" />
  </configSections>
  <unity configSource="unity.config" />
  <connectionStrings>

unity.config

<?xml version="1.0" encoding="utf-8" ?>
<unity xmlns="http://schemas.microsoft.com/practices/2010/unity">
  <container>
    <register type="Services.IInstitutionService, Services" mapTo="Services.InstitutionService, Services" />
    <register type="Services.ICourseService, Services" mapTo="Services.CourseService, Services" />
  </container>
</unity>

We need to change the UnityConfig to load the configuration from given file, modified code can be found below.

using Microsoft.Practices.Unity;
using Microsoft.Practices.Unity.Configuration;
using Services;
using System.Web.Mvc;
using Unity.Mvc5;

namespace UnityWebApplication
{
    public static class UnityConfig
    {
        public static void RegisterComponents()
        {
            var container = new UnityContainer();
            
            // register all your components with the container here
            // it is NOT necessary to register your controllers
            
            // e.g. container.RegisterType<ITestService, TestService>();

            //container.RegisterType<ICourseService, CourseService>();
            //container.RegisterType<IInstitutionService, InstitutionService>();

            container.LoadConfiguration();
            
            DependencyResolver.SetResolver(new UnityDependencyResolver(container));
        }
    }
}
Download Source (Password:sara)

You can download the source code of sample application using above link. I have added some additional classes/methods to use InstitutionService inside CourseService. You can go through the code and will be able to understand easily.