How to Add Records to MySQL With C#

Using C# to Run an SQL Insert Statement on a MySQL Database

May 27, 2009 Mark Alexander Bain

The SQL Insert Statement and C# - Mark Alexander Bain

As well as viewing data in a MySQL database, a C# programmer can add information by using the SQL insert statement and the MySQL .NET connector

Many C# applications use databases (such as MySQL) in the background, and they read stored information from the databases. This information can then be displayed to the application’s users. However, programming can do much more than just read data. They can also:

add records to the database
modify existing records in the database
remove information stored in the database

And they do this by means of send SQL (or Sequential Query Language) statements to the database.

The Types of SQL Statements

A typical C# application will use 4 types of SQL statements. These are:

insert
select
update
delete

With just those 4 statements the C# programmer can carry out all of the database operations that their application will require, and this article will show how to do one of those - inserting records into a MySQL database.

Connecting to a MySQL Database

Before connecting to a MySQL database the application developer will require two things:

the MySQL .NET connector
a MySQL database

Both of which are covered in How to Access MySQL with C#, but the structure of the table used in this article can be seen in figure 1 and the bottom of this page. Once they the database and the connector are in place they can be used to create a database connection. The programmer does this by including the correct libraries:

using System;

using MySql.Data.MySqlClient;

Then creating an appropriate class (in this case to produce a terminal application):

Read on

class Program

{

public static void Main(string[] args) {

The next step is to create a connection object:

MySqlConnection connection = new MySqlConnection ();

To set its connection string:

connection.ConnectionString =

"server=localhost;"

+ "database=aec;"

+ "uid=aec_user;"

+ "password=aec;";

And then to open the connection:

connection.Open ();

With that the database will be ready to receive SQL statements.

Using the SQL Insert Statement

In this example a console application (as shown in figure 2) will ask the user for the information to be inserted into the database:

Console.Write("Enter Description> ");

string description = Console.ReadLine();

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

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

An SQL command must then be created:

MySqlCommand command = connection.CreateCommand ();

And its command text loaded with a suitable SQL insert statement:

command.CommandText =

"insert into samples (description, site_id, received)"

+ " values "

+ "('" + description + "', " + site_no + ", now())";

At this point (especially during testing) it may be worthwhile printing the SQL statement to the screen:

Console.WriteLine(command.CommandText);

The application can then execute the command on the database:

MySqlDataReader result = command.ExecuteReader();

And with that a new record will have been inserted into the database.

Obtaining the Last Inserted ID

It is always a good idea to tell the user what is going on, for instance by telling them the id of the record that has just been added:

Console.WriteLine( "Sample " + command.LastInsertedId + " added");

So, with the record added and the user informed of that fact, the final step is to close the connection.

Closing the Database Connection

The command will return a result, but this is not used (since this is an insert statement) and it should, therefore, be closed:

result.Close();

As should the connection:

connection.Close();

In this way, as well as inserting a new record, the application will also free up any memory used by the activity and the application user will have a very simple way of adding information to the database.

Share Article |