Monday, November 21, 2016

Create an MVC Html Helper to play Audio

What are HTML Helpers? In ASP.Net a developer could rely on controls to help generate HTML code.  Controls are precompiled libraries that allowed a developer to reuse the code which in turn shortened development time.
HtmlHelpers take in a parameter or a group of parameters and returns a simple string.  This string is HTML formatted code that gets rendered to the browser.  It is reusable and has less overhead than a control.  We don’t need to reference any additional assemblies because we will write the code. 

For our sample project, let’s create an extension that…. plays mp3 files in HTML5 fashion.  In order to do this, we need to know the location of the file we want to play.  We can determine the file type by the extension.  So let’s get started.

It is beyond the scope of this blog to explain MVC, or C#.  It is assumed that you are proficient with both skillsets. 

Once you have your MVC templated site up, we need to add a folder.  In the solution explorer add a folder named ‘Components’ =>
image

Next we need to add a class called MvcHtmlMP3Player =>
image

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
 
namespace MediaBlog
{     public class MvcHtmlMP3Player     {     }
}

So in order for it to be an extension, we need to make the class and methods static.  This will allow us to use the extension anywhere within our application.  The purpose of creating a directory is that we can simply add an existing item when we change projects and copy in the extension.  This will save us development time when we want to reuse the code.

We also need to reference the System.Web.Mvc namespace.  So now our code should look like =>

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
 
namespace MediaBlog
{     
public static class MvcHtmlMP3Player    
{        
public static MvcHtmlString MP3Player(this HtmlHelper helper,
              string src)
        {
        }
    } }

Now we need to code up the method to return the HTML string we need.  Instead of using StringBuilder (old fashioned way) we will be using the TagBuilder.  The TagBuilder is the best practices way of Core.  So now the first thing we need to do is expand our signature of our method.  We need the source – the source of the mp3 file, we need the ability to be able to reference the tag when we render it to the browser.  We use ‘name’ and/or ‘id’ attributes in the tag to identify this particular instance.  We also need a way to display the controls that allow playback, rewind, fast-forward and stop of the media.  Finally, we need a way to allow unplanned attributes to be added.  For instance, if we use bootstrap then there are a bunch of data tags or knockout uses Observables which are attributes directly in the tag.  This will be the format of the new signature =>

public static MvcHtmlString MP3Player(this HtmlHelper helper,
             string name,
             string src,
             bool controls,
             object htmlAttributes = null)

In the above example, helper is a htmlhelper object – this allows us to extend the htmlhelper object.  Name is the name we want to give to this instance of the MP3Player, Src is the source file for the mp3.  Controls is a boolean (yes/no) that represents whether we want to display the player controls.  Finally, htmlAttributes are any and all unaccounted for attributes.  It’sa attribute bucket.  We default it to null so that there is no requirement to populate the attribute.

So the first thing we need to do inside our method is to instantiate the tagbuilder object.  We can do this like so =>

TagBuilder tb = new TagBuilder("audio");

This creates the opening tag for the HTML string that is returned.  Later, we will see how we close the tag.  Next we need to start building our tag attributes =>

if (!string.IsNullOrWhiteSpace(name))
             {
                 name = TagBuilder.CreateSanitizedId(name);
                 if (string.IsNullOrWhiteSpace(id))
                 {
                     tb.GenerateId(name);
                 } else
                 {
                     tb.MergeAttribute("id", TagBuilder.CreateSanitizedId(id));
                 }
             }
             tb.MergeAttribute("name", name);

Here we are trying to create the name/id attributes.  We look to make sure name has a value, then we sanitize it.  We sanitize the name to ensure that it is a correct value.  Now we make the same checks for id.  The MergeAttribute method is what actually adds the attribute to the TagBuilder object. 
Why not just use TagBuilder.Attributes.Add? The difference between TagBuilder.Attributes.Add and TagBuilder.MergeAttribute is that they are actually the same method with one exception.  TagBuilder.Attributes.Add is a SortedDictionary.  A SortedDictionary is based on a unique key/value system which means you can not add two tags with the same name (not sure why you would want to as this is bad HTML formatting but…)  you can add a value to replaceExisting and it will overwrite the existing key.  MergeAttribute performs the same functionality but replaceExisting is defaulted to True.

Now we need to add the attribute that actually goes out and fetches the mp3 file to play =>

tb.MergeAttribute("src", src);

We need to perform a check to make sure that the user wants the controls displayed.  We can do this by using the boolean controls parameter =>

if (controls)
             {
                 tb.Attributes.Add("Controls", "Controls");
             }

Finally, remember that we wanted to create a parameter that will allow us to add bootstrap or unaccounted for attributes =>

tb.MergeAttributes(HtmlHelper.AnonymousObjectToHtmlAttributes(htmlAttributes));

So to close the tag and return the string =>

return MvcHtmlString.Create(tb.ToString(TagRenderMode.SelfClosing));

This completes our HtmlHelper method.  We still have room to improve on this code.  For instance, let’s go ahead and break out some overloads.  Why are overloads important?  Well we can use the overloads so that we don’t have to populate all the parameters and make the helper extension a lot more flexible.  Here I can see having 3 overloads to account for the various ways we can create the tag.  We will always need a src (if we’re not loading an mp3 file, what’s the point?)  =>

public static MvcHtmlString MP3Player(this HtmlHelper helper,
     string src,
     object htmlAttributes = null) { return MP3Player(helper, string.Empty, src, false, htmlAttributes); } public static MvcHtmlString MP3Player(this HtmlHelper helper,
     string src,
     bool controls,
     object htmlAttributes = null) { return MP3Player(helper, string.Empty, src, controls, htmlAttributes); } public static MvcHtmlString MP3Player(this HtmlHelper helper,
     string name,
     string src,
     object htmlAttributes = null) { return MP3Player(helper, name, src, false, htmlAttributes); }

Finally, we know that we will need to add name/id to generally most all controls.  So we can go ahead and move this to a static method that can be used by all our html extensions =>

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
 
