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

While stored procedures are great, both cause it abstracts data management from the code and also because of better performance - it is often problematic writing them - finding the correct syntax /being used to SQL Server TSQL) it drives me crazy trying to figure things out.

Anyway in the examples  below: here is the SP we are gonna use:

DELIMITER $$

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

CREATE PROCEDURE `sakila`.`City_GetByCountry` (p_in_country_id int)
BEGIN
	select * from city where country_id=p_in_country_id;
END$$

DELIMITER ;

Read more on my syntactical problems in this article on ASPCode.net . MySQL5 starting with stored procedures

So now to the code:

        public DataTable GetDataSP_MySQL(long in_country_id)
        {
            MySql.Data.MySqlClient.MySqlConnection oConn = new MySql.Data.MySqlClient.MySqlConnection();
            oConn.ConnectionString = "Database=sakila;Data Source=192.168.10.4;User id=stefan;Password=pekka";
            oConn.Open();

            DataSet oDataSet = new System.Data.DataSet("tab1");
            //Get data
            MySql.Data.MySqlClient.MySqlCommand command =
                new MySql.Data.MySqlClient.MySqlCommand("City_GetByCountry", oConn);

            command.Parameters.Add(new MySql.Data.MySqlClient.MySqlParameter("?p_in_country_id",
                DbType.Int32));

            // Now, add a value to it and later execute the command as usual.
            command.Parameters[0].Value = in_country_id;
            command.CommandType = CommandType.StoredProcedure;


            MySql.Data.MySqlClient.MySqlDataAdapter oAdapter =
                new MySql.Data.MySqlClient.MySqlDataAdapter(command);
            oAdapter.Fill(oDataSet, "tab1");
            DataTable dt = oDataSet.Tables["tab1"];
            oConn.Close();

            return dt;


        }