How to find Microsoft Office version installed using C#

In the past, I had a requirement to find the Microsoft Office version installed (Office 2003, Office 2007 etc) along with their bitness (32-bit or 64-bit).  There was no straight forward way to find out the Microsoft Office version.  I searched around the net and came up with a solution from multiple code snippets I found and analyzed.

Here is a working copy that I derived:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Runtime.InteropServices;
namespace Blog
{
    public class VersionFinder
    {
        [DllImport("kernel32.dll")]
        static extern IntPtr GetCurrentProcess();
        [DllImport("kernel32.dll", CharSet = CharSet.Auto)]
        static extern IntPtr GetModuleHandle(string moduleName);
        [DllImport("kernel32", CharSet = CharSet.Auto, SetLastError = true)]
        static extern IntPtr GetProcAddress(IntPtr hModule, [MarshalAs(UnmanagedType.LPStr)]string procName);
        [DllImport("kernel32.dll", CharSet = CharSet.Auto, SetLastError = true)]
        [return: MarshalAs(UnmanagedType.Bool)]
        static extern bool IsWow64Process(IntPtr hProcess, out bool wow64Process);
        [DllImport("Advapi32.dll")]
        static extern uint RegOpenKeyEx(UIntPtr hKey, string lpSubKey, uint ulOptions, int samDesired, out int phkResult);
        [DllImport("Advapi32.dll")]
        static extern uint RegCloseKey(int hKey);
        [DllImport("advapi32.dll", EntryPoint = "RegQueryValueEx")]
        static extern int RegQueryValueEx(int hKey, string lpValueName, int lpReserved, ref uint lpType,
            System.Text.StringBuilder lpData, ref uint lpcbData);
        private static UIntPtr HKEY_LOCAL_MACHINE = new UIntPtr(0x80000002u);
        private static UIntPtr HKEY_CURRENT_USER = new UIntPtr(0x80000001u);
        private Dictionary<string, string> OfficeVersions = new Dictionary<string, string>();
        public VersionFinder()
        {
            OfficeVersions.Add("7.0", "Office97");
            OfficeVersions.Add("8.0", "Office98");
            OfficeVersions.Add("9.0", "Office2000");
            OfficeVersions.Add("10.0", "OfficeXP");
            OfficeVersions.Add("11.0", "Office2003");
            OfficeVersions.Add("12.0", "Office2007");
            OfficeVersions.Add("14.0", "Office2010");
            OfficeVersions.Add("15.0", "Office2013");
        }
        private string GetOfficeVersionNumber()
        {
            string OfficeVersionNo = null;
            bool Is64BitWindows = Is64BitOperatingSystem();
            if (!Is64BitWindows)
            {
                OfficeVersionNo = GetOfficeVersionNumber("SOFTWARE\\Microsoft\\Office\\");
            }
            else
            {
                OfficeVersionNo = GetOfficeVersionNumber("SOFTWARE\\Microsoft\\Office\\");
                if (OfficeVersionNo == null)
                    OfficeVersionNo = GetOfficeVersionNumber("SOFTWARE\\Wow6432Node\\Microsoft\\Office\\");
            }
            return OfficeVersionNo;
        }
        private string GetOfficeVersionNumber(string RegistryPrefix)
        {
            string CurrentOfficeVersionNo = null;
            foreach (string OfficeVersionNo in OfficeVersions.Keys)
            {
                string Path = GetRegKey64(HKEY_LOCAL_MACHINE, RegistryPrefix + OfficeVersionNo + "\\Excel\\InstallRoot", "Path");
                if (Path != null)
                {
                    CurrentOfficeVersionNo = OfficeVersionNo;
                    break;
                }
            }
            return CurrentOfficeVersionNo;
        }
        public string GetOfficeVersion()
        {
            string OfficeVersionNo = GetOfficeVersionNumber();
            string InstalledOfficeVersion = OfficeVersions[OfficeVersionNo];
            bool Is64BitWindows = Is64BitOperatingSystem();
            if (!Is64BitWindows)
            {
                //If windows is 32 bit, then office cannot be 64 bit
                InstalledOfficeVersion += " (32 bit)";
            }
            else
            {
                Nullable<bool> isOffice64Bit = IsOffice64Bit("SOFTWARE\\Microsoft\\Office\\", OfficeVersionNo);
                if (isOffice64Bit == null)
                    isOffice64Bit = IsOffice64Bit("SOFTWARE\\Wow6432Node\\Microsoft\\Office\\", OfficeVersionNo);
                if (isOffice64Bit.HasValue && isOffice64Bit.Value)
                    InstalledOfficeVersion += " (64 bit)";
                else if (isOffice64Bit.HasValue && !isOffice64Bit.Value)
                    InstalledOfficeVersion += " (32 bit)";
                else
                {
                    InstalledOfficeVersion += " (Unknown bit)";
                }
            }
            return InstalledOfficeVersion;
        }
        private bool Is64BitOperatingSystem()
        {
            if (IntPtr.Size == 8)
            {
                //This size indicates that this is 64-bit programs
                //and 64-bit programs can run only on Windows 64
                return true;
            }
            else
            {
                //This size indicates that this is 32-bit programs
                //and 32-bit programs can run only on Windows 32 and 64
                //Detect if current program is 32-bit, but running on Windows 64
                bool flag;
                return ((DoesWin32MethodExist("kernel32.dll", "IsWow64Process") && IsWow64Process(GetCurrentProcess(), out flag)) && flag);
            }
        }
        private Nullable<bool> IsOffice64Bit(string RegistryPrefix, string OfficeVersionNo)
        {
            Nullable<bool> isOffice64Bit = null;
            string Bitness = GetRegKey64(HKEY_LOCAL_MACHINE, RegistryPrefix + OfficeVersionNo + "\\Outlook", "Bitness");
            if (Bitness == "x86")
                isOffice64Bit = false;
            else if ((Bitness == "x64"))
                isOffice64Bit = true;
            return isOffice64Bit;
        }
        private bool DoesWin32MethodExist(string moduleName, string methodName)
        {
            IntPtr moduleHandle = GetModuleHandle(moduleName);
            if (moduleHandle == IntPtr.Zero)
            {
                return false;
            }
            return (GetProcAddress(moduleHandle, methodName) != IntPtr.Zero);
        }
        private string GetRegKey64(UIntPtr inHive, String inKeyName, String inPropertyName)
        {
            int hkey = 0;
            int in32or64key = 0x0100;
            int QueryValue = 0x0001;
            try
            {
                uint lResult = RegOpenKeyEx(HKEY_LOCAL_MACHINE, inKeyName, 0, QueryValue | in32or64key, out hkey);
                if (0 != lResult) return null;
                uint lpType = 0;
                uint lpcbData = 1024;
                StringBuilder AgeBuffer = new StringBuilder(1024);
                RegQueryValueEx(hkey, inPropertyName, 0, ref lpType, AgeBuffer, ref lpcbData);
                string Age = AgeBuffer.ToString();
                return Age;
            }
            finally
            {
                if (0 != hkey) RegCloseKey(hkey);
            }
        }
    }
}
And, you will be invoking the GetOfficeVersion method as below:

