Databases

What’s wrong with the following code? using (var transaction = session.BeginTransaction()) { session.Save(something); transaction .Commit(); } Nothing if you are using MySQL connector 6.0, which has the following Dispose method on its MySqlTransaction class. protected override void Dispose(bool disposing) { if ((((this.conn != null) && (this.conn.State == ConnectionState.Open)) || this.conn.SoftClosed) && this.open) { this.Rollback(); } base.Dispose(disposing); } But if you are using 5.0.9, check out this code (which is actually from the underlying DbTransaction object). »

Testing database queries is super important, but not easy enough. It can be done, but usually you have to write custom test fixtures and spend too much time doing it. Every project needs to test database integration, so why isn’t there a library out there to make it easier? NDbUnit seems somewhat dead, and only works on a specific set of database providers. Even worse, it relies on a ton of XML setup, which I hate. »

public interface IRepository { T Get(object id); void Update(T entity); void Add(T entity); } public interface IEntity { object Id { get; } } public abstract class EphemeralRepository<t> : IRepository<t> where T : IEntity { private IDictionary<object, T> storage; public EphemeralRepository() { this.storage = new Dictionary<object, T>(); } public T Get(object id) { return (this.storage.Keys.Contains(id) ? this.storage[id] : default(T)); } public void Update(T entity) { this.storage[entity.Id] = entity; } public void Add(T entity) { this.storage.Add(entity.Id, entity); } } The data layer can quickly become a time sink, especially since the amount of change in a new project is so high. »

The past few days, I have been playing around with mirroring on SQL Server. Even though there is a GUI to guide you through setup, it isn’t that easy until you understand a few things. I won’t go into details since there are tons of great resources to help setting up mirroring, I will just point you towards some of those resources. The step-by-step guide - This has most of the info you need for mirroring, but it is way too long. »

Backing up a SQL Server database and restoring it to another server is a common operation done for reporting, testing, or development. When restoring on a different machine, a common problem is the login principals don’t actually correlate to the restored database users. This is because they are linked by an SID. This SID isn’t the user name, its some hash value, probably a GUID. The problem is fully described in this article. »

Today I wrote my first MsBuild script that uses sqlcmd.exe to execute SQL Server commands remotely. It is awesome. Although it is fairly limited, for tasks like backing up databases, it is very simple and easy. <target name="Backup"> <exec command="sqlcmd -S $(server) -q "$(sql)""></exec> </target> »