How do I access a MySQL database with C#?

This was a little bit easier for me because I had just figured all this out on Microsoft SQL and (lo and behold), MySQL had created the exact same function structure for MySQL.

So I read through this document first:
Beginning MYSQL 5 with Visual Studio.NET 2005.pdf

It was very informative and showed this code to get into a MySQL database:

string connectionString = "server=ServerName; user id=UserName; password=P@sswd!; database=MyDatabase";
string query = "Select * from users";
MySqlConnection connection = new MySqlConnection(connectionString);
try
{
    connection.Open();
    MySqlDataAdapter dataAdapter = new MySqlDataAdapter(query, connection);
    DataSet dataSet = new DataSet();
    dataAdapter.Fill(dataSet, "users");
}

I had been accessing tables in a Microsoft SQL database using a different set of functions, so I tested to see if the method I had been using for Microsoft SQL would work for MySQL, since the object and function names are almost identical.

The following code also accessed the database and I like it better because a DataTable seems more of an obvious choice to return data from table.

string connectionString = "server=ServerName; user id=UserName; password=P@sswd!; database=MyDatabase";
string query = "Select * from users";
MySqlConnection connection = new MySqlConnection(connectionString);
try
{
    connection.Open();
    MySqlCommand command = new MySqlCommand(query, connection);
    MySqlDataReader reader = command.ExecuteReader();;
    DataTable table = new DataTable();
    table.Load(reader);
}

So I left wondering how these two methods are different…I guess I need to answer the following:

  1. Which of the above two SQL database access methods should be considered best practice and why?
  2. Is one more efficient than the other?
  3. What is the difference between a DataSet and a DataTable?
    DataSet Class
    DataTable Class

Leave a Reply

*