Program in WPF C# using .mdf database with LINQtoSQL freezes for a bit when I load the database

Question

OK I have the following code that I run inside a button clicked event in order to compare the username and password given by the user.

public static bool isAuthenticated(string Username, string Password)
    {
        //Open a connection with the database
        using (WHDataDataContext db = new WHDataDataContext())
        {
            //Compare the Username and the password and return the result
            return db.Users.Any(check => check.Username == Username && check.Password    == Cryptographer.Encrypt(Password));
        }
    }

My problem is that when I hit the button the program freezes for a moment and the it responds.

I have used this code on a c# application with .sdf file (SQL CE) and I haven't experienced this issue.

Could anyone please help?

Without any more info, my best guess is that `WHDataDataContext` takes a while to load its entities which is why the application freezes. In cases like this, you should consider multi-threading your application. — Arian Motamedi, Aug 01 '14 at 18:36
Sadly threading does not work. I have tried everything that the kind people bellow suggested and I still get the same result. Is there anyway to open and close the database faster? I mean at the moment my database has only one record in it... — GregoryGR, Aug 01 '14 at 20:36
`threading does not work` how's so? Try running it in `Task.Run()`. It shouldn't block the UI. — Arian Motamedi, Aug 01 '14 at 20:40
I have done that as well, I used the Task.StartNew method (because the Task.Run() is older) and I still got the same result. The 1st time that the program actually tries to read from the database it hangs, if I type for example the username or password wrong it hangs and then it gives me the result after 1-2 seconds, after the 1st time the result is instant. — GregoryGR, Aug 01 '14 at 20:43
Strange. Without any more code or info, it's hard to tell. On a side note, `Task.Run()` isn't "old". It guarantees that the delegate gets executed on a different thread. `Task.StartNew()` doesn't guarantee this. — Arian Motamedi, Aug 01 '14 at 21:34

score 0 · Accepted Answer · answered Aug 01 '14 at 18:36

0

It's because you are doing a blocking activity (like database query) in the UI Thread. Try to use BackgroundWorker

using (var worker = new BackgroundWorker())
{
    worker.DoWork += (theSender, theArgs) =>
    {
        theArgs.Result = isAuthenticated(userName, password);
    };
    worker.RunWorkerCompleted += (theSender, theArgs) =>
    {
        bool isValid = (bool)theArgs.Result;
        if(!isValid)
        {
            MessageBox.Show("Not Authenticated!!");
        }
    };
    worker.RunWorkerAsync();
}

answered Aug 01 '14 at 18:36

Yuliam Chandra

14,494
12
52
67

Is there anyway to implement the worker on the query instead like the async Task solution? – GregoryGR Aug 01 '14 at 19:46
@user3900447, yes it's possible, but you need to change how you will consume the method – Yuliam Chandra Aug 01 '14 at 19:48
I will test it :) If I remember correctly background worker exists on framework 4.0 right? – GregoryGR Aug 01 '14 at 19:50
@user3900447, fortunately `BackgroundWorker` has been there since 2.0 – Yuliam Chandra Aug 01 '14 at 19:51
I have implemented it in isAuthenticated method but now the program doesn't wait for the result and simply returns false... I have tested it your way as well and it pauses again since the actual problem is that it probably waits for the database authentication :( – GregoryGR Aug 01 '14 at 20:04
Also now that I implemented the worker in the loginform where I call the DatabaseControls.isAuthenticated (the isAuthenticated was a static method in an other class) the compiler says that there is an unhandled exception because the method is a part of a different thread already :S – GregoryGR Aug 01 '14 at 20:22
yes it's the expected result, after calling the isAuthenticate, the program will continue and not blocking the UI thread, that's why the UI will not freeze, you need to move other codes that depend on the result to `RunWorkerCompleted` – Yuliam Chandra Aug 01 '14 at 20:24
I have done it. When I do move the other code in the RunWorkerCompleted I get the same result as before. That result is the program to freeze for 1-2 seconds and then respond. Is there some way to connect to the database and fetch the entries faster? Should I not use the using (DataContext db = new DataContext())? – GregoryGR Aug 01 '14 at 20:40
you need to post more code related to the button click, no, it's fine using `using` – Yuliam Chandra Aug 01 '14 at 20:42

score 0 · Answer 2 · answered Aug 01 '14 at 18:54

0

Try using async task instead.

public static async Task<bool> isAuthenticated(string Username, string Password)
{
    return await Task.Factory.StartNew<bool>(()=>{
    //Open a connection with the database
    using (WHDataDataContext db = new WHDataDataContext())
    {
        //Compare the Username and the password and return the result
        return db.Users.Any(check => check.Username == Username && check.Password    == Cryptographer.Encrypt(Password));
    }
    }
}

answered Aug 01 '14 at 18:54

kidshaw

3,423
2
16
28

Sadly I am not using framework 4.5 :( I am using framework 4.0 so that the application can work on windows xp – GregoryGR Aug 01 '14 at 19:40
I have used the Task without the async and await and I got the same result as before. I think that the problem is the database's authentication or something like that. The weird part is that I didn't have the same problem when I did the exact same thing with an .sdf file instead. – GregoryGR Aug 01 '14 at 20:35
Did it, got the same result :( – GregoryGR Aug 01 '14 at 20:44

score 0 · Answer 3 · answered Aug 01 '14 at 20:42

0

Why not create the DB context once when the app starts? If it is the datacontext initialization that incurs the delay, move it to where your application starts instead.

Since it is a WPF app it is presumably long running so no need to create a new DB context for each database retrieval.

answered Aug 01 '14 at 20:42

user469104

1,206
12
15

I have read that this is not a good approach, do you think that I should do that instead? – GregoryGR Aug 01 '14 at 20:46
I'd add a splash screen if you do, just to stop the user launching the app again before it initialises. – kidshaw Aug 01 '14 at 21:03
@GregoryGR - Is all access to the database through your WPF application? If so, creating the DB context once should be fine. – user469104 Aug 01 '14 at 21:09
Yes it is. I will try it right now, I will create the Context once and I will make all my methods that need a context ask for it.\ – GregoryGR Aug 01 '14 at 21:13
I have created the DB context in the application's initialized and I got the same effect. It seems that the application freezes when it runs the first query. – GregoryGR Aug 01 '14 at 21:25
@GregoryGR - OK, then it is not the DB context creation that is the issue but rather the retrieval. Where is the database physically located, is it a local database? How many records are in the Users table? – user469104 Aug 01 '14 at 21:51
It is a local database and it has only 1 record in Users table (lol). – GregoryGR Aug 01 '14 at 22:16

Program in WPF C# using .mdf database with LINQtoSQL freezes for a bit when I load the database

3 Answers3