Form gets locked up when connecting to database

  • (4 Pages)
  • +
  • 1
  • 2
  • 3
  • Last »

49 Replies - 3957 Views - Last Post: 24 August 2014 - 10:53 PM Rate Topic: -----

#1 martynball  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 128
  • Joined: 21-July 14

Form gets locked up when connecting to database

Posted 17 August 2014 - 01:22 PM

Hey, i'm trying to connect to a SQL Server Database, but when I click my button1 my entire form just locks up and nothing happens! I have to click the "Stop" button to stop the form from running in Visual Studio! I'd at least expect to receive an error if some of the connection string was wrong.

Here is my code:
private void button1_Click(object sender, EventArgs e)
        {
            connection();
        }
        public void connection()
        {
            SqlConnection myConnection = new SqlConnection();
            myConnection.ConnectionString = "user id=System;" +
                                            "password=password;" +
                                            "Trusted_Connection=yes;" +
                                            "Data Source=localhost;" +
                                            "Database=JamSnaps;" +
                                            "Connection Timeout=30";
            try
            {
                myConnection.Open();
            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
            }
        }


This post has been edited by martynball: 17 August 2014 - 02:41 PM


Is This A Good Question/Topic? 0
  • +

Replies To: Form gets locked up when connecting to database

#2 farrell2k  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 874
  • View blog
  • Posts: 2,706
  • Joined: 29-July 11

Re: Form gets locked up when connecting to database

Posted 17 August 2014 - 09:45 PM

Stop accessing your database on the UI Thread. Use a BackgroundWorker.
Was This Post Helpful? 1
  • +
  • -

#3 Skydiver  Icon User is online

  • Code herder
  • member icon

Reputation: 5928
  • View blog
  • Posts: 20,267
  • Joined: 05-May 12

Re: Form gets locked up when connecting to database

Posted 17 August 2014 - 09:48 PM

Well, you did set a 30 second time out. After 30 seconds, does the program unfreeze?

Anyway, this is not a C# question. Sounds more like a database configuration question. I would take a look at the SQL configuration first to make sure that TCP/IP is enabled, and then follow up with making sure that your firewall(s) is(are) setup to have an opening for the TCP/IP port for SQL.
Was This Post Helpful? 0
  • +
  • -

#4 Skydiver  Icon User is online

  • Code herder
  • member icon

Reputation: 5928
  • View blog
  • Posts: 20,267
  • Joined: 05-May 12

Re: Form gets locked up when connecting to database

Posted 17 August 2014 - 11:38 PM

View Postfarrell2k, on 18 August 2014 - 12:45 AM, said:

Stop accessing your database on the UI Thread. Use a BackgroundWorker.


But where does one draw the line for doing work on the UI thread vs. in the background thread?

The Windows registry is a database. Often it's quick, but it can and does slowdown. (This is why after Windows 95, there was a big backpedaling from Microsoft's "Don't use .INI files, use the registry instead" to "Use configuration files - .INI or .CONFIG - because it lets you do xcopy deployments.") Should the registry also be accessed on a background thread?

How about calling the Windows Installer APIs to see if a required component in installed or not so that you can light up menu items? The Windows Installer APIs at times will consult its database of installed components. Should that done on the UI thread when the popup menu is about to be drawn? Or should a background worker be started when the popup menu is about to be drawn and the menu item will go from disabled to enabled eventually? Or should the background worker be fired off the minute the program starts up in anticipation that the popup menu may come up?

Personally, I suggest doing profiling and see where the bottlenecks are, and then see how that impacts the user experience.
Was This Post Helpful? 0
  • +
  • -

#5 tlhIn`toq  Icon User is offline

  • Xamarin Cert. Dev.
  • member icon

Reputation: 6507
  • View blog
  • Posts: 14,379
  • Joined: 02-June 10

Re: Form gets locked up when connecting to database

Posted 18 August 2014 - 06:25 AM

View PostSkydiver, on 18 August 2014 - 12:38 AM, said:

View Postfarrell2k, on 18 August 2014 - 12:45 AM, said:

Stop accessing your database on the UI Thread. Use a BackgroundWorker.


But where does one draw the line for doing work on the UI thread vs. in the background thread?


No work on the UI thread. Easy line to draw.

This is your final project (presumably for a class)

Quote

namespace FinalProject

And the instructor hasn't put a stop to first week issues like these????:
  • All the work in the button_click handler
  • names like "button1"
  • Using the textbox.text property like its your personal variable to hold data
  • Not to mention taking a class today and using 1980's WinForms

The instructor hasn't smacked students' hands for this stuff?

Dude... Get your money back. Or at the very least don't go back to this quack for a second year. Its not your fault, and I'm not riding you about this because you're just doing what you're told and don't know any better. But this is crap code and style. The fact that you're paying for this education is wrong on so many levels. You could have gotten more and better instruction out of a couple $50 "Learn C# in 30 days books".

