MySQL connection in C#.Net

MySQL Connection

MySQL is one of the popular database management system among desktop and networks (web and local).

For using MySQL with C#.Net we have to add the dependency using the Nugget Package Manager

NuGet\Install-Package MySql.Data -Version 8.0.32

Connection string

A minimal MySQL connection string take server, uid, pwd and a database.

  const string  cstr= "server=127.0.0.1;uid=root;pwd=123;database=mydb";

Let's configure

 try
            {
                using (var con = new MySql.Data.MySqlClient.MySqlConnection())
                {
                    con.ConnectionString = cstr;
                    
                    con.Open();
                    if(con.State == System.Data.ConnectionState.Open)
                    {
                        con.Insert<User>(new User() { Name = "scott" });
                        var r = con.Query("select * from users");
                    }
                }
            }
            catch (Exception)
            {
  string cmdStr = "CREATE TABLE IF NOT EXISTS USERS ( id INT auto_increment,NAME VARCHAR(50),PRIMARY KEY (ID));";
   using (var con = new MySql.Data.MySqlClient.MySqlConnection(cstr))
                {
                    var r = con.Execute(crtStrting);

                }

            }

We also executed a create table logic. For ease of use I prefer to use ORM (dapper) for easy data handling.

For more information on Dapper refer the Sqlite example.