C# - Microsoft Word 2010 Automated Mail Merge Part 2 of 2

Back to Part 1

In this example I'll use a more dynamic approach that reads customer data from a SQL database and customizes the query before generating the mail merge.

The ASP.NET application pulls in all of the customer data from from the Northwind database and allows paging and sorting of the data via a Grid View Control. Checkbox controls allow selection of records that will be included in the mail merge. Clicking the Mail Merge button selects all of the checked customers and launches Microsoft Word with all of the customer data pre-populated into the mail merge template (shown below).

Download the Northwind Sample Database - Here
Download the Microsoft Mail Merge Sample Code - Here

First thing I noticed was that Microsoft no longer includes the sample databases with SQL 2008R2, after a couple of searches I found that you can still download them and run scripts or restore the database files.

After I downloaded the sql2000sampleDb.msi I ran the install. Once the install was complete I opened a command prompt and navigated to C:\SQL Server 2000 Sample Databases. Because my SQL server is installed as the default instance I typed sqlcmd -i instpubs.sql to perform the install, I verified that the Northwind database was now installed on my default SQL instance. Note: if you have a non-default SQL instance you can use the following syntax - sqlcmd -S .\InstanceName -i instpubs.sql

I created a new Visual Studio 2010 ASP.NET project, added a new reference to the project by expanding references from the solution explorer and selecting new reference. I selected the COM tab and chose the Microsoft Word 14 reference. This will add 2 new references to the project - Microsoft.Office.Core and Microsoft.Office.Interop.Word. 

This example uses the Microsoft MSDN sample code for the basic mail merge functions.

Add a using statement for the newly added reference as follows:
using Word = Microsoft.Office.Interop.Word;

I cut the first four lines of the Microsoft code and pasted it at the beginning of the class declaration to ensure proper scope for these variables.

Next I Selected the Server Explorer tab and created a connection to my local SQL Server and Northwind database.

I added GridView and button controls to the page

Next I configured the datasource for the Gridview control and selected the Northwind connection string from the dropdown

Next A select * statement is chosen from the Northwind Customers table on the configure the Select Statement screen

This completes the Gridview datasource

Next I added a checkbox control to the Gridview to allow selecting one or more customers. Select the source view of the aspx page and add the following code to the gridview under the columns node.

Back to the design view - double click on the button on the form and add the following code to the  button click event.

The code will iterate through the gridview control and select only the rows that were checked. I built a very simple customer class to hold the data from the selected rows. This can be customized to include any of the fields from the SQL database but for now I'm only including the ID, ContactName and ContactTitle fields. A Generic Dictionary <T> Collection is used to hold the customer objects and this will be passed to  the CreateMailMergeDataFile method.

In the same button click event that you added the snippet above add all of the code in the button1_click event from the MSDN sample code (not all code shown highlighted below)

Next add the remainder of the MSDN sample code into the aspx page just under the public partial class declaration.

Replace the highlighted code in the CreateMailMergeDataFile with the code shown below

Replace highlighted code with this code

I included my project files but you will have to modify the connection string information to your SQL database if it's not on the local machine in the default SQL instance.

C# - Microsoft Word 2010 Automated Mail Merge Part 1 of 2

I was recently tasked with building an add-on for a CRM application to allow the application to integrate with Microsoft Word and generate a mail merge.

I decided to use two examples for demonstrating how to generate the mail merge, the first will be from a Microsoft MSDN code sample and demonstrate a hard coded method of outputting data from my C# application to Microsoft Word.

For the second example, I'll demonstrate a more dynamic approach by connecting to a SQL sample database (Northwind). I'll build an ASP.NET user interface to allow selection and filtering of customers prior to sending the data to Microsoft Word for the mail merge.

Visual Studio 2010
Microsoft Word 2010
Microsoft SQL Server 2008R2

Part 1
Microsoft Mail Merge Code - Here

Microsoft created some sample code for integrating custom c# applications with Microsoft Word and automating the mail merge, the code provides the mechanism for programmatically generating the mail merge with some inline customer data.

