Use .NET (C#) To Connect Database For MySQL

Today in this article, we will see how to use .NET C# To Connect Database For MySQL. There are multiple techniques to connect to the MySQL server and the easiest and preferred techniques are using ADO.NET and Entity Framework (ORM) etc.

MySqlConnector is High-Performance MySQL Library for .NET.

It provides the below features overall,

• Asynchronous: asynchronous I/O support
• Fast: High speed with low memory usage
• Reliable: 
• Leading Edge: First MySQL library to support .NET Core; uses the latest .NET features

MySqlConnector is an ADO.NET data provider for the below databases,

• MySQL Server, 
• MariaDB, 
• Percona Server, 
• Amazon Aurora, 
• Azure Database for MySQL, 
• Google Cloud SQL for MySQL and more.

Today we will learn simple techniques of connecting SQL servers and performing basic CRUD operations like Read, Create, Delete, Update, etc.

Getting started

Create any .NET or .NET Core application. To keep it simple I shall be using the Console application however you can use any project template.

Add NuGet package – MySqlConnector

Please install MySqlConnector Nuget package using Nuget Package Manager,

PM> NuGet\Install-Package Microsoft.Extensions.Configuration -Version 6.0.1

OR using .NET CLI

dotnet add package Microsoft.Extensions.Configuration --version 6.0.1

MySqlConnector to Read MySQL database

The .NET Framework Data Providers like ADO.NET Providers have components that can be used for data manipulation and fast, forward-only, read-only access to data.

var builder = new ConfigurationBuilder()
             .SetBasePath(Directory.GetCurrentDirectory())
             .AddJsonFile("appsettings.json", optional: true, reloadOnChange: true)
             .AddJsonFile($"appsettings.{Environment.GetEnvironmentVariable("ASPNETCORE_ENVIRONMENT")}.json", optional: true);

            _configuration = builder.Build();

With the above basic understanding of a few major components let’s start writing the Connection logic. and then fill our DataTable and DataSet objects with actual MySQL Data.

 using (var conn = new MySqlConnection(mySQLBuilder.ConnectionString))
            {
                Console.WriteLine("Opening connection");
                await conn.OpenAsync();

                using (var command = conn.CreateCommand())
                {
                    command.CommandText = "SELECT * FROM employee ORDER BY employeeID";

                    using (var reader = await command.ExecuteReaderAsync())
                    {
                        while (await reader.ReadAsync())
                        {
                            Console.WriteLine(string.Format(
                                "Reading from table=({0}, {1}, {2})",
                                reader.GetInt32(0),
                                reader.GetString(1),
                                reader.GetInt32(2)));
                        }
                    }
                }

                Console.WriteLine("Closing connection");
            }

In the above example,

MySqlConnection– This represents a connection to a MySQL Server database.

CreateCommand– Represents a Transact-SQL statement or stored procedure to be executed against MySQL Server database.

• @1 – Open a SQL connection.

• @2 – Register MySQL a set of data commands using MySQL Command object

• @3 – Execute generic Update command( Update commands can be used for UPDATE, DELETE, or SELECT (Read) depending on the command registration in step

I am able to fetch the data successfully from MySQL Database as below,

private string ConvertDataTableasJSON(DataTable dataTable)
        {
            return JsonConvert.SerializeObject(dataTable);
        }

I am loading the Connection string from apsettings.json using the ConfigurationBuilder technique mentioned in the article,

• Reading apsettings.json Configuration – Part II

Insert Command – Update MySQL Database Record

With the above basic understanding of SqlDataAdapter commands, let now perform a simple Update where we will update the first record in the SQL Database.

sing (var command = conn.CreateCommand())
                {
          
                    command.CommandText = @"INSERT INTO employee (ID, Name) VALUES (@id1, @name1);";
                    command.Parameters.AddWithValue("@id1", "1005");
                    command.Parameters.AddWithValue("@name", "abcd");
                   

                    int rowCount = await command.ExecuteNonQueryAsync();
                    Console.WriteLine(String.Format("Number of rows inserted={0}", rowCount));
                }

Delete Command – Update MySQL Database Record

 using (var conn = new MySqlConnection(builder.ConnectionString))
            {
                Console.WriteLine("Opening connection");
                await conn.OpenAsync();

                using (var command = conn.CreateCommand())
                {
                    command.CommandText = "DELETE FROM employee WHERE ID= @id;";
                    command.Parameters.AddWithValue("@id", "1009");

                    int rowCount = await command.ExecuteNonQueryAsync();
                    Console.WriteLine(String.Format("Number of rows deleted={0}", rowCount));
                }

                Console.WriteLine("Closing connection");
            }

Do you have any comments or ideas or any better suggestions to share?

Please sound off your comments below.

Happy Coding !!

Please bookmark this page and share it with your friends. Please Subscribe to the blog to receive notifications on freshly published(2024) best practices and guidelines for software design and development.

Share on Facebook

Post on X

Follow us

Save