Windows Installer – SQL Server Installation Issues and a Solution.

Here is the problem in a nutshell: You have a custom client application that uses SQL Server 2005 Express. The server installation is a little bit to be desired. First, you have to install SQL Server Express, then you have to configure the options (unless you did command line options), then you have to execute the scripts to create your database, tables, and possibly populate it with some data. Or conversely, execute scripts or install and use the management console to attach a database. Messy, at best.

So what if you want to have a single installation program that will install all the prerequisites (.Net), then install SQL Server 2005 with your custom options (instance name for example), and then have it execute scripts and fill it with data? After working at it for hours, I came to the conclusion that you could be out of luck. Until I did some reconfiguring.

First things first, you need to get SQL Server Express to install properly. I struggled with this for a while, until I had the bright idea of using a bootstrapper… But can’t we use the one that comes with the Visual Studio Package and Deployment Wizard? Yes you can!

Go into your Visual Studio 8 folder and find the SDK/v2.0/Bootstrapper/Packages folder. Make a copy of the SqlExpress folder and name it something like, MyAppSqlExpress. Inside that folder, there is a product XML file. Edit that product.xml file and change the product code. Mine was Microsoft.Sql.Server.Express.1.0 and I changed it to MyAppName.Microsoft.Sql.Server.Express.1.0 … Just change the MyAppName to the app name of the product it goes with. You will also see a En folder for the English installation. The package.xml file is the one you want to edit in that folder. The first item to edit, is the arguments line. This passes arguments to the SqlExpr32.exe file (the setup for Sql Express). The arguments are the same as any other SQL Express install… So change it to be something like this:

Arguments='-q /norebootchk /qb reboot=ReallySuppress addlocal=all 
  INSTANCENAME=MyApp SECURITYMODE=SQL SAPWD=MyStrongPassword 
  DISABLENETWORKPROTOCOLS=0 SQLAUTOSTART=1'

This will do a silent installation of SQL Server Express using all protocols, a custom instance name (MyApp), turn on the SQL security mode and specify a strong SA password.

Next, there is the strings block at the bottom. Edit the StringName “DisplayName” to look like this:

MyApp SQL Server 2005 Express Edition

Save it, and go into your setup and deployment project you created, go to the project properties, then to the prerequisites, and in the list you should now see your custom install of SQL Server Express!

This does work, I just tested it out today.

So that solves problem #1. What about the problem of executing scripts? That seems to be the easy part… From my earlier post you can incorporate that code into a custom action.

