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

Lets start with the SP code:

DELIMITER $$

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

CREATE DEFINER=`stefan`@`%` PROCEDURE `City_Insert`(in p_in_city varchar(50), in p_in_country_id int)
BEGIN
	insert into city (city,country_id) values( p_in_city, p_in_country_id);
	select LAST_INSERT_ID();
END$$

DELIMITER ;

and the C# code:

        public long InsertMySQL_GetLast_SP(string city, 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_Insert", oConn);
            command.CommandType = CommandType.StoredProcedure;
            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;
            command.Parameters[1].Value = country_id;
            //command.ExecuteNonQuery();

            long lNewId = (long)command.ExecuteScalar();

            oConn.Close();
            return lNewId;

        }