How to convert WindowsInstaller.Record to System.Data.Dataset or Data Table (While reading MSI file) in C#

Question

I want to read MSI file(Windows Installer Package). I have written a function as below which takes two input parameters : msifileName and Table Name and returns a data table which is one of the MSI table.

public DataTable ReadMsiPropertyTable(string msiFile, string tableName)
    {            
        Type installerType = Type.GetTypeFromProgID("WindowsInstaller.Installer");

        WindowsInstaller.Installer installer = (WindowsInstaller.Installer)Activator.CreateInstance(installerType);

        Database database = installer.OpenDatabase(msiFile, 0);

        string sqlQuery = String.Format("SELECT * FROM {0}",tableName);

        View view = database.OpenView(sqlQuery);

        view.Execute(null);

        Record record = view.Fetch();

        DataTable msiPropertyTable = new DataTable();

        msiPropertyTable.Columns.Add("Column1", typeof(string));
        msiPropertyTable.Columns.Add("Column2", typeof(string));
        msiPropertyTable.Columns.Add("Column3", typeof(string));
        msiPropertyTable.Columns.Add("Column4", typeof(string));

        while (record != null)
        {
            int fieldCount;
            fieldCount = record.FieldCount;

            msiPropertyTable.Rows.Add(record.get_StringData(0), record.get_StringData(1), record.get_StringData(2), record.get_StringData(3));                
            record = view.Fetch();
        }
        return msiPropertyTable;
    }

Using above code snippet, the number of rows and columns of Record are not known. SO I am statically returning only four columns. I want to return all the rows and columns of MSI Table which are in record.

So Please let me know how can I convert output of view or record to Dataset and then bind to Datatable. Or is there any other way to return all the rows and columns. Thanks in advance.

score 3 · Answer 1 · answered Dec 24 '12 at 15:22

3

I would suggest you using the Deployment Tools Foundation (the API library to work with MSI packages, comes together with WiX Toolset) for this task. The API is handy and straight-forward.

For instance, there's a class Database. You can create it by providing the path to the MSI package to the constructor:

var db = new Database("path\to\MSI");

And it gives the any information about the MSI database. For instance, db.Tables["TableName"] returns an instance of the TableInfo class, which in its turn contains the information about columns, rows, primary keys, etc.

Download and install WiX Toolset, and get more info from DTF.chm help file.

answered Dec 24 '12 at 15:22

Yan Sklyarenko

31,557
24
104
139

Thanks Yan for reply but access is denied for the above toolset link in my organization. Is there any other way to populate data table dynamically? – Devashri B. Dec 26 '12 at 06:32
Hmm, it seems the best way would to try explaining why you need access there, rather than seeking for another way just because the access is forbidden... And DTF is the best option to work with MSI database I know about – Yan Sklyarenko Dec 26 '12 at 16:20
1

DTF is the only option I'd bother with. Another question would be, why does it need to be a DataTable? Many years ago I wrote an ADO.NET provider for MSI that returned results as DataTable objects but now that DTF supports Linq to MSI I don't really see any use in it. – Christopher Painter Dec 26 '12 at 19:43

score 3 · Accepted Answer · answered Dec 26 '12 at 22:13

