Careful what you wish for...
Last time around, I wrote an msbuild-version of WiRunSql.vbs, with one notable exception: WiRunSql.vbs will actually do something meaningful with SELECT staements (other than swallowing them). I implied that I was going to do something about that. Well, no time like the present.
Let's start off by seeing what WiRunSql.vbs does with SELECT stataments. You can see from the examples, that it is supposed to print data. For example, running:
cscript wirunsql.vbs SetupPasswordInput\Debug\SetupPasswordInput.msi "SELECT `Control`, `Type` FROM `Control` WHERE `Dialog_`='CustomTextA'"
from the password input example from last time results in:
CancelButton PushButton
BannerBmp Bitmap
PreviousButton PushButton
BannerText Text
BodyText Text
NextButton PushButton
Label1 Text
Edit4 Edit
Label4 Text
Edit3 Edit
Label3 Text
Edit2 Edit
Label2 Text
Control273 Line
Edit1 Edit
Control278 Line
That's pretty neat. What happens with integer data? Let's add the `Attributes` column as well to find out:
cscript wirunsql.vbs SetupPasswordInput\Debug\SetupPasswordInput.msi "SELECT `Control`, `Type`, `Attributes` FROM `Control` WHERE `Dialog_`='CustomTextA'"
CancelButton PushButton 3
BannerBmp Bitmap 1
PreviousButton PushButton 3
BannerText Text 65539
BodyText Text 3
NextButton PushButton 3
Label1 Text 3
Edit4 Edit 6
Label4 Text 2
Edit3 Edit 6
Label3 Text 2
Edit2 Edit 2097159
Label2 Text 3
Control273 Line 1
Edit1 Edit 7
Control278 Line 1
Also neat. Let's see if we can do something similar with our MSBuild tasks.
Let's think about what we would want a select task to look like. I can think of 2 options right off the bat:
- A task which takes in a SQL statement and spits out (somehow) the found records
- A task which takes a table name, column names, and maybe a where clause and (somehow) spits out the found records
Sound familiar? Option 1 resembles ExecuteSql, while option 2 looks like ModifyTableData. I maintain the usability of ModifyTableData is easier, so I'm going to stick with that one.
Next question: How to spit out the records? Let's have the task return an array of task items. We'll add metadata to our output item: the key will be the column name, and the value will be the value in the MSI. Sounds easy enough. Let's stub this out:
public class Select : SetupProjectTask
{
protected string tableName;
protected string[] columns;
protected string where;
[Required]
public string TableName
{
get { return tableName; }
set { tableName = value; }
}
[Required]
public string[] Columns
{
get { return columns; }
set { columns = value; }
}
public string Where
{
get { return where; }
set { where = value; }
}
[Output]
public ITaskItem[] Records
{
get { return null; }
set { }
}
protected MsiOpenDatabaseMode Mode
{
get { return MsiOpenDatabaseMode.msiOpenDatabaseModeReadOnly; }
}
protected override bool ExecuteTask()
{
return true;
}
}
There are many similarities here between ModifyTableData and the parameteres here for select. The differences are subtle, but they are there.
- TableName Okay, there was no change here
- Columns Note: this is plural, whereas in ModifyTableData, it was singular. This is because we were only allowed to modify one column, but it makes sense we would want to select several columns. This results in a string []. How do we deal with that in MSBuild? We'll find out in a second.
- Where Pretty much the same, except this was a required parameter in ModifyTableData
- Records This is a new output parameter
- Mode We weren't explicit about this before, but ModifyTableData uses the default value of msiOpenDatabaseModeTransact, which allows for modifications. With Select, there is no need to modify, so we'll just open for read-only access.
Let's see this thing in action. First, let's update our SetupPasswordInput post-build step to execute the select clause we ran through WiRunSql.vbs:
<Project xmlns="https://schemas.microsoft.com/developer/msbuild/2003">
<UsingTask TaskName="ModifyTableData" AssemblyFile="$(MSBuildExtensionsPath)\SetupProjects.Tasks.dll" />
<UsingTask TaskName="Select" AssemblyFile="$(MSBuildExtensionsPath)\SetupProjects.Tasks.dll" />
<Target Name="PostBuild">
<ModifyTableData
MsiFileName="$(BuiltOutputPath)"
TableName="Control"
ColumnName="Attributes"
Value="2097159"
Where="`Dialog_`='CustomTextA' AND `Control`='Edit2'"
/>
<Select<br> MsiFileName="$(BuiltOutputPath)"<br> TableName="Control" <br> Columns="Control;Type;Attributes" <br> Where="`Dialog_`='CustomTextA'"><br> <Output TaskParameter="Records" ItemName="CustomTextARecords" /><br> </Select><br> <Message Text = "%(CustomTextARecords.Control) %(CustomTextARecords.Type) %(CustomTextARecords.Attributes)" />
</Target>
</Project>
Things to note here:
- The "Columns" property value is a semicolon-delimited string which is supposed to map into a string []. I couldn't find an explicit statement of this, but was only able to find it by looking at the MSBuild task.
- We have an Output item. The results will be stored in an Item called CustomTextARecords
- We'll use the Message task to display the 3 columns we care about
The game plan for the task is pretty straight forward:
- Create a SQL statement based on our input parameters
- Open a view based on that statement
- For each record found, create a TaskItem and populate the metadata based on the columns selected
- Append each TaskItem to an output list
A lot of this we've done before; we will look at the FindExternalCabs task for a lot of our inspiration.
using System;
using System.Collections.Generic;<br>using System.Text;
using Microsoft.Build.Framework;
using Microsoft.Build.Utilities;
using WindowsInstaller;
namespace SetupProjects.Tasks
{
public class Select : SetupProjectTask
{
protected string tableName;
protected string[] columns;
protected string where;
protected List records = new List();
[Required]
public string TableName
{
get { return tableName; }
set { tableName = value; }
}
[Required]
public string[] Columns
{
get { return columns; }
set { columns = value; }
}
public string Where
{
get { return where; }
set { where = value; }
}
[Output]<br> public ITaskItem[] Records<br> {<br> get { return records.ToArray(); }<br> set { }<br> }
protected MsiOpenDatabaseMode Mode
{
get { return MsiOpenDatabaseMode.msiOpenDatabaseModeReadOnly; }
}
protected override bool ExecuteTask()<br> {<br> string sql = CreateSelectStatement();<br> View view = Msi.OpenView(sql);<br> view.Execute(null);<br> Record record = view.Fetch();<br> while (record != null)<br> {<br> ITaskItem item = new TaskItem(record.get_StringData(1));<br> for (int i = 0; i < Columns.Length; i++)<br> {<br> item.SetMetadata(Columns[i], record.get_StringData(i + 1));<br> }<br> records.Add(item);<br> record = view.Fetch();<br> }<br> view.Close();<br> System.Runtime.InteropServices.Marshal.FinalReleaseComObject(view);<br> return true;<br> }<br> private string CreateSelectStatement()<br> {<br> StringBuilder sql = new StringBuilder("SELECT");<br> for (int i = 0; i < Columns.Length; i++)<br> {<br> sql.Append(" `");<br> sql.Append(Columns[i]);<br> sql.Append("`");<br> if (i != Columns.Length - 1)<br> {<br> sql.Append(",");<br> }<br> }<br> sql.Append(" FROM `");<br> sql.Append(TableName);<br> sql.Append("`");<br> if (!string.IsNullOrEmpty(Where))<br> {<br> sql.Append(" WHERE ");<br> sql.Append(Where);<br> }<br> return sql.ToString();<br> }
}
}
There were 3 core additions to this task.
- The Records property now returns the member variable, records, which is filled as part of the ExecuteTask call
- The task basically executes as I had originally laid it out: create a SQL statement and add all found records to a list of ITaskItems. Each column found is a piece of metadata, whose value is the value in the column
- A helper method to create the SELECT statement, because I felt that function was complicated enough to warrant breaking out
One other design decision I made: the required piece of data (the "Identity" or "included") for every item we are returning is simply the first column found. I wasn't sure what the best approach would be for this value. I kicked a couple of ideas around for what I really wanted to do here, and came up with 3 options I liked:
- The select statement I used execute the query
- The Table name
- A vector containing the names of all the columns found
I thought all 3 of these options could be useful, and I especially wanted to use the third as a means to automatically determine what to print out in my message. Unfortunately, I couldn't immediately see how to do this, so I went with none of them.
Let's build our SetupPasswordInput project and see what the post-build gives us:
Project "E:\MWadeBlog\SetupPasswordInput\PostBuild.proj" (PostBuild target(s)):
Target PostBuild:
CancelButton PushButton 3
BannerBmp Bitmap 1
PreviousButton PushButton 3
BannerText Text 65539
BodyText Text 3
NextButton PushButton 3
Label1 Text 3
Edit4 Edit 6
Label4 Text 2
Edit3 Edit 6
Label3 Text 2
Edit2 Edit 2097159
Label2 Text 3
Control273 Line 1
Edit1 Edit 7
Control278 Line 1
Build succeeded.
0 Warning(s)
0 Error(s)
Time Elapsed 00:00:00.31
Post-build events finished
========== Build: 1 succeeded or up-to-date, 0 failed, 0 skipped ==========
As we can see, we ended up with output similar to what we got when using WiRunSql.vbs.