static void Main(string[] args)
{
    String OfficeVersion = new VersionFinder().GetOfficeVersion();
    Console.WriteLine("OfficeVersion: " + OfficeVersion);
    Console.ReadLine();
}

Hope, this is useful.

EXISTS function in Oracle

There is a very simple information, and nothing greater, but still this could be useful and can save you and your time when you need it.


People who have worked in Transact-SQL in SQL Server will feel the importance of missing EXISTS function when working on Oracle or any other databases.  Using EXISTS function, we can check if a table exists in database, column exists in table, or row or value exists in a table.  The EXISTS can also be used as a logical operator in WHERE clause.


We had a requirement to check the existence of table before we attempt to create a table in an Oracle database.  I did spend some time to find any similar methods in Oracle, but it was not successful.  Then I tried a few approaches, some worked and some did not; some threw syntax errors.  I am providing the solution which I found to be useful and functional and if you are going to have any such or similar requirements in future, this information can be useful.


One of the simple ways to achieve this is to check the system table where metadata for all database objects is stored.  In Oracle, we can get this metadata from ALL_OBJECTS table.  Since metadata for objects of types TABLE, VIEW, FUNCTION, INDEX, TRIGGER etc are stored here, to check the existence of a table we will have to have to filter out the data based on the OBJECT_TYPE column of ALL_OBJECTS table.  Otherwise, if there is any other object than the type we want, the result can be misleading.