From Visual Studio 2010 I created a new ASP.NET project, the Microsoft code was developed with a Windows Forms application in mind but I ported it into an ASP.NET application. First I added a new reference to the project by expanding references from the solution explorer and selecting new reference. I selected the COM tab and chose the Microsoft Word 14 reference. This will add 2 new references to the project Microsoft.Office.Core and Microsoft.Office.Interop.Word.

Add a using statement for the newly added reference as follows:
using Word = Microsoft.Office.Interop.Word;

I cut the first four lines of the Microsoft code and pasted it at the beginning of the class declaration to ensure proper scope for these variables.

Next I switched to the design view of the default.aspx page in my project and clicked on the toolbox and added a new button to the current page.

Next I double clicked the new button and pasted the remainder of the code from the Microsoft article into the click event handler of the new button.

I built the application with no errors and launched it for a test.

I clicked the button and it looks like everything is working as expected so far, Microsoft Word launches and displays three customer forms that are ready to be printed/mailed.

The standard form data is consistent on all of the user forms and the inline unique customer information is populated into the variable field locations.

This example can be modified to read from a text or csv file or any number of methods that would allow an automated extraction of customer data.

The data can also be kept inline in the code behind as is.

This completes the first example, this example provides a good look into the Word object model and what it takes to programmatically create and manipulate Word objects. Again this is a pretty static and simple method for demonstrating the mail merge. A more advanced approach will be described in Part 2.

Continue to Part 2


Microsoft Sysprep - Which Servers/Roles/Applications include support

This might not be big news to anyone reading this article but I just found out that Microsoft does not support using Sysprep on several of its own products.

I learned this while digging through the Lync 2010 documentation for a project that I'm currently working on. This got me thinking about other things that might not be supported when using Sysprep, until now I thought that as long as you used Sysprep you would not be compromising Microsoft support.

In this article I will try and list the details of what is and isn't supported by Microsoft when it comes to Sysprep. 

Microsoft does not support using Sysprep with Lync 2010, SQL Server 2008 Express and several other products, what does this mean? It means that you can use Sysprep to clone the Windows Operating system as long as you verify that no non-supported roles are enabled prior to cloning (see table below). It means do not have any of the non-supported application's components installed/configured prior to making the clone.

For Server 2008R2 only certain roles of the operating system support using Sysprep, in other words if you are going to clone a 2008 R2 machine, make sure none of the roles listed below that say no are enabled.
Server Role
Sysprep Support
Active Directory Certificate Server (AD CS)
Active Directory Domain Services (AD DS)
Active Directory Federation Services (AD FS)
Active Directory Lightweight Directory Services (AD LDS)
Active Directory Rights Management Server (AD RMS)
Application Server
DHCP Server
DNS Server
Fax Server
File Services
Network Policy and Access Services
Network Policy Routing and Remote Access Services
Print Services
Terminal Services
Not supported in scenarios where the master Windows image is joined to a domain.
UDDI Services
Web Server (Internet Information Services)
Does not support Sysprep with encrypted credentials in applicationhost.config.
Windows Deployment Services

Sharepoint 2010 - A Sysprep'd machine can be created with Sharepoint 2010 installed but must be prior to the configuration wizard being run.

SQL Server 2008R2 - There is an MSDN article explaining how to install SQL on a Sysprep'd image - Here

Microsoft Exchange Server - You cannot Sysprep an Exchange machine because of its integration with Active Directory. Recommended method would be to Sysprep the operating system and automate the installation.

Domain ControllersYou cannot deploy preconfigured domain controllers by using image-based installation with Sysprep. However, you can configure a domain controller by first deploying a member server and then automatically running a script that runs Dcpromo.exe, the Active Directory Installation Wizard.

Limited server configuration - According to Microsoft some server components must be installed and configured after an image-based installation with Sysprep is complete. These components include Certificate Services, Cluster service, and any software that is dependent on the Active Directory directory service. They also include any application or service that stores the computer name or the computer SID and cannot recover if the computer name or SID changes.

Security SettingsYou cannot use image-based installation with Sysprep to deploy computers that contain any files that are encrypted by using Encrypting File System (EFS). In addition, you cannot use image-based installation to deploy systems that have already been configured with NTFS security settings, such as file and folder permissions, unless the disk-imaging program supports the NTFS file system. However, you can use a script to configure these settings after the image-based installation is complete.

