using System;
using System.Data;
using MySql.Data.MySqlClient;
namespace TestSP {
class TestSP {
private MySqlConnection con = null;
[STAThread]
static void Main(string[] args) {
bool error = false;
int param = 0;
try {
param = Int32.Parse(args[0]);
}
catch (Exception e) {
Console.WriteLine("You must pass an integer value " + "as parameter!");
error = true;
}
if(! error ) {
new TestSP(param);
}
}
public TestSP(int param) {
// set up the connection
if (setConnection()) {
MySqlCommand cmd = createCommandObject(param);
// first get the output parameter
int outputParameter = getOutputParameter(cmd);
if (outputParameter != -1) {
// set up the SQL command
cmd = createCommandObject(param);
// call the Stored Procedure setting the
// input parameter
callSP(cmd);
// output the output parameter
Console.WriteLine(
"The value of the output parameter is " + outputParameter
);
}
// close the connection
closeConnection();
}
}
private bool setConnection() {
// set up connection
string constring = "Data Source=localhost;" +
"User Id=root;" + "Password=pass;" + "Database=test";
con = new MySqlConnection(constring);
try {
con.Open();
}
catch (MySqlException e) {
Console.WriteLine("Could not establish connection!");
return false;
}
return true;
}
private MySqlCommand createCommandObject(int param) {
// create command object and set values
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = con;
cmd.CommandText = "testProc";
cmd.CommandType = CommandType.StoredProcedure;
// set input parameter
cmd.Parameters.Add("?_val", param);
cmd.Parameters["?_val"].Direction = ParameterDirection.Input;
// set output parameter
cmd.Parameters.Add("?square", MySqlDbType.Int32);
cmd.Parameters["?square"].Direction = ParameterDirection.Output;
return cmd;
}
private bool callSP(MySqlCommand cmd) {
try {
bool moreResults = true;
// execute Stored Procedure
MySqlDataReader reader = cmd.ExecuteReader();
// repeat while as long as there are more results
while (moreResults) {
// output result
while (reader.Read()) {
Console.WriteLine(reader.GetString(0));
}
// ask if there are more results
moreResults = reader.NextResult();
}
}
catch (MySqlException e) {
return false;
}
return true;
}
private int getOutputParameter(MySqlCommand cmd) {
try {
// execute Stored Procedure to get output parameter
cmd.ExecuteNonQuery();
// store output parameter
object outputParameter = cmd.Parameters[1].Value;
return (int)outputParameter;
}
catch (MySqlException e) {
return -1;
}
}
private void closeConnection() {
try {
con.Close();
} catch (MySqlException ignored) {}
}
}
}
|
|