Create and upgrade SQL Server database using a Visual Studio 2010 Database Project

May 31, 2011

Contents

Introduction | Objectives | Objective 1: Create a new database |
Objective 2: Create a Full Upgrade | Deploying Manual Schema Changes | Objective 3: Create a partial upgrade | Using Configurations | CruiseControl Integration | NetTiers Integration

Introduction

Maintaining your database structure within Visual Studio allows you to rebuild the database from scratch and keep a record of the modification history in SourceSafe or TFS.

On one particular project, I used the standard SQL Server Project provided in Visual Studio 2005 creating my own directories to separate schema objects such as tables and stored procedures. A custom application would run the scripts in the project in a defined order to allow you to create and upgrade the database.

Recently I decided to migrate the solution to Visual Studio 2010.

When I attempted to convert the SQL Server Project I came across all kinds of issues. Such as the compilation not working because some commands such as DROP and EXEC were not supported.

I also tried to reverse engineer the database project from SQL Server into Visual Studio 2010. Being a bit of a control freak, this did not satisfy me because I didn’t know what the reverse engineering had created. I could see there were issues (e.g. permissions were not scripted how I would like), so I decided to hand-craft the project from the scripts that already existed in the old SQL Server project.

Below is a record of the objectives, along with everything that needed to be done to achieve the objectives.

Objectives

There were 3 objectives:

  1. Create a new database with test data
  2. Create a full upgrade: restore the previous version of the database and run schema and data upgrades
  3. Create a partial upgrade: run schema and data changes on a database that has already been generated via the full upgrade (e.g. stored procedure changes) (for high load iterative development or potentially iterative deployment)

Read the rest of this entry »

1 Comment | SQL Server, Visual Studio | Tagged: , , , , , , , , , , , , , , | Permalink
Posted by Ruth D

Are you taking advantage of Visual Studio Database Projects?

November 30, 2010

We have been using Visual Studio Database Projects for a few years now and it’s easy to take the product for granted.  It’s one of the greatest boons to our SQL Server application development process and I can’t sing its praises enough.

Very briefly, here’s how we use it: A developer updates or adds database objects in the Visual Studio Solution.  He tests the change locally in his own sandbox (which includes a database instance).  When happy, he checks in the set.  Continuous Integration takes over and creates a SQL script to upgrade a central build instance of the database to include the new changes checked in.  The script is run against that instance and any errors applying the script reported back as ‘build’ errors.  So the team gets immediate feedback on the validity of SQL artifacts just like any .NET code.  The final step in the process is preparation of database upgrade scripts for shipping to your customers.  The key here is to take these off the central build instance of the database which ensures that only changes that were checked in and part of successful builds get included.  So never again are you in the situation where database artefacts are shipped but not in source control!  (We’ve all been there, don’t deny it.)

Ok, just a couple points of detail you may benefit from if you go ahead with this approach:

1) If using Visual Studio 2008 Database Edition, be sure to install GDR R2 which has the latest updates and fixes you’ll need

2) When running MSBuild.exe, you will need to make two passes: one with “/t:Rebuild” and a second with “/t:Deploy”

Well that’s the highlights of the process.  For more detail on any one of the individual steps feel free to comment on this post or ping us a note.

-Krip

Leave a Comment » | SQL Server, Uncategorized, Visual Studio | Permalink
Posted by Krip

Multiple MS Chart Series with One Line of Code

November 21, 2010

Microsoft’s Chart Controls for .NET 3.5 and 4.0 have great support for binding to data.  But what if you want to bind to a data set such that multiple series are visualised?  This can be accomplished with one line of code using the Chart.DataBindCrossTable method.  Here’s an example:

   myChart.DataBindCrossTable(myDataTable.AsEnumerable(), ”Category”, “Date”, “Sales”, “”);



So in this example one series for each distinct value of “Category” will be generated.  “Date” values will show on the X axis and “Sales” on the Y.  Also note that the first argument must be of type IEnumerable so a DataTable can be cast to such via the AsEnumerable method.

Alex Gorev does a great job of explaining the details on his blog here.  Scroll down to the section “DataBindCrossTab” (note in his post the method has a slightly different name).

Microsoft’s documentation of the method is here.

-Krip

Leave a Comment » | Reporting, Visual Studio | Tagged: , | Permalink
Posted by Krip

Crystal Reports for Visual Studio 2010

October 25, 2010

Beginning with Visual Studio 2010, Microsoft is not bundling Crystal Reports in with their software.  Crystal Reports can instead be downloaded directly from SAP’s site.  The product is currently at beta 2 with “production release planned for November 2010″.

-Krip

Leave a Comment » | Reporting, Visual Studio | Tagged: , , | Permalink
Posted by Krip

Visual Studio 2008 Hangs After Upgrade to Office 2010

May 20, 2010
After you upgrade from Office 2007 to Office 2010 you might find that the Visual Studio 2008 web editor hangs on occasion.
 
You can correct that problem by repairing the Visual Studio Web Authoring component.
 
Instructions here:
http://blogs.msdn.com/webdevtools/archive/2010/04/19/upgrade-or-uninstall-of-office-2007-might-cause-vs-2008-web-designer-to-hang.aspx

Leave a Comment » | Office, Visual Studio | Permalink
Posted by Krip

Follow

Get every new post delivered to your Inbox.