MySQL Connector/NET Developer Guide
MySQL 5.0 and higher supports stored procedures with the SQL 2003 stored procedure syntax.
A stored procedure is a set of SQL statements that is stored in the server. Clients make a single call to the stored procedure, passing parameters that can influence the procedure logic and query conditions, rather than issuing individual hardcoded SQL statements.
Stored procedures can be particularly useful in situations such as the following:
Stored procedures can act as an API or abstraction layer, allowing multiple client applications to perform the same database operations. The applications can be written in different languages and run on different platforms. The applications do not need to hardcode table and column names, complicated queries, and so on. When you extend and optimize the queries in a stored procedure, all the applications that call the procedure automatically receive the benefits.
When security is paramount, stored procedures keep applications from directly manipulating tables, or even knowing details such as table and column names. Banks, for example, use stored procedures for all common operations. This provides a consistent and secure environment, and procedures can ensure that each operation is properly logged. In such a setup, applications and users would not get any access to the database tables directly, but can only execute specific stored procedures.
Connector/NET supports the calling of stored procedures through the
MySqlCommand object. Data can be passed in and
out of a MySQL stored procedure through use of the
MySqlCommand.Parameters collection.
When you call a stored procedure (in versions before the MySQL
8.0 release series), the command object makes an additional
SELECT call to determine the
parameters of the stored procedure. You must ensure that the
user calling the procedure has the
SELECT privilege on the
mysql.proc table to enable them to verify the
parameters. Failure to do this will result in an error when
calling the procedure.
This section will not provide in-depth information on creating Stored Procedures. For such information, please refer to https://dev.mysql.com/doc/mysql/en/stored-routines.html.
A sample application demonstrating how to use stored procedures
with Connector/NET can be found in the Samples
directory of your Connector/NET installation.