?
Solved

MYSQL CONNECTION C#

Posted on 2010-01-06
12
Medium Priority
?
755 Views
Last Modified: 2012-06-27
I'm trying to write the most effecient and secure mysql connection.

I must have multiple connections.

I am using stored procedures.

I have attached my current code.

Notice how the connections and commands are globally shared, would this create a security risk? Should I move them inside the private function addRecordtoDatabase(). If I did that would that create to many connection objects?

Any suggestions, critisms would be greatly appreciated.

Ryan
using MySql.Data.MySqlClient;

namespace byDDT
{
    public partial class Default : System.Web.UI.Page
    {
//----------------------------MYSQLCONNECTIONS---------          
        #region
        /// <summary>Mysql Connection to get the data Notice these are globally shared</summary>
        protected MySqlConnection gmySqlConnection_Trust;

        /// <summary>Global mysql command Notice these are globally shared</summary>
        protected MySqlCommand gmySqlSelectCommand_Trust;
        #endregion

        /// <summary>
        /// Add data to mysql database.
        /// </summary>
        /// <returns></returns>
        private Boolean addRecordtoDatabase()
        {
            
            if (gmySqlConnection_Trust.State.Equals(ConnectionState.Closed))
            {
                gmySqlConnection_Trust.ConnectionString = ConfigurationManager.ConnectionStrings["dreamhostmysql"].ConnectionString; 
                gmySqlConnection_Trust.Open();
            } 
            try
            {
               
                //using stored procedure
                MySqlCommand cmd = new MySqlCommand("sp_adddata", gmySqlConnection_Trust);
                cmd.Parameters.Clear();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add(new MySqlParameter("INPUT1", MySqlDbType.UInt32));
                cmd.Parameters.Add(new MySqlParameter("INPUT2", MySqlDbType.UInt32));
                cmd.Parameters.Add(new MySqlParameter("INPUT3", MySqlDbType.UInt32));
      

                //seed fields with data
                cmd.Parameters["INPUT1" ].Value = 1;
                cmd.Parameters["INPUT1" ].Value = 2;
                cmd.Parameters["INPUT1" ].Value = 3;

                cmd.UpdatedRowSource = UpdateRowSource.None;
                if (gmySqlConnection_Trust.State.Equals(ConnectionState.Closed))
                    gmySqlConnection_Trust.Open();
                cmd.ExecuteNonQuery();
                cmd.Dispose();

            }
            catch (Exception ex)
            {
                //do error stuff
                String s = ex.Message;
            }
            finally
            {
                gmySqlConnection_Trust.Dispose();
            }
            return true;    
        }
    }
}

Open in new window

0
Comment
Question by:RyanUpton
  • 6
  • 5
12 Comments
 
LVL 19

Expert Comment

by:elimesika
ID: 26197797
HI

Your connections & commands are not shared between instances since you didn't make them static. So, each inherited class in your code will have it own connection & command , and the code is currently OK.
By the way , you should not care about sharing connection , since MySQL supports connection pooling ...
0
 
LVL 1

Author Comment

by:RyanUpton
ID: 26197822
Is that for all versions though?

Also I seam to open the connection twice.

Ryan
0
 
LVL 23

Expert Comment

by:Tony McCreath
ID: 26198464
In code you can repeatedly open/close connections however the connection pooling will optimise this by sharing and keeping open a small pool of real connections.

Make sute you have encrypted the connection string in the config file. That would be your weakest point.

You should probably close the connection in the finally section instead of disposing it. Once you dispose it you can't use the connection object again.

I would not use a shared connection as you may get a scenario where two nested bits of code are trying to use the same connection. Create a new connection object each time and do the open/close. Then nested connections would cause the system to just add another connection to the pool.

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:RyanUpton
ID: 26206343
Tiggerito,

excellent, then how do you eventually dispose of the connection?

Ryan:
0
 
LVL 23

Expert Comment

by:Tony McCreath
ID: 26207377
You don't. The connection pool will automatically time-out unused connections and close them.

If your talking about the connection object, you don;t need to dispose it. That is done automatically by .Net garbage collection. Just make sure you close it so that the pool can determine it is no longer being used.
0
 
LVL 1

Author Comment

by:RyanUpton
ID: 26209273
I had a bug once where the connections were not timing out fast enough and built up so much they crashed the application.

Ryan
0
 
LVL 23

Expert Comment

by:Tony McCreath
ID: 26250805
That would be caused if you aren't closing your connections or you create and open a lot of connections at the same time. Otherwise only a few pooled connections should ever be made.

You can specify time-outs, pool size etc. via the connection string
0
 
LVL 1

Author Comment

by:RyanUpton
ID: 26276028
I think your right, I will have to rewite the code. Like this ?
using MySql.Data.MySqlClient; 
 