For example, to retrieve the metadata for a table STUDENT, we can query the ALL_OBJECTS as below:


SELECT * FROM ALL_OBJECTS WHERE OBJECT_NAME = 'STUDENT' AND OBJECT_TYPE = 'TABLE'


Now, putting this query into our work as below, unfortunately, will not work, but will throw syntax error:


IF ((SELECT COUNT(*) FROM ALL_OBJECTS WHERE LOWER(OBJECT_NAME) = LOWER('TableName') AND OBJECT_TYPE = 'TABLE') > 0)
//CREATE TABLE SCRIPT HERE
END IF;


In SQL Server, the above statement will work without any error as this is a valid T-SQL statement, but not in Oracle.  The better and easy solution is to write a function which returns TRUE or FALSE.  I wrote the following function and it served my purpose:


CREATE OR REPLACE
FUNCTION IsTableExists(TableName IN VARCHAR2) RETURN BOOLEAN
AS
      record_count INTEGER;
      result_boolean BOOLEAN;
BEGIN
      SELECT COUNT(*) INTO record_count FROM ALL_OBJECTS
      WHERE LOWER(OBJECT_NAME) = LOWER(TableName)
      AND OBJECT_TYPE = 'TABLE');
      IF record_count = 1 THEN
        result_boolean := TRUE;
      ELSE
        result_boolean := FALSE;
      END IF;
      RETURN result_boolean;
END;


Similar to this, you can check the existence of column in a table from USER_TAB_COLUMNS table and existence of constraint from USER_CONSTRAINTS table.  With the metadata stored in USER_TAB_COLUMNS table, we can conclude data types for columns, which columns are nullable, column length, etc.

Custom Log Object with Log4J

In this article, I will explain how to create Custom Log Object with Log4J.

It is a common practice to pass log message string to info, debug, warn, error and fatal methods of logger class, and Log4J by default logs the given message string given to a log file, database etc. using the appenders. And, what if we want to log more details such as user ID, application name, version, browser information, client IP etc.

The PatternLayout configuration does not support logging any of the above additional information, and we cannot log any of these additional values using any of Log4J format modifier.  The solution is - we write our own log message class containing these additional information.

The info, debug, warn, error and fatal methods Log4J logger takes object as method argument. So, any Java object can be passed to these methods.

Let us create a Java POJO class containing the additional fields we are interested in:

package com.yourcompany.logger;

public class LogMessage {
      private String appId;
      private String version;
      private String userId;
      private String browserInfo;
      private String clientIp;
      private String hostIp;
      private String url;
      private String message;
      private String stackTrace;
           
