SQL Server 2008: Freeing File system space

Our database at works has only 10GB of space which is really not enough. We have tables with millions of rows and sometimes we need to either change the data or move it around. This usually enlarges the database space in the file system and we often get an error about insufficient disk space in the middle of running a query.

For the first two times it happened I called a friend of mine who is also a DB administrator. The second time it happened though he had me open a word document and create a how-to for compressing the database files. This is it.

 

1. Connect to the database computer with msdtc

2. Open SQL Server Management and connect with your SA account

3. Right click the database –> Properties –> Options: If the Recovery Mode is not Simple change it to Simple (you will have to change it back in the end):

4. Right click the database –> Tasks –> Shrink –>Files:

File Type = Log

Shrink action = Release unused space

In Available free space you can see how much you are going to free and you can chose if for example 4.28MB is really worth waiting for the shrink to end.

5. Right click the database –> Tasks –> Shrink –>Database:

Again in Available free space you can see how much you are going to free and you can chose if for example 1203.96MB is really worth waiting for the shrink to end (for me it is….).

Be warned that this could take some time.

7. If in step 3 you changed the Recovery Mode to Simple that you will have to change it back to your original value. If it was Simple you can simply skip this step.

That it – enjoy the free space…

 

keywords: SQL Server 2008, Free space, Hard Disk, recovery mode, database, DBA

IceRocket Tags: SQL Server 2008,Free space,Hard Disk,recovery mode,database,DBA

TeamCity: Running Unit Tests

Summary: Running Unit Tests and Code Coverage using TeamCity

This is the third post in my TeamCity series, the first post is TeamCity: Easy Continuous Integration and the second is TeamCity: Building the Solution

After the last post which was too long this should be quite short. Remember in the last post I showed you the full list of build runners, then one of the build runners was MsTests (and another was NUnit).

So from the project view click on Edit Configuration Settings (in the upper right corner):

Now on the right click on 3 Build Step:

Now click on Add build step, in Runner type choose MsTest:

Its optional but add a name to the step, in path to MSTest.exe either click on your version of VS (2010 = 10, 2005 = 9 …) or leave blank.

Testing with NUnit is pretty similar:

The list of assemblies here and MsTest is of the dlls of the test projects from VCS root (from the setting of TFS Source Control). Just like in the previous post with the solution file.

 

On the bottom in .NET Coverage tool chose JetBrains dotCover, this uses TeamCity’s dotCover which gives pretty good results.

Click on Save. And that’s it.

 

Looking into the build run:

Looking at the full report:

When reaching the class being tested:

We can see that not all of the method is tested. In actuality I believe that code is inaccessible (can an enum value be empty so that it’s ToString will return an empty string?) but it’s good for the demonstration.

You can view the class here, and it’s tests here.

 

IceRocket Tags: TeamCity,MsTest,unit test,nunit,Continuous Integration

ArcObjects: Extending the Framework

Summary: Using Extension Methods to make ESRI COM code more readable.

This post is the third post in my ArcObjects series.

Well after learning a bit about COM and interfacing lets talk about writing some nice clean code. Back in older times before C# 3.0 came around we really didn’t have much options – we could write wrapper classes around ArcObjects but the code in those wrappers usually wasn’t very maintainable. Moreover using Unit Tests to test most of those methods is quite impossible – instead it has become better to use Integration Unit Tests (test that access the DB).

But then came C# 3.0, when I first saw the feature of Extension Methods the first thing I asked “Does it work on interfaces?” when answered “Yes”, I knew what I wanted.

The easiest example would be getting a value from the IRow interface, using regular code it will look like this:

1. public static object GetValue(IRow row, string field)
2. {
3.     int fieldIndex = row.GetFieldIndex(field);
4.     if (fieldIndex == -1)
5.         throw new ArgumentException("Field " + field + " wasn't found in the table.");
6.     return row.get_Value(fieldIndex);
7. }

Using it:

1. object value = GetValue(row, field);

But using Extension Methods we can write a method such as:

1. public static object GetValue(this IRow row, string field)
2. {
3.     int fieldIndex = row.GetFieldIndex(field);
4.     if (fieldIndex == -1)
5.         throw new ArgumentException("Field " + field + " wasn't found in the table.");
6.     return row.get_Value(fieldIndex);
7. }

