Copy Visual Basic 2010 project with SQL database to another computer

[gs99]

I have a Visual Basic 2010 project with SQL database (2008 R2), detached from server.
The db was not copied into the project. It resides in the SQL directory.

I need to debug the app on this computer and another computer that has VB 2010 but SQL 2012. All are Express versions.

When I copy the whole project from one to the other computer, it doesn't work on the target until I delete the db and make a new connection.

Would it work better if I copied the db into the project?

 

[jmcilhinney]

What does your connection string look like? Are you using 'Initial Catalog' or 'AttachDbFilename'?

 

[gs99]

Data Source=.\SQLEXPRESS;AttachDbFilename="C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\MyFamily.mdf";Integrated Security=True;Connect

And I copied the two db files to the exact same folder in both computers:
C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\
Windows 7

 

[jmcilhinney]

You should absolutely not have put the data file in the SQL Server data folder. That's for databases that are permanently attached to the SQL Server instance. If you create an MDF data file in your project then it is part of your project, so you should add it to the project with all the other source files. You should follow the first link in my signature to learn how to manage local data files.

 

[gs99]

I come from an old school of separation between program instructions and data - the db should be developed and maintained separately from the program code. I'm not a professional in this so I may be wrong.

So I developed the db in SQL Management Studio. Then I Detach it when ready for the VB project. The VB connection string "AttachDbFilename=" dynamically attaches the db. It seemed to me that these options were available so a db could reside independent of both SQL server and the VB project.

I'm doing this for personal usage, so I don't need to worry about several users accessing the db in real time. I don't have those challenges.

But as stated, I'd like to learn how to easily copy modified VB projects from one computer to another. If the db is within the projects,
(1) Don't changes to the db design need to be replicated in all projects?
(2) If one project updates the data, how do other projects get the new data?

 

[jmcilhinney]

Either you're using a local data file or you're not. If you are then create it in VS as part of the project and use AttachDbFilename. If you're not then use Initial Catalog, not AttachDbFilename, and use an always-attached database. You are mixing your metaphors. Pick one and stick to it. A local data file means that each application instance has its own database. If that's not what you want then don't even half-try to use a local data file. If that is what you want then use it properly.

 

[gs99]

Thanks for your replies. As I see it now, there are several options.
I think this kind of info should be explained in a sticky post; I don't expect replies to every question here.

(1) What kind of database?
"SQL Server Compact 3.5 database files (.sdf), SQL Server and SQL Server Express database files (.mdf), and Microsoft Access
database files (.mdb) are the currently supported local database files."
The preferred local database for client applications is SQL Server Compact 3.5.
Local Data Overview
Comment: I have VB NET applications that use Microsoft Excel but it's not considered a database.
Question: Why is Compact 3.5 the preferred database? How does it compare with regular SQL Server?

(2) Will the database be on this computer (local) or another computer (remote)?
Local Data Overview, for visual Studio 2010:
"The term local data refers to having a connection between your application and a database file on the local computer (as opposed
to having a connection to a database on a remote server)."
Local Data Overview
Note: My application does not access a database on a remote server; therefore it is a local database.

(3) Where will the database reside in my local computer?
How to: Manage Local Data Files in Your Project, for Visual Studio 2010:
"A local database file can be included as a file in a project. The first time you connect your application to a local database file,
you can choose between creating a copy of the database in your project or connecting to the existing database file in its current
location."
How to: Manage Local Data Files in Your Project
So the options are:
a. can be included as a file in a project
b. the existing database file in its current location (possibly in SQL\DATA?)
Note, it does not say: "You should absolutely not put the data file in the SQL Server data folder."
In my case, I learned SQL by using the SQL Server Management Studio, which saves data files to its DATA folder.
When I started my VB application, I did not "put the data file in the SQL Server data folder"; it was already there!

Note 2: The article explains (a) a file copied to the project or (b) a file that is in another location.
It does not discuss (c) a SQL file generated within the project.
In that case when a new data source is made, the question "Do you want to copy this file?" is not presented.
The SQL files are already in the project folder.

Another option for a local database location is the SQL Server as an attached database.
Note: The only info I have is what you said; that the connection string uses "Initial Catalog".
When I select the database, I get a message "You don't have permission to open the database".
I wonder if this option is available for Express versions.

(4) If the database is integrated as part of the project, what is the "Copy to Output Directory" property?
>Copy if newer (default for .sdf files)
>Copy always (default for .mdf and .mdb files)
>Do not copy
Note: I think there should be more explanation to see the effects of each option.

(5) Are there options for making database connections in the program, instead of the normal way of "Adding data sources"?
Note: We design most of the forms in the IDE easily, but make some changes in code.
Where can we find an overview of connecting with databases in code?

(6) The Linq option to implement SQL.
SQL was "impossible" for me before learning about this option.

 

[jmcilhinney]

1. SQL Server CE is an improvement on Access because it has been built from the ground up with features like security in mind. It's easier to work with than SQL Server Express because it doesn't require a server to be installed. If you need more features than SQL Server CE provides, then you will need to go for SQL Server Express.

3. You should have just created the data file as part of the project in the first place. You don't have to but it should be the default and you should only do otherwise if you have a very good reason for doing so. I wouldn't consider the fact that you have used Management Studio in the past a very good reason. I've used Management Studio too but I still create data files in VS when using SQL Server Express. If you had done that then you wouldn't have needed to start this thread in the first place because it would have just worked.