      public String getAppId() {
            return appId;
      }
      public void setAppId(String appId) {
            this.appId = appId;
      }
      public String getVersion() {
            return version;
      }
      public void setVersion(String version) {
            this.version = version;
      }
      public String getUserId() {
            return userId;
      }
      public void setUserId(String userId) {
            this.userId = userId;
      }
      public String getBrowserInfo() {
            return browserInfo;
      }
      public void setBrowserInfo(String browserInfo) {
            this.browserInfo = browserInfo;
      }
      public String getClientIp() {
            return clientIp;
      }
      public void setClientIp(String clientIp) {
            this.clientIp = clientIp;
      }
      public String getHostIp() {
            return hostIp;
      }
      public void setHostIp(String hostIp) {
            this.hostIp = hostIp;
      }
      public String getUrl() {
            return url;
      }
      public void setUrl(String url) {
            this.url = url;
      }
      public String getMessage() {
            return message;
      }
      public void setMessage(String message) {
            this.message = message;
      }
      public String getStackTrace() {
            return stackTrace;
      }
      public void setStackTrace(String stackTrace) {
            this.stackTrace = stackTrace;
      }
     
      @Override
      public String toString(){
            StringBuilder logMessageString = new StringBuilder();
            //Create message string with all additional
            //information fields. Each field can be delimited
            //with comma, space, tab, etc
            return logMessageString.toString();
      }
}

After populating all the fields in the LogMessage object, it can be passed to info, debug, warn, error and fatal methods of logger class as a parameter. Log4J internally calls toString method of passed object and you will have format modifier %m of PatternLayout printing the output of LogMessage.toString(). So, your PatternLayout in log4j.xml remains unchanged with all of your other format modifier:

<layout class="org.apache.log4j.PatternLayout">
      <param name="ConversionPattern" value="%-5p [%d] %c.%t: %m%n" />
</layout>

We can have another wrapper class to create an instance of LogMessage and invoke Log4J logger as below:

package com.yourcompany.logger;

import org.apache.log4j.Logger;

public class LogManager {
      private Logger log4JLogger;
     
      public LogManager(String callerClassName){
            log4JLogger = Logger.getLogger(callerClassName);
      }
     
      public void logError(String msg, Exception ex){
            LogMessage logMessage = constructLogMessage(msg, ex);
            log4JLogger.error(logMessage);
      }
     
      //You can have more methods for different log level
     
      private String constructLogMessage(String msg,
                        Exception ex){
            LogMessage logMessage = new LogMessage();
           
            //Get appId and version from application configuration
            //file, DB or wherever it is available
            logMessage.setAppId("MyApp");
            logMessage.setVersion("1.0.0.2");
           
            //The following can be got from HttpServletRequest
            //when request object is available in this class
            logMessage.setUserId(request.getRemoteUser());
            logMessage.setBrowserInfo
                        (request.getHeader("User-Agent"));
            logMessage.setClientIp(request.getRemoteAddr());
            logMessage.setHostIp(request.getLocalAddr());
            logMessage.setUrl(request.getRequestURL().toString());
           
            logMessage.setMessage(msg);
            logMessage.setStackTrace(getCompleteStackTrace(ex));
      }
     
      private String getCompleteStackTrace(Exception e){
            StringWriter writer = null;
            try {
                  writer = new StringWriter();
                  joinStackTrace(e, writer);
                  return writer.toString();
            }
            finally {
                  if (writer != null){
                        try {
                              writer.close();
                        }
                        catch (IOException e1) {
                        // ignore
                        }
                  }
            }
      }
     
      private void joinStackTrace(Throwable e, StringWriter sw) {
            PrintWriter printer = null;
            try {
                  printer = new PrintWriter(sw);
                  while (e != null) {
                        printer.println(e);
                        StackTraceElement[] trace =
                                    e.getStackTrace();
                        for (int i = 0; i < trace.length; i++)
                        printer.println("\tat " + trace[i]);
                        e = e.getCause();
                        if (e != null)
                        printer.println("Caused by:\r\n");
                  }
            }
            finally {
                  if (printer != null)
                        printer.close();
            }
      }
}

Finally, you can invoke the logger wrapper from your class as below:

public class MyClass {
      private static LogManager logger =
            new LogManager("com.yourcompany.MyClass");
     
