17
MariaDB in C#

This example demonstrates the MariaDB database in a C# application. If you skipped Chapter 16, “MariaDB in Python,” which built a similar example in Python, go to that chapter and read the beginning and the first two sections, which are described in the following list:

  • “Install MariaDB” explains how to install MariaDB.
  • “Run HeidiSQL” explains how to use the HeidiSQL database management tool to create a MariaDB database.

When you reach the section “Create the Program” in Chapter 16, return to this chapter and read the following sections.

CREATE THE PROGRAM

To create a C# program to work with the MariaDB extraterrestrial animals database, create a new C# Console App (.NET Framework) and then add the code described in the following sections.

Jupyter Notebook lets you execute cells individually, but C# won't let you do that. It will, however, let you group related code into methods, so that's what we'll do here.

Add code to the main method so that it looks like the following:

using MySqlConnector;
…
static void Main(string[] args)
{
    CreateDatabase();
    // CreateTables();
    // CreateData();
    // FetchData();
 
    Console.ReadLine();
}

Initially, the main method only calls CreateDatabase (described shortly) and then waits for the user to press Enter. You'll uncomment the other statements as you work through the following sections, which explain how the other methods work.

Install MySqlConnector

To install a database connector, open the Project menu and select Manage NuGet Packages. If you select the Browse tab and search for MariaDB, you'll find more than 150 offerings. Most of them will probably work, but I decided to use the tool called MySqlConnector. Figure 17.1 shows that package selected in the NuGet Package Manager.

A representation exhibits the package selected in the NuGet Package Manager.

FIGURE 17.1

You can see in the figure that I installed version 2.1.13. When you build your program, the latest stable version will have definitely changed. In fact, it's different now when I'm editing just a few weeks after I built the initial program. Try the latest stable version. If that version won't work with the example code, try selecting version 2.1.13 to see if that works.

Create the Database

The following code shows the CreateDatabase method:

// Create the AnimalData database.
private static void CreateDatabase()
{
    // Connect to the database server.
    string connectString =
        "server=127.0.0.1;" + 
        "uid=root;" +
        "pwd=TheSecretPassword";
    using (MySqlConnection conn = new MySqlConnection(connectString))
    {
        // The connection must be open before you can use it.
        conn.Open();
 
        // Create a command.
        using (MySqlCommand cmd = conn.CreateCommand())
        {
            // Drop the database if it exists.
            cmd.CommandText = "DROP DATABASE IF EXISTS AnimalData";
            cmd.ExecuteNonQuery();
 
            // Create the database.
            cmd.CommandText = "CREATE DATABASE AnimalData";
            cmd.ExecuteNonQuery();
 
            // List the available databases.
            cmd.CommandText = "SHOW DATABASES";
            MySqlDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                Console.WriteLine(reader[0].ToString());
            }
        } // End using MySqlCommand cmd
    } // End using MySqlConnection conn
 
    Console.WriteLine();
}

This code first creates a database connect string that includes the server (127.0.0.1 means the local machine), username (root), and password. Replace the password shown here with the password that you used for the root user when you installed MariaDB.

The code passes the connect string to the constructor when it creates a MySqlConnection object. That object uses system resources, so it provides a Dispose method to free them when you're done with the connection. To ensure that you don't forget to call Dispose, the code creates the connection inside a using statement, so the program will call Dispose automatically when the code block ends.

Next, the program opens the connection. It then uses it to create a MySqlCommand object. Like MySqlConnection, MySqlCommand has a Dispose method so the program creates the command inside a using statement.

The code then sets the command object's CommandText property to the SQL statement DROP DATABASE IF EXISTS AnimalData. As I'm sure you can guess, that statement drops the AnimalData database if it exists.

The code calls the command object's ExecuteNonQuery method to execute the statement. That object has a few other ExecuteXxx methods that execute different kinds of statements. This time we're using ExecuteNonQuery because the DROP TABLE statement is not a query and does not return data.

The program then performs similar steps, setting the command's text to CREATE DATABASE AnimalData and executing that non-query.

After it has created the database, the code executes the SHOW DATABASES command. This command returns results, so the program calls the command's ExecuteReader method to perform the query and obtain a data reader object that holds the results.

The code calls the reader's Read method to advance to the first result row. That method returns true if as it retrieves a row and it returns false if it has run out of rows, so the while statement continues until the program has processed all the returned data.

Inside the loop, the program writes the value in the reader's first field to the console window. The SHOW DATABASES command only returns the names of the databases, so the reader only has one field.

If you run the program now, it deletes the database (if it exists), creates the new database, displays a list of the available databases, and then waits until you press Enter before closing the program. The following text shows the program's output:

animaldata
information_schema
mysql
performance:schema
sys

