How to save the SQL ODBC connection string in my application config file?
The following article uses options that are available starting
with the Enterprise edition and project
type.
Let's suppose you need to configure a setup package that will install SQL Server Express 2014 x86, test the SQL ODBC connection string and save the connection string into your application configuration file. This scenario can be fulfilled by taking advantage of the Advanced Installer built-in support for Prerequisites, Test SQL Connection and XML Updates.
Here are the steps you should follow to achieve this:
1. Go to the Prerequisites page and add our "SQL Server Express 2014 x86" predefined prerequisite in your setup project.
2. In the Setup Files tab configure a silent command line for the SQL Server prerequisite. For instance, you can use a command line like this:
/q /IACCEPTSQLSERVERLICENSETERMS /ACTION=Install /FEATURES=SQLEngine /INSTANCENAME=SQLEXPRESS /SECURITYMODE=SQL /SAPWD=sa_password /UpdateEnabled=False
3. Go to the Custom Actions page and add our "Test ODBC Connection" custom action to your setup project.
4. Go to the Dialogs page and, using the "Add Dialog..." context menu option, add the "SQLConnectionDlg" built-in dialog under the "First Time Install" dialogs chain.
5. Set the default values for the edit box controls accordingly with the command line values of the SQL Server prerequisite. Here is an example:
| Property Name | Default Value |
|---|---|
| SERVER_PROP | (local)\SQLEXPRESS |
| DATABASE_PROP | master |
| USERNAME_PROP | sa |
| PASSWORD_PROP | sa_password |
6. On the button prepend the following published events
- Control Event: Set installer property value
- Property: SqlConnectionString
- Argument: Driver=[\{]SQL Server[\}];Server=[SERVER_PROP];Uid=[USERNAME_PROP];Pwd=[PASSWORD_PROP];Database=[DATABASE_PROP];
- Condition: 1
- Control Event: Execute custom action
- Argument: TestODBCConnection
- Condition: NOT (SqlConnectionString = "")
7. Go to Files and Folders page and import your application configuration file in Windows Installer XML format.
8. Edit the "Value" field of your connectionString XML attribute to use the formatted references of the SQL connection string properties (i.e. SERVER_PROP, USERNAME_PROP, PASSWORD_PROP, etc).
9. Build and run your project.