      public void myMethod() {
            iriLogger.logError("Test exception", new Exception());
      }
}

Deploying DTS packages in SQL Server 2000

This article had already been posted by me in ASP Alliance in May 2009.  Since the images are not loading anymore in the article, that is of no use for anyone. So, I am posting the same again in my blog.

Challenges in deployment of DTS packages

When developing DTS packages, the DTS connection objects and task objects are set with server names (could be DB, FTP, SMTP, etc), credentials, source/target tables, and source/target file path and names in their properties.  This imposes the challenge of changing such hard coded values at the time of deployment of the DTS packages in different environments with environment-specific server names, credentials, etc.  With deployment being an activity of server management group, deployment of such DTS packages becomes impossible without help from development group, though the, server management group does not want to share sensitive information such as server credentials to any other group.  The second challenge of deployment is the manual intervention required in deployment.  Although Enterprise Manager provides an uncomplicated user interface to deploy DTS, it is manual; so it is tedious and error-prone.  This article explains how such hard coded values can be externalized so that changing such values becomes an easy job, and how the deployment of DTS packages can be automated.

Setting values dynamically

Dynamic Properties Task

"Dynamic Properties Task" is one of the DTS task objects.  This task object helps to set properties dynamically at runtime for data sources and data pumps.  To add "Dynamic Properties Task" to the DTS package, just double click on the "Dynamic Properties Task" located in the DTS designer toolbar or drag and drop the task into the designer window.

When added to DTS designer window, the task opens the Properties window where you will have to select the properties of the DTS objects to be made dynamic.  To set properties of FTP task such as FTP site, username, and password, we will have to add those properties by clicking Add button of the Dynamic Properties Task properties window.

When clicking Add button, all the DTS objects used in the DTS packages are displayed on the left pane of the Package Properties window and properties of the selected object are displayed on the right pane of the same.

The values can be externalized to an INI file, a table in a database, global variable, or environment variable.

Reading from INI file

An INI file contains keys grouped under sections.  Each section is enclosed in a square bracket and keys appear grouped below the section.  To set FTP task properties, we will have to create INI file as below:

[FTP]

Site=ftp://myhost/shared

UserName=admin

Password=sD8ek46B

DirectoryPath=\\fileshare\shared\input

To set a property from an INI file, select the property on the right pane of the "Package Properties" window and click Set button to open Add/Edit Assignment window.  Select the Source as INI file.  Enter the path of the INI file by browsing and select Section and Key from the dropdown values. 

Whenever, you want to change any FTP properties based on environment, you can do so by modifying them in the INI file leaving the DTS unchanged.  Make sure to have the dynamic properties task executed before FTP task by altering the execution flow.

Remember however that the path of the INI file still is hard coded.

Reading from database table

In order to read from a database table, the DTS package must have a database connection object in it.

To set a property from a database table, select the Source as Query in the Add/Edit Assignment window.  Select a database connection object from the Connection dropdown and enter a select query in the Query text area.

We have not yet fully solved the hard coding problem yet.  Now, the FTP task properties are made dynamic by setting them from a database table, but the connection itself will have hard coded values of database name, database name and credentials.  To set values dynamically using database query, the connection object must already exist with correct data source values and credentials; so, you cannot set properties of connection objects using database query.  However, you can set connection object properties using global variables.

Reading from Global Variables

Reading from Global Variables is straightforward.  Just select source as Global Variable and select the desired global variable from the Variable dropdown.  Global variables can be created and set with default value by clicking "Create Global Variables" button.

Typically, DTS packages are executed by calling them from a SQL Job, from command window, or from a process.  When executing DTS packages, values for the global variables can be passed as arguments thus changing the default value of the global variables:

DTSRun /S "DBServerName" /N "PackageName" /E /A "FTPSite":"8"="ftp://otherhost/shared"

To set values for connection object, the properties of connection object set through global variables and they can be overridden as below for different environment.

