May 31, 2011
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
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.
There were 3 objectives:
- Create a new database with test data
- Create a full upgrade: restore the previous version of the database and run schema and data upgrades
- 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 »
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.
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.
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″.
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.