Need advice for MySQL connection to be more effective.

  • (2 Pages)
  • +
  • 1
  • 2

16 Replies - 1307 Views - Last Post: 26 February 2014 - 08:06 AM Rate Topic: -----

#1 jimzcoder  Icon User is offline

  • D.I.C Regular

Reputation: 57
  • Posts: 351
  • Joined: 14-November 12

Need advice for MySQL connection to be more effective.

Posted 24 February 2014 - 06:04 AM

Good day everyone.
I have just created an online servicing system which has been hosted at our head office and all the other branches search and insert data simultaneously.
It's running now and things seem to run fine except for some reason an exception is thrown randomly.
This happens when i'm trying to execute a nonQuery(Update/Insert).
I also need advice on how will i be able to make this better.

Here's the code i have for my connection.

 class SQL
    {
        public SQL() 
        {
            //Default Constructor
            
        }
        public void OpenDB() //Opens the database connection
        {
            try
            {
                conn.Open();
            }
            catch (Exception exc)
            {
                //MessageBox.Show(exc.Message);
                exc.Data.Clear();
            }
        }
        public MySqlConnection conn = new MySqlConnection(mySQLConnString);
        public string errMessage;
        public bool noError = true;
        public MySqlCommand command = new MySqlCommand();
        public MySqlDataAdapter adapter = new MySqlDataAdapter();
        public DataSet ds;
        
        static string mySQLConnString = "Server=ServerIP;Port=ServerPort;Database=dbName;Uid=UName;Pwd=PWord;Allow Zero Datetime=true;"; //connection string
        
        private string sqlString;
        //CommandText to be executed
        public string CommandText {
            get {return sqlString; }
            set { sqlString = value; }
        }
        //For NonQuery Execution
        public void ExecuteNonQuery() 
        {
            noError = true;
            errMessage = string.Empty;
            
            command.Connection = conn;
            command.CommandText = this.CommandText;

            try 
            {
                
                command.ExecuteNonQuery();
                            }
            catch (Exception ex) 
            {
                noError = false;
                errMessage = ex.Message;
            }
        }

        public void ExecuteReader() 
        {
            noError = true;
            errMessage = string.Empty;
            
            command.Connection = conn;
            command.CommandText = this.CommandText;
            try
            {
            
                adapter.SelectCommand = command;
                ds = new DataSet();
                adapter.Fill(ds);
            }
            catch(Exception exc)
            {
                //Flag error
                noError = false;
                errMessage = exc.Message;
            }
        }

    }