Like I said, I'm not getting on you. You only know what they're telling you. I just hate seeing people getting ripped off. This is all stuff I try to stop rookies from doing in the first week.

tlhIn`toq said:

Looking at your code you have some bad practices in play that need your immediate attention.
These will form bad habits quickly and should be worked on. This is part of what I mean by "learn foundation concepts first".

Problem 1: Use of GUI controls as your variables.
You have numerous places where you are doing things like: SomeMethodCall(TextBox43.Text);
Your textboxes and other GUI elements are not meant to be your variables to hold values. They exist only to be a way for the user to interact with the data held in the logic code *behind* the GUI. You should have properties bound to the GUI. When the user changes something in a textbox it updates the property in your code. When the property gets a new value, the GUI is updated. But your logic always uses the property not the GUI element.
There is a tutorial on properties linked in my signature block.

Problem 2: This bit about button1 and textbox14 is just horrible. If you are working in old 1980's WinForms for Win98 and WinXP programs then name your control as soon as you make it before doing anything else with it. txtbxFirstName is so much easier to maintain than textbox32. Same with "frmConnectionSettings" instead of "form2" or "wndCustomerDetails" than "window4". If you are making programs for current OSes like Win7/8 then you should be working in WPF where the controls don't need to be named 90% of the time, but the same practice of giving meaningful names should still be followed in the few cases where they are needed.

Problem 3: Don't put meaningful operational code in GUI control handlers. You have a lot of places where the work is being done in the button event handler instead if in a dedicated method. Instead you want that handler to just direct action to the first methods, not actually perform the actions.
btnSave_Click(object sender, eventargs e)
{
   SaveSettings(); // Call the save method
   SavePlayers();
   SaveWindowGeometry();
   // Do NOT put all the save code here
}

This way you can call the same save methods independently form code without having to either duplicate code (evil practice) or without calling the button handler. If you are using WinForms then you have a .Click event handler. If you are writting in WPF then don't call the .Click event but instead create Commands and activate those. The .Click handler in WPF is kind of a carry-over to give WinForms coders a comfortable migration path but is not considered the way a modern profession WPF coder works.

Problem 4:
Eye sore code.
Take the extra 2 minutes to clean up your code to make it easy to read, easy to follow, and most important, easy to manage.
  • Kill all those dead blank lines at the ends of methods.
  • Wrap groups of methods that go together in #region blocks so you can find all your sending code and receiving code easily for example.
  • Get your lines aligned. Control-K, Control-D will do that for you (Think of it as Klean Document)


Problem 5: Decent naming
Methods like private void ClientSession(object sender, eventargs e); don't follow established naming style guides.
A method performs an action. By convention for decades methods are named as verbs to show what they do. Nouns like ClientSession would be presumed to be a property by any other coder looking at your code. private void CreateClientSession(object sender, eventargs e); on the other hand shows that it does something (a method) not that it is something (a variable).

I'm not going to go into a long critique of your code. And I don't want you to feel like I'm just out to pick on you. I'm just pointing out a couple of many foundation concepts you didn't pickup up in the early stages of learning C#. You can't leap-frog the learning process by trying to build a program in your first month. Nobody can. Its not just you. Nobody in the first month of a new discipline has enough knowledge to jump in and start designing finished solutions: Not carpentry, not automotive design, not software engineering. C# is a foreign language with its own syntax. You aren't going to say "I know no Russian. I'm going to write a Russian mystery novel as my vehicle to learn the language." Same here with programming.

I urge you to take a step back. Pick up a "Learn C# in 30 days" type book and work your way through it cover to cover; don't use it a like a reference dictionary when you get stuck. There are several in a thread pinned to head of the C# forum. Plus there are countless C# beginner tutorial series around just like the C# Learning Series - tutorials here on DIC. There are also numerous "My First C# Program" tutorials like this one that will help you with the design and OOP concepts that are vital to good programming.

Was This Post Helpful? 0
  • +
  • -

#6 martynball  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 128
  • Joined: 21-July 14

Re: Form gets locked up when connecting to database

Posted 18 August 2014 - 09:36 AM

@tlhIn`toq - I don't study C# programming, this is a personal project and i'm just learning as I go. I'm address the issues you have quoted, the code I put in my first post is just to see if it works, then if it does and I don't need to re-write it all then I can write the code properly and add the connections to their own methods ect...

View PostSkydiver, on 17 August 2014 - 09:48 PM, said:

Well, you did set a 30 second time out. After 30 seconds, does the program unfreeze?

