List:Commits« Previous MessageNext Message »
From:tony.bedford Date:March 30 2010 3:38pm
Subject:svn commit - mysqldoc@docsrva: r19774 - trunk/refman-common
View as plain text  
Author: tbedford
Date: 2010-03-30 17:38:37 +0200 (Tue, 30 Mar 2010)
New Revision: 19774

Log:
Reworked example code, so that it actually worked. Removed VB version for now (to be added later).

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


Modified: trunk/refman-common/connector-net-programming.xml
===================================================================
--- trunk/refman-common/connector-net-programming.xml	2010-03-30 15:20:40 UTC (rev 19773)
+++ trunk/refman-common/connector-net-programming.xml	2010-03-30 15:38:37 UTC (rev 19774)
Changed blocks: 3, Lines Added: 88, Lines Deleted: 151; 11245 bytes

@@ -1068,7 +1068,7 @@
     <para>
       Connector/NET supports the calling of stored procedures through
       the <literal>MySqlCommand</literal> object. Data can be passed in
-      and our of a MySQL stored procedure through use of the
+      and out of a MySQL stored procedure through use of the
       <literal>MySqlCommand.Parameters</literal> collection.
     </para>
 

@@ -1097,103 +1097,36 @@
       directory of your Connector/NET installation.
     </para>
 
-    <section id="connector-net-programming-stored-creating">
+    <section id="connector-net-programming-stored-using">
 
-      <title>Creating Stored Procedures from Connector/NET</title>
+      <title>Using Stored Routines from Connector/NET</title>
 
       <para>
         Stored procedures in MySQL can be created using a variety of
         tools. First, stored procedures can be created using the
         <command>mysql</command> command-line client. Second, stored
-        procedures can be created using the <literal>MySQL Query
-        Browser</literal> GUI client. Finally, stored procedures can be
-        created using the <literal>.ExecuteNonQuery</literal> method of
-        the <literal>MySqlCommand</literal> object:
+        procedures can be created using &workbench;. Finally, stored
+        procedures can be created using the
+        <literal>.ExecuteNonQuery</literal> method of the
+        <literal>MySqlCommand</literal> object.
       </para>
 
       <para>
