How to write custom stored procedures and functions in MySQL using C#

How to write custom stored procedures and functions in MySQL using C

#Introduction:
MySQL is a widely used open source database management system, and C# is A commonly used object-oriented programming language. During the development process, we often need to use database stored procedures and functions to improve code reusability and performance. This article will introduce how to use C# to write custom stored procedures and functions in a MySQL database, and provide specific code examples.

1. Stored procedures
A stored procedure is a set of SQL statements that perform specific tasks. The following will introduce in detail how to write custom stored procedures in MySQL using C#.

1.1 Create a stored procedure
First, create a new database in MySQL and create a data table named "TestDB". The table structure is as follows:

CREATE TABLE TestTable (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(100) DEFAULT NULL,
age int(11) DEFAULT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Next, create a new C# console application in Visual Studio. Connect to the database via the MySQL connection string, and then execute the following code to create a stored procedure named "GetAllData":

using MySql.Data.MySqlClient;

namespace MySQLExample
{

class Program
{
    static void Main(string[] args)
    {
        string connectionString = "server=localhost;username=root;password=password;database=TestDB";
        MySqlConnection connection = new MySqlConnection(connectionString);
        MySqlCommand cmd = new MySqlCommand();
        cmd.Connection = connection;

        cmd.CommandText = "CREATE PROCEDURE GetAllData() " +
                           "BEGIN " +
                           "SELECT * FROM TestTable; " +
                           "END";

        try
        {
            connection.Open();
            cmd.ExecuteNonQuery();
            Console.WriteLine("存储过程创建成功！");
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }
        finally
        {
            connection.Close();
        }

        Console.ReadKey();
    }
}

}

The above code creates a stored procedure named "GetAllData" by executing the CREATE PROCEDURE statement. This stored procedure can be called directly in the database and will return all the data in the TestTable table.

1.2 Calling the stored procedure
Next, let’s demonstrate how to call the just created stored procedure in C#.

Based on the previous step, we can write the following code to call the stored procedure and get the returned data:

using MySql.Data.MySqlClient;

namespace MySQLExample
{

class Program
{
    static void Main(string[] args)
    {
        string connectionString = "server=localhost;username=root;password=password;database=TestDB";
        MySqlConnection connection = new MySqlConnection(connectionString);
        MySqlCommand cmd = new MySqlCommand();
        cmd.Connection = connection;

        cmd.CommandText = "GetAllData";
        cmd.CommandType = CommandType.StoredProcedure;

        try
        {
            connection.Open();
            MySqlDataReader reader = cmd.ExecuteReader();

            while (reader.Read())
            {
                Console.WriteLine(reader["id"].ToString() + " " + reader["name"].ToString() + " " + reader["age"].ToString());
            }

            reader.Close();
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }
        finally
        {
            connection.Close();
        }

        Console.ReadKey();
    }
}

}

The above code obtains all the data in the TestTable table by executing the "GetAllData" stored procedure, and outputs the results to the console.

2. Function
The function is a reusable SQL code block that calculates the value of the input parameter and returns a value. The following will introduce in detail how to write custom functions in MySQL using C#.

2.1 Create function
Create a function named "GetAverageAge" in MySQL, which will calculate the average age of all people in the TestTable table and return it.

The code to create the function is as follows:

CREATE FUNCTION GetAverageAge() RETURNS INT(11)
BEGIN
DECLARE avg_age INT(11);
SELECT AVG(age) INTO avg_age FROM TestTable;
RETURN avg_age;
END

2.2 Call the function
Use the following code to call the function just created and get the returned value:

using MySql.Data.MySqlClient;

namespace MySQLExample
{

class Program
{
    static void Main(string[] args)
    {
        string connectionString = "server=localhost;username=root;password=password;database=TestDB";
        MySqlConnection connection = new MySqlConnection(connectionString);
        
        try
        {
            connection.Open();
            MySqlCommand cmd = new MySqlCommand("SELECT GetAverageAge()", connection);
            int averageAge = Convert.ToInt32(cmd.ExecuteScalar());

            Console.WriteLine("平均年龄：" + averageAge);
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }
        finally
        {
            connection.Close();
        }

        Console.ReadKey();
    }
}

}

The above code is called by executing the SELECT GetAverageAge() statement "GetAverageAge" function and get the average age returned by the function.

Conclusion:
This article introduces how to use C# to write custom stored procedures and functions in MySQL, and provides specific code examples. Stored procedures and functions can improve code reusability and performance and are suitable for various complex data processing needs. By using C# to connect to the MySQL database, we can flexibly write and call stored procedures and functions, thereby giving full play to the advantages of the MySQL database.

The above is the detailed content of How to write custom stored procedures and functions in MySQL using C#. For more information, please follow other related articles on the PHP Chinese website!