MySQL C#

MySQL C# tutorial

About this tutorial

This is a C# tutorial for the MySQL database. It covers the basics of MySQL programming with C#. In this tutorial, we use the Connector/Net driver. This driver is based on the ADO.NET specification. The examples were created and tested on Ubuntu Linux. There is a similar MySQL Visual Basic tutorial on ZetCode.

If you need to refresh your knowledge of the C# language, there is a full C# tutorial on ZetCode.

About MySQL database

MySQL is a leading open source database management system. It is a multi user, multithreaded database management system. MySQL is especially popular on the web. It is one part of the very popular LAMP platform consisting of Linux, Apache, MySQL, and PHP. Currently MySQL is owned by Oracle. MySQL database is available on most important OS platforms. It runs on BSD Unix, Linux, Windows or Mac OS. Wikipedia and YouTube use MySQL. These sites manage millions of queries each day. MySQL comes in two versions: MySQL server system and MySQL embedded system.

Before we start

On Linux, we need to install several packages to execute the examples in this tutorial: libmysql6.1-cil, mysql-server, mysql-client. We also need to install C# compiler from the Mono project, either from a package or from sources.

The libmysql6.1-cil is the MySQL database connector for CLI. It is written in C# and is available for all CLI languages: C#, Visual Basic, Boo, and others.

From the technical point of view, we need a DLL. On an Ubuntu Linux, it was located under the above path. We need to know the path to the DLL library. To compile our examples.

If you do not already have MySQL installed, we must install it.

$ sudo apt-get install mysql-server

This command installs the MySQL server and various other packages. While installing the package, we are prompted to enter a password for the MySQL root account. For installing MySQL from sources, have a look at MySQL installation page.

$ service mysql status
mysql start/running, process 1238

We check if the MySQL server is running. If not, we need to start the server.

$ sudo -b /usr/local/mysql/bin/mysqld_safe

The above command starts MySQL server using the MySQL server startup script. The way how we start a MySQL server might be different. It depends whether we have installed MySQL from sources or from packages and also on the Linux distro. For further information consult MySQL first steps or your Linux distro information.

Next, we are going to create a new database user and a new database. We use the mysql client.

We create a new database user. We grant all privileges to this user for all tables of the mydb database.

Definitions

ADO.NET is an important part of the .NET framework. It is a specification that unifies access to relational databases, XML files and other application data. A MySQL Connector/Net is an implementation of the ADO.NET specification for the MySQL database. It is a driver written in C# language and is available for all .NET languages.

The Connection, Command, DataReader, DataSet, and DataProvider are the core elements of the .NET data provider model. The Connection creates a connection to a specific data source. The Command object executes an SQL statement against a data source. The DataReader reads streams of data from a data source. The DataSet object is used for offline work with a mass of data. It is a disconnected data representation that can hold data from a variety of different sources. Both DataReader and DataSetare used to work with data; they are used under different circumstances. If we only need to read the results of a query, the DataReader is the better choice. If we need more extensive processing of data, or we want to bind a Winforms control to a database table, the DataSet is preferred.

MySQL version

If the following program runs OK, then we have everything installed OK. We check the version of the MySQL server.

We check for the version of the MySQL database. This time using an SQL query.

string stm = "SELECT VERSION()";

This is the SQL SELECT statement. It returns the version of the database. The VERSION() is a built-in MySQL function.

MySqlCommand cmd = new MySqlCommand(stm, conn);

The MySqlCommand is an object, which is used to execute a query on the database. The parameters are the SQL statement and the connection object.

string version = Convert.ToString(cmd.ExecuteScalar());

There are queries which return only a scalar value. In our case, we want a simple string specifying the version of the database. The ExecuteScalar() is used in such situations. We avoid the overhead of using more complex objects.

$ ./version2.exe
MySQL version : 5.5.9

Same result as in the previous example.

Creating and populating tables

Next we are going to create database tables and fill them with data. These tables will be used throughout this tutorial.

We have a books.sql file. It creates two database tables: Authors and Books. The tables are of InnoDB type. InnoDB databases support foreign key constraints and transactions. We place a foreign key constraint on the AuthorId column of the Books table. We fill the tables with initial data.

Prepared statements

Now we will concern ourselves with prepared statements. When we write prepared statements, we use placeholders instead of directly writing the values into the statements. Prepared statements increase security and performance.

Here we create a prepared statement. When we write prepared statements, we use placeholders instead of directly writing the values into the statements. Prepared statements are faster and guard against SQL injection attacks. The @Name is a placeholder, which is going to be filled later.

cmd.Parameters.AddWithValue("@Name", "Trygve Gulbranssen");

A value is bound to the placeholder.

cmd.ExecuteNonQuery();

The prepared statement is executed. We use the ExecuteNonQuery() method of the MySQLCommand object when we don't expect any data to be returned. This is when we create databases or execute INSERT,UPDATE, and DELETE statements.

The Read() method advances the data reader to the next record. It returns true if there are more rows; otherwise false. We can retrieve the value using the array index notation, or use a specific method to access column values in their native data types. The latter is more efficient.

We get the names of the columns with the GetName() method of the reader. The PadLeft() method returns a new string of a specified length in which the beginning of the current string is padded with spaces. We use this method to align strings properly.

DataSet & MySqlDataAdapter

A DataSet is a copy of the data and the relations among the data from the database tables. It is created in memory and used when extensive processing on data is needed or when we bind data tables to a Winforms control. When the processing is done, the changes are written to the data source. A MySqlDataAdapter is an intermediary between the DataSet and the data source. It populates a DataSet and resolves updates with the data source.

To compile the example, we must include additional DLLs: the DLL for MySQL connector, for theWinforms, Drawing, and for the Data.

Figure: DataGrid

Transaction support

A transaction is an atomic unit of database operations against the data in one or more databases. The effects of all the SQL statements in a transaction can be either all committed to the database or all rolled back.

The MySQL database has different types of storage engines. The most common are the MyISAM and the InnoDB engines. There is a trade-off between data security and database speed. The MyISAM tables are faster to process and they do not support transactions. On the other hand, the InnoDB tables are more safe against the data loss. They support transactions. They are slower to process.

In this program, we want to change the name of the author on the first row of the Authors table. We must also change the books associated with this author. A good example where a transaction is necessary. If we change the author and do not change the author's books, the data is corrupted.