-        Visual Basic Example
-      </para>
-
-<programlisting language="VB">
-Dim conn As New MySqlConnection
-Dim cmd As New MySqlCommand
-
-conn.ConnectionString = "server=127.0.0.1;" _
-    &amp; "uid=root;" _
-    &amp; "pwd=12345;" _
-    &amp; "database=test"
-
-Try
-    conn.Open()
-    cmd.Connection = conn
-
-    cmd.CommandText = "CREATE PROCEDURE add_emp(" _
-        &amp; "IN fname VARCHAR(20), IN lname VARCHAR(20), IN bday DATETIME, OUT empno INT) " _
-        &amp; "BEGIN INSERT INTO emp(first_name, last_name, birthdate) " _
-        &amp; "VALUES(fname, lname, DATE(bday)); SET empno = LAST_INSERT_ID(); END"
-
-    cmd.ExecuteNonQuery()
-Catch ex As MySqlException
-    MessageBox.Show("Error " &amp; ex.Number &amp; " has occurred: " &amp; ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
-End Try
-</programlisting>
-
-      <para>
-        C# Example
-      </para>
-
-<programlisting language="C#">
-MySql.Data.MySqlClient.MySqlConnection conn;
-MySql.Data.MySqlClient.MySqlCommand cmd;
-
-conn = new MySql.Data.MySqlClient.MySqlConnection();
-cmd = new MySql.Data.MySqlClient.MySqlCommand();
-
-conn.ConnectionString = "server=127.0.0.1;uid=root;" +
-    "pwd=12345;database=test;";
-
-try
-{
-    conn.Open();
-    cmd.Connection = conn;
-
-    cmd.CommandText = "CREATE PROCEDURE add_emp(" +
-        "IN fname VARCHAR(20), IN lname VARCHAR(20), IN bday DATETIME, OUT empno INT) " +
-        "BEGIN INSERT INTO emp(first_name, last_name, birthdate) " +
-        "VALUES(fname, lname, DATE(bday)); SET empno = LAST_INSERT_ID(); END";
-
-    cmd.ExecuteNonQuery();
-}
-catch (MySql.Data.MySqlClient.MySqlException ex)
-{
-MessageBox.Show("Error " + ex.Number + " has occurred: " + ex.Message,
-    "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
-}
-</programlisting>
-
-      <para>
         It should be noted that, unlike the command-line and GUI
         clients, you are not required to specify a special delimiter
         when creating stored procedures in Connector/NET.
       </para>
 
-    </section>
-
-    <section id="connector-net-programming-stored-calling">
-
-      <title>Calling a Stored Procedure from Connector/NET</title>
-
       <para>
-        To call a stored procedure using Connector/NET, create a
+        To call a stored procedure using Connector/NET, you create a
         <literal>MySqlCommand</literal> object and pass the stored
         procedure name as the <literal>.CommandText</literal> property.
-        Set the <literal>.CommandType</literal> property to
+        You then set the <literal>.CommandType</literal> property to
         <literal>CommandType.StoredProcedure</literal>.
       </para>
 
       <para>
-        After the stored procedure is named, create one
+        After the stored procedure is named, you create one
         <literal>MySqlCommand</literal> parameter for every parameter in
         the stored procedure. <literal>IN</literal> parameters are
         defined with the parameter name and the object containing the

@@ -1203,102 +1136,106 @@
       </para>
 
       <para>
-        After defining parameters, call the stored procedure by using
-        the <literal>MySqlCommand.ExecuteNonQuery()</literal> method:
+        After defining the parameters, you call the stored procedure by
+        using the <literal>MySqlCommand.ExecuteNonQuery()</literal>
+        method.
       </para>
 
       <para>
-        Visual Basic Example
+        Once the stored procedure is called, the values of the output
+        parameters can be retrieved by using the
+        <literal>.Value</literal> property of the
+        <literal>MySqlConnector.Parameters</literal> collection.
       </para>
 
-<programlisting language="VB">
-Dim conn As New MySqlConnection
-Dim cmd As New MySqlCommand
-
-conn.ConnectionString = "server=127.0.0.1;" _
-    &amp; "uid=root;" _
-    &amp; "pwd=12345;" _
-    &amp; "database=test"
-
-Try
-    conn.Open()
-    cmd.Connection = conn
-
-    cmd.CommandText = "add_emp"
-    cmd.CommandType = CommandType.StoredProcedure
-
-    cmd.Parameters.AddWithValue("@lname", "Jones")
-    cmd.Parameters["@lname"].Direction = ParameterDirection.Input
-
-    cmd.Parameters.AddWithValue("@fname", "Tom")
-    cmd.Parameters["@fname"].Direction = ParameterDirection.Input
-
-    cmd.Parameters.AddWithValue("@bday", #12/13/1977 2:17:36 PM#)
-    cmd.Parameters["@bday"].Direction = ParameterDirection.Input
-
-    cmd.Parameters.Add("@empno", MySqlDbType.Int32)
-    cmd.Parameters["@empno"].Direction = ParameterDirection.Output
-
-    cmd.ExecuteNonQuery()
-
-    MessageBox.Show(cmd.Parameters["@empno"].Value)
-Catch ex As MySqlException
-    MessageBox.Show("Error " &amp; ex.Number &amp; " has occurred: " &amp; ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
-End Try
-</programlisting>
-
       <para>
-        C# Example
+        The following example code demonstrates the use of stored
+        procedures. It assumes the database 'employees' has already been
+        created:
       </para>
 
 <programlisting language="C#">
-MySql.Data.MySqlClient.MySqlConnection conn;
-MySql.Data.MySqlClient.MySqlCommand cmd;
+using System;
+using System.Collections.Generic;
+using System.Linq;
+using System.Text;
 
-conn = new MySql.Data.MySqlClient.MySqlConnection();
-cmd = new MySql.Data.MySqlClient.MySqlCommand();
+using System.Data;
+using MySql.Data;
+using MySql.Data.MySqlClient;
 
-conn.ConnectionString = "server=127.0.0.1;uid=root;" +
-    "pwd=12345;database=test;";
-
-try
+namespace UsingStoredRoutines
 {
-    conn.Open();
-    cmd.Connection = conn;
+    class Program
+    {
+        static void Main(string[] args)
+        {
+            MySqlConnection conn = new MySqlConnection();
+            conn.ConnectionString = "server=localhost;user=root;database=employees;port=3306;password=******;";
+            MySqlCommand cmd = new MySqlCommand();
 
-    cmd.CommandText = "add_emp";
-    cmd.CommandType = CommandType.StoredProcedure;
+            try
+            {
+                Console.WriteLine("Connecting to MySQL...");
+                conn.Open();
+                cmd.Connection = conn;
+                cmd.CommandText = "DROP PROCEDURE IF EXISTS add_emp";
+                cmd.ExecuteNonQuery();
+                cmd.CommandText = "DROP TABLE IF EXISTS emp";
+                cmd.ExecuteNonQuery();
+                cmd.CommandText = "CREATE TABLE emp (empno INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, first_name VARCHAR(20), last_name VARCHAR(20), birthdate DATE)";
+                cmd.ExecuteNonQuery();
 
-    cmd.Parameters.AddWithValue("@lname", "Jones");
-    cmd.Parameters["@lname"].Direction = ParameterDirection.Input;
+                cmd.CommandText = "CREATE PROCEDURE add_emp(" +
+                                  "IN fname VARCHAR(20), IN lname VARCHAR(20), IN bday DATETIME, OUT empno INT)" +
+                                  "BEGIN INSERT INTO emp(first_name, last_name, birthdate) " +
+                                  "VALUES(fname, lname, DATE(bday)); SET empno = LAST_INSERT_ID(); END";
 
-    cmd.Parameters.AddWithValue("@fname", "Tom");
-    cmd.Parameters["@fname"].Direction = ParameterDirection.Input;
+                cmd.ExecuteNonQuery();
+            }
+            catch (MySqlException ex)
+            {
+                Console.WriteLine ("Error " + ex.Number + " has occurred: " + ex.Message);
+            }
+            conn.Close();
+            Console.WriteLine("Connection closed.");
+            try
+            {
+                Console.WriteLine("Connecting to MySQL...");
+                conn.Open();
+                cmd.Connection = conn;
 
-    cmd.Parameters.AddWithValue("@bday", DateTime.Parse("12/13/1977 2:17:36 PM"));
-    cmd.Parameters["@bday"].Direction = ParameterDirection.Input;
+                cmd.CommandText = "add_emp";
+                cmd.CommandType = CommandType.StoredProcedure;
 
-    cmd.Parameters.Add("@empno", MySqlDbType.Int32);
-    cmd.Parameters["@empno"].Direction = ParameterDirection.Output;
+                cmd.Parameters.AddWithValue("@lname", "Jones");
+                cmd.Parameters["@lname"].Direction = ParameterDirection.Input;
 
-    cmd.ExecuteNonQuery();
+                cmd.Parameters.AddWithValue("@fname", "Tom");
+                cmd.Parameters["@fname"].Direction = ParameterDirection.Input;
 
-    MessageBox.Show(cmd.Parameters["@empno"].Value);
+                cmd.Parameters.AddWithValue("@bday", "1940-06-07");
+                cmd.Parameters["@bday"].Direction = ParameterDirection.Input;
+
+                cmd.Parameters.AddWithValue("@empno", MySqlDbType.Int32);
+                cmd.Parameters["@empno"].Direction = ParameterDirection.Output;
+
+                cmd.ExecuteNonQuery();
+
+                Console.WriteLine("Employee number: "+cmd.Parameters["@empno"].Value);
+                Console.WriteLine("Birthday: " + cmd.Parameters["@bday"].Value);
+            }
+            catch (MySql.Data.MySqlClient.MySqlException ex)
+            {
+                Console.WriteLine("Error " + ex.Number + " has occurred: " + ex.Message);
+            }
+            conn.Close();
+            Console.WriteLine("Done.");
+        }
+    }
 }
-catch (MySql.Data.MySqlClient.MySqlException ex)
-{
-    MessageBox.Show("Error " + ex.Number + " has occurred: " + ex.Message,
-      "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
-}
 </programlisting>
 
-      <para>
-        Once the stored procedure is called, the values of output
-        parameters can be retrieved by using the
-        <literal>.Value</literal> property of the
-        <literal>MySqlConnector.Parameters</literal> collection.
-      </para>
-
     </section>
 
   </section>


Thread
svn commit - mysqldoc@docsrva: r19774 - trunk/refman-commontony.bedford30 Mar