namespace MediaBlog
{
     public static class MvcHtmlCommon
     {
         public enum AudioMediaType
         {
             MP3,
             OGG,
             WAV
         }
         public static void AddName(TagBuilder tb, string name, string id)
         {
             if (!string.IsNullOrWhiteSpace(name))
             {
                 name = TagBuilder.CreateSanitizedId(name);
                 if (string.IsNullOrWhiteSpace(id))
                 {
                     tb.GenerateId(name);
                 } else 
                 {
                     tb.MergeAttribute("id", TagBuilder.CreateSanitizedId(id));
                 }
             }
             tb.MergeAttribute("name", name);
         }
     } }

This is our common class.  I went ahead and created an enum and a AddName method.  This will be referenced by our MP3Player methods.  Finally, we add the code to our view like so =>

@Html.MP3Player("chad", "/Media/Infectious Groves - Feed the Monkey.mp3", true)

I added this to the About view.  In total, here is all the code listings =>

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
 
namespace MediaBlog
{
     public static class MvcHtmlMP3Player
     {
         public static MvcHtmlString MP3Player(this HtmlHelper helper,
             string src,
             object htmlAttributes = null)
         { return MP3Player(helper, string.Empty, src, false, htmlAttributes); }
         public static MvcHtmlString MP3Player(this HtmlHelper helper,
             string src,
             bool controls,
             object htmlAttributes = null)
         { return MP3Player(helper, string.Empty, src, controls, htmlAttributes); }
         public static MvcHtmlString MP3Player(this HtmlHelper helper,
             string name,
             string src,
             object htmlAttributes = null)
         { return MP3Player(helper, name, src, false, htmlAttributes); }
         public static MvcHtmlString MP3Player(this HtmlHelper helper,
             string name,
             string src,
             bool controls,
             object htmlAttributes = null)
         {
             TagBuilder tb = new TagBuilder("audio");
             MvcHtmlCommon.AddName(tb, name, "");
             tb.MergeAttribute("src", src);
             if (controls)
             {
                 tb.Attributes.Add("Controls", "Controls");
             }
             tb.MergeAttributes(HtmlHelper.AnonymousObjectToHtmlAttributes(htmlAttributes));
             return MvcHtmlString.Create(tb.ToString(TagRenderMode.SelfClosing));
         }
     } }
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
 
namespace MediaBlog
{
     public static class MvcHtmlCommon
     {
         public enum AudioMediaType
         {
             MP3,
             OGG,
             WAV
         }
         public static void AddName(TagBuilder tb, string name, string id)
         {
             if (!string.IsNullOrWhiteSpace(name))
             {
                 name = TagBuilder.CreateSanitizedId(name);
                 if (string.IsNullOrWhiteSpace(id))
                 {
                     tb.GenerateId(name);
                 } else
                 {
                     tb.MergeAttribute("id", TagBuilder.CreateSanitizedId(id));
                 }
             }
             tb.MergeAttribute("name", name);
         }
     } }
@{     ViewBag.Title = "About";
}
<h2>@ViewBag.Title.</h2>
<h3>@ViewBag.Message</h3>
 
<p>Use this area to provide additional information.</p>
@Html.MP3Player("chad", "/Media/Infectious Groves - Feed the Monkey.mp3", true)

If you debug the code, you get the about page and an MP3 player.  I clicked play and it renders as =>
image

Håþþ¥ .ñꆆïñg…

Friday, April 29, 2016

Use SpreadsheetGear with PowerShell to Export an Excel File.

I’ve been starting a new job and haven’t really had time to keep this blog updated.  Recently, I figured out how to export an Excel file using PowerShell and Spreadsheet Gear.  I worked at a shop that utilized the Spreadsheet Gear assembly pretty extensively.  So naturally, when we’re out Excel files we wanted a way to export from PowerShell and rather than learn a whole new framework we wanted to use Spreadsheet Gear.  I worked on it for a week or so and couldn’t get it.  There are barely any examples of Spreadsheet Gear and PowerShell that are searchable.  All that has changed.

Why not just use InterOp?

First problem with that, is well… I don’t want to.  I’m used to using Spreadsheet Gear.  Secondly, it creates the object as a ComObject.  The .net framework has a terrible time keeping track of ComObjects.  This is why they have lost favor (a long time ago)…

Let’s Go -

Ok.  So now we know what we are doing, how do we do it?  First, you need the assembly.  You can download a 30 day evaluation from Spreadsheet Gear.  The thing you will notice is that it is Spreadsheet Gear 2012.  Yup, they have not updated it since 2012.  Quite frankly, what it does is pretty simplistic.  Meaning, there isn’t much more they can do with it other than to make sure it is compatible with new versions of Excel.

First things first.  We need to make sure we can develop in PowerShell in VS20xx.  You do this by going into Tools | Extensions and Updates… In the modal window that opens up, make sureto highlight Online in the left pane and type powershell in the search.  You will be given a list of powershell extensions you can install.  We want PowerShell Tools for Visual Studio (insert version here) as in this example -

image

Once that is installed and ready to go we can create a PowerShell project and it will automagically create us a script -

image

Now that we have the project and script created we can start with the real work.

First thing we need to do is create the table of data that we will cycle through and populate the Excel spreadsheet with.  You can see that done in the following example (creating a datatable in PowerShell is beyond the scope of this article) -

$table = New-Object System.Data.DataTable
$col1 = New-Object system.Data.DataColumn Year,([int])
$col2 = New-Object system.Data.DataColumn Album_Nm,([string])
$col3 = New-Object system.Data.DataColumn US_Chart,([int])
 
$table.columns.add($col1)
$table.columns.add($col2)
$table.columns.add($col3)
 
$row = $table.NewRow()
$row.Year = 1978
$row.Album_Nm = "Van Halen"
$row.US_Chart = 19
$table.Rows.Add($row)
$row = $table.NewRow()
$row.Year = 1979
$row.Album_Nm = "Van Halen II"
$row.US_Chart = 6
$table.Rows.Add($row)

For brevity I only added the first two albums, but you get the idea.  So here we are with our data so now we need to load it into Spreadsheet Gear.  First thing we need to do is load the assembly into memory.  We do that using Reflection.

What is Reflection

There are four principles of OOP (Object-Orientated Programming).  They are Reflection, Encapsulation, Abstraction and Polymorphism.  We’ll discard Abstraction and Polymorphism as they are beyond the scope of this article.

Encapsulation