Create a new project, and select Installer Class from the templates. This creates a new installer class for you to augment.

  Private Sub ExecuteSQL(ByVal SQL As String, _
    ByVal ConnectionString As String)
    
    Dim srv As New Server(New ServerConnection( _
    New SqlClient.SqlConnection(ConnectionString)))
    
    srv.ConnectionContext.ExecuteNonQuery(SQL)
    srv.ConnectionContext.Disconnect()
  End Sub

  Private Sub BulkLoad(ByVal ConnectionString As String, _
    ByVal TableName As String)
    
    Dim bulk As New SqlClient.SqlBulkCopy(ConnectionString, _
    SqlClient.SqlBulkCopyOptions.KeepIdentity)
    
    Dim ds As New DataSet
    Dim path As String = My.Application.Info.DirectoryPath
    ds.ReadXmlSchema(path & "\" & TableName & ".xsd")
    ds.ReadXml(path & "\" & TableName & ".xml")
    bulk.BulkCopyTimeout = 0
    bulk.DestinationTableName = TableName
    bulk.WriteToServer(ds.Tables(TableName))
    bulk.Close()
  End Sub

  Public Overrides Sub Install(ByVal stateSaver As _
    System.Collections.IDictionary)
    
    MyBase.Install(stateSaver)
    Dim ConnBuilder As New SqlClient.SqlConnectionStringBuilder()
    ConnBuilder.UserID = "sa"
    ConnBuilder.Password = "MyStrongPassword"
    ConnBuilder.DataSource = "localhost\MyApp"
    ConnBuilder.InitialCatalog = "master"
    ExecuteSQL(My.Resources.CreateDatabaseSQL, _
    ConnBuilder.ConnectionString())
    
    ConnBuilder.InitialCatalog = "MyDatabase"
    BulkLoad(ConnBuilder.ConnectionString(), "MyTable")
  End Sub

In this example, I just stored the data files (an XML data file and an XSD schema file) in the setup project, which gets installed along with the custom installer DLL. The create database SQL stuff was all saved as a store resource in the installer class project. I did this so that some of the structure was hidden, which may or may not be an issue for others.

Now, add the custom action to a setup project and you are finished!

What is the future of COM?

The discussion came up the other day about COM (Component Object Model) and it’s future in Windows development.

COM is not dead, by any means. In fact, Microsoft said it themselves in the Windows Vista FAQ. It’s not dead, just done. By done, they mean that it will no longer be extended, or fixed if a problem is found. I am 100% sure that if a security flaw is found, they will fix it. However, I am also 100% sure that if an obscure bug is found, and it doesn’t affect any Microsoft Products, it will not be fixed.

So what does that mean for the Windows Developer? Not much. COM is still a major component in Windows, and probably will have to be for quite some time. Microsoft Office and Internet Explorer are still COM applications. Do you think they will be rewriting them in .Net any time soon? Probably not.

I don’t think the sky is falling, but I do think it’s time for existing developers to start migrating away from COM, though.

Microsoft.SqlServer.Smo, where have you been all my life?

I have had a need, for quite some time, to have a comprehensive SQL Server tool where I could execute scripts, import/export tables, and generally just run a few queries. In the past, I have been using the free SQL Management Studio Express edition, which works just fine. The problem with this utility, is using it at customer installations. The install requires administrator privileges, which work for most places, but make it impossible to install at others. So, the need to have a small utility that I can run along side my application is pretty great.

I started out just using the SQLClient name space. While it worked well for simple queries, trying to run a comprehensive database update script just killed it. While it would probably work, I would have to parse the script first, stripping out all the GO statements and breaking them up into separate scripts. That worked fine until I ran into the first transaction… Not good either, apparently.

Feeling dead in the water, I did some searching and came across a name space I had not known existed. It’s Microsoft.SqlServer.Smo. It is the replacement for Nmo and has quite a few really nice features.

One such feature is the ability to use .ExecuteNonQuery() without having to break apart or parse existing update scripts, including ones with built in transaction support!

It’s pretty simple to use and combined with the SqlClient name space, I was able to create a pretty comprehensive solution for my needs.

Imports Microsoft.SqlServer.Management.Smo
Imports Microsoft.SqlServer.Management.Common
Dim conn As New ServerConnection(SQLAuthInfo.GetSQLConnection())
Dim srv As New Server(conn)
dropdown.Items.Clear()
For i As Integer = 0 To srv.Databases.Count - 1
  dropdown.Items.Add(srv.Databases.Item(i).Name)
Next
conn.Disconnect()

That’s as simple as it gets to iterate through all the databases on the server. The SQLAuthInfo is a class I designed that will just create a SqlConnection object based on preset values.

Need to execute a script? No problem…

Dim conn As New ServerConnection(SQLAuthInfo.GetSQLConnection())
Dim srv As New Server(conn)
Dim cmd As String = ScriptMemoEdit.Text
 
srv.ConnectionContext.ExecuteNonQuery(cmd, ExecutionTypes.ContinueOnError)
 
conn.Disconnect()

It didn’t take me long to use many of the features… For import and export, I just used a select query to get all the data from one table and saved it into a dataset. Then I used the dataset’s WriteXML and WriteXMLSchema functions to save it to files. Then with the import function, I just read in the schema and data into a dataset and used the SqlBulkCopy object to save it to the target database table. Pretty easy stuff. This also gives me some new options for our server installation program I need to fix (i.e. create).

XML Parsing in .Net

While working on wxDesktop, which was my first attempt at parsing data from an XML source, I tried to find the best way to read and parse out certain data from an XML document. Before I even really got to the meat of it, I had just had it in my head that I was going to use a DataSet. Why would I do that, you ask? Well, when that’s all you know how to do, that’s usually what you go with. I have never dealt with the XML namespace, mostly because I haven’t had to. Now that I have, I know I will never use the crappy DataSet class for this ever again.

Take the following XML document.

<test>
    <data>
      <name>Sample set name</name>
      <value>5</value>
      <value>4</value>
      <value>6</value>
      <value>1</value>
      <value>20</value>
      <value>54</value>
      <value>10</value>
      <value>19</value>
      <value>17</value>
      <value>15</value>
   </data>
   <otherdata>
      <name>Second Sample set name</name>
      <value>10</value>
      <value>12</value>
      <value>14</value>
      <value>2</value>
      <value>4</value>
      <value>6</value>
      <value>8</value>
      <value>16</value>
      <value>18</value>
      <value>20</value>
   </otherdata>
</test>

When you read this document in using the DataSet.ReadXML function like this:

private void readXMLDataSet(string FileName)
{
    DataSet ds = new DataSet();
    ds.ReadXml("test.xml");
}

The DataSet will contain 3 tables. It will contain the tables called data, value, and otherdata. Not really what you would think would be in there… It sort of makes sense, except for the fact that all value columns are then lumped into one table and then linked via an ID. While this isn’t too bad, and it would probably be usable, it’s not ideal, nor fun to use.

Now take this piece of code:

private void readXMLNodes(string FileName)
{
    System.Xml.XmlDocument xd = new System.Xml.XmlDocument();
    xd.Load("test.xml");
 
    System.Xml.XmlNode node = xd.SelectSingleNode("/test/data");
    System.Xml.XmlNode nameNode = node.SelectSingleNode("name");
    System.Xml.XmlNodeList valueList = node.SelectNodes("value");
}

This uses the XmlDocument, XmlNode, and XmlNodeList classes and allows you to easily parse out certain parts of the XML document to find exactly what you need. nameNode.InnerText will show “Sample set name” and the valueList will contain every value in the data section. So valueList[0].InnerText will be 5.

Now, you could shove this information into a correctly laid out DataSet, but why bother? Now I see the value in creating a class just to parse certain XML documents. You can set the class up so that it will match the structure as it should be logically laid out and use the class to just access the nodes as needed through functions.

Next Page →