In this output, you can see the new table animaldata and the system tables.

Define Tables

The following code shows the CreateTables method:

// Create the tables.
private static void CreateTables()
{
    // Connect to the database server.
    string connectString =
        "server=127.0.0.1;" +
        "uid=root;" +
        "pwd=TheSecretPassword;" +
        "database=AnimalData";
    using (MySqlConnection conn = new MySqlConnection(connectString))
    {
        // The connection must be open before you can use it.
        conn.Open();
 
        // Create a command.
        using (MySqlCommand cmd = conn.CreateCommand())
        {
            // Drop any existing tables.
            cmd.CommandText = "DROP TABLE IF EXISTS AnimalPlanets";
            cmd.ExecuteNonQuery();
 
            cmd.CommandText = "DROP TABLE IF EXISTS Animals";
            cmd.ExecuteNonQuery();
 
            cmd.CommandText = "DROP TABLE IF EXISTS Planets";
            cmd.ExecuteNonQuery();
 
            // Create the Animals table.
            cmd.CommandText = @"CREATE TABLE Animals (
                Size TEXT NOT NULL,
                Animal TEXT NOT NULL,
                AnimalId INT PRIMARY KEY)";
            cmd.ExecuteNonQuery();
 
            // Create the Planets table.
            cmd.CommandText = @"CREATE TABLE Planets (
                PlanetId INT PRIMARY KEY,
                HomePlanet TEXT NOT NULL,
                PlanetaryMass FLOAT NOT NULL)";
            cmd.ExecuteNonQuery();
 
            // Create the AnimalPlanets table.
            // Foreign keys:
            //    AnimalPlanets.AnimalId = Animals.AnimalId
            //    AnimalPlanets.PlanetId = Planets.PlanetId
            cmd.CommandText = @"CREATE TABLE AnimalPlanets (
                AnimalId INT,
                PlanetId INT,
                CONSTRAINT fk_animals
                    FOREIGN KEY(AnimalId) REFERENCES Animals(AnimalId)
                    ON DELETE RESTRICT
                    ON UPDATE RESTRICT,
                CONSTRAINT fk_planets
                    FOREIGN KEY(PlanetId) REFERENCES Planets(PlanetId)
                    ON DELETE RESTRICT
                    ON UPDATE RESTRICT,
                PRIMARY KEY(AnimalId, PlanetId)
            )";
            cmd.ExecuteNonQuery();
        } // End using MySqlCommand cmd
    } // End using MySqlConnection conn
 
    Console.WriteLine("Created tables");
}

This code creates a database connection much as before, except this time it includes the database's name. It then opens the connection and uses it to create a command object.

Next, the code executes three DROP TABLE statements to delete the AnimalPlanets, Animals, and Planets tables if they already exist. Note, you must drop the tables in a valid order. In this database, the AnimalPlanets table has foreign key constraints matching fields in the Animals and Planets tables. If the AnimalPlanets table contains data and you drop either of the other tables first, then the AnimalPlanets records will violate their constraints and the program will crash.

Dropping tables (if they exist) and then re-creating them is a common technique for building test databases. It lets you remove any experimental changes to the tables so that you can start with a known state. (This is the main reason I didn't build the database by using HeidiSQL, which is described in the preceding chapter. If you build the database with a database management tool and then later decide to modify the table structure, you need to use the tool to rebuild the database's structure. It's often faster and easier to modify a program or script and rerun it to rebuild the database from scratch.)

Next, the code executes three commands to create the new tables. The C# code uses multiline string literals (beginning with the @ character) for the command text, so the text contains embedded carriage returns, line feeds, and spaces. Fortunately, the database engine ignores whitespace so that causes no problems.

The third CREATE TABLE statement is a bit more complicated than the first two because it also defines foreign key constraints for the table. The first constraint requires that any value in the AnimalPlanets.AnimalId field must exist in some Animals.AnimalId field. The second constraint requires that any AnimalPlanets.PlanetId field must exist in some Planets.PlanetId field.

The ON UPDATE RESTRICT and ON DELETE RESTRICT clauses mean that the database will prevent (restrict) any updates or deletions that violate the constraints. Other choices can make a change cascade to related records or null out related fields.

This final CREATE TABLE statement also sets the table's primary key to be the combination of the AnimalId and PlanetId fields.

Create Data

The following code shows the CreateData method:

// Create the data.
private static void CreateData()
{
    // Connect to the database server.
    string connectString =
        "server=127.0.0.1;" +
        "uid=root;" +
        "pwd=TheSecretPassword;" +
        "database=AnimalData";
    using (MySqlConnection conn = new MySqlConnection(connectString))
    {
        // The connection must be open before you can use it.
        conn.Open();
 
        // Create a command.
        using (MySqlCommand cmd = conn.CreateCommand())
        {
            // Delete any previous records.
            cmd.CommandText = "DELETE FROM AnimalPlanets";
            cmd.ExecuteNonQuery();
 
            cmd.CommandText = "DELETE FROM Animals";
            cmd.ExecuteNonQuery();
 
            cmd.CommandText = "DELETE FROM Planets";
            cmd.ExecuteNonQuery();
 
            using (MySqlTransaction trans = conn.BeginTransaction())
            {
                cmd.Transaction = trans;
 
                // Add records to the Animals table.
                cmd.CommandText = @"INSERT INTO Animals
                    (Size, Animal, AnimalId) VALUES
                    ('Medium', 'Hermaflamingo', 1)";
                cmd.ExecuteNonQuery();
 
                cmd.CommandText = @"INSERT INTO Animals
                    (Size, Animal, AnimalId) VALUES
                    ('Large', 'Skunkopotamus', 2)";
                cmd.ExecuteNonQuery();
 
                cmd.CommandText = @"INSERT INTO Animals
                    (Size, Animal, AnimalId) VALUES
                    ('Medium', 'Mothalope', 3)";
                cmd.ExecuteNonQuery();
 
                cmd.CommandText = @"INSERT INTO Animals
                    (Size, Animal, AnimalId) VALUES
                    ('Small', 'Platypus', 4)";
                cmd.ExecuteNonQuery();
 
                // Add records to the Planets table.
                cmd.CommandText = @"INSERT INTO Planets
                    (PlanetId, HomePlanet, PlanetaryMass) VALUES
                    (@planet_id, @home_planet, @planetary_mass)";
                cmd.Prepare();
 
                cmd.Parameters.AddWithValue("@planet_id", 101);
                cmd.Parameters.AddWithValue("@home_planet", "Virgon 4");
                cmd.Parameters.AddWithValue("@planetary_mass", 1.21);
                cmd.ExecuteNonQuery();
 
                cmd.Parameters[0].Value = 102;
                cmd.Parameters[1].Value = "Dilbertopia";
                cmd.Parameters[2].Value = 0.88;
                cmd.ExecuteNonQuery();
 
                cmd.Parameters[0].Value = 103;
                cmd.Parameters[1].Value = "Xanth";
                cmd.Parameters[2].Value = 0.01;
                cmd.ExecuteNonQuery();
 
                cmd.Parameters[0].Value = 104;
                cmd.Parameters[1].Value = "Australia";
                cmd.Parameters[2].Value = 1.0;
                cmd.ExecuteNonQuery();
 
                // Add records to the AnimalPlanets table.
                cmd.CommandText = @"INSERT INTO AnimalPlanets
                    (AnimalId, PlanetId) VALUES (@animal_id, @planet_id)";
                cmd.Prepare();
 
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("@animal_id", 1);
                cmd.Parameters.AddWithValue("@planet_id", 101);
                cmd.ExecuteNonQuery();
 
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("@animal_id", 2);
                cmd.Parameters.AddWithValue("@planet_id", 101);
                cmd.ExecuteNonQuery();
 
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("@animal_id", 2);
                cmd.Parameters.AddWithValue("@planet_id", 102);
                cmd.ExecuteNonQuery();
 
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("@animal_id", 3);
                cmd.Parameters.AddWithValue("@planet_id", 103);
                cmd.ExecuteNonQuery();
 
                cmd.Parameters.Clear();
                cmd.Parameters.AddWithValue("@animal_id", 4);
                cmd.Parameters.AddWithValue("@planet_id", 104);
                cmd.ExecuteNonQuery();
 
                // Commit the changes.
                trans.Commit();
            } // End using MySqlTransaction trans
        } // End using MySqlCommand cmd
    } // End using MySqlConnection conn
 
    Console.WriteLine("Created data");
}

This code creates a database connection and defines a command as usual. It then deletes all the records from the database's three tables. Emptying a table before adding test records is another common testing technique.

Next, the code creates a transaction object so it can insert records within a transaction. When it is finished, it can commit or roll back those insertions.

Like many database objects, the transaction object has a Dispose method, so the code creates it inside a using statement so that it is automatically disposed when the using block ends.

Before it can use the command with the transaction, the code sets the command's Transaction property to the transaction object.

The program then demonstrates three techniques for creating new records. First, it sets the command object's CommandText property to an SQL INSERT statement that includes the values that will be inserted. It then calls the command's ExecuteNonQuery method to perform the insertion. The code repeats this sequence a few times to add records to the Animals table.

For the second insertion technique, the code sets the command's text to an INSERT statement where the values are replaced by named placeholders that start with an @ symbol. It then calls the command's Prepare method to make the database compile the command for faster execution.

To insert a record, the program must use the command's Parameters collection. The code first uses the collection's AddWithValue method to add parameters to the collection specifying their names and values. After it has added the values, the program calls the command's ExecuteNonQuery method to add the record.

Now that the parameters have been created, the code replaces their values with the values for a new record and calls ExecuteNonQuery again. The program repeats that step to create the other Planets records.

The program uses the third technique to insert records into the AnimalPlanets table. It sets the command text as before and prepares the command. For each record, it then clears the Parameters collection, uses its AddWithValue method to create parameters, and calls ExecuteNonQuery to create the record.

After it has created all of the new records, the code calls the transaction object's Commit method to make the new records permanent.

Fetch Data

The following PrintReader method displays a query's results:

// Display a query's results.
private static void PrintReader(MySqlDataReader reader)
{
    while (reader.Read())
    {
        Console.Write(reader[0].ToString());
        for (int i = 1; i < reader.FieldCount; i++)
        {
            Console.Write(", " + reader[i].ToString());
        }
        Console.WriteLine();
    }
    reader.Close();
}

This method loops through a MySqlDataReader object's results. For each returned row, the code displays the first field's value. It then loops through the remaining values, displaying each with a comma in front.

The code starts a new line after each row and closes the reader after it has displayed all the rows. (You cannot change a command object's text if there is an open reader associated with that command, so the code closes the reader.)

The following code shows the FetchData method, which uses the PrintReader method to check the database's contents:

// Fetch the data.
private static void FetchData()
{
    // Connect to the database server.
    string connectString =
        "server=127.0.0.1;" +
        "uid=root;" +
        "pwd=TheSecretPassword;" +
        "database=AnimalData";
    using (MySqlConnection conn = new MySqlConnection(connectString))
    {
        // The connection must be open before you can use it.
        conn.Open();
 
        // Create a command.
        using (MySqlCommand cmd = conn.CreateCommand())
        {
            // Select each table's records.
            Console.WriteLine("
*** Animals ***");
            cmd.CommandText = "SELECT * FROM Animals";
            PrintReader(cmd.ExecuteReader());
 
            Console.WriteLine("
*** Planets ***");
            cmd.CommandText = "SELECT * FROM Planets";
            PrintReader(cmd.ExecuteReader());
 
            Console.WriteLine("
*** AnimalPlanets ***");
            cmd.CommandText = "SELECT * FROM AnimalPlanets";
            PrintReader(cmd.ExecuteReader());
 
            // Select matching records.
            Console.WriteLine("
*** Results ***");
            cmd.CommandText = @"SELECT Size, Animal, HomePlanet, PlanetaryMass
                FROM Animals, Planets, AnimalPlanets
                WHERE
                    Animals.AnimalId = AnimalPlanets.AnimalId AND
                    Planets.PlanetId = AnimalPlanets.PlanetId
                ORDER BY Animal";
            PrintReader(cmd.ExecuteReader());
        } // End using MySqlCommand cmd
    } // End using MySqlConnection conn
}

This code connects to the database and creates a command object as usual. It then uses the command to execute three queries that fetch the data from the tables. It calls the command's ExecuteReader method and passes the result to the PrintReader method to display the results.

The code then uses the same steps to execute a more complicated query that selects matching records from all three tables.

The following text shows the results:

*** Animals ***
Medium, Hermaflamingo, 1
Large, Skunkopotamus, 2
Medium, Mothalope, 3
Small, Platypus, 4
 
*** Planets ***
101, Virgon 4, 1.21
102, Dilbertopia, 0.88
103, Xanth, 0.01
104, Australia, 1
 
*** AnimalPlanets ***
1, 101
2, 101
2, 102
3, 103
4, 104
 
*** Results ***
Medium, Hermaflamingo, Virgon 4, 1.21
Medium, Mothalope, Xanth, 0.01
Small, Platypus, Australia, 1
Large, Skunkopotamus, Virgon 4, 1.21
Large, Skunkopotamus, Dilbertopia, 0.88

SUMMARY

MariaDB is a direct descendant of the extremely popular MySQL database. It is a standard relational database that also provides column-oriented features, making it useful for data warehousing and large-scale data analysis and analytics.

The example described in this chapter creates a new database, defines some tables for it, and adds a little test data to those tables. This sort of application is useful for getting the database up and running, but in a real application you'd probably also need a user interface to let the user query and modify the data.

The next chapter describes an example that uses Python and PostgreSQL to build another relational database. Before you move on to Chapter 18, however, use the following exercises to test your understanding of the material covered in this chapter. You can find the solutions to these exercises in Appendix A.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.15.226.79