1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

C# MySQL Connection[MultiThreaded] - EXAMPLE

Discussion in 'C, C++, C#' started by MysteryGuest, Apr 3, 2013.

  1. MysteryGuest

    MysteryGuest Registered Member

    Joined:
    Mar 7, 2013
    Messages:
    63
    Likes Received:
    18
    Occupation:
    Freelancer
    Location:
    Virtual World!
    Hello, here you have a small example for a multiThreaded MySQL Connection.

    So make a class called ; ConnectionHandler. Let's start by declaring some thing.
    Code:
    #region declare
            public MySqlConnection          Con;
            private string                  _server;
            private string                  _database;
            private string                  _username;
            private string                  _password;
    
            public FrmMain                  Form;
                #region Threads
                public bool                 StopOpenConThread   = true;
                public bool                 StopCloseConThread  = true;
                public bool                 StopCheckConThread  = true;
                #endregion
            #endregion
    
    #endregion
    Let's move on with adding the constructor!
    Code:
    /// <summary>
            /// Constructor.
            /// </summary>
            public ConnectionHandler() 
            {
                Initialize();
            }
    Let's add the function Initialize()!
    Code:
    /// <summary>
            /// Set connection.
            /// </summary>
            private void Initialize()
            {
                _server                         = "IP";
                _database                       = "DATABASE_NAME";
                _username                       = "DATABASE_USERNAME";
                _password                       = "DATABASE_PASSWORD";
    
                var connectionString =          "SERVER=" + _server + ";" +
                                                "DATABASE=" + _database + ";" +
                                                "UID=" + _username + ";" +
                                                "PASSWORD=" + _password + ";";
    
                Con = new MySqlConnection(connectionString);
            }
    For this we also gonna hook our class to our FROM! (FrmMain)
    Code:
    /// <summary>
            /// Hooks the main form.
            /// </summary>
            /// <param name="mainForm"></param>
            public void HookMainForm(FrmMain mainForm)
            {
                Form = mainForm;
            }
    Now go back to your Main form and go to the code cave and add this to the constructor!
    Code:
    //Declaring the class.
    readonly ConnectionHandler _conHandler = new ConnectionHandler();
    public FrmMain()
    {
    _conHandler.HookMainForm(this);
    } 
    Once that's done, we can continue working on the functions in our class. let's make the first thread! and the function!
    Code:
    /// <summary>
            /// Starts the thread.
            /// </summary>
            public void StartCheckConnectionState()
            {
                var thread = new Thread(CheckConnectionState);
                thread.Start();
            }
    
    
            public string ErrorHandler;
            /// <summary>
            /// Checks connectionstate.
            /// </summary>
            void CheckConnectionState()
            {
                while (StopCheckConThread == false)
                {
                    try
                    {
                        switch (Con.State)
                        {
                            case System.Data.ConnectionState.Open:
                                Form.Invoke((MethodInvoker) (() => Form.lblSatus.ForeColor = Color.DarkGreen));
                                Form.lblSatus.Text = @"CONNECTED";
                                break;
                            case System.Data.ConnectionState.Closed:
                                Form.Invoke((MethodInvoker) (() => Form.lblSatus.ForeColor = Color.Maroon));
                                Form.lblSatus.Text = @"DISCONNECTED";
                                
                                StopOpenConThread = false;
                                OpenConThread();
                                break;
                            case System.Data.ConnectionState.Connecting:
                                Form.Invoke((MethodInvoker) (() => Form.lblSatus.ForeColor = Color.GhostWhite));
                                Form.lblSatus.Text = @"CONNECTING...";
                                break;
                            case  System.Data.ConnectionState.Executing:
                                Form.Invoke((MethodInvoker) (() => Form.lblSatus.ForeColor = Color.Goldenrod));
                                Form.lblSatus.Text = @"EXECUTTING...";
                                break;
                        }
                    }
                    catch (Exception ex)
                    {
                        ErrorHandler = ex.Message;
                    }
                    Thread.Sleep(50);
                }
            }
    The second thread!
    Code:
    /// <summary>
            /// Starts the thread.
            /// </summary>
            private void OpenConThread()
            {
                var thread = new Thread(OpenConnection);
                thread.Start();
            }
    
            /// <summary>
            /// Opens connection in different thread.
            /// </summary>
            void OpenConnection()
            {
                while (StopOpenConThread == false)
                {
                    try
                    {
                        Con.Open();
    
                        StopOpenConThread = true;
                    }
                    catch (MySqlException ex)
                    {
                        //When handling errors, you can your application's response based 
                        //on the error number.
                        //The two most common error numbers when connecting are as follows:
                        //0: Cannot connect to server.
                        //1045: Invalid user name and/or password.
                        MessageBox.Show(ex.Message);
                        switch (ex.Number)
                        {
                            case 0:
                                MessageBox.Show(@"Cannot connect to server. Contact administrator");
                                break;
    
                            case 1045:
                                MessageBox.Show(@"Invalid username/password, please try again");
                                break;
                        }
                        StopOpenConThread = false;
                    }
                    Thread.Sleep(500);  
                }
    
            }
    and the last 1!
    Code:
    /// <summary>
            /// Starts the thread.
            /// </summary>
     private void CloseConThread()
            {
                var thread = new Thread(CloseConnection);
                thread.Start();
            }
    
            /// <summary>
            /// Closes the connection.
            /// </summary>
            void CloseConnection()
            {
                while (StopCloseConThread == false)
                {
                    try
                    {
                        Con.Close();
                        StopCloseConThread = true;
                    }
                    catch (MySqlException ex)
                    {
                        MessageBox.Show(ex.Message);
                        StopCloseConThread = false;
                    }
                    Thread.Sleep(500); 
                }
            }

    Almost forgot to say you also need to add the MySQL Connector, you can google this just install and add the reference once you did that Use this.
    Code:
    using MySql.Data.MySqlClient;

    Now to get everything working add a button on your mainform and add this line!
    Code:
    _conHandler.StopCheckConThread = false;
                _conHandler.StartCheckConnectionState();


    Almost forgot to say, because our class cant interact with private, we need to change all controls that we are using to PUBLIC. you can do this by clicking the control and go to the properties bar and set private to public!
     
    • Thanks Thanks x 3
    Last edited: Apr 4, 2013
  2. innozemec

    innozemec Jr. VIP Jr. VIP

    Joined:
    Aug 19, 2011
    Messages:
    5,290
    Likes Received:
    1,799
    Location:
    www.Indexification.com
    Home Page:
  3. MysteryGuest

    MysteryGuest Registered Member

    Joined:
    Mar 7, 2013
    Messages:
    63
    Likes Received:
    18
    Occupation:
    Freelancer
    Location:
    Virtual World!
    sure, do whatever you like with! If you need help with things or want some examples feel free to pm me or add me on skype; mysteryguest18
     
  4. DarkPixel

    DarkPixel Jr. VIP Jr. VIP Premium Member

    Joined:
    Oct 4, 2011
    Messages:
    1,328
    Likes Received:
    1,239
    Location:
    ↓↓↓↓
    Home Page:
    This is awesome mate, it will sure come in handy!
     
  5. trustedfire9

    trustedfire9 Jr. VIP Jr. VIP Premium Member

    Joined:
    Jun 15, 2010
    Messages:
    2,116
    Likes Received:
    1,786
    rep and thanks given finally something worth to read in this section ... usually threads about what is the best
    programming language to learn to code bots ..... !
     
  6. MysteryGuest

    MysteryGuest Registered Member

    Joined:
    Mar 7, 2013
    Messages:
    63
    Likes Received:
    18
    Occupation:
    Freelancer
    Location:
    Virtual World!
    thanks for the support! i really appreciate it! now it feels worth it to post a lot of tuts(examples), i wasn't sure what to expect for doing this. But i like to code, so i happily share it with other!
     
  7. healzer

    healzer Jr. Executive VIP Jr. VIP Premium Member

    Joined:
    Jun 26, 2011
    Messages:
    2,364
    Likes Received:
    1,967
    Gender:
    Male
    Occupation:
    Marketing automation tools
    Location:
    Somewhere in Europe
    Home Page:
    thanks!
    Will look into this - so this way I am able to connect to my hostgator mysql right?

    Because I tried something similar before, but hostgator didn't allow it
     
  8. MysteryGuest

    MysteryGuest Registered Member

    Joined:
    Mar 7, 2013
    Messages:
    63
    Likes Received:
    18
    Occupation:
    Freelancer
    Location:
    Virtual World!
    Hello healerz,

    I had the same problem with host-gator, it has nothing to do with this "SOURCE". What you will have to do is change the encryption type of MySQL, Open up live chat of Host-gator and ask them about the encryption of your MySQL if they aren't be able to help, then come back here and will solve it for you. After host-gator made some updates which where like 4-5 month's ago people where getting these error's!
     
    • Thanks Thanks x 1
  9. healzer

    healzer Jr. Executive VIP Jr. VIP Premium Member

    Joined:
    Jun 26, 2011
    Messages:
    2,364
    Likes Received:
    1,967
    Gender:
    Male
    Occupation:
    Marketing automation tools
    Location:
    Somewhere in Europe
    Home Page:
    Oh I see :D
    thanks!

    I don't need this right now, but when I do I'll look into it again :)

    +rep