Weekly roundup – April 7, 2014

Send to Kindle

The Weekly roundup is my regular post of interesting and useful things I’ve found or done over the last week.

Topics: Diversity in tech, crypto, online verification, Windows pkg management, .NET Foundation, a better RSS reader, tutorials, taking credit cards, debugging mod_rewrite.

From the //Build/ developer’s conference

  • OneGet is a new effort at package management on Windows that builds off and extends the work Chocolatey started. It sounds like they’ve got the right ideas for moving in the direction we need to go.
  • Microsoft announced new .NET Foundation to oversee(?) all the parts of .NET that have and will be open sourced.I think this approach is the right way to do this.

Tools, etc.

  • Signed up with Keybase – a new service that facilitates public/private key-pair (e.g. PGP, GnuPG) encryption and verification. You can find my public key at https://keybase.io/shawns. And if you’re interested in signing up I have some invites – just give me the e-mail address you want me to send them to.
  • If you’re still consuming RSS like me InoReader is a wonderful new online feed reader. The free tier gives you unlimited feeds and one filter rule along with folders that have their own feed, light & dark themes and an API.
  • Looking at using Stripe - simple online payments for developers – to accept credit cards directly for supporting my work.
  • Thanks to Dan for passing along this handy tool for debugging your Apache mod_rewrite rules.

Projects, etc.

Weekly roundup, March 3rd 2014

Send to Kindle

NOTE: I’m moving the posting day/time for the weekly roundup to Monday morning. Hopefully that will make it a little more visible as well as giving me the weekend to wrap up the content.

Tools

  • Friend of a friend, Robert shared MTR – a better traceroute for *nix. I also found WinMTR; unfortunately there’s not a Chocolatey package available.
  • I’m not quite as late to this party as Eli is, but certainly late enough to have missed Scott Hanselman’s post about SlowCheetah. Previews for my Visual Studio configuration transforms? Yes please! (Scott’s most recent blog post is also very much worth reading and sharing.)
  • I’ve recently needed to start watching my blood glucose levels. Today I discovered Nutritionix, which provides free nutrition information (including calculators for many restaurants) and an available API. Access to the API includes a free tier and they state that freeware mobile apps can get a bump in that access for no charge.

Projects, coding, etc.

Adding a Database Reference to an external database

Send to Kindle

Managing database projects within Visual Studio can be extremely confusing. There seems to be very little information out there on how to use the provided project templates properly, and what little there is is often cryptic and clearly written by insiders. Watch this blog for a forthcoming article which will hopefully clear up some of this confusion, but for now I wanted to share what I’ve learned about referencing other, existing databases from within your Visual Studio database project.

With the 2010 release, Visual Studio’s database project templates appear to want you to encapsulate your entire database within the project – and the IDE. This can create a problem when the new database you’re creating needs to reference some objects from another database on the server (an enterprise data store, for example) – since the project doesn’t know anything about this other database two things happen:

  1. You lose those handy IntellisenseTM tips when referencing the external database, and
  2. Attempting to build and/or deploy the project spews a bunch of errors about missing references.

An afternoon of hunting revealed that there are a couple of ways to address this – one of which involves creating another project, importing the other database, and then adding a reference to the first project. I don’t know about you, but that sounds like an awful lot of work and overhead for what’s essentially a Reference link.

Fortunately, there is a slightly easier way: The key is the .dbschema file. These files represent the compiled state of a database which is ready to be deployed with Visual Studio or some other tool. But what is useful for us here is that Visual Studio can also use these files to determine what objects are in the database – providing IntellisenseTM and reference mapping. So this is what we need to do:

  1. Create a .dbschema file for the external database we want to reference
  2. Add that file to your SQL Server 2008 Database Project

Creating the .dbschema file

The reason some people recommend creating another database project in Visual Studio is because that will create the .dbschema file for you, but there is also a handy tool you can use by the name of VsDbCmd. This command-line tool for deploying Visual Studio database projects can be found in the following location:

C:Program FilesMicrosoft Visual Studio 10.0VSTSDBDeployvsdbcmd.exe

(Note: It will be in C:Program Files (x86) on 64-bit Windows.)

While most of the documentation out there focuses on how VsDbCmd can be used to deploy database projects, it can also be used to generate a .dbschema file from an existing database. To do this, use the following syntax:

vsdbcmd /a:Import /cs:”ConnectionString” /dsp:Sql /model:DatabaseName.dbschema

Where ConnectionString is the full connection string for the database you want, and DatabaseName is the name of the file to create.

Adding the Reference

Now that you’ve created the file, it’s time to add it to your Visual Studio project as a Database Reference. Expand the project node in the Solution Explorer, right-click References, then select Add Database Reference…. Finally, make the following changes to the Add Database Reference dialog:

  • Select the Database project schema radio button, and Browse to the .dbschema file you just created.
  • Check the Define database variable box and enter the name of the external database for the following values:
    • Name:  $(DatabaseName)
    • ValueDatabaseName
  • Check the box that says Update the existing schema object definitions and scripts to use the database reference variables.

After clicking OK, and a bit of processing on Visual Studio’s part, you should now be able to compile your database project without errors.

Please feel free to comment below if you have any questions or information to add.


References used for this post: