Restore a SQL Server Backup to LocalDb

I’m experimenting with taking a 3-tier client/server/database application and creating a lightweight client application that uses an embedded database. We use stored procedures and our server application uses SQL Server, so SQL Express LocalDB has potential. The only problem I ran into was restoring a backup taken from SQL Server to LocalDB and it appears that I am not the only one that had problems. (more…)

Read more

Running MongoDB on Windows

MongoDbLogoI’ve been working with MonogDB lately. Getting it setup and running on a development box is easy, but I wanted to document the steps for reference.

Install MongoDB

Download MongoDB from their website. For windows, I prefer the 64-bit Zip file. The MSI installs into Program Files, but I prefer to keep the binaries and the databases together and not have to run as an Admin.

Extract the Zip file to c:\MongoDB

In the MongoDB directory, create a data subdirectory and in it add db and log subdirectories. The bin subdirectory should already be there. Your directory structure should now look like this,

c:\mongodb
    \bin
    \data
        \db
        \log

Configure MongoDB

Add a text file called  c:\MongoDB\mongod.cfg that contains the following,

systemLog:
   destination: file
   path: c:\mongodb\data\log\mongodb.log
   logAppend: true
storage:
    dbPath: c:\mongodb\data\db
net:
   bindIp: 127.0.0.1
   port: 27017

This sets the log and the db path and also sets the port to the default so it is easy to change later. Notice that I am only binding to 127.0.0.1 because my server is currently insecure so I do not want to expose it on the network. For more configuration options, see the documentation.

Test MongoDB

Now that I have it set up, it is time to test the installation. Start it at the command prompt like this,

C:\MongoDb\bin>mongod.exe -f ..\mongod.cfg

Then in another command window, connect to the running server to make sure it is up and running. I am shutting the server down while I am connected to it, thus the errors.

D:\MongoDb\bin>mongo
MongoDB shell version: 2.6.1
connecting to: test
> use admin
switched to db admin
> db.shutdownServer()
2014-06-10T14:28:35.567-0400 DBClientCursor::init call() failed
server should be down...
2014-06-10T14:28:35.571-0400 trying reconnect to 127.0.0.1:27017 (127.0.0.1) failed
2014-06-10T14:28:36.579-0400 warning: Failed to connect to 127.0.0.1:27017, reason: errno:10061 No connection could be made because the target machine actively refused it.
2014-06-10T14:28:36.579-0400 reconnect 127.0.0.1:27017 (127.0.0.1) failed failed couldn't connect to server 127.0.0.1:27017 (127.0.0.1), connection attempt failed
> exit
bye

Install as a service

We now know that our configuration is correct, so now I want to install it as a service so that it is always running,

Start an Administrator command prompt. On Windows 7, press WIN+R, then type cmd, them press CTRL+SHIFT+ENTER. On Windows 8, press WIN+X, then press A.

Now run the command,

sc.exe create MongoDB binPath="C:\MongoDB\bin\mongod.exe --service --config=C:\MongoDB\mongod.cfg" DisplayName="MongoDB 2.6 Standard" start="auto"

You should then see,

[SC] CreateService SUCCESS

You can now start and stop the service with the following commands,

net start MongoDB
net stop MongoDB

If you ever want to uninstall the service, the command is,

sc.exe delete MongoDB

Next Steps

You now have a MongoDB development server running on your local system. It does not require authentication, but is not exposed on the network, only through 127.0.0.1. When you are ready to deploy to production, you will need to set up security and replication.

For development in .NET you will want to start with the Official MongoDB C# driver NuGet package.

Read more

Win32 COLORREF vs .NET Color

I have been migrating a large application from Win32/MFC to .NET and ran into an interesting problem. We store all of the application colors in the database as integers that represent the Win32 COLORREF value. COLORREF is just a DWORD representing the RGB value, so I thought that I could just take the value and get a .NET Color structure using the static FromArgb method.

This didn’t go too well. It turns out that the byte orders are different. COLORREF is in the order 0x00bbggrr and Color is in the order 0xaarrggbb. Notice that the high order byte in .NET is the alpha channel, but in Win32 it is always zero (fully transparent in .NET) Also notice that the byte order is backwards in Win32, Blue/Green/Red compared to the more standard Red/Green/Blue in .NET.

We began by doing the conversion in code using a bit of bit fiddling;

public static Color ConvertFromWin32Color( int color )
{
    int r = color & 0x000000FF;
    int g = ( color & 0x0000FF00 ) >> 8;
    int b = ( color & 0x00FF0000 ) >> 16;
    return Color.FromArgb( r, g, b );
}

This worked well at first, but it was error prone converting between the values. As new code was written, we often forgot that we needed to convert. Finally we decided to do the right thing and convert the values in the database. To do this, I wrote a simple upgrade script for our SQL Server database that consisted of one method which was then applied to each column in the database that contained color values.

-- =============================================
-- Author: Rob Prouse
-- Date: 15/08/07
-- Description: Convert control colors from
-- Win32 COLORREF (0x00BBGGRR) values to .NET
-- Color Values (0xFFRRGGBB)
-- =============================================
CREATE FUNCTION SwapColorBytes( @Color int )
RETURNS int
AS
BEGIN
    RETURN 0xFF000000 +
    ((@Color & 0x00FF0000)/0x00010000) +
    (@Color & 0x0000FF00) +
    (( @Color & 0x000000FF)*0x00010000)

END
GO

-- Do the following for every column in the DB that contains colors
UPDATE mytable SET colorcolumn = SwapColorBytes(colorcolumn)
GO

Read more