And the using it will be as easy and readable as:

1. object value = row.GetValue(field);

Now, it could be just me but I think using Extension Methods is more readable. Even more than that most developers like to write general methods but when using the first code sample you run into the risk of not finding the class containing the method. If developers can’t find the class/method these developers are likely going to “reinvent the wheel” and implement their own GetValue method.

You could view this method and even one better (that converts the object to a generic type) here, in my CodePlex project.

Resources:

Extension Methods

Keywords: ESRI, ArcObjects, Extension Methods

IceRocket Tags: ESRI,ArcObjects,Extension Methods

ESRI Silverlight API: Getting Started

Summary: A little exercise using ESRI Silverlight API just to get started with the framework. Simply using the code samples ESRI provides like Lego building blocks.

Back when I was interviewing to my current position, the interviewer (my current team leader) gave me a station with the interactive Silverlight SDK and asked me to change one of the examples so that when selecting a polygon all the polygons that are smaller in area size from the selected polygon will also be selected. That was my first interaction with Silverlight (with ESRI I have worked before).

In this post I am just going to implement that exercise.

This example uses version 2.1 of the API (the current version). It can be downloaded from here (you will have to register to a ESRI Global Account but it is free).

 

First lets compile the solution and choose the base for the application, I have chosen Query->Spatial Query (which implements selection from the map):

Now the code behind and the xaml is a mess (because of all the extra functionality) so I will just remove some things (just keep the point selection):

The symbol for the results:

1. <Grid.Resources>
2.     <esri:FillSymbol x:Key="ResultsFillSymbol" BorderBrush="Blue" BorderThickness="5"/>
3. </Grid.Resources>

The map with one layer for the background and an event for the mouse click:

1. <esri:Map x:Name="MapControl" Extent="-15000000,2000000,-7000000,8000000" MouseClick="MapControl_MouseClick">
2.                 <esri:ArcGISTiledMapServiceLayer ID="StreetMapLayer" Url="http://services.arcgisonline.com/ArcGIS/rest/services/World_Street_Map/MapServer"/>
3. </esri:Map>

And last a canvas for the buttons of Point selection and Clear (that you can see here).

 

The code behind (in the next post I will do this with the MVVM pattern) contains the handlers for the two buttons (simply turning a flag on or off):

1. private void PointButton_Click(object sender, RoutedEventArgs e)
2. {
3.     _selectionMode = true;
4.     StatusTextBlock.Text = "Select by clicking at point location";
5. }

(the clear looks the same)

 

The map click event only works when the selection mode is on:

1. private void MapControl_MouseClick(object sender, Map.MouseEventArgs mouseEventArgs)
2. {
3.     if (_selectionMode)
4.         QueryContainingGeometry(mouseEventArgs.MapPoint);
5. }
6.  
7. private void QueryContainingGeometry(Geometry geometry)
8. {
9.     _selectionGraphicsLayer.ClearGraphics();
10.  
11.     var queryTask = new QueryTask("http://sampleserver1.arcgisonline.com/ArcGIS/rest/services/Demographics/ESRI_Census_USA/MapServer/5");
12.     queryTask.ExecuteCompleted += QueryTask_ExecuteCompleted;
13.     queryTask.Failed += (sender, args) => MessageBox.Show("Query failed: " + args.Error);
14.  
15.     var query =
16.         new Query
17.             {
18.                 Geometry = geometry,
19.                 ReturnGeometry = true,
20.                 OutSpatialReference = MapControl.SpatialReference
21.             };
22.  
23.     queryTask.ExecuteAsync(query);
24. }

QueryContainingGeometry method does a query against the States layer in ArcGis Server and returns the result to:

1. private void QueryTask_ExecuteCompleted(object sender, QueryEventArgs args)
2. {
3.     var featureSet = args.FeatureSet;
4.  
5.     if (featureSet == null || featureSet.Features.Count < 1)
6.     {
7.         MessageBox.Show("No features returned from query");
8.         return;
9.     }
10.  
11.     foreach (var feature in featureSet.Features)
12.     {
13.         feature.Symbol = LayoutRoot.Resources["ResultsFillSymbol"] as FillSymbol;
14.         _selectionGraphicsLayer.Graphics.Add(feature);
15.     }
16. }

Here we go over the results and add them to a graphics layer (we created it in the constructor) using the symbol from the xaml.

