include sql db within package and install on a standalone system with no sql server

[sid]

Hi,
i have develop an window application using **VB.NET** and** SQL SERVER2008R2**.I have Sql server2008R2 installed on my system containing sql database file.I have my connectionstring in app.config file as:-

<appSettings>

                                  <add key="mycon" value="Data Source=My-pc;Initial Catalog=MovieCatalog;User ID=sa;Password=pwd123" />
                                  <add key="ClientSettingsProvider.ServiceUri" value="" />
                  </appSettings>

Now what i want to do is to make setup of my project.My problem is that how to include all files(including sql database file) or what to do to make setup or package.
Secondly,this is single user application with no LAN or network etc., so is it necessary to install sql server on different PC before installing this package?
I mean how can i include my database file within package(so that my connectionstring work properly) without even installing sql server on user PC or tell me what to do rightly to make appropriate package.

 

[jmcilhinney]

If you want to use SQL Server then you have to have SQL Server installed somewhere, plain and simple. You have two choices with SQL Server:

1. You can connect to a database that is permanently attached to a SQL Server instance.
2. You can attach a database file to a SQL Server instance on demand.

You are currently using option 1. If you want to continue using option 1 then your user will need SQL Server or SQL Server Express installed on either their own machine or another machine on their network. The database will also have to be created/attached on that instance. If you want to use option 2 then your user will need to have SQL Server Express installed on their local machine and you would deploy the database file (MDF) with your application.

If you don't want to require that SQL Server be installed then you can't use a SQL Server database. SQL Server is a server-based database and thus MDF data files are useless without a server installed. If you don't want to have to install a server then you must use a file-based database, e.g. Access, SQLite or SQL Server CE. Despite the name, SQL Server CE is a completely different product to SQL Server and SQL Server Express.

So, the first thing you need to do is decide what type of database you want to move forward with. Note that you can configure an installer created in VS to install SQL Server Express on the local machine simply by checking a box.

 

[sid]

If you want to use SQL Server then you have to have SQL Server installed somewhere, plain and simple. You have two choices with SQL Server:

1. You can connect to a database that is permanently attached to a SQL Server instance.
2. You can attach a database file to a SQL Server instance on demand.

You are currently using option 1. If you want to continue using option 1 then your user will need SQL Server or SQL Server Express installed on either their own machine or another machine on their network. The database will also have to be created/attached on that instance. If you want to use option 2 then your user will need to have SQL Server Express installed on their local machine and you would deploy the database file (MDF) with your application.

If you don't want to require that SQL Server be installed then you can't use a SQL Server database. SQL Server is a server-based database and thus MDF data files are useless without a server installed. If you don't want to have to install a server then you must use a file-based database, e.g. Access, SQLite or SQL Server CE. Despite the name, SQL Server CE is a completely different product to SQL Server and SQL Server Express.

So, the first thing you need to do is decide what type of database you want to move forward with. Note that you can configure an installer created in VS to install SQL Server Express on the local machine simply by checking a box.

Thanks for your valuable information....
OK, i would like to go with option 1...but how to include my database file and configure installer in VS to install SQL Server on local machine(and how to attach db with the instance of SQL Server on local machine)...in short how to create complete setup...
Reply will be highly appreciated..

 

[jmcilhinney]

You can configure your installer to install SQL Server Express automatically but not a full edition of SQL Server. That would be done separately by the user themselves. What installer technology are you using? Will it be ClickOnce (the VS Publish function), a VS Setup project, InstallShield LE or something else not built into VS?

 

[sid]

You can configure your installer to install SQL Server Express automatically but not a full edition of SQL Server. That would be done separately by the user themselves. What installer technology are you using? Will it be ClickOnce (the VS Publish function), a VS Setup project, InstallShield LE or something else not built into VS?

Well,i have not use any of them before.Which one would be better if install on a single user local machine.Does they all work the same?

 

[jmcilhinney]

No they don't all work the same. If you're using VB/VS Express then ClickOnce is the only in-built option. If you're using a paid-for edition of VS then you can use ClickOnce and either or both of Setup projects and InstallShield LE, depending on your version. You can use a third-party tool of your choice regardless of your VS edition, although any VS integration will most likely not work in an Express edition. It's for you to decide what you want to use.

 

[sid]

No they don't all work the same. If you're using VB/VS Express then ClickOnce is the only in-built option. If you're using a paid-for edition of VS then you can use ClickOnce and either or both of Setup projects and InstallShield LE, depending on your version. You can use a third-party tool of your choice regardless of your VS edition, although any VS integration will most likely not work in an Express edition. It's for you to decide what you want to use.

I am using VS2010(not paid) with SQL Server2008R2.

 

[jmcilhinney]

So... what deployment technology do you want to use? I've told you what's available. It's for you to choose. People can make recommendations but it's for you to choose. If you want to compare features then go ahead and compare.

 

[sid]

So... what deployment technology do you want to use? I've told you what's available. It's for you to choose. People can make recommendations but it's for you to choose. If you want to compare features then go ahead and compare.

Ok..What if i go for ClickOnce method but i have read somewhere that ClickOnce is use for deploying Web applications only.Does it work for Windows applications also?
If it is then how?

 

[jmcilhinney]

