Published 4/30/2007 1:45:09 AM  in  MySQL

Stored procedure:

DELIMITER $$

DROP PROCEDURE IF EXISTS `sakila`.`City_Update`$$

CREATE DEFINER=`stefan`@`%` PROCEDURE `City_Update`(in p_in_city_id int, in p_in_city varchar(50), in p_in_country_id int)
BEGIN
	update city set city= p_in_city, country_id=p_in_country_id where city_id=p_in_city_id;
END$$

DELIMITER ;

C# Code:

        public void UpdateMySql_SP(int city_id, string newcity, int country_id)
        {
            MySql.Data.MySqlClient.MySqlConnection oConn =
                new MySql.Data.MySqlClient.MySqlConnection("Database=sakila;Data Source=192.168.10.4;User id=stefan;Password=pekka");

            oConn.Open();
            MySql.Data.MySqlClient.MySqlCommand command =
                new MySql.Data.MySqlClient.MySqlCommand("City_Update", oConn);

            command.CommandType = CommandType.StoredProcedure;
            command.Parameters.Add(new MySql.Data.MySqlClient.MySqlParameter("?p_in_city_id", MySql.Data.MySqlClient.MySqlDbType.Int64));
            command.Parameters.Add(new MySql.Data.MySqlClient.MySqlParameter("?p_in_city", MySql.Data.MySqlClient.MySqlDbType.VarChar, 50));
            command.Parameters.Add(new MySql.Data.MySqlClient.MySqlParameter("?p_in_country_id", MySql.Data.MySqlClient.MySqlDbType.Int64));
            command.Parameters[0].Value = city_id;
            command.Parameters[1].Value = newcity;
            command.Parameters[2].Value = country_id;

            command.ExecuteNonQuery();

            oConn.Close();

        }