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
mysql
# 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
quit

    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
Bye

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">

  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>net7.0</TargetFramework>
    <ImplicitUsings>enable</ImplicitUsings>
    <Nullable>enable</Nullable>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="MySql.Data" Version="8.0.31" />
  </ItemGroup>

</Project>

  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>; " +
            "PWD=<YourPassword>;";

            MySqlConnection Connection = new MySqlConnection(MyConString);

            Connection.Open();

            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 + ":" );
            }
            Console.WriteLine();

            while( Reader.Read())
            {
                Console.Write( ":" );
                for (int i = 0; i < fCount; i++)
                {
                    String col = Reader.GetString(i);
                    Console.Write(col + ":");
                }
                Console.WriteLine();
            }
            Reader.Close();
            Command.Dispose();
            Connection.Close();
        }
    }
}​

  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
:PersonID:LastName:FirstName:Address:City:
:102:LMN:PQR:abc road:xyz city:
:101:ABC:DEF:xyz road:Pune:

Summary

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.

References

Permalink