Here's how i use this connection from one of my forms.
        private void btnRcv_Click(object sender, EventArgs e)
        {
		//Validates the user inputs
            if (!frmRcv.ValidInput)
            {
		
                MessageBox.Show("You cannot add an Item without Servicing Slip # and a Registered Customer!", "Invalid Input...!", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                return;
            }

            frmRcv.Cursor = Cursors.WaitCursor;

		
            if (frmRcv.isBackJob)
            {
                sql.CommandText = "Insert into tblBackJob(Curr_SS_ID,Old_SS_ID,Problem,Job_Request,Techs_Note,Recommendations,Date_Received,Received_By,Date_Released,Released_By,Repaired_By) values((Select SS_ID,Problem,Job_Request,TechsNote,Recommendations,Date_Received,Received_By,Date_Released,Released_By, Check_Rep_By from tblServicing where [email protected]))";
                sql.command.Parameters.AddWithValue("@SSID", frmRcv.OldSSID);
                sql.ExecuteNonQuery();
             	  
            }
		
            if (frmRcv.isEditMode)
            {
                sql.CommandText = "Update tblServicing set [email protected], [email protected], [email protected], " +
                    "[email protected], [email protected], [email protected], [email protected], " +
                    "[email protected],[email protected] where [email protected]";
                sql.command.Parameters.Clear();
                sql.command.Parameters.AddWithValue("@NewSSID", frmRcv.txtSSID.Text.Trim());
                sql.command.Parameters.AddWithValue("@CustID", frmRcv.curCust_ID);
                sql.command.Parameters.AddWithValue("@Prob", frmRcv.txtProb.Text.Trim());
                sql.command.Parameters.AddWithValue("@JobReq", frmRcv.txtJobReq.Text.Trim());
                sql.command.Parameters.AddWithValue("@DateRcvd", frmRcv.dtpReceived.Value.ToString("yyyy-MM-dd"));
                sql.command.Parameters.AddWithValue("@RcvdBy", frmRcv.cmbRcvr.Text.Trim());
                sql.command.Parameters.AddWithValue("@BranchRcvd", frmRcv.cmbBranch.Text.Trim());
                sql.command.Parameters.AddWithValue("@Comments", frmRcv.txtSideNotes.Text.Trim());
                sql.command.Parameters.AddWithValue("@Category", frmRcv.Category);
                sql.command.Parameters.AddWithValue("@OldSSID", frmRcv.OldSSID);
                sql.ExecuteNonQuery();
                sql.command.Parameters.Clear();
                sql.CommandText = "Delete from tblItems where [email protected]";
                sql.command.Parameters.Clear();
                sql.command.Parameters.AddWithValue("@SSID", frmRcv.OldSSID);
                sql.ExecuteNonQuery();
                sql.CommandText = "Delete from tblAccessories where [email protected]";
                sql.command.Parameters.Clear();
                sql.command.Parameters.AddWithValue("@SSID", frmRcv.OldSSID);
                sql.ExecuteNonQuery();
                sql.command.Parameters.Clear();
                sql.CommandText = "Update tblAdditionalItems set [email protected] where [email protected]";
                sql.command.Parameters.AddWithValue("@SSID", txtSSID.Text);
                sql.command.Parameters.AddWithValue("@OldSSID", frmRcv.OldSSID);
                sql.ExecuteNonQuery();
                sql.command.Parameters.Clear();

            }
            else
            {
                sql.command.Parameters.Clear();
                sql.CommandText = "Insert into tblServicing(SS_ID,Cust_ID,Problem,Job_Request,Date_Received,Received_By,Branch_Received,Comments,Category) Values(@SSID,@CustID,@Prob,@JobReq,@DateRcvd,@RcvdBy,@BranchRcvd,@Comments,@Category)";
                sql.command.Parameters.AddWithValue("@SSID", frmRcv.txtSSID.Text.Trim());
                sql.command.Parameters.AddWithValue("@CustID", frmRcv.curCust_ID);
                sql.command.Parameters.AddWithValue("@Prob", frmRcv.txtProb.Text.Trim());
                sql.command.Parameters.AddWithValue("@JobReq", frmRcv.txtJobReq.Text.Trim());
                sql.command.Parameters.AddWithValue("@DateRcvd", frmRcv.dtpReceived.Value.ToString("yyyy-MM-dd"));
                sql.command.Parameters.AddWithValue("@RcvdBy", frmRcv.cmbRcvr.Text.Trim());
                sql.command.Parameters.AddWithValue("@BranchRcvd", frmRcv.cmbBranch.Text.Trim());
                sql.command.Parameters.AddWithValue("@Comments", frmRcv.txtSideNotes.Text.Trim());
                sql.command.Parameters.AddWithValue("@Category", frmRcv.Category);
                sql.ExecuteNonQuery();
            }
		//Inserts all the received items

            foreach (DataGridViewRow Row in frmRcv.dgvMainComp.Rows)
            {
                sql.command.Parameters.Clear();
                sql.CommandText = "Insert into tblItems Values(@a,@b,@c,@d,@e,@f)";
                sql.command.Parameters.AddWithValue("@a", frmRcv.txtSSID.Text.Trim());
                sql.command.Parameters.AddWithValue("@b", Row.Cells[0].Value.ToString());
                sql.command.Parameters.AddWithValue("@c", Row.Cells[1].Value.ToString());
                sql.command.Parameters.AddWithValue("@d", Row.Cells[2].Value.ToString());
                sql.command.Parameters.AddWithValue("@e", Row.Cells[3].Value.ToString());
                sql.command.Parameters.AddWithValue("@f", Row.Cells[4].Value.ToString());
                sql.ExecuteNonQuery();
            }
		//Inserts All the accessories received listed from the gridview
            foreach (DataGridViewRow Row in frmRcv.dgvAcc.Rows)
            {
                sql.command.Parameters.Clear();
                sql.CommandText = "Insert into tblAccessories Values(@a,@b,@c,@d,@e,@f)";
                sql.command.Parameters.AddWithValue("@a", frmRcv.txtSSID.Text.Trim());
                sql.command.Parameters.AddWithValue("@b", Row.Cells[0].Value.ToString());
                sql.command.Parameters.AddWithValue("@c", Row.Cells[1].Value.ToString());
                sql.command.Parameters.AddWithValue("@d", Row.Cells[2].Value.ToString());
                sql.command.Parameters.AddWithValue("@e", Row.Cells[3].Value.ToString());
                sql.command.Parameters.AddWithValue("@f", Row.Cells[4].Value.ToString());
                sql.ExecuteNonQuery();
            }
		//clears all the previous parameters
            sql.command.Parameters.Clear();
            sql.CommandText = "Insert into tblCharges(SS_ID,serv_Charge) Values(@SSID,@servCharge)";
            sql.command.Parameters.AddWithValue("@SSID", frmRcv.txtSSID.Text);
            sql.command.Parameters.AddWithValue("@servCharge", 0);
            sql.ExecuteNonQuery();
            frmRcv.Cursor = Cursors.Default;
            MessageBox.Show("Items Received!", "Successful...", MessageBoxButtons.OK, MessageBoxIcon.Information);
            frmRcv.clearItems();
        }



Hope you could share your experience and thoughts in this one.
Thanks in advance.

Is This A Good Question/Topic? 0
  • +

Replies To: Need advice for MySQL connection to be more effective.

#2 andrewsw  Icon User is online

  • lashings of ginger beer
  • member icon

Reputation: 6338
  • View blog
  • Posts: 25,554
  • Joined: 12-December 12

Re: Need advice for MySQL connection to be more effective.

Posted 24 February 2014 - 06:08 AM

What exception is thrown?

Your code is catching exceptions but does not seem to do anything with them. You should at least log them somewhere.

This post has been edited by andrewsw: 24 February 2014 - 06:10 AM

Was This Post Helpful? 0
  • +
  • -

#3 Skydiver  Icon User is offline

  • Code herder
  • member icon

Reputation: 5824
  • View blog
  • Posts: 19,831
  • Joined: 05-May 12

Re: Need advice for MySQL connection to be more effective.

Posted 24 February 2014 - 06:12 AM

And where exactly? A callback maybe useful.
Was This Post Helpful? 0
  • +
  • -

#4 jimzcoder  Icon User is offline

  • D.I.C Regular

Reputation: 57
  • Posts: 351
  • Joined: 14-November 12

Re: Need advice for MySQL connection to be more effective.

Posted 24 February 2014 - 07:23 AM

It's throwing a "Must have a valid/open connection", when i insert or update something.
Regarding the logs. i'm keeping them somewhere that is why i created a boolean and string variable which is used to test if something went wrong from on the execution and that other classes will know that there is an exception thrown and its details then log them somewhere.

it occurs randomly, for example if i have to update the service charge of an item:

sql.CommadText="Update tblCharges set [email protected] where [email protected]";
sql.command.Parameters.Clear();
sql.command.Parameters.AddWithValue("Amnt",intAmt);
sql.command.Parameters.AddWithValue("SSID",selSSID);
sql.ExecuteNonQuery();
if(!sql.NoError)
{
   MessageBox.Show(String.Format("Exception : {0}",sql.ErrMessage),"Exception!");
   //the error: "Must have a valid/open connection" sometimes appear. i need to restart the program to make things run smooth again.
   WriteToLogFile(sql.Errmessage,DateTime.Now.ToString()); //Write Exception to LogFile
}
    


Take note:
i open the connection only once right after the main form initialization.

my main concern is if there would be something i could do to simplify this things based on your experience. this wont be that big issue if i was just keeping records locally but i really need this thing to work well online.

i'll try my best to cooperate and provide any other details that you'd like to know.

Thanks
Was This Post Helpful? 0
  • +
  • -

#5 Skydiver  Icon User is offline

  • Code herder
  • member icon

Reputation: 5824
  • View blog
  • Posts: 19,831
  • Joined: 05-May 12

Re: Need advice for MySQL connection to be more effective.

Posted 24 February 2014 - 07:33 AM

Since you declared conn and command member variables a public (a really bad practice, BTW), how sure are you that nobody closed the connection through those public member variables?
Was This Post Helpful? 2
  • +
  • -

#6 jimzcoder  Icon User is offline

  • D.I.C Regular

Reputation: 57
  • Posts: 351
  • Joined: 14-November 12

Re: Need advice for MySQL connection to be more effective.

Posted 24 February 2014 - 07:44 AM

Quote

Since you declared conn and command member variables a public (a really bad practice, BTW)


thanks for this one. gotta read more regarding this one. i really wanna build this as professional as you guys, but i still have more rice to eat inorder to fulfill it and i'm willing to eat all those, if that's what it takes. :)/>/>/>

