Closing all sql connections from Solution Explorer.

27. September 2012 17:21 by admin in .net, vs2010 vs2012
In my development work I get the following message quite a lot : Cannot drop database "XXX" because it is currently in use. This usually means I have an existing website or tool with a connection to the database I want to drop. Especially with Entity Framework Code-First I tend to rewrite the whole DB quite often. Instead of killing the 'guilty' process I decided to create a VS shortcut to deal with it. So here's what I have now:


What does this do?

  1. Look for a App.Config or Web.Config of the selected project
  2. Look for connection strings in the configuration file
  3. Use the first connection string and try to connect to the corresponding master db
  4. Close existing connections to the database specified in 3

So HOW does it work?

For this purpose i created a small console application and integrated it into my Visual Studio as an external command. 

The application (source code) is included as a zip file here: connection-killer.zip (211,17 KB)
This consists of a VS2012 solution but it should be easy to open it in VS2010 as nothing special is done. (For VS2010 just import the .cs files into a 'fresh' project) The heart of the application is the following:

Basically switching between SINGLE_USER and MULTI_USER closes all active connections. 

Next (and final) step is to integrate the program into vs2012. The steps are the same on VS2010 and afaik VS2008. Here's how:
1 Add the program as external tool
Via Tools->External tools click add. Point to the point where you have connection-killer exe file and change the initial directory of the application. Move connection killer up so it is the first external tool (you'll see why later). It should look like this:


2 Create a short in the Solution Explorer context menu
  1. Go Tools->Customize and click the Commands tab. 
  2. Click the context menu 'Project and Solution Context Menus | Project'
  3. Add command select category 'tools' and select command 'External Command 1'
  4. Click 'Modify Selection' and rename the menu item to kill connection or something
  5. Close the dialog and you're done

Packages needed for mvc 4 and scaffolding.

27. September 2012 14:56 by admin in
Here's my packages folder after I added a MVC site and installed mvc-scaffolding. 

I agree with the lego strategy of Asp.Net but isn't this overkill? Added the version specific dependencies and the fact that packages don't get uninstalled if no longer needed and things get really messy quickly.

Compiling aspx forms

27. September 2012 13:02 by martijn in

Build succeeded run...fail. It annoys me that I use a TYPE SAFE language like C#, use ASP.NET with ItemType (strongly typed stuff) and still get runtime errors. So I have to click through every page when I refactor? 

 
Luckily compiling the views can be done by adding a afterbuild or using a postbuild event. Here what you paste in your afterbuild event to get the aspx compiling :
 
%windir%\Microsoft.NET\Framework64\v4.0.30319\aspnet_compiler.exe -v /-p "$(SolutionDir)$(ProjectName)"

Adding support for spatial types to Windows Server 2008 R2 x64

25. September 2012 20:10 by admin in
Recently I deployed a site that internally used the new spatial CLR type DbGeography. When I tried to run I got the folowwing error :

Spatial types and functions are not available for this provider because the assembly 'Microsoft.SqlServer.Types' version 10 or higher could not be found

This basically means that a dll is missing on the box. Since it is non-trivial to find this dll I'll blog about it. You have to install the SQLCltTypes package. If you install the wrong version of the package you might get a problem with SqlServerSpatial not being installed. The version that worken for me on Windows Server 2008 R2 x64 was here (below the x64 folder)


Distances with DbGeography and DbGeometry

23. September 2012 08:21 by admin in
In a recent project we had to do some spatial search (finding nearest shops). With the new EntityFramework 5.0 in asp.net 4.5 this is finally possible. To get the nearest products (in a search) you write something like:


Now there can be some confusion about which type to use for location. There are two types to describe the 'location' of an object:

From MSDN:
DbGeography : Represents data in a geodetic (round earth) coordinate system.
DbGeometry : Provides a base class for objects that define geometric shapes. 

Now here is a big difference. The first class being a point on earth and the second one a point (or shape). DbGeometry does not include anything about dimensions. This is clear when calculating the distance. When calculating distance for DbGeography the result is given in meters. For DbGeometry the result is still dimension less. A bit of code can say more than a thousand words...


So for real-world objects use DbGeography as location!