New blog

It will most likely sound like a weird decision but I have started to blog in a new blog, where the weird part is, it’s also a WordPress.Com hosted blog. Anyway, the new address is: http://danielwertheim.se

Cheers,

//Daniel

Fleck.Extended

Why?

When I started to consume Fleck it just lacked some of the features (which is also what I like about Fleck, it’s simple and bare bone and easy to get started with) I wanted and I also wasn’t sure I would go with Fleck, so I created a small layer sitting on top of Fleck.

Continue reading

SisoDb – Using DbPipe to add compression

In v16.1.0 of SisoDb I’ve added a new interface SisoDb.IDbPipe which lets you hook into the process of writing and reading the string-data representing a document (default JSON) to and from the DB. You could use that for encryption, compression, ….? Now I will just show you how to add compression.
Continue reading

SisoDb – Cacheable queries

This weekend I just release SisoDb v16.1.0 and about a week before that v16.0.0. I thought we might take a look at some new features with regards to caching. From version 16.0.0 of SisoDb you can not only make use of a cache provider for GetById, GetByIds but also for queries. First you need to enable caching. You can easily write one on your own, by implementing SisoDb.Caching.ICacheProvider and SisoDb.Caching.ICache. But for this post we will use an existing one, provided via NuGet.
Continue reading

Please, don’t make me waste a click on your GitHub repo

Every now and then (daily) I end up at some project page at GitHub, and almost always I click the “Wiki” link to read up on the project, just to get a sense about what it does and if it can help me. I’ve stopped counted the number of times I end up visiting a blank wiki or not getting to a wiki at all. And often there’s also no links on the landing page and sometimes there are links to an old blog. My immediate reaction is:

This is not a serious project.

I don’t expect you to have an external site or be using GitHub pages, but I do expect you to either use the wiki or remove it. It’s to easy to turn off for you to ignore it.

Continue reading

SisoDb v16.0.0 is out

I’ve been working on some changes lately on SisoDb. Mostly it’s been about cleaning the code base up but also bring in support for spatial data as well as cachable queries. There’s a bunch of changes and I will cover some of the here soon. You can have a check at the release notes here: http://sisodb.com/wiki/release-notes

While typing this I’m currently running an app that uses these new features, against a SQL Azure database. Once you have deployed the DB, SisoDb seems to work fine. Not tested all the features though.

//Daniel

SisoDb – Simple tip on how to write more performant queries

Time for a really quick tip when it comes to queries in SisoDb that will give you better performance. When firing of a query with lots of parameters in your expression, move the logic into your model instead. That way the expression only has to lookup one index-value for your structure, and it will probably also state what you are doing. Example

session
    .Query<WorkOrder>()
    .Where(w => w.Status == WoStatus.Done || w.Status == WoStatus.Trashed);

should be

session
    .Query<WorkOrder>()
    .Where(w => w.IsClosed);

This was just a super simple example, but really. Try and get it down to one param. You will gain performance.

//Daniel

Writing own libs for SisoDb – SisoDb.Spatials

I’m in the process of finalizing a v16.0.0 of SisoDb and there’s a first pre-release out on NuGet (SQLCE4, SQL2005, SQL2008, SQL2012). I’m will soon get a pre-release 2 out which have one simple change in code but it allows you to write third party libs to SisoDb. Almost anything can allready be swapped out for your implementations. But what if you just want to provide some new functions via a NuGet? Lets say I would like to create SisoDb.Spatial and provide some simple helpers for spatial data. You should then extend the ISession and make use of the Session.ExecutionContext. It exposes all you need via the Session, but more importantly, exposes two Try methods that you should wrap your methods within. Why? Well SisoDb uses Auto-commit and rollback on exception, but to be sure states are updated correctly, the exception should be caught by that specific try methods.

Lets look at an example. First I extend ISession and pass along the ExecutionContext (available from v16.0.0-pre2)

public static class SessionExtensions
{
    public static ISisoSpatials Spatials(this ISession session)
    {
        return new SqlServerSisoSpatials(session.ExecutionContext);
    }
}

Lets define some operations

public interface ISisoSpatials 
{
    void UpsertFor<T>() where T : class;
    void DropFor<T>() where T : class;
    bool PointExistsInPolygonFor<T>(object id, Tuple<double, double> coords, int srid = SpatialReferenceId.Wsg84) where T : class;
    void DeleteGeoIn<T>(object id) where T : class;
    void InsertPolygonTo<T>(object id, Tuple<double, double>[] coords, int srid = SpatialReferenceId.Wsg84) where T : class;
    void UpdatePolygonIn<T>(object id, Tuple<double, double>[] coords, int srid = SpatialReferenceId.Wsg84) where T : class;
    void SetPolygonIn<T>(object id, Tuple<double, double>[] coords, int srid = SpatialReferenceId.Wsg84) where T : class;
}