Until now the code was mostly ESRI’s example (as I said I removed some of it and refactored a bit more): the full xaml can be found here, and the code behind here.

But now we start with the actual exercise. We are going to use the field SQMI which is the square miles of the state.

The first thing we do is change QueryTask_ExecuteCompleted so that it executes a query:

1. var minimumSquareMiles = int.MaxValue;
2. foreach (var feature in featureSet.Features)
3. {
4.     feature.Symbol = LayoutRoot.Resources["FirstStateSymbol"] as FillSymbol;
5.     _selectionGraphicsLayer.Graphics.Add(feature);
6.     minimumSquareMiles = Math.Min(minimumSquareMiles, (int) feature.Attributes["SQMI"]);
7. }
8.  
9. QueryByArea(minimumSquareMiles);

In line 4 I changed the symbol being used just so we could see the first state as opposed to the ending states.

QueryByArea looks like:

1. private void QueryByArea(int minimumSquareMiles)
2. {
3.     var queryTask = new QueryTask("http://sampleserver1.arcgisonline.com/ArcGIS/rest/services/Demographics/ESRI_Census_USA/MapServer/5");
4.     queryTask.ExecuteCompleted += QueryAreaTask_ExecuteCompleted;
5.     queryTask.Failed += (sender, args) => MessageBox.Show("Query failed: " + args.Error);
6.  
7.     var query =
8.         new Query
9.         {
10.             Where = "SQMI > " + minimumSquareMiles,
11.             ReturnGeometry = true,
12.             OutFields = new OutFields { "*" },
13.             OutSpatialReference = MapControl.SpatialReference
14.         };
15.  
16.     queryTask.ExecuteAsync(query);
17. }

This time instead of querying by a geometry, we query by a where clause.

QueryAreaTask_ExecuteCompleted just adds the results to graphics feature layer:

1. private void QueryAreaTask_ExecuteCompleted(object sender, QueryEventArgs args)
2. {
3.     var featureSet = args.FeatureSet;
4.  
5.     if (featureSet == null || featureSet.Features.Count == 0)
6.         return;
7.  
8.     foreach (var feature in featureSet.Features)
9.     {
10.         feature.Symbol = LayoutRoot.Resources["ResultsFillSymbol"] as FillSymbol;
11.         _selectionGraphicsLayer.Graphics.Add(feature);
12.     }
13. }

And that’s it. The result looks like this:

The source code can be found here.

On the next post I will walk you through on how to turn this application from a code behind application to a MVVM patterned application.

 

Keywords: Silverlight, ESRI

IceRocket Tags: Silverlight,ESRI

TeamCity: Building the Solution

Summary: Adding a solution and building it using TeamCity and MsBuild

This is the second post in my TeamCity series, the first post is TeamCity: Easy Continuous Integration

After going through the setup process, and setting up the admin user this page will open up:

Here simply click on Create project.

In this demo I am going to add my CodePlex solution. Note that if you have a serious Open source project you can apply for a hosted Continuous Integration server at TeamCity.CodeBetter.com (more about it here).

Simply click on “add a build configuration”.

Here the only thing you must do is add a name.

Option you can consider changing: “Fail build if: it runs longer that _ minutes”. Though there is a detection on if a build hangs I had once received a Unit Test(integration) failure for a test that had a timeout after an hour because the service it tried to access hanged. Because of this I think you should wait and see what your average build time is, add to it 5 minutes and set that number here.

Before setting the Source Control folder we should really set our server.

These are the options of available Source Control systems, in this case I am going to setup Team Foundation Server (TFS).

Pretty straightforward set the VCS root name otherwise TeamCity generate a folder with a weird name. If in the setup phase you entered a SYSTEM account (like me) than please enter your user/password. If this TFS server is used in several solutions you would like to add that mark the V in “Make this VCS root available to all of the projects”.

Click on “Test connection” to test your settings, you should get a popup such as:

Close the popup and click on save.

“Checkout directory” is a bit misleading this is just the name of the local folder that will be used by TeamCity.

Note that if your build changes the source files in anyway you should check “Clean all files before build” (though this step will cause a slower build). Personally I found it best on the first couple of runs to check this option on since it ensures the solution has all the files (and you never know what build action someone else has set that might hurt your build).

