Using MySQL with C# (III): Coding in C#

13 Jul

  • Install the nuget package: in the nuget management console, type below line and run

Install-Package MySql.Data

  • Let’s create a class SqlUtils, and in the static constructor, type below code:
                    con = new MySqlConnection(connectionStr);
                    con.Open(); //open the connection
                    Console.WriteLine(“Successfully opened database.”);
                catch (MySqlException err) //Catch MySql errors
                    Console.WriteLine(“Error: “ + err.ToString());

where the connection string is:

public static string connectionStr = @”server=localhost;database=MyBooks;userid=root;password=xxxxx;”;
public static MySqlConnection con = null;

  • Next, let’s insert some records: to make sure the sql statement is correct, first try below sql statement in MySQL workbench:

INSERT INTO `Books` (`name`,`code`,`isbn`, `year`)
VALUES(‘Getting Started in MySQL’, ‘#12345’, ‘0987654321’, 2019);



  • OK, success. Now we can do the same insert operation using C# code:

var sql = string.Format(“INSERT INTO `Books` (`name`,`code`,`isbn`, `year`)
           VALUES({0}, {1}, {2}, {3});“,
Getting Started in MySQL“, “#12345“, “0987654321“, 2019);
MySqlCommand cmd = new MySqlCommand(sql, con);

That is it! Compile and Run, you are done!

Some other useful command you might be interested in:

long id = cmd.LastInsertedId; //Get the newly created record id

var sql =  “SELECT name FROM books where code = \”#12345\”;”;
MySqlCommand cmd = new MySqlCommand(sql, con);
var output = cmd.ExecuteScalar();
if (output != null)
        return output.ToString(); // –> “Getting Started in MySQL”

Lastly, don’t forget to close the connection when you don’t need it any more:

if (con != null)
         con.Close(); //safely close the connection

Happy coding!

1 Comment

Posted by on July 13, 2015 in Dotnet/C#, Programming


Tags: , , , , , ,

One response to “Using MySQL with C# (III): Coding in C#

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: