Programming Languages on Power

 View Only

Connect a .NET 7 app running on IBM Power to a MySQL database using ADO.NET

By Sapana Khemkar posted Tue January 17, 2023 08:26 AM


In this blog, we explain how to connect a sample .NET 7 application that runs on Power to a MySQL database using an ADO.NET connector.

For the purpose of this blog, we have taken an example .NET 7 application that connects to a MySQL server running on the same local host. However, you can modify this sample application to connect to a remote server as well. This application has been verified on a Power virtual machine (VM) running RHEL 8.7, but it should work on RHEL 9.1 as well.

Set up the MySQL Server

Complete the following steps to set up a MySQL server on your local Power system:

  1. Install the MySQL server using dnf/yum (software package manager).

    #install MySQL Server dnf install -y mysql-server
  2. Start the MySQL service.

    #start MySQL service systemctl start mysqld.service
  3. Enable the MySQL service to start at boot.

    systemctl enable mysqld.service
  4. Create a database for testing purpose using the command line tool.

    # Invoke mysql client from command line to get MariaDB command prompt
    # Create database
    CREATE DATABASE <Your Database Name>;
    # Switch to newly created database
    USE <YourDatabaseName>;
    # Create table in database with different columns
    CREATE TABLE <YourTableName> ( <ColoumnName1> <datatype1>, <ColoumnName2> <datatype2>, ..);
    # Insert some dummy data into table
    INSERT INTO <YourTableName> VALUES (<DummyData1>, DummyData1>, ...);
    # View table 
    SELECT * FROM <YourTableName> ;
    #exit from MariaDB client prmot

    The output for the test database and table that you created in step 4 should look as follows.

    mysql> USE TestDB;
    Database changed
    mysql> CREATE TABLE Persons ( PersonID int, LastName varchar(255), FirstName varchar(255), Address varchar(255), City varchar(255) );
    Query OK, 0 rows affected (0.02 sec)
    mysql> INSERT INTO Persons VALUES (102, 'LMN', 'PQR', 'abc road', 'xyz city');
    Query OK, 1 row affected (0.01 sec)
    mysql> mysql>INSERT INTO Persons VALUES (101, 'ABC', 'DEF', 'xyz road', 'Pune');
    ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'mysql>INSERT INTO Persons VALUES (101, 'ABC', 'DEF', 'xyz road', 'Pune')' at line 1
    mysql> INSERT INTO Persons VALUES (101, 'ABC', 'DEF', 'xyz road', 'Pune');
    Query OK, 1 row affected (0.01 sec)
    mysql> SELECT * FROM Persons;
    | PersonID | LastName | FirstName | Address  | City     |
    |      102 | LMN      | PQR       | abc road | xyz city |
    |      101 | ABC      | DEF       | xyz road | Pune     |
    2 rows in set (0.00 sec)
    mysql> quit

Create a .NET 7 application with MySql.Data package

Complete the following steps to create a .NET 7 console application:

  1. Create a console application using the dotnet new command.

    dotnet new console -o mysql_sample

    Ensure that the output that is displayed is as follows:

    # dotnet new console -o mysql_sample
    The template "Console App" was created successfully.
    Processing post-creation actions...
    Restoring /root/temp/mysql_sample/mysql_sample.csproj:
      Determining projects to restore...
      Restored /root/temp/mysql_sample/mysql_sample.csproj (in 238 ms).
    Restore succeeded.

  2. Add the MySql.Data package using the dotnet add package command. This command adds a package reference in the csproj file.

    cd mysql_sample/ dotnet add package MySql.Data
    # cat mysql_sample.csproj
    <Project Sdk="Microsoft.NET.Sdk">
        <PackageReference Include="MySql.Data" Version="8.0.31" />
  3. Copy the following code in the Program.cs file. This sample program:

    • Connects to the YourTestDatabase on the local MySql server using the ADO.NET connector.
    • Reads data from all rows and columns from the YourTestTable.
    • Displays all rows and columns on console output.
      using System;
      using System.Collections.Generic;
      using System.Text;
      using MySql.Data.MySqlClient;
      namespace ConsoleApplication1
          class Program
              static void Main(string[] args)
                  string MyConString = "SERVER=localhost; " +
                  "DATABASE=<YourDatabaseName>; " +
                  "UID=<YourUserName>; " +
                  MySqlConnection Connection = new MySqlConnection(MyConString);
                  string Query = "SELECT * FROM <YourTableName>";
                  MySqlCommand Command = new MySqlCommand(Query, Connection);
                  MySqlDataReader Reader = Command.ExecuteReader();
                  int fCount = Reader.FieldCount;
                  Console.Write( ":" );
                  for ( int i = 0; i < fCount; i ++ )
                      String fName = Reader.GetName(i);
                      Console.Write( fName + ":" );
                  while( Reader.Read())
                      Console.Write( ":" );
                      for (int i = 0; i < fCount; i++)
                          String col = Reader.GetString(i);
                          Console.Write(col + ":");
  4. Build the project using the dotnet build command.

    # dotnet build
    MSBuild version 17.4.0+18d5aef85 for .NET
      Determining projects to restore...
      All projects are up-to-date for restore.
      mysql_sample -> /root/temp/mysql_sample/bin/Debug/net7.0/mysql_sample.dll
    Build succeeded.
        0 Warning(s)
        0 Error(s)
    Time Elapsed 00:00:08.20
  5. Use the dotnet run command to run this application. Output should look as follows:

    # dotnet run
    :102:LMN:PQR:abc road:xyz city:
    :101:ABC:DEF:xyz road:Pune:


In this blog you have learned to connect MySQL database using an ADO.NET connector. If you want, you can extend this program to connect to a remote MySQL database server and run more advanced SQL queries.

Look for our other blogs with sample .NET applications connecting to other databases. Drop a comment below if you want an example of connecting to a different database.