Tag Archives: Databases

Unintended consequences – Transactions

Posted on by

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).

public void Dispose()
{
    this.Dispose(true);
}
 
protected virtual void Dispose(bool disposing)
{
}

Where’s the rollback? Missing. Just wait till your next transaction block.

Looks like it was fixed in 5.1. Srijak and Dellanoce figured this out, but I sniped it from them before they could post. I was standing over their shoulder :)

Database integration testing, Interested?

Posted on by

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.

So I decided to play around with creating an NUnit addin that makes this a lot easier. Does the following code interest anyone?

 
[MySqlDbTestFixture("Data Source=localhost;User ID=root;"), 
RandomDatabaseName, 
ImportFromFile("schema.sql")]
public class SomeTestFixture
{
    [DbTest,
    ImportFromFile("BadCompanyDataInDb.sql")]
    public void SomethingWorksWithBadCompanyData(IDbConnection conn)
    {
        ...
    }
}

I have most of it working, and want to see if there are any developers that might think of using it. Am I on the right track? Any thoughts or ideas for improvement?

(I told you I would have some code in a post this week)

How you should start your next project…

Posted on by 
public interface IRepository<T>
{
    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. There isn’t much value to spending time coding it initially. It is going to change, and you will have to update your repository, schemas, mapping files, etc.

I know I am not the first to offer this advice, but I think reinforcing it is important.

SQL Server Mirroring Starter

Posted on by

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. Still a great article though.
  • Servers should be in a domain – The first time I tried this, I tried it without a domain controller and DNS. It was a pain. Adding the servers to an AD domain makes it a ton easier. Also, most everyone with SQL Server already has an AD domain, so this is a no brainer.
  • SID’s are a pain – In a previous post, I talked about the orphaned user problem that backup restores have. Well, mirroring has the same problem, but a different solution. Since the witness server controls failover, you can’t rely on a script to fix the user mapping when something fails over. Instead you have to create a login with the same SID on both the principal and partner servers. This article talks more about SIDs and the MSDN site has info on CREATE LOGIN syntax.

Once mirroring has been setup correctly once, the next time is a lot easier. I would highly recommend creating deployment scripts that can set up your entire DB environment because SQL Server setup just has too many steps that are easy to forget or miss.

SQL Server 2005: Backup, Restore, and Orphaned Users

Posted on by

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.

Fixing the problem is really simple. Just run the following SQL and substitute and with the appropriate values. I added these lines to all my Powershell scripts that are used to restore a database and it works like a charm.

USE <database_name>;
GO
sp_change_users_login @Action='update_one', @UserNamePattern='<database_user>',  @LoginName='<login_name>';
GO