I am working on a Winforms project, and I am just setting up the setup file for the installation of the program.
Scenario
The connection string I have set for my use is like this:
SqlConnection con = new SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=F:\Application\MyAppv1.0\MyApp\DB.mdf;Integrated Security=True");
return con;
Here, I use local DB from my personal location.
Question
On installation on the client's PC, the database is also attached, so the installed files will be on C:\Program Files\Author\MyAppv1.0
. When I use the same connection string, the app shows an exception. So, should I update my connection string for that location during the setup creation? (I am using MSI).
Is there any commonly followed approach for this scenario?
My aim is to make the installed app use the DB that is also on the same location of installation named DB.mdf
and is provided with setup.
Thanks guys in advance.
CodePudding user response:
Do not use AttachDbFilename
, it has many issues. Instead, attach your database normally using CREATE DATABASE...FOR ATTACH
.
At startup of your app, you can connect to the server using master
as the current DB, and check for existence of your DB. If it's not there you can create it.
private static void CheckDbExistence(string connectionString)
{
const string query = @"
IF NOT EXISTS (SELECT 1
FROM sys.databases
WHERE name = 'MyDb')
BEGIN
CREATE DATABASE MyDb ON Primary
FOR ATTACH ON
(FILENAME = @mdf),
(FILENAME = @ldf);
END;
";
var csb = new SqlConnectionStringBuilder(connectionString);
csb.Initial Catalog = "master";
using (var conn = new SqlConnection(csb.ConnectionString))
using (var comm = new SqlCommand(query, conn))
{
comm.Parameters.Add("@mdf", SqlDbType.NVarChar, 255).Value = Path.Combine(Environment.CurrentDirectory, "MyDb.mdf");
comm.Parameters.Add("@ldf", SqlDbType.NVarChar, 255).Value = Path.Combine(Environment.CurrentDirectory, "MyDb.ldf");
conn.Open();
comm.ExecuteNonQuery();
}
}