[RESOLVED] Database for distribution with application

[gwboolean]

I have an SQL database that I built using SQL Server Express. I have an application for this database that I want to distribute. To do this, I want to locate the database in my project folder. I am unable to do this. I have looked for instructions on how to take this database and be able to locate and use it with the application and have failed to find much anything on this subject.

Is there anyone who can point me in the right direction as to how to figure this out?

I have done this, in the past with Access without any issues or roadblocks.

[jmcilhinney]

The steps to deploy a data file with your application are exactly the same regardless of the database. If you can do it with an Access MDB or ACCDB file then you can do it with a SQL Server MDF file. What steps did you follow to achieve this with an Access data file? When you followed the same steps with a SQL Server MDF data file, what exactly happened that you didn't want/expect?

[jdelano]

If you aren't including any data with your database, you can include a create database script, using the Generate and Publish Scripts Wizard, that you can run at first startup of your app. Have the user select the SQL Server that will house the data, once they do your app can execute the queries to create the database, tables, indexes, and any stored procedures.

[jmcilhinney]

If you aren't including any data with your database, you can include a create database script, using the Generate and Publish Scripts Wizard, that you can run at first startup of your app. Have the user select the SQL Server that will house the data, once they do your app can execute the queries to create the database, tables, indexes, and any stored procedures.

Based on the OP, that doesn't really apply. That will cause the SQL Server instance to create a permanently-attached database with data files in the data folder for that instance. The OP is talking about having a data file in the program folder, so that means deploying the MDF file with the application and attaching it to an instance at run time using "AttachDbFilename" in the connection string.

[gwboolean]

The steps to deploy a data file with your application are exactly the same regardless of the database

Not exactly. Yes, I can deploy an application using SQL. But what I want is to be able to deploy the database with the application.

The database must be contained in the deployment package, since the system that the application is being deployed to might not have a database and should not have to obtain a database to work with the application.
With Access, I can easily do this by just placing a copy of the database into the deployment package. I define the package as the project file for this discussion.
When I setup the distribution, I can include this database in the distribution such that when I put the application on another machine, the database goes with it and is part of the application.

I have tried numerous times and ways to include a SQL database (that is from SQL Server Express) and have never been able to do it. I have tried to find instructions on how this can be done with little success. I am game to do it if I can find some instructions for how to get the SQL database (that sits in SQL Server Express) and into the distribution package and on the target machine.

If you aren't including any data with your database

There is some data that will go with the database when distributed. There are tables with default settings information.

The OP is talking about having a data file in the program folder, so that means deploying the MDF file with the application and attaching it to an instance at run time using "AttachDbFilename" in the connection string.

That is true. The application and database have to go together to the target machine. Which brings me back to my issue. I have been unable to figure out how to get the database, or copy, that is in SQL Server Express out to the target machine as part of the distribution package.

I am sure that this is probably fairly easy to do, but I have been completely unsuccessful in finding any information that guides me through the process, assuming the process exists at all.

[wes4dbt]

What are you using to make the deployment package?

[OptionBase1]

Are you expecting that the destination device will work with your application and database even if SQL Express is not installed on the destination device?

[gwboolean]

Here is what I have. I have a database that I created and located within SQL Server Express. I am able to use this database with my vb application.

I now want to distribute this application and database to a target platform. To do this, I need to get a copy of this database, without SQL Server Express, into the distribution package that is used to load the application and database onto the target platform. I would assume that the database must, in some way, become free of SQL Server Express and be transitioned into an mdf file. I assume that because I have found no information that tells me how to take the database that is in SQL Server Express and distribute it.

What are you using to make the deployment package?

The deployment package is made using Visual Studio. There is a wizard for that, and at my level is all I know how to do this with.

Are you expecting that the destination device will work with your application and database even if SQL Express is not installed on the destination device?

Yes, I am. Without that capability the database would be of little use for this type of application.

[jmcilhinney]