Anyway, this is not a C# question. Sounds more like a database configuration question. I would take a look at the SQL configuration first to make sure that TCP/IP is enabled, and then follow up with making sure that your firewall(s) is(are) setup to have an opening for the TCP/IP port for SQL.



I will check the settings and firewalls for these issues, thanks.
Was This Post Helpful? 0
  • +
  • -

#7 Robin19  Icon User is offline

  • D.I.C Addict
  • member icon

Reputation: 272
  • View blog
  • Posts: 552
  • Joined: 07-July 10

Re: Form gets locked up when connecting to database

Posted 19 August 2014 - 10:07 AM

It is best to get into the habit to always do those things correctly the first time. There are too many times a developer (including me sometimes) will "clean it up later" and end up never going back to clean it up.

It doesn't look like anyone explained why you are getting the freezing problem. When you click a button the UI thread runs the handler method. A thread can only do one thing at a time. It needs to wait until the handler method finishes before it can go on to handle other events like listening for any new mouse or button events. If you have a method that could take some time to finish your UI thread will wait the entire time. That is why you should push it off to a background worker/thread. If the task is going to take 100 ms, say create and run a worker thread, you can usually not care that the UI thread was frozen for that amount of time.
Was This Post Helpful? 1
  • +
  • -

#8 Skydiver  Icon User is online

  • Code herder
  • member icon

Reputation: 5928
  • View blog
  • Posts: 20,267
  • Joined: 05-May 12

Re: Form gets locked up when connecting to database

Posted 19 August 2014 - 10:34 AM

There's the interesting aspect to the question which is why I brought up the configuration issue: Typically connecting to a SQL database that is within the LAN is very quick. (It's often faster than trying to connect to a UNC share.) What is causing that simple SQL connection to make the UI thread freeze?
Was This Post Helpful? 0
  • +
  • -

#9 martynball  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 128
  • Joined: 21-July 14

Re: Form gets locked up when connecting to database

Posted 19 August 2014 - 10:52 AM

I'm hoping i'm looking in the correct place here, but it seems that TCP/IP is enabled as you suggested in your first post.

See Image

Edit: Oh wait, this seems like the place, and it's disabled, will enable this now.

See Image

And I understand about the thread locking up, but i'd assume that if it's freezing up until the timeout expires, then i'm assuming that I have written the connection string wrong.

Edit 2: With the ports situation, would I still need to open the ports in the firewall? The SQL Server is being accessed locally and will never be accessed from an outside device.

This post has been edited by martynball: 19 August 2014 - 10:59 AM

Was This Post Helpful? 0
  • +
  • -

#10 Skydiver  Icon User is online

  • Code herder
  • member icon

Reputation: 5928
  • View blog
  • Posts: 20,267
  • Joined: 05-May 12

Re: Form gets locked up when connecting to database

Posted 19 August 2014 - 11:03 AM

Open the ports on the machine, not on your router.

I had to guess, the next thing to look at is your use of Trusted_Connection. It seems to be counter intuitive to use that, and then to pass in a username and password.
Was This Post Helpful? 0
  • +
  • -

#11 martynball  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 128
  • Joined: 21-July 14

Re: Form gets locked up when connecting to database

Posted 19 August 2014 - 11:09 AM

I have since removed the Trusted_Connection part to remove further complications. Just created another SQL User login incase my other one was created wrong. Still does not work, the application does unfreeze after the timeout has expired.

private void btnCreateConnection_Click(object sender, EventArgs e)
        {
            doCreateConnection();
        }
        private void doCreateConnection()
        {
            SqlConnection myConnection = new SqlConnection("user id=jamsnaps;" +
                                                           "password=password;" +
                                                           "Data Source=localhost;" +
                                                           "database=JamSnaps;" +
                                                           "connection timeout=10");
            try
            {
                myConnection.Open();
            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
            }
            //Close the connection
            if (myConnection != null && myConnection.State == ConnectionState.Closed)
            {
                Console.WriteLine("Connection is closed!");
            }
        }


This post has been edited by martynball: 19 August 2014 - 11:21 AM

Was This Post Helpful? 0
  • +
  • -

#12 Curtis Rutland  Icon User is offline

  • (╯□)╯︵ (~ .o.)~
  • member icon


Reputation: 5101
  • View blog
  • Posts: 9,283
  • Joined: 08-June 10

Re: Form gets locked up when connecting to database

Posted 19 August 2014 - 11:20 AM

Side note: you need to pick either SSPI or SQL Authentication. Either works (though I recommend SSPI), but you shouldn't mix them.

Your connection string has a username & password, but also has Trusted_Connection=yes. That's actually not correct at all, since it should be True or False, not Yes or No. But even if it were correct, it would still be incorrect, because you've included a username/password.

A malformed connection string could be part of your problem. It also might not be. Hard to rule out until you correct it.

