How to Update A MySQL Database with C#

Maintaining a Database by using the MySQL .NET Connector and C#

May 30, 2009
Mark Alexander Bain

Update a MySQL Database with C# - Mark Alexander Bain

It is important to keep the contents of a database up to date. It's fortunate, therefore, that C# and the MySQL .NET connector make that an easy task.

There are 4 basic kinds of SQL (Structured Query Language) statements that the typical C# programmer is interested in. These are:

Insert
Select
Update
Delete

These can be used by the C# programmer to add records to MySQL database, to view them, to delete them and, as will be shown in this article, to modify the contents of the database.

The Update Statement and the Importance of the Where Clause

The update statement is used to modify the details stored in individual records in a database, and has the format:

update table_name

set field1 = ‘new information’

However, this by itself is a dangerous statement because it will update every record in the table. It is, therefore, essential that the “where” clause is always used:

update table_name

set field1 = ‘new information’

where id = 26

And it’s worth noting that more that one field in the record can be updated in a single update statement:

update table_name

set field1 = ‘new information’, field2 = ‘updated too’

where id = 26

It’s also worth noting that the statements are not case sensitive, so the query can be written as:

UPDATE table_name

SET field1 = ‘new information’, field2 = ‘updated too’

WHERE id = 26

However, it is always good practice to change one format and stick to it.

Connecting to a MySQL Database

Whichever format a programmer chooses and regardless of whether they are going to insert, select, update or delete records, they will first need to connect to the MySQL database. Therefore that is covered in a separate article: How to Connect to a MySQL Database with C#. The article shows how to create the MySQL connection that will be required to carry out any database operations.

The Update Statement and C#

The insert statement will be a command run via the connection and so the first step is to create a command object:

MySqlCommand command = connection.CreateCommand ();

The C# application must, of course, determine the information to be update. In this example that’s done as part of a console application (as shown in figure 1 at the bottom of this article):

Console.Write("Enter Sample Number> ");

int sample_no = Convert.ToInt16(Console.ReadLine());

That information can then be used to create the update statement (the table structure used in this example can be seen in figure 2):

command.CommandText =

"update samples"

+ " set processed = now() "

+ " where id = " + sample_no;

And the command can be executed on the database:

MySqlDataReader result = command.ExecuteReader();

If the database is examined at the end of this process then the selected record will have been updated with a current date.

Mark Alexander Bain - Mark Alexander Bain is a writer, Mo Bro and consultant for all aspects of software development at dsquared. He has also written regularly ...