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

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

#MySQL is a widely used open source relational database management system, and C# It is a powerful programming language, and MySQL and C# are good choices for development tasks that require interaction with databases. In MySQL, we can use C# to write custom stored procedures, triggers and functions to achieve more flexible and powerful database operations.

This article will guide you through examples of writing and executing custom stored procedures, triggers, and functions using C#. We will introduce how stored procedures, triggers and functions are defined and sample code respectively. Note that this article assumes you have MySQL and a C# development environment installed.

1. Custom stored procedure

A stored procedure is a block of SQL code that is pre-compiled and stored in the database and can be called by name and parameters. Below is an example that demonstrates how to create and execute a simple stored procedure.

First, create a stored procedure named GetEmployeeByID in MySQL:

DELIMITER //
CREATE PROCEDURE GetEmployeeByID(IN empID INT)
BEGIN
   SELECT * FROM Employees WHERE EmployeeID = empID;
END //
DELIMITER ;

Then, you can use C# to call this stored procedure:

using MySql.Data.MySqlClient;

MySqlConnection connection = new MySqlConnection("connectionString");
MySqlCommand command = new MySqlCommand("GetEmployeeByID", connection);
command.CommandType = CommandType.StoredProcedure;

command.Parameters.AddWithValue("@empID", 1);

connection.Open();
MySqlDataReader reader = command.ExecuteReader();

while (reader.Read())
{
    Console.WriteLine(reader["EmployeeName"]);
}

reader.Close();
connection.Close();

2. Custom trigger

A trigger is a special type of stored procedure that automatically executes when a specified operation occurs in the database. Below is an example showing how to create and use a trigger.

First, create a trigger named UpdateInventory in MySQL:

CREATE TRIGGER UpdateInventory AFTER INSERT ON Orders
FOR EACH ROW
BEGIN
   UPDATE Products SET Inventory = Inventory - NEW.Quantity WHERE ProductID = NEW.ProductID;
END

Then, you can perform an insert operation in C# to trigger this trigger:

using MySql.Data.MySqlClient;

MySqlConnection connection = new MySqlConnection("connectionString");
MySqlCommand command = new MySqlCommand("INSERT INTO Orders (ProductID, Quantity) VALUES (1, 10)", connection);

connection.Open();
command.ExecuteNonQuery();
connection.Close();

3. Custom functions

A function is a reusable piece of code that can be used like a built-in function in MySQL. Below is an example that demonstrates how to create and use a custom function.

First, create a function named CalculateDiscount in MySQL:

CREATE FUNCTION CalculateDiscount(price DECIMAL(10,2), discount DECIMAL(10,2))
RETURNS DECIMAL(10,2)
RETURN price - (price * (discount / 100));

Then, you can call this function in C#:

using MySql.Data.MySqlClient;

MySqlConnection connection = new MySqlConnection("connectionString");
MySqlCommand command = new MySqlCommand("SELECT CalculateDiscount(100, 10)", connection);

connection.Open();
object result = command.ExecuteScalar();
connection.Close();

Console.WriteLine(result);

Above That's an example of how to write custom stored procedures, triggers, and functions in MySQL using C#. These examples provide basic usage that you can modify and extend appropriately to suit your needs. Hope it helps you better understand and use MySQL and C#.

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