How to SELECT data from a MySQL database using C#

A guide on how to use C# to query data from a MySQL database table using Prepared Statements with multiple parameters for safe and efficient data retrieval.

In this article, you will learn how to connect to a MySQL database and use Prepared Statements in C# to execute a SELECT query with multiple parameters, allowing you to safely retrieve data from a table in the database.

using System;
using MySql.Data.MySqlClient;

namespace MySQLSelectExample
{
    class Program
    {
        static void Main(string[] args)
        {
            // MySQL database connection information
            string connectionString = "server=localhost;user=root;password=password;database=test_db";

            using (MySqlConnection conn = new MySqlConnection(connectionString))
            {
                try
                {
                    // Open the connection
                    conn.Open();

                    // SELECT statement with Prepared Statement
                    string query = "SELECT * FROM students WHERE age = @age AND city = @city";

                    using (MySqlCommand cmd = new MySqlCommand(query, conn))
                    {
                        // Assign values to the parameters
                        cmd.Parameters.AddWithValue("@age", 20);
                        cmd.Parameters.AddWithValue("@city", "Hanoi");

                        using (MySqlDataReader reader = cmd.ExecuteReader())
                        {
                            // Loop through the query results and display them
                            while (reader.Read())
                            {
                                Console.WriteLine($"ID: {reader["id"]}, Name: {reader["name"]}, Age: {reader["age"]}, City: {reader["city"]}");
                            }
                        }
                    }
                }
                catch (Exception ex)
                {
                    Console.WriteLine($"Error: {ex.Message}");
                }
                finally
                {
                    // Close the connection
                    conn.Close();
                }
            }
        }
    }
}

Detailed explanation:

using MySql.Data.MySqlClient: Imports the MySql.Data.MySqlClient library to work with MySQL.
string connectionString = ...: The connection string containing the necessary information to connect to the MySQL database.
using (MySqlConnection conn = new MySqlConnection(connectionString)): Creates the MySQL connection object.
conn.Open(): Opens the connection to the database.
string query = "SELECT * FROM students WHERE age = @age AND city = @city": The SELECT query with @age and @city as parameters.
cmd.Parameters.AddWithValue(...): Assigns values to the parameters.
using (MySqlDataReader reader = cmd.ExecuteReader()): Executes the SELECT query and retrieves the data.
while (reader.Read()): Iterates through each row of data in the query result.
Console.WriteLine(...): Displays the data.
conn.Close(): Closes the connection.

System Requirements:

.NET Framework or .NET Core
MySQL library: MySql.Data

How to install the libraries needed to run the C# code above:

Use the NuGet Package Manager to install the MySQL library:

Install-Package MySql.Data

Tips:

Always use Prepared Statements to prevent SQL injection attacks.
Double-check your connection string for accuracy to ensure a successful connection.

How to POST Data to an API using C#

A guide on how to send data to an API using the POST method in C# with the HttpClient class, enabling you to easily interact with APIs.

How to GET JSON data from an API using C#

A guide on how to retrieve JSON data from an API using C#, leveraging the HttpClient class and Newtonsoft.Json library for processing data.

How to INSERT data into a MySQL database using C#

A guide on how to use Prepared Statements in C# to insert data into a table in a MySQL database safely and effectively.

Send JavaScript code to a website using Selenium in C#

A guide on how to use Selenium in C# to send a JavaScript snippet to a website opened in the Chrome browser. The article will provide sample code and detailed explanations for each step.

Common Functions Used with Selenium Chrome in C#

This article lists and describes common functions used when working with Selenium Chrome in C#. These functions help automate tasks in the Chrome browser effectively.

Convert markdown string to HTML using C#

A guide on how to convert a markdown string into HTML in C# using the MarkdownSharp library.

How to UPDATE data in a MySQL database using C#

A guide on how to use Prepared Statements in C# to update data in a MySQL table safely and efficiently with multiple parameters.

How to open Notepad using C#

A guide on how to open the Notepad application using C# via the `Process` class in .NET. This tutorial helps C# developers understand how to interact with external applications using simple code.

Passing Authentication Header Token when Posting Data to API Using C#

A guide on how to pass the Authentication Header Token when making a POST request to an API using C# by utilizing HttpClient and a Bearer Token.

How to automatically log into a website using Selenium with Chrome in C#

A guide on how to use Selenium in C# to automatically log into a website. This article will use the Chrome browser and outline step-by-step how to automate the login process.

How to SELECT data from a MySQL database using C#

Detailed explanation:

System Requirements:

How to install the libraries needed to run the C# code above:

Tips:

Related