首先在数据库中兴建一个存储过程:
DELIMITER //
CREATE PROCEDURE country_hos
(IN con CHAR(20))
BEGIN
SELECT Name, HeadOfState FROM Country
WHERE Continent = con;
END //
DELIMITER ;
如果想测试一下行不行;可以用以下来查询;
CALL country_hos('Europe');
以下是全部程序;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MySql.Data;
using MySql.Data.MySqlClient;
using System.Data;
namespace ConsoleApplication1
{
class Program
{
static void Main(string[] args)
{
string connStr = "server=localhost;user=root;database=world;port=3306;password=1234;";
MySqlConnection conn = new MySqlConnection(connStr);
try
{
Console.WriteLine("Connecting to MySQL...");
conn.Open();
//首先连接并选择连接类型为存储过程;
string sql = "country_hos";
MySqlCommand cmd = new MySqlCommand(sql, conn);
cmd.CommandType = CommandType.StoredProcedure;
Console.WriteLine("Enter a continent e.g. 'North America', 'Europe': ");
string user_input = Console.ReadLine();
//这里是添加变量,给变量赋值;
cmd.Parameters.AddWithValue("@con", user_input);
//多行数据读入;
MySqlDataReader rdr = cmd.ExecuteReader();
//这里的Read函数是一行一行的读;其中有自增的,返回的是True或False;
while(rdr.Read())
{
Console.WriteLine(rdr["Name"] + "------" + rdr["HeadOfState"]);
}
rdr.Close(); //这里注意要关闭这个reader;
}
catch (Exception ex)
{
Console.WriteLine(ex.ToString());
}
conn.Close();
Console.WriteLine("Done.");
Console.ReadKey();
}
}
}
下面是运行结果;