Quote

how sure are you that nobody closed the connection through those public member variables?

i only have the closing code executed from the Main Form Close event and there's nothing else that could close it(from my other classes/methods).

the weird thing is if, i search for any item right after that exception has been thrown, i still receive relevant results where infact they were just using the same connection and is suppose to throw also same exception.

This post has been edited by jimzcoder: 24 February 2014 - 07:50 AM

Was This Post Helpful? 0
  • +
  • -

#7 jimzcoder  Icon User is offline

  • D.I.C Regular

Reputation: 57
  • Posts: 351
  • Joined: 14-November 12

Re: Need advice for MySQL connection to be more effective.

Posted 24 February 2014 - 07:59 AM

if there would be a better way to build a connection class for this one. i'm willing to give it a shot i just need some pointers and expert advice from you guys since you know better on this stuffs hundred of times than i do. since this is my very first project for hosting an online database.
i want this to be my strongest stepping stone/foundation so that i'll be a put more into it as time passes by.

your help will be much appreciated.
Thanks
Was This Post Helpful? 0
  • +
  • -

#8 Skydiver  Icon User is offline

  • Code herder
  • member icon

Reputation: 5824
  • View blog
  • Posts: 19,831
  • Joined: 05-May 12

Re: Need advice for MySQL connection to be more effective.

