Home > Database > Mysql Tutorial > body text

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

WBOY
Release: 2023-09-20 12:04:46
Original
761 people have browsed it

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 ;
Copy after login

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();
Copy after login
  1. 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
Copy after login

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();
Copy after login
  1. 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));
Copy after login

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);
Copy after login

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!

source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template