You use "Initial Catalog" in your connection string to connect to a permanently-attached database. When you create a database in Management Studio, it is attached by default and will remain so unless you explicitly detach it. If you create a database like that then you have to do extra work to use it with SQL Server Express as an attached-on-demand database. You use "AttachDbFilename" in your connection string to connect to a local data file and attach it on demand.

4. The link you provided yourself in point 3 has explanations for those three options. I always use "Copy If Newer" because it allows me to retain test data between debugging sessions and will automatically create a new copy if I modify the schema. If I specifically want to blow away the existing data then I just change it to Copy Always for one run or delete the data file from the output folder explicitly.

5. Connecting to a database is always done the same way. Even if you use something like Entity Framework, it still uses the same connection method under the hood. You create an appropriate DbConnection object with the appropriate connection string and call Open. That's what happens behind the scenes if you use the Data Source wizard and that's what happens in your own code if you do it manually. The connection string is just a String, so you can build it at run time if that's what you need to do.

 

[gs99]

I would like to conclude this thread which asked the question about copying a project with SQL between computers during development. Using Visual Studio 2010 Express, and SQL Server Express 2008 R2 or SQL Server 2012.

I see two options:
.1 The SQL data file resides in the Visual Studio project folder. This can be accomplished either by (a) generating the SQL database within the project or (b) allowing VS to copy the SQL file that resides elsewhere into the project when a new data source is made.
.2 The SQL data file resides outside the project; the answer to ?Copy the file?? question is No.

Both options are ?local data? because the data files are on the same computer as the project; the connection string includes ?AttachDbFilename=?.
And if the database resides in ?\MSSQL\DATA, it must be ?detached? from the SQL server before connecting it to the project.

Option 1 is the preferred option in this case. When the project folder is copied, it includes the SQL files so there shouldn't be a problem referencing these internal objects on the target computer. Initial testing shows good results.
In Option 2, pointers to the external SQL data files apparently is more difficult; the Data source must be regenerated after the paste.

If the user has a preference for Option 2, it could still be utilized after the project was completed.
But if the project is to be deployed to other computers, Option 1 probably is best.

Note that if there is no need for project copies as explained in the initial question, and there is no need for deployment, VS projects can operate OK with Option 2.

Ps. I plan to post new threads for various other issues raised in this thread.

 

[jmcilhinney]

I would like to conclude this thread by saying that you're just making things harder for yourself by doing anything other than creating the MDF file as part of the project using VS so you should only do so if you have a very good reason. I haven't seen any good reason mentioned in this thread. If you intend to use AttachDbFilename in your connection string, i.e. you have an MDF file that will be used only by your app that you want to attach on demand then you should create that MDF file in VS, plain and simple. It will be part of your project and you have to do absolutely nothing to have it deployed with your app.

If you want a permanently attached database, i.e. using Initial Catalog in your connection string, then you should create it in SQL Server Management Studio and then, when it comes time for deployment, you can either create a backup of that database that you can distribute with your app and restore on a target machine or, preferably, have SSMS generate scripts that can be executed either in SSMS or in code to create a database from scratch on a target machine.

That is how it should be done and, while there are alternatives, they should not be considered unless neither of the two options above can do what you want. In this instance, one of the two options mentioned above could have been used so there is no reason to consider alternatives.

 

[Herman]

When designing a database I too prefer SQL Management Studio, just because it's fast and efficient. Designing a DB from scratch in VS is less pleasant in my experience. But even then, nothing stops you from detaching the newly created database from the instance, and adding the MDF file to the project.

 

[jmcilhinney]

When designing a database I too prefer SQL Management Studio, just because it's fast and efficient. Designing a DB from scratch in VS is less pleasant in my experience.

Really? That seems odd given that it's basically the same experience in both cases.

 

[gs99]

I have no disagreement that the integral database solves the problem at hand.

But I do not agree it's the only way for general practice.

You're just making things harder for yourself by doing anything other than creating the MDF file as part of the project using VS so you should only do so if you have a very good reason. I haven't seen any good reason mentioned in this thread.

To start with, I and many others have started the "SQL experience" at the source - the SQL server's IDE. The generated databases are saved in ...MSSQL\DATA folder. These are available for whatever projects may need them.

Is that a viable approach? In the article that your signature points to (How to: Manage Your Local Data Files in Your Project) it's mentioned first:
"If you choose to connect to the existing file, then a connection is created just as if you were connecting to any remote database, and the database file is left in its original location."
When they say "its original location", that could refer to ...MSSQL\DATA. There's no hint of saying "Don?t do this."

If the databases exist already in another location, it doesn't "make things harder" just letting them stay there!

I also mentioned a challenge of having integrated databases, for example I had this:
Project 1 maintained a SQL database of family members.
Project 2 was then developed to show the data as visual objects similar to what?s available in ancestorDotCom.
Both projects used the same SQL file. If the db was integrated in the projects, how would Project 2 get data updates?
I imagine this happens often in business systems; I'm curious as to how it's resolved.
In my simple world, I merged the two projects into one.
Isn't there an advantage in having a central database, where schema and data changes are made at one place?

Deployment is another story. I'm not in a business, so I don't need deployment. If I did, I would probably convert all my projects to integral databases as you suggest or consider SQL Compact. I certainly would not want my friends or customers to experience the agony of installing SQL Server Express just to run an application I developed.

To summarize why some people do not have integrated db's: Their SQL-Server-centric learning path; saving databases in the SQL Server's folder. Microsoft teaching them it's OK. And not needing SQL deployment. Does that make sense?