Ok..What if i go for ClickOnce method but i have read somewhere that ClickOnce is use for deploying Web applications only.Does it work for Windows applications also?
If it is then how?

What you've heard is completely incorrect. ClickOnce is specifically for Windows applications. It was created to enable deployment of Windows applications to be pretty much as easy as that of web applications. Most notably, you don't need to be an administrator to install an app and the app is updated automatically as new versions are deployed. It does have certain limitations though.

In your case, you can deploy SQL Server Express simply by checking the appropriate box in the Prerequisites dialogue. You can't execute arbitrary actions during setup though, so creation or attachment of an existing database would have to be done by the app at startup instead of during installation.

If you're going to use SQL Server Express locally though, do you need a permanently attached database? If only the app will access the database then you could just deploy the MDF data file with the app and have it attached on demand via the connection string. Only if you need to access it some other way, e.g. via Management Studio, would you need to manually attach the database.

 

[sid]

What you've heard is completely incorrect. ClickOnce is specifically for Windows applications. It was created to enable deployment of Windows applications to be pretty much as easy as that of web applications. Most notably, you don't need to be an administrator to install an app and the app is updated automatically as new versions are deployed. It does have certain limitations though.

In your case, you can deploy SQL Server Express simply by checking the appropriate box in the Prerequisites dialogue. You can't execute arbitrary actions during setup though, so creation or attachment of an existing database would have to be done by the app at startup instead of during installation.

If you're going to use SQL Server Express locally though, do you need a permanently attached database? If only the app will access the database then you could just deploy the MDF data file with the app and have it attached on demand via the connection string. Only if you need to access it some other way, e.g. via Management Studio, would you need to manually attach the database.

i have my connection string something like this:

<appSettings>
        <add key="mycon" value="Data Source=My-pc;Initial Catalog=MovieCatalog;User ID=sa;Password=pwd123" />
        <add key="ClientSettingsProvider.ServiceUri" value="" />
    </appSettings>

and i am calling it like this:

con.ConnectionString = System.Configuration.ConfigurationManager.AppSettings.Get("mycon

now tell me how can i change my connection string so that i can deploy the MDF file...When i searched, i found my MDF file somewhere in
C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\MovieCatalog.mdf alongwith log file.Is Log file need to be deploy along to?

 

[jmcilhinney]

If you want to deploy your data file with your app then you should add your data file to your app. That is not something that you do as part of the deployment process but as part of the development process, i.e. if you intend to attach on demand after deployment then attach on demand during development. That requires SQL Server Express and you can see ConnectionStrings.com for connection string info.

 

[sid]

If you want to deploy your data file with your app then you should add your data file to your app. That is not something that you do as part of the deployment process but as part of the development process, i.e. if you intend to attach on demand after deployment then attach on demand during development. That requires SQL Server Express and you can see ConnectionStrings.com for connection string info.

OK...Finally,i succeeded in making setup of my application using 'ClickOnce'.............but but...its working fine when install on my system but when i try to install it on client pc,i got error related to path of database file as i have defined db path in connectionstring(changed this time after searching a lot @ ConnectionStrings.com) according to my pc as below:

<appSettings>
        <add key="mycon" value="Data Source=.;Integrated Security=true;AttachDbFilename=C:\Users\Sid\Desktop\WatchUrStuff\WatchUrStuff\bin\Release\MovieCalalog.mdf;Database=dbname;Trusted_Connection=Yes" />
        <add key="ClientSettingsProvider.ServiceUri" value="" />
    </appSettings>

Now...What to do....How to provide a default path that work for all pc's???

 

[jmcilhinney]

You should not be using a hard-coded path for the data file. You should be using "|DataDirectory|" to represent the folder path. That will get resolved to the correct location during development and after deployment without your having to change anything. In a Windows application, it will resolve to the same folder as the EXE was run from during development and also, if you're not using ClickOnce, after deployment. If you do use ClickOnce then it will resolve to a special folder dedicated to data files. For web applications, it will resolve to the App_Data folder. The link I provided earlier provides examples that use "|DataDirectory|" but, basically, you simply substitute it for the folder path in the AttachDbFilename value.

 

[sid]

You should not be using a hard-coded path for the data file. You should be using "|DataDirectory|" to represent the folder path. That will get resolved to the correct location during development and after deployment without your having to change anything. In a Windows application, it will resolve to the same folder as the EXE was run from during development and also, if you're not using ClickOnce, after deployment. If you do use ClickOnce then it will resolve to a special folder dedicated to data files. For web applications, it will resolve to the App_Data folder. The link I provided earlier provides examples that use "|DataDirectory|" but, basically, you simply substitute it for the folder path in the AttachDbFilename value.

I tried out that too as;

<add key="mycon" value="Data Source=.;Integrated Security=true;AttachDbFilename=|DataDirectory|MovieCalalog.mdf;Database=dbname;Trusted_Connection=Yes" />

but i got an error message

Database'C:\Users\Sid\Desktop\WatchUrStuff\WatchUrStuff\bin\Release\MovieCalalog.mdf' already exists.Choose a different database name.
Cannot attach the file
'C:\Users\Sid\Desktop\WatchUrStuff\WatchUrStuff\bin\Debug\MovieCalalog.mdf' as database 'dbname'.

i tried to change 'dbname' to someother name but it doesn't work too.