svn commit - mysqldoc@docsrva: r14989 - trunk/refman-common

[anthony.bedford]

Author: tbedford
Date: 2009-05-19 12:24:19 +0200 (Tue, 19 May 2009)
New Revision: 14989

Log:
Added tutorial section on calling Stored Procedures from a Connector/NET application.

Modified:
   trunk/refman-common/connector-net-programming-tutorial.xml


Modified: trunk/refman-common/connector-net-programming-tutorial.xml
===================================================================
--- trunk/refman-common/connector-net-programming-tutorial.xml	2009-05-19 02:31:42 UTC
(rev 14988)
+++ trunk/refman-common/connector-net-programming-tutorial.xml	2009-05-19 10:24:19 UTC
(rev 14989)
Changed blocks: 1, Lines Added: 138, Lines Deleted: 0; 4853 bytes

@@ -718,4 +718,142 @@
 
   </section>
 
+  <section id="connector-net-programming-tutorial-stored-procedures">
+
+    <title>Working with Stored Procedures</title>
+
+    <para>
+      In this section you will see how to work with Stored Procedures.
+      This section assumes you have a basic understanding of what a
+      Stored Procedure is, and how to create one.
+    </para>
+
+    <para>
+      For the purposes of this tutorial, you will create a simple Stored
+      Procedure to see how it can be called from Connector/NET. In the
+      MySQL Client program, connect to the World database and enter the
+      following Stored Procedure:
+    </para>
+
+<programlisting>DELIMITER //
+CREATE PROCEDURE country_hos
+(IN con CHAR(20))
+BEGIN
+  SELECT Name, HeadOfState FROM Country 
+  WHERE Continent = con;
+END //
+DELIMITER ;</programlisting>
+
+    <para>
+      Test the Stored Procedure wors as expec ted by typing the
+      following into the MySQL Client program:
+    </para>
+
+<programlisting>CALL country_hos('Europe');</programlisting>
+
+    <para>
+      Note that The Stored Routine takes a single parameter, which is
+      the continent you wish to restrict your search to.
+    </para>
+
+    <para>
+      Having confirmed that the Stored Procedure is present and correct
+      you can now move on to seeing how it can be accessed from
+      Connector/NET.
+    </para>
+
+    <para>
+      Calling a Stored Procedure from your Connector/NET application is
+      similar to techniques you have seen earlier in this tutorial. A
+      <literal>MySqlCommand</literal> object is created, but rather than
+      taking a SQL query as a parameter it takes the name of the Stored
+      Procedure to call. The <literal>MySqlCommand</literal> object also
+      needs to be set to the type of Stored Procedure. This is
+      illustrated by the following code snippet:
+    </para>
+
+<programlisting>string rtn = "country_hos";
+MySqlCommand cmd = new MySqlCommand(rtn, conn);
+cmd.CommandType = CommandType.StoredProcedure;</programlisting>
+
+    <para>
+      In this case you also need to pass a parameter to the Stored
+      Procedure. This can be achieved using the techniques seen in the
+      previous section on parameters,
+      <xref linkend="connector-net-programming-tutorial-parameters"/>.
+      This is shown in the following code snippet:
+    </para>
+
+<programlisting>MySqlParameter param = new MySqlParameter();
+param.ParameterName = "@con";
+param.Value = "Europe";
+cmd.Parameters.Add(param);</programlisting>
+
+    <para>
+      The value of the parameter <literal>@con</literal> could more
+      realistically have come from a user input control, but for
+      simplicity it is set as a static string in this example.
+    </para>
+
+    <para>
+      At this point everything is set up and all that now needs to be
+      done is to call the routine. This can be achieved using techniques
+      also learned in earlier sections, but in this case the
+      <literal>ExecuteReader</literal> method of the
+      <literal>MySqlCommand</literal> object is used.
+    </para>
+
+    <para>
+      Complete working code for the Stored Procedure example is shown
+      below:
+    </para>
+
+<programlisting>using System;
+using System.Data;
+
+using MySql.Data;
+using MySql.Data.MySqlClient;
+
+public class Tutorial1
+{
+    public static void Main()
+    {
+        string connStr =
"server=localhost;user=root;database=world;port=3306;password=090662;";
+        MySqlConnection conn = new MySqlConnection(connStr);
+        try
+        {
+            Console.WriteLine("Connecting to MySQL...");
+            conn.Open();
+
+            string rtn = "country_hos";
+            MySqlCommand cmd = new MySqlCommand(rtn, conn);
+            cmd.CommandType = CommandType.StoredProcedure;
+            MySqlParameter param = new MySqlParameter();
+            param.ParameterName = "@con";
+            param.Value = "Europe";
+            cmd.Parameters.Add(param);
+
+            MySqlDataReader rdr = cmd.ExecuteReader();
+            while (rdr.Read())
+            {
+                Console.WriteLine(rdr[0] + " --- " + rdr[1]);
+            }
+            conn.Close();
+        }
+        catch (Exception ex)
+        {
+            Console.WriteLine(ex.ToString());
+        }
+        Console.WriteLine("Done.");
+    }
+}</programlisting>
+
+    <para>
+      In this section you have seen how to call a Stored Procedure from
+      Connector/NET. For the moment, this concludes our introductory
+      tutorial on programming with Connector/NET.
+    </para>
+
+  </section>
+
 </section>