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.
CREATE PROCEDURE sp_simple( ) BEGIN SET autocommit=0; END;
Calling this stored procedure is only slightly more complex
than calling a non-
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
CALLstatement is unnecessary, as are parentheses to represent the parameter list.
CommandTypeproperty of the
MySqlCommandobject should be set to
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
CommandType.StoredProcedure. The stored procedure ...