PeterKellner.net

Introduction and Problem

Converting Data Formats back and forth between browser and back end servers can be a little confusing.  In my case, I have a simple Sencha ExtJS 4.0 form that the user types a date into.  That date gets passed through an ajax request to the Microsoft asp.net controller, then that dates updates the SqlServer Database.  When the Date is displayed on a Grid Panel, again, an ajax request is made that grabs the date from the controller, then formats it using the MS dateFormat from ExtJS and hopefully, the same date the user typed in gets re-displayed.

So, let’s look at the details.

Posting the Date To the Server (View From JavaScript)

First, we need to define an ExtJS Model that has the date in it.  That codes looks like this in my case.

Not sure why this was hard, but I wanted to make a trivial command file to deploy my database (or any change to it).  For those of you that don’t know the DataDude project, I have to say it’s one of the coolest tools that has come out the Microsoft Visual Studio Team for a while.  Basically, in a nutshell, what it does is split’s your database into hundreds of little files.  One for each table, key, foreign key, user, role, etc.  Then, since it is a standard VS2008 project, you can keep those files and the project under source control.  The big benefit is that multiple team members can change files and check them in, and everyone’s schema is kept up to date.

At MIX09 this year, Scott Guthrie presented the Web Platform Installer which seemed like an easy way to install specific technologies on an IIS server.  At the moment, I need to test an application that uses PHP so I figure this is a good time to try it.  Over the years, I’ve had several spectacular failures trying to install PHP on IIS.  Now, maybe I will get it right.  So, here we go.

Step 1: Read the walk through ( http://learn.iis.net/page.aspx/523/web-platform-installer-walkthrough/ )

Step 2:  Click Install now  (Can’t find PHP, starting searching the web now)  {Note: 4/14/2009. Turns out I missed an easy step.  See bottom of article for that step}

Found this link:  Install PHP with Microsoft Web Platform Installer 2.0

Step 3: Go Here and try again:  http://php.iis.net/

As a developer who has spent much of my life doing DBA type work, I really appreciate the simplicity and elegance of what Microsoft Visual Studio Team has put together with the Database Edition and can be seen for sale here as the Microsoft Visual Studio 2010 Premium with MSDN Download – Download Direct from Microsoft I’ll first try and explain in a nutshell what it is.  Then I’ll go through an example of creating a Visual Studio Database Project out of a site I’ve been working on to show the steps.  Finally, I’ll give a short summary reliving the experience and giving you some other pointers that may help you get through the experience.

What is VS2008 Database Edition?

Basically, what VS2008 does is to first process your existing schema into hundreds (maybe thousands) of little files.  That is, each file is one database thing. That thing might be a table definition, a stored procedure, a trigger, a constraint, a foreign key, etc.  The beauty of this is that now, each thing can be tracked separately.  Say for example, you want to update just one table and someone else on your team wants to update another table in the same schema.  Since the files are now separate files, it’s no problem.  When you grab the latest changes from source control, you will get your friends changes and he will get yours.  Very very clean!

Also, it has a very powerful comparison engine.  Say you update your data outside of this project.  VS2008 Database Edition will compare your definitions in all these little files with either a sql script you generate, or it will connect to an external database and compare to that.  Me personally, I like to use a tool called SqlManager to manage my data.  I can continue to do that, then when I’ve made my changes, I can simply connect VS2008 Database Edition to my updated development database and it will automatically figure out the changes.

So, you may ask, what version do I need to run this of Visual Studio?  The answer is in this link:  http://msdn.microsoft.com/en-us/vs2008/products/cc149003.aspx

Here We Go, Let’s do an Example

So, first, I will open my existing solution which has a web project in it, a data access project as well as other projects.  Then, I create the new Database solution by saying File/Open/New Project/Database Project.

Then, we follow the wizard…

Taking defaults…

Now, I choose my schema.

Press Start…

Once you’ve done that, you’ll now have your schema spread out across your project in little files.  Below is what the "file based" view of your schema looks like.

Notice that the file we are looking at also includes comments for both the table and the columns using the standard extended property documentation format as follows:

GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', 
@value = N'tracks all changes from each camp

title;link;guid;pubDate;category;description', @level0type = N'SCHEMA', 
@level0name = N'dbo', @level1type = N'TABLE', 
@level1name = N'CampFeed';


GO
EXECUTE sp_addextendedproperty @name = N'MS_Description', 
@value = N'if true, this is the main news feed from the site.  
there should only be one main news feed from any site.  this would 
not be feeds like twitter', @level0type = N'SCHEMA', 
@level0name = N'dbo', 
@level1type = N'TABLE', @level1name = N'CampFeed', 
@level2type = N'COLUMN', @level2name = N'PrimaryFeed';

Now, when you actually build the project as follows:

it will generate the full script combining all the files back into one again. Here is where it puts it:

So, that’s about it for now. I haven’t gone into any of the ways to compare and update your schema based on external changes, but we can leave that for another post.

To see a great video by Richard Hundhausen, President of Accentient, a Team Systems Consulting company, go to this link:  http://msdn.microsoft.com/en-us/vsts2008/cc659682.aspx

Also, if you are using Sql Server 2008, make sure you install visual studio 2008 sp1, then go to this link and install the download:  http://www.microsoft.com/downloads/details.aspx?FamilyID=bb3ad767-5f69-4db9-b1c9-8f55759846ed&displaylang=en

Conclusions

From what I can tell, this Visual Studio Database Edition will be a huge help in building database schema’s and database programs (sp’s, triggers,etc.) in a collaborative environment.   If you have the license to use this, I strongly recommend it.  It is definitely going to be part of my tool kit for now on.  Even if I’m the only author in the project.