How do I program against a local database while I develop in ASP.NET MVC?

Question

At my work I found out that we belong to some program with Microsoft and get access to various products because of it.

I've downloaded Microsoft Visual Studio 2010.

I'm not a professional programmer but I've dabbled before and I wanted to check out ASP.NET MVC. I know that I want to use some flavor of SQL Server and that I want to create a hobby website as a project (which may get deployed at some point in the future).

How do I create the schema for, and code against a local database that resides on my machine and then at a later point, use an external, actual SQL Server?

Does VS2010 make programming against a local SQL Server database pretty easy?

Answer 1

Developing against a local database is simple.

Download SQL Server Express (or SQL Server from MSDN, since it sonds like you have access).

Use your Web.config file to store your database Connection String. You can utilize it in your application using:

using(SqlConnection conn =
    new SqlConnection(ConfigurationManager.
        ConnectionStrings["MyConnectionString"].ConnectionString))
{
}

All you have to do is change the connection string in your application to point your application to the Production server:

<connectionStrings>
    <add name="MyConnectionString"
         connectionString="Data Source=localhost;
                           Initial Catalog=myDataBase;
                           User Id=myUsername;
                           Password=myPassword;" />
</connectionStrings>

Becomes

<connectionStrings>
    <add name="MyConnectionString"
         connectionString="Data Source=ProductionServer.MyDomain.com;
                           Initial Catalog=myDataBase;
                           User Id=myUsername;
                           Password=myPassword;" />
</connectionStrings>

Answer 2

Check out this tutorial:

Create a Movie Database Application in 15 Minutes with ASP.NET MVC

In "Creating the Database" part it shows you a complete step by step guide to create a local database using Micrososft SQL Server Express that comes bundled with Visual Studio.

Answer 3

Check out this MSDN Section, "Using SQL Server Express with ASP.NET:" http://msdn.microsoft.com/en-us/library/ms247257.aspx. There's a series of articles here that should help you get started with your MVC site and SQL Express.

Answer 4

There are various tutorial that will show you how to connect to a local SQL Server Express and use Visual Studio's Server Explorer to create database objects, or design the database schema in the LINQ modeler and use the LINQ Datacontext deployment methods. this approach threats the MDF file of the database as a binary file of the project. Previous answers already pointed out the most popular ones.

What all these tutorials will fail to mention is that this method works only to deploy v1 of your product, and fails miserable when you want to deploy the next version. None of these methods provide a way to deploy a database schema upgrade to a production runing site.

For a hobyyist developer this approach will work, but a professional shop will have to use a much different approach. SO itself is full of questions from developers burned by the naive approach trumpeted in these tutorials (questions ranging from 'how do I check in my MDf in SVN?' to 'I have made modification to the MDF, how do I dpeloy them to my hosting?'). One alternative is to use a Visual Studio Database Project, define the schema in the database project that produces a .dbschema file and use the vsdbcmd tool schema diff deployment capabilities. Another alternative is to take full control and deploy all schema objects via upgrade T-SQL scripts: for each schema change, an upgrade script is created that deploys the change from vN to vN+1, and marks the database schema as being at VN+1. This later approach is best fit for multiple deployments, when each location may be at a differne tversion and each release has to be able to upgrade the schema from any verison on disk to the desired version, by running succesively all the upgrade scripts from the current on-disk version to the desired version. This, in fact, is the approach used by SQL Server itself to upgrade the resource database.