caching a simple Username to UserId mapping to ease on the db

hey, I have a c# server-side app that has db (mysql), and quite oftenly, due to my DB current schema, i have to retrieve a user's id by his username in order to manipulate the user's data (such as editing his profile db tables etc.) .

I was thinking of creating a simple cache class that when it handles a request for a specific username to userid mapping, it caches the result in a dictionary, or even in a dedicated dictionary for the first character of the given username.

Here is an example of what i was thin开发者_如何学Cking:

public class UsernameToIdCache
{
    public int GetUserIdFromUsername(string username)
    {
        if (string.IsNullOrEmpty(username)) return -1;

        var targetDictionary = _cacheDictionaries[username[0]];
        int id;
        if (targetDictionary.TryGetValue(username,out id))
        {
            return id;
        }
        else
        {
            id = RunSqlQuery("select id from users where username ='" + username + "'");
            if (IsDictionaryNotFull(targetDictionary))
            {
                targetDictionary.Add(username,id);
                return id;
            }
        }
    }
}

the IsDictionaryNotFull(Dictionary<int,string>) method is used for memory management (i don't want the dictionaries to explode, so it can only help until the dictionaries are full) .

what do you think? Is it a good db coding practice ?

---

Is it ASP.NET app? If so, consider putting ID into Session object. It will be loaded once the user logs in and no other magic cacheing is needed.

---

The idea of caching is a good idea, especially if the volume of transactions is very high. The key piece here is to remember that ASP.NET applications are multi-threaded, so you will want to be locking access to that dictionary when you do the writes, as you don't want two threads trying to write the same data into the dictionary.

But overall I'd say if you need/want the performance gain of caching, then go for it.