0

I'm very new to creating installers. This application needs to connect to a SQL Server database for it to be used. The tables of that database has rows which are needed by the application, so just reconstructing the database structure is not what is needed.

Can I just export the database as a .mdf file and connect from there?

Or do I need to install SQL Server Express on those computer as a prerequisite and attach the database from there? If so, how do I attach it on installation?

Or are there other ways to include a SQL Server database to an installer and be used by the installed app?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Gerald Torres
  • 403
  • 6
  • 18

1 Answers1

1

On the target machines you need to install SqlServer (express or standard).
You don't need any management application like Sql Server Management Studio.
Then you can attach your MDF to the database engine via connection string as this one

Server=.\SQLExpress;AttachDbFilename=c:\yourfolder\yourfile.mdf;Database=yourdatabase; Trusted_Connection=Yes;

The installation of SqlServer is not an easy task to do via custom installers.
If your application is not intended to be used outside of the local machine you could use LocalDB.
The install of this flavor of sqlserver is more easy.

Other alternative is:
Extract a sql script that rebuilds your database. (and provide a method to execute the script)
Export the data needed and execute a bulk insert to reinsert the data in the rebuilded database

Community
  • 1
  • 1
Steve
  • 213,761
  • 22
  • 232
  • 286
  • So only SQL Express is needed... How and when can I attach the .mdf file to the installed SQL Express? – Gerald Torres May 06 '12 at 17:04
  • 1
    That's the work of the connectionstring. Usually it has been saved in your app.config, this file is distributed with your app and will be read during startup, Then every call to methods that require database access could access the info and build the SqlConnection needed to open the database. – Steve May 06 '12 at 17:08
  • There's an error: "Directory lookup for the file ... failed with the operating system error 5(Access is denied.). Could not attach file ... as database DATABASENAME." I used `Server=.\SQLExpress;AttachDbFilename=|DataDirectory|db.mdf;Database=db; Trusted_Connection=Yes; MultipleActiveResultSets=True` as my connection string – Gerald Torres May 06 '12 at 18:13
  • Is this an asp.net application? – Steve May 06 '12 at 18:25
  • a c# windows forms application. some sites say there are permission problems with the `.mdf` file and the directories. I'm currently using XP, there are no security tabs on the Folder Properties, the `.mdf` file, `.ldf` file and the directories are not read-only. – Gerald Torres May 06 '12 at 18:33
  • 1
    `|DataDirectory|` is a shortcut way to set the relative path inside a website where a database is located. It's provided by the ASP.NET/IIS duo (C# or VB.NET doesn't matter here). Can't work on a standard WinForms application. In this case you need a physical path. – Steve May 06 '12 at 18:37
  • Thanks :) It's working when the `.mdf` file is in the `C:\Program Files\Microsoft SQL Server ... Data` folder, but has an error on other directories: Database 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\db.mdf' already exists. Could not attach file 'C:\db.mdf' as database 'db'. Also, I checked the ..MSSQL\Data Directory has no db.mdf – Gerald Torres May 06 '12 at 18:54
  • let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/10939/discussion-between-gerald-torres-and-steve) – Gerald Torres May 06 '12 at 19:04