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;" _
- & "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 ex As MySqlException
- MessageBox.Show("Error " & ex.Number & " has occurred: " & 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;" _
- & "uid=root;" _
- & "pwd=12345;" _
- & "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 " & ex.Number & " has occurred: " & 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-common | tony.bedford | 30 Mar |
