MySQL Stored Procedure Programming

Using Stored Programs in ADO.NET

Stored programs have always been an integral and important part of application development within SQL Server, and SQL Server support is a primary focus of the ADO.NET interfaces. Unlike some implementations of stored programs (Oracle’s for instance), SQL Server’s stored programs can directly return multiple result sets, which results in the ADO.NET interfaces providing very natural support for the MySQL implementation.

Calling a Simple Stored Procedure

Let’s start with a very simple stored procedure. Example 17-19 shows a simple stored procedure that takes no parameters and returns no result sets.

Example 17-19. A simple stored procedure

CREATE PROCEDURE sp_simple(  )
BEGIN
 SET autocommit=0;
END;

Calling this stored procedure is only slightly more complex than calling a non-SELECT statement, as described in "Issuing a Non-SELECT Statement" earlier in this chapter. The procedure for calling this stored procedure differs in two small ways:

• The text for the SQL call contains only the stored procedure—the CALL statement is unnecessary, as are parentheses to represent the parameter list.

• The CommandType property of the MySqlCommand object should be set to CommandType.StoredProcedure.

Example 17-20 illustrates the process of calling the simple stored procedure from Example 17-19 in VB.NET. The name of the stored procedure is used to initialize the MySqlCommand object, and the CommandType for that object is set to CommandType.StoredProcedure. The stored procedure ...