Proper structure of asp.net website and database in visual studio

My main problem is where does database go?

The project will be on SVN and is developed using asp.net mvc repository pattern. Where do I put the sql server database (mdf file)? If I put it in app_data, then my other team mates can check out the source and database and run it with the database being deployed in the vs ins开发者_Go百科tance.

The problem with this method are:

1. I cannot use SQL Management Studio with this database.
2. Most web hosts require me to deploy the database using their UI or SQL Management studio. Putting it in App Data will make no sense.
3. Connection String has to be edited each time I'm moving from testing locally to testing on the web host.

If I create the database using SQL Management studio, my problems are:

1. How do I keep this consistent with the source control (team mates have to re-script the db if the schema changes).
2. Connection string again. (I'd like to automatically use the string when on production server).

Is there a solution to all my problems above? Maybe some form of patterns of tools that I am missing?

Basically your two points are correct - unless you're working off a central database everyone will have to update their database when changes are made by someone else. If you're working off a central database you can also get into the issues where a database change is made (ie: a column dropped), and the corresponding source code isn't checked in. Then you're all dead in the water until the source code is checked in, or the database is rolled back. Using a central database also means developers have no control over when databsae schema changes are pushed to them.

We have the database installed on each developer's machine (especially good since we target different DBs, each developer has one of the supported databases giving us really good cross platform testing as we go).

Then there is the central 'development' database which the 'development' environment points to. It is build by continuous integration each checkin, and upon successful build/test it publishes to development.

Changes that developers make to the database schema on their local machine need to be checked into source control. They are database upgrade scripts that make the required changes to the database from version X to version Y. The database is versioned. When a customer upgrades, these database scripts are run on their database to bring it up from their current version to the required version they're installing.

These dbpatch files are stored in the following structure:

./dbpatches
    ./23
        ./common
            ./CONV-2345.dbpatch
        ./pgsql
            ./CONV-2323.dbpatch
        ./oracle
            ./CONV-2323.dbpatch
        ./mssql
            ./CONV-2323.dbpatch

In the above tree, version 23 has one common dbpatch that is run on any database (is ANSI SQL), and a specific dbpatch for the three databases that require vendor specific SQL.

We have a database update script that developers can run which runs any dbpatch that hasn't been run on their development machine yet (irrespective of version - since multiple dbpatches may be committed to source control during a single version's development).

Connection strings are maintained in NHibernate.config, however if present, NHibernate.User.config is used instead, however NHibernate.User.config is ignored from source control. Each developer has their own NHibernate.User.config, which points to their local database and sets the appropriate dialects etc.

When being pushed to development we have a NAnt script which does variable substitution in the config templates for us. This same script is used when going to staging as well as when doing packages for release. The NAnt script populates a templates config file with variable values from the environment's settings file.

Use management studio or Visual Studios server explorer. App_Data isn't used much "in the real world".

1. This is always a problem. Use a tool like SqlCompare from Redgate or the built in Database Compare tools of Visual Studio 2010.

2. Use Web.Config transformations to automatically update the connection string.

I'm not an expert by any means but here's what my partner and I did for our most recent ASP.NET MVC project:

Connection strings were always the same since we were both running SQL Server Express on our development machines, as were our staging and production servers. You can just use a dot instead of the computer name (eg. ".\SQLEXPRESS" or ".\SQL_Named_Instance").

Alternatively you could also use web.config transformations for deploying to different machines.

As far as the database itself, we just created a "Database Updates" folder in the SVN repository and added new SQL scripts when updates needed to be made. I always thought it was a good idea to have an organized collection of database change scripts anyway.

A common solution to this type of problem is to have the database versioning handled in code rather than storing the database itself in version control. The code is typically executed on app_start but could be triggered in other ways (build/deploy process). Then developers can run their own local databases or use a shared development database. The common term for this is called database migrations (migrating from one version to the next). Here is a stackoverflow question for .net tools/libraries to make this easier: https://stackoverflow.com/questions/8033/database-migration-library-for-net

This is the only way I would handle this on projects with multiple developers. I've used this successfully with teams of over 50 developers and it's worked great.

The Red Gate solution would be to use SQL Source Control, which integrates into SSMS. Its maintains a sql scripts folder structure in source control, which you can keep in the same folder/ respository that you keep your app code in.

http://www.red-gate.com/products/SQL_Source_Control/