build connection string at runtime and save the same in application setting on c# windows app

Question

I am building a C# windows application using sql server 2005 and visual studio 2008.
To deploy the application on clients computer. I do this this in this order

1. I install the sql server 2005 with the same user/pass (ie sa/pass) i made in my app.
2. I run my windows application msi file. It install my databasae in sql server 2005.

when I try to run the application I fail due to different machine name and sql server instance name on my clients computer. i am getting error in connection string. i need a way to extract the target computername and sql server instance and save the same in my application setting during deployment (using in my c# code so that i can make a connection string at runtime.)

To resolve the problem what i am doing is I am installing visual studio on my client's computer and editing the source code to change the machine name and sql server instance. Then my windows application runs. I don't want to resolve my problem this way but rather need a way where i can set my parameters at deployment time or leave it up to you to suggest a better way.

I would request you to help me.

Answer 1

Google will give you lot of options. Have you followed the right steps while copying a database.

Follow these links http://blogs.msdn.com/b/sreekarm/archive/2009/09/11/move-a-database-from-one-server-to-another-server-in-sql-server-2008.aspx

and this will answer yours DBA Stack Exchange

Also post the Exact error message you get.It ll be useful to figure out the issue

Answer 2

Using SQL CE will require a lot of rework.

The full install order is:

1. Install Windows Installer 3.1 (if its not installed)
2. Install .Net 2.0 (if its not installed)
3. Check if SQL is NOT already installed:

HKLM "SOFTWARE\Microsoft\Microsoft SQL Server\${instance_name}\MSSQLServer\CurrentVersion" "CurrentVersion"

Then you need to install SQL Server:

${installer_path}\SQLEXPR.exe -q /norebootchk /qn reboot=ReallySuppress addlocal=all INSTANCENAME=${instance_name} SAPWD=${password} SECURITYMODE=SQL SQLBROWSERAUTOSTART=1 SQLAUTOSTART=1 AGTAUTOSTART=1 ASAUTOSTART=0 RSAUTOSTART=0 DISABLENETWORKPROTOCOLS=0 ERRORREPORTING=1 SQMREPORTING=0 ENABLERANU=0 ADDUSERASADMIN=1

To connect to the SQL Server instance use your data link properties dialog. You will need to give clear instruction to your users how to connect to the instance. eg

If you use the .\Instance_Name syntax where the "." dot represents the client PCs name that should solve the problem. Also use this in your connection string.

Answer 3

So, if I understand you correctly, you have hardcoded your connection string inside your source code. And of course, you have discovered at your expense how this is really a bad idea.

You need to have that connection string saved in your app.config file. In this way, when you install your application to your customer, you need only to change the config file and your code is ready to go.

To insert your connection string in your app.config:

• right click on your project and select Properties, then go to the Settings page. This will create a settings.settings file for your project and will open a GUI editor for your properties.
• Now insert a string in the column name (for example testConnection)
• Select from the Type column the ConnectionString type
• Insert, in the value column, a connection string valid for your development machine

Now if you open the app.config you will notice a new section called ConnectionStrings that will look like this:

 <connectionStrings>
    <add name="ProjectName.Properties.Settings.testConnection" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\temp\mytemp.mdb;User Id=admin;Password=;" />
  </connectionStrings>

Now it is time to fix your code once and forever.
In every place where you have an hardcoded connection string replace that code with something like this

 string constring = ConfigurationManager.ConnectionStrings["ProjectName.Properties.Settings.testConnection].ConnectionString;

And while we are here, check if the LocalDB version of SqlServer Express 2012 could be used to remove also the need to install SqlServer.