Posted 24 February 2014 - 11:46 AM

View Postjimzcoder, on 24 February 2014 - 09:44 AM, said:

the weird thing is if, i search for any item right after that exception has been thrown, i still receive relevant results where infact they were just using the same connection and is suppose to throw also same exception.

Are you sure you are using the same instance of your SQL class between those forms?
Was This Post Helpful? 0
  • +
  • -

#9 jimzcoder  Icon User is offline

  • D.I.C Regular

Reputation: 57
  • Posts: 351
  • Joined: 14-November 12

Re: Need advice for MySQL connection to be more effective.

Posted 25 February 2014 - 02:51 AM

yes. i'm sure(i just checked it again before answering this one). i centralized all the code execution into my main form so that i will only have to use 1/same instance of mysql connection.
what i did was just listen to other forms events and trigger a method that corresponds to it from the main form.
Was This Post Helpful? 0
  • +
  • -

#10 andrewsw  Icon User is online

  • lashings of ginger beer
  • member icon

Reputation: 6338
  • View blog
  • Posts: 25,554
  • Joined: 12-December 12

Re: Need advice for MySQL connection to be more effective.

Posted 25 February 2014 - 06:16 AM

Please clarify: Are you opening the connection when your application starts, and attempting to maintain this open connection for each user's entire session?