Now just implement it and make use of the API exposed by the ExecutionContext.

public class SqlServerSisoSpatials : ISisoSpatials
{
    protected readonly ISessionExecutionContext ExecutionContext;
    protected ISession Session { get { return ExecutionContext.Session; } }
    protected readonly ISqlStatements SqlStatements;

    protected internal SqlServerSisoSpatials(ISessionExecutionContext executionContext)
    {
        ExecutionContext = executionContext;
        SqlStatements = SpatialSqlStatements.Instance;
    }

    public virtual void SetPolygonIn<T>(object id, Tuple<double, double>[] coords, int srid = SpatialReferenceId.Wsg84) where T : class
    {
        ExecutionContext.Try(() =>
        {
            var schema = Session.GetStructureSchema<T>();
            var sidParam = CreateStructureIdParam<T>(id);
            var geoParam = CreatePolygonParam(coords, srid);
            var sql = SqlStatements.GetSql("SetGeo").Inject(GenerateTableName(schema));
            Session.DbClient.ExecuteNonQuery(sql, sidParam, geoParam);
        });
    }

    //...
    //Some stuffe left out.
    //...

    protected virtual string GenerateTableName(IStructureSchema schema)
    {
        return string.Concat(DbSchemaNamingPolicy.GenerateFor(schema.Name), "Spatials");
    }

    protected virtual DacParameter CreateStructureIdParam<T>(object id) where T : class
    {
        return new DacParameter("id", id);
    }

    protected virtual GeographyDacParameter CreatePointParam(Tuple<double, double> coords, int srid)
    {
        return new GeographyDacParameter("geo", SqlGeography.Point(coords.Item1, coords.Item2, srid));
    }
}

That’s pretty much it.

//Daniel

Windows 8 – Windows defender, adding a Scan with option

I’ve been using Microsoft Security Essentials on my previous Windows installations, but now with Windows 8, the Windows Defender is built in and automatically scans files. I’m one of those persons who like to have a bit of control and really want to be able to right click a file or folder in Explorer and say “Scan this”. This isn’t an option anymore. But it’s really easy to fix.

Create a bat file and name it something. I’ve created one: “Scan with.bat”. Add the following contents:

"C:\Program Files\Windows Defender\MpCmdRun.exe" -Scan -ScanType 3 -File %1
pause

Place the bat file somewhere and then open up the SendTo directory located under AppData for your user account. E.g

"$User$\AppData\Roaming\Microsoft\Windows\SendTo"

and just add a shortcut there to your bat file. Now you can right click files and folders in explorer and scan your files.

You could probably do this in 1001 ways that are more nifty. But this took less then 5min to figure out and I don’t get a rush of making registry hacks etc. Works for me.

//Daniel

SisoDb and SQL Server Express 2012, LocalDB

This is a verry short introduction to SQL Server Express 2012, LocalDB, and I’ll show you how to use it in conjunction with SisoDb (Simple, Structure, Oriented DB).

Edit: The intentions of LocalDb is as I’ve understood it, for developers and local use. But there are some interesting readings about other scenarios, e.g: http://blogs.msdn.com/b/sqlexpress/archive/2011/12/09/using-localdb-with-full-iis-part-2-instance-ownership.aspx#SharedLocalDB

Also, I’ve been getting questions about how it compares to other editions. Instead of rewriting what’s allready been written: http://blogs.msdn.com/b/jerrynixon/archive/2012/02/26/sql-express-v-localdb-v-sql-compact-edition.aspx

For impatient readers

This section is for you that just want to get started, this is what I will go through:

Step 0: If you have Visual Studio 2012 RC, you probably allready have SQL Server Express LocalDb installed. Check under “Program and Features“. If you do, feel free to skip ahead to Step 3.

Step 1: If you aren’t already a lucky user of Chocolatey, install it by running this in a command prompt:

@powershell -NoProfile -ExecutionPolicy unrestricted -Command "iex ((new-object net.webclient).DownloadString('http://bit.ly/psChocInstall'))" && SET PATH=%PATH%;%systemdrive%\chocolatey\bin

Step 2: Install SQL Server Express 2012, LocalDb using this Chocolatey package:

cinst SqlServerLocalDb

Step 3: Use it with SisoDb: Install the Sql2012 provider.

install-package SisoDb.Sql2012
using System;
using System.Collections.Generic;
using SisoDb.Sql2012;

namespace DummyConsole
{
    class Program
    {
        static void Main(string[] args)
        {
            var db = @"data source=(localdb)\v11.0;
                       initial catalog=Dummy;
                       integrated security=SSPI;".CreateSql2012Db();
            db.CreateIfNotExists();

            foreach(var message in AskForMessages())
                db.UseOnceTo().Insert(new Message { Text = message });
            
            Console.WriteLine("You have said:");
            foreach (var msg in db.UseOnceTo().Query<Message>().ToArray())
                Console.WriteLine(msg.Text);
        }