Continue to “Add Build Step”.

The next step is setting up the build with MsBuild, you can give the step a name though it is optional, in “Build file path” enter the path to the solution file from VCS root (from the setting of TFS Source Control). In my case the file is under the folder DllShepherd so I entered DllShepherd\DllShepherd.sln.

Now simply enter the solution .net version, in my case .net 4.0. The ToolsVersion could always be set to 4.0 (if you have .net 4.0 installed) since MsBuild supports compilation to older versions.

Click on “Save” to continue.

On the right side:

Click on “Build Triggering”.

Here we are going to add two triggers:

1. Changes in the Source Code:

Quiet period mode: wait 60 seconds for other changes before building

In “Trigger rules” you can setup that the build won’t be triggered if the only change was in a html file:

-:**.html

You can read more about other options here.

2. Schedule – every day at 3 AM:

The only change (apart from the time) to the trigger was unchecking “Trigger build only if there are pending changes” since we want the build to run everyday (and we already have a build when the source control is changed).

The next section on the right panel is for Dependencies to other projects. I didn’t have any but it seems easy enough to set. You can even skip it all together by creating a large solution that contains all of your projects (though build times will suffer).

All the other sections on the right panel can be kept on their default values.

The only thing remaining is clicking on “Run” to test the settings:

 

My first run failed with this error:

(396, 5): Running transformation: System.Exception: T4MVC can only execute through the Visual Studio host

Since I used T4 build with TextTemplating that is support in VS but not in MsBuild. Removing it, checking “Clean all files before build” (since the T4 build deleted one of the source control files – because it tried to recreate it and failed)  and reconnecting to the internet (for some odd reason my connection died):

And that it.

 

Note: Though I didn’t show it TeamCity is not just for .Net:

 

The next post will be on adding Unit Tests to the mix (I promise it’s going to be shorter).

 

Resources:

Announcing TeamCity.CodeBetter.com

TeamCity MsBuild help

 

IceRocket Tags: TeamCity,MsBuild,Continuous Integration

TeamCity: Easy Continuous Integration

Summary: installing TeamCity 6.5 on a new server

Two weeks ago I had a friend drop down to a visit who suggested I give TeamCity a go. I have been complaining that my IT department has a build server that didn’t run Unit Tests (in fact they wanted me to setup the running of Unit Tests for them).

It took me less than a day to set it all up (most of that time spent playing around with the settings). If I had to set a new project today I guess it will take at the maximum an hour (and that assuming I can’t just copy my settings from the existing project).

In this post I will walk you through the setup process and in the next couple of posts adding a solution build and running Unit Tests.

---

I find it important to note that you should start from a computer that has all the applications that a production/test Server/Client needs. Moreover if you are using in your build Toolkits (or other third party tool with installations) then you should have them installed on the build server. I have decided to start from a machine that can be used as a Developer workspace meaning it has VS2010 installed but having VS installed is not a must.

Download the installation from here.

Continue with yes, read the license agreement and if you agree with it continue the installation (and this guide)

Install all the items.

I have chosen to change the installation directory since the users folder of windows is backed up on a central machine and I want TeamCity to work on only one machine. So I changed it to:

C:\TeamCity\.BuildServer

Which is inside the installation folder.

Wait until the installation is complete…

Here I have chosen to install TeamCity on port 8080 instead of 80, since I already use port 80 in my application with IIS. TeamCity will also warn you if it detects that the port is in use.

On the next screen click Save.

Since I don’t actually have a dedicated IT user I have chosen to use the SYSTEM account –> next.

Start all the services –> next.

And that’s it – all done.

When running the web site for the first time you will have to read the license agreement and if you agree with it continue on…

The second thing to do is set an administrator account.

 

In the next post I will show you how to add your solution and build it using TeamCity and MsBuild.

 

IceRocket Tags: TeamCity,Setup,Continuous Integration

Geographic Database: ESRI File Formats

ESRI gave us two types of file GeoDatabases: File GeoDatabase and Personal GeoDatabase.

So far at all the companies I worked at we used them for upgrading the real database (SQL Server) or as a backup for the Geographic data. Until today Personal GeoDatabase was the best choice for that purpose (we even backed up the file on the TFS).

I have decided to compare the two types:

	File GeoDatabase	Personal GeoDatabase