Encapsulation is the wizard behind the curtain.  I am here because I know what you want and I know what I want but most importantly, I don’t care what you have to do to accomplish the goal.  I don’t care what gears need to turn or buttons need to be pushed. 

Reflection

It is the exact opposite of Encapsulation.  I need to know everything about you so I can use you to give me what I want.  I have to know how your methods work to accomplish the task.

In our instance, we are using reflection.  This isn’t necessarily true but think of it this way-
When you import (VB) or using (C#) you are using encapsulation.  You don’t care about anything but the signature of the object (what it requires and what you are getting back) but when inherit an object, you need to know everything about it (like in reflection).

So the first thing to accomplish is to get the assembly loaded.  We’ll use the Reflection.Assembly namespace to accomplish this task-

[Reflection.Assembly]::LoadFile("C:\Personal Projects\Project Reporter\Powershell 
Project Reporter\SpreadsheetGear.dll")

Obviously you will need to store your assembly where the LoadFile path points to.  This loads the assembly into memory and prepares it for us to use.  Next we need to create a workbook.  We do this by using the shared (VB) or static (C#) method GetWorkbook() in the assembly-

$wb = [SpreadsheetGear.Factory]::GetWorkbook()

If you wanted to load a workbook that had been previously created then you would use GetWorkbook(“C:\Path\To\File.xlsx”)

Now we need to create a worksheet in our new Workbook.  We do this using the Add() method for the workbook-

[SpreadsheetGear.IWorksheet]$ws = $wb.Worksheets.Add()

Here you can see we are creating the $ws variable and type-casting it to an IWorksheet.  Now we need a way to keep track of what row we are on.  We want to be able to dynamically move between rows.  So we create a “pointer” which really isn’t a pointer but simply an integer indicating the number row we are on -

[int]$pointer = 1

Now that we have our pointer, let’s go ahead and add a header to the report.  We’ll name this report Van Halen Albums -

$ws.Cells["B1:E1"].Merge()
$ws.Cells["B1"].Value = "Van Halen Albums"
$ws.Cells["B1"].Font.Size = 20
$ws.Cells["B1"].Font.Bold = $true

So here we Merged cells B1 – E1 and then wrote “Van Halen Albums” in the cell.  We changed the font size and then made it bold.  We also could have changed the font color by using .Font.Color or changed the background color of the cell by using .Interior.Color…

Now it is time to put our Row headers in.  So we can identify what data is what -

$pointer = 3
$ws.Cells["A$pointer"].Value = "Year"
$ws.Cells["B$pointer"].Value = "Album Name"
$ws.Cells["C$pointer"].Value = "Chart Placement"

So here you can see we incremented the pointer to row #3.  We then put our text into the appropriate cell.  From here, we are going to go through a loop so that we can style the header correctly.  We need to know what cells to apply this to so we have to define that and then go through the loop, the rest is pretty much self-explanatory -

$cells = ("A", "B", "C")
foreach ($cell in $cells) {
 $c = $cell + $pointer.ToString()
 #$ws.Cells[$c].HorizontalAlignment = [SpreadsheetGear.HAlign]::Center
 $ws.Cells[$c].Font.Size = 11
 $ws.Cells[$c].Font.Bold = $true
 $ws.Cells[$c].Borders[[SpreadsheetGear.BordersIndex]::EdgeTop].Weight = 
[SpreadsheetGear.BorderWeight]::Thin $ws.Cells[$c].Borders[[SpreadsheetGear.BordersIndex]::EdgeRight].Weight = 
[SpreadsheetGear.BorderWeight]::Thin $ws.Cells[$c].ColumnWidth = 15      }

Now you see how the pointer is an added value.  This only gives us part of the example, because next we will want to add the data (the actual album stuff from the table we created…) -

foreach ($row in $table.Rows) {
 $pointer +=1
 $ws.Cells["A$pointer"].Value = $row.Item("Year")
 $ws.Cells["B$pointer"].Value = $row.Item("Album_Nm")
 $ws.Cells["C$pointer"].Value = $row.Item("US_Chart")
}

Last thing we need to do is name the file and save it.  There are specific file types for SpreadsheetGear and you will need to google to find out which one is the one that best suits your needs but the OpenXMLWorkbook is backward compatible to Excel 2007 -

$wb.SaveAs("C:\temp\VanHalenAlbums.xlsx", [SpreadsheetGear.FileFormat]::OpenXMLWorkbook)

 

So here we created an Excel file in PowerShell without using ComObjects and then populated it with data.  Finally we saved the file for archiving.  Now we can automate creating Excel in PowerShell -

image

Unformatted completed example code follows -

$table = New-Object System.Data.DataTable
$col1 = New-Object system.Data.DataColumn Year,([int])
$col2 = New-Object system.Data.DataColumn Album_Nm,([string])
$col3 = New-Object system.Data.DataColumn US_Chart,([int])
 
$table.columns.add($col1)
$table.columns.add($col2)
$table.columns.add($col3)
 
$row = $table.NewRow()
$row.Year = 1978
$row.Album_Nm = "Van Halen"
$row.US_Chart = 19
$table.Rows.Add($row)
$row = $table.NewRow()
$row.Year = 1979
$row.Album_Nm = "Van Halen II"
$row.US_Chart = 6
$table.Rows.Add($row)
 
 
## SPREADSHEETGEAR Example starts here
[Reflection.Assembly]::LoadFile("C:\Personal Projects\Project Reporter\Powershell Project Reporter\SpreadsheetGear.dll")
 
[int]$pointer = 1
$wb = [SpreadsheetGear.Factory]::GetWorkbook()
[SpreadsheetGear.IWorksheet]$ws = $wb.Worksheets.Add()
$ws.Cells["B1:E1"].Merge()
$ws.Cells["B1"].Value = "Van Halen Albums"
$ws.Cells["B1"].Font.Size = 20
$ws.Cells["B1"].Font.Bold = $true
$pointer = 3
$ws.Cells["A$pointer"].Value = "Year"
$ws.Cells["B$pointer"].Value = "Album Name"
$ws.Cells["C$pointer"].Value = "Chart Placement"
 
$cells = ("A", "B", "C")
foreach ($cell in $cells) {
 $c = $cell + $pointer.ToString()
 #$ws.Cells[$c].HorizontalAlignment = [SpreadsheetGear.HAlign]::Center
 $ws.Cells[$c].Font.Size = 11
 $ws.Cells[$c].Font.Bold = $true
 $ws.Cells[$c].Borders[[SpreadsheetGear.BordersIndex]::EdgeTop].Weight = [SpreadsheetGear.BorderWeight]::Thin
 $ws.Cells[$c].Borders[[SpreadsheetGear.BordersIndex]::EdgeRight].Weight = [SpreadsheetGear.BorderWeight]::Thin
 $ws.Cells[$c].ColumnWidth = 15
    
}
#start data cells
foreach ($row in $table.Rows) {
 $pointer +=1
 $ws.Cells["A$pointer"].Value = $row.Item("Year")
 $ws.Cells["B$pointer"].Value = $row.Item("Album_Nm")
 $ws.Cells["C$pointer"].Value = $row.Item("US_Chart")
}
$wb.SaveAs("C:\temp\chadisawesome.xlsx", [SpreadsheetGear.FileFormat]::OpenXMLWorkbook)
#You get the point....

 

Håþþ¥ .ñꆆïñg…

Monday, November 23, 2015

SQL Injection in your ASP.NET web applications–Inline Dynamic Sql Query

In order to teach how to prevent a SQL Injection attack, it is necessary to teach how a SQL Injection attack works.  This means you will learn how to initiate an attack.  If you have followed my blog, you know that I am a once black hat.  Fortunately for me, I like money more than I like freeing data or digital knowledge. Thus I have grown up and have professional employment, besides the fact that prison at my age is extremely unappealing. 

DISCLAIMER: I am not responsible for anything you do with the knowledge contained in this post! You assume all responsibility for your actions. Be an adult.

The idea behind a SQL Injection is to change the way a query was intended to work.  Let’s see how we can make this happen.

Inline SQL Query

In order to get a query we need something to query.  So let’s fire up the old  trusty dusty Northwind database.  I have setup a website that utilizes an inline dynamic query:

DAL -
image
BLL -
image
Code-Behind -
image
HTML -
image

This is the complete code for the site.  So now a would-be hacker is asking himself if this site is susceptible to a SQL Injection attack.  First, we need to know the dynamics of the query (what a resultset looks like).  This will play a major factor later on in the process.  So we need to have a ‘true’ query -
This is what our website looks like when it is running -
image
Now we need to get a resultset from it to see what the query returns -
image 
AHA! As a hacker, I am looking at the number of columns and likely data types.  Hmm, EmployeeID looks like it can either be a varchar or an int.  LastName, FirstName, Title and Address are most definitely varchar.  I take notes on this and now, I need to see if the site is vulnerable.  I can do this with a little bit of innocent code that if it throws an error that gets logged, will not directly point to an intrusion attack.  How do we do this?  We insert into the field a command that we can visually see if it processes the command.  I have chosen the DELAY command.  Here I will enter ‘; WAITFOR DELAY ‘0:0:30’ --
What does this do?  Well in bad search coding, the SQL command will most likely be performing a LIKE command in the WHERE clause.  So the single quote closes that open single quote in the query.  Then I issue the WAITFOR command to make the server wait 30 seconds and finally the - - (double hyphen) closes out the rest of the query.  So essentially, I am searching for nothing which will return nothing but I am issuing a follow-up command to tell the server to wait for a delay.  If this works, I will notice that the return takes an extraordinary amount of time in addition to not receiving an error -
image
MAN!! That search took forever!  Here is the result -
image
YES!!! WHOOP WHOOP!!!! This site is vulnerable! I did not receive an error and the query took forever for a return because of the WAITFOR DELAY command.  The server is now mine! But wait, how do I get access to the data?  Well now that we know
A) That it is vulnerable
B) Likely data types
C) The resultset
I will inject my own data into the query and make sure I have the correct data types.  I do this by knowing that strings (when they can) are converted into the correct data types on the SQL server.  Thus, if I assume EmployeeID is a varchar and it is actually an int, SQL will convert it for me.  So what value can either be a varchar or int?  How about a numeric value that is in (single) quotes making it a varchar.  Like ‘1’ or ‘2’ or ‘1000’.  So if I inject five ‘1’s (one for each column) whether it is an int or varchar, SQL will handle it.  For instance, Select ‘1’,’1’,’1’,’1’,’1’.  This command will not actually work because of the inline command.  I don’t know the entire nature of the inline command so how do I either nullify or add to the command?  How about we use the UNION command?  If we perform a UNION command, no matter what the server returns, our values will be added to it and we should see a resultset -
image
So let’s see what SQL returns to us -
image
No WAY!!! I controlled the resultset.  This might appear to do nothing for us as hackers, but what if we tried to get other information instead of constant values.  Let’s try to get the table names from the database.  That will certainly help us in our quest to understand their data model.  Let’s see if we can do that by querying sysobjects and what we come up with -
image
What does it return?  All the tables from the Northwind Database -
image
Rut row.  This isn’t good.  I now have my first piece of the puzzle in getting the information that I need.  So now that we know the table names, what we need next is to know what columns are in a table.  As a hacker, the Customers table looks really interesting to me.  I want to know everything there is to know about that table.  So now I need to query that table to get the columns -
image
Now what is our resultset -
image

Double rut row.  I now have access to the entire customer information contained in the database.  Remember the Ashley Madison customer hack and how damaging it was for some people to show up in their list?  Well now imagine if this was your run of the mill site and this table contained bank or credit card information?  We aren’t actually there yet. So let’s complete the hack and see what we get -
image
From this query, we should be able to obtain all the customers in the database, where they work, phone number and address.  Let’s see if this works -
image
Well it is a good thing that Northwind does not store banking or credit card information.  I’m not sure how thrilled Art Braunschweiger would be to receive a call from a hacker that knows where he works and his address… and as in the case with Ashley Madison, just having proof of their name and personal information associated might be damaging enough.

Here we have seen that it can be extremely damaging if you are not filtering your queries.  This is actually a pretty common method of Sql Injection and developers need to understand how to protect their data against this type of attack.  The obvious answer is to filter the input search field using REGEX or simply do not use inline dynamic Sql.  Next we will look at the vulnerabilities of other SQL query methods.

 

Håþþ¥ .ñꆆïñg…

Tuesday, November 3, 2015

Create a PDF file from a DataSet/DataTable and present that to the browser for preview with iTextSharp

I recently had a friend ask how he could create a PDF report and present that PDF to the user for preview prior to saving from a Windows Application.  It got a little tricky as you have to ensure that there is an application on the client machine that can read the PDF file.

