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!
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).