ESRI Description	“A collection of various types of GIS datasets held in a file system folder.(This is the recommended native data format for ArcGIS stored and managed in a file system folder.)”	“Original data format for ArcGIS geodatabases stored and managed in Microsoft Access data files.(This is limited in size and tied to the Windows operating system.)”
Storage Format	“Each dataset is a separate file on disk. A file geodatabase is a file folder that holds its dataset files.”	“All the contents in each personal geodatabase are held in a single Microsoft Access file (.mdb).”
Size Limits	“One TB for each dataset. Each file geodatabase can hold many datasets. The 1 TB limit can be raised to 256 TB for extremely large image datasets. Each feature class can scale up to hundreds of millions of vector features per dataset.”	“Two GB per Access database. The effective limit before performance degrades is typically between 250 and 500 MB per Access database file.”
Structure	a file + a folder full of files	one file in access format
adding/removing a feature	the files in the folder change (added or deleted)	change in the access data
editing the geographic data	change in the files	change in the access data
Performance	better then personal but worse from SDE	not good but better than shape
Storage in TFS (or any other source control system)	difficult, for each change you should delete the folder and add it again to the TFS (the files in the folder may be deleted or created when changes occur)	easy, simply check out the mdb file (be sure not to check in the lock file)
Getting IWorkspace for the GeoDatabase	fileGDBWorkspaceFactory.Create(path, "Sample.gdb", null, 0);	accessWorkspaceFactory.Create(path, "Sample.mdb", null, 0);

Quotes come from the ESRI site. ESRI compared some more things like number of users, versioning, platform and security but I consider it an overkill for a file GeoDatabase.

Today I am working on a bigger project (something a bit worldwide with a lot of data) and 2GB is just not enough. The good thing is that we probably won’t use the TFS to backup the data but actual IT systems.

Resources:

ESRI: Types of geodatabases

 

Keywords: file, GeoDatabase, database, access, mdb, source control, IWorkspace, ESRI, Sde

IceRocket Tags: file,GeoDatabase,database,access,mdb,source control,IWorkspace,ESRI,Sde

ArcObjects: Getting Started

This post is the second post in my ArcObjects series.

I have been an ESRI developer for the past ~5 years and in that time the biggest hurdle of all was the fact that ESRI uses COM objects. I don’t know how many times I got a COM Exception but they numbered at least in the hundreds if not the thousands (many of those could only be fixed by trial and error since the internet didn’t have the answer).

COM objects for those who don't know are not managed .NET code they are classes that are accessed through their interfaces, for example the Feature class implements IFeature, IRow and IValidate (at least according to the literature) but actually Feature implements only IRow:

The class that actually implements IRow and IValidate is FeatureClass_2:

But don't use it!
Why not?

Because it's name suggest that in the next version ESRI will call it FeatureClass_3.
For this reason you shouldn't work directly with the Classes in ArcObjects  - use the interfaces instead.

 

Another thing that ESRI just loves is adding interfaces. A good example for this is PolylineClass:

This are only some of it's interfaces as you can see ESRI (and probably most COM modules) like to add interfaces with each version, for that reason we have six IGeometry interfaces which inherit from each other:

Meaning that you can use IGeometry5 and get all the methods from IGeometry.

This is not a rule though if you look at ITopologicalOperator6 you will find out that it doesn't inherit or at all connected to ITopologicalOperator:

So be careful and use the minimum interface that you may need.

Because we are using COM objects and COM is a resource we should dispose each object after we are done with it, there are a few ways of doing this:

1. The manual way:

1. System.Runtime.InteropServices.Marshal.ReleaseComObject(cursor);

You release the COM object when you are done using it.

2. The managed way:

1. using (var comReleaser = new ComReleaser())
2. {
3.     // Create insert feature cursor using buffering.
4.     var featureCursor = featureClass.Insert(DefaultUseBufferingInInsert);
5.     comReleaser.ManageLifetime(featureCursor);
6.  
7.     //...Use the cursor in some way
8. }

At the end of the Using scope the resource is released.

I personally prefer the second way, but to each his own…

 

Next: ArcObjects: Extending the Framework

References:

Introduction to COM for ArcObjects developers

Releasing COM references

ArcObjects Resources

 

Keywords: ArcObjects, ComReleaser

IceRocket Tags: ArcObjects,ComReleaser