Here we will create a PDF from an ASP.Net application and using the browser, present that PDF to the user for preview prior to saving using a memoryStream. 

In order to start our project, we need to define some prerequisites.  Of course we need to have some data to populate a table or grid on the PDF.  In this case, we will use the three-tier method (DAL, BLL, Presentation).  The code to generate the report will reside in the BLL above the presentation layer.  Finally, we need a trigger that will “activate” our code so that we know to begin creating the report.

As with all projects that include any kind of data, you start at the data.  How can you design an application for data if you don’t know what data to expect or what it looks like (types… etc). 

So let’s get started…

I am using VS2013 Professional, however this project is Framework dependent and should not be affected by your VS version.  For this project, you need to at least be at .NET 3.5 Framework.

Fire up VS and click File | New Web Site.  In the modal window that pops up, click ASP.NET Web Forms Site.  I save mine to the file system and the location is irrelevant in the context of this project.

image

Once you click ok, you should be taken to the website in the editor.  The first thing we need to do is create our three tiers to make our site readable and modular. 

Right-Click on the name of your solution | Highlight Add | Add ASP.NET Folder | Left-Click App_Code.  This will put the ASP.NET reserved folder App_Code in your solution. 

image

Right-Click the App_Code folder | Add | New Folder.  Name this folder BLL.  Repeat this step and create a folder named DAL.  Your solution Explorer should look like -

image

Now we need to create our classes for the PDF report.  Right-Click the DAL folder | Add | Class.  Name this class PDFExport.  Repeat this step under the BLL and name the class PDFExport.  So you should have a class named PDFExport under both the BLL and DAL folders.  You should be seeing an error (little red wavy line under the DAL folder and our new class – This is to be expected).

image

Why do we get the error?  This is because of namespacing.  There are two objects with the exact same name that exists in the same namespace.  So we need to separate these two objects in the hierarchy.  Double Left click the DAL/PDFExport.vb file (or the PDFExport.vb file located in the DAL folder).  You should see the following code:

Imports Microsoft.VisualBasic

Public Class PDFExport

End Class 

In the concept of namespacing, we can name the namespace anything we want as long as we know how to reference the namespace or class, otherwise it is completely useless to us.  When creating a website (or application) it is important to start from the data and move down (or backwards).  When namespacing it is important to build the namespace from the ground up.  This is where VB and C# differ.  VB.Net assumes the name of the project, C# does not.  Due to the fact that we are referencing the class from inside the project this is neither here nor there.  However, if you were to reference this namespace from outside the project, you would need to start from the lowest level (the project) and move up.
That being said, I own a development company named Recon Mobile Systems.  So this is the logical place to start.  My namespace will be ReconMobileSystem.PDF.DAL.  This is so that I know the code that is in this particular node of the namespace deals with PDFs and it is the Data Access Layer.  Your namespace should encompass the entire class like so:

Imports Microsoft.VisualBasic

Namespace ReconMobileSystems.PDF.DAL
Public Class PDFExport

End Class
End Namespace

Anyone who has read my blog before knows I am a stickler for Regions.  If you are a developer and are not using Regions, there are people looking at your code like a second class citizen.  It is the single easiest method to add a level of professionalism to your code… Think of it this way, if you do not respect your own code enough to spend the time to create Regions, why should I (as your team leader/Director/CTO… boss) take your code seriously?
At any rate, after we add the default Regions for the DAL:

Imports Microsoft.VisualBasic

Namespace ReconMobileSystems.PDF.DAL
Public Class PDFExport
#Region "Globals"

#End Region
#Region
"Properties"

#End Region
#Region
"Methods"

#End Region
#Region
"Helpers"

#End Region
End Class
End Namespace
Now we can go ahead and create the method that returns the data.  For simplicity I am simply going to create a DataTable which is a single table from a DataSet (Thus, DataSet.Tables( 0) is the same as DataTable).  Since we are going to have to return an object, I will create a function.  However, we need to import the correct namespaces (assemblies) so that the Framework knows how to handle these objects.  We also could alternatively use strongly typed objects but here I will simply import:
Imports Microsoft.VisualBasic
Imports System.Data
Since we are only creating a DataTable, we only need to import a single assembly.  Now that we have the reference, we can go ahead and continue with creating our function:
 Public Function getDataTable() As DataTable
getDataTable = New DataTable
getDataTable.Columns.Add("Record Number", GetType(Integer))
getDataTable.Columns.Add(
"Album Name", GetType(String))
getDataTable.Columns.Add(
"Year Produced", GetType(Integer))
getDataTable.Columns.Add(
"Label", GetType(String))
getDataTable.Columns.Add(
"Date Added", GetType(DateTime))

getDataTable.Rows.Add(
1, "Van Halen", 1978, "Warner Bros.", _
DateTime.Now)
getDataTable.Rows.Add(
2, "Van Halen II", 1979, _
"Warner Bros.", DateTime.Now)
getDataTable.Rows.Add(
3, "Women and Children First", 1980, _
"Warner Bros.", DateTime.Now)
getDataTable.Rows.Add(
4, "Fair Warning", 1981, _
"Warner Bros.", DateTime.Now)
getDataTable.Rows.Add(
5, "Diver Down", 1982, _
"Warner Bros.", DateTime.Now)
getDataTable.Rows.Add(
6, "1984", 1984, _
"Warner Bros.", DateTime.Now)
getDataTable.Rows.Add(
7, "5150", 1986, _
"Warner Bros.", DateTime.Now)
getDataTable.Rows.Add(
8, "OU812", 1988, _
"Warner Bros.", DateTime.Now)
getDataTable.Rows.Add(
9, "For Unlawful Carnal Knowledge", 1991, _
"Warner Bros.", DateTime.Now)
getDataTable.Rows.Add(
10, "Balance", 1995, _
"Warner Bros.", DateTime.Now)
getDataTable.Rows.Add(
11, "Van Halen III", 1998, _
"Warner Bros.", DateTime.Now)
getDataTable.Rows.Add(
12, "A Different Kind of Truth", 2012, _
"Warner Bros.", DateTime.Now)
End Function
Obviously this should go in the methods Region and completes the DAL:
Imports Microsoft.VisualBasic
Imports System.Data