namespace byDDT 
{ 
    public partial class Default : System.Web.UI.Page 
    { 

        /// <summary> 
        /// Add data to mysql database. 
        /// </summary> 
        /// <returns></returns> 
        private Boolean addRecordtoDatabase() 
        { 

            MySqlConnection gmySqlConnection_Trust;
             
            MySqlCommand gmySqlSelectCommand_Trust;

            if (gmySqlConnection_Trust.State.Equals(ConnectionState.Closed)) 
            { 
                gmySqlConnection_Trust.ConnectionString = ConfigurationManager.ConnectionStrings["dreamhostmysql"].ConnectionString;  
                gmySqlConnection_Trust.Open(); 
            }  
            try 
            { 
                
                //using stored procedure 
                MySqlCommand cmd = new MySqlCommand("sp_adddata", gmySqlConnection_Trust); 
                cmd.Parameters.Clear(); 
                cmd.CommandType = CommandType.StoredProcedure; 
                cmd.Parameters.Add(new MySqlParameter("INPUT1", MySqlDbType.UInt32)); 
                cmd.Parameters.Add(new MySqlParameter("INPUT2", MySqlDbType.UInt32)); 
                cmd.Parameters.Add(new MySqlParameter("INPUT3", MySqlDbType.UInt32)); 
       
 
                //seed fields with data 
                cmd.Parameters["INPUT1" ].Value = 1; 
                cmd.Parameters["INPUT1" ].Value = 2; 
                cmd.Parameters["INPUT1" ].Value = 3; 
 
                cmd.UpdatedRowSource = UpdateRowSource.None; 
                if (gmySqlConnection_Trust.State.Equals(ConnectionState.Closed)) 
                    gmySqlConnection_Trust.Open(); 
                cmd.ExecuteNonQuery(); 
                cmd.Dispose(); 
 
            } 
            catch (Exception ex) 
            { 
                //do error stuff 
                String s = ex.Message; 
            } 
            finally 
            { 
                gmySqlConnection_Trust.Close(); 
            } 
            return true;     
        } 
    } 
}

Open in new window

0
 
LVL 23

Accepted Solution

by:
Tony McCreath earned 2000 total points
ID: 26276342
Thats how I would do it.

You need to create the instances of the Connection .

You could simplfy your connection Open code as you will know that it has not been opened yet.

You don't need the cmd.Dispose(); line but it won't harm.
using MySql.Data.MySqlClient; 
 
namespace byDDT 
{ 
    public partial class Default : System.Web.UI.Page 
    { 

        /// <summary> 
        /// Add data to mysql database. 
        /// </summary> 
        /// <returns></returns> 
        private Boolean addRecordtoDatabase() 
        { 

            MySqlConnection gmySqlConnection_Trust = new MySqlConnection();
   
      
            try 
            { 
                gmySqlConnection_Trust.ConnectionString = ConfigurationManager.ConnectionStrings["dreamhostmysql"].ConnectionString;  
                gmySqlConnection_Trust.Open(); 
                
                //using stored procedure 
                MySqlCommand cmd = new MySqlCommand("sp_adddata", gmySqlConnection_Trust); 
                cmd.Parameters.Clear(); 
                cmd.CommandType = CommandType.StoredProcedure; 
                cmd.Parameters.Add(new MySqlParameter("INPUT1", MySqlDbType.UInt32)); 
                cmd.Parameters.Add(new MySqlParameter("INPUT2", MySqlDbType.UInt32)); 
                cmd.Parameters.Add(new MySqlParameter("INPUT3", MySqlDbType.UInt32)); 
       
 
                //seed fields with data 
                cmd.Parameters["INPUT1" ].Value = 1; 
                cmd.Parameters["INPUT1" ].Value = 2; 
                cmd.Parameters["INPUT1" ].Value = 3; 
 
                cmd.UpdatedRowSource = UpdateRowSource.None; 

                cmd.ExecuteNonQuery();  
 
            } 
            catch (Exception ex) 
            { 
                //do error stuff 
                String s = ex.Message; 
            } 
            finally 
            { 
                gmySqlConnection_Trust.Close(); 
            } 
            return true;     
        } 
    } 
}

Open in new window

0
 
LVL 1

Author Comment

by:RyanUpton
ID: 26280048
shouldn't we check to see if the connection is open? at line 39? What if it fails to open?
0
 
LVL 23

Expert Comment

by:Tony McCreath
ID: 26280624
If it fails to open then you will get an exception. Its OK to still close in this case.
0
 
LVL 1

Author Closing Comment

by:RyanUpton
ID: 31673859
Pure Excellence
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will the learn the benefit of plain text editors and code an HTML5 based template for use in further tutorials.
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question