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

Lets create a single stored procedure to be used for both inserts and updates. By sending in -1 as city_id this will trigger an insert, and a value as city_id will mean we update that row

Lets look at how the stored procedure look like:

DELIMITER $$

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

CREATE DEFINER=`stefan`@`%` PROCEDURE `City_Save`(in p_in_city_id int, in p_in_city varchar(50), in p_in_country_id int)
BEGIN

IF p_in_city_id = -1 THEN  
	insert into city(city, country_id) values( p_in_city, p_in_country_id );   
	select  LAST_INSERT_ID();   
ELSE  
	update city set city= p_in_city, country_id=p_in_country_id where city_id=p_in_city_id;
	select  p_in_city_id;   
END IF;   

END$$

DELIMITER ;

and the code calling it (we send in -1 as citY_id for insert, remember)

        public long UpdateOrInsertMySql_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_Save", 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;

            long lId = Convert.ToInt32( command.ExecuteScalar());

            oConn.Close();
            return lId;

        }