How to deploy or publish Windows app with SQL Server database

Question

I have developed a C# application with a SQL Server database. When I try to publish the application to test it >> it works correctly on my machine (that include the database in SQL Server data folder) >> but when I move that published app to another machine it doesn't work.

I want to know the simplest way to deploy the project with its database together

I am using this connection string in my app.config:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <connectionStrings>
    <add name="cs" 
         providerName="System.Data.ProviderName" 
         connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Data\KBank.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True" />
  </connectionStrings>
</configuration>

What should I do in order to place the database with the deployed app and dynamically modify the connection string for the database in app.config?

the exception shown when i open the project on the users machines says:

An Attempet to Attach an auto-named database for file C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Data\KBank.mdf Failed, a database with the same name exists or specified file cannot be opened, or its location on UNC share

it seems it is unable to read the SQL DataBase

Solution 1

I have resolved my problem > the problem was that i didn't add a Local Database to my project solution.. so when i try the project on another machine the application couldn't find the specified connection sting that have the path:

C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Data\KBank.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True

but when i added the local db to my project solution the connection string path became :

|DataDirectory|\KBank.mdf

it has been attached with the project files successfully thank you

Solution 2

Your application can not find the database file that is specified in the connection string talk less of connection to it. This is because the database is auto-named rather than a data file. All you need to do is to make the database an attached data file to the package so that when deployed the database will go along with it. To do this, on visual studio IDE, on the publish tab chose "Application Files" change the Publish status of the database file from "Include Auto" to "Data File", then validate. This will add the database file to ur application upon publish and deployment. To access project property, 1)With Visual studio 2010/2012 - Click Project Menu -> Properties -->

I hope this helps u. If not i'll try to explain again. Stay Blessed

Comments

[Glenn Ferrie]

can you clarify 'doesn't work'? Was there an exception?

[dougajmcdonald]

Does the target / test machine have SQL server express installed? And an instance called 'sqlexpress'?

[Hassanation]

@dougajmcdonald No , i just want it to work on any machine that either have or doesn't have the SQL server

[Hassanation]

@GlennFerrieLive I mean it seems it is unable to read the SQL DataBase the exception says: An Attempet to Attach an auto-named database for file C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Data\KBank.mdf Failed, a database with the same name exists or specified file cannot be opened, or its location on UNC share

[Glenn Ferrie]

Interesting. I'll see if I can get you a response, will try to re-pro

[Hassanation]

@GlennFerrieLive thank you, but i have got a solution for my problem you can look at the answer i have posted >> thank you