Namespace ReconMobileSystems.PDF.DAL
Public Class PDFExport
#Region "Globals"

#End Region
#Region
"Properties"

#End Region
#Region
"Methods"
Public Function getDataTable() As DataTable
getDataTable = New DataTable
getDataTable.Columns.Add("Record Number", GetType(Integer))
getDataTable.Columns.Add(
"Album Name", GetType(String))
getDataTable.Columns.Add(
"Year Produced", GetType(Integer))
getDataTable.Columns.Add(
"Label", GetType(String))
getDataTable.Columns.Add(
"Date Added", GetType(DateTime))

getDataTable.Rows.Add(
1, "Van Halen", 1978, "Warner Bros.", _
DateTime.Now)
getDataTable.Rows.Add(
2, "Van Halen II", 1979, _
"Warner Bros.", DateTime.Now)
getDataTable.Rows.Add(
3, "Women and Children First", 1980, _
"Warner Bros.", DateTime.Now)
getDataTable.Rows.Add(
4, "Fair Warning", 1981, _
"Warner Bros.", DateTime.Now)
getDataTable.Rows.Add(
5, "Diver Down", 1982, _
"Warner Bros.", DateTime.Now)
getDataTable.Rows.Add(
6, "1984", 1984, _
"Warner Bros.", DateTime.Now)
getDataTable.Rows.Add(
7, "5150", 1986, _
"Warner Bros.", DateTime.Now)
getDataTable.Rows.Add(
8, "OU812", 1988, _
"Warner Bros.", DateTime.Now)
getDataTable.Rows.Add(
9, "For Unlawful Carnal Knowledge", 1991, _
"Warner Bros.", DateTime.Now)
getDataTable.Rows.Add(
10, "Balance", 1995, _
"Warner Bros.", DateTime.Now)
getDataTable.Rows.Add(
11, "Van Halen III", 1998, _
"Warner Bros.", DateTime.Now)
getDataTable.Rows.Add(
12, "A Different Kind of Truth", 2012, _
"Warner Bros.", DateTime.Now)
End Function
#End Region
#Region
"Helpers"

#End Region
End Class
End Namespace

Now that we are done with the DAL, we need to move to the BLL. Double
Left-Click the class (PDFExport) in the BLL. We also need to make a reference to the iTextSharp assembly. You may need to download this from their website which can be found here and is completely free (GNU License). Now that we have the assembly downloaded and extracted, we need to reference the assembly in our project. To do this, you need to Right-Click your project name in the solution explorer | Add | Reference.
image


To the left of the “OK” button there is a Browse… button. Left-Click and navigate to where you saved the iTextSharp.dll file. This is the only assembly we need to reference. Left-Click OK.  Now back to the PDFExport class that is in the BLL. We need to import
The iTextSharp classes that we will use to build our PDF:

Imports Microsoft.VisualBasic
Imports iTextSharp.text
Imports iTextSharp.text.pdf
Imports iTextSharp.text.html.simpleparser

Next we need to add our namespace to the class:

Namespace ReconMobileSystems.PDF.BLL

So in total our class should look like:

Imports Microsoft.VisualBasic
Imports iTextSharp.text
Imports iTextSharp.text.pdf
Imports iTextSharp.text.html.simpleparser
Namespace ReconMobileSystems.PDF.BLL
Public Class PDFExport
End Class
End Namespace

Again, let’s add some professionalism and add our Regions. In the case of the BLL, I (typically) add Globals | Methods | Helpers |
Exports. Then we can add our global variable to the DAL class that we need in order to get the Van Halen (YA!) data:

Imports Microsoft.VisualBasic
Imports iTextSharp.text
Imports iTextSharp.text.pdf
Imports iTextSharp.text.html.simpleparser

Namespace ReconMobileSystems.PDF.BLL
Public Class PDFExport
#Region "Globals"
Dim rmc_DAL As ReconMobileSystems.PDF.DAL.PDFExport = New DAL.PDFExport
#End Region
#Region
"Methods"

#End Region
#Region
"Helpers"

#End Region
#Region
"Exports"

#End Region
End Class
End Namespace

Now that we have our global, in the Methods Region we can create a Function that will call the DAL, run the getDataTable function and then return that object to the original caller. This is accomplished with a function declaration and a single line of code:

Public Function getDataTable() As System.Data.DataTable
getDataTable = rmc_DAL.getDataTable()
End Function

You will notice here that I used a strongly typed function type. This is because I do not expect to re-use the type and therefore do not need to make a complete reference. Here we are setting the function variable to the value returned by the referenced object rmc_DAL which is our PDFExport class in the DAL and finally we are calling the getDataTable method (function) in the DAL. We do not need to create any helpers, so we can move to the presentation layer to create our trigger. Double Left-Click the Default.aspx file located in the project folder. Here we will add a button that will serve as our trigger:

<asp:Button ID="btnTrigger" runat="server" Text="Create PDF" />
So for our Default.aspx file it should look like:
<html xmlns="http://www.w3.org/1999/xhtml">
<
head runat="server">
<
title></title>
</
head>
<
body>
<
form id="form1" runat="server">
<
div>
<
asp:Button ID="btnTrigger" runat="server" Text="Create PDF" />
</
div>
</
form>
</
body>
</
html>

Now that we have created the trigger, we need to code the trigger.
Double Left-Click the Default.aspx.vb file by clicking the triangular
tick in front of the Default.aspx. This will open the back-end code
for our page. From here we need to select our button object from the
dropdown selection for the page:

image

Then select the Click event in the drop down to the right and it will place the event
in your page:
image


Again, we need to practice some professionalism and add some Regions.  On pages I typically use Globals | Page Methods | Page Helpers then we need to move our trigger inside the Page Methods Region. Additionally we can create our global variable that will reference our class in the BLL to get the DataTable and in total our .vb code for our page should look like:

Partial Class _Default
Inherits System.Web.UI.Page
#Region "Globals"
Dim rmc_BLL As ReconMobileSystems.PDF.BLL.PDFExport = New _
ReconMobileSystems.PDF.BLL.PDFExport
#End Region
#Region
"Page Methods"
Protected Sub btnTrigger_Click(sender As Object, e As EventArgs) _
Handles btnTrigger.Click

End Sub
#End Region
#Region
"Page Helpers"

#End Region
End Class
Now comes the meat of the PDF creation phase.  Prior to this was all setup for the one method that will deliver the goods.  We need to move back to the BLL class PDFExport.  Once there we need to create some more imports.  We need to bring in the System.IO, System.Text and since we are here we can pull in the System.Data assemblies.  Now we should look like:
Imports Microsoft.VisualBasic
Imports iTextSharp.text
Imports iTextSharp.text.pdf
Imports iTextSharp.text.html.simpleparser
Imports System.IO
Imports System.Text
Imports System.Data
Generally while creating exports I create some globals that can accessed across the export.  These are objects that I may use several times throughout the export.  In our case, we only need a single method to create and export the PDF.  This being the case, I am going to continue my tradition of creating globals if nothing else than for my conformity.  Obviously, we have moved to the Exports Region and there I will create three objects or global variables.  One to hold the datatable that is created by the DAL, passed to the BLL who then passes it to the presentation layer where it then passes it to our export (whew!).  Secondly, I like to timestamp my exports and usually have the timestamp in the filename so I know when the export was produced.  Here I will create a string variable to hold that value and finally, I will create a string variable to hold the filename of the pdf that we create:
#Region "Exports"
Dim _data As DataTable = New DataTable
Dim runtime As String = String.Empty
Dim filename As String = String.Empty
Now we need to go ahead and create our method which will be a sub or subroutine:
Public Sub PDFExport(ByVal Data As DataTable, _
ByVal RunTime As DateTime, _
ByVal context As HttpContext)
When we use the keyword “Me” in VB or “this” in C# it is basically a reference in memory to the currently active object (or class in this case).  So, if we say Me in a method of our BLL.PDFExport class, we are referring to the PDFExport class.  This being said, we are requiring that in order to use our PDFExport subroutine that they must provide three parameters Data | RunTime | and context.  The first thing we need to do is setup our globals with these values so that they are reusable:
Me._data = Data
Me.runtime = RunTime.ToShortDateString
Now this is my own preference and has nothing to do with exporting the pdf but like previously said, I like to have the date stamp in the file name.  So in this case what I do is convert a DateTime object.  I break down the year | month | day so that I can pad a 0 (zero) in front of the value.  So if you convert a date like January 9, 2015 it will basically convert it to 1/9/2015 as opposed to the much more prettier 01/09/2015, this code will do that:
Dim convertDate As DateTime = RunTime
Dim year As String = convertDate.Year.ToString()
Dim month As String = String.Empty
If convertDate.Month < 10 Then
month = "0" & convertDate.Month.ToString()
Else
month = convertDate.Month.ToString()
End If
Dim
day As String = String.Empty
If convertDate.Day < 10 Then
day = "0" & convertDate.Day.ToString()
Else
day = convertDate.Day.ToString()
End If
Now that we have the correct formatting for our values, we need to go ahead and set our filename:
Me.filename = "c:\temp\PDFTest_" & day & "-" & month & _
"-" & year & ".pdf"
Now.  When any pdf is created, it is created on a BLANK page.  There is no formatting or any setup to the page.  So basically, the next thing we need to do is setup a header.  We do that using what is called a paragraph (if you can guess what that is):
Dim header As Paragraph = New Paragraph("Van Halen Albums", _
New Font(Font.FontFamily.HELVETICA, 22))
header.Alignment =
Element.ALIGN_CENTER
header.SpacingAfter =
30
So basically, we are creating a paragraph with an horizontal alignment of center and will use the Helvetica font with a size of 22 and display the text Van Halen Albums.  Next we need to create a table (aptly name PdfTable) to hold the data from our DataTable:
Dim pdfTable As PdfPTable = New PdfPTable(_data.Columns.Count)
pdfTable.DefaultCell.Padding =
3
pdfTable.WidthPercentage = 95
pdfTable.HorizontalAlignment = Element.ALIGN_CENTER
pdfTable.DefaultCell.BorderWidth =
1
So we are creating the PdfPTable object and then you have to tell it how many columns to create.  You can’t make a breakfast table without knowing the size/dimensions you need.  It works the same way in the virtual world.  Due to the fact that we created the DataTable in the DAL we actually knew how many columns we needed and could have put an integer there, but just in case in the future you find yourself in a dynamic situation, this is the correct way to do it.  The rest of the code is simply parameters that define the table.  The major one to pay attention to is the WidthPercentage.  This controls how much space the table takes up on the page and in the case 95% (width) of the page is populated with the table.  Next we need to take the table and populate the “header” cells of the table with the column names of our datatable.  There are actually no header or footer on the PdfPTable.  We create that using smoke and mirrors with colors and etc..
Dim cell As PdfPCell
For Each column As DataColumn In _data.Columns
cell =
New PdfPCell(New Phrase(column.ColumnName))
cell.BackgroundColor =
New iTextSharp.text.BaseColor(240, 240, 240)
pdfTable.AddCell(cell)
Next
Now we need to go ahead and create and populate the rows of our table from our DataTable:
Dim cell As PdfPCell
For Each column As DataColumn In _data.Columns
cell =
New PdfPCell(New Phrase(column.ColumnName))
cell.BackgroundColor =
New iTextSharp.text.BaseColor(240, 240, 240)
pdfTable.AddCell(cell)
Next
So we are now ready to create the actual PDF Document that will house our header and our table:
Dim pdfDoc As New Document(PageSize.A4, 10.0F, 10.0F, 10.0F, 0.0F)
Since we now have a PdfDoc, we need some place to store it (as opposed to creating a physical file on the system).  We do this with a memoryStream:
Dim memoryStream As MemoryStream = New MemoryStream
You write the PdfDoc to the memoryStream using a pdfWriter:
Dim writer As PdfWriter = PdfWriter.GetInstance(pdfDoc, memoryStream)
So here we are saying to write pdfDoc to the memoryStream as represented in the GetInstance method parameters.  Except we haven’t actually written anything yet.  So now we need to add our header and table objects:
pdfDoc.Open()
pdfDoc.Add(header)
pdfDoc.Add(pdfTable)
pdfDoc.Close()
So now our Pdf Document is created and residing in memory on the server.  How do we get it to the client or user browser?  We use the HttpContext parameter that we required in the PdfExport method:
context.Response.ClearContent()
context.Response.ClearHeaders()
context.Response.ContentType =
"application/pdf"
context.Response.OutputStream.Write(memoryStream.ToArray(), _
0, memoryStream.ToArray().Length)
context.Response.Flush()
context.Response.Close()
The OutputStream.Write method takes three parameters.  The object to be written (our memoryStream that contains the PDF Document), the starting point (why you would start somewhere other than 0, I’m not sure of and have never seen.  0 represents the beginning of the memoryStream), and finally the end point which is the memoryStream to length (or memoryStream.ToArray().Length)