DTSRun /S "DBServerName" /N "PackageName" /E /A "glbvarDBName":"8"="TestDB" /A "glbvarDBServer":"8"="DBServerName" /A "glbvarDBUser":"8"="sa" /A "glbvarDBPassword":"8"="welcome01*"

Using DTSRun or DTSRunUI utility, the command can be encrypted for security reasons.  The encrypted command for the above, will appear as below:

DTSRun /~Z0x2D35A86F276F249E84839CBBFB3F964CB97886C12A0B29069E392586DE48995E77DF17953697B2381635BBE582FDFF85D6A1D577F5B287478FFE28B0724A28CB64D271A37B10B06A7CA59BAF9DAED4FC6ECC0D2B1E1D83311B39628C467320C13CEBD60C92B1745C0D1108BD67F7A93EB473282B91057AFE9BB228CFB3597F2D1D5AD85F4C408B601841560A5BC0F4161A4F9053BB661A136996151C855043BE8803C199D3F003D3B8C1B97D86013C385121036F86CBA501F9652B95CD9256F1E4FC5097DC0D2EFEEB616374B12D41781A21258B74DFB94828586FE032AE49C42846A3F287178EF414EB9D8E95EB731BFF11E82159FEF0C4C5FFB226856A3728733BF29D94B9A823576FD848AEAE01CA4690A1FE4AF7D77694DAFCE25E181DB645170DC526557851790E77369240266E07661598FB17E9D328AD98CE2021A2B628EE0DB0DD89E1C4CB7E64AF7AB6D3612ACE3D0B27660F09DD3AB19A2B7DFAB052E1 DA39D88E12975DBED439D3F468845AF0C1D6C032FB7E2AC6EF

The global variables also provide an advantage of ability of being utilized in ActiveX scripts.  They can read as below:

FtpSiteUrl = DTSGlobalVariables("FTPSite").Value

Reading from Environment Variables

The environment variable is set from System Properties.  Setting a system variable makes the variable available for all the users including the account in which SQL Agent runs.

Reading from environment variable is as easy as selecting source as "Environment Variable" and selecting the environment variable from the variable dropdown.

The disadvantage however in reading from environment variable is that adding a new environment variable or modifying an existing environment variable may require server restart.  When database is clustered, the variables have to be set in all the clustered servers.

Automated Deployment

Using VBScript

The DTS.Package object of VBScript exposes two methods - LoadFromStorageFile and SaveToSQLServer - which help to deploy DTS packages from physical files to DB server.  The following script serves the purpose.  The script loops through all the files with DTS extension in the DTS subdirectory where the script is located, and install the DTS packages one by one.

Option Explicit

Dim DBServerName, DBUserName, DBPassword

DBServerName = "10.142.117.202\ARD54, 1297"

DBUserName = "sa"

DBPassword = "welcome01*"

'--------------------------------------------------

Dim objFSO, objFile, file, objDTSPackage, DTSPath

DTSPath = ".\DTS"

Set objFSO = CreateObject("Scripting.FileSystemObject")

For Each file In objFSO.GetFolder(DTSPath).Files

   If LCase(objFSO.GetExtensionName(file)) = "dts" Then

      Set objDTSPackage = CreateObject("DTS.Package")

      WScript.Echo "Creating DTS: " & file.name

      objDTSPackage.LoadFromStorageFile DTSPath & "\" & file.name, ""

      WScript.Echo Chr(9) & "DTS loaded successfully"

      objDTSPackage.SaveToSQLServer DBServerName, DBUserName, DBPassword

      WScript.Echo Chr(9) & "DTS created in the server successfully"

      Set objDTSPackage = Nothing

   End If

Next

WScript.Echo "END"

When there are multiple versions in the physical DTS file, then GUID of the version to be deployed must be mentioned.  When there is only one version in the physical file, then the available version will be deployed.