Here's similar logic using the interop types rather then the DTF types. As you can see it's a lot more work. It's also a lot more fragile since COM is involved instead of P/Invoke. Also, if you can eliminate the requirement to create an ADO.NET DataTable, DTF supports LINQ queries and databinding. In other words, I wrote this for fun only. I would NEVER use this method in real code.

 public static DataTable ReadMsiPropertyTable(string msiFile, string tableName)
        {
            DataTable dataTable = new DataTable(tableName);
            Type installerType = Type.GetTypeFromProgID("WindowsInstaller.Installer");
            Installer installer = (WindowsInstaller.Installer)Activator.CreateInstance(installerType);
            Database database = installer.OpenDatabase(msiFile, MsiOpenDatabaseMode.msiOpenDatabaseModeReadOnly);

            string sqlQuery = String.Format("SELECT * FROM {0}", tableName);
            View view = database.OpenView(sqlQuery);
            view.Execute(null);

            Record names = view.ColumnInfo[MsiColumnInfo.msiColumnInfoNames];
            Record types = view.ColumnInfo[MsiColumnInfo.msiColumnInfoTypes];
            Record row = view.Fetch();

            for (int index = 1; index < names.FieldCount+1; index++)
            {
                string columnName = names.get_StringData(index);
                string columnSpec = types.get_StringData(index);

                switch (columnSpec.Substring(0, 1).ToLower())
                {
                    case "s":
                        dataTable.Columns.Add(columnName, typeof(String));
                        break;

                    case "l":
                        dataTable.Columns.Add(columnName, typeof(String));
                        break;

                    case "i":
                        dataTable.Columns.Add(columnName, typeof(Int32));
                        break;

                    case "v":
                        dataTable.Columns.Add(columnName, typeof (Stream));
                        break;
                }
            }

            while (row != null)
            {
                DataRow dataRow = dataTable.NewRow();
                for (int index = 0; index < dataTable.Columns.Count; index++)
                {

                    if(dataTable.Columns[index].DataType == typeof(String))
                    {
                        dataRow[index] = row.StringData[index + 1];
                    }
                    else if(dataTable.Columns[index].DataType == typeof(Int32))
                    {
                        dataRow[index] = row.IntegerData[index + 1];
                    }
                    else if(dataTable.Columns[index].DataType == typeof(Stream))
                    {
                       // Insanity has it's limits. Not implemented.
                    }
                }
                dataTable.Rows.Add(dataRow);
                row = view.Fetch();
            }
            return dataTable;
        }

The comment "Insanity has its limits." deserves +5! Brilliant demo of the anti-pattern, Christopher! — Yan Sklyarenko, Dec 27 '12 at 09:23
Thanks, Yan. All one has to do is write a couple demos using WPF and Linq to MSI databinding to realize that writing an application like Orca is child's play these days. — Christopher Painter, Dec 27 '12 at 13:04

score 2 · Answer 3 · answered Dec 26 '12 at 20:32

2

Here's how you do it using DTF to create the types for your Database, View and Record objects. It would be similar using COM Interop but I have no desire to do things the hard way.

public static DataTable TableToDataTable( string msiPath, string tableName )
{
    DataTable dataTable = null;
    using(var database = new Database(msiPath, DatabaseOpenMode.ReadOnly))
    {
        using(var view = database.OpenView("SELECT * FROM `{0}`", tableName))
        {
            view.Execute();

            dataTable = new DataTable(tableName);
            foreach (var column in view.Columns)
            {
                dataTable.Columns.Add(column.Name, column.Type);
            }

            foreach (var record in view) using (record)
            {
                var row = dataTable.NewRow();
                foreach (var column in view.Columns)
                {
                    row[column.Name] = record[column.Name];
                }
                dataTable.Rows.Add(row);
            }
        }
    }
    return dataTable;
}

answered Dec 26 '12 at 20:32

Christopher Painter

54,556
6
63
100

i'm trying to do the same thing but I get: "The system cannot open the device or file specified. Database="Setup.msi"" – ShaneKm Jan 28 '13 at 14:58
You have to pass in a valid msiPath that exists and that you have access to. – Christopher Painter Jan 28 '13 at 15:07
I have this and it still fails: string path = @"c:\Temp\WixSqlDeployment\Setup\bin\Debug\Setup.msi"; db = new Database(path, DatabaseOpenMode.Direct); HOWEVER, when I change DatabaseOpenMode. TO ReadOnly it goes thru but then I can't update the table. – ShaneKm Jan 28 '13 at 15:08
see this for my full code: http://stackoverflow.com/questions/14562231/writing-data-to-msi-database – ShaneKm Jan 28 '13 at 15:11

How to convert WindowsInstaller.Record to System.Data.Dataset or Data Table (While reading MSI file) in C#

3 Answers3

Linked