        private static IEnumerable<string> AskForMessages()
        {
            while (true)
            {
                Console.WriteLine("What would you like to say?");
                var message = Console.ReadLine();
                if (string.IsNullOrEmpty(message))
                    yield break;

                yield return message;
            }            
        }
    }

    public class Message
    {
        public Guid Id { get; set; }
        public string Text { get; set; }
    }
}

Note! LocalDb can be a bit slow the first time, since it might need to create the instance and then spin up SQLServr.exe as a child process. Once the instance and the Db is created it’s better.

The longer version

That was the quick start version. Now lets take it from the beginning. I stumple upon some users of SisoDb using the SQL Compact provider locally, just because they don’t have SQL Server Express edition or higher installed, since it’s “hard to setup“. I don’t know about that, but Microsoft has listened to you devs out there and with SQL Server 2012 (Denali) they released a concept of LocalDb. I’m not going to go into the details of it, but I would like to emphasize a bit on some aspects found in the docs:

  • LocalDB installation copies a minimal set of files necessary to start the SQL Server Database Engine.
  • Supports both private and shared instances.
  • The instance collation for LocalDB is set to SQL_Latin1_General_CP1_CI_AS and cannot be changed.
  • LocalDB cannot be a merge replication subscriber.
  • LocalDB does not support FILESTREAM.
  • LocalDB only allows local queues for Service Broker.

Install not needed?

If you are sitting on Visual Studio 2012 RC, you probably got it installed from the beginning. Otherwise, there are traditional downloads or probably simpler, you could use Microsoft Web Platform Installer. But lets introduce another way (just for fun and simplicity). Lets have a look at Chocolatey.

Install using Chocolatey

Since the installation of LocalDb from Microsoft, should be really simplified, I thought it would be a perfect fit for Chocolatey. After a quick ping to Rob Reynolds (Mr Chocolatey), it’s now present there. Let’s try it. Since I never have used Chocolatey before, I needed to install it. I just fired up a command prompt and ran the command found on the landing page of http://chocolatey.org, which as of this writing was:

@powershell -NoProfile -ExecutionPolicy unrestricted -Command "iex ((new-object net.webclient).DownloadString('http://bit.ly/psChocInstall'))" && SET PATH=%PATH%;%systemdrive%\chocolatey\bin

After that, I just went to the page for LocalDB on Chocolatey and ran the below in a command prompt:

cinst SqlServerLocalDb

Which will result in something looking like this.

That’s it. You now have a SQL Server Express LocalDB 2012 installed on your machine.

Lets consume it with SisoDb

Lets just create something really simple to see how the connection string should look like.

using System;
using System.Collections.Generic;
using SisoDb.Sql2012;

namespace DummyConsole
{
    class Program
    {
        static void Main(string[] args)
        {
            var db = @"data source=(localdb)\v11.0;
                       initial catalog=Dummy;
                       integrated security=SSPI;".CreateSql2012Db();
            db.CreateIfNotExists();

            foreach(var message in AskForMessages())
                db.UseOnceTo().Insert(new Message { Text = message });
            
            Console.WriteLine("You have said:");
            foreach (var msg in db.UseOnceTo().Query<Message>().ToArray())
                Console.WriteLine(msg.Text);
        }

        private static IEnumerable<string> AskForMessages()
        {
            while (true)
            {
                Console.WriteLine("What would you like to say?");
                var message = Console.ReadLine();
                if (string.IsNullOrEmpty(message))
                    yield break;

                yield return message;
            }            
        }
    }

    public class Message
    {
        public Guid Id { get; set; }
        public string Text { get; set; }
    }
}

Lets get acquainted with SQLLocalDb-util

If you just installed LocalDb, then open a new command prompt so that the updated environment path setting is honored. To demo this tool, I’m going to create a shared instance. And when creating a shared instance, you need to run the command prompt in elevated mode.

sqllocaldb create MyFooInstance
sqllocaldb share daniel MyFooInstance MySharedFooInstance

We have now created one named instance and also one shared representation of it. Now lets start them.

sqllocaldb start MySharedFooInstance

If you have a look at the screenshot below, you will see that MyFooInstance is “linked” to the shared instance, as it gets started.

You can now use the following connection strings instead:

--Targetting named instance
var cnString = @"data source=(localdb)\MyFooInstance;
           initial catalog=Dummy;
		   integrated security=SSPI;".CreateSql2012Db();
--Targetting shared named instance
var cnString = @"data source=(localdb)\.\MySharedFooInstance;
           initial catalog=Dummy;
		   integrated security=SSPI;".CreateSql2012Db();

Other tools in the path of simplicity

To inspect contents a really simple and slick tool is LinqPad. It’s quite small, about 4Mb.