Use this site when you're looking for connection strings. It is a great resource. In your case, you need to pick: are you expecting your application to use Windows credentials to access the database, or use SQL Authentication (where you create an account inside SQL Server and connect using that).

There are pros and cons both ways, though I'd honestly say there are more pros with Windows Authentication. For instance, if you use Windows Auth, you don't include passwords in your connection strings. It does make configuring your application harder, like running it under a user that does have permissions, or using a service layer to do database calls for users.

Either way, pick one and use the correct connection string. Basically, choose between the first or the second on that page.

Edit: looks like this was discussed while I was writing this reply. Either way, you should read through this and double check it.
Was This Post Helpful? 0
  • +
  • -

#13 martynball  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 128
  • Joined: 21-July 14

Re: Form gets locked up when connecting to database

Posted 19 August 2014 - 11:42 AM

Awesome, seems to be working now, it was my connection string!

private void btnCreateConnection_Click(object sender, EventArgs e)
        {
            doCreateConnection();
        }
        private SqlConnection doCreateConnection()
        {
            string connectionString = "Server=MARTYNBALL-DPC\\SQLEXPRESS;Database=JamSnaps;User Id=jamsnaps;Password=password;";
            SqlConnection myConnection = new SqlConnection(connectionString);
            try
            {
                myConnection.Open();
                return myConnection;
            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
                return null;
            }
        }

        private void btnOpenConnection_Click(object sender, EventArgs e)
        {
            SqlConnection conn = doCreateConnection();
            conn.Open();
            Console.WriteLine(conn.State);
        }

        private void btnCloseConnection_Click(object sender, EventArgs e)
        {
            SqlConnection conn = doCreateConnection();
            conn.Close();
            Console.WriteLine(conn.State);
        }


Thanks guys!
Was This Post Helpful? 0
  • +
  • -

#14 Curtis Rutland  Icon User is offline

  • (╯□)╯︵ (~ .o.)~
  • member icon


Reputation: 5101
  • View blog
  • Posts: 9,283
  • Joined: 08-June 10

Re: Form gets locked up when connecting to database

Posted 19 August 2014 - 12:01 PM

Cool, glad that worked. Next suggestion: don't do what you're doing. Don't open the connection with one button and close it with another. That's really, really bad practice.

For that matter, it's not actually doing what you think it's doing. You discard the connection you create each time and create a new one, since you call doCreateConnection() (also, the major style guides for C# suggest using PascalCase for method names rather than camelCase). Every time you call that method, you create a new connection and open it. So, the connection you created previously? It's going to live on and stay open until you shut down the app or the garbage collector comes by and sees that nothing references it. Luckily for us, the GC can Dispose of things, but we should set it up to not need that.

So here's my suggestion: write your methods around an actionable task. If your task is to load users from a database, your method might look like this:

public List<User> LoadAllUsers()
{
  string connStr = Config.UserDBConnectionString;
  string query = Queries.LoadAllUsers;
  List<User> results = new List<User>();
  using(SqlConnection conn = new SqlConnection(connStr))
  {
    using(SqlCommand cmd = new SqlCommand(query, conn))
    {
      conn.Open();
      //code to execute query and store values in results List
      return results;
    }
  }
}


Obviously you'd need to add error checking, but that's the gist. You create the connection on demand, and only leave it open long enough to perform your operation. If you were doing multiple back-to-back operations, you might keep the same connection alive and use it, but typically you can just make a connection when you need one.

Side note: in my example, I referenced Config and Queries. Those are (imaginary) static classes that would hold Configuration info and Query strings. You don't have to set things up that way; that's just probably how I'd do it. I'd have a static class for Configuration that lazily loads config values and stores them, as well as a static class to hold queries.

That, or I'd use Stored Procedures instead. Either way, I very much suggest you change your design.

Lastly, I'd suggest you not do what you're doing in your exception handling as well. During development, there's so much information available to you as a developer when the debugger breaks on an exception (stack trace, memory values, inner exceptions, etc...). By catching the exception and just printing the .ToString of the Exception, you're losing a ton of useful information.

Generally, during development I'll add a throw; immediately inside a catch so that the debugger always stops when an exception happens. I'll get rid of that code as I implement proper exception handling and logging. But what you're doing is called "swallowing" the exception. You "catch" it, and then it's gone.
Was This Post Helpful? 1
  • +
  • -

#15 martynball  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 128
  • Joined: 21-July 14

Re: Form gets locked up when connecting to database

Posted 19 August 2014 - 02:10 PM

The buttons where just to make sure that it was working correctly, but thanks. That post is helpful and I will use it as a template as it looks good :)
Was This Post Helpful? 0
  • +
  • -

  • (4 Pages)
  • +
  • 1
  • 2
  • 3
  • Last »