I usually opt to build fresh virtual machines and not Sysprep because of these kind of incompatibilities and to rule out strange behavior potentially caused by cloning machines.

I'll continue to update this post if I find any other incompatibilities, please feel free to respond if you know of any other incompatible software.


VMWare vCloud Director - Installation and Configuration Part 3

This is part 3 and the final part in a three part blog post, in this first post I'll briefly describe the process of configuring the vCloud Director application. I will also try and describe why vCloud Director is a useful application and what it brings to a development lab.

  1. vCloud Director - Configuration
  2. The Why - Why Clouds, what are the benefits?

vCloud Director Configuration

I won't cover the initial configuration step by step as there are many tutorials already available for establishing the initial vCloud Director configuration. I will point out the important things that will hopefully assist in successfully understanding and configuring your environment.

Steps - perform the following steps from the vDC home tab quick start menu
  1. Attach a vCenter server, the first step is to attach a vCenter server to the vCloud Director environment. The vCenter server must be in an available automated DRS configured cluster with an available resource pool. Note: for each vCenter server that you add to the vCloud Director environment you will first need a vShield Manager server configured and available.
    1. From the vDC home page select step 1 attach a vCenter
    2. Select the correct vCenter server to add to the vDC environment
  2. Create a Provider vDC - a Provider vDC combines the compute and memory resources of a single vCenter server resource pool with the storage resources of one or more datastores connected to that resource pool. A Provider vDC is the source for Organization vDCs (Org vDCs)
    1. From the vDC home page select step 2 create provider vDC
    2. Provide a Name
    3. Select a vCenter and Resource Pool
    4. Add Datastores
    5. Prepare the ESX(i) hosts
    6. Navigate to the Management Monitor tab and verify the ESX(i) hosts were prepared successfully, if you see a red x next to a host an error must have occurred. Note: I had an issue on both of my ESXi hosts where the preparation process was failing, it also affected HA on my vCenter cluster as this configuration would fail and pop an error stating the agent could not be installed. I ended up having to re-run the ESXi install to fix the issue on both hosts, and once HA would configure successfully then the vCloud host prepare finished successfully
    7. Once the Provider vDC has been created it should appear under the selected resource pool in the selected vCenter interface
  3. Create external provider networks - a logically separated network based on a vSphere port group. It is the network created by the service provider to allow virtual machines and organizations to access the outside world (Internet). If you want your Organization (and also your vApps) to have connectivity to the external world you need to have External Networks. 
    1. From the vDC home page select step 3 create external network
    2. Choose a vCenter server
    3. Add the network mask, default gateway, DNS information and ip range
    4. Enter a name for the external network
    5. The provider is automatically added to the Provider vDC
  4. Create a network pool - a group of networks that is available for use within an Org vCD to create vApp networks and certain types of organization networks. A network pool is backed by vSphere network resources such as vlan ids, port groups, or cloud isolated networks. vCD uses network pools to create NAT routed and internal organization networks and all vApp networks. Network traffic on each network in a pool is isolated at layer 2 from all other networks. Each Org vCD can have one network pool, multiple Org vCDs can share the same network pool. The network pool for an Org vDC provides the networks created to satisfy the network quota for an Org vDC. There are three different kinds of network pools available within vCD and they are VLAN backed network pools, vCD isolation backed network pools and vSphere port group backed network pools.
    1. From the vDC home page select step 4 create a network pool
    2. Select a Network Pool Type
    3. Select vCenter Server
    4. Configure Pool
    5. Provide a name for the pool
  5. Create an Organization - Organizations provide resources to a group of users and set policies that determine how users can consume those resources. An organization is the fundamental vCD grouping that contains users the vApps that they create and the resources that the vApps use. It is a top level container in a cloud that contains one or more organization virtual datacenters (Org vDC's) and catalog entities.
    1. From the vDC home page select step 5 create and organization
    2. Provide a name
    3. Specify LDAP Settings (if desired) or create local users
    4. Specify if organization can publish its catalogs to other organizations
    5. Configure SMTP if email notifications are desired
    6. Specify leases and quotas (if desired)
    7. Create the Org vDC and specify which Provider vDC it is going to get its resources from
    8. Select an allocation model - example pay as you go
      1. Configure settings for model selected
      2. Specify how much storage is allocated (if desired)
      3. Specify which network pools the Org vDC is connected to
      4. Provide a name and description for Org vDC
  6. Catalog - a container for the following entities vApp templates, vApps, media images (floppy, ISO). Organizations will have their own catalog which they can populate and share with other organizations and users. 
    1. From the vDC home page select step 8 which is add a catalog to an organization
    2. Select the organization to add the catalog too
    3. provide a name for the catalog

The Why - Why Clouds? What are the benefits and why should I care?

"Cloud is all about giving the end-users an unprecedented level of flexibility that allows them to do things that were only available to vSphere administrators before. In a way you can think of vCloud Director as an interface (or a proxy) into the virtual infrastructure. This allows vSphere administrators to give end-users a lot more flexibility, but at the very same time it allows them to keep full control of what end-users can do"

The Virtual DataCenter - much like when you walk into a physical datacenter you will find some resources that you want to connect your virtual machines too. For example you will find network sockets, power sockets, storage resources that you can put your virtual machines on, you might find one or two sockets that state this socket goes to the internet.  Users are now empowered to provision vApps and workloads without having to talk to the IT administrator or the system administrator. They don't have to worry about connecting something to the wrong network or doing something that would require the IT administrator. Its all about Self Service

I'm still trying to understand and wrap my head around the whole vCloud infrastructure and how it changes a typical vSphere environment. vCloud Director provides another layer of abstraction to your environment, it reminds me of what vCenter did to my two ESX(i) hosts. Only vCD does this to vCenter.

What will vCD do to my existing infrastructure? You will go through a process where you surrender some of the resources of the infrastructure to the vCD at which point it will take over those resources and manage them but it will not touch any of the resources that you have left aside. 

I think my next post will be about configuring vCD networking


VMWare vCloud Director - Installation and Configuration Part 2

In this part we will install vShield Manager, create SSL certificates for vCloud Director and install the CENTOS Linux operating system which will host the vCloud Director software. vCloud Director requires a Linux operating system and cannot be installed on Microsoft Windows operating systems.
  1. vShield Manager - Download, deploy and configure
  2. SSL Certificates - Download Java Development Kit, generate certificates
  3. CENTOS - Download, install and configure 
  4. vCloud Director - Download install and configure

vShield Manager - Download and deploy the vShield Manager virtual appliance from vmware.com vShield Download

Note: for each vCenter server that you add to the vCloud Director environment you will first need a vShield Manager server configured and available.

Deploy the vShield Manager appliance, from vCenter select File | Deploy OVF Template, browse to the VMware-vShield-Manager-5.0.0-473791.ova file downloaded from vmware.com

After the vShield Manager is deployed, power on the virtual appliance. You will be prompted to login, the username is: admin and the password is: default.

Type en or enable followed by the password default to enter privileged mode. Type setup and complete all of the networking information. Save the configuration and type exit to logout. You can log back into the console and type show interface to view the configuration.

This completes the vShield Manager installation/configuration

SSL Certificates - The SSL certificates can be installed using multiple methods, first the Java Developement Kit contains the keytool required to create the certificates. The JDK can be downloaded and installed on a separate machine and the certificates can be created prior to performing the vCloud Director installation. The second method is to use the vCloud Director machine to generate the certificates, this can be accomplished mid way through the software installation and will require a couple of extra steps.

Method 1

Download and install the Java Software Development Kit  - Download Java Development Kit

Create the certificates using the command line tool, note the directories listed below will vary depending on the version of the JDK that you download.

"C:\Program Files\Java\jdk1.7.0_01\bin\keytool.exe” -keystore certificates.ks -storetype JCEKS -storepass password -genkey -keyalg RSA -alias http
“C:\Program Files\Java\jdk1.7.0_01\bin\keytool.exe” -keystore certificates.ks -storetype JCEKS -storepass password -genkey -keyalg RSA -alias consoleproxy
“C:\Program Files\Java\jdk1.7.0_01\bin\keytool.exe” -storetype JCEKS -storepass password -keystore certificates.ks -list

Note: The certificates will be created and stored in a file named certificates.ks located in the C:\Program Files\Java\jdk1.7.0_01\bin directory (or alternate version directory). The certificates created in this example all have the password: password. Make sure to copy these files to a shared location, they will need to be copied to the CENTOS machine before the vCloud Director installation can be completed

CENTOS Installation / Configuration - I chose to download the latest CENTOS v 6.0 x64 version.

Create a new virtual machine

Provide a name for the virtual machine

Select an operating system for the new machine

Add one additional Network Interface Card for a total of 2 to the new virtual machine.

After the virtual machine has been created, attach the CD drive of the virtual machine to the ISO file (CentOS-6.0-x86_64-bin-DVD1.iso) downloaded from vmware.com

Make sure that during the installation of CENTOS you configure the two network adapters with 2 unique ip addresses on the same subnet.

Note: make sure that the network cards are configured to connect automatically and available to all users.

I chose web server from the available package group options during the installation, I also chose customize now and selected several of the available options. Unfortunately I don't know the CENTOS very well and I can't list which options to select to install the tools necessary to successfully build a host for vCloud Director so choose most of the available options. I did not include the ones that made no sense such as the main frame, san, databases, graphics, etc.

The important thing to include is the security package, without the firewall being installed and disabled, I was not able to remotely access the vCloud Director configuration page. 

If after you complete the installation and you power on the virtual machine you see no firewall, you can manually add this be entering the following command at the command line.

yum install system-config-securitylevel

After the installation is complete and rebooted, log in to the machine as root. Open the firewall configuration by selecting System | Administration | Firewall and choose disable.

Map a network drive to the shared location where your certificates.ks file is located.

Open Computer and select File | Connect to Server

Copy the certificates.ks file to the CENTOS desktop, repeat these procedures for the vCloud Director software - vmware-vcloud-director-1.5.0-464915.bin file. Copy the vCloud Director sofware into the root's Home folder on the desktop.

Note: you must change the permissions on the vmware-vcloud-director-1.5.0-464915.bin file by right clicking on the file and selecting the permissions tab and check the Allow executing file as a program checkbox.

vCloud Director
Select Applications | System Tools | Terminal and enter ls -al at the command prompt, this will list all of the available files. type ./vmware-vcloud-director-1.5.0-464915.bin and press enter. This will begin the vCloud Director installation process.
The following prompts will be displayed
Run the installer - type “y”
If you chose Method 1 for creating the SSL certificates earlier in the tutorial, make sure you copy the certificates.ks file into the /opt/vmware/vcloud-director directory now and then you can come back to this prompt and answer "y" to the Run the configuration script and skip the certificates creation step, otherwise type “n”
Method 2 for creating SSL Certificates
Create self signed certificates, from the terminal window type the following commands:  
/opt/vmware/vcloud-director/jre/bin/keytool -keystore certificates.ks -storetype JCEKS -storepass password -genkey -keyalg RSA -alias http -dname “cn=vcloud, ou=vmware, o=vmware, c=US” -keypass password

/opt/vmware/vcloud-director/jre/bin/keytool -keystore certificates.ks -storetype JCEKS -storepass password -genkey -keyalg RSA -alias consoleproxy -dname “cn=vcloud, ou=vmware, o=vmware, c=US” -keypass password
A file named “certificates.ks” will be created in the root's Home folder, copy this file to the /opt/vmware/vcloud-director directory. If the file is located in this directory the installer will not need a path specified.
Continue the installation process by typing /opt/vmware/vcloud-director/bin/configure
  • Select your first IP address, this will be the IP address which is used for vCD web page or press enter to accept the default 
  • Select your second IP address, this will be the IP address which is used for the VM Remote Console or press enter to accept the default 
  • Because we copied the certificates.ks file into the vcloud-directory already just type certificates.ks 
  • Enter the password: password Press enter to skip the “syslog server”
  • Select Oracle or SQL Server 
  • Enter the machine name or IP address for the database server 
  • Press enter to use default database port (1521) Oracle or (1433) SQL 
  • Type the database name, press enter for default (vcloud) 
  • Type the database username 
  • Type the database password 
  • Now the database will be initialized and the vCD install will be completed 
  • Type “y” to start the vCD service

You can monitor the progress of the vCD service start up as follows 
type: tail -f /opt/vmware/cloud-director/log/cell.log

This is an important step to verify your installation has completed successfully.
It will show you the percentage of the initialization of the application that has completed. If everything installs correctly you will see a message “Application Initialization: Complete.
Note: I had a few failed attempts where the application initialization would hang, ussually around 18%. I found that if for whatever reason you have to run the installation again, you must delete the old database from your database server before you re-run the install.
I also had to had to start over rebuilding the CENTOS and re-running the installation a couple of times before I figured out some of these gotchas. After the installation is complete, you should be able to launch a browser and enter the vCloud Director IP Address (the first one assigned) to access the vCloud Director configuration page.

This completes Part 2


VMWare vCloud Director - Installation and Configuration Part 1

This is part 1 in a multi-part blog post, in this first post I'll describe the process of laying the groundwork for what will eventually be a working vCloud Director / vShield Manager deployment in my VMWare VSphere lab.

Part 2 will be forthcoming
  1. Database Server - Guide for installing Oracle 11g or choose Microsoft SQL Server
  2. vShield Manager, SSL Certs, CENTOS and vCloud Director - Deploy and configure the vShield Manager virtual appliance. Generate SSL certificates necessary when installing vCloud Director. Create a Linux operating system to host vCloud Director. Install and configure vCloud Director.  
I decided to learn the process of installing and configuring VMWare's vCloud Director. I plan on installing this within my own lab infrastructure for testing and development/prototyping purposes.

The first thing I wanted to know before I decided to install vCD was

What will vCD do to my existing infrastructure? You will go through a process where you surrender some of the resources of the infrastructure to the vCD at which point it will take over those resources and manage them but it will not touch any of the resources that you have left aside.

Required components for this installation.

Database Server - I decided to document the process of installing Oracle, if you want to use Microsoft SQL Server you can skip to part 2. I chose to use Oracle 11g, created an account on the Oracle website and downloaded the database sofware. I'll be installing the database software on an existing Windows 2003 Server R2 x64 bit virtual machine that I currently use to run my VCenter Server on. Oracle Download

vCloud DirectorVMware vCloud Director enables customers to build secure, multitenant hybrid clouds by pooling infrastructure resources into virtual datacenters. and enabling those resources to be consumed by users on-demand. vCloud Director pools datacenter resources, including compute, storage and network, along with their relevant policies into virtual data centers. Fully encapsulated multitier virtual machine services are delivered as vApps, using the Open Virtualization Format (OVF). End users and their associated policies are captured in organizations. With programmatic and policy-based pooling of infrastructure, users and services, VMware vCloud Director enforces policy intelligently and creates unprecedented flexibility and portability. vCloud Download

vShield Manager - Included with all vShield products, vShield Manager provides a central point of control for managing, deploying, reporting, logging and integrating vShield as well as third-party security services. Working in conjunction with vCenter Server, vShield Manager enables role based access control and separation of duties as part of a unified framework for managing virtualization security. vShield Download

CENTOS - Linux operating system to install vCloud Director on - CENTOS Download

If you are planning on using Microsoft SQL Server skip to part 2.

Oracle installation - After downloading the two files that make up the Oracle 11g installation for Windows x64, extract the zip files and run the installation.

Follow the installation wizard and configure the install according to your requirements, the screenshots below show the configuration that I chose for my lab.

Note: The global database name used in the field below will be used to connect to the Oracle database after the installation is complete

After the Oracle Installation is complete, launch a command prompt and type: 
sqlplus system/oracle@localhost/[Global Database Name]
example:  sqlplus system/oracle@localhost/orcl.csharpsnippets.local

From the SQL> prompt type: create user cloud identified by oracle;

A message should appear: User created.

Now type the following command: grant "RESOURCE", "DBA", "CONNECT" to orcl;

You should see the message: Grant succeeded.

Verify the Oracle install and listener

This completes part 1 and the Oracle Installation.