Finally, we need to close the memoryStream so that we do not create any memory leaks:

memoryStream.Close()
Of course, in today’s babysitting world Microsoft has some cleanup routines that run throughout the stack that will prevent us from creating memory leaks but you should not rely on them.  Also it is best practices.  So that is our entire Subroutine to export the file.  In total our BLL.PDFExport class should look like:
Imports Microsoft.VisualBasic
Imports iTextSharp.text
Imports iTextSharp.text.pdf
Imports iTextSharp.text.html.simpleparser
Imports System.IO
Imports System.Text
Imports System.Data

Namespace ReconMobileSystems.PDF.BLL
Public Class PDFExport
#Region "Globals"
Dim rmc_DAL As ReconMobileSystems.PDF.DAL.PDFExport = New _
DAL.PDFExport
#End Region
#Region
"Methods"
Public Function getDataTable() As DataTable
getDataTable = rmc_DAL.getDataTable()
End Function
#End Region
#Region
"Helpers"

#End Region
#Region
"Exports"
Dim _data As DataTable = New DataTable
Dim runtime As String = String.Empty
Dim filename As String = String.Empty

Public Sub PDFExport(ByVal Data As DataTable, _
ByVal RunTime As DateTime, _
ByVal context As HttpContext)
Me._data = Data
Me.runtime = RunTime.ToShortDateString

Dim convertDate As DateTime = RunTime
Dim year As String = convertDate.Year.ToString()
Dim month As String = String.Empty
If convertDate.Month < 10 Then
month = "0" & convertDate.Month.ToString()
Else
month = convertDate.Month.ToString()
End If
Dim
day As String = String.Empty
If convertDate.Day < 10 Then
day = "0" & convertDate.Day.ToString()
Else
day = convertDate.Day.ToString()
End If

Me
.filename = "c:\temp\PDFTest_" & day & "-" & _
month &
"-" & year & ".pdf"

Dim header As Paragraph = New _
Paragraph("Van Halen Albums", New Font(Font.FontFamily.HELVETICA, 22))
header.Alignment =
Element.ALIGN_CENTER
header.SpacingAfter =
30

Dim pdfTable As PdfPTable = New _
PdfPTable(_data.Columns.Count)
pdfTable.DefaultCell.Padding =
3
pdfTable.WidthPercentage = 95
pdfTable.HorizontalAlignment = Element.ALIGN_CENTER
pdfTable.DefaultCell.BorderWidth =
1

Dim cell As PdfPCell
For Each column As DataColumn In _data.Columns
cell =
New PdfPCell(New Phrase(column.ColumnName))
cell.BackgroundColor =
New _
iTextSharp.text.BaseColor(240, 240, 240)
pdfTable.AddCell(cell)
Next
For Each
row As DataRow In _data.Rows
pdfTable.AddCell(
CInt(row(0)))
pdfTable.AddCell(row(
1).ToString())
pdfTable.AddCell(
CInt(row(2)))
pdfTable.AddCell(row(
3).ToString())
pdfTable.AddCell(
CDate(row(4)).ToShortDateString)
Next


Dim
pdfDoc As New _
Document(PageSize.A4, 10.0F, 10.0F, 10.0F, 0.0F)
Dim memoryStream As MemoryStream = New MemoryStream
Dim writer As PdfWriter = PdfWriter.GetInstance(pdfDoc, _
memoryStream)
pdfDoc.Open()
pdfDoc.Add(header)
pdfDoc.Add(pdfTable)
pdfDoc.Close()

context.Response.ClearContent()
context.Response.ClearHeaders()
context.Response.ContentType =
"application/pdf"
context.Response.OutputStream.Write(memoryStream.ToArray(), _
0, memoryStream.ToArray().Length)
context.Response.Flush()
context.Response.Close()

memoryStream.Close()

End Sub
#End Region
End Class
End Namespace
Unfortunately, we are not creating PDFs quite yet.  We still have to setup the trigger.  Jump back to the Default.aspx.vb file.  We need to add a line of code to the Click trigger that will execute our BLL | DAL | create the datatable and pass it back to the BLL | pass the table to the Presentation Layer | Executes the PDFExport Subroutine in our BLL.  Quite honestly, this is rather easy since we already have all the other supporting code in place:
rmc_BLL.PDFExport(rmc_BLL.getDataTable(), DateTime.Now, _
HttpContext.Current)
So our entire back-end page code should look like:
Partial Class _Default
Inherits System.Web.UI.Page
#Region "Globals"
Dim rmc_BLL As ReconMobileSystems.PDF.BLL.PDFExport = New _
ReconMobileSystems.PDF.BLL.PDFExport
#End Region
#Region
"Page Methods"
Protected Sub btnTrigger_Click(sender As Object, e As EventArgs) _
Handles btnTrigger.Click
rmc_BLL.PDFExport(rmc_BLL.getDataTable(),
DateTime.Now, _
HttpContext.Current)
End Sub
#End Region
#Region
"Page Helpers"

#End Region
End Class
Press F5 and run the application.  You should see a button to click and then you should be well versed in the albums that Van Halen has produced:


image


 


 


 


Håþþ¥ .ñꆆïñg…