Using MySQL.Data with C# while not making a MySql Connection for transactions

Question

This question is similar to this one: Using MySql.Data with C# while not making a MySqlConnection

Basically, I can see in the documentation: https://dev.mysql.com/doc/connector-net/en/connector-net-connections-pooling.html it says to avoid creating a MySqlConnection object, however, it doesn't say how to handle transactions.

Is there a similar api that is supposed to be used instead of creating a MySqlConnection object as detailed here? https://dev.mysql.com/doc/dev/connector-net/latest/api/data_api/MySql.Data.MySqlClient.MySqlTransaction.html

This is a legacy project that we're trying to update the MySql.Data library to a newer version. So we are trying to avoid as many breaking changes as possible. I would prefer not to have to change to a completely different library — Corey
– Corey, Commented Sep 5, 2024 at 15:15
I'm looking for a different api within MySql.Data that allows you to utilize transactions while also following their directive of not creating a MySqlConnection object directly. — Corey
– Corey, Commented Sep 9, 2024 at 16:30
As Bradley Grainger points out in his answer, there is nothing wrong with creating your own as outlined in your last link. The key to the warning in the documentation is "...globally accessible...and reuse..." because it circumvents the connection pooling. — Kevin
– Kevin, Commented Oct 16, 2024 at 16:29

Bradley Grainger · Accepted Answer · 2024-10-16 13:46:16Z

The documentation doesn't say to "avoid creating a MySqlConnection object" as a blanket rule.

It says:

"Do not create a globally accessible instance of MySqlConnection and then manually open and close it." This is good advice.
"One approach that simplifies things is to avoid creating a MySqlConnection object manually. Instead, use the overloaded methods that take a connection string as an argument." This is a recommendation for a possible approach you can use when a special-purpose API has a method that takes a connection string and does its work.

However, you don't have to do that, and there's nothing wrong with new MySqlConnection(connectionString).

The other documentation you linked to (here) shows a perfectly acceptable way of opening a connection and starting a transaction:

using MySqlConnection myConnection = new MySqlConnection(myConnString);
myConnection.Open();
// Start a local transaction
using MySqlTransaction myTrans = myConnection.BeginTransaction();
using MySqlCommand myCommand = myConnection.CreateCommand();
// do your work here...
myTrans.Commit();

stuifrasncoe · Accepted Answer · 2025-05-22 17:07:11Z

In my case if I have a project including a database, firstly I always create a class where I define the constructors necessary for the certain database datas. For examples:

Constructors.cs:

namespace ExampleForMySqlDatabase{
    class Course
    {
        private long id;
        private string name;
        private int length;

        public Course(long id, string name, int length)
        {
            this.id = id;
            this.name = name;
            this.length = length;
        }

        public long Id { get => id; set => id = value; }
        public string Name { get => name; set => name = value; }
        public int Length { get => length; set => length = value; }

        /*public override string ToString()
        {
            return $"id: {id}, etc..(To write out the datas, without this, it's only an object)";*/
        }
    }
}

After the correct datas in that file, next you need to create a new class file where you will read in the database and will do other methods using it. You need to download a certain extension, Tools->Nuget package manager->Manage Nuget packages for solution-> Browse->Download MySql.Data

ReadIn.cs

using MySql.Data.MySqlClient; //Don't forget to import it


namespace ExampleForMySqlDatabase
{
    internal class ReadIn    {
        private List<Course> courses;

        public ExampleForMySqlDatabase()
        {
            Loaded();
            GetLongestCourse();
            GetCourseLength();
        }

        public void Loaded()
        {
            string connStr = "server=localhost;user=root;database=The name of your database;port=3306;password=";
            MySqlConnection conn = new MySqlConnection(connStr);
            try
            {
                conn.Open();

                string sql = "SELECT id, name, length FROM The name of your database";
                MySqlCommand cmd = new MySqlCommand(sql, conn);
                MySqlDataReader rdr = cmd.ExecuteReader();
                courses = new List<Course>();

                while (rdr.Read())
                {
                    Course course = new Course(Convert.ToInt64(rdr.GetInt32(0)), rdr.GetString(1), rdr.GetInt32(2));
                    courses.Add(course);
                }
                rdr.Close();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.ToString());
                Environment.Exit(1);
            }
            conn.Close();
        }
        //this for looking for the longest for example
       public void GetLongestCourse()
        {
            Course course = courses[0];
            long longest = course.Id;
        
            for (int i = 0; i < courses.Count; i++)
            {
                if (courses[i].Length > longest)
                {
                    longest = course.Length;
                    course = courses[i];
                }
            }
        
            Console.WriteLine($"Longest course: \n\t Name: {course.Name}}\n\t Length: {course.Length}, \n\tOktató: {course.Instructor}");
        }
        //or this you want to search for someone:
        public void GetCourseLength()
        {
            Console.Write("Course name: ");
            string course_name = Console.ReadLine();
        
            for (int i = 0; i < courses.Count; i++)
            {
                if (courses[i].Name == course_name)
                {
                    Console.WriteLine("Length of the course: " + courses[i].Length);
                    return;
                }
            }
        
            Console.WriteLine("No courses found");
        }
        
    }

}

And in your main Program.cs file it should look like this:

 internal class Program
 {
     static void Main(string[] args)
     {
         ExampleForMySqlDatabase exampleForMySqlDatabase= new ExampleForMySqlDatabase();
         Console.ReadKey();
     }
 }

Collectives™ on Stack Overflow

Using MySQL.Data with C# while not making a MySql Connection for transactions

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related