none
Query, write & delete from a MySQL database in C# RRS feed

  • Question

  • I have my connection set up to the local MySQL database and am first trying to query out data, but every example I look at does it in a different way.

    What I need to do is query the database to read out certain ranges of data from a table that meet some conditions (that are between two values). These entries then needs to be written to an array for me to manipulate as I need to.

    The next part is to write some of the manipulated data back into another table. This will just append it to the end of the table.

    Finally, I need to delete entries from tables that meet certain criteria, i.e. a post is older than a certain timestamp.

    Any help would be great!

    Thanks, Dan.

    P.S. I have taken a long break from C# coding and have just picked it up again for this project, so don't assume I know things :)

    Sunday, April 15, 2018 1:22 PM

All replies

  • Hi DPashley,

    Thank you for posting here.

    For your question, please download the source file from the code project. In this article, it demonstrated how to connect C# to MySQL and query the tables using simple examples for the insert, update, delete and select statements.

    https://www.codeproject.com/Articles/43438/Connect-C-to-MySQL

    Best Regards,

    Wendy


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, April 17, 2018 2:11 AM
    Moderator
  • Hello,

    The link Wendy provided looks solid other than when creating the SQL statements which have a WHERE condition or are setting values such as in a INSERT or UPDATE they are not using parameters.

    For instance, in the following example taken from the link to the naked eye this looks good yet what do you think would happen with what is shown in the second code block?

    Figure 1

    //Insert statement
    public void Insert()
    {
        string query = "INSERT INTO tableinfo (name, age) VALUES('John Smith', '33')";
    
        //open connection
        if (this.OpenConnection() == true)
        {
            //create command and assign the query and connection from the constructor
            MySqlCommand cmd = new MySqlCommand(query, connection);
            
            //Execute command
            cmd.ExecuteNonQuery();
    
            //close connection
            this.CloseConnection();
        }
    }

    Figure 2

    INSERT INTO tableinfo (name, age) VALUES('Johm O' Reilly', '33')

    In the second code block this would cause an exception at runtime because of an unescaped apostrophe. Some might say you could use string replace method to fix it yet a parameter would be better.

    Also not using parameters opens you up to hackers e.g. 

    So by combining Wendy's link with using parameters you have a good start. One last tip, use Using statements which are there for immediately disposing of objects such as the connection and command objects.

    using (var conn = new MySqlConnection(ConnectionString))
    {
         conn.Open();
         using (MySqlCommand cmd = conn.CreateCommand())
         {
              cmd.CommandText = "SELECT * FROM SomeTable";
              using (MySqlDataReader reader = cmd.ExecuteReader())
              {
                   while (reader.Read())
                   {
                       // DO SOME WORK
                   }
              }
         }
    }
    It's best to not have a single connection but instead create the objects when needed and dispose of them immediately.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Tuesday, April 17, 2018 10:23 AM
    Moderator