It sounds like you've done little research on how SQL Server works. The reason that you can deploy an Access MDB file with an application and have it just work is because an MBD is not really an Access database at all. It's actually a Jet database and the Jet database engine is basically part of Windows. Access is an application that was created by Microsoft to manage Jet databases. When you connect to an MDB database using ADO.NET, you specify the OLE DB provider as "Microsoft.Jet.OLEDB.4.0" in the connection string and that OLE DB provider and the database engine itself are installed as part of Windows. If you use an ACCDB file, which actually is an Access database, then you need to use the ACE provider. That is NOT part of Windows, so your application will not work unless you have installed the ACE database engine and OLE DB provider, either with Office or as a standalone component. The database engine needs to be there and it already is in the case of Jet.

In the case of SQL Server, it is a server-based database system, so you have to install the server software in order for it to work. The data files are just data. Without the server, there's nothing to work with that data. When you use a SQL Server database in your app, you connect to the server and the server connects to the data file. This means that you need to have some edition of SQL Server installed either on the same machine as your app or on a network server accessible to that client machine. That might be full SQL Server (Standard, Enterprise, etc) or SQL Server Express or even just LocalDB, but you need SQL Server to use SQL Server.

When creating a SQL Server database for your app, you have two choices of approach. You can create an always-attached database or you can create a data file that gets attached on demand. The former will usually be done using SQL Server Management Studio (SSMS) while the latter is done in VS.

In the first case, you would create a database in SSMS and the server will create the appropriate data files where it is configured to do so. If you want to deploy such a database with an application then that is usually done either using a backup file or script files. To use a backup file, you would backup your existing database in your development environment and then create a database and restore it in the user's environment. The SQL Server instance you create the database on may be on the user's machine or a different machine on the same network. To use scripts, you can open the SQL files in SSMS and execute them against the desired instance or you could also run them in code via an installer. Again, the SQL Server instance may be on the same machine or a separate server.

If you want to deploy an actual MDF data file with your app then you should be adding an MDF data file to your project in the first place, not creating a database on an instance. You add a Service-based Database to your project like you do any other project item. Your connection string will then contain the name of a SQL Server instance to attach that data file to at run time - generally a LocalDB instance - and the path of the file is specified using AttachDbFilename. When you run your app, the data file is temporarily attached to the specified instance and then detached when your app exits and you can't access the database using SSMS. When you deploy your app, the MDF data file goes with it like any other file. The instance to which you attach such a file must be on the same machine as the app - it won't work with a network server. If the instance name is the same on the end user's machine as on your development machine then it will just work as is. Otehrwise, you will need to change the instance name in the connection string in the config file.

So, what do you need to do? You're trying to do it half one way and half the other, so that's never going to work. You need to decide which way you're going to do it and do it that way from go to woe. Firstly, you MUST have some edition of SQL Server installed on the end user's network for your app to work. If that's not possible then you simply can't use SQL Server. In that case, you should probably be using SQLite instead. If you're going to stick with SQL Server then it needs to be installed on the end user's machine or their network. It is possible to do that automatically as part of your installer. You should look into that yourself. As for the data, you need to decide whether you're using an always-attached database or attaching on demand. If the former, stop trying to find an MDF file and deploy the database using a backup or a script. If you do want to deploy an MDF file then stop using an always-attached database and add an MDF data file to your project and use that in your code. The code won't actually change but the connection string will.

[gwboolean]

It sounds like you've done little research on how SQL Server works.

Sure. That's what it is.

Anyway, I appreciate your information and like to thank you. That answers what I wanted to know. I think that for the kind of stuff I want to do, SQL Server is not the way for me to go for my database needs.

[jmcilhinney]

Sure. That's what it is.

SQL Server itself and how to code against SQL Server are not the same thing. If you don't know that SQL Server is a server-based database then it is indeed due to a lack of research on how SQL Server works. That it needs a server is pretty fundamental - possibly the most fundamental point - to how it works. You always seem to bristle whenever anyone criticises you. That will slow down the learning process because it means that you will ignore useful advice. If someone points out something that you've done wrong, it isn't an accusation of being a bad person. It's just pointing out something that you have done wrong. If you had researched SQL server better then you might have wasted less time trying to use it, only to have to switch to a different database. Acknowledging that might help you avoid the same mistake in future. I'd think that that would be something you would want but maybe not.

[wes4dbt]

The VS ClickOnce installer your using can install SQL Server Express for you if it's not on the target machine. You need to add it as a prerequisite. I haven't done it for a long time but it shouldn't be hard to find an example.