C# Retrieving info from a table without lagging the program

[samwilki]

Hi, I am currently doing a little program that displays a MySQL table into a rich text box on my form, but every 5 seconds I have it updating but this causes the program to lag for a second, this is really annoying and I am wondering if there is any better way to do it.

This is my timer code.

string host = "**********";
            string database = "********";
            string user = "********";
            string password = "*******";
            string strSQL = "CREATE TABLE IF NOT EXISTS `*********`.`" + TxtChannel.Text + "` ( `name` TEXT NOT NULL , `text` TEXT NOT NULL , `time` TIMESTAMP NOT NULL ); SELECT name, text, time FROM " + TxtChannel.Text + ";";

            string strProvider = "Data Source=" + host + ";Database=" + database + ";User ID=" + user + ";Password=" + password;
            try
            {
                MySqlConnection mysqlCon = new MySqlConnection(strProvider);
                mysqlCon.Open();

                if (mysqlCon.State.ToString() == "Open")
                {

                    MySqlCommand mysqlCmd = new MySqlCommand(strSQL, mysqlCon);
                    MySqlDataReader mysqlReader = mysqlCmd.ExecuteReader();
                                            RtbChat.Text = "";

                    while (mysqlReader.Read())
                    {

                        RtbChat.Text += "(" + mysqlReader.GetString(2) + ") " + mysqlReader.GetString(0) + ": " + mysqlReader.GetString(1) + "\n";

                    }

                }

                mysqlCon.Close();

            }
            catch (Exception er)
            {
                MessageBox.Show(er.Message);
            }

How can I make this not lag the shit out of me for 1 second.

[Shammah]

Without even bothering to mess with your code: [url]http://dotnetperls.com/backgroundworker[/url]

[Crhem van der B]

Or a thread?

[demoguy08]

Uh, I don't know much C# so I'm just throwing a wild guess out there, but if you're opening a new connection to your database on each iteration then that might be what causes the lag? Wouldn't it be better to open the connection and keep it open until you close the program?

[Encryption]

Multithreading

[Anthony]

Here try this. [url]http://pastebin.com/5Ub1m0mw[/url] Also leave it out of that damned try catch. It will speed it up alot.

[Shammah]

[QUOTE=Crhem van der B;27521707]Or a thread?[/QUOTE]

[QUOTE=Encryption;27521831]Multithreading[/QUOTE]

Backgroundworker = Threading

[Fox-Face]

[QUOTE=anthonywolfe;27523499]Also leave it out of that damned try catch. It will speed it up alot.[/QUOTE]

[url]http://www.programmersheaven.com/user/pheaven/blog/175-Do-trycatch-blocks-hurt-runtime-performance/[/url]

[samwilki]

How can I stop this when using the background workers?

[img]http://dl.dropbox.com/u/8165303/Errir.png[/img]

[editline]19th January 2011[/editline]

I am doing

RtbChat.Text += "(" + mysqlReader.GetString(2) + ") " + mysqlReader.GetString(0) + ": " + mysqlReader.GetString(1) + "\n";

[Shammah]

RtbChat.Text.Invoke((mysqlReader) => this.Text += "(" + mysqlReader.GetString(2) + ") " + mysqlReader.GetString(0) + ": " + mysqlReader.GetString(1) + "\n";);

Not sure if my lambda expression works correctly, but the point is that when dealing with threads is that you should use a WinForms Invoke method when modifying it.

[url]http://msdn.microsoft.com/en-us/library/zyzhdc6b.aspx[/url]

[casual ruby dev]

OP, you have an SQL injection vulnerability.

MySQL's Connector/Net makes it trivial to use parameterized queries, so do it.

[supersnail11]

I know this might be wrong, but I suggest not connecting to the MySql database clientside. Use a PHP page, because if you have a vulnerability, it can be updated with out making the client download a new one. Especially good, if the client keeps the old one to wreak havoc on your database.

[casual ruby dev]

Usually when client apps connect to a database, it's a local database.

Although if OP is writing an application where multiple clients connect into the same database, he's doing it wrong.