CODEWITHSUNDEEP
c#sql-serverperformancedatacontext
Tom Gullen
Tom Gullen

Reputation: 61729

OK to establish multiple data contexts?

Given the following code:

public static void SomeLoop()
{
    using(var db = new ArcadeContext())
    {
        var changeRecs = db.ArcadeGameRanks.Where(c => c.Date == today);
        foreach (var rankRecord in changeRecs)
        {
            var rank = SomeMethod(rankRecord.GameID);
            UpdateGamesCatRank(rankRecord.GameID, rank);
        }
    }
}

public static void UpdateGamesCatRank(int gameID, int catRank)
{
    using(var db = new ArcadeContext())
    {
        db.ExecuteCommand("UPDATE ArcadeGame SET CategoryRank = " + catRank + " WHERE ID = " + gameID);
    }
}

When I run the SQL Server Profiler I get a lot of repeating Audit Login and Audit Logout messages which seem to impact performance:

enter image description here

I'm self taught in C#, so I know that there's a good chance I'm doing something non-typically.

My question is, is the above design pattern considered good? Or should I be reusing/passing data contexts as parameters to functions so that they do not need to be re-established each time the function is called? (If my assumption that the repeated creation of new Data Contexts is the cause of the logins and logouts).

Upvotes: 0

Views: 78

Answers (2)

Captain Skyhawk
Captain Skyhawk

Reputation: 3500

Not a good idea. That might have just been an example to illustrate your point but I don't see a need for a whole new method just to execute a sql command. Just put that command in your loop. Instantiating a new db context for each call is going to kill your performance.

Upvotes: 0

David L
David L

Reputation: 33815

Since your context is already instantiated, pass it to your method.

public static void SomeLoop()
{
    using(var db = new ArcadeContext())
    {
        var changeRecs = db.ArcadeGameRanks.Where(c => c.Date == today);
        foreach (var rankRecord in changeRecs)
        {
            var rank = SomeMethod(rankRecord.GameID);
            UpdateGamesCatRank(rankRecord.GameID, rank, db);
        }
    }
}

public static void UpdateGamesCatRank(int gameID, int catRank, ArcadeContext db)
{
    db.ExecuteCommand("UPDATE ArcadeGame SET CategoryRank = " + catRank + " WHERE ID = " + gameID);
}

This will execute your query, return, and resolve by disposing the context when you are done with your foreach loop.

Upvotes: 1

Related Questions