SO said:

Yes you can, provided:

  • You will reconnect if you lose the connection
  • You can reset the connection state if something strange happens
  • You will detect if the connection "goes quiet", for example if a firewall timeout occurs


Basically it requires a good deal of attention to failure cases and correct recovery; connecting and disconnecting often is a lot easier.


SO topic

This post has been edited by andrewsw: 25 February 2014 - 06:23 AM

Was This Post Helpful? 1
  • +
  • -

#11 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 13392
  • View blog
  • Posts: 53,445
  • Joined: 12-June 08

Re: Need advice for MySQL connection to be more effective.

Posted 25 February 2014 - 08:27 AM

Quote

attempting to maintain this open connection for each user's entire session?

Just that being a possibility makes my head hurt.

OP - you want to open a connection right before you use it, and close it right after. Maintaining an open connection the duration of an app is a horrible idea.. it eats up resources, can lead to deadlocks, is lazy, concurrency issues crop up, etc. Let alone this maybe an asp.net page. Oooh boy.
Was This Post Helpful? 1
  • +
  • -

#12 andrewsw  Icon User is online

  • lashings of ginger beer
  • member icon

Reputation: 6338
  • View blog
  • Posts: 25,554
  • Joined: 12-December 12

Re: Need advice for MySQL connection to be more effective.

Posted 25 February 2014 - 11:08 AM

Does each user have their own username/credentials?
Was This Post Helpful? 0
  • +
  • -

#13 jimzcoder  Icon User is offline

  • D.I.C Regular

Reputation: 57
  • Posts: 351
  • Joined: 14-November 12

Re: Need advice for MySQL connection to be more effective.

Posted 25 February 2014 - 08:46 PM

View Postandrewsw, on 25 February 2014 - 06:16 AM, said:

Please clarify: Are you opening the connection when your application starts, and attempting to maintain this open connection for each user's entire session?


yes. i'm trying to maintain the connection as long as the application is running.
i've read the link you've shared from SO and i found out pooling maybe the answer to this one but haven't tried it yet.
Was This Post Helpful? 0
  • +
  • -

#14 jimzcoder  Icon User is offline

  • D.I.C Regular

Reputation: 57
  • Posts: 351
  • Joined: 14-November 12

Re: Need advice for MySQL connection to be more effective.

Posted 25 February 2014 - 08:52 PM

View Postandrewsw, on 25 February 2014 - 11:08 AM, said:

Does each user have their own username/credentials?


no they're just using same credentials. approximately 10PCs are using the same credentials.
the reason why i used only one instance of connection was its performance, specifically speed.
what could you suggest drewsw? since you are one of the experts in here.
Reading your credentials and reputation in here is really amazing.
please i'm not asking for any codes i just need to know which path follow.
Thanks.
:)
Was This Post Helpful? 0
  • +
  • -

#15 jimzcoder  Icon User is offline

  • D.I.C Regular

Reputation: 57
  • Posts: 351
  • Joined: 14-November 12

Re: Need advice for MySQL connection to be more effective.

Posted 25 February 2014 - 08:58 PM

View Postmodi123_1, on 25 February 2014 - 08:27 AM, said:

Quote

attempting to maintain this open connection for each user's entire session?

Just that being a possibility makes my head hurt.

OP - you want to open a connection right before you use it, and close it right after. Maintaining an open connection the duration of an app is a horrible idea.. it eats up resources, can lead to deadlocks, is lazy, concurrency issues crop up, etc. Let alone this maybe an asp.net page. Oooh boy.


yeah. i was expecting for this reaction from the experts right before i posted this problem.
i'm ashamed but asking for advice/help right before a bigger problems arise is way better than banging your head on the wall just because you were stupid enough not to seek for help.

Thanks though. :)
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2