Offline Database Development

  • Article

DBPro supports an offline development model. This is a key notion to understand if you are to realize the full potential of Visual Studio 2005 Team Edition for Database Professionals ( aka "Data Dude" or "DBPro" ). Many folks do not quite understand the importance or power of this feature on first blush. Fundamentally, once you have imported database schema from an existing database ( or created from scratch ), the database project is no longer connected to that database. Changes made to the project are isolated to that project and are not propagated to the original database ( in fact, no memory of the original database is maintained! ). But this does not mean that DBPro loses context behind the meaning of your DDL, or the validity of your sprocs now represented in the project. In fact, the product understands and is quite sensitive to the relationships between tables, or the columns being used inside stored procedures. Let me show you an example in an effort to drive this point home( if you want to follow along, import the Northwind database into an empty project. For more info, please see this section in the online help ):

1) Let's create a new stored procedure. Right click on the "Stored Procedures" node in Schema View, and call the sproc "mySproc". You should see the following code:

CREATE PROCEDURE [dbo].[mySproc]
@param1 int = 0,
@param2 int
AS
SELECT @param1, @param2
RETURN 0;

2) Now add a comma after the @param2 in the select clause, and save the stored procedure 

3) You should see the following in your error list ( if you can't see the error list, select View->Error List ):

and you should see an error icon overlayed on the sproc in Schema View:

4) Remove the comma, and everything clears up ( nothing in the error list, no error icon in Schema View )

Alright, I know, you're probably thinking, "......and....? Is that it?". Well, yes and no. This example is quite simplistic. Most any decent tool worth its salt can report syntax errors. In actuality, DBPro not only examined this stored procedure for syntax errors, but it also performed what we call "semantic binding". What that means is that any schema objects being referenced were validated. In this simple example, that means that the parameters being referenced in the select statement were analyzed to make sure that they were valid / existed in a particular namespace or scope. You can see evidence of this by changing the name of one of the parameters to something that you know doesn't exist. You'll see an error in that case like so ( I changed @param2 to @param3 ), and the error glyph will show back up in Schema View:

This starts to get very interesting when you start dealing with more complex script. For example:

  1. Double click on the dbo.CustOrderHist stored procedure.
  2. Change P.ProductID to P.ProductID2 and save the procedure
  3. Notice your error list now:

You'll see an error and a warning. The error is indicating to you that the ProductID2 column does not exist, and the warning is basically a side effect of the error. DBPro attempts to locate the ProductID2 column using the various objects it finds within the scope of the reference( the Products table, the Order Details table, Customers, etc. ).

This continues to be very interesting as your database evolves. For example, stored procedures that are already deployed can "atrophy" as schema that the sproc refers to changes, setting yourself up for a runtime error the next time that stored procedure is executed. In DBPro, that stored proc shows up in an error state upon import into a DBPro project, alerting you that you have a problem lurking in your database, just waiting to raise its ugly head! Making changes inside DBPro prevents these types of inconsistencies before you deploy, thereby never allowing this type of error to exist in your deployed databases.

All this analysis and dependency checking, and you are NOT connected with any production or staging database! You are offline!

But why is this important? What does this enable?

Offline database development allows you to evolve your database on your own terms, without fear of wrecking any runtime database or application leveraging that database. This is fundamentally what DBPro is all about: mitigating the risks involved in database changes.

Hope that helps!

Cameron